Thread: Simple example of Oracle Stored Procedure

Please give me a simple example of oracle stored procedure with in out parameter and out parameter and how to execute it?

create or replace procedure display
(
eno in emp.empno%type,
name out emp.ename%type,
job out emp.job%type,
salary out emp.sal%type,
location out dept.loc%type
)
is
begin
select ename,job,sal,loc into name,job,salary,location from emp e,dept d
where e.deptno=d.deptno AND empno=eno;
end;


NOTE:----If a procedure contains any OUT or IN OUT parameter, it can't be executed from SQL prompt it must be called from with in an anonymous block.

To execute the above procedure.

declare
name emp.ename%type;
job emp.job%type;
salary emp.sal%type;
location dept.loc%type;
begin
display(7839,name,job,salary,location);
dbms_output.put_line(name||' '||job||' '||salary||' '||location);
end;

Sample example of procedure with in out mode ============================================

Code:

 
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
==================================================

Code:

 
declare 
x number; 
begin x:=&values; 
fact(x); 
dbms_output.put_line(x); 
end;