Oracle PL/SQL - PL SQL Function Procedure Package OUT Parameters

Introduction

An OUT parameter returns a value to the main program.

It allows you to return more than one variable from the subprogram.

The actual parameter is always a variable.

The actual parameter works as a placeholder and waits for a subprogram to return something.

The formal parameter is also a variable initialized with NULL that could be used in any normal operation.

In the example, variables v_hour_nr and v_min_nr are actual parameters.

The default values will be overridden when the procedure is executed.

From the logical side, o_hour_nr and o_min_nr are formal parameters that serve as variables and are initialized with NULL values.

Demo

SQL>
SQL>-- from  w  w  w.ja v  a  2  s . c om
SQL> declare
  2    v_hour_nr NUMBER:=12;
  3    v_min_nr  NUMBER:=20;
  4    procedure p_split (i_date_dt DATE,
  5        o_hour_nr OUT NUMBER, o_min_nr OUT NUMBER)
  6    is
  7    begin
  8      DBMS_OUTPUT.put_line(o_hour_nr ||'/'||o_min_nr);
  9      o_hour_nr:=to_NUMBER(to_char(i_date_dt,'hh24'));
 10      o_min_nr :=TO_CHAR(i_date_dt,'mi');
 11      DBMS_OUTPUT.put_line(o_hour_nr ||'/'||o_min_nr);
 12    end;
 13  begin
 14      p_split(sysdate, v_hour_nr, v_min_nr);
 15      DBMS_OUTPUT.put_line (
 16         'Total minutes:'||(v_hour_nr*60+v_min_nr));
 17  end;
 18  /
/
16/42
Total minutes:1002

PL/SQL procedure successfully completed.

SQL>