reference resources: https://www.runoob.com/w3cnote/mysql-stored-procedure.html
https://www.cnblogs.com/geaozhang/p/6797357.html
http://blog.sina.com.cn/s/blog_86fe5b440100wdyt.html
1. Mysql stored procedure
MySQL version 5.0 supports stored procedures.
Stored Procedure is a database object that stores complex programs in a database for external programs to call.
Stored procedure is a set of SQL statements to complete specific functions. It is compiled, created and saved in the database. Users can call and execute it by specifying the name of the stored procedure and giving parameters (when necessary).
The idea of stored procedure is very simple, that is, code encapsulation and reuse at the level of database SQL language.
1.1. Assign the execution result of query statement to variable in Mysql stored procedure
1 Mode 1: 2 DECLARE cnt INT DEFAULT 0; 3 SELECT COUNT(*) INTO cnt FROM test; 4 SELECT cnt; 5 6 Mode 2: 7 DECLARE cnt INT DEFAULT 0; 8 SET @cnt = (SELECT count(*) FROM test); 9 SELECT @cnt; 10 11 Mode 3: 12 DECLARE cnt INT DEFAULT 0; 13 SELECT count(*) INTO @cnt FROM test; 14 SELECT @cnt; 15 16 In the case of multiple columns, it seems that it can only be used into Method: 17 SELECT max(status), avg(status) into @max,@avg from test_tbl; 18 SELECT @max, @avg;
2. Advantages and disadvantages of Mysql stored procedures
2.1 advantages of Mysql stored procedure
Stored procedures can be encapsulated and hide complex business logic.
Stored procedures can return values and accept parameters.
A stored procedure cannot be run using the SELECT instruction because it is a subroutine, unlike a view table, data table, or user-defined function.
Stored procedures can be used for data validation, enforcing business logic, etc.
2.2 disadvantages of Mysql stored procedure
Stored procedures are often customized to a specific database because the supported programming languages are different. When switching to the database system of other manufacturers, the original stored procedure needs to be rewritten.
The performance tuning and writing of stored procedures are limited by various database systems.
3. Creation and invocation of stored procedures
A stored procedure is a piece of code with a name used to complete a specific function.
The created stored procedure is saved in the data dictionary of the database.
1 CREATE
2 [DEFINER = { user | CURRENT_USER }]
3 PROCEDURE sp_name ([proc_parameter[,...]])
4 [characteristic ...] routine_body
5
6 proc_parameter:
7 [ IN | OUT | INOUT ] param_name type
8
9 characteristic:
10 COMMENT 'string'
11 | LANGUAGE SQL
12 | [NOT] DETERMINISTIC
13 | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
14 | SQL SECURITY { DEFINER | INVOKER }
15
16 routine_body:
17 Valid SQL routine statement
18
19 [begin_label:] BEGIN
20 [statement_list]
21 ......
22 END [end_label]3.1 key syntax in MYSQL stored procedure
1 # Change the closing symbol of the statement from semicolon; Temporarily change to two $$(can be customized). Among them, use the command delimiter; Returns the closing symbol of the statement to a semicolon. 2 # 1. Statement terminator can be customized: 3 DELIMITER $$ 4 or 5 DELIMITER // 6 7 # 2. Declare stored procedure: 8 CREATE PROCEDURE demo_in_parameter(IN p_in int) 9 10 # 3. Stored procedure start and end symbols: 11 BEGIN .... END 12 13 # 4. Variable assignment: 14 SET @p_in=1 15 16 # 5. Variable definition: 17 DECLARE l_int int unsigned default 0; 18 19 # 6. Create mysql stored procedures and functions: 20 create procedure Stored procedure name(parameter) 21 22 # 7. Stored procedure body: 23 create function Store function name(parameter)
3.2 cases of stored procedures
Resolution: by default, the stored procedure is associated with the default database. If you want to specify that the stored procedure is created under a specific database, prefix the procedure name with the database name. When defining a procedure, use the DELIMITER command to change the closing symbol of the statement from a semicolon; It is temporarily changed to two, so that the semicolon used in the process body is directly passed to the server without being interpreted by the client (such as mysql).
1 mysql> delimiter $$ # Change the closing symbol of the statement from semicolon; Temporarily changed to two $$(can be customized). 2 mysql> CREATE PROCEDURE delete_matches(IN p_playerno INTEGER) 3 -> BEGIN 4 -> DELETE FROM MATCHES 5 -> WHERE playerno = p_playerno; 6 -> END$$ 7 Query OK, 0 rows affected (0.01 sec) 8 9 mysql> delimiter; # Returns the closing symbol of the statement to a semicolon.
3.3. Call stored procedure:
Resolution: the variable p that needs to pass parameters is set in the stored procedure_ PlayerNo, when calling the stored procedure, assign 57 to P by passing reference_ PlayerNo, and then perform SQL operations in the stored procedure.
1 call sp_name[(Transmission parameter)];
The case of calling a stored procedure is as follows:
1 mysql> select * from MATCHES; 2 +---------+--------+----------+-----+------+ 3 | MATCHNO | TEAMNO | PLAYERNO | WON | LOST | 4 +---------+--------+----------+-----+------+ 5 | 1 | 1 | 6 | 3 | 1 | 6 | 7 | 1 | 57 | 3 | 0 | 7 | 8 | 1 | 8 | 0 | 3 | 8 | 9 | 2 | 27 | 3 | 2 | 9 | 11 | 2 | 112 | 2 | 3 | 10 +---------+--------+----------+-----+------+ 11 5 rows in set (0.00 sec) 12 13 mysql> call delete_matches(57); 14 Query OK, 1 row affected (0.03 sec) 15 16 mysql> select * from MATCHES; 17 +---------+--------+----------+-----+------+ 18 | MATCHNO | TEAMNO | PLAYERNO | WON | LOST | 19 +---------+--------+----------+-----+------+ 20 | 1 | 1 | 6 | 3 | 1 | 21 | 8 | 1 | 8 | 0 | 3 | 22 | 9 | 2 | 27 | 3 | 2 | 23 | 11 | 2 | 112 | 2 | 3 | 24 +---------+--------+----------+-----+------+ 25 4 rows in set (0.00 sec)
3.4 stored procedure body
The stored procedure body contains the statements that must be executed during procedure call, such as dml, ddl statements, if then else and while do statements, declare statements that declare variables, etc.
Procedure body format: start with begin and end with end (can be nested).
1 BEGIN 2 BEGIN 3 BEGIN 4 statements; 5 END 6 END 7 END
Note: each nested block and each statement in it must end with a semicolon. For the begin end block (also known as compound statement) indicating the end of the process body, a semicolon is not required.
3.5 labeling statement blocks:
Labels serve two purposes:
1) enhance the readability of the code.
2) labels are required in some statements (such as leave and iterate statements).
1 [begin_label:] BEGIN 2 [statement_list] 3 END [end_label]
For example:
1 label1: BEGIN 2 label2: BEGIN 3 label3: BEGIN 4 statements; 5 END label3 ; 6 END label2; 7 END label1
4. Parameters of stored procedure
The parameters of MySQL stored procedure are used IN the definition of stored procedure. There are three parameter types: IN, OUT and INOUT. The forms are as follows:
1 CREATE PROCEDURE Stored procedure name([[IN |OUT |INOUT ] Parameter name data class...])
If the procedure has no parameters, parentheses must also be written after the procedure name. For example:
1 CREATE PROCEDURE sp_name ([proc_parameter[,...]]) ......
Ensure that the name of the parameter is not equal to the name of the column, otherwise the parameter name will be treated as the column name in the procedure body.
4.1 IN input parameters
IN input parameter: indicates that the caller passes IN a value to the procedure (the value can be literal or variable). The default is IN input parameter. If it is not filled IN, it is the default IN input parameter.
1 # As can be seen below, p_in is modified in the stored procedure, but does not affect @p_ The value of in because the former is a local variable and the latter is a global variable. 2 mysql> delimiter $$ 3 mysql> create procedure in_param(in p_in int) 4 -> begin 5 -> select p_in; 6 -> set p_in=2; 7 -> select P_in; 8 -> end$$ 9 mysql> delimiter ; 10 11 mysql> set @p_in=1; 12 13 mysql> call in_param(@p_in); 14 +------+ 15 | p_in | 16 +------+ 17 | 1 | 18 +------+ 19 20 +------+ 21 | P_in | 22 +------+ 23 | 2 | 24 +------+ 25 26 mysql> select @p_in; 27 +-------+ 28 | @p_in | 29 +-------+ 30 | 1 | 31 +-------+
4.2 OUT output parameters
OUT output parameter: indicates that the procedure sends OUT a value to the caller (multiple values can be returned) (the outgoing value can only be a variable).
1 mysql> delimiter // 2 mysql> create procedure out_param(out p_out int) 3 -> begin 4 -> select p_out; 5 -> set p_out=2; 6 -> select p_out; 7 -> end 8 -> // 9 mysql> delimiter ; 10 11 mysql> set @p_out=1; 12 13 mysql> call out_param(@p_out); 14 +-------+ 15 | p_out | 16 +-------+ 17 | NULL | 18 +-------+ 19 #Because out outputs parameters to the caller and does not receive input parameters, P in the stored procedure_ Out is null 20 +-------+ 21 | p_out | 22 +-------+ 23 | 2 | 24 +-------+ 25 26 mysql> select @p_out; 27 +--------+ 28 | @p_out | 29 +--------+ 30 | 2 | 31 +--------+ 32 #Out called_ Param stored procedure, output parameters, changed P_ The value of the out variable
4.3 INOUT input and output parameters
INOUT input / output parameter: it indicates that the caller passes in a value to the procedure and the procedure passes out a value to the caller (the value can only be a variable).
1 mysql> delimiter $$ 2 mysql> create procedure inout_param(inout p_inout int) 3 -> begin 4 -> select p_inout; 5 -> set p_inout=2; 6 -> select p_inout; 7 -> end 8 -> $$ 9 mysql> delimiter ; 10 11 mysql> set @p_inout=1; 12 13 mysql> call inout_param(@p_inout); 14 +---------+ 15 | p_inout | 16 +---------+ 17 | 1 | 18 +---------+ 19 20 +---------+ 21 | p_inout | 22 +---------+ 23 | 2 | 24 +---------+ 25 26 mysql> select @p_inout; 27 +----------+ 28 | @p_inout | 29 +----------+ 30 | 2 | 31 +----------+ 32 #Inout called_ Param stored procedure, which accepts input parameters and also outputs parameters and changes variables
5. Stored procedure declaration variable
1) user variable name generally starts with @.
2) misuse of user variables will make the program difficult to understand and manage.
5.1 variable definition
The local variable declaration must be placed at the beginning of the stored procedure body:
1 DECLARE variable_name [,variable_name...] datatype [DEFAULT value];
Where datatype is the data type of MySQL, such as int, float, date,varchar(length), for example:
1 DECLARE l_int int unsigned default 4000000; 2 DECLARE l_numeric number(8,2) DEFAULT 9.95; 3 DECLARE l_date date DEFAULT '1999-12-31'; 4 DECLARE l_datetime datetime DEFAULT '1999-12-31 23:59:59'; 5 DECLARE l_varchar varchar(255) DEFAULT 'This will not be padded';
5.2 variable assignment
1 SET Variable name = Expression value [,variable_name = expression ...]
5.3 user variables
Using user variables in MySQL client:
1 mysql > SELECT 'Hello World' into @x; 2 mysql > SELECT @x; 3 +-------------+ 4 | @x | 5 +-------------+ 6 | Hello World | 7 +-------------+ 8 mysql > SET @y='Goodbye Cruel World'; 9 mysql > SELECT @y; 10 +---------------------+ 11 | @y | 12 +---------------------+ 13 | Goodbye Cruel World | 14 +---------------------+ 15 16 mysql > SET @z=1+2+3; 17 mysql > SELECT @z; 18 +------+ 19 | @z | 20 +------+ 21 | 6 | 22 +------+
Use user variables in stored procedures:
1 mysql > CREATE PROCEDURE GreetWorld( ) SELECT CONCAT(@greeting,' World'); 2 mysql > SET @greeting='Hello'; 3 mysql > CALL GreetWorld( ); 4 +----------------------------+ 5 | CONCAT(@greeting,' World') | 6 +----------------------------+ 7 | Hello World | 8 +----------------------------+
Pass globally scoped user variables between stored procedures:
1 mysql> CREATE PROCEDURE p1() SET @last_procedure='p1';
2 mysql> CREATE PROCEDURE p2() SELECT CONCAT('Last procedure was ',@last_procedure);
3 mysql> CALL p1( );
4 mysql> CALL p2( );
5 +-----------------------------------------------+
6 | CONCAT('Last procedure was ',@last_proc |
7 +-----------------------------------------------+
8 | Last procedure was p1 |
9 +-----------------------------------------------+6. Comments for stored procedures
MySQL stored procedures can use two styles of annotations.
Two horizontal bars -- this style is generally used for single line notes.
c Style: generally used for multi line comments.
1 mysql > DELIMITER // 2 mysql > CREATE PROCEDURE proc1 -- name Stored procedure name 3 -> (IN parameter1 INTEGER) 4 -> BEGIN 5 -> DECLARE variable1 CHAR(10); 6 -> IF parameter1 = 17 THEN 7 -> SET variable1 = 'birds'; -- use SET Knowledge points are very important for variable assignment. 8 -> ELSE 9 -> SET variable1 = 'beasts'; 10 -> END IF; 11 -> INSERT INTO table1 VALUES (variable1); 12 -> END 13 -> // 14 mysql > DELIMITER ;
7. Query, modification, deletion and call control of stored procedures
7.1. Call of MySQL stored procedure
Use call, your procedure name and a bracket. Add parameters in the bracket as needed. The parameters include input parameters, output parameters and input / output parameters. Please refer to the above example for specific calling methods.
7.2 query of MySQL stored procedure
We know the tables under a database. We generally use show tables; View. So we want to check whether the stored procedure under a database can also be used? The answer is that we can view the stored procedures under a database, but it's another way.
We can query with the following statement:
1 select name from mysql.proc where db='Database name'; 2 3 perhaps 4 select routine_name from information_schema.routines where routine_schema='Database name'; 5 6 perhaps 7 show procedure status where db='Database name';
If we want to know the details of a stored procedure, what should we do? Can I also use the describe table name to view it like the operation table?
The answer is: we can view the details of stored procedures, but we need to use another method:
1 SHOW CREATE PROCEDURE database.Stored procedure name;
You can view the details of the current stored procedure.
7.3 calling MySQL stored procedure
1 ALTER PROCEDURE
Changing the pre specified stored procedure established with CREATE PROCEDURE will not affect the related stored procedure or storage function.
7.4 calling MySQL stored procedure
Deleting a stored procedure is as simple as deleting a table:
1 DROP PROCEDURE
Delete one or more stored procedures from the MySQL table.
8. Control statement of MySQL stored procedure
8.1 variable scope
Internal variables have higher priority within their scope when executed to end. Variable, the internal variable disappears. At this time, it is outside its scope, and the variable is no longer visible. It should be that the declared variable can no longer be found outside the stored procedure, but you can save its value through the out parameter or assign its value to the session variable.
1 mysql > DELIMITER // 2 mysql > CREATE PROCEDURE proc3() 3 -> begin 4 -> declare x1 varchar(5) default 'outer'; 5 -> begin 6 -> declare x1 varchar(5) default 'inner'; 7 -> select x1; 8 -> end; 9 -> select x1; 10 -> end; 11 -> // 12 mysql > DELIMITER ;
8.2 conditional statement, if then else statement
1 mysql > DELIMITER // 2 mysql > CREATE PROCEDURE proc2(IN parameter int) 3 -> begin 4 -> declare var int; 5 -> set var=parameter+1; 6 -> if var=0 then 7 -> insert into t values(17); 8 -> end if; 9 -> if parameter=0 then 10 -> update t set s1=s1+1; 11 -> else 12 -> update t set s1=s1+2; 13 -> end if; 14 -> end; 15 -> // 16 mysql > DELIMITER ;
8.3. Conditional statement and case statement:
The syntax used is as follows:
1 case 2 when var=0 then 3 insert into t values(30); 4 when var>0 then 5 when var<0 then 6 else 7 end case
The use cases are as follows:
1 mysql > DELIMITER // 2 mysql > CREATE PROCEDURE proc3 (in parameter int) 3 -> begin 4 -> declare var int; 5 -> set var=parameter+1; 6 -> case var 7 -> when 0 then 8 -> insert into t values(17); 9 -> when 1 then 10 -> insert into t values(18); 11 -> else 12 -> insert into t values(19); 13 -> end case; 14 -> end; 15 -> // 16 mysql > DELIMITER ;
8.4. Loop statement while ···· end while
The syntax used is as follows:
1 while condition do 2 --Circulatory body 3 end while
The use cases are as follows:
1 mysql > DELIMITER // 2 mysql > CREATE PROCEDURE proc4() 3 -> begin 4 -> declare var int; 5 -> set var=0; 6 -> while var<6 do 7 -> insert into t values(var); 8 -> set var=var+1; 9 -> end while; 10 -> end; 11 -> // 12 mysql > DELIMITER ;
8.5. Circular statement repeat ···· end repeat
It checks the results after the operation is executed, while while it checks before execution.
The syntax used is as follows:
1 repeat 2 --Circulatory body 3 until Cycle condition 4 end repeat;
The use cases are as follows:
1 mysql > DELIMITER // 2 mysql > CREATE PROCEDURE proc5 () 3 -> begin 4 -> declare v int; 5 -> set v=0; 6 -> repeat 7 -> insert into t values(v); 8 -> set v=v+1; 9 -> until v>=5 10 -> end repeat; 11 -> end; 12 -> // 13 mysql > DELIMITER ;
8.6. loop statement ····· endloop
The loop loop loop does not need an initial condition, which is similar to the while loop. At the same time, like the repeat loop, it does not need an end condition. The meaning of the leave statement is to leave the loop.
1 mysql > DELIMITER // 2 mysql > CREATE PROCEDURE proc6 () 3 -> begin 4 -> declare v int; 5 -> set v=0; 6 -> LOOP_LABLE:loop 7 -> insert into t values(v); 8 -> set v=v+1; 9 -> if v >=5 then 10 -> leave LOOP_LABLE; 11 -> end if; 12 -> end loop; 13 -> end; 14 -> // 15 mysql > DELIMITER ;
8.7 LABLES label of circular statement:
Labels can be used before begin repeat while or loop statements. Statement labels can only be used before legal statements. You can jump out of the loop and make the running instruction reach the last step of the compound statement.
ITERATE starts the compound statement again by referring to the label of the compound statement:
1 mysql > DELIMITER // 2 mysql > CREATE PROCEDURE proc10 () 3 -> begin 4 -> declare v int; 5 -> set v=0; 6 -> LOOP_LABLE:loop 7 -> if v=3 then 8 -> set v=v+1; 9 -> ITERATE LOOP_LABLE; 10 -> end if; 11 -> insert into t values(v); 12 -> set v=v+1; 13 -> if v>=5 then 14 -> leave LOOP_LABLE; 15 -> end if; 16 -> end loop; 17 -> end; 18 -> // 19 mysql > DELIMITER ;