MySQLのストアドプロシージャ（ストアドプロシージャとストアドファンクション）の作成について詳しく説明します。

簡単に言えば、ストアド プロシージャは 1 つ以上の SQL ステートメントの組み合わせであり、バッチ ファイルとみなすことができますが、その役割はバッチ処理に限定されません。

(1)ストアドプロシージャの作成
(2)ストアドファンクションの作成
(3)変数の使用
(4)条件とハンドラの定義
(5)カーソルの使用
(6) プロセス制御の使用

## (無料学習の推奨事項: mysql ビデオ チュートリアル)

#(1) ストアド プロシージャを作成する

---

ストアド プロシージャを作成するには、

createprocedure

ステートメントを使用する必要があります。基本的な構文形式は次のとおりです。 ##

create procedure sp_name( [ proc_parameter ] )[ characteristics ... ] routine_body

createprocedure はストアド関数の作成に使用されるキーワードです。sp_name はストアド プロシージャの名前です。proc_parameter はストアド プロシージャのパラメータ リストです。リストの形式は次のとおりです:

[in | out | inout] param_name type

in は入力パラメータを表します

out は出力パラメータを表します
• inout は入力または出力できることを示します
• param_name はパラメータ名を示し、type はパラメータのタイプを示します
• characteristics は、ストアド プロシージャの特性を次のように指定します。 値:

言語 SQL: 説明 Luke_body 部分は SQL で構成されます。現在のシステムでサポートされている言語は SQL であり、SQL が言語属性の唯一の値です。

• [not] deterministic: ストアド プロシージャの実行結果が正しいかどうかを示します。決定的とは、ストアド プロシージャが実行されるたびに同じ入力が同じ出力を取得することを意味しますが、非決定的とは、同じ入力が異なる出力を取得する可能性があることを意味します。デフォルトは決定的ではありません。

• {SQL を含む | SQL なし | SQL 日付を読み取る | SQL 日付を変更する }: サブプログラムによる SQL ステートメントの使用に対する制限を示します。 「SQL を含む」はサブルーチンに SQL ステートメントが含まれていることを示します。「SQL なし」はサブルーチンに SQ が含まれていないことを示します。「SQL データの読み取り」はサブルーチンにデータを読み取るためのステートメントが含まれていることを示します。「SQL データの変更」はサブルーチンにデータを書き込むためのステートメントが含まれていることを示します。デフォルトでは SQL が含まれます。

• SQL セキュリティ {定義者 | 呼び出し者}: 実行権限を持つユーザーを指定します。定義者は定義者のみが実行できることを意味し、呼び出し者は権限を持つ呼び出し元が実行できることを意味します。デフォルトは定義者です。

• comment 'string': ストアド プロシージャまたは関数を記述するために使用できるコメント情報。

• routine_body は SQL コードの内容です。begin...end を使用して SQL コードの開始と終了を示すことができます。

[例 1] フルーツ テーブルを表示するストアド プロシージャを作成します。コード ステートメントは次のとおりです:

create procedure proc()
	BEGIN
	select * from fruits;
	END ;

このコードは、フルーツ テーブルを表示するストアド プロシージャを作成します。コードの実行プロセス

mysql> delimiter //mysql> create procedure Proc()
    -> begin
    -> select * from fruits;
    -> end //Query OK, 0 rows affected (0.36 sec)mysql> delimiter ;

ヒント: 「区切り文字 //」ステートメントの機能は、MySQL のデフォルトのステートメント終了記号がセミコロンであるため、MySQL の終了記号を // に設定することです。 ;"。これは、ストアド プロシージャ内の SQL との競合を避けるために行われます。ステートメント ターミネータが競合します。ストアド プロシージャを定義した後、「delimiter;」を使用してデフォルトのターミネータを復元します。バックスラッシュは MySQL のエスケープ文字であるため、デリミタ コマンドを使用する場合は、バックスラッシュ "" の使用を避ける必要があります。

• [例 2] CountProc という名前のストアド プロシージャを作成します。コードは次のとおりです:

create procedure CountProc (OUT paraml int)beginselect count(*) into paraml from fruits;end;

上記のコードは、フルーツ テーブルのレコード数を取得するストアド プロシージャを作成します。名前はCountProcで、count(*)の計算後、その結果がパラメータparamlに入れられます。

