Tutorials Oracle 9i Tutorial 13: Oracle 9i : Procedures and Functions Tutorial 13: Oracle 9i : Procedures and Functions - Page 2
Functions:
A function is a PL/SQL subprogram, which is used to compute a value. Function is same like a procedure except for the difference that it have RETURN clause.
Syntax for Function
Examples
Function without arguments
Function with arguments. Different ways of executing the function.
Privileges for creation and execution of procedures To create a procedure in current user schema we use the syntax
CREATE OR REPLACE PROCEDURE (<argslist) IS
-------
-------
To create procedure in any schema we use
CREATE OR REPLACE ANY PROCEDURE (<argslist) IS
-------
-------
To grant EXECUTE privilege to some user on a procedure we write
GRANT EXECUTE ON <procedure-name> TO <user-name>
SQL> SELECT * FROM USER_PROCEDURES;
Lists all the procedures, functions in current user.
SQL> SELECT * FROM USER_PROCEDURES;
Lists all the procedures, functions in all users.
SQL> DROP PROCEDURE <procedure-name> ;
Drops or Removes a procedure from database
SQL> DROP FUNCTION <function-name> ;
Drops or Removes a function from database
First Page: Tutorial 13: Oracle 9i : Procedures and Functions
| [EMAIL]psdchak@yahoo.com[/EMAIL] |
|
hi all, please send me anser what is difference b/w truncate and delete command and exaMPLE |
|
HI ambuj 1. Delete is DML command. after which u can rollback.But Truncate is DDL command after which it do commit. 2.In delete u can give some condition so it will delete not all row. But in truncate there is no such option. eg delete from emp where eno=134 it will delete that row which have eno 134. To delete all row u can write delete from emp // But it is not permanent truncate from emp;// it delete all rows and its deletion is permanent |
|
truncate is a ddl and delete is a dml command tru delete always segment,not a record so that u can't rollback while delete deletes row and u can rollback because after trun autocommit occur |
| can a function return more than one value at a time? |
|
'NO'........FUNCTION CANT RETURN MORE THAN ONE VALUE AT A TIME. BY USING "OUT PARAMETER" IN THE PROCEDURE WE CAN RETURN MORE THAN ONE VALUE AT A TIME. |
| how can i fetch a value from an already existing table, and execute an operation in my procedure using select statement |
| what is the use of p-code?where we can use please give me any example for easy understan? |
| what is difference between IS/AS in procedure and function in oracle9i |
| difine diff b/w in out inout parmeters with example and also return type , boc students are not able to understand wht is basic diff b/w them |
|
please tell me how to execute pl sql block.... declare a number; b number; c number; begin a:=&a; b:=&b; c:=a+b; dbms_output.put_line('Sum of ' || a || ' and ' || b || ' is ' || c); Here to take user input at runtime.....but its giving error... |
| how can i call a parameterised procedure in program?plz explain with example. |
|
can any one help me to rectify errors in this package create or replace package pack1 as FUNCTION fun1(dno number) return number; PROCEDURE PR1(eno number); end; CREATE OR REPLACE package body pack1 as FUNCTION fun1(dno number) return number as lag ask_employees.department_id%type; begin select department_id into lag from ask_employees where department_id=dno; return lag; end; PROCEDURE PR1(eno number)as begin insert into eem values(eno); dbms_output.PUT_LINE('Data Inserted'); end if; end; end; declare empno ask_employees.employee_id%type:=&no; depno ask_employees.department_id%type:=&deno; sag ask_employees.department_id%type; begin sag:=pack1.fun1(depno); if sag is null then dbms_output.PUT_LINE('not a valid department number'); else pack1.PR1(empno) end if; end; |
| can i use select statment in procdure if yes plzzzzzz give me ex. |
| Good one.. It will be more useful if add function explanation, procedure vs function and some more examples. :) |
|
Can you me Tell me How to alter Procedure In Oracle ? Cause i don't want to Replace it.. |
| what is shared sql and dyanamic sql? |
| all DDL cmds are internally commit cmds. There is no need to execute commit cmd after execution of DDL cmds. |
| hi prasan u didn't end u r pl/sql block |
|
hi ajay u can call the parameterised procedure exec procedurename(parameterlist); |
|
SQL> desc emp_test; Name Null? Type ----------------------------------------- -------- ---------------------------- EMPNO NUMBER(4) ENAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(2) SQL> CREATE OR REPLACE PROCEDURE pro_delete_rows ( 2 p_table_name IN VARCHAR2, 3 p_deleted_rows OUT NUMBER 4 ) 5 IS 6 BEGIN 7 EXECUTE IMMEDIATE 'delete from' || p_table_name; 8 9 p_deleted_rows := SQL%ROWCOUNT; 10 END ; 11 / Procedure created. SQL> variable deleted number SQL> execute pro_delete_rows('EMP_TEST',:deleted) BEGIN pro_delete_rows('EMP_TEST',:deleted); END; * ERROR at line 1: ORA-00942: table or view does not exist ORA-06512: at "SCOTT.PRO_DELETE_ROWS", line 7 ORA-06512: at line 1 SQL> print deleted DELETED ---------- |
|
i created one procedure and execute result will come. i want change in procedure, how will i get procedure view and edit? |
| How can we see the all database in Oracle? |
|
hi Ambuj Delete is a DML command while truncate is DDL command Delete Command Can be Rollback while Truncate command cannot be rollback Delete command delete the whole record but not free the table space while truncate free the table storage place. |
|
How to schedule a procedure? |
| How to create procedure and funtions at a time |
|
Generally DDL Statements are those whose implementation affects the strcuture of database objects like Create, Alter, Drop. But TRUNCATE only operates on Data Values i.e. Tuples will be unconditionally deleted permanently from a Table. Then how TRUNCATE can be DDL statement? can I get some logical explanantion on it from those who have written that TRUNCATE is a DDL Statement? Regards to all. |
