ストアドプロシージャは、直接戻り値を持たないサブルーチンです。パラメータの型が 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