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

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を使用すると、すべてのストアドプロシージャの詳細を一覧表示できます。また、

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

以下もご興味があるかもしれません:
  • MySQLストアドプロシージャの詳細な説明
  • MySQL ストアド プロシージャ カーソル ループの使用の概要
  • MySQL ストアドプロシージャの使用例の詳細な説明
  • MySql ストアド プロシージャと関数の詳細な説明
  • 入力および出力パラメータを持つ MySQL ストアド プロシージャの例
  • MySQL ストアドプロシージャと関数の違い
  • MySQL ストアド プロシージャのグラフィック例の説明

<<:  WeChatアプレットは記録機能を実装します

>>:  Linux で at および cron スケジュールタスクをカスタマイズする方法

推薦する

Vueのprovideとinjectの使い方と原則を分析する

まず、provide/inject を使用する理由について説明しましょう。祖父コンポーネントと孫コン...

MySQL の起動オプションとシステム変数の例の詳細な説明

目次ブートオプションコマンドラインパラメータの長い形式と短い形式設定ファイル構成グループシステム変数...

ハンドラー PageHandlerFactory-Integrated のモジュール リストに不正なモジュール ManagedPipelineHandler が含まれています

Web プロジェクトを開発する場合、IIS をインストールする必要があります。IIS がインストール...

HTML タグ dl dt dd 使用方法

基本構造:コードをコピーコードは次のとおりです。 <ダウンロード> <dt>...

JavaScript で 9 グリッドのモバイル パズル ゲームを実装

この記事では、Jiugonggeモバイルパズルゲームを実装するためのJavaScriptの具体的なコ...

Linux に起動方法を追加する (サービス/スクリプト)

システムの起動時に読み込む必要がある設定ファイル/etc/profile、/root/.bash_p...

Dockerでリモートアクセスを有効にする方法

DockerデーモンソケットDocker デーモンは、 unix 、 tcp 、 fdの 3 種類の...

MySQLユーザー権限管理の詳細な説明

目次序文: 1. ユーザー権利の概要2. 実際の権限管理序文:データベースのユーザー権限管理について...

Ubuntu 20.04にvncserverをインストールする方法

Ubuntu 20.04は2020年4月に正式にリリースされました。本日、ミラーシステムを正式にイン...

プレーヤー機能を実現するためのvue + element uiのサンプルコード

効果画像のない表示は単なる空虚な言葉です。 1. オーディオをベースにし、elementUI と組み...

Windows 10 に MySQL 8.0.19 を zip 形式でインストールする詳細なチュートリアル

目次1.ダウンロード後、インストールしたいディレクトリに解凍します。 2. インストールディレクトリ...

CentOS 6-7 PHPのyumインストール方法(推奨)

1. 現在インストールされているPHPパッケージを確認するyum list installed |...

仮想マシンに Linux rhel7.3 オペレーティング システムをインストールする (具体的な手順)

仮想化ソフトウェアをインストールする仮想マシンにオペレーティング システムをインストールする前に、ホ...

IE8 と Chrome でテーブルの幅を修正する方法

IE8 や Chrome で上記の設定を使用すると、画面の最大幅に合わせて表示が統一され、各列の幅は...

ウェブページにプレーヤーを埋め込む埋め込み要素の自動開始が false 無効

最近、仕事でサウンド ファイルを再生するために Web ページにプレーヤーを埋め込む必要に迫られまし...