MySQL 5.0 バージョンからストアドプロシージャをサポートし始めました。

ストアドプロシージャ（Stored Procedure）は、データベース内に複雑なプログラムを格納し、外部プログラムから呼び出せるようにするデータベースオブジェクトです。

ストアドプロシージャは、特定の機能を実現するためのSQL文の集まりであり、コンパイルされてデータベースに作成・保存されます。ユーザーはストアドプロシージャの名前を指定し、必要に応じてパラメータを渡すことで呼び出して実行できます。

ストアドプロシージャは考え方がとてもシンプルで、データベースのSQL言語レベルでのコードのカプセル化と再利用です。

利点

• ストアドプロシージャはカプセル化でき、複雑なビジネスロジックを隠蔽できます。
• ストアドプロシージャは値を返すことができ、パラメータを受け取ることができます。
• ストアドプロシージャはSELECTコマンドで実行できません。なぜなら、それはサブルーチンであり、ビュー、データテーブル、ユーザー定義関数とは異なるからです。
• ストアドプロシージャはデータ検証、ビジネスロジックの強制実行などに使用できます。

欠点

• ストアドプロシージャは、サポートされるプログラミング言語が異なるため、特定のデータベースに特化していることがよくあります。他のベンダーのデータベースシステムに切り替える場合、既存のストアドプロシージャを書き直す必要があります。
• ストアドプロシージャのパフォーマンスチューニングと作成は、各種データベースシステムに制約されます。

一、ストアドプロシージャの作成と呼び出し

• ストアドプロシージャとは、特定の機能を実行するための名前付きのコードブロックです。
• 作成されたストアドプロシージャは、データベースのデータディクショナリに保存されます。

ストアドプロシージャの作成

MYSQL ストアドプロシージャの主要な構文

文の終端記号を宣言し、カスタマイズできます:

DELIMITER $$
或
DELIMITER //

ストアドプロシージャを宣言:

CREATE PROCEDURE demo_in_parameter(IN p_in int)       

ストアドプロシージャの開始および終了記号:

BEGIN .... END    

変数への代入:

SET @p_in=1  

変数の定義:

DECLARE l_int int unsigned default 4000000; 

mysqlストアドプロシージャ、ストアドファンクションの作成:

create procedure 存储过程名(参数)

ストアドプロシージャ本体:

create function 存储函数名(参数)

例

サンプル操作用にデータベースを作成し、データテーブルをバックアップします：

以下はストアドプロシージャの例で、指定された選手が参加したすべての試合を削除します：

解説：デフォルトでは、ストアドプロシージャはデフォルトデータベースに関連付けられます。特定のデータベースの下にストアドプロシージャを作成したい場合は、プロシージャ名の前にデータベース名をプレフィックスとして付けます。プロシージャを定義するときに、使用しますDELIMITER $$コマンドで文の終端記号をセミコロンから;一時的に2つに変更して$$、プロシージャ本体で使用されるセミコロンがクライアント（mysqlなど）によって解釈されずに直接サーバーに渡されるようにします。

ストアドプロシージャの呼び出し：

call sp_name[(传参)];

解説：ストアドプロシージャ内でパラメータを渡す変数p_playernoが設定されています。ストアドプロシージャを呼び出すとき、パラメータを渡すことで57をp_playernoに代入し、その後ストアドプロシージャ内のSQL操作を実行します。

ストアドプロシージャ本体

• ストアドプロシージャ本体には、プロシージャ呼び出し時に実行する必要がある文が含まれます。例：DML、DDL文、if-then-elseやwhile-do文、変数を宣言するdeclare文など。
• プロシージャ本体の形式：beginで始まり、endで終わります（ネスト可能）。

BEGIN
　　BEGIN
　　　　BEGIN
　　　　　　statements; 
　　　　END
　　END
END

注意：各ネストブロックおよびその中の各文はセミコロンで終了する必要がありますが、プロシージャ本体の終了を示すbegin-endブロック（複合文compound statementとも呼ばれる）にはセミコロンは不要です。

文ブロックにラベルを付ける:

[begin_label:] BEGIN
　　[statement_list]
END [end_label]

例：

ラベルには2つの役割があります：

• 1、コードの可読性を高める
• 2、一部の文（例：leave文やiterate文）ではラベルが必要です。

二、ストアドプロシージャのパラメータ

MySQLストアドプロシージャのパラメータはストアドプロシージャの定義で使用され、IN、OUT、INOUTの3種類のパラメータタイプがあります。形式は次のとおりです：

CREATEPROCEDURE 存储过程名([[IN |OUT |INOUT ] 参数名 数据类形...])

• IN 入力パラメータ：呼び出し元がプロシージャに値を渡すことを示します（渡す値はリテラルまたは変数にできます）。
• OUT 出力パラメータ：プロシージャが呼び出し元に値を渡すことを示します（複数の値を返すことができます）（渡す値は変数のみです）。
• INOUT 入出力パラメータ：呼び出し元がプロシージャに値を渡すと同時に、プロシージャが呼び出し元に値を渡すことを示します（値は変数のみです）。

