Oracle PL/SQL Tutorial/Function Procedure Packages/IN OUT Parameters

Demonstrate the behavior of IN, OUT, and IN OUT parameter modes.

   <source lang="sql">

SQL> SQL> set serveroutput on format wrapped SQL> SQL> CREATE OR REPLACE PROCEDURE ModeIn (p_In IN NUMBER) AS

 2     v_LocalVariable NUMBER := 0;
 3   BEGIN
 4     DBMS_OUTPUT.PUT("Inside ModeIn: ");
 5     IF (p_In IS NULL) THEN
 6       DBMS_OUTPUT.PUT_LINE("p_In is NULL");
 7     ELSE
 8       DBMS_OUTPUT.PUT_LINE("p_In = " || p_In);
 9     END IF;
10
11     v_LocalVariable := p_In;
12
13     IF (p_In IS NULL) THEN
14        DBMS_OUTPUT.PUT_LINE("p_In is NULL");
15     ELSE
16       DBMS_OUTPUT.PUT_LINE("p_In = " || p_In);
17     END IF;
18   END ModeIn;
19   /

Procedure created. SQL> SQL> SQL> show errors No errors. SQL> SQL> SQL> SQL> DECLARE

 2     v_In NUMBER := 1;
 3   BEGIN
 4     DBMS_OUTPUT.PUT_LINE("Before calling ModeIn, v_In = " || v_In);
 5     ModeIn(v_In);
 6     DBMS_OUTPUT.PUT_LINE("After calling ModeIn, v_In = " || v_In);
 7   END;
 8   /

Before calling ModeIn, v_In = 1 Inside ModeIn: p_In = 1 p_In = 1 After calling ModeIn, v_In = 1 PL/SQL procedure successfully completed. SQL> SQL> SQL> SQL></source>

IN OUT parameters

You can use IN OUT parameters for both input to and output from the subprogram.

If the actual parameter already had some value, the formal parameter is initialized with that value.

   <source lang="sql">

SQL> create or replace procedure p_split (i_date_dt DATE,

 2       o_hour IN OUT NUMBER, o_min IN OUT NUMBER)
 3  is
 4  begin
 5      DBMS_OUTPUT.put_line(o_hour||"/"||o_min);
 6      o_hour:=to_NUMBER(to_char(i_date_dt,"hh24"));
 7      o_min :=to_char(i_date_dt,"mi");
 8      DBMS_OUTPUT.put_line(o_hour||"/"||o_min);
 9  end;
10  /

Procedure created. SQL> 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 (
 7         "Total minutes:"||(v_hour*60+v_min));
 8  end;
 9  /

12/20 21/27 Total minutes:1287 PL/SQL procedure successfully completed. SQL></source>