Home >> FAQs/Tutorials >> Oracle DBA FAQ

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

By: FYIcenter.com

Part:   1   2  3  4  5  6  7 

A collection of 26 FAQs on PL/SQL managing our own procedures. It can also be used as learning tutorials on creating procedures and functions, executing and dropping procedures, passing actual parameters to formal parameters, making optional parameters. Topics included in this FAQ are:

1. What Is a Procedure?
2. What Is a Function?
3. How To Define an Anonymous Procedure without Variables?
4. How To Define an Anonymous Procedure with Variables?
5. How To Create a Stored Procedure?
6. How To Execute a Stored Procedure?
7. How To Drop a Stored Procedure?
8. How To Pass Parameters to Procedures?
9. How To Create a Stored Function?
10. How To Call a Stored Function?
11. How To Drop a Stored Function?
12. How To Call a Stored Function with Parameters?
13. How To Define a Sub Procedure?
14. How To Call a Sub Procedure?
15. How To Define a Sub Function?
16. Can Sub Procedure/Function Be Called Recursively?
17. What Happens If Recursive Calls Get Out of Control?
18. What Is the Order of Defining Local Variables and Sub Procedures/Functions?
19. What Is the Difference between Formal Parameters and Actual Parameters?
20. What Are the Parameter Modes Supported by PL/SQL?
21. How To Use "IN" Parameter Properly?
22. How To Use "OUT" Parameter Properly?
23. How To Use "IN OUT" Parameter Properly?
24. How To Define Default Values for Formal Parameters?
25. What Are Named Parameters?
26. What Is the Scope of a Local Variable?

Sample scripts used in this FAQ collection assumes that you are connected to the server with the HR user account on the default database instance XE. See other FAQ collections on how to connect to the server. It is also assumed that you are using SQL*Plus to practice the sample scripts.

What Is a Procedure?

A procedure is a named program unit. It consists of three parts:

• Declaration Part - Defining the procedure name, calling parameters, local variables and local procedures. Declaration part is required.
• Execution Part - Defining execution logic with executable statements. Execution part is required.
• Exception Part - Defining error handling logics. Exception part is optional.

Here how a complete procedure should look like:

PROCEDURE name (parameter_1, parameter_2) AS
  -- Declaration statements
BEGIN
  -- Executable statements
EXCEPTION
  -- Error handling statements
END;

What Is a Function?

A function is a named program unit. It consists of three parts:

• Declaration Part - Defining the function name, calling parameters, return value type, local variables and local procedures. Declaration part is required.
• Execution Part - Defining execution logic with executable statements. Execution part is required.
• Exception Part - Defining error handling logics. Exception part is optional.

Here how a complete procedure should look like:

FUNCTION name (parameter_1, parameter_2) RETURN type AS
  -- Declaration statements
BEGIN
  -- Executable statements
  RETURN value;
EXCEPTION
  -- Error handling statements
END;

How To Define an Anonymous Procedure without Variables?

Anonymous procedure is a procedure without any name. If you don't have any variables to declare, you can define an anonymous procedure by using the BEGIN keyword directly in SQL*Plus as shown in the following tutorial script:

SQL> set serveroutput on;

SQL> begin 
  2    dbms_output.put_line('Welcome to FYICenter!'); 
  3  end;
  4  /
Welcome to FYICenter!

PL/SQL procedure successfully completed.

"set serveroutput on;" allows dbms_output.put_line() to work.

"/" runs the anonymous block, which print the "Welcome to FYICenter!" message.

How To Define an Anonymous Procedure with Variables?

Anonymous procedure is a procedure without any name. If you have some variables to declare, you can define an anonymous procedure by using the DECLARE keyword in SQL*Plus as shown in the following tutorial script:

SQL> set serveroutput on;

SQL> declare
  2    site char(80) := 'FYICenter';
  3  begin 
  4    dbms_output.put_line('Welcome to ' || site); 
  5  end;
  6  /
Welcome to FYICenter

PL/SQL procedure successfully completed.

SQL>  /
Welcome to FYICenter

PL/SQL procedure successfully completed.

"/" runs the anonymous block, which print the "Welcome to FYICenter" message.

(Continued on next part...)

Part:   1   2  3  4  5  6  7