MYSQL ストアドプロシージャと関数の簡単な記述

ストアドプロシージャとは

簡単に言えば、これは強力で、JAVA 言語のメソッドに似た比較的複雑な論理関数を実装できる一連の SQL ステートメントです。

追記: ストアドプロシージャはトリガーに似ています。どちらも SQL セットのセットですが、ストアドプロシージャはアクティブに呼び出され、トリガーよりも強力です。トリガーは何かがトリガーされた後に自動的に呼び出されます。

特徴は何ですか

入力および出力パラメータがあり、変数を宣言でき、if/else、case、while などの制御文があります。ストアドプロシージャを記述することで、複雑な論理関数を実装できます。

関数の共通機能: モジュール性、カプセル化、コードの再利用。

高速で、最初の実行のみコンパイルと最適化の手順を実行する必要があり、後続の呼び出しは直接実行できるため、上記の手順は不要です。

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

文法

CREATE PROCEDURE sp_name ([proc_parameter[,...]]) [特性...] ルーチン本体

CREATE PROCEDURE プロシージャ名([[IN|OUT|INOUT] パラメータ名データ型[,[IN|OUT|INOUT] パラメータ名データ型…]]) [特性…] プロシージャ本体

区切り文字 //
 CREATE PROCEDURE myproc(OUT s int)
  始める
   SELECT COUNT(*) INTO s FROM students;
  終わり
  //
区切り文字 ;

デリミタ

MySQL はデフォルトで「;」を区切り文字として使用します。区切り文字が宣言されていない場合、コンパイラはストアドプロシージャを SQL ステートメントとして扱うため、コンパイルプロセスでエラーが報告されます。したがって、現在のセグメント区切り文字を事前に「DELIMITER //」で宣言する必要があります。これにより、コンパイラは 2 つの「//」の間の内容をストアドプロシージャコードとして扱い、このコードを実行しません。「DELIMITER ;」は区切り文字を復元することを意味します。

パラメータ

ストアドプロシージャには、必要に応じて入力、出力、および入出力パラメータを設定できます。パラメータが複数ある場合は、「,」を使用して区切ります。 MySQL ストアドプロシージャパラメータは、ストアドプロシージャの定義で使用されます。パラメータには、IN、OUT、INOUT の 3 種類があります。

ストアドプロシージャを呼び出すときに、IN パラメータの値を指定する必要があります。ストアドプロシージャの実行中にパラメータの値が変更された場合、その値は返されません。これはデフォルト値です。
OUT: 値はストアドプロシージャ内で変更でき、返される。
INOUT: 呼び出し時に指定され、変更して返すことができます

このうち、sp_name パラメータはストアドプロシージャの名前、proc_parameter はストアドプロシージャのパラメータリスト、characteristic パラメータはストアドプロシージャの特性、routine_body パラメータは SQL コードの内容、BEGIN...END は SQL コードの開始と終了を示すために使用できます。

proc_parameter 内の各パラメータは 3 つの部分で構成されます。これら 3 つの部分は、入力と出力の型、パラメータ名、およびパラメータ型です。その形式は次のとおりです。

[ IN | OUT | INOUT ] パラメータ名タイプ

これらのうち、IN は入力パラメータを表し、OUT は出力パラメータを表します。INOUT は入力と出力の両方が可能であることを意味し、param_name パラメータはストアドプロシージャのパラメータ名です。type パラメータはストアドプロシージャのパラメータタイプを指定します。これは、MySQL データベースの任意のデータ型にすることができます。

特性パラメータには複数の値があります。値は次のとおりです。

LANGUAGE SQL: ルーチン本体部分が SQL 言語のステートメントで構成されていることを示します。SQL 言語は、データベースシステムのデフォルト言語でもあります。

[NOT] DETERMINISTIC: ストアドプロシージャの実行結果が決定的であるかどうかを示します。 DETERMINISTIC は結果が確実であることを意味します。ストアドプロシージャを実行するたびに、同じ入力によって同じ出力が生成されます。 NOT DETERMINISTIC は、結果が非決定的であり、同じ入力で異なる出力が生成される可能性があることを意味します。デフォルトでは、結果は非決定的です。

