10.1. Procedures
A PL/SQL procedure is a stand-alone program that you compile into an Oracle database schema. Procedures can accept arguments. When you compile a procedure, the procedure identifier of the CREATE PROCEDURE statement becomes the object name in the data dictionary. Figure 10-1 illustrates the components and keywords of a procedure. This structure applies for a stand-alone procedure and the procedure code of a package body.
Figure 10-1. Procedure.
The key components of a procedure to which you write code are the following:
| Declarative Part | This is where you declare variables, for example:
local_counter NUMBER := 0; You may also have type definitions. Type definitions would be required for composite structures such as records and index-by tables. You may code procedures and functions within the declarative part. For example, you might write a local function that returns a substring with certain characteristics. You could use this local function to make the body easier to read. You can declare exceptions here. If you declare an exception within a procedure you should handle it locally. No program that calls your procedure can capture exceptions that are local to your procedure. |
| Subprogram Body | The subprogram body contains the logical algorithm implemented with PL/SQL control constructs. PL/SQL supports loops, if-then-else structures, case statements, and declare-block structures. |
| Exception handler | The exception handler is optional. The exception handler is similar to the try-catch model in other languages. You can code an exception handler for a specific type of error or write a general-purpose exception handler. |
You should name a procedure with a verb. Procedures usually perform some action such as update the database, write to a file, or send a message.
A procedure need not have parameters. When creating a procedure do not use parentheses if there are no parameters. Close parentheses are optional when calling the procedure. The following illustrates the absence of parentheses.
First, create a table.
CREATE TABLE TEMP(n NUMBER);
The procedure INSERT_TEMP inserts a row into the table TEMP.
PROCEDURE insert_temp IS
BEGIN
INSERT INTO TEMP (n) VALUES (0);
END insert_temp:
A procedure that uses INSERT_TEMP can code either of the following statements:
insert_temp; insert_temp();
You can code IS or AS—either syntax is acceptable.
PROCEDURE insert_temp IS | AS
Appending the procedure name to the END clause is optional, but highly recommended. A procedure can span several screens. When scrolling through code, it is extremely helpful to see an END clause and know you have not skipped reading into the next package procedure.
END; -- acceptable.
END procedure_name; -- highly recommended in production.
A common procedure style is to align the IS, BEGIN, EXCEPTION, and END. Indent all code within these keywords. This style makes the code easier to read. The following illustrates a procedure that prints the average and sum of values in a table. The code section between the IS and BEGIN is called the declarative part of the procedure.
PROCEDURE print_temp
IS
v_average NUMBER;
v_sum NUMBER;
BEGIN
SELECT AVG(n), SUM(n) INTO v_average, v_sum
FROM TEMP;
dbms_output.put_line('Average:'||v_average);
dbms_output.put_line('Sum:'||v_sum);
END print_temp;
A stand-alone procedure frequently evolves into a new package or merges with an existing package. Consider the procedure, INSERT_TEMP, shown on p. 251, which inserts a number into the TEMP table. The migration of INSERT_TEMP into a package is a simple editing process that produces the following:
PACKAGE temp_operations IS
PROCEDURE insert_temp;
END temp_operations;
PACKAGE BODY temp_operations IS
PROCEDURE insert_temp IS
BEGIN
INSERT INTO temp (n) VALUES (0);
END insert_temp;
END temp_operations;
The user of INSERT_TEMP changes their PL/SQL to use the new interface:
temp_operations.insert_temp; temp_operations.insert_temp();