7/9

# 3 Triggers and Stored Procedures

This chapter compares MySQL and Oracle triggers and stored procedures. (The information in this chapter applies only to MySQL release 5, not to earlier releases.) For more information about Oracle triggers and stored procedures, see the PL/SQL User's Guide and Reference. This chapter includes the following sections:

- Triggers

- Stored Procedures

## 3.1 Triggers

Triggers are named database objects that are implicitly fired when a triggering event occurs. The trigger action can be run before or after the triggering event. Triggers are similar to stored procedures but differ in the way that they are invoked.

Support for triggers in MySQL is only included beginning with release 5.0.2. A trigger can only be associated with a table and defined to fire when an INSERT, DELETE or UPDATE statement is performed on the table. MySQL does not permit two triggers with the same trigger timing (BEFORE or AFTER) and trigger event or statement (INSERT, DELETE, or UPDATE) to be defined on a table. For example, you cannot define two BEFORE INSERT or two AFTER UPDATE triggers for a table. All triggers defined on MySQL are row triggers, which means that the action defined for the triggers is executed for each row affected by the triggering statement.

Error handling during trigger execution for transactional tables ensures that either both the triggering statement and trigger action is completed successfully or neither the trigger statement nor the trigger action is executed, that is all changes made are rollback on failure. For non-transactional tables, all changes made prior to the point of error remains in effect.

The following is the syntax to create a trigger in MySQL:

    CREATE TRIGGER <trigger name>
      { BEFORE | AFTER }
      { INSERT | UPDATE | DELETE }
      ON <table name>
      FOR EACH ROW
      <triggered action>

In Oracle, triggers can be fired when one of the following operations occurs:

- DML statements (INSERT, DELETE or UPDATE) that modify data on a table or view

- DDL statements

- User events such as logon and logoff

- System events such as startup, shutdown, and error messages

Oracle allows multiple triggers with the same trigger timing and trigger event to be defined on a table; however, these triggers are not guaranteed to execute in any specific order. Triggers can be defined as row triggers or statement triggers. Statement triggers are fired once for each triggering statement regardless of the number of rows in a table affected by the triggering statement. For example if a DELETE statement deletes several rows from a table, a statement trigger is only fired once.

The execution model for Oracle triggers is transactional. All actions performed as a result of the triggering statement, including the actions performed by fired triggers, must all succeed; otherwise, they are rolled back.

## 3.2 Stored Procedures

Stored procedures provide a powerful way to code application logic that can be stored on the server. MySQL and Oracle both use stored procedures and functions. Stored functions are similar to procedures, except that a function returns a value to the environment in which it is called. In MySQL, stored procedures and functions are collectively called routines.

The following sections compare stored procedures in MySQL and Oracle:

- Individual SQL Statements

- Variables in Stored Procedures

- Error Handling in Stored Procedures

### 3.2.1 Individual SQL Statements

This section describes considerations related to the following statements or constructs:

- REPLACE Statement

- DO Statement

- Compound DECLARE Statement

- Compound SET Statement

#### 3.2.1.1 REPLACE Statement

The REPLACE statement in MySQL is a dual-purpose statement. It works like the INSERT statement when there is no record in the table that has the same value as the new record for a primary key or a unique index, and otherwise it works like the UPDATE statement.

Oracle does not have any built-in SQL statements that supports the purposes of the MySQL REPLACE statement. To convert this statement to Oracle, an emulated function using both the INSERT and UPDATE statements has to be created. An attempt is first made to place the data into the table using the INSERT statement; and if this fails, the data in the table is then updated using the UPDATE statement.

#### 3.2.1.2 DO Statement

As its name implies, the DO statement in MySQL does something but does not return anything; specifically, it executes the comma-delimited list of expressions specified as its parameters. The DO statement is converted to a `SELECT expr1 [, expr2,…] INTO … FROM DUAL` statement in Oracle.

#### 3.2.1.3 Compound DECLARE Statement

MySQL uses the DECLARE statement to declare local variables in stored procedures. PL/SQL does not allow multiple declarations; each declaration must be made separately. To convert compound DECLARE statements into functionally equivalent PL/SQL code, each MySQL multiple declaration statement should be converted into logically equivalent separate statements, one for each declaration.