{ SQL を含む | SQL なし | SQL データを読み取る | SQL データを変更する }: サブルーチンによる SQL ステートメントの使用に関する制限を指定します。 CONTAINS SQL は、サブプログラムに SQL ステートメントが含まれているが、データの読み取りまたは書き込み用のステートメントが含まれていないことを意味します。NO SQL は、サブプログラムに SQL ステートメントが含まれていないことを意味します。READS SQL DATA は、サブプログラムにデータの読み取り用のステートメントが含まれていることを意味します。MODIFIES SQL DATA は、サブプログラムにデータの書き込み用のステートメントが含まれていることを意味します。デフォルトでは、CONTAINS SQL が指定されます。

SQL SECURITY { DEFINER | INVOKER }: 実行権限を持つユーザーを指定します。 DEFINER は定義者だけが実行できることを意味し、INVOKER は呼び出し側が実行できることを意味します。デフォルトでは、システムによって割り当てられる権限は DEFINER です。

COMMENT '文字列': コメント情報。

ヒント: ストアドプロシージャを作成すると、システムはデフォルトで CONTAINS SQL を指定し、ストアドプロシージャで SQL ステートメントが使用されることを示します。ただし、ストアドプロシージャで SQL ステートメントが使用されていない場合は、NO SQL に設定するのが最適です。さらに、後でストアドプロシージャコードを読みやすくするために、COMMENT セクションでストアドプロシージャに関する簡単なコメントを記入することをお勧めします。

[例 1] 以下は、num_from_employee という名前のストアドプロシージャを作成します。コードは次のとおりです。

CREATE PROCEDURE num_from_employee (IN emp_id INT、OUT count_num INT) 
     SQLデータの読み取り 
     始める 
       COUNT(*) を count_num に選択します 
       従業員より 
       ここで、d_id=emp_id; 
     終わり

上記のコードでは、ストアドプロシージャの名前は num_from_employee、入力変数は emp_id、出力変数は count_num です。 SELECT ステートメントは、employee テーブルで d_id 値が emp_id と等しいレコードを照会し、COUNT(*) を使用して同じ d_id 値を持つレコードの数を計算し、最後に計算結果を count_num に格納します。コードの実行結果は次のとおりです。

mysql> デリミタ && 
mysql> プロシージャ num_from_employee を作成します
(IN emp_id INT、OUT count_num INT) 
  -> SQLデータの読み取り 
  -> 開始 
  -> SELECT COUNT(*) INTO count_num 
  -> 従業員より 
  -> WHERE d_id=emp_id; 
  -> 終了 && 
クエリは正常、影響を受けた行は 0 行 (0.09 秒) 
mysql> 区切り文字 ;

コードの実行後、エラーメッセージが報告されない場合は、ストレージ関数が正常に作成されたことを意味します。将来このストアドプロシージャを呼び出すと、ストアドプロシージャ内の SQL ステートメントがデータベース内で実行されます。

注: MySQL のデフォルトのステートメント終了文字はセミコロン (;) です。ストアドプロシージャ内の SQL ステートメントはセミコロンで終了する必要があります。競合を避けるには、まず「DELIMITER &&」を使用して MySQL ターミネータを && に設定します。最後に、「DELIMITER ;」を使用して終了文字をセミコロンに戻します。これはトリガーを作成するときと同じです。

関数

MySQL では、ストアド関数を作成する基本的な形式は次のとおりです。

CREATE FUNCTION sp_name ([func_parameter[,...]]) 戻り値 type [characteristic ...] ルーチン本体
このうち、sp_name パラメータはストアド関数の名前です。func_parameter はストアド関数のパラメータリストを表します。RETURNS type は戻り値の型を指定します。characteristic パラメータはストアド関数の特性を指定します。このパラメータの値はストアドプロシージャの値と同じです。セクション 14.1.1 の内容を参照してください。routine_body パラメータは SQL コードの内容であり、BEGIN...END を使用して SQL コードの始まりと終わりを示すことができます。

func_parameterは複数のパラメータで構成することができ、各パラメータは次の形式のパラメータ名とパラメータ型で構成されます: param_name type

param_name パラメータは、ストアド関数のパラメータ名です。type パラメータは、ストアド関数のパラメータタイプを指定します。これは、MySQL データベースの任意のデータ型にすることができます。

[例 2] 以下は、name_from_employee という名前のストアド関数を作成します。コードは次のとおりです。