mysql> delimiter  //mysql> create procedure CountProc(OUT paraml int )
    -> begin
    -> select count(*) into paraml from fruits;
    -> end //Query OK, 0 rows affected (0.08 sec)mysql> delimiter ;

(2) ストアドファンクションの作成

ストアドファンクションを作成するには、create function ステートメントを使用する必要があります。基本的な構文は次のとおりです。

create function func_name ( [ func_parameter] )
returns type
[characteristic ...] routine_body

create function は、ストアド関数の作成に使用されるキーワードです。

func_name は、ストアド関数の名前を表します。
• func_parameter は、ストアド関数のパラメータ リストです。パラメータ リストの形式は次のとおりです:
• [in | out | inout ] param_name type
• このうち、in は入力パラメータ、out は出力パラメータ、inout はそれぞれを表します。入力と出力の両方の param_name はパラメータ名を表し、type はパラメータのタイプを表します。returns type ステートメントは関数によって返されるデータのタイプを表します。characteristic はストアド関数の特性を指定します。値は、ストアド プロシージャ。

[例 3] NameByZip という名前のストレージ関数を作成します。この関数は、select ステートメントのクエリ結果を返します。数値型は文字列型です。コードは次のとおりです。コードの結果は次のとおりです;

create function NameByZip()returns char( 50)return(select s_name from suppliers where s_call ='48075');

ストアド関数の ruturn ステートメントが、関数の returns 句で指定された型とは異なる型の値を返す場合、戻り値は適切な型に強制されます。 。

注: パラメータを in、out、または inout として指定することは、プロシージャに対してのみ有効です。 (関数のデフォルトは常に in パラメーターです。)returns 句は関数にのみ指定でき、関数には必須です。これは関数の戻り値の型を指定するために使用され、関数本体には戻り値ステートメントが含まれている必要があります。

(3) 変数の使用

• 変数はサブプログラム内で宣言して使用することができ、これらの変数のスコープは begin...end プログラム内にあります。

1. 変数の定義

ストアド プロシージャで変数を定義するには、declar ステートメントを使用します。構文形式は次のとおりです:

mysql> delimiter //mysql> create function NameByZip()
    -> returns char(50)
    -> return (select s_name from suppliers where s_call = '48075');
    -> //Query OK, 0 rows affected (0.06 sec)mysql> delimiter;

var_name はローカル変数の名前です。 。デフォルト値句は、変数のデフォルト値を提供します。値は定数として宣言されるだけでなく、式として指定することもできます。デフォルト句がない場合、初期値は null です。

【例4】定义名称为myparam的变量，类型为int类型，默认值为100，代码如下：

declare myparam int default 100;

2.为变量赋值

set var_name = expr [,var_name = expr]...;

存储程序中的set语句是一般set语句的扩展版本。被参考变量可能是子程序内声明的变量，或者是全局服务器变量，如系统变量或者用户变量。

【例5】声明3个变量，分别为var1，var2和var3，数据类型为int，使用set为变量赋值，代码如下：

declare var1，var2，var3 int;set var1 = 10, var2 =20;set var3 = var1 + var2;

MySQL中还可以通过select…into为一个或多个变量赋值，语句如下：

select col_name[,...] into var_name[,...] table_expr;

这个select语法把选定的列直接存储到对应位置的变量。col_name表示字段名称;var_name表示定义的变量名称；table_expr表示查询条件表达式，包括表名称和where子句。

【例6】声明变量fruitname和fruitprice，通过select…into语句查询指定记录并为变量赋值,代码如下:

declare fruitname char(50);declare fruitprice decimal(8,2);select f_name,f_price into fruitname,fruitpricefrom fruits where f_id='a1;

(4)定义条件和处理程序

特定条件需要特定处理。定义条件是事先定义程序执行过程中遇到的问题，处理程序定义了在遇到这些问题时应当采取的处理方式，并且保证存储过程或函数在遇到警告或错误时能继续执行。这样就增强了存储程序处理问题的能力，避免程序异常停止运行。

1.定义条件
定义条件使用declare语句，语法格式如下：

declare conditon_name Condition for [condition_type][condition_type];SQLSTATE [value] sqlstate_value | mysql_error_code

• condition_name表示条件的名称
• condition_type表示条件的类型
• sqlstate_value和mysql_error_code都可以表示MySQL的错误
• sqlstate_value为长度为5的字符类型错误代码
• mysql_error_code为数值类型错误代码

