はじめに
MySQLストアドプロシージャ複数のタスクをグループ化 1つにまとめて、将来使用するためにタスクをサーバーに保存します。
ストアドプロシージャは、データベース管理を簡素化し、ネットワークトラフィックを削減します。たとえば、MySQLサーバーにクエリを発行すると、クエリが処理され、結果が返されます。ストアドプロシージャを使用すると、クエリがサーバーに保存されるため、後で実行できます。
このチュートリアルでは、ストアドプロシージャの作成、一覧表示、変更、および削除について学習します。
前提条件
- MySQLServerとMySQLWorkbenchがインストールされています
- root権限を持つMySQLユーザーアカウント
MySQLのストアドプロシージャとは何ですか？
MySQLストアドプロシージャはプリコンパイル済みです SQLステートメント データベースに保存されます。これらは、名前、パラメータリスト、およびSQLステートメントを含むサブルーチンです。
すべてのリレーショナルデータベースシステムはストアドプロシージャをサポートしており、追加のランタイム環境パッケージは必要ありません。
ストアドプロシージャの使用方法
ストアドプロシージャを呼び出すには、 CALLを使用できます ステートメントまたはその他のストアドプロシージャ。ストアドプロシージャが初めて呼び出されると、MySQLはそれをデータベースカタログで検索し、コードをコンパイルして、キャッシュメモリに配置します。 、実行します。
同じセッションでの後続の実行は、キャッシュメモリからストアドプロシージャを実行するため、反復的なタスクに非常に役立ちます。
ストアドプロシージャはパラメータを利用します 値を渡し、結果をカスタマイズします。パラメータは、クエリが動作して結果を返すテーブルの列を指定するために使用されます。
ストアドプロシージャには、 IFを含めることもできます 、CASE 、および LOOP 制御フローステートメント コードを手続き的に実装します。
ストアドプロシージャの作成
次の2つの方法でストアドプロシージャを作成します。
1.MySQLシェルを使用する
次の構文を使用して、MySQLでストアドプロシージャを作成します。
DELIMITER //
CREATE PROCEDURE procedure_name ( IN | OUT | INOUT parameter_name parameter_datatype (length), … )
BEGIN
SQL statements
END //
DELIMITER ;
デフォルトでは、構文は使用中のデータベースに関連付けられていますが、次の方法でデータベース名を指定することにより、別のデータベースの構文を使用することもできます。 database_name.procedure_name 。
ここでは、最初の DELIMITER 引数は、デフォルトの区切り文字を //に設定します 、最後の DELIMITER 引数はセミコロンに戻します; 。複数のステートメントを使用するには、 $$のような異なる区切り文字を指定します 。
プロシージャ名は、 CREATE PROCEDUREの後にあります 口論。プロシージャー名の後に、括弧を使用して、プロシージャーで使用するパラメーター、パラメーターの名前、データ型、およびデータ長を指定します。各パラメーターはコンマで区切ります。
パラメータモード は：
-
IN–パラメーターを入力として渡すために使用します。定義されると、クエリはストアドプロシージャに引数を渡します。パラメータの値は常に保護されています。 -
OUT–パラメーターを出力として渡すために使用します。ストアドプロシージャ内で値を変更すると、新しい値が呼び出し元のプログラムに返されます。 -
INOUT–INの組み合わせ およびOUTパラメーター。呼び出し側プログラムは引数を渡し、プロシージャはINOUTを変更できます。 パラメータ、新しい値をプログラムに戻します。
例：
ストアドプロシージャを呼び出して実行します：
CALL procedure_name;
クエリはストアドプロシージャの結果を返します。
2. MySQLWorkbenchを使用する
ストアドプロシージャを作成する別の方法は、MySQLWorkbenchウィザードを使用することです。ウィザードは直感的で、区切り文字を配置したり形式を気にしたりする必要がないため、プロセスが簡素化されます。
次の手順に従ってください：
ステップ1： ストアドプロシージャを右クリックします MySQL Workbenchのナビゲータウィンドウで、ストアドプロシージャの作成…を選択します。 ウィザードを開始します。
ステップ2： プロシージャ名を指定し、 BEGIN内にコードを入力します …終了 ブロック。
ステップ3： コードを確認し、[適用]をクリックします 。
ステップ4： 適用をクリックして実行を確認します 完了をクリックしてプロシージャを作成します 。
ステップ5： 手順を実行して、機能するかどうかを確認します。クエリを実行するための新しいSQLタブを作成します。
ステップ6： CALL [SQL]タブの手順を選択し、[実行]をクリックします 。
エラーが返されない場合、MySQLはストアドプロシージャを実行し、結果を表示します。
ストアドプロシージャの一覧表示
すべてのストアドプロシージャのリストを表示するには、次の3つの方法があります。
1.MySQLシェルを使用する
アクセスできるすべてのストアドプロシージャのリスト（特性を含む）を取得するには、次の構文を使用します。
SHOW PROCEDURE STATUS [LIKE 'pattern' | WHERE search_condition]
SHOW PROCEDURE STATUS ステートメントは長い出力を返します。このステートメントには、サーバー上でアクセスできるストアドプロシージャの名前と特性が表示されます。
出力をスクロールして、現在サーバー上にある手順を見つけます。
LIKE 引数は、名前に特定の単語を含むストアドプロシージャを検索します。 %を使用する ゼロを含む任意の数の文字を置き換えます。
例：
WHERE 引数を使用すると、特定のデータベースにのみストアドプロシージャを一覧表示できます。
例：
この例では、ステートメントは’ customer_listのストアドプロシージャのみを返します。 ’データベース。
2.データディクショナリを使用する
information_schema データベースには、ルーチンというテーブルが含まれています 、現在のMySQLサーバー上のすべてのデータベースに関連するストアドプロシージャと関数に関する情報があります。
次の構文を使用して、データベースのすべてのストアドプロシージャを表示します。
SELECT
routine_name
FROM
information_schema.routines
WHERE
routine_type = 'PROCEDURE'
AND routine_schema = 'database_name';
3. MySQLWorkbenchを使用する
ストアドプロシージャを表示するためのGUIアプローチについては、MySQLWorkbenchを使用してください。ストアドプロシージャを表示するには、次の手順に従います。
ステップ1 ：ナビゲーターで使用するデータベースをダブルクリックします セクション。
ステップ2 ：ストアドプロシージャを展開します ドロップダウンアイテム。
このアイテムには、現在のデータベースのすべてのストアドプロシージャが表示されます。
ストアドプロシージャの変更
ストアドプロシージャを変更するということは、特性を変更することを意味します 手順の。 ステートメントはありません MySQLでパラメータまたは本文を変更する ストアドプロシージャの。パラメータまたは本文を変更するには、ストアドプロシージャを削除して、新しいプロシージャを作成します。
ストアドプロシージャを2つの方法で変更します。
1.MySQLシェルを使用する
ALTER PROCEDUREを使用して、プロシージャの特性を変更します 声明。たとえば、以前に作成したプロシージャにコメントを追加できます。構文は次のとおりです。
ALTER PROCEDURE procedure_name
COMMENT 'Insert comment here';
2. MySQLWorkbenchを使用する
MySQL Workbench GUIを使用すると、ユーザーはストアドプロシージャを変更して、パラメータを追加したりコードを変更したりできます。 MySQLWorkbenchのドロップ 既存のストアドプロシージャと作成 変更が加えられた後の新しいもの。
次の手順に従ってください：
ステップ1： [ナビゲーター]セクションで、変更するストアドプロシージャを右クリックします。 ストアドプロシージャの変更…を選択します アイテム。
ステップ2： タブが開いたら、既存のストアドプロシージャに必要な変更を加えて、[適用]をクリックします。 。
ステップ3： SQLスクリプトレビューウィンドウ プロセスを示すが表示されます–既存のストアドプロシージャを削除し、変更を含む新しいプロシージャを作成します。
適用をクリックします 次に終了 次のウィンドウでスクリプトを実行します。
ストアドプロシージャの削除
プロシージャを削除（削除）するには：
1.MySQLシェルを使用する
DROP PROCEDUREを使用して、サーバーからストアドプロシージャを削除します ステートメント。
基本的な構文は次のとおりです。
DROP PROCEDURE [IF EXISTS] stored_procedure_name;
IF EXISTS パラメーターは、サーバー上に存在する場合にのみストアード・プロシージャーをドロップします。 stored_procedure_nameの代わりにストアドプロシージャの名前を入力します 構文。
例：
「test」という名前のプロシージャがないため サーバー上の’の出力には、0行が影響を受け、指定されたプロシージャが存在しないことが示されます。
IF EXISTSなしで存在しないプロシージャを削除する パラメータはエラーを返します。
2. MySQLWorkbenchを使用する
MySQL Workbenchでストアドプロシージャを削除するには、次の手順に従います。
ステップ1： 「ナビゲーター」セクションの「ストアード・プロシージャー」項目を展開します。削除するストアドプロシージャを右クリックして、ストアドプロシージャの削除…を選択します。 コンテキストメニューで。
ステップ2： 確認ウィンドウで、[今すぐドロップ]をクリックします ストアドプロシージャを削除します。
このアクションは完全に削除します 手順。
MySQLストアドプロシージャの長所と短所
ストアドプロシージャには、特定のニーズに合わせて調整されるため、いくつかの長所と短所があります。以下は、長所と短所の一部です。
ストアドプロシージャを使用する利点
ストアドプロシージャの利点は次のとおりです。
ネットワークトラフィックの削減
ストアドプロシージャは、すべてのプログラミングロジックをサーバー上に保持することにより、アプリケーションとMySQLサーバー間のネットワークトラフィックを削減するのに役立ちます。アプリは、ネットワークを介して複数のクエリ結果を送信する代わりに、プロシージャ名とパラメータ入力のみを送信します。
セキュリティの向上
データベース管理者は、テーブルへの直接アクセスを許可せずに、特定のストアドプロシージャのみを呼び出してアクセスする権限をアプリに付与します。ストアドプロシージャは、入力パラメータが実行可能コードではなく値として扱われるため、スクリプトインジェクション攻撃を防ぐのに役立ちます。
一元化されたビジネスロジック
ストアドプロシージャは、複数のアプリケーションで再利用可能なビジネスロジックをカプセル化します。これにより、多くの異なるアプリケーションで同じロジックが重複するのを減らし、データベースの一貫性を高めることができます。
ストアドプロシージャを使用することのデメリット
ストアドプロシージャの欠点は次のとおりです。
リソースの使用
多くのストアドプロシージャと論理演算を使用すると、接続ごとにメモリとCPUの使用量が大幅に増加します。
移植性なし
特定の言語で記述されたストアドプロシージャをあるインストールから別のインストールに移植するのは簡単ではありません。ストアドプロシージャに依存することで、ユーザーは特定のデータベースに関連付けられます。
トラブルシューティングとテスト
MySQLはストアドプロシージャをテストおよびデバッグするためのユーティリティを提供していないため、それらをデバッグするのは難しい場合があります。ストアドプロシージャの開発と保守には、広範な知識が必要です。これは新しい開発者にとっての課題であり、メンテナンスコストが増加します。