ステートメントプロシージャとは、直接値を返さないサブルーチンの一種です。パラメータの型がOUTの場合、ステートメントプロシージャは呼び出し元に値を返すこともできます。
ステートメントプロシージャの構造
PLステートメントプロシージャの構造は以下のとおりです：
PROCEDURE sp_name ([proc_parameter[,...]])[characteristic ...]
BEGIN -- 実行開始
SQL statement; [ SQL statement; ]...
END; -- 実行終了
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 }
}
ストアドプロシージャの作成
ストアドプロシージャは、CREATE PROCEDURE ステートメントを使用して作成します。MySQLモードとOracleモードの間で顕著な違いは以下のとおりです：
DECLAREブロックはBEGIN ... END;ブロック内に配置する必要があり、すべての定義宣言が完了してから他のステートメントを定義できます。DETERMINISTIC / LANGUAGE SQLなどのsp_create_chistic情報を定義することで、ストアドプロシージャの使用を豊かにできます。MySQLモードでは、ストアドプロシージャのオーバーロードは存在しません。
例1：IN および OUT パラメータを持たないストアドプロシージャを作成します。
obclient> DELIMITER //
obclient> CREATE PROCEDURE proc_name()
BEGIN
DECLARE var_name VARCHAR(20) DEFAULT 'ZhangSan';
SET var_name = 'LiSi';
SELECT var_name;
END //
Query OK, 0 rows affected
obclient> DELIMITER ;
obclient> CALL proc_name();
実行結果は次のとおりです：
+----------+
| var_name |
+----------+
| LiSi |
+----------+
1 row in set
例2：IN および OUT パラメータを持つストアドプロシージャを作成します。
//サンプルテーブルempを作成
obclient> CREATE TABLE emp(
empno NUMBER(4,0),
empname VARCHAR(10),
job VARCHAR(10),
deptno NUMBER(2,0),
salary NUMERIC
);
Query OK, 0 rows affected
obclient> INSERT INTO emp VALUES (200,'Jennifer','AD_ASST',1,15000),(202,'Pat','MK_REP',2,12000),
(119,'Karen','PU_CLERK', 4,10000),(118,'Guy','PU_CLERK', 4,10000),
(201,'Michael','MK_MAN',3,9000);
Query OK, 5 rows affected
Records: 5 Duplicates: 0 Warnings: 0
obclient> DELIMITER //
obclient> CREATE PROCEDURE my_proc(IN emp_no INT,OUT emp_count INT)
BEGIN
SELECT COUNT(*) INTO emp_count FROM emp WHERE empno=emp_no;
END //
Query OK, 0 rows affected
obclient> DELIMITER ;
//パラメータの初期化
obclient> SET @emp_no='200',@emp_count=0;
Query OK, 0 rows affected
//ストアドプロシージャmy_procを呼び出す
obclient> CALL my_proc(@emp_no,@emp_count);
実行結果は次のとおりです：
+-----------+
| emp_count |
+-----------+
| 1 |
+-----------+
1 row in set
引き続き、emp_countに代入された結果を確認します：
obclient> SELECT @emp_count;
実行結果は次のとおりです：
+------------+
| @emp_count |
+------------+
| 1 |
+------------+
1 row in set
ステートメントの呼び出し
作成済みのストアドプロシージャは、CALL ステートメントを使用して呼び出す必要があります。ただし、SQL式の一部として呼び出すことはできません。
//ストアドプロシージャmy_procを呼び出す
obclient> CALL my_proc(@emp_no,@emp_count);
Query OK, 0 rows affected
//emp_countに代入された結果を確認する
obclient> SELECT @emp_count;
実行結果は次のとおりです：
+------------+
| @emp_count |
+------------+
| 1 |
+------------+
1 row in set