1、in 入力パラメータ

以上からわかるように、p_in はストアドプロシージャ内で変更されますが、影響しません@p_inの値には影響しません。なぜなら前者はローカル変数、後者はグローバル変数だからです。

2、out出力パラメータ

3、inout入出力パラメータ

注意：

1、プロシージャにパラメータがない場合でも、プロシージャ名の後に括弧を書く必要があります。例：

CREATE PROCEDURE sp_name ([proc_parameter[,...]]) ……

2、パラメータ名が列名と同じにならないようにしてください。そうしないと、プロシージャ本体内でパラメータ名が列名として扱われます。

提案：

• 入力値にはinパラメータを使用します。
• 戻り値にはoutパラメータを使用します。
• inoutパラメータはできるだけ使用を避けます。

---

三、変数

1. 変数の定義

ローカル変数の宣言は必ずストアドプロシージャ本体の先頭に置く必要があります：

DECLAREvariable_name [,variable_name...] datatype [DEFAULT value];

ここで、datatype は MySQL のデータ型です。例：int, float, date, varchar(length)

例：

2. 変数への代入

SET 变量名 = 表达式值 [,variable_name = expression ...]

3. ユーザー変数

MySQLクライアントでユーザー変数を使用する:

ストアドプロシージャ内でユーザー変数を使用する

ストアドプロシージャ間でグローバルスコープのユーザー変数を渡す

注意:

• 1、ユーザー変数名は通常@で始まります
• 2、ユーザー変数を乱用すると、プログラムが理解・管理しにくくなります

---

四、コメント

MySQL ストアドプロシージャでは2種類のスタイルのコメントを使用できます

2つのハイフン--：このスタイルは通常、単行コメントに使用されます。

c スタイル：通常、複数行コメントに使用されます。

例：

MySQLストアドプロシージャの呼び出し

callとプロシージャ名、および括弧を使用します。括弧内には必要に応じてパラメータを追加します。パラメータには入力パラメータ、出力パラメータ、入出力パラメータが含まれます。具体的な呼び出し方法は上記の例を参照してください。

MySQLストアドプロシージャのクエリ

データベース内にどのようなテーブルがあるかを知りたい場合、通常は次を使用しますshowtables;で確認します。では、特定のデータベースの下にあるストアドプロシージャを確認する場合も同じ方法が使えるでしょうか？答えは、特定のデータベースの下にあるストアドプロシージャを確認できますが、別の方法です。

次の文でクエリを実行できます：

selectname from mysql.proc where db='数据库名';

或者

selectroutine_name from information_schema.routines where routine_schema='数据库名';

或者

showprocedure status where db='数据库名';

特定のストアドプロシージャの詳細を知りたい場合は、どうすればよいでしょうか？テーブルと同じようにdescribe テーブル名で確認できるのでしょうか？

答えは：ストアドプロシージャの詳細を確認できますが、別の方法を使用する必要があります：

SHOW CREATE PROCEDURE 数据库.存储过程名;

これで現在のストアドプロシージャの詳細を確認できます。

MySQLストアドプロシージャの変更

ALTER PROCEDURE

CREATE PROCEDURE で作成された事前に指定されたストアドプロシージャを変更しても、関連するストアドプロシージャやストアドファンクションには影響しません。

MySQLストアドプロシージャの削除

ストアドプロシージャの削除は比較的簡単で、テーブルの削除と同じです：

DROPPROCEDURE

MySQL から1つまたは複数のストアドプロシージャを削除します。

MySQLストアドプロシージャの制御文

(1). 変数のスコープ

内部の変数はそのスコープ内でより高い優先度を持ちます。end に到達すると、内部変数は消滅し、その時点でスコープ外になるため、変数はもはや見えなくなります。なぜなら、ストアドプロシージャの外ではこの宣言された変数を見つけることができないからです。ただし、outパラメータを使用するか、その値をセッション変数に割り当てることで値を保存できます。

(2). 条件文

1. if-then-else 文

2. case文：

(3). ループ文

1. while ···· end while

while 条件 do
    --循环体
endwhile

2. repeat···· end repeat

それは操作を実行した後に結果をチェックし、while は実行前にチェックします。

repeat
    --循环体
until 循环条件  
end repeat;

3. loop ·····endloop

loop ループは初期条件を必要としません。この点は while ループと似ています。また、repeat ループと同様に終了条件も必要としません。leave 文はループを離れることを意味します。

4. LABLES ラベル：

ラベルは begin、repeat、while、または loop 文の前に使用できます。文ラベルは正しい文の前にのみ使用できます。ループを抜け出して、実行命令を複合文の最後のステップに到達させることができます。

(4). ITERATE 反復

ITERATE は複合文のラベルを参照して、複合文を再開します。

参考記事：

https://www.cnblogs.com/geaozhang/p/6797357.html

http://blog.sina.com.cn/s/blog_86fe5b440100wdyt.html