What is PL/SQL?

PL/SQL, then, is an application-development language that is a superset of SQL, supplementing it with standard programming-language features that include the following:

• block (modular) structure

• flow-control statements and loops

• variables, constants, and types

• structured data

• customized error handling

Unlike SQL, PL/SQL is not an industry standard, but is an exclusive product of Oracle Corporation.

Note: For the sake of efficiency, PL/SQL code is compiled prior to runtime. It cannot refer at compile time to objects that do not yet exist, and, for that reason, the one part of SQL that PL/SQL does not include is DDL (Data Definition Language)--the statements, such as CREATE TABLE, that create the database and the objects it contains. However, you can work around this by using the package DBMS_SQL, included with the server, to generate the DDL code itself dynamically at runtime. For more information, see "Using DDL and Dynamic SQL" in the PL/SQL User's Guide and Reference.

Basic Structure and Syntax

A block has three parts:

• A DECLARE section. This is where you define local variables, constants, types, exceptions, and nested subprograms. PL/SQL has a forward declaration, but you can use it only for subprograms. Therefore, you must define all variables, constant, and types before referencing them. For more information on forward declarations, see "Declaring Subprograms" in the PL/SQL User's Guide and Reference.

• An EXECUTABLE section. This is the actual code that the block executes. This is the only part of the block that must always be present.

• An EXCEPTION section. This is a section for handling runtime errors and warnings.

The DECLARE Section

Datatypes

• Scalar. These include all string, number, and binary types. All of the SQL datatypes, which are the datatypes that you can store in the database, fall into this category. To find out about these datatypes, see "Datatypes" in the Oracle7 Server SQL Reference.

• Composite. These are structured datatypes, which is to say data structures that have components you can address independently. The PL/SQL composite types are TABLE (which is distinct from both database and HTML tables) and RECORD. These types are explained later in this chapter.

• Reference. There is one kind of reference datatype--REF CURSOR--which is a pointer to a cursor. Cursors are explained later in this chapter. For more information on the REF CURSOR datatype, see "Using Cursor Variables" in the PL/SQL User's Guide and Reference.

In many cases, you can convert from one datatype to another, either explicitly or automatically. The possible conversions and the procedure involved are explained in the PL/SQL User's Guide and Reference under "Datatype Conversion".

You can also define a variable so that it inherits its datatype from a database column or from another variable or constant, as explained in the next section.

Declaring Variables

cnum INTEGER(5) NOT NULL;

This declares a five-digit integer called cnum that will not accept nulls. The use of case above serves to distinguish keywords from identifiers; PL/SQL is not case-sensitive. NOT NULL is the only SQL constraint that you can use as a PL/SQL attribute.

Note: PL/SQL initializes all variables to null. Therefore, a NOT NULL variable, such as the above, produces an error if referenced before it is assigned a value.

Optionally, you can assign an initial value to the variable when you declare it by following the datatype specification with an assignment, as follows:

cnum INTEGER(5) := 254;

This sets cnum to the initial value of 254. Alternatively, you can use the keyword DEFAULT in place of the assignment operator := to achieve the same effect. For more information on setting defaults, see "Declarations" in the PL/SQL User's Guide and Reference .

Inheriting Datatypes To have the variable inherit the datatype of a database column or of another variable, use the %TYPE attribute in place of a declared datatype, as follows:

snum cnum%TYPE;

This means that snum inherits the datatype of cnum. You can inherit datatypes from database columns in the same way, by using the notation tablename.columname in place of the variable name. Normally, you do this if the variable in question is to place values in or retrieve them from the column. The advantages are that you need not know the exact datatype the column uses and that you need not change your code if the datatype of that column changes. If you do not own the table containing the column, precede the tablename with the schemaname, as described under "Naming Conventions" elsewhere in this chapter. For more information on %TYPE assignments, see "Declarations" in the PL/SQL User's Guide and Reference.

Declaring Constants

interest CONSTANT REAL(5,2) := 759.32;

Defining Types

SUBTYPE shortnum IS INTEGER(3);

This defines SHORTNUM as a 3-digit version of INTEGER. For more information see "User-Defined Subtypes" in the PL/SQL User's Guide and Reference.

Scope and Visibility