例如：ERROR1142(42000)中，sqlstate_value的值是42000，mysql_error_code的值为1142。

这个语句指定需要特殊处理的条件。它将一个名字和指定的错误条件关联起来。这个名字可以随后被用在定义处理程序的declare handler语句中。

【例7】定义"error 1148(42000)"错误，名称为command_not_allowed。可以用两种不同的方法来定义，代码如下：

[方法一]：使用sqlstate_valuedeclare command_not_allowed condition for sqlstate '42000'[]方法二]:使用mysql_error_codedeclare command_not_allowed condition for 1148

2.定义处理程序

定义处理程序时，使用declare语句的语法如下：

declare handler_type handler for condition_value[,...] sp_statement
handler_type:	continue|exit|undo

condition_value:
	sqlstate[value] sqlstate_value	|condition_name	|sqlwarning	|not found	|sqlexception	|mysql_error_code

其中，

• handler_type为错误处理方式，参数取3个值：continue、exit和undo。
• continue表示遇到错误不处理，继续执行；
• exit遇到错误马上退出；
• undo表示遇到错误后撤回之前的操作，MySQL中暂时不支持这样的操作。

condition_value表示错误类型，可以有以下取值：

• sqlstate[value] sqlstate_value包含5个字符串错误值
• condition_name表示declare condition定义的错误条件名称
• sqlwarning匹配所有以01开头的sqlstate错误代码
• notfound 匹配所有以02开头的sqlstate错误代码
• sqlexception匹配所有没有被sqlwarning或not found捕获的sqlstate错误代码
• mysql_error_code匹配数值类型错误代码

sp_statement参数为程序语句段，表示在遇到定义的错误时，需要执行的存储过程或函数。

【例8】定义处理程序的几种方式如下：

方法1：捕获sqlstate_valuedeclare continue handler for sqlstate '42S02' set @info='No_SUCH_TABLE';方法2:捕获mysql_error_codedeclare continue handler for 1146 set @info='No_SUCH_TABLE';方法3:先定义条件,然后调用declare no_such_table condition for 1146;declare continue handler for NO_SUCH_TABLE SET @info='NO_SUCH_TABLE';方法4:使用sqlwarningdeclare exit handler for sqlwarning set @info='ERROR';方法5:使用not founddeclare exit handler for not found set @info=' NO_SUCH_TABLE ';方法6:使用sqlexceptiondeclare exit handler forsqlexception set @info='ERROR';

上述代码是6种定义处理程序的方法。

第一种，捕获sqlstate_value值。如果遇到sqlstate_value值为"42S02"，执行continue操作，并且输出"NO_SUCH_TABLE"信息。
第二种，捕获mysql_error_code值。如果遇到mysql_error_code值为1146，就执行continue操作，并且输出"NO_SUCH_TABLE"信息。
第三种，先定义条件再调用条件。这里先定义no_such_table条件，遇到1146错误就执行continue操作。
第四种，使用sqlwarning。sqlwarning捕获所有以01开头的sqlstate_value值，然后执行exit操作，并且输出"ERROE"信息。
第五种，使用not found。not found捕获所有以02开头的sqlstate_value值，然后执行exit操作，并且输出"NO_SUCH_TABLE"信息。
第六种，使用SQLEXCEPTION。sqlexception捕获所有没有被sqlwarning或not found捕获的sqlstate_value值，然后执行exit操作，并且输出"ERROR"信息。

【例9】定义条件和处理程序，具体执行的过程如下：

mysql> create table test.t(s1 int,primary key (s1));Query OK, 0 rows affected (0.14 sec)mysql> delimiter //mysql> create procedure handlerdemo()
    -> begin
    -> declare continue handler for sqlstate '23000' set @x2=1;
    -> set @x =1;
    -> insert into test.t values(1);
    -> set @x=2;
    -> insert into test.t values(1);
    -> set @x=3;
    -> end;
    -> //Query OK, 0 rows affected (0.06 sec)[调用存储过程]mysql> delimiter ;mysql> call handlerdemo();Query OK, 0 rows affected (0.08 sec)[查看调用过程结果]mysql> select @x;+------+| @x   |+------+|    3 |+------+1 row in set (0.00 sec)

