Friday 10 May 2013

How to Create a Procedure with both IN and OUT Parameter

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

1 comment:

  1. A NOCOPY hints can be added to improve the performance
    (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 .

    ReplyDelete

Related Posts Plugin for WordPress, Blogger...

ShareThis