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

Home >> FAQs/Tutorials >> Oracle DBA FAQ

By: FYIcenter.com

Part: 1 2 3 4 5 6 7

(Continued from previous part...)

What Is the Order of Defining Local Variables and Sub Procedures/Functions?

In the declaration part, you must define all local variables before defining any sub procedures or sub functions. See the following sample script:

SQL> CREATE OR REPLACE PROCEDURE WELCOME AS
  2    SITE CHAR(80) := 'FYICenter';
  3    PROCEDURE WELCOME_PRINT(S CHAR) AS
  4    BEGIN
  5      DBMS_OUTPUT.PUT_LINE('Welcome to ' || S);
  6    END;
  7  BEGIN
  8    WELCOME_PRINT(SITE);
  9  END;
 10  /

SQL> EXECUTE WELCOME;
Welcome to FYICenter

Notice that variable SITE should be declared before procedure WELCOME_PRINT

What Is the Difference between Formal Parameters and Actual Parameters?

Formal parameter and actual parameter are two different terms related parameters used in the procedures and functions:

A formal parameter is a term used to refer to a parameter defined in the procedure or function declaration statement.
An actual parameter is a term used to refer to a parameter provided by the calling statement to a procedure or a function.

What Are the Parameter Modes Supported by PL/SQL?

PL/SQL supports 3 parameter modes on procedure/function parameters:

IN: This is the default mode. IN parameters allow the calling code to pass values into the procedure or function.
OUT: OUT parameters allow the procedure or function to pass values back to the calling code.
IN OUT: IN OUT parameters allow the calling code to pass values into and receive values from procedure or function.

How To Use "IN" Parameter Properly?

Here are the rules about IN parameters:

A formal IN parameter acts like constant. It can not be assigned with new values.
An actual IN parameter can take a value or a variable.
An actual IN parameter is passed by reference to the specified value or the value of the specified variable.
An actual IN parameter will not receive any value from the formal parameter.

Here is good example of a procedure with an IN parameter:

SQL> CREATE OR REPLACE PROCEDURE WELCOME AS
  2    SITE CHAR(80) := 'FYICenter.com';
  3    PROCEDURE WELCOME_PRINT(S IN CHAR) AS
  4    BEGIN
  5      DBMS_OUTPUT.PUT_LINE('Welcome to ' || S);
  6      -- S := 'Google.com'; -- Not allowed
  7    END;
  8  BEGIN          
  9    WELCOME_PRINT('MySpace.com');
 10    WELCOME_PRINT(SITE);
 11  END;            
 12  /

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

How To Use "OUT" Parameter Properly?

Here are the rules about OUT parameters:

A formal OUT parameter acts like an un-initialized variable. It must be assigned with new values before the end of the procedure or function.
An actual OUT parameter must be a variable.
An actual OUT parameter will not pass any value to the formal parameter.
An actual 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 an OUT parameter:

SQL> CREATE OR REPLACE PROCEDURE WELCOME AS
  2    SITE CHAR(40) := 'FYICenter.com';
  3    MESSAGE CHAR(80);
  4    PROCEDURE WELCOME_PRINT(S IN CHAR, M OUT CHAR) AS
  5    BEGIN
  6      M := 'Welcome to ' || S;
  7    END;
  8  BEGIN
  9    WELCOME_PRINT('MySpace.com', MESSAGE);
 10    DBMS_OUTPUT.PUT_LINE(MESSAGE);
 11    WELCOME_PRINT(SITE, MESSAGE);
 12    DBMS_OUTPUT.PUT_LINE(MESSAGE);
 13  END;
 14  /

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

(Continued on next part...)

Part: 1 2 3 4 5 6 7

More...