For example, consider the following MySQL simple declaration and multiple declaration statements:

    /* Simple declaration */
    DECLARE a INT;

    /* Compound declaration */
    DECLARE a, b INT DEFAULT 5;

The PL/SQL functionally equivalent statements are:

    /* Simple declaration */
    a INT;

    /* Multiple declarations */
    a INT := 5;
    b INT := 5;

In this example, the two original MySQL DECLARE statements are converted into three logically equivalent PL/SQL declaration statements, with one PL/SQL declaration statement for every declaration used within the MySQL DECLARE statements.

#### 3.2.1.4 Compound SET Statement

MySQL uses the SET statement to assign values to variables (user variables or system variables). MySQL allows compound statements that assign values to two or more variables within the same statement. PL/SQL allows only simple assignments that assign a single value to a single variable. To convert compound SET statements into functionally equivalent PL/SQL code, split each MySQL multiple assignment statement into logically equivalent simple assignment statements.

For example, consider the following MySQL simple assignment and multiple assignment statements:

    /* Simple statement */
    SET a:=1;

    /* Compound statement*/
    SET x:=1, y:=0;

The PL/SQL functionally equivalent statements are:

    /* Simple statement */
    a:=1;

    /* Multiple statements */
    x:=1;
    y:=0;

In this example, the two original MySQL SET statements are converted into three logically equivalent PL/SQL assignment statements, with one PL/SQL assignment statement for every declaration used within the MySQL SET statements.

### 3.2.2 Variables in Stored Procedures

MySQL supports three types of variables in stored procedures: local variables, user variables, and system variables.

Local variables are declared within stored procedures and are only valid within the BEGIN…END block where they are declared. Local variables must be declared within a BEGIN…END block before they can be referenced in other statements in the block, including any nested BEGIN…END blocks. If a local variable declared within a nested BEGIN…END block has the same name as a local variable declared in its enclosing BEGIN…END block, the local variable in the nested block takes precedence wherever the local variable is referenced in the nested BEGIN…END block. Local variables can have any SQL data type. The following example shows the use of local variables in a stored procedure.

    CREATE PROCEDURE p1()
    BEGIN
       /* declare local variables */
       DECLARE x INT DEFAULT 0;
       DECLARE y, z INT;

       /* using the local variables */
       SET x := x + 100;
       SET y := 2;
       SET z := x + y;

       BEGIN
          /* local variable in nested block */
          DECLARE z INT;

          SET z := 5;

          /* local variable z takes precedence over the one of the
             same name declared in the enclosing block. */
          SELECT x, y, z;
       END;

       SELECT x, y, z;
    END;

    mysql> call p1();
    +-----+---+---+
    | x   | y | z |
    +-----+---+---+
    | 100 | 2 | 5 |
    +-----+---+---+
    1 row in set (0.00 sec)

    +-----+---+-----+
    | x   | y | z   |
    +-----+---+-----+
    | 100 | 2 | 102 |
    +-----+---+-----+
    1 row in set (0.00 sec)
    Query OK, 0 rows affected (0.00 sec)

User variables are specific to a user session and cannot be seen or used by other users. They are valid only for the duration of a user session and are automatically freed when the user session ends. User variables have a session-scope; thus, all references to a user variable of the same name within a session refer to the same variable. In MySQL stored procedures, user variables are referenced with an ampersand (@) prefixed to the user variable name (for example, `@x` and `@y`). The following example shows the use of user variables in two stored procedures.

    CREATE PROCEDURE p2()
    BEGIN
       SET @a = 5;
          SET @b = 5;
          SELECT @a, @b;
       END;

    CREATE PROCEDURE p3()
    BEGIN
       SET @a = @a + 10;
          SET @b = @b - 5;
          SELECT @a, @b;
       END;

    mysql> call p2();
    +------+------+
    | @a   | @b   |
    +------+------+
    | 5    | 5    |
    +------+------+
    1 row in set (0.00 sec)

    Query OK, 0 rows affected (0.00 sec)

    mysql> call p3();
    +------+------+
    | @a   | @b   |
    +------+------+
    | 15   | 0    |
    +------+------+
    1 row in set (0.00 sec)

    Query OK, 0 rows affected (0.00 sec)

In the second procedure (p3) in the preceding example, the use of the user variables `a` and `b` on the right-hand side of the assignment statement assumed that the variables have previously been initialized to some value by the first procedure. If the variables have not been initialized by the first procedure, they would have null values, and the result for the assignment would also be a null value.

