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
A NOCOPY hints can be added to improve the performance
ReplyDelete(create or replace procedure ORDERDETAILS(orno in varchar2,b in out NOCOPY varchar2)
Using the NOCOPY hint tells the compiler to use pass by reference,
so no temporary buffer is needed and no copy forward and copy back operations happen.
Instead, any modification to the parameter values are written directly to the parameter variable .