This chapter is devoted to the procedural database object called the stored procedure or database procedure. We start by giving its definition:

Thus, a stored procedure is a piece of code. This code can consist of declarative SQL statements, such as CREATE, UPDATE, and SELECT, possibly complemented with procedural statements, such as IF-THEN-ELSE and WHILE-DO. The code from which a stored procedure has been built is, therefore, not a part of a program, but is stored in the catalog.

Calling a stored procedure is comparable to calling a “normal” procedure (otherwise called a function or routine) in procedural languages. For calling stored procedures, a new SQL statement has been introduced. When calling stored procedures, you can also specify input and output parameters. As the definition indicates, stored procedures can be called from other stored procedures, just as functions in C can call other functions. The definition states that stored procedures can also be activated from triggers; we return to this subject in Chapter 32, “Triggers.”

The stored procedure offers the possibility of storing certain parts of a program centrally in the catalog of the database server. They can then be called from all programs. For this reason, a database server that supports stored procedures is sometimes also called a programmable database server.

We can best illustrate what a stored procedure is and show its possibilities with a number of examples. Therefore, this chapter describes several examples of increasing complexity.

Before we begin with a simple example, to process stored procedures and other procedural objects, we must change a property in WinSQL (that is, if you use this product). The terminator string must be switched to a symbol other than the semicolon. The reason is that the semicolon is used within procedural database objects. Therefore, change it, for example, to the number sign (#); see Figure 30.1.

Figure 30.1. The terminator string in WinSQL

CREATE PROCEDURE DELETE_MATCHES
   (IN P_PLAYERNO INTEGER)
BEGIN
   DELETE
   FROM   MATCHES
   WHERE  PLAYERNO = P_PLAYERNO;
END

Explanation. The CREATE PROCEDURE statement is actually one SQL statement, just as CREATE TABLE and SELECT are. The statement is made up of several other SQL statements. We return to this subject and discuss it extensively later in this chapter. Each stored procedure consists of at least three parts: a list of parameters, a body, and a name.

The previous procedure has only one parameter, called P_PLAYERNO (the player number). The word IN indicates that this parameter is an input parameter. The value of this parameter can be used within the procedure, but after the execution of the procedure, the variable that is used at the call will stay unchanged.

Between the keywords BEGIN and END, the procedure body is specified. In this example, the body is very simple because it consists of only a single DELETE statement. New in this statement is the use of the parameter P_PLAYERNO. Here is the rule: Everywhere a scalar expression is allowed, a parameter may be used.

In most products, the names of the procedures within a database have to be unique, just as with the names of users. However, this requirement does not apply to all the products. In Oracle, for example, procedures may have the same name, but then the parameter lists have to be different. In that case, the number of parameters must be different, or, if the number of parameters is equal, the data types of the parameters must be different. If a procedure is called, one of the procedures is activated, depending on the parameter list.

The result of the previous CREATE PROCEDURE statement is not that the DELETE statement is executed. The only thing that happens is that the syntax of the statement is verified and, if it is correct, it is stored in the catalog. This is comparable to creating views.

To activate a stored procedure, a separate SQL statement must be used: the CALL statement.

CALL DELETE_MATCHES (8)

Explanation: This statement is straightforward. The value of the player number that is assigned to the parameter P_PLAYERNO is included between the brackets. If we compare this with classic programming languages, the CREATE PROCEDURE statement is comparable to the declaration of a procedure, and with CALL, the procedure is invoked.

Figure 30.2 shows in a graphical way how a stored procedure is processed. The left block represents the program from which the procedure is called, the middle block represents the database server, and the right side represents the database and its catalog. The process begins when the procedure is called from the program (step 1). The database server receives this call and finds the matching procedure in the catalog (step 2). Next, the procedure is executed (step 3). This can result in inserting new rows or, in the situation of the DELETE_MATCHES procedure, removing rows. If the procedure is finished, a code is returned indicating that the procedure was processed correctly (step 4). No communication takes place between the database server and the program during the execution of the procedure.

Figure 30.2. The processing steps of a stored procedure

How the database server actually calls and processes the stored procedure is not important to the programmer or the program. The processing of a stored procedure can be seen as an extension of the processing of the program itself. Imagine that a program calling the stored procedure DELETE_MATCHES looks as follows:

Answer := 'Y';
WHILE answer = 'Y' DO
   PRINT 'Do you want to remove all matches of another
          player (Y/N)? '
   READ answer
   IF answer = 'Y' THEN
      PRINT 'Enter a player number: ';
      READ pno;
      CALL DELETE_MATCHES(pno);
   ENDIF;
ENDWHILE;

The final result of this program is the same as if we replaced the stored procedure call with the body of the procedure itself:

Answer := 'Y';
WHILE answer = 'Y' DO
   PRINT 'Do you want to remove all matches of another
          player (Y/N)? '
   READ answer
   IF answer = 'Y' THEN
      PRINT 'Enter a player number: ';
      READ pno;
      DELETE
      FROM   MATCHES
      WHERE  PLAYERNO = :pno;
   ENDIF;
ENDWHILE;

In the following sections, we describe the features and syntax of stored procedures step by step, along with the statements that can be used within the body of a stored procedure.

A stored procedure has zero, one, or more parameters. Through these parameters, the procedure is capable of communicating with the outside world. Three types of parameters are supported. With input parameters, data can be passed to a stored procedure. The procedure in Example 30.1, for example, contained one input parameter: the player number of the matches that must be removed. The stored procedure uses output parameters when an answer or result must be returned. For example, we could create a stored procedure that finds the name of a player. That name is the output parameter then. The third type is the input/output parameter. As the name suggests, this parameter can act as input as well as an output parameter.

<create procedure statement> ::=
   CREATE PROCEDURE <procedure name> ( [
 <parameter list> ] )
      <procedure body>

<parameter list> ::=
   <parameter specification>
      [ { , <parameter specification> }... ]

<parameter specification> ::=
   [ IN | OUT | INOUT ] <parameter> <data type>

Make sure that the names of parameters are not equal to the names of columns. If we want to change P_PLAYERNO in the previous example into PLAYERNO, SQL will not return an error message. The DELETE statement will consider the second PLAYERNO as the name of the column, not of the parameter. As a result, with every call, the stored procedure will remove all the players.

The body of a stored procedure contains all the statements that must be executed when the procedure is called. The body always begins with the word BEGIN and ends with END. In between, all statement types can be specified. These can be the well-known SQL statements from the previous chapters—thus, all DDL, DCL, and DML statements, and also the procedural statements, are allowed as well. These are other versions of statements that we see in all procedural programming languages, such as IF-THEN-ELSE and WHILE DO. As with embedded SQL, there are statements to declare and update cursors. Local variables can be declared, and it is possible to assign values to them.

<create procedure statement> ::=
   CREATE PROCEDURE <procedure name> ( [
 <parameter list> ] )
      <procedure body>

<procedure body> ::= <begin-end block>

<begin-end block> ::=
   [ <label> : ] BEGIN <statement list> END [ 
<label> ]

<statement list> ::= { <body statement> ; }...

<statement in body::=
   <declarative statement> |
   <procedural statement>

<declarative statement> ::=
   <call statement>              |
   <close statement>             |
   <commit statement>            |
   <delete statement>            |
   <execute immediate statement> |
   <fetch statement>             |
   <insert statement>            |
   <lock table statement>        |
   <open statement>              |
   <rollback statement>          |
   <savepoint statement>         |
   <select statement>            |
   <select into statement>       |
   <set statement>               |
   <set transaction statement>   |
   <star -transaction statement> |
   <update statement>

<procedural statement> ::=
   <begin-end block>             |
   <call statement>              |
   <close statement>             |
   <declare condition statement> |
   <declare cursor statement>    |
   <declare handler statement>   |
   <declare variable statement>  |
   <fetch cursor statement>      |
   <flow control statement>      |
   <open cursor statement>       |
   <set statement>

With a begin-end block, statements can be grouped into one statement. Sometimes, such a block is called a compound statement. The body of a stored procedure is, in fact, a begin-end block. Blocks may be nested. In other words, you can define subblocks within begin-end blocks. So, this is a legal body of a stored procedure.

BEGIN
   BEGIN
      BEGIN
      END;
   END;
END

Note that each statement, including each begin-end block must end with a semicolonbegin-end. However, this is not required for the begin-end block that indicates the end of the procedure body.

A label may be assigned to a begin-end block. In fact, the block is named with it:

BLOCK1 : BEGIN
   BLOCK2 : BEGIN
      BLOCK3 : BEGIN
      END BLOCK1;
   END BLOCK2;
END BLOCK3

Labeling blocks has two advantages. First, labeling makes it easier to determine which BEGIN belongs to which END, especially when many blocks are used within a stored procedure. Second, certain SQL statements, such as LEAVE and ITERATE, need these names. We return to this topic in Section 30.7.

A closing label behind END is not necessary. However, if it is used, it must refer to a label that stands in front of a BEGIN. The following code is not allowed, for example:

BLOCK1 : BEGIN
   SET VAR1 = 1;
END BLOCK2

The following statement is not correct, either. The name of the closing label BLOCK2 does exist, but it belongs to the wrong BEGIN:

BLOCK1 : BEGIN
   BLOCK2 : BEGIN
      SET VAR1 = 1;
   END
END BLOCK2

Within a stored procedure, local variables can be declared. They can be used to keep temporary intermediate results. If we need a local variable within a stored procedure, we must introduce it first with a DECLARE statement. So, SQL is different from similar languages such as PHP, in which a variable, if it is used, is declared implicitly.

With a declaration, the data type of the variable is determined and an initial value can be specified. The data types that are supported are the ones that may be used in CREATE TABLE statements; see Section 15.3, in Chapter 15, “Creating Tables.”

<declare variable statement> ::=
   DECLARE <variable list> <data type> [
      DEFAULT <expression> ]

<variable list> ::=
   <variable> [ { , <variable> }... ]

DECLARE NUM1 DECIMAL(7,2);
DECLARE ALPHA1 VARCHAR(20);

DECLARE NUMBER1, NUMBER2 INTEGER;

CREATE PROCEDURE TEST
   (OUT NUMBER1 INTEGER)
BEGIN
   DECLARE NUMBER2 INTEGER DEFAULT 100;
   SET NUMBER1 = NUMBER2;
END

CALL TEST (@NUMBER)

SELECT @NUMBER

@NUMBER
-------
    100

Explanation. If DECLARE statements are used, they must be included as the first statements of a begin-end block. @NUMBER is a user variable.

The expression for the default value is not limited to literals but may consist of compound expressions, including scalar subqueries.

CREATE PROCEDURE TEST
   (OUT NUMBER1 INTEGER)
BEGIN
   DECLARE NUMBER2 INTEGER
      DEFAULT (SELECT COUNT(*) FROM PLAYERS);
   SET NUMBER1 = NUMBER2;
END

B1 : BEGIN
   DECLARE V1 INTEGER;
   B2 : BEGIN
      DECLARE V2 INTEGER;
      SET V2 = 1;
      SET V1 = V2;
   END B2;
   B3 : BEGIN
      SET V1 = V2;
   END B3;
   SET V2 = 100;
END B1

Do not confuse local variables with user variables. The first difference is that, in front of local variables, no @ symbol is placed. Another difference is that user variables exist during the entire session. Local variables disappear immediately after the processing of the begin-end block in which they have been declared is finished. User variables can be used within and outside a stored procedure, whereas local variables have no meaning outside a procedure.

For your information, SQL does not support arrays as local variables.

The SET statement can be used to assign a value to local variables. The rule that any expression can be used applies here as well.

<set statement> ::=
   SET <local variable definition>
       [ {, <local variable definition> }... ]

<local variable definition> ::=
   <local variable> { = | := } <scalar expression>

In the previous sections, we showed several examples of the SET statement. The following examples are also correct:

SET VAR1 = 1;
SET VAR1 := 1;
SET VAR1 = 1, VAR2 = VAR1;

In the last example, a value is assigned to VAR1 first, and that value is assigned to VAR2 via VAR1 next.

Within the body of a stored procedure, the well-known procedural statements can be used. Their definitions are specified here:

<flow control statement> ::=
   <if statement>      |
   <case statement>    |
   <while statement>   |
   <repeat statement>  |
   <loop statement>    |
   <leave statement>   |
   <iterate statement>

<if statement> ::=
   IF <condition> THEN <statement list>
      [ ELSEIF <condition> THEN <statement list> ]...
      [ ELSE <statement list> ]
   END IF

<case statement> ::=
   { CASE <expression>
        WHEN <expression> THEN <statement list>
        [ WHEN <expression> THEN <statement list> ]...
        [ ELSE <statement list> ]
     END CASE } |
   { CASE
        WHEN <condition> THEN <statement list>
        [ WHEN <condition> THEN <statement list> ]...
        [ ELSE <statement list>
     END CASE }

<while statement> ::=
   [ <label> : WHILE <condition> DO <statement list>
   END WHILE [ <label> ]

<repeat statement> ::=
   [ <label> : ] REPEAT <statement list>
   UNTIL <condition>
   END REPEAT <label>

<loop statement> ::=
   [ <label> : ] LOOP <statement list>
   END LOOP [ <label> ]

<leave statement> ::= LEAVE <label>

<iterate statement> ::= ITERATE <label>

<statement list> ::= { <statement in body> ; }...

<begin-end block> ::=
   [ <label> : ] BEGIN <statement list> END [ 
<label> ]

<label> ::= <name>

Let’s begin with examples of the IF statement.

CREATE PROCEDURE DIFFERENCE
   (IN P1 INTEGER,
    IN P2 INTEGER,
    OUT P3 INTEGER)
BEGIN
   IF P1 > P2 THEN
      SET P3 = 1;
   ELSEIF P1 = P2 THEN
      SET P3 = 2;
   ELSE
      SET P3 = 3;
   END IF;
END

Explanation: The ELSE clause is not mandatory, and you may specify many ELSEIF clauses.

CREATE PROCEDURE FIBONNACI
   (INOUT NUMBER1 INTEGER,
    INOUT NUMBER2 INTEGER,
    INOUT NUMBER3 INTEGER)
BEGIN
   SET NUMBER3 = NUMBER1 + NUMBER2;
   IF NUMBER3 > 10000 THEN
      SET NUMBER3 = NUMBER3 - 10000;
   END IF;
   SET NUMBER1 = NUMBER2;
   SET NUMBER2 = NUMBER3;
END

SET @A=16, @B=27

CALL FIBONNACI(@A,@B,@C)

SELECT @C

CALL FIBONNACI(@A,@B,@C)

SELECT @C

CALL FIBONNACI(@A,@B,@C)

SELECT @C

number1 := 16;
number2 := 27;

counter := 1;
while counter <= 10 do
   CALL FIBONNACI (:number1, :number2, :number3);
   print 'The number is ', number3;
   counter := counter + 1;
endwhile;

CREATE PROCEDURE LARGEST
   (OUT T CHAR(10))
BEGIN
   IF (SELECT COUNT(*) FROM PLAYERS) >
      (SELECT COUNT(*) FROM PENALTIES) THEN
       SET T = 'PLAYERS';
   ELSEIF (SELECT COUNT(*) FROM PLAYERS) =
          (SELECT COUNT(*) FROM PENALTIES) THEN
      SET T = 'EQUAL';
   ELSE
      SET T = 'PENALTIES';
   END IF;
END

Explanation: As this example shows, conditions are allowed to contain scalar subqueries. However, this stored procedure would be more efficient if the results of the subqueries were assigned to local variables first and, subsequently, if the values of the variables were compared in the condition. In the previous example, the subqueries are sometimes executed twice.

The CASE statement makes it possible to specify complex IF-THEN-ELSE constructs. The IF statement in Example 30.7, for example, can be rewritten as follows:

CASE
   WHEN P1 > P2 THEN SET P3 = 1;
   WHEN P1 = P2 THEN SET P3 = 2;
   ELSE SET P3 = 3;
END CASE;

SQL supports three statements to create loops: the WHILE, the REPEAT, and the LOOP statements.

CREATE PROCEDURE AGE
   (IN  START_DATE   DATE,
    IN  END_DATE     DATE,
    OUT YEARS       INTEGER,
    OUT MONTHS      INTEGER,
    OUT DAYS        INTEGER)
BEGIN
    DECLARE NEXT_DATE, PREVIOUS_DATE DATE;

    SET YEARS = 0;
    SET PREVIOUS_DATE = START_DATE;
    SET NEXT_DATE = START_DATE + INTERVAL 1 YEAR;
    WHILE NEXT_DATE < END_DATE DO
       SET YEARS = YEARS + 1;
       SET PREVIOUS_DATE = NEXT_DATE;
       SET NEXT_DATE = NEXT_DATE + INTERVAL 1 YEAR;
    END WHILE;

    SET MONTHS = 0;
    SET NEXT_DATE = PREVIOUS_DATE + INTERVAL 1 MONTH;
    WHILE NEXT_DATE < END_DATE DO
       SET MONTHS = MONTHS + 1;
       SET PREVIOUS_DATE = NEXT_DATE;
       SET NEXT_DATE = NEXT_DATE + INTERVAL 1 MONTH;
    END WHILE;

    SET DAYS = 0;
    SET NEXT_DATE = PREVIOUS_DATE + INTERVAL 1 DAY;
    WHILE NEXT_DATE <= END_DATE DO
       SET DAYS = DAYS + 1;
       SET PREVIOUS_DATE = NEXT_DATE;
       SET NEXT_DATE = NEXT_DATE + INTERVAL 1 DAY;
    END WHILE;
END

SET @START = '1991-01-12'

SET @END = '1999-07-09'

CALL AGE (@START, @END, @YEAR, @MONTH, @DAY)

SELECT @START, @END, @YEAR, @MONTH, @DAY

Explanation. The first loop determines the number of intervening years, the second indicates the number of months, and the last indicates the number of days. Of course, scalar functions can be used to realize the same in a more simple way; this method is chosen only to illustrate the WHILE statement.

In a WHILE statement, a check is done first to see whether the specified condition is true; only if the condition is true is the statement executed. At the REPEAT statement, the statements are executed first; then, a check is done to see whether the condition is true. The first WHILE statement from Example 30.10 can be rewritten as follows:

SET YEARS = -1;
SET NEXT_DATE = START_DATE;
REPEAT
   SET PREVIOUS_DATE = NEXT_DATE;
   SET NEXT_DATE = PREVIOUS_DATE + INTERVAL 1 YEAR;
   SET YEARS = YEARS + 1;
UNTIL NEXT_DATE > END_DATE END REPEAT;

Before we explain the LOOP statement, we describe the LEAVE statement. The LEAVE statement can be used to stop the processing of a begin-end block early. However, the relevant block must have a label.

CREATE PROCEDURE SMALL_EXIT
   (OUT P1 INTEGER, OUT P2 INTEGER)
BEGIN
   SET P1 = 1;
   SET P2 = 1;
   BLOCK1 : BEGIN
      LEAVE BLOCK1;
      SET P2 = 3;
   END;
   SET P1 = 4;
END

If we call this stored procedure, the value of the second parameter will be equal to 1 and the value of P1 will be equal to 4. The SET statement that comes right after the LEAVE statement is not executed, contrary to the SET statement specified after BLOCK1 that is actually executed.

With the LOOP statement, we do not use a condition—we use a LEAVE statement to end the loop.

The first WHILE statement from Example 30.10 can be rewritten as follows:

SET YEARS = 0;
SET PREVIOUS_DATE = START_DATE;
SET NEXT_DATE = START_DATE + INTERVAL 1 YEAR;
YEARS_LOOP: LOOP
   IF NEXT_DATE > END_DATE THEN
      LEAVE YEARS_LOOP;
   END IF;
   SET YEARS = YEARS + 1;
   SET PREVIOUS_DATE = NEXT_DATE;
   SET NEXT_DATE = NEXT_DATE + INTERVAL 1 YEAR;
END LOOP YEARS_LOOP;

CREATE PROCEDURE WAIT
   (IN WAIT_SECONDS INTEGER)
BEGIN
   DECLARE END_TIME INTEGER
      DEFAULT NOW() + INTERVAL WAIT_SECONDS SECOND;
   WAIT_LOOP: LOOP
      IF NOW() > END_TIME THEN
         LEAVE WAIT_LOOP;
      END IF;
   END LOOP WAIT_LOOP;
END

Explanation: If we call this stored procedure with CALL(5), SQL checks whether the 5 seconds have passed. If so, we leave the loop with the LEAVE statement.

The ITERATE statement is the counterpart of the LEAVE statement. The difference between the two is that, with the LEAVE statement, we leave a loop early, whereas we restart the loop with ITERATE.

CREATE PROCEDURE AGAIN
   (OUT RESULT INTEGER)
BEGIN
   DECLARE COUNTER INTEGER DEFAULT 1;
   SET RESULT = 0;
   LOOP1: WHILE COUNTER <= 1000 DO
      SET COUNTER = COUNTER + 1;
      IF COUNTER > 100 THEN
         LEAVE LOOP1;
      ELSE
         ITERATE LOOP1;
      END IF;
      SET RESULT = COUNTER * 10;
   END WHILE LOOP1;
END

Explanation. The value of the parameter RESULT will always be equal to 0. The stored procedure will never come at the statement SET RESULT = COUNTER * 10. The reason is that the IF statement leads to the processing of the LEAVE statement (and then we leave the loop) or to the processing of the ITERATE statement. In that case, the processing jumps again to the loop with the name LOOP1.

A procedure can be called from a program, from interactive SQL, and from stored procedures. In all three cases, the CALL statement is used.

<call statement> ::=
   CALL [ <database name> . ] <stored procedure name>
      ( <expression list> )

<expression list> ::= <expression> [ { , 
<expression> }... ]

Even though the statement is not complex, there are certain rules. The number of expressions in the expression list must always be equal to the number of parameters of the stored procedure. In front of the procedure name, the name of a database may be specified. SQL automatically places that same database name in all the DML statements in front of all table names. This does not apply when a database name is explicitly specified in front of a table name, of course.

Any scalar expression may be used as the input parameter of a stored procedure. SQL calculates the value of that expression before the value is passed on to the procedure.

CALL WAIT ((SELECT COUNT(*) FROM PENALTIES))

CREATE TABLE PLAYERS_WITH_PARENTS
      (PLAYERNO         INTEGER NOT NULL PRIMARY KEY,
       FATHER_PLAYERNO  INTEGER,
       MOTHER_PLAYERNO  INTEGER)

ALTER TABLE PLAYERS_WITH_PARENTS ADD
   FOREIGN KEY (FATHER_PLAYERNO)
      REFERENCES PLAYERS_WITH_PARENTS (PLAYERNO)

ALTER TABLE PLAYERS_WITH_PARENTS ADD
   FOREIGN KEY (MOTHER_PLAYERNO)
      REFERENCES PLAYERS_WITH_PARENTS (PLAYERNO)

INSERT INTO PLAYERS_WITH_PARENTS VALUES
   (9,NULL,NULL), (8,NULL,NULL), (7,NULL,NULL),
   (6,NULL,NULL), (5,NULL,NULL), (4,8,9), (3,6,7),
   (2,4,5), (1,2,3)

Figure 30.3. The family relationships between several players

CREATE PROCEDURE TOTAL_NUMBER_OF_PARENTS
   (IN P_PLAYERNO INTEGER,
    INOUT NUMBER INTEGER)
BEGIN
   DECLARE V_FATHER, V_MOTHER INTEGER;
   SET V_FATHER =
      (SELECT   FATHER_PLAYERNO
       FROM     PLAYERS_WITH_PARENTS
       WHERE    PLAYERNO = P_PLAYERNO);
   SET V_MOTHER =
      (SELECT   MOTHER_PLAYERNO
       FROM     PLAYERS_WITH_PARENTS
       WHERE    PLAYERNO = P_PLAYERNO);

   IF V_FATHER IS NOT NULL THEN
      CALL TOTAL_NUMBER_OF_PARENTS (V_FATHER, NUMBER);
      SET NUMBER = NUMBER + 1;
   END IF;

   IF V_MOTHER IS NOT NULL THEN
      CALL TOTAL_NUMBER_OF_PARENTS (V_MOTHER, NUMBER);
      SET NUMBER = NUMBER + 1;
   END IF;
END

SET @NUMBER = 0

CALL TOTAL_NUMBER_OF_PARENTS (1, @NUMBER)

SELECT @NUMBER

Explanation. The result of the last SELECT statement is 8. Apart from the way this procedure works, you can clearly see the recursive style of calling procedures. But how does it work precisely? We assume that the procedure is called with the number of a player—for example, 27—as the first parameter and a variable in which the number of ancestors is recorded as the second parameter. However, this variable first must be initialized and set to 0; otherwise, the procedure will not work correctly. The first SELECT statement determines the player numbers of the father and mother. If the father is indeed a member of the club, the procedure TOTAL_NUMBER_OF_PARENTS is again called (recursively), this time with the player number of the father as the input parameter. When this procedure has finished, the number of ancestors of the father is shown. Next, we add 1 because the father himself must also be counted as the ancestor of the child. Thus, it is possible that, for the father, TOTAL_NUMBER_OF_PARENTS is activated for the third time, because he, in turn, has a father or mother who is still a member of the club. After the number of ancestors has been determined for the father, the same is done for the mother.

In practice, the need to walk through a hierarchy from top to bottom, or vice versa, and perform calculations occurs often. A production company, for example, records which products are a part of other products. A car consists of, among other things, a chassis and an engine. The engine itself contains sparking plugs, a battery, and other parts, and this hierarchy goes on and on. Another example involves departments in large companies. Departments consist of smaller departments, which, in turn, consist of even smaller departments. And there are many more examples to think of.

Just as with embedded SQL, the results of SELECT statements within stored procedures can be retrieved in two ways. If the SELECT statement is guaranteed to return one row at the most, the SELECT INTO statement can be used.

CREATE PROCEDURE TOTAL_PENALTIES_PLAYER
   (IN P_PLAYERNO INTEGER,
    OUT TOTAL_PENALTIES DECIMAL(8,2))
BEGIN
   SELECT SUM(AMOUNT)
   INTO   TOTAL_PENALTIES
   FROM   PENALTIES
   WHERE  PLAYERNO = P_PLAYERNO;
END

CALL TOTAL_PENALTIES_PLAYER (27, @TOTAL)

SELECT @TOTAL

Explanation: The result of the SELECT INTO statement is immediately assigned to the output parameter TOTAL_PENALTIES.

Another example in which the SELECT INTO statement could be used well is Example 30.15. The first two SET statements with subqueries could be replaced by one SELECT INTO statement, to improve the processing speed:

SELECT    FATHER_PLAYERNO, MOTHER_PLAYERNO
INTO      V_FATHER, V_MOTHER
FROM      PLAYERS_WITH_PARENTS
WHERE     PLAYERNO = P_PLAYERNO

CREATE PROCEDURE GIVE_ADDRESS
   (IN  P_PLAYERNO SMALLINT,
    OUT P_STREET VARCHAR(30),
    OUT P_HOUSENO CHAR(4),
    OUT P_TOWN VARCHAR(30),
    OUT P_POSTCODE CHAR(6))
BEGIN
   SELECT TOWN, STREET, HOUSENO, POSTCODE
   INTO   P_TOWN, P_STREET, P_HOUSENO, P_POSTCODE
   FROM   PLAYERS
   WHERE  PLAYERNO = P_PLAYERNO;
END

CREATE TABLE FIBON
      (NUMBER1   INTEGER NOT NULL PRIMARY KEY,
       NUMBER2   INTEGER NOT NULL)

CREATE PROCEDURE FIBONNACI_START()
BEGIN
   DELETE FROM FIBON;
   INSERT INTO FIBON (NUMBER, NUMBER2) VALUES (16, 27);
END

CREATE PROCEDURE FIBONNACI_GIVE
   (INOUT NUMBER INTEGER)
BEGIN
   DECLARE N1, N2 INTEGER;
   SELECT NUMBER1, NUMBER2
   INTO   N1, N2
   FROM   FIBON;
   SET NUMBER = N1 + N2;
   IF NUMBER > 10000 THEN
      SET NUMBER = NUMBER - 10000;
   END IF;
   SET N1 = N2;
   SET N2 = NUMBER;
   UPDATE FIBON
   SET    NUMBER1 = N1,
          NUMBER2 = N2;
END

CALL FIBONNACI_START()

CALL FIBONNACI_GIVE(@C)

SELECT @C

CALL FIBONNACI_GIVE(@C)

SELECT @C

CALL FIBONNACI_GIVE(@C)

SELECT @C

CREATE PROCEDURE DELETE_PLAYER
   (IN P_PLAYERNO INTEGER)
BEGIN
   DECLARE NUMBER_OF_ PENALTIES INTEGER;
   DECLARE NUMBER_OF_TEAMS INTEGER;
   SELECT COUNT(*)
   INTO   NUMBER_OF_PENALTIES
   FROM   PENALTIES
   WHERE  PLAYERNO = P_PLAYERNO;

   SELECT COUNT(*)
   INTO   NUMBER_OF_TEAMS
   FROM   TEAMS
   WHERE  PLAYERNO = P_PLAYERNO_;

   IF NUMBER_OF_PENALTIES = 0 AND NUMBER_OF_TEAMS = 0 THEN
      CALL DELETE_MATCHES (P_PLAYERNO);
      DELETE FROM PLAYERS
      WHERE PLAYERNO = P_PLAYERNO;
   END IF;
END

This stored procedure can be optimized by checking, after the first SELECT statement, whether the number of penalties is not equal to zero. If this is the case, the procedure can be interrupted because the second SELECT statement is no longer necessary.

All the error messages supported by SQL have a unique code, called the SQL error code, a piece of describing text, and a code called SQLSTATE, which has been added to comply with the SQL standard. The SQLSTATE codes are not unique; several error codes can have the same SQLSTATE. For example, SQLSTATE 23000 belongs to, among other things, the following error codes:

Error 1022—. Can’t write; duplicate key in table

Error 1048—. Column cannot be null

Error 1052—. Column is ambiguous

Error 1062—. Duplicate entry for key

The manuals of SQL list all the error messages and their respective codes.

Processing SQL statements in stored procedures can lead to error messages. For example, when a new row is added but the value in the primary key already exists, or an index is removed that does not exist, SQL stops the processing of the stored procedure. We illustrate this with an example.

CREATE PROCEDURE DUPLICATE
   (OUT P_PROCESSED SMALLINT)
BEGIN
   SET P_PROCESSED = 1;
   INSERT INTO TEAMS VALUES (2,27,'third'),
   SET P_PROCESSED = 2;
END

CALL DUPLICATE(PROCESSED)

Explanation: Because team 2 already exists, the INSERT statement results in an error message. SQL stops the processing of the stored procedure right away. The last SET statement is no longer processed, and the parameter PROCESSED is not set to 2.

With a special version of the DECLARE statement, the DECLARE HANDLER statement, we can prevent SQL from stopping the processing:

<declare handler statement> ::=
   DECLARE <handler type> HANDLER FOR <condition 
value list>
      <procedural statement>

<handler type> ::=
   CONTINUE |
   EXIT     |
   UNDO

<condition value list> ::=
   <condition value> [ { , <condition value> }... ]

<condition value> ::=
   SQLSTATE [ VALUE ] <sqlstate value> |
   <mysql error code>                  |
   SQLWARNING                          |
   NOT FOUND                           |
   SQLEXCEPTION                        |
   <condition name>

With the DECLARE HANDLER statement, a so-called handler is defined. A handler indicates what should happen if the processing of an SQL statement leads to a certain error message. The definition of a handler consists of three parts: the type of handler, the condition, and the action.

Three types of handlers exist: CONTINUE, EXIT, and UNDO. When we specify a CONTINUE handler, SQL does not interrupt the processing of the stored procedure, whereas the processing is indeed stopped with an EXIT handler.

CREATE PROCEDURE SMALL_MISTAKE1
   (OUT ERROR CHAR(5))
BEGIN
   DECLARE CONTINUE HANDLER FOR SQLSTATE '23000'
      SET ERROR = '23000';
   SET ERROR = '00000';
   INSERT INTO TEAMS VALUES (2,27,'third'),
END

Explanation. After the call of this stored procedure, the ERROR parameter has the value 23000. But how does it work? Obviously, the INSERT statement leads to an error message of which the code is 23000. When an error occurs, SQL checks whether a handler has been defined for this code, which happens to be the case in this example. Next, SQL executes the additional statement belonging to the DECLARE statement (SET ERROR = '23000'). After that, SQL checks what kind of handler it is; in this case, it is a CONTINUE handler. Because of this, the processing of the stored procedure goes on where it was. If the INSERT statement could have been executed without mistakes, the ERROR parameter would have had the value 00000.

You are allowed to define several handlers within a stored procedure, provided that they apply to different error messages.

CREATE PROCEDURE SMALL_MISTAKE2
   (OUT ERROR CHAR(5))
BEGIN
   DECLARE CONTINUE HANDLER FOR SQLSTATE '23000'
      SET ERROR = '23000';
   DECLARE CONTINUE HANDLER FOR SQLSTATE '21S01'
      SET ERROR = '21S01';
   SET ERROR = '00000';
   INSERT INTO TEAMS VALUES (2,27,'third',5);
END

Explanation: The error message with SQLSTATE code 21S01 is returned if the number of values in the INSERT statement does not comply with the number of columns in the table. In this example, the output parameter will have the value 21S01 when the procedure is processed.

Instead of an SQLSTATE code, you can also define an error code. The handlers in the previous example could have been defined as follows:

DECLARE CONTINUE HANDLER FOR 1062 SET ERROR = '23000';
DECLARE CONTINUE HANDLER FOR 1136 SET ERROR = '21S01';

The SQLWARNING handler is activated for all SQLSTATE codes beginning with 01, the NOT FOUND handler for all codes beginning with 02, and the SQLEXCEPTION handler for all codes that do not begin with 01 or 02. The three handlers can be used when we do not want to define a separate handler for every error message possible.

CREATE PROCEDURE SMALL_MISTAKE3
   (OUT ERROR CHAR(5))
BEGIN
   DECLARE CONTINUE HANDLER FOR SQLWARNING, NOT FOUND,
      SQLEXCEPTION SET ERROR = 'XXXXX';
   SET ERROR = '00000';
   INSERT INTO TEAMS VALUES (2,27,'third'),
END

<declare condition statement> ::=
   DECLARE <condition name> CONDITION FOR
   { SQLSTATE [ VALUE ] <sqlstate value> } |
      <mysql error code> }

CREATE PROCEDURE SMALL_MISTAKE4
   (OUT ERROR CHAR(5))
BEGIN
   DECLARE NON_UNIQUE CONDITION FOR SQLSTATE '23000';
   DECLARE CONTINUE HANDLER FOR NON_UNIQUE
      SET ERROR = '23000';
   SET ERROR = '00000';
   INSERT INTO TEAMS VALUES (2,27,'third'),
END

Explanation: The condition NON_UNIQUE can be used instead of the SQLSTATE code.

Handlers and conditions can be defined within each begin-end block. A handler is relevant for all SQL statements that belong to the same block, plus all its subblocks.

CREATE PROCEDURE SMALL_MISTAKE5
   (OUT ERROR CHAR(5))
BEGIN
   DECLARE NON_UNIQUE CONDITION FOR SQLSTATE '23000';
   DECLARE CONTINUE HANDLER FOR NON_UNIQUE
      SET ERROR = '23000';
   BEGIN
      DECLARE CONTINUE HANDLER FOR NON_UNIQUE
         SET ERROR = '23000';
   END;
   BEGIN
      DECLARE CONTINUE HANDLER FOR NON_UNIQUE
         SET ERROR = '00000';
      INSERT INTO TEAMS VALUES (2,27,'third'),
   END;
END

Explanation: In this procedure, the parameter ERROR will have the value 00000 when something goes wrong with the INSERT statement.

In fact, the rules for the range of handlers are equivalent to those of declared variables.

Two or more handlers cannot be defined for the same error message and within the same begin-end block. For example, the following two statements in the same stored procedure are not allowed:

DECLARE CONTINUE HANDLER FOR SQLSTATE '23000'
   SET ERROR = '23000';
DECLARE EXIT HANDLER FOR SQLSTATE '23000'
   SET ERROR = '24000';

However, the same handler can be defined in a subblock; see the following example:

CREATE PROCEDURE SMALL_MISTAKE6 ()
BEGIN
   DECLARE CONTINUE HANDLER FOR SQLSTATE '23000'
      SET @PROCESSED = 100;
   BEGIN
      DECLARE CONTINUE HANDLER FOR SQLSTATE '23000'
         SET @PROCESSED = 200;
      INSERT INTO TEAMS VALUES (2,27,'third'),
   END;
END

If the processing of the INSERT statement goes wrong, SQL checks whether a relevant DECLARE statement occurs within that same begin-end block. If so, it is activated; otherwise, SQL tries to find a relevant handler in the surrounding begin-end block.

To process a SELECT statement of which the result possibly contains more than one row, cursors must be used, just as with embedded SQL.

<declare cursor statement> ::=
   DECLARE <cursor name> CURSOR FOR <table expression>

<open statement> ::=
   OPEN <cursor name>

<fetch statement> ::=
   FETCH <cursor name> INTO <variable> [ { , 
<variable> }... ]

<close statement> ::=
   CLOSE <cursor name>

CREATE PROCEDURE NUMBER_OF_PLAYERS
   (OUT NUMBER INTEGER)
BEGIN
   DECLARE A_PLAYERNO INTEGER;
   DECLARE FOUND BOOLEAN DEFAULT TRUE;
   DECLARE C_PLAYERS CURSOR FOR
      SELECT PLAYERNO FROM PLAYERS;
   DECLARE CONTINUE HANDLER FOR NOT FOUND
      SET FOUND = FALSE;
   SET NUMBER = 0;
   OPEN C_PLAYERS;
   FETCH C_PLAYERS INTO A_PLAYERNO;
   WHILE FOUND DO
      SET NUMBER = NUMBER + 1;
      FETCH C_PLAYERS INTO A_PLAYERNO;
   END WHILE;
   CLOSE C_PLAYERS;
END

Explanation. The WHILE statement can be used to browse the result of the cursor row by row while the variable FOUND is true. If a FETCH statement does not produce a result, the CONTINUE handler is activated and FOUND is set to false. This stops the WHILE statement.

CREATE PROCEDURE DELETE_OLDER_THAN_30()
BEGIN
   DECLARE V_AGE, V_PLAYERNO,V_YEARS,
      V_MONTHS, V_DAYS INTEGER;
   DECLARE V_BIRTH_DATE DATE;
   DECLARE FOUND BOOLEAN DEFAULT TRUE;
   DECLARE C_PLAYERS CURSOR FOR
      SELECT PLAYERNO, BIRTH_DATE
      FROM PLAYERS;
   DECLARE CONTINUE HANDLER FOR NOT FOUND
      SET FOUND = FALSE;
   OPEN C_PLAYERS;
   FETCH C_PLAYERS INTO V_PLAYERNO, V_BIRTH_DATE;
   WHILE FOUND DO
      CALL AGE (V_BIRTH_DATE, NOW(), V_YEARS,
         V_MONTHS, V_DAYS);
      IF V_YEARS > 30 THEN
         DELETE FROM PENALTIES WHERE PLAYERNO = V_PLAYERNO;
      END IF;
      FETCH C_PLAYERS INTO V_PLAYERNO, V_BIRTH_DATE;
   END WHILE;
   CLOSE C_PLAYERS;
END

Explanation. With cursor C_PLAYERS, we walk through the PLAYERS table. If the age of a player concerned is greater than 30, we remove that player’s penalties.

CREATE PROCEDURE TOP_THREE
   (IN P_PLAYERNO INTEGER,
    OUT OK BOOLEAN)
BEGIN
   DECLARE A_PLAYERNO, BALANCE, SEQNO INTEGER;
   DECLARE FOUND BOOLEAN;
   DECLARE BALANCE_PLAYERS CURSOR FOR
      SELECT   PLAYERNO, SUM(WON) - SUM(LOST)
      FROM     MATCHES
      GROUP BY PLAYERNO
      ORDER BY 2;
   DECLARE CONTINUE HANDLER FOR NOT FOUND
      SET FOUND = FALSE;
   SET SEQNO = 0;
   SET FOUND = TRUE;
   SET OK = FALSE;
   OPEN BALANCE_PLAYERS;
   FETCH BALANCE_PLAYERS INTO A_PLAYERNO, BALANCE;
   WHILE FOUND AND SEQNO < 3 AND OK = FALSE DO
      SET SEQNO = SEQNO + 1;
      IF A_PLAYERNO = P_PLAYERNO THEN
         SET OK = TRUE;
      END IF;
      FETCH BALANCE_PLAYERS INTO A_PLAYERNO, BALANCE;
   END WHILE;
   CLOSE BALANCE_PLAYERS;
END

Explanation: The stored procedure uses a cursor to determine for each player what the difference is between the total number of sets won and the total number of sets lost (the balance). These players are ordered by balance: the player with the largest difference first and the one with the smallest last. With the WHILE statement, we “browse” through the first three rows of this result. The parameter OK has the value true if the entered player number is equal to one of the first three players.

Within stored procedures, all the well-known transaction-oriented statements can be used, such as COMMIT, ROLLBACK, and START TRANSACTION. A transaction does not begin with the start of a stored procedure, nor does it stop with the end of it. With regard to the transactions, SQL does not see the difference between SQL statements that are delivered by the applications and those that are delivered by the stored procedures. This means, for example, that when certain changes of an application are not permanent yet and a stored procedure is called that also executes some changes, all changes are part of the current transaction. It also means that if a stored procedure sends a COMMIT statement and there are still nonpermanent changes, they also are made permanent.

CREATE PROCEDURE NEW_TEAM ()
BEGIN
   INSERT INTO TEAMS VALUES (100,27,'first'),
END

SET AUTOCOMMIT = 1

START TRANSACTION

INSERT INTO TEAMS VALUES (200,27,'first')

CALL NEW_TEAM()

ROLLBACK WORK

The ROLLBACK statement is now responsible for removing the row that has been entered with the INSERT statement, and also for removing the row that has been added by the stored procedure.

We have not defined a catalog view for stored procedures; you must access the catalog of SQL directly. This catalog table is called ROUTINES.

SELECT   COLUMN_NAME
FROM     INFORMATION_SCHEMA.COLUMNS
WHERE    TABLE_SCHEMA = 'INFORMATION_SCHEMA'
AND      TABLE_NAME = 'ROUTINES'
ORDER BY ORDINAL_POSITION

COLUMN_NAME
------------------
SPECIFIC_NAME
ROUTINE_CATALOG
ROUTINE_SCHEMA
ROUTINE_NAME
ROUTINE_TYPE
DTD_IDENTIFIER
ROUTINE_BODY
ROUTINE_DEFINITION
EXTERNAL_NAME
EXTERNAL_LANGUAGE
PARAMETER_STYLE
IS_DETERMINISTIC
SQL_DATA_ACCESS
SQL_PATH
SECURITY_TYPE
CREATED
LAST_ALTERED
SQL_MODE
ROUTINE_COMMENT
DEFINER

SHOW PROCEDURE STATUS LIKE 'FIBONNACI'

SHOW CREATE PROCEDURE FIBONNACI

PROCEDURE SQL_MODE CREATE PROCEDURE
--------- -------- -------------------------------------
FIBONNACI          CREATE PROCEDURE `tennis`.`FIBONNACI`
                      (INOUT NUMBER1 INTEGER,
                       INOUT NUMBER2 INTEGER,
                       INOUT NUMBER3 INTEGER)
                   BEGIN
                      SET NUMBER3 = NUMBER1 + NUMBER2;
                      IF NUMBER3 > 10000 THEN
                         SET NUMBER3 = NUMBER3 - 10000;
                      END IF;
                      SET NUMBER1 = NUMBER2;
                      SET NUMBER2 = NUMBER3;
                   END

Just as for tables, views, and indexes, it is possible to remove stored procedures from the catalog. For this, SQL supports the DROP PROCEDURE statement.

<drop procedure statement> ::=
   DROP PROCEDURE [ <database name> . ] <procedure
 name>

DROP PROCEDURE DELETE_PLAYER

Most SQL products enable you to create multiple tables in one database with the same name, provided that they have different owners. If a table name is specified in a stored procedure without being qualified by the owner, which table is actually meant? The answer to this question is different for each product. For Oracle and Ingres, for example, the procedure is compiled the moment the stored procedure is created. This implies that during creation it is determined which tables should be accessed when the procedure is called. Imagine that two users—one named John—created a table with the name PLAYERS. If John’s procedure is called, John’s PLAYERS table will also be accessed, regardless of who called that procedure.

So, this holds for, among others, Oracle and Ingres, but not for all SQL products. Microsoft SQL Server and Sybase, for example, work in an opposite way. If in the procedure the PLAYERS table is mentioned and John executes that procedure, his PLAYERS table is used. If Diane calls the procedure, her table is accessed.

The moment of compiling is also different for each product. Some products perform their compilation when the procedure is created; others do so when the procedure is called for the first time, and still other products do it every time the procedure is called. With Microsoft SQL Server and Sybase, you can explicitly indicate in the CREATE PROCEDURE statement when compiling should take place.

CREATE PROCEDURE DELETE_MATCHES_2
   (PLAYERNO_VAR IN SMALLINT) AS
   WITH RECOMPILE
BEGIN
   :
   :
END

Explanation. The addition of WITH RECOMPILE guarantees that, for each call of the procedure, the compiler is called again. The advantage of this is that the processing strategy, or the processing plan, of the procedure repeatedly is adjusted to the current situation of the database. The disadvantage is that recompilation takes time and performance decreases. For each separate procedure, database managers should determine the best method.

Sybase enables you to include the option WITH RECOMPILE when you activate the procedure. The result is that the procedure is recompiled before it is executed. All calls occurring hereafter use the recompiled procedure.

Therefore, when it is necessary to recompile a procedure, you can do this with Sybase by executing the procedure. Oracle uses a separate ALTER PROCEDURE for this.

ALTER PROCEDURE DELETE_MATCHES COMPILE

Who is allowed to call a stored procedure? Every SQL user? No, to access tables and views, privileges are required. These are granted with the GRANT statement. There is a special privilege for this called EXECUTE. The definition of this form of the GRANT statement looks as follows:

<grant statement> ::=
   <grant execute statement>

<grant execute statement> ::=
   GRANT EXECUTE
   ON    <stored procedure name>
   TO    <grantees>
   [ WITH GRANT OPTION ]

<grantees> ::=
   PUBLIC                                           |
   <user name> [ { , <user name> }... ] |
   <role name> [ { , <role name> }... ]

<grantees> ::= <user> [ { , <user> }... ]

GRANT EXECUTE
ON    DELETE_MATCHES
TO    JOHN

However, John does not need to have a privilege for the SQL statements that are executed within the procedure. With respect to the DELETE_MATCHES procedure, John does not need an explicit DELETE privilege for the MATCHES table.

The person who does need this privilege is the developer who created the procedure. In other words, if a user creates a stored procedure, he or she must have privileges for all SQL statements executed within the procedure.

For most products, it also holds that a procedure will not be executed if the owner of a stored procedure loses several privileges after the procedure has been created correctly. SQL will send an error message when the procedure is called.

Several examples have shown the features of stored procedures. This section covers the advantages of the use of stored procedures. These advantages refer to several areas: maintenance, performance, security, and centralization.

The first advantage, maintenance, has to do with the way applications can be set up with the use of stored procedures. If a specific set of updates on the database logically forms a unit, and if this set of updates is used in multiple applications, it is better to put them in one procedure. Examples are: remove all data of a player (at least five statements) and calculate the number of ancestors of a player. The only thing that needs to be done is to activate the procedure in the programs. This improves the productivity, of course, and prevents a programmer from implementing the set of updates “incorrectly” in his or her program.

The second advantage of stored procedures has nothing to do with productivity or maintenance, but with performance. If an application activates a procedure and waits for completion, the amount of communication between the application and the database server is minimal. This is in contrast to the application sending each SQL statement separately to the database server. Especially now that more applications access the database server through a network, it is important to minimize the amount of communication. This reduces the chance that the network will get overloaded. Briefly, the use of stored procedures can minimize network traffic.

Another advantage has to do with compiling SQL statements. In some database servers, SQL statements are compiled at precompile time (called binding in DB2). In brief, compiling means that the syntax of the statements is verified, that the existence of the tables and columns used is checked, that privileges are verified, and that the optimizer is asked to determine the optimal processing strategy. The result, the compiled SQL statement, is stored in the database. It is then no longer necessary to compile the SQL statements when the programs runs. However, not all database servers compile SQL statements. They compile the statements during the execution of the program, which, of course, reduces the speed. If SQL statements in these systems are stored in stored procedures, they are precompiled again. The advantage of stored procedures for this type of database server is an improved performance.

Stored procedures are not dependent on a particular host language; they can be called from different host languages. This means that if multiple languages are used for development, certain common code does not have to be duplicated (for each language). For example, a specific stored procedure can be called from an online Java application, from a batch application written in C, or from a PHP program operating in an Internet environment.