MySQL stored procedures detailed _mysql

MySQL stored procedures

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

Where 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 attributes of the stored procedure, and the Routine_body parameter is the content of the SQL code, which can be used with the begin ... End to flag the beginning and ending of the SQL code.

Each parameter in the Proc_parameter is made up of 3 parts. These 3 parts are input output type, parameter name and parameter type respectively. The form is as follows:

[In | Out | INOUT] Param_name Type

Where, in represents the input parameter, out represents the output parameter, the InOut representation can be either input or output, the Param_name parameter is the parameter name of the stored procedure, and 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 value of the note is as follows:

LANGUAGE sql: Description The routine_body part is made up of SQL language statements, which is also the default language for the database system.

[NOT] Deterministic: Indicates whether the execution result of the stored procedure is determined. Deterministic says the result is OK. The same input gets the same output each time the stored procedure is executed. The not deterministic indicates that the result is indeterminate and that the same input may get different outputs. By default, the result is indeterminate.

{CONTAINS SQL | NO SQL | Reads SQL DATA | Modifies SQL DATA}: Indicates the limitations of a subroutine using SQL statements. CONTAINS SQL represents a subroutine that contains SQL statements, but does not contain statements that read or write data; No SQL represents a subroutine that contains no SQL statements; Reads SQL data represents statements that contain read data in a subroutine; modifies SQL Data represents a statement in a subroutine that contains write data. By default, the system is specified as contains SQL.

SQL Security {Definer | Invoker}: Indicates who has permission to execute. Definer says that only the person who defines it can execute it; invoker indicates that the caller can execute it. By default, the system-specified permissions are definer.

COMMENT ' string ': Comment information.

Tip: When you create a stored procedure, the system defaults to contains SQL, which means that the SQL statement is used in the stored procedure. However, if you do not use SQL statements in stored procedures, it is best to set to no SQL. Furthermore, it is best to simply annotate stored procedures in the comment section in order to make it easier to read the stored procedure code later.

Under 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 (*) in To 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 employee table for records with d_id values equal to emp_id, and counts (*) to calculate the number of records with the same d_id value, and then the results are stored in count_num. The code performs the following results:

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; 

When the code completes, no error message is reported, which means that the stored function has been created successfully. This stored procedure can be invoked later, and the SQL statements in the stored procedure are executed in the database.

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

14.1.2 Create a 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 

Where 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 types of the return value; The characteristic parameter specifies the attributes of the stored function. The value of this parameter is the same as the value in the stored procedure, please refer to the contents of the 14.1.1 section, the Routine_body parameter is the content of the SQL code, you can use the BEGIN ... End to flag the beginning and ending of the SQL code.

A func_parameter can consist of multiple parameters, each of which consists of a parameter name and a parameter type, in the form of the following:

Param_name type
Where 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.

Under Example 14-2, create a stored function named Name_from_employee. The code is as follows:

CREATE FUNCTION Name_from_employee (emp_id INT) 
     RETURNS VARCHAR 
     BEGIN return 
       (SELECT name 
       from Employee 
       WHERE num=emp_id); 
     End 

In the preceding code, the name of the stored function is name_from_employee; the function's argument is emp_id; The return value is the varchar type. The SELECT statement queries the NUM value equal to the emp_id record from the employee table and returns the value of the record's name field. The code performs the following results:

