8,127
====================================
Expand|Select|Wrap|Line Numbers
- 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;
=============================================
Expand|Select|Wrap|Line Numbers
- --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;
================================================== =
Expand|Select|Wrap|Line Numbers
- 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;
Sample Example Of Procedure With In ,out And In Out Mode
================================================== =======
Expand|Select|Wrap|Line Numbers
- 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;
===============
Expand|Select|Wrap|Line Numbers
- Declare
- N Varchar2(10);
- N1 Int :=7566;
- Begin
- Ioio(7788,n,n1);
- End;
==================================================
Expand|Select|Wrap|Line Numbers
- 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;
======================================
Expand|Select|Wrap|Line Numbers
- 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;
