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...)

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