MySQL ストアドプロシージャとストアドファンクションの詳細な説明

1 ストアドプロシージャ

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

ストアド プロシージャは、特定の機能を実行するための一連の SQL ステートメントです。本質的には、データベースに保存されるコードです。宣言型 SQL ステートメント (CREATE、UPDATE、SELECT など) と手続き型 SQL ステートメント (IF...THEN...ELSE 制御構造ステートメントなど) で構成できます。ストアド プロシージャの考え方は非常にシンプルで、データベースの SQL 言語レベルでコードをカプセル化して再利用するというものです。

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

アドバンテージ：

1. SQL 言語の機能性と柔軟性を強化できます。ストアド プロシージャはプロセス制御言語で記述できるため、柔軟性が高く、複雑な判断やより複雑な計算を実行できます。

2. 適切なカプセル化 ストアド プロシージャを作成すると、ストアド プロシージャを記述する SQL ステートメントの書き換えを気にすることなく、プログラム内で複数回呼び出すことができます。

3. 高性能ストアド プロシージャを一度実行すると、その実行プランはキャッシュ メモリに保存されます。その後の操作では、キャッシュ メモリからコンパイルされたバイナリ コードを呼び出すだけで実行できるため、システム パフォーマンスが向上します。

欠点:

ストアド プロシージャはさまざまなプログラミング言語をサポートしているため、特定のデータベースに合わせてカスタマイズされることがよくあります。他社のデータベース システムに切り替える場合は、元のストアド プロシージャを書き直す必要があります。

1.3 ストアドプロシージャを作成する

1.3.1 区切り文字

SQL では、サーバーはデフォルトで SQL ステートメントの終了マークとしてセミコロンを使用します。ただし、ストアド プロシージャを作成する場合、ストアド プロシージャ本体に複数の SQL ステートメントが含まれることがあります。これらの SQL ステートメントが依然としてステートメント終了記号としてセミコロンを使用している場合、サーバーは最初の SQL ステートメントのセミコロンをプログラム全体の終了マークとして使用し、後続の SQL ステートメントを処理しなくなります。
この問題を解決するには、通常、DELIMITER コマンドを使用して、SQL ステートメントの終了文字を一時的に他の記号に変更します。

DELIMITER 構文形式:

区切り文字 $$

$$ はユーザー定義の終端文字であり、通常この記号はいくつかの特殊記号になることがあります。また、バックスラッシュはエスケープ文字なので使用しないでください。
終了マークをデフォルトのセミコロンに戻す場合は、コマンド ラインに次の SQL ステートメントを入力します。

区切り文字 ;

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

MySQL では、 CREATE PROCEDUREステートメントを使用してストアド プロシージャを作成します。

プロシージャ p_name([proc_parameter[,...]]) を作成します。
ルーチン本体

構文項目「proc_parameter」の構文形式は次のとおりです。

[IN|OUT|INOUT]parame_name タイプ

1. 「p_name」はストアド プロシージャの名前を指定するために使用されます。

2. 「proc_parameter」は、ストアド プロシージャ内のパラメーター リストを指定するために使用されます。構文項目「parame_name」はパラメータ名、「type」はパラメータ タイプです (type は MySQL で有効な任意のデータ型にすることができます)。 Mysql ストアド プロシージャは、入力パラメータ IN、出力パラメータ OUT、および入力および出力パラメータ INOUT の 3 種類のパラメータをサポートします。入力パラメータを使用すると、データをストアド プロシージャに渡すことができます。出力パラメータは、ストアド プロシージャが返す必要のある操作結果に使用されます。入力パラメータと出力パラメータは、入力パラメータと出力結果の両方として機能します。
パラメータ名は、テーブル内の列名と同じであってはなりません。同じでないと、エラー メッセージは返されませんが、ストアド プロシージャ内の SQL ステートメントはパラメータ名を列名として扱い、予期しないエラーが発生します。

