<> preface

In project development , Such a scenario is often encountered , When modified A When a piece of data in the table , Need associated modification B surface ,C Table and even more , Why is this ?

In a real business scenario , Often, the data of a table is related to a variety of businesses , for instance , Users complete an order on the page , For the server , There are many businesses related to this order , For example, generate an issue record , Deduct inventory , User's account balance deduction , Increase in account balance of merchants , Financial settlement, etc …

All these different salesperson operations , On the server side , The final embodiment is the interaction with the database , as one can imagine , If the associated business is more complex , Need to communicate with the database IO The more times ;

Is there any way , In the face of such scenario business , Improve the performance of the server , Reduce database IO And ? This is the stored procedure to be shared in this article .

<> What is a stored procedure

A stored procedure is a segment that has been compiled in advance and stored in the database SQL A collection of statements ;

<> Why use stored procedures

Calling stored procedures can simplify a lot of work for application developers , Reduce data transmission between database and application server , It is good for improving the efficiency of data processing . Stored procedures are simple in thought , Database
SQL Code encapsulation and reuse at language level .

Stored procedure characteristics ：

* encapsulation , multiplexing , Can put a certain business SQL Encapsulated in stored procedures , You can call it directly when you need it ;
* Can receive parameters , You can also return data , In a stored procedure , Parameters can be passed , You can also receive return values ;
* Reduce network interaction , Improve efficiency , If more than one operation is involved SQL, Every execution is a network transmission
transport . If these sql Operations are encapsulated in stored procedures , It only needs one network interaction ;
<> Basic syntax of stored procedure

1, Create stored procedure syntax
CREATE PROCEDURE Stored procedure name ([ parameter list ]) BEGIN -- SQL sentence END ;
A simple case
2, Call stored procedure
CALL name ([ parameter ]);
Call the above stored procedure
call p1();

3, View stored procedures
SHOW CREATE PROCEDURE Stored procedure name ; -- Query the definition of a stored procedure

4, Delete stored procedure
DROP PROCEDURE [ IF EXISTS ] Stored procedure name ;

Attention points ： The above statement to create a stored procedure is in navicat perhaps sqlyog No problem , But if you put it into the command line, an error will be reported , In command line mode , Keyword required delimiter
appoint SQL Statement Terminator

<> Variables in stored procedures

stay MySQL in , Variables can generally be divided into three types : System variables , User defined variables , local variable ;

<> one , System variables

The system variable is MySQL Provided by the server system itself , Divided into global variables （GLOBAL）, conversation
variable （SESSION）;

1） View system variables
SHOW [ SESSION | GLOBAL ] VARIABLES ; -- View all system variables SHOW [ SESSION | GLOBAL ]
VARIABLESLIKE '......'; -- Can pass LIKE Fuzzy match formula Find variable by SELECT @@[SESSION | GLOBAL] System variable name
; -- View the value of the specified variable
2） Set system variables
SET [ SESSION | GLOBAL ] System variable name = value ; SET @@[SESSION | GLOBAL] System variable name = value ;
If not specified SESSION/GLOBAL, Default is SESSION, Session variables .

Attention points ：

* mysql After service restart , The set global parameters will be invalid , In order not to fail , Can be in /etc/my.cnf Medium configuration ;
* global variable (GLOBAL): Global variables are for all sessions ;
* Session variables (SESSION): Session variables are for a single session , It doesn't work in another session window ;
For example, use the following statement to view the system's query_cache value
SHOW VARIABLES LIKE '%query_cache%';
<> two , User defined variables

User defined variables , It is a variable defined by users according to their needs , User defined variables need not be declared in advance , Use it directly when using it “@ Variable name ” Just use it . Its scope is current session Connection of .

1, Customize variables as follows
SET @var_name = expr [, @var_name = expr] ... ; SET @var_name := expr [,
@var_name := expr] ... ;
When assigning values , have access to = , You can also use := .

2, use select into Assign values to variables in the way of
SELECT @var_name := expr [, @var_name := expr] ... ; SELECT Field name INTO @var_name
FROM Table name ;

be careful : User defined variables do not need to be declared or initialized , Only the obtained value is NULL nothing more ;

<> three , local variable

1, Local variable definition

Locally effective variables defined as needed , Before visit , Need to pass DECLARE statement . It can be used as local variables and input parameters in stored procedures , The scope of a local variable is declared within it BEGIN … END block .

2, Definition method
DECLARE Variable name Variable type [DEFAULT ... ] ;
The variable type is the database field type , Optional values include ：INT,BIGINT,CHAR,VARCHAR,DATE,TIME etc .

A simple case
create procedure p2() BEGIN declare my_count int default 0; select count(*)
into my_count from account; select my_count; END;
Execute next call

<> four , Conditional judgment statement if Use of

Conditional judgment is not strange in many programming languages , The same is true in stored procedures , And many scenarios are used , Be sure to master ;