System variables can also be referenced in MySQL stored procedures. There are two kinds of system variable in MySQL: global system variables and session system variables. Global system variables affect the operation of the overall server. Session system variables affect the individual user session. System variables that are dynamic can also be changed in MySQL stored procedures.

In a SET statement, variables name preceded by `GLOBAL` or `@@global.` are global variables, and session variables name can optionally be preceded by `SESSION`, `@@session`., `LOCAL` or `@@local.`. System variables can be referenced in SELECT statement using the `@@[global.|session.|local.]var_name` syntax. If `global.`, `session.`, or `local.` is not present, MySQL returns the SESSION variable if it exists or the GLOBAL value otherwise. The following example shows some of these syntax options.

    CREATE PROCEDURE p4()
    BEGIN
         /* setting the value of a (dynamic) global variable */
         SET GLOBAL sort_buffer_size := 10000;

         /* retrieving the value of a global variable */
         SELECT @@global.sort_buffer_size;

         /* setting the value of a (dynamic) session variable */
         SET max_join_size := DEFAULT;

         /* retrieving the value of a session variable, shown using
            different syntax */
         SELECT @@session.max_join_size;
         SELECT @@local.max_join_size;
         SELECT @@max_join_size;
    END;

Oracle PL/SQL also allows variables to be declared and used in stored procedures. As in MySQL, variables in PL/SQL must be declared in the declarative part of a PL/SQL block before they are referenced in any other statements in the block.

Local variables in PL/SQL have same scope as local variables in MySQL stored procedures. They are valid within the PL/SQL block where they are declared, including nested PL/SQL blocks. A variable of the same name in a nested PL/SQL block takes precedence over the variable in the enclosing PL/SQL block.

As with local variables in MySQL, variables in PL/SQL can have any SQL data type, such as NUMBER or VARCHAR2. In addition, variables in PL/SQL can also have PL/SQL data types, such as BOOLEAN or PLS_INTEGER, or be declared to hold table columns or table rows using the special qualifiers `%TYPE` and `%ROWTYPE`.

The following example shows some variable declarations in a PL/SQL block.

    DECLARE
      /* variables of SQL data-type */
      wages NUMBER;
      hours_worked NUMBER := 40;
      hourly_salary NUMBER := 22.50;
      bonus NUMBER := 150;
      country VARCHAR2(128);
      counter NUMBER := 0;

      /* variables of PL/SQL data-types */
      done BOOLEAN;
      valid_id BOOLEAN;

      /* variables declared to hold table rows */
      emp_rec1 employees%ROWTYPE;
      emp_rec2 employees%ROWTYPE;
    BEGIN
      wages := (hours_worked * hourly_salary) + bonus;
      country := 'France';
      country := UPPER('Canada');
      done := (counter > 100);
      valid_id := TRUE;
      emp_rec1.first_name := 'Antonio';
      emp_rec1.last_name := 'Ortiz';
      emp_rec1 := emp_rec2;
    END;

Oracle PL/SQL also allows constants to be declared in stored procedures. Like variables, constants must be declared in the declarative part of a PL/SQL block before they can be referenced in other statements in the PL/SQL block, including nested PL/SQL blocks. A constant is declared with the `CONSTANT` keyword. A constant must be initialized in its declaration, and no further assignments to the constant are allowed. The following example declares a constant in PL/SQL.

    credit_limit CONSTANT NUMBER := 5000.00;

User variables in MySQL stored procedures can be emulated in Oracle by defining the variables in a package. The package specification emulates the per-session MySQL user variables.Variables defined in a package are available to the users of the package. For an example of a MySQL stored procedure and the converted equivalent in Oracle, consider the following MySQL stored procedure:

    CREATE PROCEDURE p2()
    BEGIN
      SET @a = 5;
      SET @b = 5;
      SELECT @a, @b;
    END;

For this example, the Oracle equivalent statements are:

    CREATE OR REPLACE PACKAGE root.globalPkg AS
       a NUMBER;
       b NUMBER;
    END globalPkg;

    CREATE OR REPLACE PROCEDURE root.p2 AS
    BEGIN
       globalPkg.a := 5;
       globalPkg.b := 5;

       DBMS_OUTPUT.PUT_LINE(globalPkg.a || ',' || globalPkg.b);
    END p2;

    CREATE OR REPLACE PROCEDURE root.p3 AS
    BEGIN
       globalPkg.a := globalPkg.a + 10;
       globalPkg.b := globalPkg.b - 5;

       DBMS_OUTPUT.PUT_LINE(globalPkg.a || ',' || globalPkg.b);
    END p3;

