MySQL ストアド プロシージャと関数を作成する方法

1.0 ストアド プロシージャと関数の作成

ストアド プロシージャと関数の作成とは、頻繁に使用される一連の SQL ステートメントを結合し、これらの SQL ステートメント全体を MySQL サーバーに格納することです

1ストアド プロシージャを作成します

CREATE PROCEDURE procedure_name ([proc_param[,...]])
    [characteristic ...] routine_body

• procedure_name は作成するストアド プロシージャの名前を表します

• proc_param はストアド プロシージャのパラメータを表します

• characteristic はストアド プロシージャの特性を表します

• routine_body はストアド プロシージャの SQL ステートメントを表します

#procedure_name を繰り返すことはできません name

proc_param の各パラメータの構文形式は次のとおりであり、各パラメータは入出力タイプ、パラメータ名、パラメータ タイプの 3 つの部分で構成されます。入出力には IN、OUT、INOUT の 3 つのタイプがあります。 param_name はパラメータ名を表し、type はパラメータのタイプを表します。

[ IN | OUT | INOUT ] param_name type

characteristic はストアド プロシージャの特性を指定し、次の値を持ちます。

• LANGUAGE SQL: を示します。ストアド プロシージャが使用されている SQL 言語で書かれている

• [NOT] DETERMINISTIC: ストアド プロシージャの実行結果が正しいかどうかを示します。 DETERMINISTIC は、結果が確実であること、つまりすべての結果が正しいことを示しますストアド プロシージャが実行されると、同じ入力は同じ結果を取得します。出力は NOT DETERMINISTIC で、結果が不確実であることを意味します。同じ入力が異なる出力を取得する場合があります。デフォルトは NOT DETERMINISTIC

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

• SQL SECURITY {DEFINER | INVOKER}: 実行権限を持つユーザーを示します。DEFINER は定義者のみが実行できることを意味します。INVOKER は権限を持つ呼び出し元が実行できることを意味します。デフォルトは DEFINER
  です。
• COMMENT 'string': ストアド プロシージャまたは関数の説明に使用できるコメント情報

例: すべての従業員の給与をクエリするストアド プロシージャを作成するテーブル t_employee

DELIMITER $$
CREATE PROCEDURE proc_employee()
COMMENT '查询员工薪水'
BEGIN
	SELECT salary FROM t_employee
END;
$$
DELIMITER ;

MySQL のデフォルトはステートメント終了文字としてセミコロンです。ストアド プロシージャ内の SQL ステートメントは競合を避けるためにセミコロンで終了する必要があり、最初に DELIMITER $$ を使用します。 MySQL の終了文字を $$ に設定し、 DELIMITER ; を使用して終了文字をセミコロンに戻します

2。ストアド関数

#

CREATE FUNCTION fun_name([func_param,[,...]])
	[characteristic ...] routine_body

• fun_name を作成します関数名を表します

• func_param 関数のパラメータを表します

• characteristic は関数の特性を表し、値は関数のパラメータと同じですストアド プロシージャ 値は同じです

• routine_body は関数の SQL ステートメントを表します

func_param の各パラメータの構文は次のとおりです以下はパラメータ名とパラメータ タイプです

param_type type

引き続き前の例

DELIMITER $$
	CREATE FUNCTION func_employee(id INT(4))
		RETURNS INT(6)
	COMMENT '查询员工薪水'
	BEGIN
		SELECT salary FROM t_employee
	END;
	$$
DELIMITER ;

2|0 variable

MySQL では、変数を定義して使用できます。変数のスコープは BEGIN ... END プログラム セクションにあります

1. 変数の定義

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

• キーワードDECLARE は変数を宣言するために使用されます

• パラメータ var_name は変数の名前です。複数の変数を同時に定義できます

• #パラメータ タイプは、変数のタイプを指定するために使用されます

• DEFAULT value 句は、変数のデフォルト値を value に設定します。DEFAULT 句が使用されない場合、デフォルト値は NULL です。

#例: 変数 sql_test を定義し、データ型は INT、デフォルト値は 10

DECLARE test_sql INT DEFAULT 10;

2 です。変数の割り当て

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

キーワード SET は変数に値を割り当てるために使用されます
パラメータ var_name は変数です名前
パラメータ expr は代入式です。
例: 変数 sql_test を 30

SET test_sql = 30;

3|0 に代入します。 定義 条件とハンドラ

条件とハンドラの定義プログラムの実行中に発生する可能性のある問題を事前に定義し、これらの問題を解決する機能をハンドラ