1,if Conditional sentence syntax structure
IF condition 1 THEN ..... ELSEIF condition 2 THEN -- Optional ..... ELSE -- Optional ..... END IF;
stay if Condition judgment structure ,ELSE IF There can be multiple structures , Or not . ELSE Structure can have , Or not .

A simple case , Judge whether the scores are in different ranges , Then output different values ;
create procedure p3() BEGIN declare score int default 59; declare result
VARCHAR(12); if score >= 85 then set result := ' excellent '; elseif score >= 60 then set
result := ' pass '; else set result := ' fail, '; end if; select result; END;
Execute next call

2,case - when grammar

In actual coding , And if-else Similar effects also exist case-when This way of writing , It is also a commonly used way of writing ;
CASE case_value WHEN when_value1 THEN statement_list1 [ WHEN when_value2 THEN
statement_list2] ... [ ELSE statement_list ] END CASE;
interpretative statement

* When case_value The value of is when_value1 Hour , implement statement_list1;
* When the value is when_value2 Hour , implement statement_list2, Otherwise, execute statement_list;
There is another grammatical structure as follows ：
CASE WHEN search_condition1 THEN statement_list1 [WHEN search_condition2 THEN
statement_list2] ... [ELSE statement_list] END CASE;
interpretative statement ：

* When condition search_condition1 When established , implement statement_list1
* When condition search_condition2 become Immediately , implement statement_list2, Otherwise, execute statement_list;
If used case-when The transformation of the above is as follows ：
create procedure p5() BEGIN declare score int default 77; declare result
VARCHAR(12); case when score >= 85 then set result := ' excellent '; when score >= 60
then set result := ' pass '; else set result := ' Unknown result '; end case; select result; END
Execute call

What is the problem with the above writing ? Obviously , Not flexible enough , Generally speaking , In actual business , We prefer that stored procedures receive parameters , And return the processing result , So that it can be used by subsequent business logic , This requires the input and output parameters of the stored procedure ;

<> five , Use of input and output parameters of stored procedure

Types of parameters used in stored procedures , It is mainly divided into the following three types ：IN,OUT,INOUT;

Type meaning remarks
IN These parameters are used as input , That is, the default value is passed in when calling
OUT Such parameters are output , That is, this parameter can be used as the return value
INOUT It can be used as an input parameter , It can also be used as an output parameter
1, Grammatical definition
CREATE PROCEDURE Stored procedure name ([ IN/OUT/INOUT Parameter name Parameter type ]) BEGIN -- SQL END;
Simply transform the above case , Parameters passed in through the outside , Judge whether the scores are in different ranges , So as to return different results ;
create procedure p4(in score int,out result varchar(12)) BEGIN if score >= 85
then set result := ' excellent '; elseif score >= 60 then set result := ' pass '; else set
result := ' fail, '; end if; END;
Execute next call
call p4(90,@result); select @result;

<> Case demonstration

There is an employee list below

Case requirements ：

Create stored procedure show_emp_salary(), see employees Salary of an employee in the table , Combined use IN parameter emp_name Enter employee name , And return salary ;
CREATE PROCEDURE show_emp_salary (IN emp_name VARCHAR(20),OUT emp_salary INT)
BEGIN select salary into emp_salary from employees WHERE first_name = emp_name;
Execute call

<> six , Recycling in stored procedures

Loop statements are often used in programming , It is often used for cyclic processing of batch data , stay mysql In the stored procedure of , Several common loop statements are also provided , include ：while loop ,repeat loop , and loop loop ;

1,while Loop statement

while A loop is a conditional loop control statement . When conditions are met , Then execute the SQL sentence ;
WHILE condition DO SQL logic ... END WHILE;
First determine the condition , If the condition is true, Then execute logic , otherwise , Do not execute logic

demand ： from 1 Add up to N, And find the cumulative result
create procedure p6(in n int) begin declare total int default 0; while n>0 do
set total := total + n; set n := n - 1 ; end while; select total; end ;
Execute next call

2,repeat Loop statement

repeat Is a conditional loop control statement , When satisfied until When declaring conditions , Then exit the cycle , The grammatical structure is ：
REPEAT SQL logic ... UNTIL condition END REPEAT;
Execute logic first , Then determine UNTIL Whether the conditions are met , If satisfied , Then exit . If not satisfied , Then continue the next cycle ;

Still use the above cumulative summation requirements to transform as follows ：
create procedure p7(in n int) begin declare total int default 0; repeat set
total := total + n; set n := n - 1; until n <= 0 end repeat; select total; end ;
Execute call

3,loop Loop statement

LOOP Simple loops can be achieved , If not SQL Add conditions for exiting the cycle in the logic , It can be used to realize a simple dead cycle .LOOP It can be used with the following two statements ：

* LEAVE ： Combined with recycling , Exit loop ;
* ITERATE： Must be used in a loop , The function is to skip the remaining statements of the current cycle , Go directly to the next cycle ;
The grammatical structure is as follows ：
[begin_label:] LOOP SQL logic ... END LOOP [end_label];
explain ：

* LEAVE label; – Exit the loop body of the specified tag
* ITERATE label; – Go directly to the next cycle
In the above grammar begin_label,end_label,label It refers to our customized tag name ;

