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 create a stored procedure by using the CREATE PROCEDURE statement. The differences between MySQL-compatible mode and Oracle-compatible mode are as follows:

• The DECLARE block must be placed inside the BEGIN END block, and all definitions must be declared before other statements.

• You can define sp_create_characteristic information such as DETERMINISTIC and LANGUAGE SQL to enrich the use of stored procedures.

• Stored procedures in MySQL-compatible mode do not support overloading, so you do not need to consider parameter types when deleting a stored procedure.

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