• variable_declarations—Where you declare any variables that are local to the procedure.
• program_code—Where you write the PL/SQL statements that make up the procedure.
• error_handling_code—Where you write any error-handling code.

As you can see, the procedure declaration resembles a function declaration except that there is no return datatype and the keyword PROCEDURE is used instead of FUNCTION. Listing 3.6 shows an example of a procedure.

INPUT
Listing 3.6. A procedure block.

1: PROCEDURE swapn (num_one IN OUT NUMBER, num_two IN OUT NUMBER) IS
2:     temp_num    NUMBER;
3:   BEGIN
4:     temp_num := num_one;
5:     num_one := num_two;
6:     num_two := temp_num ;
7:   END;

ANALYSIS
You will see how this function is executed later in this chapter in the section titled "Nesting Functions and Procedures."

Procedures and functions are both useful constructs that promote modularity, allow you to hide complexity, and facilitate reuse of code. During Day 7, you will read about many of the built-in functions provided by Oracle, and on Day 5, you will learn more about creating your own functions.

Trigger Blocks

PL/SQL can also be used to write database triggers. Triggers are used to define code that is executed when certain actions or events occur. At the database level, triggers can be defined for events such as inserting a record into a table, deleting a record, and updating a record.

The following syntax for creating a database trigger is much more complex than that for a function or a procedure. Don't try and understand it all now. Triggers are discussed in more detail on Day 11, "Writing Database Triggers."

The Syntax for Creating a Database Trigger

CREATE [OR REPLACE] TRIGGER trigger_name
  {BEFORE|AFTER} verb_list ON table_name
  [[REFERENCING correlation_names] FOR EACH ROW [WHEN (condition)]]
DECLARE
  declarations
BEGIN
  pl/sql_code
END;
/

In this syntax, the placeholders are as follows:

• trigger_name—The name you want to give the trigger.
• verb_list—The SQL verbs that fire the trigger.
• table_name—The table on which the trigger is defined.
• correlation_names—Allows you to specify correlation names other than the default of OLD and NEW.
• condition—An optional condition placed on the execution of the trigger.
• declarations—Consists of any variable, record, or cursor declarations needed by this PL/SQL block.
• pl/sql_code—The PL/SQL code that gets executed when the trigger fires.

As you can see, even though the specification for a trigger is much more complex than that of a procedure or a function, the basic PL/SQL block structure is still present. The first three lines of the declaration tell Oracle the type of trigger, the table it is associated with, and when it should be fired. The remainder is simply the PL/SQL block that executes when the trigger fires.

Listing 3.7 shows a database trigger that sets the primary key of a new record being inserted into the database table my_table.

INPUT
Listing 3.7. Trigger that sets a primary key.

 1: CREATE OR REPLACE TRIGGER my_table_set_key
 2:         BEFORE INSERT ON my_table
 3:         REFERENCING NEW AS n
 4:         FOR EACH ROW
 5: DECLARE
 6:   new_key INTEGER;
 7: BEGIN
 8:   SELECT key_for_table.NEXTVAL INTO new_key FROM DUAL;
 9:   :n.fld_pk := new_key;
10: END;

Again, don't worry if you don't understand triggers completely right now. Day 11 contains a complete discussion of their benefits and how to use them.

Nested Blocks

PL/SQL blocks can be nested, one inside the other. This is often done for purposes of error handling, and also for purposes of modularity. Listing 3.8 shows a nested anonymous block.

INPUT/OUTPUT
Listing 3.8. A nested anonymous block.

 1: --Be sure to execute: SET SERVEROUTPUT ON
 2: --before executing this PL/SQL block.
 3: --This is an example of nested anonymous blocks.
 4: DECLARE
 5:   error_flag  BOOLEAN := false;  --true if an error occurs while counting.
 6:
 7: BEGIN
 8:   DBMS_OUTPUT.PUT_LINE(`We are going to count from 100 to 1000.');
 9:
10:   --Execute the nested block to do the actual counting.
11:   --Any errors will be trapped within this block.
12:   DECLARE
13:     hundreds_counter  NUMBER(1,-2);
14:   BEGIN
15:     hundreds_counter := 100;
16:     LOOP
17:       DBMS_OUTPUT.PUT_LINE(hundreds_counter);
18:       hundreds_counter := hundreds_counter + 100;
19:       IF hundreds_counter > 1000 THEN
20:         EXIT;
21:       END IF;
22:      END LOOP;
23:   EXCEPTION
24:   WHEN OTHERS THEN
25:     --set the error flag if we can't finish counting.
26:     error_flag := true;
27:   END;
28:
29:   --We are done. Were we successful?
30:   IF error_flag THEN
31:     DBMS_OUTPUT.PUT_LINE(`Sorry, I cannot count that high.');
32:    ELSE
33:     DBMS_OUTPUT.PUT_LINE(`Done.');
34:   END IF;
35: END;
36: /
37:  We are going to count from 100 to 1000.
38: 100
39: 200
40: 300
41: 400
42: 500
43: 600
44: 700
45: 800
46: 900
47: Sorry, I cannot count that high.
48:
49: PL/SQL procedure successfully completed.

ANALYSIS
The exception handler on the inner block, lines 23 through 26, sets the error_flag variable to true if any error occurs while counting. This allows the outer block to detect the error and display an appropriate message.

Nesting Functions and Procedures

Functions and procedures can be declared and executed from within other PL/SQL blocks. Remember the iifn function shown in Listing 3.5? That function takes three arguments: one boolean and two numbers. It functions as an inline IF statement. If the first argument is true, the first number is returned. If the first argument is false, the second number is returned. You can see how the function works by writing a simple PL/SQL block to declare and execute it, as shown in Listing 3.9.

INPUT/OUTPUT
Listing 3.9. Executing the iifn function.

 1: --This is a pl/sql wrapper that exercises the
 2: --function shown in Listing 3.5.
 3: --Be sure to execute: SET SERVEROUTPUT ON
 4: --
 5: DECLARE
 6: temp  NUMBER;
 7:
 8:   FUNCTION iifn(boolean_expression IN BOOLEAN,
 9:               true_number IN NUMBER,
10:               false_number IN NUMBER)
11:   RETURN NUMBER IS
12:   BEGIN
13:     IF boolean_expression THEN
14:       RETURN true_number;
15:     ELSIF NOT boolean_expression THEN
16:       RETURN false_number;
17:     ELSE
18:       --nulls propagate, i.e. null input yields null output.
19:       RETURN NULL;
20:     END IF;
21:   END;
22: BEGIN
23:   DBMS_OUTPUT.PUT_LINE(iifn(2 > 1,1,0));
24:   DBMS_OUTPUT.PUT_LINE(iifn(2 > 3,1,0));
25:   --
26:   --The next few lines verify that a null input yields a null output.
27:   temp := iifn(null,1,0);
28:   IF temp IS NULL THEN
29:     DBMS_OUTPUT.PUT_LINE(`NULL');
30:   ELSE
31:     DBMS_OUTPUT.PUT_LINE(temp);
32:   END IF;
33: END;
34: /
35: 1
36: 0
37: NULL
38:
39: PL/SQL procedure successfully completed.