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