Procedure can created with 3 types of parameters

1. IN
2. OUT
3. IN OUT

IN : It is used to pass values to the procedure at run time (it gets the value inside the procedure_

Syntax: create or replace procedure procedure_name (parameter1 data type, parameter2 data type) is
Note: In is default, hence no need to mention

Example1:

SQL> ed proc_in1.sql

     create or replace procedure add2nos(a number, b number) is
     c number;
     begin
         c:=a+b;
     dbms_output.put_line ('Sum is '||c);
  end;

SQL> @ filename.sql
Procedure created

SQL> execute add2nos (10,20);
Sum is 30

---

Example2: create a procedure to get vendor_id as parameter then show the purchase information

SQL> ed proc_in2.sql

create or replace procedure purchase_proc(p_vendor_id number) is

cursor purchase_cur is select * from purchase where vendor_id = p_vendor_id;
begin
for purchase_rec in purchase_cur
loop
dbms_output.put_line (purchase_rec.pono||’ ‘||purchase_rec.item_id||purchase_rec.qty);
end loop;
end;

SQL> select * from purchase;

 PO_NO    ITEM_ID    QUNTITY      PRICE  VENDOR_ID

---

   771       8003         10      10300        101
   772       8003         11      20300        101
   773       8003         12       1500        101
   774       8002      10000       1400        102
   775       8004          6       9300        103
   776       8005         12        405        105
   777       8002         10       1800        102
   778       8005         15       2300        103
   780       8001         10        404        104

9 rows selected.

SQL> ed proc_in2.sql

SQL> @ proc_in2.sql
10 /

Procedure created.

SQL> EXECUTE PURCHASE_PROC(101);
771 800310
772 800311
773 800312

exmaple 3: Calling another procedure inside the procedure

SQL> ed proc_in3.sql

create or replace procedure purchase_proc(p_vendor_id number) is

cursor purchase_cur is select * from purchase where vendor_id = p_vendor_id;

begin

dbms_output.put_line (‘Showing all Supplier orders’);
dbms_output.put_line (‘—————————-‘);

SUPPLIER_PROC; — Calling supplier_proc to show all supplier;

dbms_output.put_line (‘Showing purchase orders belongs to ‘||p_vendor_id);
dbms_output.put_line (‘————————————————–‘);
for purchase_rec in purchase_cur
loop
dbms_output.put_line (purchase_rec.po_no||’ ‘||purchase_rec.item_id||purchase_rec.quntity);
end loop;
end;

SQL> @ Filename.sql
procedure created

SQL> execute purchase_proc(101);

Showing all Suppliers

101 dell
102 hcl
103 hp
104 lenovo
105 nokiya
106 IBM
107 Samsung
110 Htc
111 Philips

Showing purchase orders belongs to 101

771 800310
772 800311
773 800312

PL/SQL procedure successfully completed.

OUT : It is used to return a value from the procedure / When the procedure is called we will get the value from the procedure

Syntax: create or replace procedure procedur_name (parameter out data type) is

Example:

SQ> ed proc_out1.sql

create or replace procedure sum2nos(a number, b number,c out number) is
begin
c:=a+b;
end;

SQL> @ proc_out1.sql
procedure created

— Call above procedue

SQL ed test_out.sql

DECLARE
a number:=20;
b number:=30;
c number;
begin
sum2nos(a,b,c); — while called1 20,30 are passed to a,b in the procedure, and procedure will return sum of 10,20 to c
dbms_output.put_line (‘sum is ‘||c);
end;

SQL> @ test_out.sql
Sum is 50

=====

CREATE OR REPLACE PROCEDURE EMPSAL_PROC (P_SAL NUMBER, P_ANSAL OUT NUMBER, P_TAX OUT NUMBER) IS
BEGIN
P_ANSAL :=P_SAL12; IF P_ANSAL< 100000 THEN P_TAX := 2 P_ANSAL/100;
ELSE
P_TAX := 5* P_ANSAL / 100;
END IF;
END;

Call above procedure by passing monthly salary and get ansal and tax

SQL> ed emp_Data.sql

declare
cursor emp_cur is select * from emp_new;
asal number;
tax number;
begin
for emp_rec in emp_cur
loop
EMPSAL_PROC(emp_rec.sal,asal,tax);
dbms_output.put_line (emp_rec.sal||’ ‘||asal|| ‘ ‘||tax);
end loop;
end;

SQL> @ emp_data.sql
12 /
25500 306000 15300
12200 146400 7320
12000 144000 7200
25000 300000 15000
30000 360000 18000
56500 678000 33900

IN OUT : It is used to get the vlaue inside the procedure and send the value from the procedure.
Single parameter can be used for in and out

sql> ED EMP_PROC.SQL

create or replace procedure sal_hike (psal in out number) is
h number;
begin
h:= psal*10/100;
psal := psal + h;

end;

sql> @ EMP_PROC.SQL
PROCEDURE CREATED

In the above procedure psal will get the old sal and returns the new sal after calculations

sql> ED HIKE.SQL
declare
cursor emp_cur is select * from emp_new;
asal number;
tax number;
begin
for emp_rec in emp_cur
loop
dbms_output.put_line (‘Eno = ‘||Emp_rec.eno||’ ‘||’Old Sal = ‘||emp_rec.sal);
sal_hike(emp_rec.sal);
dbms_output.put_line (‘Eno = ‘||Emp_rec.eno||’ ‘||’New Sal = ‘||emp_rec.sal);
end loop;
end;

SQL> @ hike.sql
13 /
Eno = 115 Old Sal = 25500
Eno = 115 New Sal = 28050
Eno = 105 Old Sal = 12200
Eno = 105 New Sal = 13420
Eno = 111 Old Sal = 12000
Eno = 111 New Sal = 13200
Eno = 112 Old Sal = 25000
Eno = 112 New Sal = 27500
Eno = 113 Old Sal = 30000
Eno = 113 New Sal = 33000
Eno = 114 Old Sal = 56500
Eno = 114 New Sal = 62150

Share this post: on Twitter on Facebook on Google+