【MySQL 08】ストアドプロシージャ

まず、SQLストアドプロシージャについては、Javaでの定義メソッドと呼び出しメソッドに似ています。

1. ストアド プロシージャを作成します。MySQL では、ストアド プロシージャの基本形式:

CREATE PROCEDURE sp_name ([proc_parameter[,...]])  
        [characteristic ...] routine_body

sp_name パラメータはストアド プロシージャの名前です。
proc_parameter はストアド プロシージャのパラメータ リストを表します。
特性パラメータの仕様 ストアド プロシージャの特性;
routine_body パラメータは、SQL コードの開始と終了をマークするために使用できます。
proc_parameter の各パラメータは 3 つの部分で構成されます。これら 3 つの部分は、入力タイプと出力タイプ、パラメータ名、パラメータ タイプです。その形式は次のとおりです:

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

• INOUT は入力または出力のいずれかを表します。 name;

• type パラメータは、MySQL データベースの任意のデータ型であるストアド プロシージャのパラメータ タイプを指定します。まず、デフォルトのステートメント終了 ';' を '&&' などの別のものに変更し、ストアド プロシージャで定義されたセミコロンがステートメントの終了とみなされないようにします (そうしないとステートメントが直接送信されます)。定義後、「;」をデフォルトのターミネータに戻す必要があります。
• 2. パラメータのないストアドプロシージャ

  [ IN | OUT | INOUT ] param_name type

  4. 戻り値のあるストアドプロシージャ

create procedure 过程名(参数...)
begin
    SQL语句...end

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

1.変数の定義

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

  call 过程名(实参)

その中で、DECLARE キーワードは変数の名前であり、複数の変数を同時に定義できます。変数のタイプを指定するために使用されます。 DEFAULT value 句 DEFAULT 句が使用されない場合、デフォルト値は NULL に設定されます。

りー

2.変数への値の割り当て

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

//建立存储过程mysql> delimiter %%
mysql> create procedure p1()    
-> begin    
-> select Sname,Ssex from student;    
-> end %%mysql> delimiter ;  
//调用存储过程mysql> call p1(); 
+--------+------+| Sname  | Ssex |
+--------+------+| 赵英    | 女   |
| 郑美丽  | 女   |
| 李哲    | 男   |
| 王洋    | 女   |
| 王平    | 男   |
| 赵东新  | 男   |
| 王新    | 男   |
| 陶丽平  | 男   |
| 陈文    | 女   |
| 蔡天    | 女   || 杨洋    | 男   |
+--------+------+

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

//查询mysql> select * from employee;
+------------+------------+------------+--------------+| employeeID | name       | job        | departmentID |
+------------+------------+------------+--------------+| 6651       | Ajay Patel | Programmer |          128 |
| 7513       | Nora Edwar | Programmer |          128 |
| 9006       | Candy Burn | Systems Ad |          128 |
| 9842       | Ben Smith  | DBA        |           42 |
| 9843       | Pert Park  | DBA        |           42 |
| 9845       | Ben Patel  | DBA        |          128 |
| 9846       | Red Right  | NULL       |          128 |
| 9847       | Run Wild   | NULL       |          128 |
| 9848       | Rip This J | NULL       |          128 |
| 9849       | Rip This J | NULL       |          128 |
| 9850       | Reader U   | NULL       |          128 || 6651       | Ajay Patel | Programmer |          128 |
+------------+------------+------------+--------------+//建立存储过程mysql> delimiter &&
mysql> create procedure p4(in employeeID char(4),in name varchar(20),in job varchar(20),in departmentID int(11))    -> begin    
-> insert into employee value(employeeID,name,job,departmentID);    -> select * from employee;    -> end &&mysql> delimiter ;  
//调用存储过程mysql> call p4('7322','cid','udewhuuwdwq',127);
+------------+------------+-------------+--------------+| employeeID | name       | job         | departmentID |
+------------+------------+-------------+--------------+| 6651       | Ajay Patel | Programmer  |          128 |
| 7513       | Nora Edwar | Programmer  |          128 |
| 9006       | Candy Burn | Systems Ad  |          128 |
| 9842       | Ben Smith  | DBA         |           42 |
| 9843       | Pert Park  | DBA         |           42 |
| 9845       | Ben Patel  | DBA         |          128 |
| 9846       | Red Right  | NULL        |          128 |
| 9847       | Run Wild   | NULL        |          128 |
| 9848       | Rip This J | NULL        |          128 |
| 9849       | Rip This J | NULL        |          128 |
| 9850       | Reader U   | NULL        |          128 |
| 6651       | Ajay Patel | Programmer  |          128 || 7322       | cid        | udewhuuwdwq |          127 |
+------------+------------+-------------+--------------+

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