The area of a program within which an object can be used is called the object's scope. An object's scope is distinct from its visibility. The former is the area of the program that can reference the object; the latter is the, generally smaller, portion that can reference it without qualification.

An ambiguous reference can arise because objects or subprograms contained in different blocks can have the same names, even if they have overlapping scopes. When this happens, the reference by default means the object most local in scope--in other words, the first one PL/SQL finds by starting in the current block and working out to the enclosing ones. Qualification is the method used to override this. It is similar to the system of qualification used for database objects, as explained under "Naming Conventions" elsewhere in the chapter. To qualify an object's name, precede it with the name of the subprogram where it is declared, followed by a dot, as follows:

relocate.transmit(245, destination);

This invokes a procedure called transmit declared in some subprogram called relocate. The subprogram relocate must be global to the block from which it is called.

Data Structures

PL/SQL Tables These are somewhat similar to database tables, except that they always consist of two columns: a column of values and a primary key. This also makes them similar to one-dimensional arrays, with the primary key functioning as the array index. Like SQL tables, PL/SQL tables have no fixed allocation of rows, but grow dynamically. One of their main uses is to enable you to pass entire columns of values as parameters to subprograms. With a set of such parameters, you can pass an entire table. The primary key is always of type BINARY_INTEGER, and the values can be of any scalar type.

You declare objects of type TABLE in two stages:

1. You declare a subtype using the following syntax:

TYPE type_name IS TABLE OF

	datatype_spec

	[ NOT NULL ]

	INDEX BY BINARY INTEGER;

Where datatype_spec means the following:

datatype | variablename%TYPE | tablename.columname%TYPE 

In other words, you can either specify the type of values directly or use the %TYPE attribute (explained under "Declaring Variables", elsewhere in this chapter) to inherit the datatype from an existing variable or database column.

2. You assign objects to this subtype in the usual way. You cannot assign initial values to tables, so the first reference to the table in the EXECUTABLE section must provide it at least one value.

When you reference PL/SQL tables, you use an array-like syntax of the form:

column_value(primary_key_value)

In other words, the third row (value) of a table called "Employees" would be referenced as follows:

Employees(3)

You can use these as ordinary expressions. For example, to assign a value to a table row, use the following syntax:

Employees(3) := 'Marsha';

For more information, see "PL/SQL Tables" in the PL/SQL User's Guide and Reference.

Records As in many languages, these are data structures that contain one or more fields. Each record of a given type contains the same group of fields with different values. Each field has a datatype, which can be RECORD. In other words, you can nest records, creating data structures of arbitrary complexity. As with tables, you declare records by first declaring a subtype, using the following syntax:

TYPE record_type IS RECORD

	(fieldname datatype[, fieldname datatype]...);

The second line of the above indicates a parenthesized, comma-separated, list of fieldnames followed by datatype specifications. The datatype specifications can be direct or be inherited using the %TYPE attribute, as shown for TABLE and as explained under "Declaring Variables", elsewhere in this chapter.

You can also define a record type that automatically mirrors the structure of a database table or of a cursor, so that each record of the type corresponds to a row, and each field in the record corresponds to a column. To do this, use the %ROWTYPE attribute with a table or cursor name in the same way you would the %TYPE attribute with a variable, or column. The fields of the record inherit the column names and datatypes from the cursor or table. For more information, see "Records" and "%ROWTYPE Attribute" in the PL/SQL User's Guide and Reference.

Cursors A cursor is a data structure that holds the results of a query (a SELECT statement) for processing by other statements. Since the output of any query has the structure of a table, you can think of a cursor as a temporary table whose content is the output of the query.

When you declare a cursor, you associate it with the desired query. When you want to use that cursor, you open it, executing the associated query and filling the cursor with its results. You then fetch each row of the query's output in turn for processing by other statements in the program. You can also use a cursor to update a table's contents. To do this, use a FOR UPDATE clause to lock the rows in the table. See "Using FOR UPDATE" in the PL/SQL User's Guide and Reference for more information. Sometimes, you may need to use cursor variables, which are not associated with a query until runtime. This is a form of dynamic SQL.

