In this post I will try to
explain how to create a procedure with both Input and output Parameter
Out parameter: The out
parameter mode is used to return values to the caller of the subprogram.
In parameter: The in parameter mode is used to pass values to
the subprogram.
First Let’s Create a table order_details
SQL> create table ORDER_DETAILS
2 (ordno varchar2(12),
3 itemcode varchar2(12),
4 qtyord number,
5 qtydeld number);
Table created.
SQL> INSERT INTO
ORDER_DETAILS
VALUES('1','ITEM4',300,300);
1 row created.
SQL> INSERT INTO
ORDER_DETAILS
VALUES('5','ITEM1',200,200);
1 row created.
SQL> INSERT INTO
ORDER_DETAILS
VALUES('7','ITEM1',300,200);
1 row created.
SQL> INSERT INTO
ORDER_DETAILS
VALUES('13','ITEM4', 20, 20);
1 row created.
SQL> INSERT INTO
ORDER_DETAILS
VALUES('31','ITEM2',300,300);
1 row created.
Below is the procedure which will
pass the input parameter into the procedure as well as output the result into
an initialized variable
SQL> ed
Wrote file afiedt.buf
1 create or replace procedure ORDERDETAILS(orno
in varchar2,b in out varchar2
)
2 is
3 qtyor number;
4 qtydel number;
5 code varchar2(5);
6 begin
7 select QTYORD,QTYDELD,ITEMCODE into qtyor,
qtydel, code from ORDER_DETAILS
where
ORDNO=orno;
8 if qtydel < qtyor then
9 b:= code;
10 dbms_output.put_line('You supply less than
what is ordered '||b);
11 elsif qtydel > qtyor then
12 b:= code;
13 dbms_output.put_line('You supply more than
what is ordered '||b);
14 else
15 b:=code;
16 dbms_output.put_line('NOTHING WRONG WITH THE
SUPPLY '||b);
17 end if;
18 exception
19 when no_data_found then
20 dbms_output.put_line('no data returned');
21 when TOO_MANY_ROWS then
22 dbms_output.put_line('MANY ITEMS WITH SAME
ITEMCODE');
23 when OTHERS then
24 dbms_output.put_line('SOME OTHER ERROR');
25* end
ORDERDETAILS;
26 /
Procedure created.
After successful compilation of
the procedure we can execute the same by passing the parameters
Executing the Procedure with
intialized variable:
SQL> set serveroutput on;
SQL>
DECLARE
2 var varchar2(10):='123';
3 begin
4 ORDERDETAILS('23',var);
5 end;
6 /
no data returned
PL/SQL procedure successfully completed.
SQL>
DECLARE
2 var varchar2(10):='232';
3 begin
4 ORDERDETAILS('7',var);
5 end;
6 /
You supply less than what is ordered ITEM1
PL/SQL procedure successfully completed.
Please let me know if you want to
know how to execute the same procedure using initialized variables