3. 構文項目「rountine_body」は、ストアド プロシージャの主要部分 (ストアド プロシージャ本体とも呼ばれます) を表します。これには、実行する必要のある SQL が含まれます。プロシージャ本体はキーワード BEGIN で始まり、キーワード END で終わります。 SQL ステートメントが 1 つしかない場合は、BEGIN....END 記号は無視できます。

1.3.3 ローカル変数

ストアド プロシージャ本体でローカル変数を宣言して、プロシージャ本体に一時的な結果を保存できます。 MySQL では、DECLARE ステートメントを使用してローカル変数を宣言します。

DECLARE var_name type [デフォルト値]

「var_name」はローカル変数の名前を指定するために使用されます。「type」は変数の型を宣言するために使用されます。「DEFAULT」はデフォルト値を指定するために使用されます。指定されていない場合は NULL になります。

注: ローカル変数は、ストアド プロシージャ本体の BEGIN...END ステートメント ブロックでのみ使用できます。ローカル変数は、ストアド プロシージャ本体の先頭で宣言する必要があります。ローカル変数のスコープは、宣言されている BEGIN...END ステートメント ブロックに限定され、他のステートメント ブロック内のステートメントでは使用できません。

1.3.4 ユーザー変数

ユーザー変数は通常 @ で始まります。

注意: ユーザー変数を誤って使用すると、プログラムの理解と管理が困難になる可能性があります。

1.3.5 SET文

MySQL では、SET ステートメントを通じてローカル変数に値を割り当てる形式は次のとおりです。

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

1.3.6 SELECT ... INTO ステートメント

MySQL では、SELECT...INTO ステートメントを使用して、選択した列の値をローカル変数に格納できます。形式は次のとおりです。

SELECT col_name[,..] INTO var_name[,....] table_expr

「col_name」は列名を指定するために使用され、「var_name」は割り当てる変数名を指定するために使用され、「table_expr」はSELECT文のFROMの後の部分を表します。

注意: SELECT...INTO ステートメントによって返される結果セットには、 1 行のデータのみを含めることができます。

1.3.7 フロー制御文

条件文

if-then-else ステートメント:

mysql > 区切り文字 && 
mysql > CREATE PROCEDURE proc2(IN パラメータ int) 
 -> 開始 
 -> var int を宣言します。 
 -> var=パラメータ+1を設定します。 
 -> var=0の場合 
 -> t値に挿​​入(17); 
 -> 終了の場合; 
 -> パラメータ=0の場合 
 -> t を更新し、s1=s1+1 に設定します。 
 -> それ以外の場合 
 -> t を更新し、s1=s1+2 に設定します。 
 -> 終了の場合; 
 -> 終了; 
 -> && 
mysql > 区切り文字 ; 

ケースステートメント:

mysql > 区切り文字 && 
mysql > CREATE PROCEDURE proc3 (パラメータ in int) 
 -> 開始 
 -> var int を宣言します。 
 -> var=パラメータ+1を設定します。 
 -> ケース変数 
 -> 0の場合 
 -> t値に挿​​入(17); 
 -> 1の場合 
 -> t値に挿​​入(18); 
 -> それ以外の場合 
 -> t値に挿​​入(19); 
 -> 終了ケース; 
 -> 終了; 
 -> && 
mysql > 区切り文字 ;

ループ文
while ···· end while:

mysql > 区切り文字 && 
mysql > プロシージャ proc4() を作成します 
 -> 開始 
 -> var int を宣言します。 
 -> var=0 を設定します。 
 -> var<6の間 
 -> t 値(var) に挿入します。 
 -> var=var+1 を設定します。 
 -> 終了しながら; 
 -> 終了; 
 -> && 
mysql > 区切り文字 ;

繰り返し···· 繰り返し終了:

操作を実行した後に結果をチェックしますが、操作を実行する前にチェックします。