For more information on cursor variables, see "Using Dynamic SQL" in the Oracle7 Server Application Developers Guide and "Cursor Variables" in the PL/SQL User's Guide and Reference.

For more information on cursors in general, see "Cursors" in the PL/SQL User's Guide and Reference. See also "DECLARE CURSOR," "OPEN", and "FETCH" in the Oracle7 Server SQL Reference.

You can simplify some cursor operations by using cursor FOR loops. For more information on these, see "Using Cursor FOR Loops" in the PL/SQL User's Guide and Reference.

Exceptions

Declaring Subprograms

PROCEDURE procedure_name (parameter_name datatype,

parameter_name datatype...) IS

	{local declarations}

	BEGIN {executable code}

	EXCEPTION

	END;

Note: For subprograms, the keyword DECLARE is omitted before the local declarations. Place local declarations before the keyword BEGIN, as shown.

The names you give the parameters in the declaration are the names that the procedure itself uses to refer to them. These are called the formal parameters. When the procedure is invoked, different variables or constants may be used to pass values to or from the formal parameters; these are called the actual parameters.

When calling the procedure, you can use each parameter for input of a value to the procedure, output of a value from it, or both. These correspond to the three parameter modes: IN, OUT, and IN/OUT. For more information, see "Parameter Modes" in the PL/SQL User's Guide and Reference.

When you call the procedure, you can match the actual to the formal parameters either implicitly, by passing them in the same order they are given in the declaration, or explicitly, by naming the formal followed by the actual parameter as shown:

transmit(destination => address);

This invokes a procedure called transmit, assigning the value of address as the actual parameter for the formal parameter destination. This implies that the parameter destination is used within the transmit procedure and that the parameter address is used outside of it. Usually, it is good programming practice to use different names for matching formal and actual parameters. For more information on this, see "Positional and Named Notation" in the PL/SQL User's Guide and Reference.

Functions are the same, except for the addition of a return value, specified as follows:

FUNCTION function_name (parameter_name, parameter_name datatype...)

	RETURN datatype IS

	{local declarations}

	BEGIN {executable code}

	EXCEPTION {local exception handlers}

	END;

Again, line breaks are only for readability. A RETURN statement in the executable section actually determines what the return value is. This consists of the keyword RETURN followed by an expression. When the function executes the RETURN statement, it terminates and passes the value of that expression to whichever statement called it in the containing block.

You can also use the RETURN statement without an expression in a procedure to force the procedure to exit.

For more information on procedures and functions, see "Declaring Subprograms" in the PL/SQL User's Guide and Reference.

The EXECUTABLE Section

Assignments

a := 45;

