The MySQL storage process

The stored procedure can be easily understood as a collection of one or more SQL statements.

The operations of the storage process include creating a stored procedure, viewing the storage process, updating the storage process, and deleting the storage process.

1. Create a stored procedure

1.1 Stored Procedure syntax structure

Creating a stored procedure in MySQL is implemented through the SQL statement CREATE procedure, which has the following syntax:

CREATE PROCEDURE procedure_name ([procedure_paramter[,... ]])    [characteristic ... ] Routine_body

The procedure_name parameter represents the name of the stored procedure to be created, Procedure_paramter represents the parameters of the stored procedure,characteristic represents the characteristics of the stored procedure, Routine_ The body represents the SQL statement code for the stored procedure,
You can use the BEGIN ... End to flag the start and end of the SQL statement.

The syntax for each parameter in Procedure_name is as follows:

[in| Out| INOUT] procedure_name type

Each parameter consists of three parts, the input/output type, the parameter name, and the type of the argument. There are three types of input/output types, which are in for input parameters, out for output parameters, inout for either input or output, param_name parameter is the parameter name of the stored procedure, and type parameter specifies the parameter type of the stored procedure. The type can be any data type of the MySQL database.

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

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

[NOT] Deterministic: Indicates whether the execution result of the stored procedure is deterministic. Deterministic indicates that the result is deterministic. The same input will get the same output each time the stored procedure is executed. Not deterministic indicates that the result is indeterminate, and the same input may get different output. By default, the result is non-deterministic.

{CONTAINS SQL | NO SQL | READS SQL DATA | Modifies SQL DATA}: Indicates the limitations of the use of SQL statements by subroutines. CONTAINS SQL indicates that a subroutine contains SQL statements, but does not contain statements that read or write data; No SQL indicates that the subroutine does not contain SQL statements; READS SQL data represents the statements in the subroutine that contain read data; 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 means that only the definition can execute itself; 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 specifies contains SQL by default, which means that the SQL statement is used in the stored procedure. However, if the SQL statement is not used in the stored procedure, it is best to set to no SQL. Also, it is best to make a simple comment on the stored procedure in the comment section of the stored procedure so that it is more convenient to read the stored procedure's code later.

Examples are as follows:

DELIMITER $$ CREATE PROCEDURE proce_employee_sal () COMMENT ' Check the wages of all employees ' BEGIN    SELECT Sal      from T_employee; END $ $DELIMITER;

1.2 Expressions about the stored procedure

expressions, like expressions in other high-level languages, are composed of variables, operators, and process controls.

1.2.1 Action variables

A. Defining variables

Use the DECLARE keyword to define a variable. The basic syntax for defining variables is as follows:

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

The DECLARE keyword is used to declare a variable, the Var_name argument is the name of the variable, and you can define multiple variables at the same time; the type parameter is used to specify the types of the variables, the default value clause sets the variable defaults to value, The default value is null.

Examples are as follows:

DECLARE  My_sql  INT  DEFAULTTen

B. Assignment variables

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]

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 parameter is an assignment expression. A SET statement can assign values to multiple variables at the same time, separating the assignment statements of each variable with commas.

Examples are as follows:

SET  =  -

You 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  

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.

It is important to note that when you assign a query result to a variable, the result of the query statement is only a single row.

Examples are as follows:

SELECT  d_id to    my_sql from    employee  wehre  id=2 

1.2.2 Operating conditions

Defining conditions and handlers is a pre-defined problem that you might encounter during program execution. You can also define ways to resolve these problems in your handlers. This way you can anticipate potential problems in advance and propose solutions. This will enhance the ability of the program to handle problems, and prevent the program from stopping abnormally. In MySQL, the conditions and handlers are defined by the DECLARE keyword.

A. Defining conditions

Use the DECLARE keyword to define the condition. The basic syntax is as follows:

DECLARE  Condition_name  condition for    condition_value  condition_value:  [value ]|

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

Examples are as follows:

Define "error 1146 (42S02)", which is named Can_not_find. It can be defined in two different ways, with the following code:

// 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

B. Defining handlers

Use the Declare keyword to define handlers. The basic syntax is as follows:

