[MySQL 08] Stored procedure

First of all, for SQL stored procedures, it is similar to the definition method and calling method in Java.

1. Create a stored procedure

In MySQL, the basic form of a stored procedure:

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

• The sp_name parameter is the name of the stored procedure;

• proc_parameter represents the parameter list of the stored procedure;

• characteristic parameter specifies the characteristics of the stored procedure;

• routine_body The parameter is the content of the SQL code. You can use BEGIN...END to mark the beginning and end of the SQL code.

• Each parameter in proc_parameter consists of 3 parts. These three parts are input and output types, parameter names and parameter types. Its form is as follows:

[ IN | OUT | INOUT ] param_name type

• IN represents input parameters;

• OUT represents output parameters;

• INOUT means it can be either input or output;

• The param_name parameter is the parameter name of the stored procedure;

• The type parameter specifies the parameter type of the stored procedure, which can be any data type of the MySQL database

Simplified definition:

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

Call:

call 过程名(实参)

Note:
Before defining, you need to change the default statement end ';' to something else, such as '&&', so that the semicolon defined in the stored procedure will not be regarded as the end of the statement ( Otherwise it will be submitted directly). After defining it, you need to restore ';' to the default terminator.

2. Stored procedures without parameters

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

3. Stored procedures with parameters

//查询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 |
+------------+------------+-------------+--------------+

4. Stored procedures with return values ​​

//建立存储过程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 |
+------+//////////////////////////系统变量名称：@@变量名/// //用户变量名称：@变量名 ///

5. Use of variables

In stored procedures and functions, variables can be defined and used. Users can use the DECLARE keyword to define variables. The variable can then be assigned a value. The scope of these variables is in the BEGIN...END program section. This section will explain how to define variables and assign values ​​to variables.

1. Defining variables

You can use the DECLARE keyword to define variables in MySQL. The basic syntax for defining variables is as follows:

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

Among them, the DECLARE keyword is used to declare variables; the var_name parameter is the name of the variable, where multiple variables can be defined at the same time; the type parameter is used to specify the type of the variable; The DEFAULT value clause sets the default value of the variable to value. When the DEFAULT clause is not used, the default value is NULL.

DECLARE  my_sql  INT  DEFAULT 10 ; //定义变量my_sql，数据类型为INT型，默认值为10

2. Assigning values ​​to variables

You can use the SET keyword in MySQL to assign values ​​to variables. The basic syntax of the SET statement is as follows:

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

Among them, the SET keyword is used to assign values ​​to variables; the var_name parameter is the name of the variable; the expr parameter is the assignment expression. A SET statement can assign values ​​to multiple variables at the same time. The assignment statements for each variable are separated by commas.

SET my_sql = 30 ; //为变量my_sql赋值为30

You can also use the SELECT...INTO statement to assign values ​​to variables in MySQL. The basic syntax is as follows:

SELECT col_name[,…] INTO var_name[,…]
FROM table_name WEHRE condition
Among them, the col_name parameter represents the query field name; the var_name parameter is the name of the variable; The table_name parameter refers to the name of the table; the condition parameter refers to the query condition.

SELECT d_id INTO my_sql  
   FROM employee WEHRE id=2 ; //从employee表中查询id为2的记录，将该记录的d_id值赋给变量my_sql

6. Define conditions and handlers

Defining conditions and handlers is to define in advance the problems that may be encountered during program execution. And solutions to these problems can be defined in the handler. This approach can predict possible problems in advance and propose solutions. This can enhance the program's ability to handle problems and prevent the program from stopping abnormally. In MySQL, conditions and handlers are defined through the DECLARE keyword. This section explains in detail how to define conditions and handlers.

1. Define conditions

The DECLARE keyword can be used in MySQL to define conditions. Its basic syntax is as follows:

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）！