SELECT col_name[,…] INTO var_name[,…]

FROM table_name WEHRE 条件

このうち、col_name パラメータはクエリのフィールド名を表し、var_name パラメータは変数の名前を表します。 ; table_name パラメータはテーブルの名前を指します。 条件パラメータはクエリ条件を指します。

//建立存储过程mysql> delimiter &&
mysql> create procedure p9(in employeeID char(4),in name varchar(20),in job varchar(20),in departmentID int(11),out num int)
    -> begin
    -> insert into employee value(employeeID,name,job,departmentID);
    -> select count(*) into num from employee;
    -> end &&

mysql> delimiter ; 

//调用存储过程mysql> call p9('3632','dueh','xianggang',28,@num);//注意输出变量的输出mysql> select @num;
+------+
| @num |
+------+
|   15 |
+------+//////////////////////////系统变量名称：@@变量名/// //用户变量名称：@变量名 ///

6. 条件とハンドラーを定義する

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

1.条件を定義する

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

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的错误。例如ERROR 1146 (42S02)中，sqlstate_value值是42S02，mysql_error_code值是1146。

//定义"ERROR 1146 (42S02)"这个错误，名称为can_not_find
//方法一：使用sqlstate_value  DECLARE  can_not_find  CONDITION  FOR  SQLSTATE  '42S02' ;  
//方法二：使用mysql_error_code  DECLARE  can_not_find  CONDITION  FOR  1146 ;

2．定义处理程序

MySQL中可以使用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个取值。这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表示一些存储过程或函数的执行语句。

//定义处理程序的几种方式
//方法一：捕获sqlstate_value  
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02'SET @info='CAN NOT FIND';  
//方法二：捕获mysql_error_code  
DECLARE CONTINUE HANDLER FOR 1146 SET @info='CAN NOT FIND';  
//方法三：先定义条件，然后调用  
DECLARE  can_not_find  CONDITION  FOR  1146 ;  
DECLARE CONTINUE HANDLER FOR can_not_find SET 
@info='CAN NOT FIND';  
//方法四：使用SQLWARNING  
DECLARE EXIT HANDLER FOR SQLWARNING SET @info='ERROR';  
//方法五：使用NOT FOUND  
DECLARE EXIT HANDLER FOR NOT FOUND SET @info='CAN NOT FIND';  
//方法六：使用SQLEXCEPTION  
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info='ERROR';

• 第一种方法是捕获sqlstate_value值。如果遇到sqlstate_value值为42S02，执行CONTINUE操作，并且输出”CANNOT FIND”信息。

• 第二种方法是捕获mysql_error_code值。如果遇到mysql_error_code值为1146，执行CONTINUE操作，并且输出”CAN NOT FIND”信息。

• 第三种方法是先定义条件，然后再调用条件。这里先定义can_not_find条件，遇到1146错误就执行CONTINUE操作。

• 第四种方法是使用SQLWARNING。SQLWARNING捕获所有以01开头的sqlstate_value值，然后执行EXIT操作，并且输出”ERROR”信息。

• 第五种方法是使用NOT FOUND。NOT FOUND捕获所有以02开头的sqlstate_value值，然后执行EXIT操作，并且输出”CAN NOT FIND”信息。

• 第六种方法是使用SQLEXCEPTION。SQLEXCEPTION捕获所有没有被SQLWARNING或NOT FOUND捕获的sqlstate_value值，然后执行EXIT操作，并且输出”ERROR”信息。

以上就是 【MySQL 08】存储过程的内容，更多相关内容请关注PHP中文网（www.php.cn）！