PL/SQL-PROCEDURES - 3

[debasisdas]

SAMPLE PROCEDURE USING REF-CURSOR
=============== =============== ======

create or replace procedure refex(mnum  number)
as
--ref cursor is declared.
type c1 is ref cursor;
mname varchar2(10);
mid number(4);
--vc1 is a variable of type c1 ,which is a ref cursor.
vc1 c1;
begin
--depending on the user input the procedure selects data dynamically from separate tables using ref cursor..
if mnum=1 then
open vc1 for select empno,ename from emp;
elsif mnum=2 then
open vc1 for select deptno,dname from dept;
end if;
loop
fetch vc1 into mid,mname;
exit when vc1%notfound;
dbms_output.put_line(mid ||' '|| mname);
end loop;
--close the ref cursor.
close vc1;
end;
 

SAMPLE EXAMPLE OF PROCEDURE WITH IN OUT MODE
=============== =============== ===============

--the same variable receive the value and returns the value after prcessing.
CREATE OR REPLACE procedure fact(a in out number)
is
b number:=1;
begin
for i in 1..a loop
b:=b*i;
end loop;
a:=b;
end;
 

TO EXECUTE THE PROCEDURE FROM AN ANONYMOUS BLOCK
=============== =============== =============== ======

declare
x number;
begin
x:=&values;
--x is the input variable
fact(x);
--same x is the output also.
dbms_output.put_line(x);
end;
 

Note :--since this procedure contains both IN and OUT mode parameter ,it can't be executed directly at SQL prompt using exec.

Sample Example Of Procedure With In ,out And In Out Mode
=============== =============== =============== ============

Create Or Replace Procedure Ioio
(
Num Emp.empno%type,
Name Out Emp.ename%type,
Num1 In Out Number
)
As
Begin
Select Ename,sal Into Name,num1 From Emp Where Empno=num And Mgr =num1;
 Dbms_output.put_line(name||'   '||num1);
End;
 

To Execute
===============

Declare
N Varchar2(10);
N1 Int :=7566;
Begin
Ioio(7788,n,n1);
End;
 

SAMPLE TO SHOW CALLING A PROCEDURE WITHIN ANOTHER PROCEDURE
=============== =============== =============== =====

create or replace procedure find(eno number, flag out boolean)
is
a number(2);
begin
--finds out the number of records in the table where empno = the user input.
select count(*) into a from emp where empno=eno;
if a=0 then
--if there is no such reacord already .set flag to FALSE.
flag:=FALSE;
else
--else to TRUE.
flag:=TRUE;
end if;
end;
 

THIS PROCEDURE CALLS THE PREVIOUS ONE
=============== =============== ========

create or replace procedure ins_emp(a number)
is
eno emp.empno%type;
fg boolean;
begin
--the input of this procedure is passed to the previous procedure.
eno:=a;
--abc is a label.
<<abc>>
--the previous procedure is called here.
find(eno,fg);
--depending on the existance of the record the previosu procedure set the flag variable to TRUE  or FALSE
if fg=true then
--if flag is true then display the message 
dbms_output.put_line(eno||'  No already exists...');
--and increment the input value by 1 
eno := eno+1;
--and go back to the label and keep trying in a loop till the value is not in the table.
goto abc;
else
--if flag is false insert the vale to the table.
insert into emp(empno) values(eno);
dbms_output.put_line('New empno inserted is : '||eno);
end if;
end;
 

Note :--since this procedure contains only IN mode parameter ,it can be executed directly at SQL prompt.