Use "IN OUT" Parameters in Oracle

Q

How To Use "IN OUT" Parameter Properly in Oracle?

✍: FYIcenter.com

A

Here are the rules about IN OUT parameters:

  • A formal IN OUT parameter acts like an initialized variable.
  • An actual IN OUT parameter must be a variable.
  • An actual IN OUT parameter passes a copy of its value to the formal parameter when entering the procedure or function.
  • An actual IN OUT parameter will receive a copy of the value from the formal parameter at the end of the procedure or function.

Here is good example of a procedure with IN OUT parameters:

SQL> CREATE OR REPLACE PROCEDURE SWAP_TEST AS
  2    A NUMBER := 3;
  3    B NUMBER := 8;
  4    PROCEDURE MY_SWAP(X IN OUT NUMBER,Y IN OUT NUMBER) AS
  5      T NUMBER;
  6    BEGIN
  7      T := X;
  8      X := Y;
  9      Y := T;
 10    END MY_SWAP;
 11  BEGIN     
 12    MY_SWAP(A,B);
 13    DBMS_OUTPUT.PUT_LINE('A = ' || TO_CHAR(A));
 14    DBMS_OUTPUT.PUT_LINE('B = ' || TO_CHAR(B));
 15  END;
 16  /

SQL> EXECUTE SWAP_TEST;
A = 8
B = 3

 

Creating Oracle PL/SQL Procedures and Functions

⇒⇒Oracle Database Tutorials

2016-10-17, 142👍, 0💬