Detailed explanation of mysql stored procedures_MySQL

mysql/>MySQL stored procedure

14.1.1 Create stored procedure

In MySQL, the basic form of creating a stored procedure is as follows:

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

Among them, the sp_name parameter is the name of the stored procedure; proc_parameter represents the parameter list of the stored procedure; the characteristic parameter specifies the characteristics of the stored procedure; the routine_body parameter is the content of the SQL code, and BEGIN...END can be used to mark the start 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

Among them, IN represents the input parameter; OUT represents the output parameter; INOUT represents 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 of the MySQL database Any data type.

The characteristic parameter has multiple values. Its value description is as follows:

LANGUAGE SQL: Explain that the routine_body part is composed of SQL language statements, which is also the default language of the database system.

[NOT] DETERMINISTIC: Indicates whether the execution result of the stored procedure is deterministic. DETERMINISTIC means the result is deterministic. Each time the stored procedure is executed, the same input will result in the same output. NOT DETERMINISTIC means that the result is non-deterministic and the same input may result in different outputs. By default, the results are non-deterministic.

{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }: Indicates the restrictions on the use of SQL statements by subprograms. CONTAINS SQL means that the subprogram contains SQL statements, but does not contain statements for reading or writing data; NO SQL means that the subprogram does not contain SQL statements; READS SQL DATA means that the subprogram contains statements for reading data; MODIFIES SQL DATA means that the subprogram contains statements for reading data. Contains statements for writing data. By default, the system specifies CONTAINS SQL.

SQL SECURITY { DEFINER | INVOKER }: Specify who has permission to execute. DEFINER means that only the definer can execute it; INVOKER means that the caller can execute it. By default, the system-specified permission is DEFINER.

COMMENT 'string': comment information.

Tip: When creating a stored procedure, the system specifies CONTAINS SQL by default, indicating that SQL statements are used in the stored procedure. However, if no SQL statements are used in the stored procedure, it is best to set it to NO SQL. Moreover, it is best to make simple comments on the stored procedure in the COMMENT section so that it is more convenient to read the code of the stored procedure in the future.

[Example 14-1] Create a stored procedure named num_from_employee below. The code is as follows:

CREATE PROCEDURE num_from_employee (IN emp_id INT, OUT count_num INT ) 
     READS SQL DATA 
     BEGIN 
       SELECT COUNT(*) INTO count_num 
       FROM employee 
       WHERE d_id=emp_id ; 
     END 

In the above code, the stored procedure name is num_from_employee; the input variable is emp_id; the output variable is count_num. The SELECT statement queries the records whose d_id value is equal to emp_id from the employee table, uses COUNT(*) to calculate the number of records with the same d_id value, and finally stores the calculation results in count_num. The execution result of the code is as follows:

mysql> DELIMITER && 
mysql> CREATE PROCEDURE num_from_employee
(IN emp_id INT, OUT count_num INT ) 
  -> READS SQL DATA 
  -> BEGIN 
  -> SELECT COUNT(*) INTO count_num 
  -> FROM employee 
  -> WHERE d_id=emp_id ; 
  -> END && 
Query OK, 0 rows affected (0.09 sec) 
mysql> DELIMITER ; 

After the code is executed, if no error message is reported, it means that the storage function has been successfully created. This stored procedure can be called later, and the SQL statements in the stored procedure will be executed in the database.

Note: The default statement terminator in MySQL is semicolon (;). SQL statements in stored procedures require a semicolon to end. In order to avoid conflicts, first use "DELIMITER &&" to set the MySQL terminator to &&. Finally, use "DELIMITER;" to restore the terminator to the semicolon. This is the same as when creating a trigger.

14.1.2 Create stored function

In MySQL, the basic form of creating a stored function is as follows:

CREATE FUNCTION sp_name ([func_parameter[,...]]) 
    RETURNS type 
    [characteristic ...] routine_body 

Among them, the sp_name parameter is the name of the stored function; func_parameter represents the parameter list of the stored function; RETURNS type specifies the type of return value; the characteristic parameter specifies the characteristics of the stored function. The value of this parameter is the same as the value in the stored procedure. , please refer to the content in section 14.1.1; the routine_body parameter is the content of the SQL code, and BEGIN...END can be used to mark the beginning and end of the SQL code.

func_parameter can be composed of multiple parameters, where each parameter consists of parameter name and parameter type, and its form is as follows:

param_name type
Among them, the param_name parameter is the parameter name of the stored function; the type parameter specifies the parameter type of the stored function, which can be any data type of the MySQL database.

[Example 14-2] Next create a stored function named name_from_employee. The code is as follows:

CREATE FUNCTION name_from_employee (emp_id INT ) 
     RETURNS VARCHAR(20) 
     BEGIN 
       RETURN (SELECT name 
       FROM employee 
       WHERE num=emp_id ); 
     END 

In the above code, the name of the storage function is name_from_employee; the parameter of this function is emp_id; the return value is of VARCHAR type. The SELECT statement queries the record whose num value is equal to emp_id from the employee table, and returns the value of the name field of the record. The execution result of the code is as follows:

mysql> DELIMITER && 
mysql> CREATE FUNCTION name_from_employee (emp_id INT ) 
  -> RETURNS VARCHAR(20) 
  -> BEGIN 
  -> RETURN (SELECT name 
  -> FROM employee 
  -> WHERE num=emp_id ); 
  -> END&& 
Query OK, 0 rows affected (0.00 sec) 
mysql> DELIMITER ; 

The results show that the storage function has been created successfully. The use of this function is the same as the use of MySQL internal functions.

14.1.3 Use of variables

在存储过程和函数中，可以定义和使用变量。用户可以使用DECLARE关键字来定义变量。然后可以为变量赋值。这些变量的作用范围是BEGIN…END程序段中。本小节将讲解如何定义变量和为变量赋值。

1．定义变量

MySQL中可以使用DECLARE关键字来定义变量。定义变量的基本语法如下：

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

其中， DECLARE关键字是用来声明变量的；var_name参数是变量的名称，这里可以同时定义多个变量；type参数用来指定变量的类型；DEFAULT value子句将变量默认值设置为value，没有使用DEFAULT子句时，默认值为NULL。

【示例14-3】 下面定义变量my_sql，数据类型为INT型，默认值为10。代码如下：

DECLARE  my_sql  INT  DEFAULT 10 ;

2．为变量赋值

MySQL中可以使用SET关键字来为变量赋值。SET语句的基本语法如下：

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

其中，SET关键字是用来为变量赋值的；var_name参数是变量的名称；expr参数是赋值表达式。一个SET语句可以同时为多个变量赋值，各个变量的赋值语句之间用逗号隔开。

【示例14-4】 下面为变量my_sql赋值为30。代码如下：

SET  my_sql = 30 ;

MySQL中还可以使用SELECT…INTO语句为变量赋值。其基本语法如下：

SELECT  col_name[,…]  INTO  var_name[,…] 
    FROM  table_name  WEHRE  condition

其中，col_name参数表示查询的字段名称；var_name参数是变量的名称；table_name参数指表的名称；condition参数指查询条件。

【示例14-5】 下面从employee表中查询id为2的记录，将该记录的d_id值赋给变量my_sql。代码如下：

SELECT  d_id  INTO  my_sql 
        FROM  employee  WEHRE  id=2 ; 

14.1.4  定义条件和处理程序

定义条件和处理程序是事先定义程序执行过程中可能遇到的问题。并且可以在处理程序中定义解决这些问题的办法。这种方式可以提前预测可能出现的问题，并提出解决办法。这样可以增强程序处理问题的能力，避免程序异常停止。MySQL中都是通过DECLARE关键字来定义条件和处理程序。本小节中将详细讲解如何定义条件和处理程序。

1．定义条件

MySQL中可以使用DECLARE关键字来定义条件。其基本语法如下：

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。

【示例14-6】 下面定义"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表示一些存储过程或函数的执行语句。

【示例14-7】 下面是定义处理程序的几种方式。代码如下：

//方法一：捕获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'; 

The above codes are 6 ways to define handlers. The first method is to capture the sqlstate_value value. If the sqlstate_value value is 42S02, perform the CONTINUE operation and output the "CAN NOT FIND" message. The second method is to capture the mysql_error_code value. If the mysql_error_code value is 1146, perform the CONTINUE operation and output the "CAN NOT FIND" message. The third method is to define the condition first and then call the condition. Here we first define the can_not_find condition, and execute the CONTINUE operation when encountering a 1146 error. The fourth method is to use SQLWARNING. SQLWARNING captures all sqlstate_value values ​​starting with 01, then performs the EXIT operation and outputs "ERROR" information. The fifth method is to use NOT FOUND. NOT FOUND captures all sqlstate_value values ​​starting with 02, then performs the EXIT operation and outputs "CAN NOT FIND" information. The sixth method is to use SQLEXCEPTION. SQLEXCEPTION captures all sqlstate_value values ​​that are not captured by SQLWARNING or NOT FOUND, then performs the EXIT operation and outputs "ERROR" information.

Thank you for reading this article, I hope it can help everyone, thank you for your support of this site!