Subprocedures and Oracle PL/SQL Subprograms

In this second part of a six-part series on Oracle PL/SQL subprograms, you will learn how Oracle implements subprograms, and how to create a stored procedure. This article is excerpted from chapter 36 of the book Beginning PHP and Oracle: From Novice to Professional, written by W. Jason Gilmore and Bob Bryla (Apress; ISBN: 1590597702).

How Oracle Implements Subprograms

Although the term stored procedures is commonly bandied about, Oracle actually implements three procedural variants, which are collectively referred to as subprograms:

  1. Stored procedures: Stored procedures support execution of SQL statements such as SELECT , INSERT , UPDATE , and DELETE . They also can set parameters that can be referenced later from outside of the procedure.
  2. Stored functions: Stored functions support execution only of the SELECT statement, accept only input parameters, and must return one and only one value. Furthermore, you can invoke a stored function directly into a SQL command just like you might do with standard Oracle functions such as COUNT() and TO_DATE()
     
  3. Anonymous blocks: Anonymous blocks are much like stored procedures and functions except that they cannot be stored in the database and referenced directly because they are, as the name implies, anonymous. They do not have a name or parameters; you either run them in the SQL Commands or SQL Developer GUI application, or you can embed them within a stored procedure or function to isolate functionality.

Generally speaking, you use subprograms when you need to work with data found in the database, perhaps to retrieve rows or insert, update, and delete values; whereas you use stored functions to manipulate that data or perform special calculations. In fact, the syntax presented throughout this chapter is practically identical for both variations, except that the term procedure is swapped out for function. For example, the command DROP PROCEDURE procedure_name   is used to delete an existing stored procedure, while DROP FUNCTION function_name  is used to delete an existing stored function.

{mospagebreak title=Creating a Stored Procedure}

The following abbreviated syntax is available for creating a stored procedure; see the Oracle Database XE documentation for a complete definition:

CREATE [OR REPLACE] PROCEDURE procedure_name ([parameter[, ...]])
   [characteristics, ...] [IS | AS] plsql_subprogram_body

The following is used to create a stored function:

CREATE [OR REPLACE] FUNCTION function_name ([parameter[, ...]])
   RETURNS type
[characteristics, ...] [IS | AS] plsql_subprogram_body

Finally, you create and use anonymous PL/SQL blocks as follows:

DECLARE
     declarations;
BEGIN
    statement1;
    statement2;
    …
END;

The DECLARE section is optional regardless of whether you are writing a procedure, a function, or an anonymous block. As you can infer from the syntax, you cannot pass variables, return variables, or reference the block from any other procedure or function; you can, however, save the block in a text file and retrieve it from the SQL Commands interface or embed the block within another stored function or procedure.

In this example, you use the SQL Commands interface to calculate an employee’s salary after two consecutive 10 percent raises. Figure 36-1 shows the anonymous block itself and the results after you click the Run button.


Figure 36-1.  Running an anonymous PL/SQL block in SQL Commands

Although you could obtain the results in Figure 36-1 by using one or more SQL statements, the advantages of using PL/SQL are evident. The list of steps you use to obtain your results is easy to understand, and the output from the block would be difficult to obtain using just SQL commands. Note the embedded procedure call to DBMS_OUTPUT.PUT_LINE . This predefined stored procedure is included with your installation of Oracle Database XE that produces text output from your procedures. We show you more examples of calling procedures from within a procedure later in this chapter in the section “Creating and Using a Stored Function.”

The other advantage of using an anonymous block is clear only if you look at the output line Statement Processed . When you click the Run button, the entire block is sent to Oracle for processing as a unit; you see the results after Oracle executes the block. This minimizes the network traffic to and from the Oracle server in contrast to sending SQL commands one at a time.

For our second introductory example, let’s create a simple stored procedure that returns the static string Hello, World :

create or replace procedure say_hello as begin
  
dbms_output.put_line(‘Hello, World’); end;

You don’t need to pass any parameters; the procedure already has the text to print. Note the OR REPLACE clause; if the procedure already exists, it will be replaced. If you do not specify OR REPLACE and the procedure already exists, you will get an error message and the procedure is not replaced.

Now execute the procedure using the following command:

begin
  
say_hello();
end;

Note that from the SQL Commands interface, you must use an anonymous block to call a stored procedure. Executing this procedure within the anonymous block returns the following output:

——————————————–
Hello, World

Statement processed.

0.00 seconds
——————————————–

In contrast to the previous example, once you create the procedure, you can call it repeatedly from different sessions without sending the procedure definition each time.

Please check back for the next part of this series.

[gp-comments width="770" linklove="off" ]

chat