use loop Syntax complete the above from 1 reach N Cumulative summation of
create procedure p8(in n int) begin declare total int default 0; sum:loop if n
<=0 then leave sum; end if; set total := total + n; set n := n - 1; end loop sum
; select total; end ;
Execute call

<> seven , Storage function

A stored function is a stored procedure with a return value , The parameters of a storage function can only be IN Type , The syntax is defined as follows ：
CREATE FUNCTION Storage function name ([ parameter list ]) RETURNS type [characteristic ...] BEGIN --
SQL sentence RETURN ...; END ;
characteristic explain ：

* DETERMINISTIC： The same input parameters always produce the same results ;
* NO SQL ： Not included SQL sentence ;
* READS SQL DATA： Contains statements that read data , But it does not contain statements that write data ;
Store function cases 1： seek 1 reach N Accumulation of
create function fun_add(n int) returns int deterministic BEGIN declare sum int
default 0; while n > 0 do set sum := sum + n; set n := n - 1; end while; return
sum; END;
Execute call

Generally speaking , What storage functions can accomplish , Using stored procedures can also be done , But the limitation of storage functions is , Function must have return result ;

<> eight , Use of cursors in stored procedures

cursor （CURSOR） Is the data type used to store the query result set , Cursors can be used in stored procedures and functions to cycle through the result set ;

cursor , Provides a flexible operation mode , Enables us to locate each record in the result set , And operate on the data in the pointed record . Cursor let SQL
This set oriented language has been oriented
Ability of program development ;

stay SQL in , A cursor is a temporary database object , Can point to data row pointers stored in database tables . Cursor here Acted as Function of pointer , We can operate on data rows by operating cursors .

The use of cursors includes the declaration of cursors ,OPEN,FETCH and CLOSE, The syntax is as follows ：

1, declare cursor
DECLARE Cursor name CURSOR FOR Query statement ;
2, Open cursor
OPEN Cursor name ;
3, Get cursor record
FETCH Cursor name INTO variable [, variable ] ;
4, Close cursor
CLOSE Cursor name ;
Case requirements , There is an employee table below , Create a stored procedure , statement IN parameter
limit_total_salary,DOUBLE type ; statement OUT parameter total_count,INT type . The function can accumulate the salary values of several employees with the highest salary , Until the total salary reaches limit_total_salary Value of parameter , Return the accumulated number of people to total_count;
CREATE PROCEDURE get_count_by_limit_total_salary(IN limit_total_salary DOUBLE,
OUT total_count INT) BEGIN DECLARE sum_salary DOUBLE DEFAULT 0; # Record accumulated total wages
DECLARE cursor_salary DOUBLE DEFAULT 0; # Record a salary value DECLARE emp_count INT DEFAULT
0; # Record the number of cycles # Define cursor DECLARE emp_cursor CURSOR FOR SELECT salary FROM employees
ORDER BY salary DESC; # Open cursor OPEN emp_cursor; REPEAT # Use cursor （ Get data from cursor ） FETCH
emp_cursorINTO cursor_salary; SET sum_salary = sum_salary + cursor_salary; SET
emp_count= emp_count + 1; UNTIL sum_salary >= limit_total_salary END REPEAT; SET
total_count= emp_count; # Close cursor CLOSE emp_cursor; END ;
Execute next call

Through the above case , We have experienced the strength of a downstream target , in fact , In many times , We want to output the obtained records to an intermediate record table through the cursor , For subsequent business processing , It is very suitable to use the cursor , Look at the following requirements ：

Based on the passed in parameters salary, To query the user table employees in , All wages are greater than or equal to 15000 Employees of ID, as well as first_name, And the employee's ID and first_name Insert into a new table ;
create procedure p_emp(in in_salary int) begin declare u_empid int(12); declare
u_fnamevarchar(100); declare u_cursor cursor for select employee_id,first_name
from employees where salary >= in_salary; -- Claim condition handler ：
When SQL The status code thrown by statement execution is 02000 Hour , Cursor will be closed u_cursor, And exit declare exit handler for SQLSTATE
'02000' close u_cursor; drop table if exists tb_em_pro; create table if not
exists tb_em_pro( id int primary key auto_increment, employee_id int(12),
first_namevarchar(20) ); -- Open cursor open u_cursor; while true do fetch u_cursor
into u_empid,u_fname; insert into tb_em_pro values (null, u_empid, u_fname); end
while; -- Close cursor close u_cursor; end;
Execute next call

After execution , You can see that the data processed by the cursor is written to an intermediate table

©2019-2020 Toolsou All rights reserved,
Solve in servlet The Chinese output in is a question mark C String function and character function in language MySQL management 35 A small coup optimization Java performance —— Concise article Seven sorting algorithms （java code ） use Ansible Batch deployment SSH Password free login to remote host according to excel generate create Build table SQL sentence Spring Source code series ( sixteen )Spring merge BeanDefinition Principle of Virtual machine installation Linux course What are the common exception classes ?