-
Copyrights 2010 - Online Training Oracle Applications. All Rights Reserved.
|
PL/ SQL:
A Procedure is a named PL/SQL block which is compiled and stored in the database for repeated execution. Basic Syntax : Create or replace procedure <procedure_name> Ex 1: Procedure created. To execute the procedure: SQL> Exec p1 A procedure can have three types of parameters. In Parameters are used to accept values from the user. Ex 2: create or replace procedure add_num ( a IN number, Procedure created. To execute the procedure:
Ex 3: Create a Procedure which accepts an empno and increments his salary by 1000. create or replace procedure inc_sal ( a in number) Procedure created. TO execute the procedure: SQL> exec inc_sal(7900) We can improve the above procedure code by using %type attribute in procedure parameters. The above procedure can be re-written as below : create or replace procedure inc_sal ( a in emp.empno%type) Ex 4: create or replace procedure display_emp ( l_empno emp.empno%type) l_ename emp.ename%type; select ename, sal into l_ename,l_sal from emp dbms_output.put_line(l_ename||'....'||l_sal); exception Ex 5: create or replace procedure display_emp1 (l_deptno emp.deptno%type) begin for emp_rec in c1 loop end; Ex 6: create or replace procedure demo1 create or replace procedure demo2 dbms_output.put_line ('Thank you'); SQL> Exec demo2 Ex 7: begin Ex 8: create or replace procedure add_num ( a IN number, Procedure is created with compilation errrors. SQL> sho err We get error information. Ex 9: create or replace procedure test begin In the above example procedure sample is called as Sub procedure. A Sub procedure can be invoked from the main procedure only. SQL> EXEC test We cannot invoke the Sub procedure independently. SQL>EXEC sample Ex 10: create a procedure which accepts empno and return salary. create or replace procedure ret_sal( l_empno in emp.empno%type, select sal into l_sal from emp end; As the procedure is returning a value using OUT parameter, Step 1: Create bind variable Step 1: creating Bind variable SQL> variable g_sal number Step 2: Invoking the procedure using bind variable SQL> Exec ret_sal( 7900, :g_sal) Step 3: Print the value in the bind variable SQL> Print g_sal Ex 11: IN OUT parameters are used to accept the value as well as return the values to the calling environment. Create a procedure which accepts a number and return its square. create or replace procedure cal_square( a In OUT number) To run the above proceure we need to follow a four step process. Step 1: Create Bind variable Step 1: Step 2: begin Step 3: SQL> Exec cal_square (:n) Step 4: Ex 12: SQL> select object_name from user_objects where or SQL> select procedure_name from user_procedures.
Ex 13: create or replace procedure add_num3( a number, Procedure created. To execute the procedure SQL> EXEC add_num3(10,20,30) SQL> Exec add_num3(10,20) Note: Default value is considered if we do not pass any value. SQL> You need to use arrow operator if you pass values to specific parameters Ex: SQL> Exec add_num3(a=>10, c =>20) ex 14: We need to rectify the errors and recreate the procedure sucessfully. Ex 15: select text from user_source TO drop a procedure: Ex:
|