Character strings should be set off with single quotes (') as in all expressions. An example follows:

FNAME := 'Clair';

There are other examples of assignments in other parts of this chapter.

Flow Control

• IF statements. These execute a group of one or more statements based on whether a condition is TRUE.

• Basic loops. These repeatedly execute a group of one or more statements until an EXIT statement is reached.

• FOR loops. These repeatedly execute a group of one or more statements a given number of times or until an EXIT statement is reached.

• WHILE loops. These repeatedly execute a group of one or more statements until a particular condition is met or an EXIT statement is reached.

• GOTO statements. These pass execution directly to another point in the code, exiting loops and enclosing blocks as necessary. Use these sparsely, as they make code difficult to read and debug.

You can nest flow control statements within one another to any level of complexity.

IF Statements

The IF statement has the following forms:

1	IF <condition> THEN <statement-list>;

	END IF;

If the condition following IF is TRUE, PL/SQL executes the statements in the list following THEN. A semicolon terminates this list. END IF (not ENDIF) is mandatory and terminates the entire IF statement. Here is an example:

	IF balance > 500 THEN send_bill(customer);

	END IF;

We are assuming that send_bill is a procedure taking a single parameter.

2	IF <condition> THEN <statement-list>;

	ELSE <statement-list>;

	END IF;

This is the same as the preceding statement, except that, if that condition is FALSE or NULL, PL/SQL executes the statement list following ELSE instead of that following THEN.

3	IF <condition> THEN <statement-list>;

	ELSIF <condition> THEN <statement-list>;

	ELSIF <condition> THEN <statement-list>;.....

	ELSE <statement-list>;

	END IF;

You can include any number of ELSIF (not ELSEIF) conditions. Each is tested only if the IF condition and all preceding ELSIF conditions are FALSE or NULL. As soon as PL/SQL finds an IF or ELSIF condition that is TRUE, it executes the associated THEN statement list and skips ahead to END IF. The ELSE clause is optional, but, if included, must come last. It is executed if all preceding IF and ELSIF conditions are FALSE or NULL.

NULL Statements If you do not want an action to be taken for a given condition, you can use the NULL statement, which is not to be confused with database nulls, Boolean NULLs, or the SQL predicate IS NULL. The syntax of this statement is simply:

NULL;

The statement performs no action, but fulfills the syntax requirement that a statement list must follow every THEN keyword. In some cases, you can also use it to increase the readability of your code. For more information on the NULL statement, see "NULL Statement" in the PL/SQL User's Guide and Reference.

Basic Loops

credit := 0;

 LOOP

	IF c = 5 THEN EXIT;

	END IF;

	credit := credit + 1;

END LOOP;

This loop keeps incrementing credit until it reaches 5 and then exits. An alternative to placing an exit statement inside an IF statement is to use the EXIT-WHEN syntax, as follows:

EXIT WHEN credit = 5;

This is equivalent to the earlier IF statement.

Note: The EXIT statement cannot be the last statement in a PL/SQL block. If you want to exit a PL/SQL block before its normal end is reached, use the RETURN statement. For more information, see "RETURN Statement" in the PL/SQL User's Guide and Reference.

FOR Loops

FOR credit IN 1..5 LOOP

	interest := interest * 1.2;

END LOOP;

The numbers used to specify the range (in this case, 1 and 5) can be variables, so you can let the number of iterations of the loop be determined at runtime if you wish.

WHILE Loops

credit := 1;

WHILE credit <= 5 LOOP

	interest := interest * 1.2;

	credit := credit + 1;

END LOOP;

Unlike some languages, PL/SQL has no structure, such as REPEAT-UNTIL, that forces a LOOP to execute at least once. You can create this effect, however, using either basic or WHILE loops and setting a variable to a value that will trigger the loop, as in the above example. For more information on loops, see "Iterative Control" in the PL/SQL User's Guide and Reference.

GOTO Statements

<<this_is_a_label>>

You only use the brackets at the target itself, not in the GOTO statement that references it, so a GOTO statement transferring execution to the above label would be:

GOTO this_is_a_label;

Note: An EXIT statement can also take a label, if that label indicates the beginning of a loop enclosing the EXIT statement. You can use this to exit several nested loops at once. See "Loop Labels" in the PL/SQL User's Guide and Reference for more information.

A GOTO statement is subject to the following restrictions:

• It must branch to an executable statement, not, for example, an END.

• It cannot branch to a point within the body of IF or a LOOP statement, unless it is contained in the body of that statement itself.

• It cannot branch to a subprogram or enclosing block of the present block (with one exception, explained shortly).

• It cannot branch from one IF statement clause to another. That is to say, it cannot jump between THEN, ELSIF, and ELSE clauses that are part of the same IF statement.

• It cannot branch from the EXCEPTION section to the EXECUTABLE section of the same block.

• It can, however, branch from the EXCEPTION section of a block to the EXECUTABLE section of an enclosing block, which is the exception to the third rule above.

The EXCEPTION Section

There are two basic kinds of exceptions: predefined and user-defined. The predefined exceptions are provided by PL/SQL in a package called STANDARD. They correspond to various runtime problems that are known to arise often--for example, dividing by zero or running out of memory. These are listed in the PL/SQL User's Guide and Reference under "Predefined Exceptions".

The Oracle Server can distinguish between and track many more kinds of errors than the limited set that STANDARD predefines. Each of Oracle's hundreds of messages are identified with a number, and STANDARD has simply provided labels for a few of the common ones. You can deal with the other messages in either or both of two ways:

• You can define your own exception labels for specified Oracle messages using a pragma (a compiler directive). This procedure will be explained shortly.

• You can define a handler for the default exception OTHERS. Within that handler, you can identify the specific error by accessing the built-in functions SQLCODE and SQLERRM, which contain, respectively, the numeric code and a prose description of the message.

Declaring Exceptions

customer_deceased EXCEPTION;

In other words, an identifier you choose followed by the keyword EXCEPTION. Notice that all this declaration has done is provide a name. The program still has no idea when this exception should be raised. In fact, there is at this point no way of telling if this is to be a user-defined exception or simply a label for an Oracle message.

Labeling Oracle Messages

PRAGMA EXCEPTION_INIT (exception_name, Oracle_error_number);

A PRAGMA is a instruction for the compiler, and EXCEPTION_INIT is the type of PRAGMA. This tells the compiler to associate the given exception name with the given Oracle error number. This is the same number to which SQLCODE is set when the error occurs. The advantage of this over defining your own error condition is that you pass the responsibility for determining when the error has occurred and raising the exception to Oracle. You can find the numeric codes and explanations for Oracle messages in Oracle7 Server Messages.

User-Defined Exceptions

IF cnum < 0 THEN RAISE customer_deceased;

You can also use the RAISE statement to force the raising of predefined exceptions. For more information, see "Error Handling" in the PL/SQL User's Guide and Reference.

Handling Exceptions

This is the syntax of an exception handler:

WHEN exception_condition THEN statement_list;

The exception is the identifier for the raised condition. If desired, you can specify multiple exceptions for the same handler, separated by the keyword OR. The exception can be either one the package STANDARD provided or one you declared. The statement list does what is appropriate to handle the error--writing information about it to a file, for example--and arranges to exit the block gracefully if possible. Although exceptions do not necessarily force program termination, they do force the program to exit the current block. You cannot override this with a GOTO statement. You can use a GOTO within an exception handler, but only if its destination is some enclosing block.

Note: If you have an error prone statement and want execution to continue following this statement, even when an exception occurs, put the statement, including the appropriate exception handlers, in its own block, so that the current block becomes the enclosing block.

Note: If an exception occurs in the DECLARE section or the EXCEPTION section itself, local exception handlers cannot address it; execution passes automatically to the EXCEPTION section of the enclosing block.

Storing Procedures and Functions in the Database

The syntax for these statements is slightly different than that used to declare subprograms in PL/SQL, as the following example shows:

CREATE PROCEDURE fire_employee (empno INTEGER) IS

	BEGIN

		DELETE FROM Employees WHERE enum = empno;

	END;

As you can see, the main difference is the addition of the keyword CREATE. You also have the option of replacing the keyword IS with AS, which does not affect the meaning. To replace an existing procedure of the same name with this procedure (as you frequently may need to do during development and testing), you can use CREATE OR REPLACE instead of simply CREATE. This destroys the old version, if any, without warning.

Privileges Required

• If the procedure is to be in your own schema, you must have the CREATE PROCEDURE or the CREATE ANY PROCEDURE system privilege. These privileges apply as well to functions.

• If the procedure is to be in a schema you do not own, you must have the CREATE ANY PROCEDURE system privilege.

• You must have the object privileges necessary to perform all operations contained in the procedure. You must have these privileges as a user, not through roles. If your privileges change after you have created the procedure, the procedure may no longer be executable.

GRANT EXECUTE ON show_product TO PUBLIC;

Of course, the public normally does not execute such a procedure directly. This statement enables you to use the procedure in your PL/SQL code that is to be publicly executable. If multiple users access the same procedure simultaneously, each gets his own instance. This means that the setting of variables and other activities by different users do not affect one another.

For more information on privileges and roles, see "GRANT" in Chapter 4 of the Oracle7 Server SQL Reference. There are three versions of GRANT listed--one each for object privileges, system privileges, and roles.

For more information on storing procedures and functions in the database, see "Storing Procedures and Functions" in the Oracle7 Server Application Developers Guide and see "CREATE FUNCTION" and "CREATE PROCEDURE" in the Oracle7 Server SQL Reference.

Packages

• The package specification is the public interface to the package. It declares all objects and subprograms that are to be accessible from outside the package. Packages do not take parameters, so these constitute the entire public interface.

• The package body is the internal portion of the package. It contains all objects and subprograms that are to be local to the package. It also contains definitions of the public cursors and subprograms. The package specification declares but does not define these.

Packages cannot be nested, but they can call one another's public subprograms and reference one another's public objects.

Instantiation of Packages

There is an exception, however. When one package calls another, execution of the second has a dependency on the first. If the first is invalidated, for example because its creator loses a privilege that the package requires, the second, while not necessarily invalidated, becomes deinstantiated. That is to say, all its objects are reinitialized.

Note: In PL/SQL, stored procedures and packages are automatically recompiled if changes to the database mandate it. For example, a change to the datatype of a column can automatically cascade to a variable referencing that column if the former is declared with the %TYPE attribute, but that change requires that the PL/SQL procedure declaring that variable be recompiled. So long as the PL/SL code as written is still valid, the recompilation occurs automatically and invisibly to the user.

Creating Packages

Note: Before you can create a package, the special user SYS must run the SQL script DBMSSTDX.SQL. The exact name and location of this script may vary according to your operating system. Contact your database administrator if you are not sure this script has been run.

Creating the Package Specification The syntax of the CREATE PACKAGE statement is as follows:

CREATE [OR REPLACE] PACKAGE package_name IS

	{PL/SQL declarations}

	END;

The optional OR REPLACE clause operates just as it does for stored procedures, as explained elsewhere in this chapter. The PL/SQL declarations are as outlined under DECLARE SECTION elsewhere in this chapter, except that the keyword DECLARE is not used and that the subprogram and cursor declarations are incomplete. For subprograms, you provide only the name, parameters, and, in the case of functions, the datatype of the return value. For cursors, provide the name and a new item called the return type. This approach hides the implementation of these objects from the public while making the objects themselves accessible.

The syntax for declaring a cursor with a return type is as follows:

CURSOR c1 IS RETURN return_type;

The return type is always some sort of record type that provides a description of the cursor's output. The structure of this record is to mirror the structure of the cursor's rows. You can specify it using any of the following:

• A record subtype previously defined and in scope. For more information, see "Records" elsewhere in this chapter.

• A type inherited from such a record subtype using the %TYPE attribute. For more information, see "Declaring Variables" elsewhere in this chapter.

• A type inherited from a table, most likely the table the cursor queries, using the %ROWTYPE attribute. For more information, see "Records" elsewhere in this chapter.

• A type inherited from a cursor using the %ROWTYPE attribute. For more information, see "Records" elsewhere in this chapter.

Creating the Package Body To create the package body, use the CREATE PACKAGE BODY statement. The syntax is as follows:

CREATE [OR REPLACE] PACKAGE BODY package_name IS

	{PL/SQL declarations}

	END;

Since a package as such does not do anything, the PL/SQL code still consists only of a DECLARE section with the keyword DECLARE omitted. It is the subprograms within the package that contain the executable code. Variables, constants, types, and cursors declared directly (in other words, not within a subprogram) in the declare section have a global scope within the package body. Variables, constants, and types already declared in the package specification are public and should not be declared again here.

Public cursors and subprograms, however, must be declared again here, as their declarations in the specification is incomplete. This time the declarations must include the PL/SQL code (in the case of subprograms) or the query (in the case of cursors) that is to be executed. For subprograms, the parameter list must match that given in the package specification word for word (except for differences in white space). This means, for example, that you cannot specify a datatype directly in the specification and use the %TYPE attribute to specify it in the body.

You can create an initialization section at the end of the package body. This is a body of executable code--chiefly assignments--enclosed with the keywords BEGIN and END. Use this to initialize constants and variables that are global to the package, since otherwise they could be initialized only within subprograms, and you have no control of the order in which subprograms are called by outside applications. This initialization is performed only once per session.

For more information, see CREATE PACKAGE BODY in the Oracle7 Server SQL Reference, "Packages" in the PL/SQL User's Guide and Reference, and "Using Procedures and Packages" in the Oracle7 Server Application Developers Guide.

Overloading Subprograms

The reason this is permitted is so you can overload subprograms. Overloading permits you to have several versions of a procedure that are conceptually similar but behave differently with different parameters. This is one of the properties of object-oriented programming. For more information on overloading, see "Overloading" in the PL/SQL User's Guide and Reference.

Database Triggers

You create triggers as you do stored procedures and packages, by using your text editor to write scripts that create them and then using SQL*Plus or Server Manager to run these scripts. A trigger is like a package in that:

• It takes no parameters as such. It refers to, responds to, and possibly affects the data in the database.

• It cannot be directly called like a procedure. To fire (execute) a trigger, you must make the database change to which it responds. If you only want to test the trigger, you can rollback (undo) the database change that you made after the trigger fires.

• INSERT triggers, UPDATE triggers, and DELETE triggers. This is a classification based on the statement to which the trigger responds. The categories are not mutually exclusive, meaning one trigger can respond to any or all of these statements.

• Row triggers and statement triggers. Any of the above statements can affect any number of rows in a table at once. A row trigger is fired once for each row affected. A statement trigger is fired once for each statement, however many rows it affects.

• BEFORE triggers and AFTER triggers. This specifies whether the trigger is fired before or after the data modification occurs.

Creating Triggers

CREATE [OR REPLACE] TRIGGER trigger_name

	BEFORE | AFTER

	DELETE | INSERT | UPDATE [OF column_list]

	ON table_name

	[ FOR EACH ROW [ WHEN predicate ] ]

	{PL/SQL block};

In the above, square brackets ([ ]) enclose optional elements. Vertical bars ( | ) indicate that what precedes may be replaced by what follows.

In other words, you must specify the following:

• A trigger name. This is used to alter or drop the trigger. The trigger name must be unique within the schema.

• BEFORE or AFTER. This specifies whether this is a BEFORE or AFTER trigger.

• INSERT, UPDATE, or DELETE. This specifies the type of statement that fires the trigger. If it is UPDATE, you optionally can specify a list of one or more columns, and only updates to those columns fire the trigger. In such a list, separate the column names with commas and spaces. You may specify this clause more than once for triggers that are to respond to multiple statements; if you do, separate the occurrences with the keyword OR surrounded by white space.

• ON table_name. This identifies the table with which the trigger is associated.

• PL/SQL Block. This is an anonymous PL/SQL block containing the code the trigger executes.

• OR REPLACE. This has the usual effect.

• FOR EACH ROW [WHEN predicate]. This identifies the trigger as a row trigger. If omitted, the trigger is a statement trigger. Even if this clause is included, the WHEN clause remains optional. The WHEN clause contains a SQL (not a PL/SQL) predicate that is tested against each row the triggering statement alters. If the values in that row make the predicate TRUE, the trigger is fired; else it is not. If the WHEN clause is omitted, the trigger is fired for each altered row.

CREATE TRIGGER give_bonus

	AFTER UPDATE OF sales

	ON salespeople

	FOR EACH ROW WHEN sales > 8000.00

	BEGIN

	UPDATE salescommissions SET bonus = bonus + 150.00;

	END;

This creates a row trigger called give_bonus. Every time the sales column of the salespeople table is updated, the trigger checks to see if it is over 8000.00. If so, it executes the PL/SQL block, consisting in this case of a single SQL statement that increments the bonus column in the salescommissions table by 150.00.

Privileges Required

• You own the table associated with the trigger.

• You have the ALTER privilege on the table associated with the trigger.

• You have the ALTER ANY TABLE system privilege.

Referring to Altered and Unaltered States

If these names are not suitable, you can define others using the REFERENCING clause of the CREATE TRIGGER statement, which is omitted from the syntax diagram above for the sake of simplicity. For more information on this clause, see CREATE TRIGGER in the Oracle7 Server SQL Reference.

Note: if a trigger raises an unhandled exception, its execution fails and the statement that triggered it is rolled back if necessary. This enables you to use triggers to define complex constraints. If the effects of the trigger have caused a change in the value of package body variables, however, this change is not reversed. You should try to design your packages to spot this eventuality. For more information, see "Using Database Triggers" in the Oracle7 Server Application Developers Guide.

Enabling and Disabling Triggers

ALTER TRIGGER trigger_name DISABLE;

Later you can enable the trigger again by issuing the same statement with ENABLE in place of DISABLE. The ALTER TRIGGER statement does not alter the trigger in any other way. To do that you must replace the trigger with a new version using CREATE OR REPLACE TRIGGER. For more information on enabling triggers, see ALTER TRIGGER in the Oracle7 Server SQL Reference.