Recent Posts

Monday, 25 July 2016

Sub Programs in PL/SQL


1. Sub program is a part of the program which is used for code re usability.
2. Sub programs have two names those are procedures and functions.
3. 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.
1. Declaration of the procedure
2. 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;

Next Tutorial  Functions in PL/SQL

Previous Tutorial  Cursors Tutorial

No comments:

Post a Comment