##1 で定義できます。条件を定義します

DECLARE condition_name CONDITION FOR condition_value
condition_value:
SQLSTATE[VALUE] sqlstate_value|mysql_error_code

パラメータcondition_name は条件名を表します

• パラメータcondition_value は条件タイプを表します

• パラメータ sqlstate_value およびパラメータ mysql_error_code は MySQL エラーを表すことができます

• 例: can_not_find という名前の ERROR 1146 (42S02) エラーを定義します。これは 2 つの異なる方法で定義できます

// 方法一:使用sqlstate_value
DECLARE can_not_find CONDITION FOR SQLSTATE '42S02';
// 方法二:使用mysql_error_code
DECLARE can_not_find CONDITION FOR 1146;

2. ハンドラーを定義します

DECLARE handler_type HANDLER FOR condition_value[,...] proc_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: エラーが発生した後に中止することを示します 前の操作

• condition_value はエラー タイプを表し、次の値を指定できます:

• #SQLSTATE[VALUE]sqlstate_value には A 5 が含まれます。 -文字列エラー値

• condition_nameはDECLARE CONDITION

  で定義されたエラー条件名を表します。

• SQLWARNING 匹配所有 01 开头的 SQLSTATE 错误代码

• NOT FOUND 匹配所有 02 开头的 SQLSTATE 错误代码

• SQLEXCEPTION 匹配所有没有被 SQLWARNING 或 NOT FOUND 捕获的 SQLSTATE 错误代码

• mysql_error_code 匹配数值类型错误代码

• proc_statement 匹配数值类型错误代码

下面是定义处理程序的几种示例方式：

// 方法一:捕获sqlstate_value
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02'
SET @info='NOT FOUND';
// 方法二:使用mysql_error_code 
DECLARE CONTINUE HANDLER FOR 1146
SET @info='NOT FOUND';
// 方法三:先定义条件,然后调用
ECLARE can_not_find CONDITION FOR 1146;
DECLARE CONTINUE HANDLER FOR can_not_find
SET @info='NOT FOUND';
// 方法四:使用SQLWARNING
DECLARE EXIT HANDLER FOR SQLWARNING 
SET @info='ERROR';
// 方法五:使用NOT FOUND
DECLARE EXIT HANDLER FOR NOT FOUND
SET @info='NOT FOUND';
// 方法六:使用SQLEXCEPTION 
DECLARE EXIT HANDLER FOR 使用SQLEXCEPTION
SET @info='ERROR';

4|0光标

在存储过程和函数中，可以使用光标逐条读取查询结果集中可能出现的多条记录。光标必须声明在处理程序之前，声明在变量和条件之后

1. 声明光标

DECLARE cursor_name CURSOR
FOR select_statement;

• 参数 cursor_name 表示光标名称

• 参数 select_statement 表示 SELECT 语句

下面声明一个名为 cur_employee 的光标

DECLARE cur_employee CURSOR
FOR SELECT name,age FROM t_employee;

2. 打开光标

OPEN cursor_name;

• 参数 cursor_name 表示光标的名称

下面打开一个名为 cur_employee 的光标

OPEN cur_employee;

3. 使用光标

FETCH cursor_name
INTO var_name[,var_name...]

• cursor_name 表示光标的名称

• var_name 表示将光标中的 SELECT 语句查询出来的信息存入该参数，该参数必须在声明光标之前就定义好

下面打开一个名为 cur_employee 的光标，将查询的数据存入 emp_name 和 emp_age 这两个变量中

FETCH cur_employee INTO emp_name,emp_age;

4. 关闭光标

CLOSE cursor_name

• cursor_name 表示光标的名称

5|0流程控制

1. IF 语句

IF search_condition THEN statement_list
 [ELSEIF search_condition THEN statement_list]...
 [ELSE statement_list]
END IF

• 参数 search_condition 表示条件判断语句

• 参数 statement_list 表示不同条件的执行语句

下面是一个 IF 语句的示例

IF age>20 THEN SET @count1=@conut1+1;
	ELSEIF age=20 THEN @count2=@conut2+1;
	ELSE @count3=@count3+1;
END IF;

2. CASE 语句

CASE case_value
	WHEN when_value THEN statement_list
	[WHEN when_value THEN statement_list]...
	[ELSE statement_list]
END CASE

• 参数 case_value 表示条件判断的变量

• 参数 when_value 表示变量的取值

• 参数 statement_list 表示不同 when_value 的执行语句

下面的是一个 CASE 语句的示例

