Oracle PL/SQL Tutorial/Function Procedure Packages/Out Parameters

Calling myProc

SQL>
SQL> set serveroutput on
SQL>
SQL> CREATE OR REPLACE PROCEDURE myProc(ip_line_length IN NUMBER,ip_separator IN VARCHAR2,op_line OUT VARCHAR2)
  2  IS
  3    myString VARCHAR2(150);
  4  BEGIN
  5
  6    FOR idx in 1..ip_line_length LOOP
  7
  8      myString :=myString ||ip_separator;
  9
 10    END LOOP;
 11
 12    op_line :=myString;
 13
 14  EXCEPTION WHEN OTHERS THEN
 15
 16    dbms_output.put_line(SQLERRM);
 17
 18    op_line :=null;
 19  END;
 20  /
Procedure created.
SQL>
SQL>
SQL> DECLARE
  2    v_length NUMBER :=50;
  3
  4    v_separator VARCHAR2(1):="=";
  5
  6    v_line VARCHAR2(150);
  7  BEGIN
  8
  9    myProc(v_length,v_separator,v_line);
 10
 11    dbms_output.put_line(v_line);
 12  END;
 13  /

IN OUT difference

SQL> create or replace package PARAMETERS as
  2  FUNCTION in_out_inout(
  3  aiv_in                         in     varchar2,
  4  aov_out                           out varchar2,
  5  aiov_inout                     in out varchar2)
  6  return                                varchar2;
  7  PROCEDURE in_out_inout(
  8  aiv_in                         in     varchar2,
  9  aov_out                           out varchar2,
 10  aiov_inout                     in out varchar2);
 11  end PARAMETERS;
 12  /
Package created.
SQL>
SQL>
SQL> create or replace package body PARAMETERS as
  2  FUNCTION in_out_inout(
  3  aiv_in                         in     varchar2,
  4  aov_out                           out varchar2,
  5  aiov_inout                     in out varchar2)
  6  return                                varchar2 is
  7  begin
  8
  9    dbms_output.put_line(aiv_in);
 10    dbms_output.put_line(aov_out);
 11    dbms_output.put_line(aiov_inout);
 12    aov_out   := "OUT";
 13    aiov_inout := aiv_in;
 14    aiov_inout := aiov_inout||"OUT";
 15
 16    dbms_output.put_line(aiv_in);
 17    dbms_output.put_line(aov_out);
 18    dbms_output.put_line(aiov_inout);
 19    return "OK";
 20
 21  end in_out_inout;
 22
 23
 24  PROCEDURE in_out_inout(
 25  aiv_in                         in     varchar2,
 26  aov_out                           out varchar2,
 27  aiov_inout                     in out varchar2) is
 28  begin
 29
 30    dbms_output.put_line(aiv_in);
 31    dbms_output.put_line(aov_out);
 32    dbms_output.put_line(aiov_inout);
 33    aov_out   := "OUT";
 34    aiov_inout := aiv_in;
 35    aiov_inout := aiov_inout||"OUT";
 36
 37    dbms_output.put_line(aiv_in);
 38    dbms_output.put_line(aov_out);
 39    dbms_output.put_line(aiov_inout);
 40  end in_out_inout;
 41  end PARAMETERS;
 42  /
