Creating a Stored Procedure - Oracle

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;

TheDECLARE 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 toDBMS_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 lineStatement 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 stringHello, 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 theOR REPLACEclause; if the procedure already exists, it will be replaced. If you do not specifyOR REPLACEand 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.