Mysql stored procedure details, mysql Stored Procedure

Mysql stored procedure details, mysql Stored Procedure

MySQL Stored Procedure

14.1.1 create a 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 indicates the parameter list of the stored procedure; characteristic parameter specifies the features of the stored procedure; routine_body parameter is the content of the SQL code, you can use BEGIN... END indicates the start and END of the SQL code.

Each parameter in proc_parameter consists of three parts. These three parts are the input and output types, parameter names, and parameter types respectively. The format is as follows:

[IN | OUT | INOUT] param_name type

IN indicates the input parameter, OUT indicates the output parameter, INOUT indicates the input or output, and param_name indicates 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.

The characteristic parameter has multiple values. The values are described as follows:

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

[NOT] DETERMINISTIC: Specifies whether the execution result of a stored procedure is correct. DETERMINISTIC indicates that the result is correct. Each time a stored procedure is executed, the same input will get the same output. Not deterministic indicates that the result is uncertain. Different outputs may be obtained for the same input. The result is uncertain by default.

{Contains SQL | no SQL | reads SQL data | modifies SQL data}: limits on the use of SQL statements by subprograms. Contains SQL indicates that the subprogram contains SQL statements but does not contain statements for reading or writing DATA. no SQL indicates that the subprogram does not contain SQL statements. reads SQL DATA indicates that the subprogram CONTAINS read DATA; modifies SQL DATA indicates the statement that contains the written DATA in the subprogram. By default, the system specifies contains SQL.

SQL SECURITY {DEFINER | INVOKER}: specifies who has the permission to execute the task. DEFINER indicates that only the DEFINER can execute the job. INVOKER indicates that the caller can execute the job. By default, the permission specified by the system is DEFINER.

COMMENT 'string': COMMENT information.

Tip: When a stored procedure is created, contains SQL is specified by default, indicating that SQL statements are used in the stored procedure. However, if no SQL statement is used in the stored procedure, it is best to set it to no SQL. In addition, the best part of the stored procedure is the COMMENT part, which makes it easier to read the stored procedure code in the future.

[Example 14-1] Create a stored procedure named num_from_employee. 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 preceding code, the stored procedure name is num_from_employee, the input variable is emp_id, and the output variable is count_num. The SELECT statement queries records whose d_id value is equal to emp_id from the employee table, calculates the number of records with the same d_id value using COUNT (*), and finally stores the calculation result in count_num. The code execution result 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, no error message is reported, indicating that the storage function has been successfully created. You can call this stored procedure later. The database executes the SQL statements in the stored procedure.

Description: The default statement terminator in MySQL is a semicolon (;). The SQL statement in the stored procedure must end with a semicolon. To avoid conflicts, use "DELIMITER &" to set the MySQL terminator &&. Finally, use "DELIMITER;" to restore the component number. This is the same as creating a trigger.

14.1.2 create a storage function

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

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

The sp_name parameter indicates the name of the stored function, func_parameter indicates the list of stored function parameters, RETURNS type indicates the type of the returned value, and characteristic parameter specifies the features of the stored function, the value of this parameter is the same as the value in the stored procedure. For details, see section 14.1.1. The routine_body parameter is the content of the SQL code. You can use BEGIN... END indicates the start and END of the SQL code.

Func_parameter can be composed of multiple parameters, each of which consists of the parameter name and parameter type. Its form is as follows:

Param_name type
The parameter param_name is the parameter name of the stored function. The type parameter specifies the parameter type of the stored function. This type can be any data type of the MySQL database.

[Example 14-2] Create a storage 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 stored function is name_from_employee; the parameter of this function is emp_id; the returned value is of the VARCHAR type. The SELECT statement queries records whose num value is equal to emp_id from the employee table, and returns the value of the name field of the record. The code execution result 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 result shows that the storage function has been created successfully. This function is used in the same way as MySQL internal functions.

14.1.3 use of Variables

Variables can be defined and used in stored procedures and functions. You can use the DECLARE keyword to define variables. Then you can assign values to variables. The scope of these variables is BEGIN... In the END section. This section describes how to define variables and assign values to variables.

1. Define Variables

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

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

Specifically, the DECLARE keyword is used to DECLARE the variable; 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. If the DEFAULT clause is not used, the DEFAULT value is NULL.

[Example 14-3] The variable my_ SQL is defined below. The data type is INT type and the default value is 10. The Code is as follows:

DECLARE my_ SQL INT DEFAULT 10;

2. assign values to variables

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

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

The SET keyword is used to assign values to variables, the var_name parameter is the variable name, And the expr parameter is a value expression. A set statement can assign values to multiple variables at the same time. values of each variable are separated by commas.