mysql> DELIMITER && 
mysql> CREATE FUNCTION name_from_employee (emp_id INT) 
  -> RETURNS VARCHAR ( 
  -> 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 stored function has been created successfully. This function is used in the same way as the MySQL internal function.

Use of 14.1.3 variables

In stored procedures and functions, you can define and use variables. Users can define variables by using the DECLARE keyword. You can then assign a value to a variable. These variables are scoped to the begin ... End Program section. This section explains how to define variables and assign values to variables.

1. Defining variables

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

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

Where the DECLARE keyword is used to declare a variable, and 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 variable, and the default value clause sets the variable defaults to value. The default value is NULL when no default clause is used.

"Example 14-3" defines the variable my_sql, the data type is int, and the default value is 10. The code is as follows:

DECLARE my_sql INT DEFAULT 10;

2. Assigning values to variables

In MySQL, you can use the Set keyword to assign a value to a variable. The basic syntax for the SET statement is as follows:

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

Where the Set keyword is used to assign a value to a variable, the Var_name parameter is the name of the variable, and the expr argument is an assignment expression. A SET statement can assign values to multiple variables at the same time, and the assignment statements for each variable are separated by commas.

Under Example 14-4, assign a value of variable my_sql to 30. The code is as follows:

SET my_sql = 30;

In MySQL, you can also use Select ... The INTO statement assigns a value to a variable. The basic syntax is as follows:

SELECT col_name[,...] Into var_name[,...]
From table_name wehre condition

Where the Col_name parameter represents the field name of the query, the Var_name parameter is the name of the variable, the table_name parameter refers to the name of the table, and the condition parameter refers to the query condition.

Under example 14-5, query for a record with ID 2 from the Employee table, assigning 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 handlers

Defining conditions and handlers is a predefined problem that you may encounter during the execution of your program. And you can define solutions to these problems in your handlers. This approach can anticipate possible problems in advance and suggest solutions. This will enhance the program's ability to handle problems and prevent programs from stopping abnormally. In MySQL, you define conditions and handlers by declare keywords. This section explains in detail how to define conditions and handlers.

1. Define criteria

You can use the DECLARE keyword to define a condition in MySQL. The basic syntax is as follows:

DECLARE condition_name condition for condition_value 
condition_value: 
   SQLSTATE [value] sqlstate_value | mysql_ Error_code 

Where the Condition_name parameter represents the name of the condition, the Condition_value parameter represents the type of the condition, and both the Sqlstate_value parameter and the Mysql_error_code parameter can represent MySQL errors. For example, in the error 1146 (42S02), the Sqlstate_value value is the 42s02,mysql_error_code value is 1146.

"Error 1146 (42S02)" is defined under "Example 14-6" with the name Can_not_find. There are two different ways to define the code as follows:

Method One: Use Sqlstate_value 
DECLARE can_not_find CONDITION for SQLSTATE ' 42S02 '; 
Method Two: Use Mysql_error_code 
DECLARE can_not_find CONDITION for 1146; 

2. Defining handlers

In MySQL, you can use the DECLARE keyword to define the handler. 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 

Where the Handler_type parameter indicates how the error was handled, and the parameter has 3 values. These 3 values are continue, exit, and undo respectively. Continue indicates that an error has been encountered without processing, continues to execute downward, exit indicates that an error has been encountered, exits immediately, and undo indicates that the operation before the recall of the error has been encountered, and is not supported in MySQL for the time being.

Note: Normally, errors encountered during execution should immediately stop executing the following statement and recall the previous action. However, the undo operation is not currently supported in MySQL. Therefore, it is best to perform an exit operation when an error is encountered. If you can predict the type of error beforehand and handle it appropriately, you can perform a continue operation.

The Condition_value parameter indicates the error type, which has 6 values. Sqlstate_value and Mysql_error_code are the same meaning in the definition of a condition. Condition_name is the declare-defined condition name. SQLWarning represents all sqlstate_value values that begin with 01. The not found represents all sqlstate_value values that begin with 02. SqlException represents all sqlstate_value values that are not captured by sqlwarning or not found. Sp_statement represents the execution statements of some stored procedures or functions.

Under Example 14-7, there are several ways to define handlers. The code is as follows:

Method One: Capture Sqlstate_value 
DECLARE CONTINUE HANDLER for SQLSTATE ' 42s02 '
SET @info = ' CAN don't find '; 
Method Two: Capture Mysql_error_code 
DECLARE CONTINUE HANDLER for 1146 SET @info = ' CAN don't find '; 
Method Three: First define the condition, then call 
DECLARE can_not_find CONDITION for 1146; 
DECLARE CONTINUE HANDLER for can_not_find SET 
@info = ' can don't find '; 
Method Four: Use sqlwarning 
DECLARE EXIT HANDLER for sqlwarning SET @info = ' ERROR '; 
Method Five: Use the not FOUND 
DECLARE EXIT HANDLER for the not FOUND SET @info = ' CAN don't find '; 
Method VI: Use SQLEXCEPTION 
DECLARE EXIT HANDLER for SQLEXCEPTION SET @info = ' ERROR '; 

The above code is 6 ways to define handlers. The first method is to capture the Sqlstate_value value. If you encounter a Sqlstate_value value of 42S02, perform a continue operation and output "CAN not find" information. The second method is to capture the Mysql_error_code value. If you encounter a Mysql_error_code value of 1146, perform a continue operation and output "CAN not find" information. The third method is to define the condition before invoking the condition. This defines the can_not_find condition first, and performs the continue operation when 1146 errors are encountered. The fourth option is to use sqlwarning. SQLWarning captures all sqlstate_value values that begin with 01, then performs an exit operation and prints "ERROR" information. The fifth approach is to use not FOUND. The not found captures all sqlstate_value values that begin with 02, then performs an exit operation and outputs "CAN not find" information. The sixth option is to use SqlException. SqlException captures all sqlstate_value values that are not captured by sqlwarning or not found, and then performs the exit operation and outputs "ERROR" information.

Thank you for reading this article, I hope to help you, thank you for your support for this site!