A is a special kind of a stored procedure that executes in response to certain actions

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.

3.2.1 Individual SQL Statements

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

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

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

3 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,

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

4 and

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

5). 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

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

6 and

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

7 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

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

8 or

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

9 are global variables, and session variables name can optionally be preceded by

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

0,

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

1.,

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

2 or

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

3. System variables can be referenced in SELECT statement using the

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

4 syntax. If

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

5,

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

6, or

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

7 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

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

8 and

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

9.

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

/* Simple statement */
a:=1;
 
/* Multiple statements */
x:=1;
y:=0;
 

0 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:

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

0

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:

Which of the following are special stored procedures that are executed automatically?

Which of the following is a stored procedure that is run when a specific action occurs within a database?

What are the after triggers?

What are triggers and procedures?