MySQL ストアド プロシージャの作成、呼び出し、管理の詳細な説明

ストアドプロシージャの概要

ストアド プロシージャを使用する理由は何ですか?

MySQL バージョン 5.0 ではストアド プロシージャのサポートが開始されました。

ほとんどの SQL ステートメントは、1 つ以上のテーブルに対する単一のステートメントです。すべての操作がそれほど単純というわけではありません。多くの場合、完全な操作を完了するには複数のステートメントが必要です。

簡単に言えば、ストアド プロシージャとは、後で使用するために保存された 1 つ以上の MySQL ステートメントの集合です。バッチファイルとして考えてください。ただし、その役割はバッチ処理に限定されません。

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

ストアドプロシージャの利点

1. 処理を使いやすい単位にカプセル化することで複雑な操作を簡素化します。
2. 変更の管理を簡素化します。テーブル名、列名、またはビジネス ロジックが変更された場合。変更する必要があるのはストアド プロシージャのコードのみであり、それを使用するユーザーは自分のコードを変更する必要はありません。
3. 通常、ストアド プロシージャはアプリケーションのパフォーマンスの向上に役立ちます。作成されたストアド プロシージャはコンパイルされると、データベースに保存されます。 ただし、MySQL ではストアド プロシージャの実装が若干異なります。 MySQL ストアド プロシージャはオンデマンドでコンパイルされます。ストアド プロシージャをコンパイルした後、MySQL はそれをキャッシュに配置します。 MySQL は、接続ごとに独自のストアド プロシージャ キャッシュを維持します。アプリケーションが単一の接続でストアド プロシージャを複数回使用する場合は、コンパイルされたバージョンが使用されます。それ以外の場合、ストアド プロシージャはクエリのように動作します。
4. ストアド プロシージャを使用すると、アプリケーションは複数の長い SQL ステートメントを送信する必要がなく、ストアド プロシージャの名前とパラメータのみを送信するだけで済むため、アプリケーションとデータベース サーバー間のトラフィックを削減できます。
5. ストアド プロシージャは再利用可能であり、どのアプリケーションに対しても透過的です。ストアド プロシージャは、データベース インターフェイスをすべてのアプリケーションに公開するため、開発者はストアド プロシージャで既にサポートされている機能を開発する必要がありません。
6. 保存されたプログラムは安全です。データベース管理者は、基礎となるデータベース テーブルに対する権限を付与せずに、データベース内のストアド プロシージャにアクセスするアプリケーションに適切な権限を付与できます。
   

ストアドプロシージャの欠点

1. 多数のストアド プロシージャを使用する場合、それらのストアド プロシージャを使用する各接続のメモリ使用量が大幅に増加します。 さらに、ストアド プロシージャで論理操作を多用しすぎると、CPU 使用率も増加します。これは、MySQL データベースの元の設計が効率的なクエリに重点を置いており、論理操作には適していないためです。
2. ストアド プロシージャの構造により、複雑なビジネス ロジックを持つストアド プロシージャの開発が困難になります。
3. ストアド プロシージャをデバッグするのは困難です。ストアド プロシージャのデバッグが可能なデータベース管理システムはごくわずかです。残念ながら、MySQL ではストアド プロシージャをデバッグする機能は提供されていません。
4. ストアド プロシージャの開発と保守は簡単ではありません。ストアド プロシージャの開発と保守には、すべてのアプリケーション開発者が備えているわけではない特殊なスキル セットが必要になることがよくあります。これにより、アプリケーションの開発および保守フェーズで問題が発生する可能性があります。

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

プロシージャの作成と呼び出し

ストアド プロシージャを作成します。コードは次のようになります。

-- ストアド プロシージャを作成します。create procedure mypro(in a int, in b int, out sum int) 
始める 
合計を a+b に設定します。 
終わり;

結果は以下のとおりです

以下に示すように、Navicat クライアントの「関数」ノードでプロセスを表示することもできます。

ストアド プロシージャを呼び出す場合、コードは次のようになります。

call mypro(1,2,@s);-- ストアド プロシージャを呼び出す select @s;-- プロシージャの出力結果を表示する

運用結果

ストアドプロシージャ構文解析