CREATE FUNCTION name_from_employee (emp_id INT) を作成します。 
     VARCHAR(20)を返します 
     始める 
       RETURN (SELECT 名前 
       従業員より 
       ここで、num = emp_id ); 
     終わり

上記のコードでは、ストアド関数の名前は name_from_employee、関数のパラメーターは emp_id、戻り値は VARCHAR 型です。 SELECT ステートメントは、employee テーブルで num 値が emp_id に等しいレコードを照会し、そのレコードの name フィールドの値を返します。コードの実行結果は次のとおりです。

mysql> デリミタ && 
mysql> CREATE FUNCTION name_from_employee (emp_id INT) を作成します 
  -> VARCHAR(20)を返します 
  -> 開始 
  -> RETURN (SELECT 名前 
  -> 従業員より 
  -> WHERE num=emp_id ); 
  -> 終了&& 
クエリは正常、影響を受けた行は 0 行 (0.00 秒) 
mysql> 区切り文字 ;

結果は、保存された関数が正常に作成されたことを示しています。この関数の使用方法は、MySQL 内部関数と同じです。

変数の使用

ストアドプロシージャと関数では、変数を定義して使用できます。ユーザーは DECLARE キーワードを使用して変数を定義できます。その後、変数に値を割り当てることができます。これらの変数のスコープは、BEGIN...END プログラムセグメントです。このセクションでは、変数を定義して値を割り当てる方法について説明します。

1.変数の定義

MySQL では、DECLARE キーワードを使用して変数を定義できます。変数を定義するための基本的な構文は次のとおりです。

DECLARE var_name[,...] type [DEFAULT value]

このうち、DECLARE キーワードは変数を宣言するために使用され、var_name パラメータは変数の名前であり、複数の変数を同時にここで定義できます。type パラメータは変数の型を指定するために使用され、DEFAULT value 句は変数のデフォルト値を value に設定します。DEFAULT 句を使用しない場合、デフォルト値は NULL になります。

[例 3] 以下は、データ型が INT、デフォルト値が 10 の変数 my_sql を定義します。コードは次のとおりです。

my_sql INT DEFAULT 10 を宣言します。

2.変数に値を割り当てる

MySQLでは、SETキーワードを使用して変数に値を割り当てることができます。 SET ステートメントの基本的な構文は次のとおりです。

SET var_name = expr [, var_name = expr] ...

このうち、SET キーワードは変数に値を割り当てるために使用され、var_name パラメータは変数の名前であり、expr パラメータは割り当て式です。 SET ステートメントは複数の変数に同時に値を割り当てることができ、各変数の割り当てステートメントはカンマで区切られます。

[例4] 以下は変数my_sqlに値30を割り当てます。コードは次のとおりです。

SET my_sql = 30;

MySQL では、SELECT...INTO ステートメントを使用して変数に値を割り当てることもできます。基本的な構文は次のとおりです。

SELECT col_name[,…] INTO var_name[,…] FROM table_name WEHRE 条件
このうち、col_name パラメータはクエリ対象フィールドの名前を示し、var_name パラメータは変数の名前、table_name パラメータはテーブルの名前、condition パラメータはクエリ条件を示します。

[例 5] 次のクエリは、従業員テーブルの ID 2 のレコードに対して実行され、レコードの d_id 値が変数 my_sql に割り当てられます。コードは次のとおりです。

従業員 WEHRE id=2 から d_id を my_sql に選択します。

条件とハンドラーの定義

条件とハンドラーを定義することは、プログラム実行中に発生する可能性のある問題を事前に定義することです。これらの問題に対する解決策はハンドラーで定義できます。このアプローチにより、起こりうる問題を事前に予測し、解決策を提案することができます。これにより、プログラムの問題処理能力が向上し、異常なプログラム停止を回避できます。 MySQL では、条件とハンドラーは DECLARE キーワードを使用して定義されます。このセクションでは、条件とハンドラーを定義する方法について詳しく説明します。

1.条件を定義する

MySQL では、DECLARE キーワードを使用して条件を定義できます。基本的な構文は次のとおりです。

条件名条件を条件値として宣言する 
条件値: 
   SQLSTATE [値] sqlstate_value | mysql_error_code