DECLAREHandler_type Handler forCondition_value[,...]sp_statement Handler_type:CONTINUE | EXIT |UNDO condition_value:sqlstate[VALUE]Sqlstate_value|Condition_name|sqlwarning|  notFOUND|SQLEXCEPTION|Mysql_error_code

Where the Handler_type parameter indicates how the error is handled, and the parameter has 3 values. These 3 values are continue, exit, and Undo, respectively. Continue indicates that an error has not been processed, continues to execute downward, exits immediately after an error has been encountered, and Undo indicates that the operation was not supported until the error was encountered and is temporarily unsupported in MySQL.

Note: Typically, 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 if an error is encountered. If the error type can be predicted beforehand and processed accordingly, the continue operation can be performed.

The Condition_value parameter indicates the type of error, which has 6 values. Sqlstate_value and Mysql_error_code are the same meaning as in conditional definitions. Condition_name is the condition name defined by declare. SQLWarning represents all sqlstate_value values that begin with 01. 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 some execution statements for stored procedures or functions.

Examples are as follows:

Here are a few ways to define the handler. The code is as follows:

    //method One: Capture Sqlstate_valueDECLARE CONTINUEHANDLER forSQLSTATE'42S02'    SET @info='CAN not FIND'; //method Two: Capture Mysql_error_codeDECLARE CONTINUEHANDLER for 1146 SET @info='CAN not FIND'; //method Three: Define the condition first, and then call theDECLARECan_not_find CONDITION for  1146 ; DECLARE CONTINUEHANDLER forCan_not_findSET     @info='CAN not FIND'; //method Four: Use SQLWarningDECLARE EXITHANDLER forSQLWarningSET @info='ERROR'; //method Five: Use not FOUNDDECLARE EXITHANDLER for  notFOUNDSET @info='CAN not FIND'; //method Six: Use SqlExceptionDECLARE EXITHANDLER forSQLEXCEPTIONSET @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 a mysql_error_code value of 1146 is encountered, the continue operation is performed and the "CAN not FIND" message is output. The third method is to define the condition before calling the condition. The can_not_find condition is defined first, and the continue operation is performed with 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 the "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 the sqlwarning or not found, and then performs the exit operation and outputs the "ERROR" information.

1.3 Using Cursors

A cursor can be seen as a data type that can be used to traverse a result set, equivalent to a pointer, or subscript in an array. A method that processes a result set can navigate to a row of the result set by a cursor, search for a line or branch from the location of the current result set, or make data modifications to the current row in the result set.

1.3.1 Declaring cursors

DECLARE CURSOR  for select_statement;

1.3.2 Opening Cursors

OPEN cursor_name

1.3.3 Using Cursors

FETCH  into [, Var_name] ...

In the preceding statement, the execution result of the SELECT statement in the parameter cursor cursor_name is saved to the variable parameter var_name. The variable parameter var_name must be defined before the cursor is used. Use a cursor like an array traversal in a high-level language, when the cursor is first used, at which point the cursor points to the first record in the result set.

1.3.4 Closing Cursors

CLOSE cursor_name

The complete example is shown below:

DROP PROCEDURE IF EXISTSEmplayee_count;delimiter $ #创建存储过程CREATE PROCEDUREEmplayee_count (out NUMINTEGER)BEGIN#声明变量DECLAREEmplayee_salINTEGER;DECLAREFlagINTEGER; #声明游标DECLARECursor_emplayeeCURSOR  for SELECTSal fromT_employee;DECLARE CONTINUEHANDLER for  notFOUNDSETFlag= 1; #设置结束标志SETFlag=0;SETNum=0; ' #打开游标OPENCursor_emplayee; #遍历游标指向的结果集FETCHCursor_emplayee intoemplayee_sal; whileFlag<>1 DoIFEmplayee_sal>999  ThenSETNum=Num+1;END IF;FETCHCursor_emplayee intoemplayee_sal;END  while; #关闭游标CLOSECursor_emplayee;END$DELIMITER;

2. View the storage process

3. Update the storage process

4. Deleting a stored procedure

The MySQL storage process