[Example 14-4] The variable my_ SQL is assigned with a value of 30. The Code is as follows:

SET my_ SQL = 30;

MySQL can also use SELECT... The INTO statement assigns a value to the variable. The basic syntax is as follows:

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

The col_name parameter indicates the name of the queried field, the var_name parameter indicates the name of the variable, the table_name parameter indicates the table name, and the condition parameter indicates the query condition.

[Example 14-5] The following table queries a record with id 2 from the employee table and assigns the d_id value of the record to the variable my_ SQL. The Code is as follows:

SELECT d_id INTO my_ SQL
FROM employee WEHRE id = 2;

14.1.4 define conditions and handling procedures

Defining conditions and processing programs is a problem that may occur during program execution. You can also define solutions to these problems in the handler. This method can predict possible problems in advance and propose solutions. In this way, the program's ability to handle problems can be enhanced to avoid abnormal program stop. MySQL uses the DECLARE keyword to define conditions and processing programs. This section describes in detail how to define conditions and processing programs.

1. define conditions

MySQL can use the DECLARE keyword to define conditions. The basic syntax is as follows:

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

The condition_name parameter indicates the condition name, The condition_value parameter indicates the condition type, and the sqlstate_value parameter and mysql_error_code parameter both indicate MySQL errors. For example, in ERROR 1146 (42S02), sqlstate_value is 42S02, and mysql_error_code is 1146.

[Example 14-6] the ERROR "ERROR 1146 (42S02)" is defined as "can_not_find. You can define it in two different ways. The Code is as follows:

// Method 1: Use sqlstate_value DECLARE can_not_find condition for sqlstate '42s02 '; // Method 2: Use mysql_error_code DECLARE can_not_find condition for 1146;

2. Define the Handler

MySQL can use the DECLARE keyword to define the processing program. The basic syntax is as follows:

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 

The handler_type parameter specifies the error handling method. This parameter has three values. The three values are CONTINUE, EXIT, and UNDO respectively. CONTINUE indicates that if an error is encountered, it is not processed. if the error is encountered, it is exited immediately. If the error is encountered, UNDO indicates that the operation before the error is recalled. This method is not supported currently in MySQL.

Note: Generally, when an error occurs during execution, you should immediately stop executing the following statement and recall the previous operation. However, UNDO operations are not supported in MySQL. Therefore, it is best to execute the EXIT operation in case of an error. If you can predict the error type and handle it in advance, you can perform the CONTINUE operation.

The condition_value parameter specifies the error type. This parameter has six values. Sqlstate_value and mysql_error_code share the same meaning with the condition definition. Condition_name is the condition name defined by DECLARE. SQLWARNING indicates all sqlstate_value values starting with 01. Not found indicates all sqlstate_value values starting with 02. SQLEXCEPTION indicates all sqlstate_value values that are NOT captured by SQLWARNING or not found. Sp_statement indicates the execution statements of some stored procedures or functions.

[Example 14-7] The following are several methods for defining the handler. The Code is as follows:

// Method 1: capture sqlstate_value declare continue handler for sqlstate '42s02 'SET @ info = 'can not find'; // Method 2: capture mysql_error_code declare continue handler for 1146 SET @ info = 'can not find '; // method 3: first define the CONDITION and then call DECLARE can_not_find condition for 1146; declare continue handler for can_not_find SET @ info = 'can not find '; // Method 4: Use sqlwarning declare exit handler for sqlwarning set @ info = 'error'; // Method 5: use not found declare exit handler for not found set @ info = 'can not find '; // Method 6: Use sqlexception declare exit handler for sqlexception set @ info = 'error ';

The above code is six methods for defining the handler. The first method is to capture the sqlstate_value value. If sqlstate_value is 42S02, perform the CONTINUE operation and output "can not find" information. The second method is to capture the value of mysql_error_code. If the value of mysql_error_code is 1146, perform the CONTINUE operation and output "can not find" information. The third method is to define the conditions before calling the conditions. Here, the can_not_find condition is defined first, and the CONTINUE operation will be executed in case of a 1146 error. The fourth method is to use SQLWARNING. SQLWARNING captures all sqlstate_value values starting with 01, then executes the EXIT Operation, and outputs "ERROR" information. The fifth method is not found. Not found captures all sqlstate_value values starting with 02, then executes the EXIT Operation, and outputs "can not find" information. The sixth method is to use SQLEXCEPTION. SQLEXCEPTION captures all sqlstate_value values NOT captured by SQLWARNING or not found, then executes the EXIT Operation and outputs "ERROR" information.

Thank you for reading this article and hope to help you. Thank you for your support for this site!