### 3.2.3 Error Handling in Stored Procedures

Both Oracle PL/SQL and MySQL implement an error handling mechanism for their stored procedures. Each SQL statement in the stored procedure is checked for errors before the next statement is processed. If an error occurs, control immediately is passed to an error handler. For example, if a SELECT statement does not find any rows in the database, an error is raised, and the code to deal with this error is executed.

In MySQL stored procedures, handlers can be defined to deal with errors or warnings that occurs from executing a SQL statement within a stored procedure. MySQL allows two types of handlers: CONTINUE handlers and EXIT handlers. The two types of handlers differ from their next point of execution in the stored procedure after the handler is run. For a CONTINUE handler, execution continue at the next statement after the statement that raised the error. For an EXIT handler, execution of the current compound statement, enclosed by a pair of BEGIN and END statements, is terminated and execution continues at the next statement (if any) after the compound statement.

Handlers are defined to deal with one or more conditions. A condition may be a SQLSTATE value, a MySQL error code, or a predefined condition. There are three predefined conditions: SQLWARNING (warning or note), NOT FOUND (no more rows) and SQLEXCEPTION (error). A condition may be defined separately with a name and subsequently referenced in the handler statement. All the handler definitions are made at the start of a compound statement block.

In Oracle PL/SQL stored procedures, an error condition is called an exception. Exceptions may be internally defined (by the runtime system) or user-defined. Some internal exceptions have predefined name, such as ZERO_DIVIDE or NO_DATA_FOUND. Internal exceptions are implicitly (automatically) raised by the runtime system. User-defined exceptions must be given names and must be raised explicitly by RAISE statements in the stored procedures. Exception handlers handle exceptions that are raised.

Exception handlers can be declared for a PL/SQL block. Such exception handlers are enclosed between BEGIN and END statements, and they handle exceptions that might be raised by statements in the PL/SQL block, including sub-blocks. A PL/SQL block is similar to a MySQL compound statement block. Exceptions can be declared only in the declarative part of a PL/SQL block, and they are local to that block and global to all of its sub-blocks. Thus, the enclosing block cannot handle exceptions raised in a sub-block if they are exceptions local to the sub-block. Exceptions raised in a sub-block are not propagated to the enclosing block if exception handlers defined for sub-block handle them and if are not raised again in the exception handlers. After an exception handler runs, the current block stops executing and execution resumes at the next statement in the enclosing block.

For an example of using the error handling mechanism in MySQL and Oracle stored procedures, consider the following MySQL stored procedure:

    CREATE PROCEDURE adjust_emp_salary ()
    BEGIN
      DECLARE job_id INT;
      DECLARE employee_id INT DEFAULT 115;
      DECLARE sal_raise DECIMAL(3,2);
      DECLARE EXIT HANDLER FOR 1339;

      SELECT job_id INTO jobid FROM employees WHERE employee_id = empid;
      CASE
        WHEN jobid = 'PU_CLERK' THEN
          SET sal_raise := .09;
        WHEN jobid = 'SH_CLERK' THEN
          SET sal_raise := .08;
        WHEN jobid = 'ST_CLERK' THEN
          SET sal_raise := .07;
      END CASE;
    END;

The following is the Oracle PL/SQL equivalent.

    CREATE OR REPLACE PROCEDURE adjust_emp_salary ()
    AS
      jobid employees.job_id%TYPE;
      empid employees.employee_id%TYPE := 115;
      sal_raise NUMBER(3,2);
    BEGIN
      SELECT job_id INTO jobid from employees
        WHERE employee_id = empid;

      CASE
        WHEN jobid = 'PU_CLERK' THEN
          sal_raise := .09;
        WHEN jobid = 'SH_CLERK' THEN
          sal_raise := .08;
        WHEN jobid = 'ST_CLERK' THEN
          sal_raise := .07;
      END CASE;
      EXCEPTION
        WHEN CASE_NOT_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Employee salary not adjusted.');
    END;