condition_name パラメータは条件の名前を示します。condition_value パラメータは条件のタイプを示します。sqlstate_value パラメータと mysql_error_code パラメータはどちらも MySQL エラーを示します。たとえば、ERROR 1146 (42S02) の場合、sqlstate_value 値は 42S02 で、mysql_error_code 値は 1146 です。

[例 6] 以下は、エラー「ERROR 1146 (42S02)」を定義し、can_not_find という名前を付けます。 2 つの異なる方法で定義できます。コードは次のとおりです。

//方法 1: sqlstate_value を使用する 
SQLSTATE '42S02' の can_not_find 条件を宣言します。 
//方法2: mysql_error_codeを使用する 
1146 の can_not_find 条件を宣言します。

2.ハンドラの定義

MySQL では、DECLARE キーワードを使用してハンドラーを定義できます。基本的な構文は次のとおりです。

handler_type ハンドラーを宣言する 
条件値[,...] sp_statement 
ハンドラタイプ: 
  続行 | 終了 | 元に戻す 
条件値: 
  SQLSTATE [値] sqlstate_value |
条件名 | SQLWARNING 
    | 見つかりません | SQL例外 | mysql_error_code

handler_type パラメータはエラーの処理方法を指定し、3 つの値を指定できます。 3つの値はCONTINUE、EXIT、UNDOです。 CONTINUE はエラー発生時に何も処理を行わず実行を継続することを意味します。EXIT はエラー発生時に直ちに実行を終了することを意味します。UNDO はエラー発生時に前の操作を取り消すことを意味します。MySQL は現在この処理方法をサポートしていません。

注: 通常、実行中にエラーが発生した場合は、次のステートメントの実行を直ちに停止し、以前の操作を元に戻す必要があります。ただし、MySQL は現在 UNDO 操作をサポートしていません。したがって、エラーが発生した場合は、EXIT アクションを実行するのが最適です。エラーの種類を事前に予測し、それに応じて処理できる場合は、CONTINUE 操作を実行できます。

condition_value パラメータはエラーの種類を示し、6 つの値が可能です。 sqlstate_value と mysql_error_code は、条件定義の場合と同じ意味を持ちます。 condition_name は、DECLARE によって定義される条件の名前です。 SQLWARNING は、01 で始まるすべての sqlstate_value 値を表します。 NOT FOUNDは02で始まるすべてのsqlstate_value値を表します。 SQLEXCEPTION は、SQLWARNING または NOT FOUND によってキャプチャされないすべての sqlstate_value 値を表します。 sp_statement は、いくつかのストアドプロシージャまたは関数の実行ステートメントを表します。

[例 7] 以下にハンドラーを定義するいくつかの方法を示します。コードは次のとおりです。

//方法 1: sqlstate_value を取得する 
SQLSTATE '42S02' の継続ハンドラを宣言します
SET @info='見つかりません'; 
//方法 2: mysql_error_code をキャプチャする 
1146 の継続ハンドラーを宣言し、@info='見つかりません' を設定します。 
//方法 3: 最初に条件を定義し、次に DECLARE can_not_find CONDITION FOR 1146; を呼び出します。 
can_not_find SETのCONTINUEハンドラを宣言する 
@info='見つかりません'; 
//方法4: SQLWARNINGを使用する 
SQLWARNING の終了ハンドラーを宣言し、@info='ERROR' を設定します。 
//方法5: NOT FOUNDを使用する 
NOT FOUND SET @info='CAN NOT FIND' の終了ハンドラーを宣言します。 
//方法6: SQLEXCEPTIONを使用する 
SQLEXCEPTION の終了ハンドラーを宣言し、@info='ERROR' を設定します。

上記のコードは、ハンドラーを定義する 6 つの方法を示しています。

最初の方法は、sqlstate_value 値をキャプチャすることです。 sqlstate_value が 42S02 の場合、CONTINUE 操作が実行され、「CAN NOT FIND」メッセージが出力されます。

2 番目の方法は、mysql_error_code 値をキャプチャすることです。 mysql_error_code 値が 1146 の場合、CONTINUE 操作が実行され、「CAN NOT FIND」メッセージが出力されます。

3 番目の方法は、最初に条件を定義してから、その条件を呼び出すことです。ここでは、まず can_not_find 条件を定義し、1146 エラーが発生したときに CONTINUE 操作を実行します。

