Home >> FAQs/Tutorials >> Oracle DBA FAQ >> Index

Oracle DBA FAQ - Creating Your Own PL/SQL Procedures and Functions

By: FYIcenter.com

Part:   1  2  3  4  5  6   7 

(Continued from previous part...)

How To Use "IN OUT" Parameter Properly?

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

How To Define Default Values for Formal Parameters?

If you have an IN parameter, you can make it as an optional parameter for the calling statement by defining the formal parameter with the DEFAULT clause. This gives you the freedom of not providing the actual parameter when calling this procedure or function. See the following tutorial script shows you an example procedure with an optional parameter:

SQL> CREATE OR REPLACE PROCEDURE WELCOME AS
  2    PROCEDURE GREETING(S IN CHAR DEFAULT 'FYICenter') AS
  3    BEGIN
  4      DBMS_OUTPUT.PUT_LINE('Welcome to ' || S);
  5    END;
  6  BEGIN        
  7    GREETING('MySpace.com');
  8    GREETING;
  9  END;
 10  /     
Procedure created.

SQL> EXECUTE WELCOME;
Welcome to MySpace.com
Welcome to FYICenter

What Are Named Parameters?

Named parameters are actual parameters specified not by position but by providing formal parameter names when calling the procedure or function. The main advantage of named parameters is that the caller don't have to remember the position of each parameter. But the caller have to remember the formal parameter names. The script below illustrates how to use named 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(Y=>B, X=>A); -- same as (X=>A, Y=B), OR (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

(Continued on next part...)

Part:   1  2  3  4  5  6   7