可以看到，@x 是一个用户变量，执行结果@x等于3，这表明MySQL被执行到程序末尾。

• "var_name"表示用户变量，使用set语句为其赋值。用户变量与连接有关，一个客户端定义的变量不能被其他客户端看到或使用。当客户端退出时，该客户端连接的所有变量将自动释放。

(5)光标的使用

MySQL中光标只能在存储过程和函数中使用。

查询语句可能返回多条记录，如果数据量非常大，需要在存储过程和存储函数中使用光标来逐条读取查询结果集中的记录。光标必须在声明处理程序之前被声明，并且变量和条件还必须在声明光标或处理程序之前被声明。

1.声明光标

MySQL中使用declare关键字来声明光标，语法形式如下：

declare cursor_name cursor for select_statement

其中，cursor_name参数表示光标的名称；select_statement表示select语句的内容，返回一个用于创建光标的结果集。

【例10】声明名称为cursor_fruit的光标，代码如下：

declare cursor_fruit cursor for select f_name,f_price from fruits;

该代码中光标名称为cursor_fruit，select语句部分从fruits表汇总查询出f_name和f_price字段的值。

2.打开光标

open cursor_name{光标名称}

这个语句打开先前声明的名称为cursor_name的光标。

【例11】打开名称为cursor_fruit的光标，代码如下：

open cursor_fruit ;

3.使用光标

使用光标的语法格式:

fetch cursor_name into var_name [，var_name ] ... {参数名称}

其中，cursor_name参数表示光标的名称；var_name表示将光标中的select语句查询出来的信息存入该参数中，var_name必须在声明光标之前就定义好。

【例12】使用名称为cursor_fruit的光标。将查询出来的数据存入fruit_name和fruit_price两个变量中，代码如下:

fetch cursor_fruit into fruit_name,fruit_price;

4.关闭光标
关闭光标的语法格式:

close cursor_name(光标名称)

这个语句关闭先前打开的光标。
如果未被明确地关闭，那么光标将在它被声明的复合语句的末尾被关闭。

【例13】关闭名称为cursor_fruit的光标，代码如下：

close cursor_fruit;

(6)流程控制的使用

流程控制语句用来根据条件控制语句的执行。MySQL中用来构造控制流程的语句有IF语句、case语句、loop语句、leave语句、iterate语句、repeat语句和while语句。每个流程中可能包含一个单独语句，或者是使用begin…end构造的符合语句，构造可以被嵌套。

1.if语句

if语句包含多个条件判断，根据判断的结果为true或false执行相应的语句，语法格式如下：

if expr_condition then statement_list	[elseif expr_condition then statement_list]...
	[else statement_list]end if

如果expr_condition求值为真，相应的SQL语句列表被执行；如果没有expr_condition匹配，则else子句里的语句列表被执行。statement_list列表可包括一个或多个语句。

MySQL中还有一个if()函数，它不同于这里描述的if语句。

【例14】if语句示例

if val is null
	then select ‘val is null’;
	else select 'val is not null';end if

该示例判断val值是否为空，如果为空输出字符串"val is null"；否则输出字符串"val is not null"。if语句都需要使用end if来结束。

2.case语句

case是另一个进行条件判断的语句，有两种语句格式，第一种：

case case_expr	when when_value then statement_list	[when when_value then statement_list]...
	[else statement_list]end case

• case_expr表示条件判断的表达式，决定了哪一个when语句会被执行
• when_value表示表达式可能的值。
• 如果某个when_value表达式和case_expr表达式结果相同，则执行对应的then关键字后面的statement_list中的语句。
• statement_list表示不同when_value值的执行语句。

【例15】使用case流程控制语句的第1种格式，判断val值等于1、等于2或者两者都不等，SQL语句如下：

case val	when 1 then select ‘val is 1’；	when 2 then select ‘val is 2’；	else select ‘val is not 1 or 2’；end case;

当val值为1时，输出字符串"val is 1";当val值为2时，输出字符串"val is 2";否则输出字符串"val is not 1 or 2"。

case语句的第2种格式如下：

case
	when expr_condition then statement_list	[when expr_condition then statement_list]
	[else statement_list]end case

• expr_condition表示条件判断语句
• statement_list表示不同条件的执行语句

该语句中，when语句将被逐个执行，直到某个expr_condition表达式为真，则执行对应then关键字后面的statement_list语句。如果没有条件匹配，else子句里的语句被执行。