4 番目の方法は、SQLWARNING を使用することです。 SQLWARNING は、01 で始まるすべての sqlstate_value 値をキャプチャし、EXIT 操作を実行して「ERROR」メッセージを出力します。

5番目の方法は、NOT FOUND を使用することです。 NOT FOUND は、02 で始まるすべての sqlstate_value 値をキャプチャし、EXIT 操作を実行して、「CAN NOT FIND」メッセージを出力します。

6 番目の方法は、SQLEXCEPTION を使用することです。 SQLEXCEPTION は、SQLWARNING または NOT FOUND によってキャプチャされないすべての sqlstate_value 値をキャプチャし、EXIT 操作を実行して「ERROR」メッセージを出力します。

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

1. パラメータなしのストアドプロシージャを作成します。

プロシージャ product() を作成する
始める
    ユーザーから*を選択します。
終わり;

単純なストアドプロシージャ作成ステートメント。呼び出しステートメントは次のとおりです。

call procedure();

##コマンドラインで記述する場合、この記述方法では構文エラーが発生することに注意してください。つまり、その文を選択して終了します。
mysql が説明してくれるので、まず終了文字を変更する必要があります。

区切り文字 //
プロシージャ product() を作成する
始める
    ユーザーから*を選択します。
終わり //

最終的に元に戻す

delimiter ;

2. パラメータ付きのストアドプロシージャを作成する

パラメータ付きストレージには、次の 2 つのパラメータが含まれます。
1 つは入力パラメータです。
1 つは送信パラメータです。
たとえば、ストアドプロシージャは次のようになります。

プロシージャを作成する procedure2(
出力p1小数点(8,2)、
出力p2小数点(8,2)、
p3 int 内
）
始める
order_name = p3 のユーザーから sum(uid) を p1 に選択します。
ユーザーからavg(uid)をp2に選択します。
終わり ;

上記の SQL ステートメントから、p1 と p2 は値を取得して渡すために使用され、p3 を呼び出すには特定の値を渡す必要があることがわかります。
具体的な呼び出しプロセスについては以下を参照してください。

call product(); //パラメータなし
procedure2(@userSum,@userAvg,201708); を呼び出す // パラメータ付き

使い切ったら、userSum と userAvg の値を直接照会できます。

@userSum、@userAvg を選択します。

結果は次のとおりです。

+----------+-----------+
| @ユーザー合計 | @ユーザー平均 |
+----------+-----------+
| 67.00 | 6.09 |
+----------+-----------+
セット内の 1 行 (0.00 秒)

3. ストアドプロシージャを削除する

1 つのステートメント: drop procedure product; // その後に括弧はありません

4. 完全なストアドプロシージャの例:

-- 名前: drdertotal 
-- パラメータ: onumbrella = 注文番号 
-- 課税対象 = 課税対象外の場合は 0、課税対象の場合は 1 
--ototal = 注文合計変数 
 
プロシージャ ordertotal( を作成する 
オンナンバー int、 
課税ブール値では、 
合計小数点以下(8,2) 
) '注文合計を取得し、オプションで税金を追加する' をコミットします  
始める 
  -- 合計の変数を宣言する 
  合計小数点(8,2)を宣言します。 
  -- 税率を申告する 
  税率を int デフォルト 6 と宣言します。 
   
  --注文合計を取得する 
  合計(item_price*quantity)を選択します 
  注文商品から 
  order_num = オンナンバー 
  合計に; 
   
  --これは課税対象ですか? 
  課税対象の場合 
    --はい、合計に税率を加えます 
    total+(total/100*taxrate)をtotalに選択します。 
  終了の場合; 
   
  --最後に追加し、out変数に保存します 
  合計をototalに選択します。 
終わり;

上記のストアドプロシージャは高級言語の業務処理に似ており、理解するのは難しくありません。記述の細部に注意してください。

commit キーワード: 必須ではありませんが、指定した場合は show procedure status の結果に表示されます。

if ステートメント: この例では、mysqlif ステートメントの基本的な使用方法を示します。if ステートメントは、elseif 句と else 句もサポートします。

show procedure statusを使用すると、すべてのストアドプロシージャの詳細を一覧表示できます。また、

フィルタリングするフィルタリングモードを「いいね+指定」します。

以下もご興味があるかもしれません: