1, Stored procedure

1.1 what is a stored procedure

Stored Procedure is a set of SQL statements to complete specific functions in a large database system. It is stored in the database and is permanently valid after one compilation. Users execute it by specifying the name of the Stored Procedure and giving parameters (if the Stored Procedure has parameters). Stored Procedure is an important object in database. When the amount of data is very large, the efficiency can be improved by using stored procedures

1.2 database stored procedure

After we know what the stored procedure is, we need to know the three types of database storage procedures in the database, as follows:

• Stored procedure: stored procedure is the most common stored program. Stored procedure is a program unit that can accept input and output parameters and can be executed on request.
• Stored function: a stored function is very similar to a stored procedure, but its execution result will return a value. Most importantly, storage functions can be used as standard SQL statements, allowing programmers to effectively expand the capabilities of SQL language.
• Trigger: a trigger is a stored program used to respond to activation or trigger database behavior events. Usually, triggers are called as the response of database operation language, and triggers can be used as data verification and automatic reverse formatting.

Note: other databases provide other data storage programs, including packages and classes. At present, MySQL does not provide this structure.

1.3 why use stored programs

Although we don't use many stored programs in the current development, we don't necessarily deny it. In fact, storage programs will bring us many advantages in using and managing databases:

• Using stored programs is safer.
• Stored program provides an abstract mechanism of data access, which can greatly improve the maintainability of your code in the evolution of the underlying data structure.
• Storing programs can reduce network congestion because it belongs to the internal data of the database server, which is much faster than transmitting data on the Internet.
• The stored program can implement shared access routines for a variety of peripheral applications using different architectures, whether these architectures are based outside or inside the database server.
• Data centric logic can be placed in the stored program independently, which can bring programmers a higher and more unique database programming experience.
• In some cases, using stored programs can improve the portability of applications. (in some other cases, portability will be poor!)

Here I will roughly explain the advantages of using storage programs mentioned above:

We need to know that in the Java language, we use the combination of database and Java code, and persistent storage needs to be completed by introducing JDBC. I think of JDBC. Can we still think of SQL injection? Although using PreparedStatement to solve the problem of SQL injection, is it really absolutely safe? No, it's not absolutely safe.

At this time, analyze the connection operation flow between the database and Java code. In BS structure, the browser usually accesses the server, and then the server sends SQL statements to the database, compiles and runs the SQL statements in the database, and finally returns the results to the browser through the server processing. During this operation, every time the browser sends a request for database operation to the server, it will call the corresponding SQL statement to compile and execute, which is a waste of performance. If the performance decreases, it means that the operation efficiency of the database is low.

Another possibility is that the SQL statement sent and transmitted in this process is the SQL statement that operates on the real database table. If it is intercepted in the process of sending and transmitting, some criminals will infer the database table structure in our database according to the intercepted SQL statement, which is a great security risk.

For the improvement of maintainability, a scenario is simulated here. Usually, the database is managed by the DBA in the company. If the DBA who has managed the database for many years resigns, the database will be managed by the next DBA. Here comes the problem. So many data and SQL statements in the database are obviously not friendly to the next manager. Even if a DBA who has managed for many years does not operate and view the database for a long time, it will forget something. Therefore, we need to introduce storage programs to uniformly write and compile SQL statements, which provides convenience for maintenance. (in fact, I don't think this example is vivid and reasonable, but for your understanding, please forgive me!)

The advantage evolution process of many stored programs is described. Its core is that one or more compiled SQL statements need to be placed in the stored program on the database side to solve the above problems and facilitate developers to call directly.

2, Steps for using stored procedures

2.1 development idea of stored procedure

Stored procedure is an important object of database. It can encapsulate SQL statement set, can be used to complete some complex business logic, and can enter parameters (pass parameters) and output parameters (return parameters). The encapsulation method here is very similar to that in Java.

Moreover, it will be pre compiled and saved during creation, and subsequent calls by developers do not need to be compiled again.

2.2 advantages and disadvantages of stored procedures

The advantages and disadvantages of using stored procedures are actually mentioned in the advantages in 1.3. Here I briefly list the advantages and disadvantages of stored procedures.

• advantage:
• In the production environment, business logic or bug s can be modified by directly modifying stored procedures without restarting the server.
• The execution speed is fast. After compilation, the stored procedure will execute much faster than a single compilation.
• Reduce network transmission traffic.
• It is convenient for developers or DBA s to use and maintain.
• With the same database syntax, portability is improved.
• Disadvantages:
• Procedural programming, complex business processing, high maintenance cost.
• Inconvenient debugging.
• Because the syntax of different databases is inconsistent, the portability between different databases is poor.

2.3 official documents of MySQL stored procedures

Friends with good English or ability can refer to the official documents. If you don't refer to the official documents, it doesn't matter. I will also describe in detail the knowledge points of MySQL stored procedures below.

1https://dev.mysql.com/doc/refman/5.6/en/preface.html

2.3 usage syntax of stored procedures

1create PROCEDURE Process name( in|out|inout Parameter name data type , ...)
2begin
3    sql sentence;
4end;
5call Process name(Parameter value);

In is the keyword that defines the incoming parameter. out is the keyword that defines the parameter. inout is a parameter that defines an access parameter. If nothing is defined in parentheses, it means that the stored procedure is a parameterless function. There will be a detailed case study later.

Note: the default terminator of SQL statement is;, Therefore, when using the above stored procedures, a syntax error of 1064 will be reported. We can use the DELIMITER keyword to temporarily declare that the ending character of the modified SQL statement is / /, as follows:

1-- The temporary definition terminator is"//"
2DELIMITER //
3create PROCEDURE Process name( in|out Parameter name data type , ...)
4begin
5    sql sentence;
6end//
7-- Redefine the terminator back to";"
8DELIMITER ;

For example: use stored procedure to query employee's salary (no parameter)

Note: if necessary, we can also use the delimiter keyword to delete; The terminator declaration is used back. In the following cases, I did not declare the terminator back to the original;, Please pay attention here~

Why do I provide drop here?

This is because if we need to modify the contents of the stored procedure when using it, we need to delete the existing stored procedure first and then creat e it again.

 1#The declaration terminator is//
 2delimiter //
 3
 4#Create stored procedure (function)
 5create procedure se()
 6begin
 7    select salary from employee;
 8end //
 9
10#Call function
11call se() //
12
13#Delete existing stored procedure -- se() function
14drop procedure if exists se //

3, Variables and assignments of stored procedures

3.1 local variables

Syntax for declaring local variables: declare var_name type [default var_value];

Assignment syntax:

Note: the definition of local variables is valid in the begin/end block.

Assign values to parameters using set

 1#set assignment
 2
 3#The declaration terminator is//
 4delimiter //
 5
 6#Create stored procedure
 7create procedure val_set()
 8begin
 9    #Declare a Val whose default value is unknown_ Name local variable
10    declare val_name varchar(32) default 'unknown';
11    #Assign values to local variables
12    set val_name = 'Centi';
13    #Query local variables
14    select val_name;
15end //
16
17#Call function
18call val_set() //
19

Receive parameters using into

 1delimiter //
 2create procedure val_into()
 3begin
 4    #Define two variables to store name and age
 5    declare val_name varchar(32) default 'unknown';
 6    declare val_age int;
 7    #Query the name and age with id 1 in the table and put them in the two defined variables
 8    select name,age into val_name,val_age from employee where id = 1;
 9    #Query two variables
10    select val_name,val_age;
11end //
12
13call val_into() //
14

3.2 user variables

User defined user variable. Current session (connection) is valid. Similar to member variables in Java.

• Syntax: @ val_name
• Note: this user variable does not need to be declared in advance. It is declared when it is used.

 1delimiter //
 2create procedure val_user()
 3begin
 4    #Assign values to user variables
 5    set @val_name = 'Lacy';
 6end //
 7
 8#Call function
 9call val_user() //
10
11#Query the user variable
12select @val_name //

3.3 session variables

Session variables are provided by the system and are only valid in the current session (connection).

Syntax: @ @ session val_ name

1#View all session variables
2show session variables;
3#View the specified session variables
4select @@session.val_name;
5#Modify the specified session variable
6set @@session.val_name = 0;

Here I get all the session variables, about 500 records of session variables. After we deeply study MySQL, we understand the role of each session variable value, and we can modify the session variable value according to the needs and scenarios.

1delimiter //
2create procedure val_session()
3begin
4    #View session variables
5    show session variables;
6end //
7
8call val_session() //
9

image-20200610112512964

3.4 global variables

Global variables are provided by the system and are valid in the whole MySQL server.

Syntax: @ @ global val_ name

1#View the records with char in the variable name in the global variable
2show global variables like '%char%' //
3#View global variable character_ set_ Value of client
4select @@global.character_set_client //

3.5 input and output parameters

The syntax of input, input and output parameters has been mentioned at the beginning of the article, but there is no demonstration. Here I will demonstrate the use of input, input and output parameters.

Syntax: in|out|inout parameter name data type

In defines the parameter; Out defines the input parameter; inout defines out and in parameters.

Out parameter in

When using the parameter in, we need to pass in parameters. Here we can change the parameters. Simply put, in is only responsible for passing parameters into stored procedures, similar to formal parameters in Java.

 1delimiter //
 2create procedure val_in(in val_name varchar(32))
 3begin
 4    #Use user variables to output parameters (assign parameter values to user variables)
 5    set @val_name1 = val_name;
 6end //
 7
 8#Call function
 9call val_in('DK') //
10
11#Query the user variable
12select @val_name1 //

Input parameter out

When using out, you need to pass in a parameter. This parameter can be called to get and return the value of this parameter. Simply put, out is only responsible for the return value.

 1delimiter //
 2#Create a stored procedure with input and output parameters
 3create procedure val_out(in val_id int,out val_name varchar(32))
 4begin
 5    #Pass in parameter val_id queries the employee and returns the name value (the queried name value is received and returned with the output parameter)
 6    select name into val_name from employee where id = val_id;
 7end //
 8
 9#Call the function to pass in parameters and declare a user variable
10call val_out(1, @n) //
11
12#Query user variables
13select @n //

Input and output parameters inout

inout keyword is to combine in and out into one keyword. Parameters modified by keywords can be either out or in parameters.

 1delimiter //
 2create procedure val_inout(in val_name varchar(32), inout val_age int)
 3begin
 4    #Declare an a variable
 5    declare a int;
 6    #Assign the passed in parameter to the a variable
 7    set a = val_age;
 8    #Query age through name and return val_age
 9    select age into val_age from employee where name = val_name;
10    #Splice the incoming a and - and query age result strings and find them (concat -- splice string)
11    select concat(a, '-', val_age);
12end //
13
14#Declare a user variable and give the parameter 40
15set @ages = '40' //
16#Call the function and pass in the parameter value
17call val_inout('Ziph', @ages) //
18#Execution results
19# 40-18

4, Process control in stored procedure

4.1 if condition judgment (recommended)

Extension: timestampdiff(unit, exp1, exp2) is the difference obtained by exp2 - exp1, and the unit is unit. (often used for dates)

Extension example: select timestampdiff(year, '2020-6-6', now()) from emp e where id = 1;

Explain the extension example: query the age of the employee with id 1 in the employee table, and exp2 can be the date of birth of the employee, which is calculated in years.

Syntax:

1IF Conditional judgment THEN result
2    [ELSEIF Conditional judgment THEN result] ...
3    [ELSE result]
4END IF

Example: pass in the queried id parameter to query the salary standard (s < = 6000 is the low salary standard; 6000 = 15000 is the high salary standard)

 1delimiter //
 2create procedure s_sql(in val_id int)
 3begin
 4    #Declare a local variable result to store the salary standard results
 5    declare result varchar(32);
 6    #Declare a local variable to store the salary obtained by query
 7    declare s double;
 8    #Query salary based on input parameter id
 9    select salary into s from employee where id = val_id;
10    #Use of if judgment
11    if s <= 6000 then
12        set result = 'Low wage standard';
13    elseif s <= 10000 then
14        set result = 'Medium wage standard';
15    elseif s <= 15000 then
16        set result = 'Upper middle salary standard';
17    else
18        set result = 'High wage standard';
19    end if;
20    #Query salary standard results
21    select result;
22end //
23
24#Call function, pass in parameters
25call s_sql(1);

4.2 case condition judgment

case statements can be used not only in stored procedures, but also in MySQL basic query statements. It is equivalent to the switch statement in Java.

Syntax:

 1#Grammar one
 2CASE case_value
 3    WHEN when_value THEN result
 4    [WHEN when_value THEN result] ...
 5    [ELSE result]
 6END CASE
 7
 8#Grammar 2 (recommended grammar)
 9CASE
10    WHEN Conditional judgment THEN result
11    [WHEN Conditional judgment THEN result] ...
12    [ELSE result]
13END CASE

give an example:

 1#Grammar one
 2delimiter //
 3create procedure s_case(in val_id int)
 4begin
 5    #Declare a local variable result to store the salary standard results
 6    declare result varchar(32);
 7    #Declare a local variable to store the salary obtained by query
 8    declare s double;
 9    #Query salary based on input parameter id
10    select salary into s from employee where id = val_id;
11    case s
12        when 6000 then set result = 'Low wage standard';
13        when 10000 then set result = 'Medium wage standard';
14        when 15000 then set result = 'Upper middle salary standard';
15        else set result = 'High wage standard';
16    end case;
17    select result;
18end //
19
20call s_case(1);
21
22#Grammar 2 (recommended)
23delimiter //
24create procedure s_case(in val_id int)
25begin
26    #Declare a local variable result to store the salary standard results
27    declare result varchar(32);
28    #Declare a local variable to store the salary obtained by query
29    declare s double;
30    #Query salary based on input parameter id
31    select salary into s from employee where id = val_id;
32    case
33        when s <= 6000 then set result = 'Low wage standard';
34        when s <= 10000 then set result = 'Medium wage standard';
35        when s <= 15000 then set result = 'Upper middle salary standard';
36        else set result = 'High wage standard';
37    end case;
38    select result;
39end //
40
41call s_case(1);

4.3 loop loop loop

Loop is an endless loop and needs to exit the loop manually. We can use leave to exit the loop

leave can be regarded as a break in Java; Correspondingly, there is iterate (continue loop), which can also be regarded as Java continue

Syntax:

1[alias:] LOOP
2    Circular statement
3END LOOP [alias]

Note: alias and alias control the same label.

Example 1: cycle printing 1 ~ 10 (leave controls the exit of the cycle)

Note: the loop loop loop is a dead loop. The number 1 ~ 10 we checked is i. in the dead loop, it is set to stop the loop when it is greater than or equal to 10, that is, the contents of the loop are executed 10 times successively, the results are queried 10 times, and 10 results (1 ~ 10) are generated.

 1delimiter //
 2create procedure s_loop()
 3begin
 4    #Declaration counter
 5    declare i int default 1;
 6    #Start cycle
 7    num:
 8    loop
 9        #Query the value of counter record
10        select i;
11        #Judge whether it is greater than or equal to stop counting
12        if i >= 10 then
13            leave num;
14        end if;
15        #Counter self increment 1
16        set i = i + 1;
17    #End cycle
18    end loop num;
19end //
20
21call s_loop();

Print results:

image-20200610191639524

Example 2: cycle printing 1 ~ 10 (iterate and leave control cycle)

Note: Here we use the string splicing counter result, and if the condition is iterate, it must be when I < 10!

 1delimiter //
 2create procedure s_loop1()
 3begin
 4    #Declare variable i counter
 5    declare i int default 1;
 6    #Declaration string container
 7    declare str varchar(256) default '1';
 8    #Start cycle
 9    num:
10    loop
11        #Counter self increment 1
12        set i = i + 1;
13        #String container splice counter results
14        set str = concat(str, '-', i);
15        #If the counter i is less than 10, the execution will continue
16        if i < 10 then
17            iterate num;
18        end if;
19        #If the counter i is greater than 10, the cycle is stopped
20        leave num;
21    #Stop cycle
22    end loop num;
23    #Query string container splicing results
24    select str;
25end //
26
27call s_loop1();

image-20200610193153512

4.4 repeat cycle

The repeat loop is similar to the do while loop in Java. The loop will not end until the conditions are not met.

Syntax:

1[alias:] REPEAT
2    Circular statement
3UNTIL condition
4END REPEAT [alias]

Example: cycle printing 1 ~ 10

 1delimiter //
 2create procedure s_repeat()
 3begin
 4    declare i int default 1;
 5    declare str varchar(256) default '1';
 6    #Start repeat loop
 7    num:
 8    repeat
 9        set i = i + 1;
10        set str = concat(str, '-', i);
11    #until end condition
12    #End # repeat # end num # end repeat loop
13    until i >= 10 end repeat num;
14    #Query string splicing results
15    select str;
16end //
17
18call s_repeat();

4.5 while loop

The while loop is very similar to the while loop in Java.

Syntax:

1[alias] WHILE condition DO
2    Circular statement
3END WHILE [alias]

Example: cycle printing 1 ~ 10

 1delimiter //
 2create procedure s_while()
 3begin
 4    declare i int default 1;
 5    declare str varchar(256) default '1';
 6    #Start while loop
 7    num:
 8    #Specify the while loop end condition
 9    while i < 10 do
10        set i = i + 1;
11        set str = concat(str, '+', i);
12    #The while loop ends
13    end while num;
14    #Query while circular splicing string
15    select str;
16end //
17
18call s_while();

4.6 process control statement (continue, end)

As for the continuation and end of process control, we have used it earlier. Here's another example.

leave: break with Java; be similar

1leave label;

iterate: similar to continue in Java; be similar

1iterate label;

5, Cursor and handler

5.1 cursor

Cursors can get a result set and process data row by row. The line by line operation of cursors makes cursors rarely used!

Syntax:

1DECLARE Tour label CURSOR FOR Query statement
2-- Open syntax
3OPEN Tour label
4-- Value syntax
5FETCH Tour label INTO var_name [, var_name] ...
6-- Close syntax
7CLOSE Tour label

Having understood the syntax of cursors, we began to use cursors. As follows:

Example: use cursors to query id, name, and salary.

 1delimiter //
 2create procedure f()
 3begin
 4    declare val_id int;
 5    declare val_name varchar(32);
 6    declare val_salary double;
 7
 8    #Declare cursor
 9    declare emp_flag cursor for
10    select id, name, salary from employee;
11
12    #Open
13    open emp_flag;
14
15    #Value
16    fetch emp_flag into val_id, val_name, val_salary;
17
18    #Shut down
19    close emp_flag;
20
21    select val_id, val_name, val_salary;
22end //
23
24call f();

Execution result:

image-20200610203622749

Because of the characteristics of line by line operation of cursors, we can only use cursors to query one line of records. How to improve the code to query all records? The clever little friend thought of using recycling. Yes, let's try using a loop.

 1delimiter //
 2create procedure f()
 3begin
 4    declare val_id int;
 5    declare val_name varchar(32);
 6    declare val_salary double;
 7
 8    #Declare cursor
 9    declare emp_flag cursor for
10    select id, name, salary from employee;
11
12    #Open
13    open emp_flag;
14
15    #Use circular value
16    c:loop
17        #Value
18        fetch emp_flag into val_id, val_name, val_salary;
19    end loop;
20
21    #Shut down
22    close emp_flag;
23
24    select val_id, val_name, val_salary;
25end //
26
27call f();

image-20200610204034224

After using the loop, we found a problem. Because the loop is an dead loop, we don't add the condition to end the loop. The cursor will always query the records. When we find no records, we will throw an exception 1329: the number of rows selected for processing is not obtained.

What if we try to specify the conditions for ending the loop?

At this time, you can declare a boolean tag. If it is true, the result set will be queried; if it is false, the loop will end.

 1delimiter //
 2create procedure f()
 3begin
 4    declare val_id int;
 5    declare val_name varchar(32);
 6    declare val_salary double;
 7
 8    #Declare flag flag
 9    declare flag boolean default true;
10
11    #Declare cursor
12    declare emp_flag cursor for
13    select id, name, salary from employee;
14
15    #Open
16    open emp_flag;
17
18    #Use circular value
19    c:loop
20        fetch emp_flag into val_id, val_name, val_salary;
21        #If the query result set is marked as true
22        if flag then
23            select val_id, val_name, val_salary;
24        #If it is marked as false, it proves that the query of the result set is completed and the dead cycle is stopped
25        else
26            leave c;
27        end if;
28    end loop;
29
30    #Shut down
31    close emp_flag;
32
33    select val_id, val_name, val_salary;
34end //
35
36call f();

You will find that the above code is not finished. It leaves a very serious problem. When flag = false, the loop can be ended. But when will flag be false?

Therefore, MySQL provides us with a handler handle. It can help us solve this doubt.

Handler handle syntax: declare continue handler for exception set flag = false;

The handler handle can be used to catch exceptions, that is, in this scenario, when 1329 is caught: the number of rows selected for processing is not obtained, the value of the flag tag is changed to false. In this way, the problem of ending the loop is solved by using the handler handle. Let's try!

Ultimate example: solves the problem of multi line query and ending loop.

 1delimiter //
 2create procedure f()
 3begin
 4    declare val_id int;
 5    declare val_name varchar(32);
 6    declare val_salary double;
 7
 8    #Declare flag flag
 9    declare flag boolean default true;
10
11    #Declare cursor
12    declare emp_flag cursor for
13    select id, name, salary from employee;
14
15    #Use the handler handle to solve the problem of ending the loop
16    declare continue handler for 1329 set flag = false;
17
18    #Open
19    open emp_flag;
20
21    #Use circular value
22    c:loop
23        fetch emp_flag into val_id, val_name, val_salary;
24        #If marked true, the query result set
25        if flag then
26            select val_id, val_name, val_salary;
27        #If it is marked as false, it proves that the query of the result set is completed and the dead cycle is stopped
28        else
29            leave c;
30        end if;
31    end loop;
32
33    #Shut down
34    close emp_flag;
35
36    select val_id, val_name, val_salary;
37end //
38
39call f();

Execution result:

image-20200610210925964

In the execution result, it can be seen that the query results are distributed and displayed in each query result window in the form of multiple queries.

Note: in syntax, variable declaration, cursor declaration and handler declaration must be written in order, otherwise there is an error in creating the stored procedure.

5.2 handler handle

Syntax:

1DECLARE handler operation HANDLER
2    FOR Situation list...(For example: abnormal error)
3    Operation statement

Note: exception error code, exception alias or SQLSTATE code can be written in case of exception.

handler operation:

• CONTINUE: CONTINUE
• EXIT: EXIT
• UNDO: UNDO

Exception list:

• mysql_error_code
• SQLSTATE [VALUE] sqlstate_value
• condition_name
• SQLWARNING
• NOT FOUND
• SQLEXCEPTION

Note: for various exception codes, error codes, aliases and sqlstate codes in MySQL, please refer to the official documents:

https://dev.mysql.com/doc/refman/5.6/en/server-error-reference.html

Example of writing:

1    DECLARE exit HANDLER FOR SQLSTATE '3D000' set flag = false;
2    DECLARE continue HANDLER FOR 1050 set flag = false;
3    DECLARE continue HANDLER FOR not found set flag = false;

6, Loop create table

Requirement: create a table corresponding to each day of the next month in the format of comp_2020_06_01,comp_2020_06_02,...

Description: we need to use a certain table to record a lot of data, such as the search and purchase behavior of a certain user (note that this is assumed to be saved in the database). When there are many records every day, if it is too large to record all data in one table, it needs to be divided into tables. Our requirement is to use a table every day to store the statistical data of the day, It is required to produce these tables in advance - create daily tables for the next month at the end of each month!

Precompiled: PREPARE database object name FROM parameter name

EXECUTE: EXECUTE database object name [USING @var_name [, @var_name]...]

Create or delete tables from database objects: {DEALLOCATE | DROP} PREPARE database object name

Statements about time processing:

1-- EXTRACT(unit FROM date)               The specified location value of the intercept time
2-- DATE_ADD(date,INTERVAL expr unit)     Date operation
3-- LAST_DAY(date)                          Gets the last day of the date
4-- YEAR(date)                             Returns the year in the date
5-- MONTH(date)                            Month of return date
6-- DAYOFMONTH(date)                        Return day

code:

 1-- Idea: circular construction of table names comp_2020_06_01 reach comp_2020_06_30；And execute create sentence.
 2delimiter //
 3create procedure sp_create_table()
 4begin
 5    #Declare the year, month and day of the next month for which the table name needs to be spliced
 6    declare next_year int;
 7    declare next_month int;
 8    declare next_month_day int;
 9
10    #A string that declares the month and day of the next month
11    declare next_month_str char(2);
12    declare next_month_day_str char(2);
13
14    #Declare that the table name needs to be processed every day
15    declare table_name_str char(10);
16
17    #Declare the 1 to be spliced
18    declare t_index int default 1;
19    # declare create_table_sql varchar(200);
20
21    #Gets the year of the next month
22    set next_year = year(date_add(now(),INTERVAL 1 month));
23    #What month is the next month
24    set next_month = month(date_add(now(),INTERVAL 1 month));
25    #What's the last day of next month
26    set next_month_day = dayofmonth(LAST_DAY(date_add(now(),INTERVAL 1 month)));
27
28    #If the next month is less than 10, a 0 is spliced in front of the month
29    if next_month < 10
30        then set next_month_str = concat('0',next_month);
31    else
32        #If the month is greater than 10, do nothing
33        set next_month_str = concat('',next_month);
34    end if;
35
36    #Cycle operation (cycle starts when the day of the next month is greater than or equal to 1 cycle)
37    while t_index <= next_month_day do
38
39        #If t_ If the index is less than 10, splice 0 in front
40        if (t_index < 10)
41            then set next_month_day_str = concat('0',t_index);
42        else
43            #If t_ If the index is greater than 10, do nothing
44            set next_month_day_str = concat('',t_index);
45        end if;
46
47        #Concatenated command string
48        set table_name_str = concat(next_year,'_',next_month_str,'_',next_month_day_str);
49        #Splice create sql statement
50        set @create_table_sql = concat(
51                    'create table comp_',
52                    table_name_str,
53                    '(`grade` INT(11) NULL,`losal` INT(11) NULL,`hisal` INT(11) NULL) COLLATE=\'utf8_general_ci\' ENGINE=InnoDB');
54        #Precompiled
55        #Note: local variables cannot be used after FROM!
56        prepare create_table_stmt FROM @create_table_sql;
57        #Execute
58        execute create_table_stmt;
59        #Create table
60        DEALLOCATE prepare create_table_stmt;
61
62        # t_index increases by 1
63        set t_index = t_index + 1;
64
65    end while;  
66end//
67
68#Call function
69call sp_create_table()

7, Other

7.1 characteristic

In MySQL stored procedures, if there is no character displayed, it will implicitly define the default values of a series of features to create stored procedures.

• LANGUAGE SQL

• The stored procedure language is sql by default, which means that the stored procedure is written in sql language. For the time being, only sql is supported, and other languages may be supported later

• NOT DETERMINISTIC

• Whether the deterministic input is the deterministic output. The default is NOT DETERMINISTIC. Only for the same input, the output is the same. At present, this value has not been used

• CONTAINS SQL

• It provides the internal information of subroutine use data. These eigenvalues are currently provided to the server, and the actual use of data in the process is not constrained according to these eigenvalues. There are the following options:

  • CONTAINS SQL means that the subroutine does not contain statements to read or write data
  • NO SQL means that the subroutine does not contain sql
  • Read SQL data indicates that the subroutine contains statements to read data, but does not contain statements to write data
  • Modifications SQL data indicates that the subroutine contains statements to write data.

• SQL SECURITY DEFINER

• MySQL stored procedure specifies the actual user executing the stored procedure by specifying the SQL SECURITY clause. Therefore, the secondary value is used to specify whether the stored procedure is executed with the permission of the creator or the executor. The default value is designer

  • For the current user, if the creator has the permission to execute the stored procedure and the creator has the permission to access the table, the current user can successfully execute the procedure call
  • INVOKER is executed as the caller. For the current user, if the user has the permission to execute the stored procedure, he can access the table as the current user. If the current user does not have the permission to access the table, even if he has the permission to execute the procedure, he cannot successfully execute the procedure call.

• COMMENT ''

• The annotative information of the stored procedure is written in COMMENT. Here, it can only be single line text. Multiple lines of text will be removed to carriage return, line feed, etc

7.2 dead cycle treatment

If there is dead loop processing, you can view and kill (end) through the following command

1show processlist;
2kill id;

7.3 write case in select statement

1select 
2    case
3        when Conditional judgment then result
4        when Conditional judgment then result
5        else result
6    end alias,
7    *
8from Table name;

7.4 copying tables and data

1CREATE TABLE dept SELECT * FROM procedure_demo.dept;
2CREATE TABLE emp SELECT * FROM procedure_demo.emp;
3CREATE TABLE salgrade SELECT * FROM procedure_demo.salgrade;

7.5 temporary table

 1create temporary table Table name(
 2　　Field name type [constraint],
 3　　name varchar(20) 
 4)Engine=InnoDB default charset utf8;
 5
 6-- Demand: query employees by department name, and select View the employee's number, name and salary. (note that only cursor usage is demonstrated here)
 7delimiter $$
 8create procedure sp_create_table02(in dept_name varchar(32))
 9begin
10    declare emp_no int;
11    declare emp_name varchar(32);
12    declare emp_sal decimal(7,2);
13    declare exit_flag int default 0;
14
15    declare emp_cursor cursor for
16        select e.empno,e.ename,e.sal
17        from emp e inner join dept d on e.deptno = d.deptno where d.dname = dept_name;
18
19    declare continue handler for not found set exit_flag = 1;
20
21    -- Create temporary tables to collect data
22    CREATE temporary TABLE `temp_table_emp` (
23        `empno` INT(11) NOT NULL COMMENT 'Employee number',
24        `ename` VARCHAR(32) NULL COMMENT 'Employee name' COLLATE 'utf8_general_ci',
25        `sal` DECIMAL(7,2) NOT NULL DEFAULT '0.00' COMMENT 'salary',
26        PRIMARY KEY (`empno`) USING BTREE
27    )
28    COLLATE='utf8_general_ci'
29    ENGINE=InnoDB;  
30
31    open emp_cursor;
32
33    c_loop:loop
34        fetch emp_cursor into emp_no,emp_name,emp_sal;
35
36
37        if exit_flag != 1 then
38            insert into temp_table_emp values(emp_no,emp_name,emp_sal); 
39        else
40            leave c_loop;
41        end if;
42
43    end loop c_loop;
44
45    select * from temp_table_emp;
46
47    select @sex_res; -- Just to see if it will be implemented
48    close emp_cursor;
49
50end$$
51
52call sp_create_table02('RESEARCH');

Classification: MySQL