注意：存储程序中的case语句和case控制流程函数的区别：
存储程序中的case语句不能有else null子句，并且用end case替代end来终止。

【例16】使用case流程控制语句的第2种格式，判断val是否为空、小于0、大于0或者等于0，SQL语句如下：

case
	when val is null then select  ‘val is null’;
	when val < 0 then  select &#39;val is less than 0&#39;;
	when val > 0 then select 'val is greater than 0';
	else select 'val is 0';end case;

当val值为空时，输出字符串"val is null";当val值小于0时，输出字符串"val is less than 0";当val值大于0时，输出字符串"val is greater than 0";否则输出字符串"val is 0"。

3.loop语句

loop循环语句用来重复执行某些语句，与if和case语句相比，loop只是创建一个循环操作过的过程，并不进行条件判断。退出循环过程使用leave子句。loop语法格式如下:

[loop_label:] loop
	statement_listend loop [loop_label]

loop_label表示loop语句的标注名称，该参数可省略。statement_list参数表示需要循环执行的语句。

【例17】使用loop语句进行循环操作，id值小于等于10之前，将重复执行循环过程，SQL语句如下：

declare id int default 10add_loop:loopset id = id +1;
	if >=10 then leave add_loop;
	end if;end loop add_ loop;

该示例循环执行id加1的操作。当id值小于10时，循环重复执行。当id值大于或等于10时，使用leave语句退出循环。loop循环都以end loop结束。

4.leave语句

leave语句用来退出任何被标注的流程控制构造，leave语句基本格式如下：

leave label

其中，label参数表示循环的标志。leave和begin…end或循环一起被使用。

【例18】使用leave语句退出循环，代码如下：

add_num:loopset @count=@count+1;if @count=50 then leave add_num;end loop add_num;

该示例循环执行count加1的操作，当count的值等于50时，使用leave语句跳出循环。

5.iterate语句

iterater label语句将执行顺序转到语句段开头处，语法格式如下：

iterate label

iterate只可以出现在loop、repeat和while语句内。iterate的意思为"再次循环"，label参数表示循环的标志。iterate语句必须跟在循环标志前面。

【例19】iterate语句示例：

create procedure doiterate()begin
 declare p1 int default 0;
 declare p1 int default 0;
 my_loop:loop;
 set p1 = p1 + 1;
 if p1 < 10 then iterate my_loop;
 elseif p1 > 20 then leave my_loop;
 end if;
 select 'p1 is between 10 and 20';end loop my_loop;end

首先定义p1=0，当p1的值小于10时重复执行p1加1操作；当p1大于等于10并且小于等于20时，打印消息"p1 is between 10 and 20";当p1大于20时，退出循环。

6.repeat语句

repeat语句创建一个带条件判断的循环过程，每次语句执行完毕之后，会对条件表达式进行判断，若表达式为真，则循环结束；否则重复执行循环中的语句。repeat语句的语法格式如下：

[repeat_label:] repeat
	statement_list
until expr_conditionend repeat [repeat_label]

repeat_label为repeat语句的标注名称，该参数可以省略；repeat语句内的语句或语句群被重复，直至expr_condition为真。

【例20】repeat语句示例，id值等于10之前，将重复执行循环过程，代码如下：

declare id int default 0；repeatset id = id + 1；
until id >= 10end repeat;

该示例循环执行id加1的操作。当id值小于10时，循环重复执行；当id值大于或者等于10时，退出循环。repeat循环都以end repeat结束。

7.while语句

while语句创建一个带条件判断的循环过程，与repeat不同，while在执行语句执行时，先对指定的表达式进行判断，如果为真，就执行循环内的语句，否则退出循环。while语句的基本格式如下：

[while_label:] while expr_condition do
	statement_listend while [while_label]

• while_label为while语句的标注名称
• expr_condition为进行判断的表达式，如果表达式结果为真，while语句内的语句或语句群被执行，直至expr_condition为假，退出循环。

【例21】while语句示例，i值小于10时，将重复执行循环过程，代码如下：

declare i int default 0；while i <10 doset i = i + 1;end while;

相关免费学习推荐：mysql数据库(视频)

以上がMySQLのストアドプロシージャ（ストアドプロシージャとストアドファンクション）の作成について詳しく説明します。の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。