Stored procedures

A stored procedure is a subprogram that does not directly return a value. If the parameter type is OUT, the stored procedure can return values to the caller.

Structure of a stored procedure

The following sample code shows the structure of a stored procedure:

CREATE
    [DEFINER = user]
    PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body

proc_parameter:
    [ IN | OUT | INOUT ] param_name type

characteristic: {
    COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
}

routine_body:
    Valid SQL routine statement

Create a stored procedure

You can use the CREATE PROCEDURE statement to create a stored procedure. Compared with that in Oracle mode, the statement in MySQL mode has the following features:

• The DECLARE block must be placed inside the BEGIN END block. You must declare all definitions before defining other statements.

• You can define sp_create_chistic information such as DETERMINISTIC and LANGUAGE SQL to enrich the use of a stored procedure.

• You do not need to consider the parameter types when deleting a stored procedure in MySQL mode, because stored procedures in MySQL mode are never reloaded.

Here is an example:

obclient> DELIMITER /
obclient> CREATE TABLE city(CountryCode CHAR(3))/
Query OK, 0 rows affected

obclient> CREATE PROCEDURE citycount (IN country CHAR(3), OUT cities INT)
     BEGIN
        SELECT COUNT(*) INTO cities FROM city
         WHERE CountryCode = country;
     END/
Query OK, 0 rows affected

Call a stored procedure

To call a stored procedure that has been created, you can use the CALL statement. However, you cannot call the stored procedure as a part of an SQL expression.

obclient> CALL citycount('JPN', @cities);/
+--------+
| cities |
+--------+
|      0 |
+--------+
1 row in set

obclient> SELECT @cities/
+---------+
| @cities |
+---------+
|       0 |
+---------+
1 row in set

obclient> DROP PROCEDURE citycount/
Query OK, 0 rows affected