mysql > 区切り文字 && 
mysql > プロシージャ proc5 () を作成します 
 -> 開始 
 -> v int を宣言します。 
 -> v=0 を設定します。 
 -> 繰り返し 
 -> t 値(v) に挿入します。 
 -> v=v+1 を設定します。 
 -> v>=5 まで 
 -> 繰り返し終了; 
 -> 終了; 
 -> && 
mysql > 区切り文字 ;

繰り返す
 --ループ条件が終了するまで本体をループします repeat;

ループ·····エンドループ:

ループには初期条件は必要ありません。これは while ループに似ています。repeat ループと同様に、終了条件は必要ありません。ループを終了するには、leave ステートメントを使用します。

mysql > 区切り文字 && 
mysql > プロシージャ proc6 () を作成します 
 -> 開始 
 -> v int を宣言します。 
 -> v=0 を設定します。 
 -> LOOP_LABLE:ループ 
 -> t 値(v) に挿入します。 
 -> v=v+1 を設定します。 
 -> v >=5の場合 
 -> LOOP_LABLE を終了します。 
 -> 終了の場合; 
 -> ループを終了します。 
 -> 終了; 
 -> && 
mysql > 区切り文字 ;

ITERATE 反復:

mysql > 区切り文字 && 
mysql > プロシージャ proc10 () を作成します 
 -> 開始 
 -> v int を宣言します。 
 -> v=0 を設定します。 
 -> LOOP_LABLE:ループ 
 -> v=3の場合 
 -> v=v+1 を設定します。 
 -> LOOP_LABLE を反復します。 
 -> 終了の場合; 
 -> t 値(v) に挿入します。 
 -> v=v+1 を設定します。 
 -> v>=5の場合 
 -> LOOP_LABLE を終了します。 
 -> 終了の場合; 
 -> ループを終了します。 
 -> 終了; 
 -> && 
mysql > 区切り文字 ;

1.3.8 カーソル

MySQL のカーソルは、反復可能なオブジェクト (Python のリストや辞書などの反復可能なオブジェクトに類似) として理解できます。カーソルは、選択ステートメントの結果セットを格納するために使用できます。この結果セットには複数のデータ行を含めることができるため、反復メソッドを使用してカーソルから各データ行を順番に取得できます。

MySQL カーソルの機能:
1. 読み取り専用: 基本テーブル内のデータはカーソルを通じて更新できません。
2. スクロール不可: 行は、SELECT ステートメントによって決定された順序でのみ取得できます。逆の順序で行を取得することはできません。 さらに、結果セット内の行をスキップしたり、特定の行にジャンプしたりすることはできません。
3. センシティブ: カーソルには、センシティブ カーソルとインセンシティブ カーソルの 2 種類があります。センシティブ カーソルは実際のデータを指し、インセンシティブ カーソルはデータの一時コピーを使用します。センシティブ カーソルは、一時的なデータのコピーを必要としないため、インセンシティブ カーソルよりも高速に実行されます。 MySQL カーソルは敏感です。

1. カーソルを宣言する

カーソルの宣言は変数の宣言の後で行う必要があります。変数宣言の前にカーソルを宣言すると、MySQL はエラーを発行します。カーソルは常に SELECT ステートメントに関連付けられている必要があります。

select_statement の cursor_name カーソルを宣言します。

2.カーソルを開く

カーソルを開くには、open ステートメントを使用します。カーソルが開かれた後でのみ、データを読み取ることができます。

カーソル名を開きます。

3. カーソルを読み取る

fetch ステートメントを使用して、カーソルが指すデータ行を取得し、カーソルを結果セット内の次の行に移動します。

cursor_name を var_name にフェッチします。

4. カーソルを閉じる

カーソルを閉じるには、close ステートメントを使用します。

カーソル名を閉じます。

カーソルが使用されなくなったら、閉じる必要があります。 MySQL カーソルを使用する場合は、カーソルが行を見つけられない場合を処理するために notfound ハンドラーも宣言する必要があります。 フェッチ ステートメントが呼び出されるたびに、カーソルは結果セット内の各行のデータを順番に読み取ろうとします。 カーソルが結果セットの末尾に到達すると、データを取得できなくなり、条件が生成されます。 この状況を処理するにはハンドラーが使用されます。