• create procedure はプロシージャを作成するために使用されます。
• mypro はプロシージャ名を定義するために使用されます。
• (in a int、in b int、out sum int) はプロシージャのパラメータを表します。in は入力パラメータを表し、out は出力パラメータを表します。 Java でメソッドを定義するときのパラメーターと戻り値に似ています。
• begin と end はプロセス本体の開始と終了を示します。これは、Java でメソッドを定義する中括弧のペアに相当します。
• callはプロシージャを呼び出すために使用され、@sはプロシージャの出力パラメータを受け取るために使用される変数です。
  

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

MySQL ストアド プロシージャ パラメータは、ストアド プロシージャの定義で使用されます。パラメータには次の 3 つの種類があります。

• IN 入力パラメータ: 呼び出し元がプロシージャに値を渡すことを示します (渡される値はリテラルまたは変数です)。
• OUT 出力パラメータ: プロシージャが呼び出し元に値を渡すことを示します (複数の値を返すことができます) (出力値は変数のみになります)。
• INOUT 入力および出力パラメータ: 呼び出し元がプロシージャに値を渡し、プロシージャが呼び出し元に値を渡すことを示します (値は変数のみ可能です)。

ストアド プロシージャは、パラメーターに基づいて 4 つのカテゴリに分類できます。

1). パラメータのない手順。

2) プロセスには入力パラメータのみがあります。

3) 出力パラメータのみを持つプロセス。

4) 入力パラメータと出力パラメータを含むプロシージャ。

変数

MySQL のストアド プロシージャは、Java のメソッドに似ています。

この場合、変数はストアド プロシージャでも使用できます。 Java のローカル変数のスコープは変数が配置されているメソッドですが、MySQL のローカル変数のスコープは変数が配置されているストアド プロシージャです。

変数の定義

DECLARE 変数名 [,変数名...] データ型 [デフォルト値];

declare変数を宣言するために使用されます。

variable_name変数名を示します。

datatypeは MySQL のデータ型です。

defaultデフォルト値を宣言するために使用されます。

例えば：

名前varchar(20)を宣言します。デフォルトは'jack'です。

変数の割り当て

SET 変数名 = 式の値 [,variable_name = 式 ...]

ストアドプロシージャで変数を使用する場合、コードは次のようになります。

use schooldb;-- schooldb データベースを使用する -- プロシージャを作成する create procedure mypro1()
始める
名前を宣言するvarchar(20);
名前を 'Qiu Chuji' に設定します。
studentname = name の場合、studentinfo から * を選択します。
終わり;
-- プロシージャを呼び出します call mypro1();

運用結果

フロー制御文

if条件文

IF文には複数の条件判断が含まれており、 TRUEまたはFALSEの結果に基づいて文を実行します。これは、プログラミング言語のif 、 else if 、 else構文に似ています。

ストアド プロシージャを定義し、整数を入力し、if ステートメントを使用してそれが正の数か負の数かを判断します。コードは次のとおりです。

-- プロシージャを作成する create procedure mypro2(in num int)
始める
if num<0 then -- 条件は「負の数」の選択を開始します。
そうでない場合はnum=0
「肯定的でも否定的でもない」を選択します。
それ以外
「正の数」を選択します。
end if;-- 条件が終了します end;
-- プロシージャ call mypro2(-1); を呼び出します。

運用結果

case 条件文

case 、プログラミング言語のchooseやwhen構文に似た別の条件文です。 MySQL のcaseステートメントには 2 つの構文形式があります。

ストアド プロシージャを定義し、整数を入力し、case ステートメントを使用してそれが正の数か負の数かを判断します。コードは次のとおりです。

-- プロシージャを作成する create procedure mypro3(in num int)
始める
case -- 条件は num<0 のときに開始し、その後 '負の数' を選択します。
num=0 の場合は、「正でも負でもない」を選択します。
それ以外の場合は「正の数」を選択します。
end case; -- 条件が終了します end;
-- プロシージャ call mypro3(1); を呼び出す。

運用結果

ストアド プロシージャを定義し、整数を入力し、case ステートメントを使用してそれが 1 か 2 かを判断します。コードは次のとおりです。

