Sub Programs

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.

3. IN/OUT

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;

Sub Programs