- Sub program is a part of the program which is used for code re usability.
- Sub programs have two names those are procedures and functions.
- The main difference between the procedure and the function is procedure does not return any value but function returns one value.
PL/SQL Procedures are two parts.
- Declaration of the procedure
- Body of the procedure
Declaration of the procedure always start with a keyword called create and ends with last variable parameter. Whereas the body of the procedure starts with a keyword called as Is and ends with End statement.
In PL/SQL sub program has three modes
1. IN MODE
It is a default mode which is used to read the variables form the end user.
2. OUT MODE
This mode is used for writing purpose it sends the value to the end user.
This mode is used for both reading and writing purpose it accepts and sends the values to the end user.
Example for Procedure
create or replace procedure k (a in number, b out varchar, c out number, d out number) as begin select ename,sal,deptno into b,c,d from emp where empno=a; end; Procedure created. SQL> var x varchar2(15) SQL> var y number SQL> var z number SQL> exec k(7788,:x,:y,:z) PL/SQL procedure successfully completed. SQL> print x X ------------ RAJU SQL> print y Y ---------- 5699 SQL> print z Z ---------- 10 SQL> print X ---------- RAJU Y ---------- 5699 Z ---------- 10
Procedures with Cursors
create or replace procedure p2(p_deptno in emp.deptno%type) is p_empno emp.empno%type; p_ename emp.ename%type; p_sal emp.sal%type; cursor c is select empno,ename, sal from emp where deptno=p_deptno; begin open c; loop fetch c into p_empno,p_ename,p_sal; if c% found then dbms_output.put_line(p_empno); dbms_output.put_line(p_ename); dbms_output.put_line(p_sal); else exit; end if; end loop; end;