見つからない場合の継続ハンドラを宣言し、type = 1 を設定します。

type は、カーソルが結果セットの末尾に到達したことを示す変数です。

区切り文字 $$
PROCEDURE phoneDeal() を作成します。
始める
 id varchar(64); を宣言します -- id
 DECLARE phone1 varchar(16); -- 電話番号
 DECLARE password1 varchar(32); -- パスワード DECLARE name1 varchar(64); -- ID
 --トラバーサルデータ終了マーク DECLARE done INT DEFAULT FALSE;
 -- カーソル DECLARE cur_account CURSOR FOR select phone,password,name from account_temp;
 -- 終了フラグをカーソルにバインドします。DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
 
 -- カーソルを開きます OPEN cur_account; 
 -- トラバース read_loop: LOOP
 -- 複数のフィールドから値を取得します FETCH NEXT from cur_account INTO phone1,password1,name1;
 完了したら
 read_loop を終了します。
 終了の場合;
 
 -- 実行する操作 insert into account(id,phone,password,name) value(UUID(),phone1,password1,CONCAT(name1,'的父'));
 ループを終了;
 
 -- カーソルを閉じます CLOSE cur_account;
終了 $$

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

call ステートメントを使用してストアド プロシージャを呼び出す

sp_name[(渡されたパラメータ)]を呼び出します。

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

ドロップステートメントを使用してストアドプロシージャを削除します

DROP PROCEDURE sp_name

2 ストレージ機能

2.1 ストアド関数とは何ですか?

ストアド関数は、ストアド プロシージャと同様に、SQL とステートメントで構成されるコード ブロックです。
ストアド関数は入力パラメータを持つことができず、呼び出しステートメントなしで直接呼び出すことができますが、RETURN ステートメントを含める必要があります。

2.2ストアド関数の作成

MySQL の CREATE FUNCTION ステートメントを使用して以下を作成します。

CREATE FUNCTION fun_name (par_name type[,...])
戻り値タイプ
【特徴】 
楽しい体

ここで、fun_name は関数名であり、名前は一意であり、ストアド プロシージャの名前と同じにすることはできません。 par_name は指定されたパラメータ、type はパラメータ タイプです。RETURNS 句は戻り値と戻り値の型を宣言するために使用されます。 fun_body は関数本体です。ストアド プロシージャ内のすべての SQL ステートメントは、ストアド関数でも使用できます。ただし、ストアド関数本体には RETURN ステートメントが含まれている必要があります。
特性はストアド プロシージャの特性を指定し、次の値を持ちます。

• LANGUAGE SQL: ルーチン本体部分が SQL ステートメントで構成されていることを示します。現在のシステムでサポートされている言語は SQL です。LANGUAGE プロパティの値は SQL のみです。
• [NOT] DETERMINISTIC: ストアド プロシージャの実行結果が決定されるかどうかを示します。 DETERMINISTIC は、結果が決定的であることを意味します。ストアド プロシージャが実行されるたびに、同じ入力で同じ出力が生成されます。NOT 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 は、UFIDA 権限を持つ呼び出し元が実行できることを意味します。デフォルトでは、システムは DEFINER として指定されます。
• COMMENT '文字列': ストアド プロシージャまたは関数を説明するために使用されるコメント情報。

区切り文字 $$
関数 getAnimalName(animalId int) を作成し、VARCHAR(50) を返します。
決定論的
始める
 名前を宣言する VARCHAR(50);
 name=(id=animalId の animal から名前を選択) を設定します。
 戻り値 (名前);
終わり$$
デリミタ;

-- select getAnimalName(4) を呼び出す

以上がこの記事の全内容です。皆様の勉強のお役に立てれば幸いです。また、123WORDPRESS.COM を応援していただければ幸いです。