CASE age
	WHEN 20 THEN SET @count1=@count1+1;
	ELSE SET @count2=@count2+1;
END CASE;

3. LOOP 语句

[begin_label:]LOOP
	statement_list
END LOOP [end_label]

• 参数 begin_label 和参数 end_label 分别表示循环开始和结束的标志，必须相同，而且都可以省略

• 参数 statement_list 表示需要循环执行的语句

下面是一个 LOOP 语句的示例

add_num:LOOP
	SELECT @count1=@count1+1;
END LOOP add_num

4. LEAVE 语句

主要用于跳出循环

LEAVE label

• 参数 label 表示循环的标志

下面是一个 LEAVE 语句的示例

add_num:LOOP
	SELECT @count1=@count1+1;
	IF @count1=100 THEN
		LEAVE add_num;
END LOOP add_num

5. ITERATE 语句

也是用于跳出循环，不过是跳出本次循环，直接进入下次循环，类似 Java 的 continue

ITERATE label

ITERATE label

• 参数 label 表示循环的标志

下面是一个 ITERATE 语句的示例

add_num:LOOP
	SELECT @count1=@count1+1;
	IF @count1=100 THEN
		LEAVE add_num;
	ELSE IF MOD(@count1,3)=0 THEN
		ITERATE add_num;
	SELECT * FROM employee;
END LOOP add_num

6. REPEAT 语句

REPEAT 语句是由条件的控制循环语句，当满足特定条件就会退出循环语句

[begin_label:]REPEAT
	statement_list
	UNTIL search_condition
END REPEAT [end_label]

• 参数 statement_list 表示需要循环执行的语句

• 参数 search_condition 表示结束循环的条件，满足该条件即结束循环

下面是一个 REPEAT 语句的示例

add_num:REPEAT
	SELECT @count1=@count1+1;
	UNTIL @count1=100
END REPEAT add_num

7. WHILE 语句

WHILE 语句也是有条件控制的循环语句，与 REPEAT 语句不同的是，WHILE 语句是当满足条件时执行循环内的语句

[begin_label:]WHILE search_condition DO
	statement_list
END WHILE [end_label]

• 参数 statement_list 表示需要循环执行的语句

• 参数 search_condition 表示结束循环的条件，满足该条件即结束循环

下面是一个 WHILE 语句的示例

WHILE @count<100 DO
	SET @count=@count+1;
END WHILE;

6|0操作存储过程和函数

1. 调用存储过程

在 MySQL 中，使用 CALL 语句来调用存储过程

CALL proc_name([parameter[,...]])

• proc_name 是存储过程的名称

• paramter 是指存储过程的参数

2. 调用存储函数

在 MySQL 中，存储函数的使用方法和 MySQL 内部函数是一样的

3. 使用 SHOW STATUS 语句查看存储过程和函数的状态

SHOW {PROCEDURE|FUNCTION} STATUS{LIKE 'pattern'}

• 参数 PROCEDURE 表示查询存储过程，FUNCTION 表示存储函数

• 参数 LIKE 'pattern' 用来匹配存储过程或函数的名称

4. 使用 SHOW CREATE 语句查看存储过程和函数的定义

SHOW CREATE {PROCEDURE|FUNCTION} proc_name

5. 从 information_schema.Routine 表中查看存储过程和函数的信息

SELECT * FROM information_schema.Routine
WHERE ROUTINE_NAME='proc_name'

6. 修改存储过程和函数

ALTER {PROCEDURE|FUNCTION} proc_name[characteristic...];
characteristic:
	{CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA}
|SQL SECURITY{DEFINER|INVOKER}
|COMMENT 'string'

• 参数 proc_name 表示存储过程或函数的名称

• 参数 characteristic 指定存储过程的特性：

• CONTAINS SQL 表示子程序包含 SQL 语句，但不包含读或写数据的语句

• NO SQL 表示子程序不包含 SQL 语句

• READS SQL DATA 表示子程序中包含读数据的语句

• MODIFIES SQL DATA 表示子程序中包含写数据的语句

• SQL SECURITY{DEFINER|INVOKER} 指明谁有权限执行：

• DEFINER 表示只有定义者才能执行

• INVOKER 表示调用者可以执行

• COMMENT 'string' 是注释信息

7. 删除存储过程和函数

通过 DROP 语句删除存储过程

DROP PROCEDURE proc_name;

通过 DROP FUNCTION 语句删除存储函数

DROP FUNCTION func_name;

以上がMySQL ストアド プロシージャと関数を作成する方法の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。