-- プロシージャを作成する create procedure mypro4(in num int)
始める
case num -- 条件は 1 のときに開始し、その後「値が 1 である」を選択します。
2 の場合は「値は 2 です」を選択します。
それ以外の場合は「1でも2でもない」を選択します。
end case; -- 条件が終了します end;
-- プロシージャ call mypro4(3); を呼び出す。

運用結果

どちらの case 構文でも条件判断を実装できますが、最初の構文は範囲の値の判断に適しており、2 番目の構文は確定的な値の判断に適しています。

whileループ文

whileステートメントの使用方法は、 javaのwhileループに似ています。

ストアド プロシージャを定義し、while ループを使用して 1 から 10 までの累積合計を出力します。コードは次のとおりです。

-- プロシージャを作成します。create procedure mypro5(out sum int)
始める
num int のデフォルトを 0 と宣言します。
合計を 0 に設定します。
while num<10 do -- ループ開始 set num = num+1;
sum を sum+num に設定します。
end while; -- ループの終了 end;
-- プロシージャを呼び出す call mypro5(@sum);
--変数値を照会する select @sum;

運用結果

繰り返しループ文

repeat文の使用方法は、 javaのdo…while文と似ています。どちらも最初にループ操作を実行し、次に条件を判断します。違いは、ループ操作はrepeat式の値がfalseの場合にのみ実行され、式の値がtrueになるまで停止することです。

ストアド プロシージャを定義し、繰り返しループを使用して 1 から 10 までの累積合計を出力します。コードは次のとおりです。

-- プロシージャを作成します。create procedure mypro6(out sum int)
始める
num int のデフォルトを 0 と宣言します。
合計を 0 に設定します。
repeat-- ループ開始 set num = num+1;
sum を sum+num に設定します。
数値>=10になるまで
end repeat; -- ループの終了 end;
-- プロシージャを呼び出します call mypro6(@sum);
--変数値を照会する select @sum;

運用結果

ループ文

ループ ステートメントは、特定のステートメントを繰り返し実行するために使用されます。

実行プロセス中に、 leaveステートメントまたはiterateを使用してループから抜け出すことも、 IFなどの判断ステートメントをネストすることもできます。

leaveステートメントは Java のbreakに相当し、ループを終了するために使用されます。

iterateステートメントは Java のcontinueに相当し、現在のループ操作を終了して次のループに入るために使用されます。

ストアド プロシージャを定義し、ループを使用して 1 から 10 までの累積合計を出力します。コードは次のとおりです。

-- プロシージャを作成します。create procedure mypro7(out sum int)
始める
num int のデフォルトを 0 と宣言します。
合計を 0 に設定します。
loop_sum:loop-- ループ開始 set num = num+1;
sum を sum+num に設定します。
num>=10の場合
loop_sum を終了します。
終了の場合;
end loop loop_sum; -- ループの終了 end;
-- プロシージャを呼び出します call mypro7(@sum);
--変数値を照会する select @sum;

運用結果

コード内の loop_sum はループにラベルを付けるのと同じで、複数のループが関係する場合に柔軟な操作を可能にします。

ストアドプロシージャ管理

ストアド プロシージャの管理には、主に、プロシージャの表示、プロシージャのソース コードの表示、およびプロシージャの削除が含まれます。

比較的簡単な方法は、次の図に示すように、マウスをクリックするだけの、Navicat クライアント ツールを使用して管理することです。

ストアドプロシージャの表示

プロシージャのステータスを表示します。

特定のデータベースのストアド プロシージャを表示します。

SHOW PROCEDURE ステータス where db = 'schooldb';

特定のスキーマのストアド プロシージャを表示し、名前に「my」が含まれるストアド プロシージャが表示されるように要求します。

SHOW PROCEDURE ステータス、名前が '%my%' のような場合;

ストアドプロシージャ「mypro1」のソースコードを表示します。

CREATE PROCEDURE mypro1 を表示します。

ストアドプロシージャ「mypro1」を削除します。

PROCEDURE mypro1 を削除します。

要約する

これで、MySQL ストアド プロシージャの作成、呼び出し、および管理に関するこの記事は終了です。MySQL ストアド プロシージャの詳細については、123WORDPRESS.COM の以前の記事を検索するか、次の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。