Package body created.
SQL>
SQL>
SQL>
SQL> declare
  2      v_in                         varchar2(30) := "IN";
  3      v_out                        varchar2(30) := "AAA";
  4      v_inout                      varchar2(30) := "BBB";
  5      v_return                     varchar2(30);
  6  begin
  7
  8    dbms_output.put_line("Inside test unit parameters v_in    = "||v_in);
  9    dbms_output.put_line("Inside test unit parameters v_out   = "||v_out);
 10    dbms_output.put_line("Inside test unit parameters v_inout = "||v_inout);
 11    dbms_output.put_line("Test function PARAMETERS.in_out_inout(v_in, v_out, v_inout).");
 12
 13    v_return := PARAMETERS.in_out_inout(v_in, v_out, v_inout);
 14
 15    dbms_output.put_line(v_return);
 16    dbms_output.put_line("Inside test unit parameters v_in    = "||v_in);
 17    dbms_output.put_line("Inside test unit parameters v_out   = "||v_out);
 18    dbms_output.put_line("Inside test unit parameters v_inout = "||v_inout);
 19    dbms_output.put_line("Resetting initial values...");
 20
 21    v_out   := "AAA";
 22    v_inout := "BBB";
 23
 24    dbms_output.put_line("Before calling the procedure...");
 25    dbms_output.put_line("Inside test unit parameters v_in    = "||v_in);
 26    dbms_output.put_line("Inside test unit parameters v_out   = "||v_out);
 27    dbms_output.put_line("Inside test unit parameters v_inout = "||v_inout);
 28    dbms_output.put_line("Test procedure PARAMETERS.in_out_inout(v_in, v_out, v_inout).");
 29
 30    PARAMETERS.in_out_inout(v_in, v_out, v_inout);
 31
 32    dbms_output.put_line("Inside test unit parameters v_in    = "||v_in);
 33    dbms_output.put_line("Inside test unit parameters v_out   = "||v_out);
 34    dbms_output.put_line("Inside test unit parameters v_inout = "||v_inout);
 35  end;
 36  /
Inside test unit parameters v_in    = IN
Inside test unit parameters v_out   = AAA
Inside test unit parameters v_inout = BBB
Test function PARAMETERS.in_out_inout(v_in, v_out, v_inout).
IN
BBB
IN
OUT
INOUT
OK
Inside test unit parameters v_in    = IN
Inside test unit parameters v_out   = OUT
Inside test unit parameters v_inout = INOUT
Resetting initial values...
Before calling the procedure...
Inside test unit parameters v_in    = IN
Inside test unit parameters v_out   = AAA
Inside test unit parameters v_inout = BBB
Test procedure PARAMETERS.in_out_inout(v_in, v_out, v_inout).
IN
BBB
IN
OUT
INOUT
Inside test unit parameters v_in    = IN
Inside test unit parameters v_out   = OUT
Inside test unit parameters v_inout = INOUT
PL/SQL procedure successfully completed.
SQL>

Out parameter

SQL> CREATE  PROCEDURE changePrice (old_price NUMBER,percent_update NUMBER := 5,new_price OUT NUMBER)
  2  IS
  3  BEGIN
  4          new_price := old_price + old_price * percent_update / 100;
  5  END changePrice;
  6  /
Procedure created.
SQL>
SQL> set serveroutput on
SQL> DECLARE
  2          price_to_update NUMBER(6,2) := 20;
  3          updated_price NUMBER(6,2) := 0;
  4  BEGIN
  5          dbms_output.put_line("price before " || price_to_update);
  6          dbms_output.put_line("updated_price before " || updated_price);
  7          changePrice (old_price => price_to_update, new_price => updated_price);
  8          dbms_output.put_line("price_to_update after update " || price_to_update);
  9          dbms_output.put_line("updated_price after update " || updated_price);
 10  END;
 11  /
price before 20
updated_price before 0
price_to_update after update 20
updated_price after update 21
PL/SQL procedure successfully completed.
SQL>
SQL>

OUT Parameters

An OUT parameter returns a value to the main program.

An OUT parameter allows you to return more than one variable from the subprogram.

The actual parameter is a variable.

SQL> create or replace procedure p_split (i_date_dt DATE,o_hour OUT NUMBER, o_min OUT NUMBER)
  2  is
  3  begin
  4      DBMS_OUTPUT.put_line(o_hour||"/"||o_min);
  5      o_hour:= to_NUMBER(to_char(i_date_dt,"hh24"));
  6      o_min := TO_CHAR(i_date_dt,"mi");
  7      DBMS_OUTPUT.put_line(o_hour||"/"||o_min);
  8  end;
  9  /
Procedure created.
SQL>
SQL> declare
  2    v_hour NUMBER:=12;
  3    v_min  NUMBER:=20;
  4  begin
  5      p_split(sysdate, v_hour, v_min);
  6      DBMS_OUTPUT.put_line ("Total minutes:"||(v_hour*60+v_min));
  7  end;
  8  /
/
21/27
Total minutes:1287
PL/SQL procedure successfully completed.
SQL>