Tutorials Oracle 9i Tutorial 13: Oracle 9i : Procedures and Functions Tutorial 13: Oracle 9i : Procedures and Functions - Page 2
Oracle 9i : Procedures and Functions
PL/SQL subprograms
A subprogram is a named block of PL/SQL. There are two types of subprograms in PL/SQL namely Procedures and Functions. Every subprogram will have a declarative part, an executable part or body, and an exception handling part, which is optional.
Declarative part contains variable declarations. Body of a subprogram contains executable statements of SQL and PL/SQL. Statements to handle exceptions are written in exception part.
When client executes a procedure are function, the processing is done in the server. This reduces network traffic. The subprograms are compiled and stored in the Oracle database as stored programs and can be invoked whenever required. As they are stored in compiled form when called they only need to be executed. Hence they save time needed for compilation.
Subprograms provide the following advantages
Procedures
Procedure is a subprogram used to perform a specific action. A procedure contains two parts specification and the body. Procedure specification begins with CREATE and ends with procedure name or parameters list. Procedures that do not take parameters are written without a parenthesis. The body of the procedure starts after the keyword IS or AS and ends with keyword END.
In the above given syntax things enclosed in between angular brackets (“< > “) are user defined and those enclosed in square brackets (“[ ]”) are optional.
OR REPLACE is used to overwrite the procedure with the same name if there is any.
AUTHID clause is used to decide whether the procedure should execute with invoker (current-user or person who executes it) or with definer (owner or person created) rights
Example
CREATE PROCEDURE MyProc (ENO NUMBER)
AUTHID DEFINER AS
BEGIN
DELETE FROM EMP
WHERE EMPNO= ENO;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(‘No employee with this number’);
END;
Let us assume that above procedure is created in SCOTT schema (SCOTT user area) and say is executed by user SEENU. It will delete rows from the table EMP owned by SCOTT, but not from the EMP owned by SEENU. It is possible to use a procedure owned by one user on tables owned by other users. It is possible by setting invoker-rights
AUTHID CURRENT_USER
PRAGMA AUTONOMOUS_TRANSACTION is used to instruct the compiler to treat the procedure as autonomous. i.e. commit or rollback the changes made by the procedure.
Parameter Modes
Parameters are used to pass the values to the procedure being called. There are 3 modes to be used with parameters based on their usage. IN, OUT, and IN OUT.
IN mode parameter used to pass the values to the called procedure. Inside the program IN parameter acts like a constant. i.e it cannot be modified.
OUT mode parameter allows you to return the value from the procedure. Inside Procedure the OUT parameter acts like an uninitialized variable. Therefore its value cannot be assigned to another variable.
IN OUT mode parameter allows you to both pass to and return values from the subprogram. Default mode of an argument is IN.
POSITIONAL vs. NOTATIONAL parameters
A procedure can be communicated by passing parameters to it. The parameters passed to a procedure may follow either positional notation or named notation.
Example
If a procedure is defined as GROSS (ESAL NUMBER, ECOM NUMBER)
If we call this procedure as GROSS (ESA, ECO) then parameters used are called positional parameters. For Notational Parameters we use the following syntax
GROSS (ECOM => ECO, ESAL => ESA)
A procedure can also be executed by invoking it as an executable statement as shown below.
BEGIN
PROC1; --- PROC1 is name of the procedure.
END;
/
Next Page: Tutorial 13: Oracle 9i : Procedures and Functions - Page 2
| [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. |
