Shulou(Shulou.com)06/03 Report--

This article mainly introduces the example analysis of MYSQL stored procedures, which has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, let the editor take you to understand it.

0. Environment description:

Software version mysql8.0navicat

1. instructions

Stored procedure is an important object in the database, which can encapsulate the set of SQL statements, can be used to complete some complex business logic, and can be used for input and output parameters (similar to the writing of methods in java).

When it is created, it is pre-compiled and saved, and subsequent calls by the user do not need to be compiled again.

/ / compare editUser to a stored procedure public void editUser (User user,String username) {String a = "nihao"; user.setUsername (username);} main () {User user = new User (); editUser (user, "Zhang San"); user.getUseranme (); / / java Foundation}

You may wonder, you have to relearn to use sql to deal with business logic. Can't I use java to deal with logic (such as loop judgment, loop query, etc.)? So why use stored procedures to deal with business logic?

Advantages:

In a production environment, the business logic (or bug) can be modified by directly modifying the stored procedure without restarting the server.

The execution speed is fast, and the compiled stored procedure is faster than a single one.

Reduce network traffic.

Convenient for optimization.

Disadvantages:

Due to process programming, the maintenance cost of complex business processing is high.

Inconvenient to debug

The portability between different databases is poor. -- different database syntax is inconsistent!

two。 Prepare for

See the sql script in Resources for the database:

Delimiter $$- declaration Terminator 3. Grammar

Official reference website:

Https://dev.mysql.com/doc/refman/5.6/en/sql-statements.html

Https://dev.mysql.com/doc/refman/5.6/en/sql-compound-statements.html

# 3.0 Syntax structure ```sql-- stored procedure structure CREATE [DEFINER = user] PROCEDURE sp_name ([proc_parameter [,...]]) [characteristic...] Routine_body-- 1. Proc_parameter parameter section, you can write as follows: [IN | OUT | INOUT] param_name type-- type type can be all the types supported by MySQL-- 2. Routine_body (program body) part, you can write a legal SQL statement BEGIN. END

Simple demonstration:

-- declare Terminator. Because MySQL uses';'as the Terminator by default, while in stored procedures, it uses';'as the end of a statement, resulting in'; 'using the conflict delimiter $CREATE PROCEDURE hello_procedure () BEGIN SELECT' hello procedure';END $call hello_procedure (); 3.1 variables and assignments

Compare the declaration and use of local and member variables in java

Local variables:

User-defined, valid in begin/end block

Syntax:

Declare the variable declare var_name type [default var_value]

Example: declare nickname varchar (32)

-- set assignment delimiter $$create procedure sp_var01 () begin declare nickname varchar (32) default 'unkown'; set nickname =' ZS';-- set nickname: = 'SF'; select nickname;end$$-- into assignment delimiter $$create procedure sp_var_into () begin declare emp_name varchar (32) default' unkown'; declare emp_no int default 0; select e.empnojie.ename into emp_no,emp_name from emp e where e.empno = 7839; select emp_no,emp_name;end$$

User variable:

User defined, the current session (connection) is valid. Member variables of analogical java

Syntax:

@ var_name

There is no need to declare in advance, use is to declare

-- assign delimiter $$create procedure sp_var02 () begin set @ nickname = 'ZS';-- set nickname: =' SF';end$$call sp_var02 () $$select @ nickname$$-- you can see the result

Session variables:

Provided by the system, the current session (connection) is valid

Syntax:

@ @ session.var_name

Show session variables;-- View the session variable select @ @ session.unique_checks;-- View a session variable set @ @ session.unique_checks = 0;-- modify the session variable

Global variables:

Provided by the system, the entire mysql server is valid

Syntax:

@ @ global.var_name

For example:

-- check the record of the variable name with char in the global variable

Show global variables like'% char%';-- check the value of the global variable character_set_client, select @ @ global.character_set_client;3.2, in and out parameters

-Grammar

In | out | inout param_name type

For example:

-- IN type demo delimiter $$create procedure sp_param01 (in age int) begin set @ user_age = age;end$$call sp_param01 (10) $$select @ user_age$$-- OUT type, only responsible for output! -- demand: output the department number corresponding to the passed address string. Delimiter $$create procedure sp_param02 (in loc varchar (64), out dept_no int (11)) begin select d.deptno into dept_no from dept d where d.loc = loc;-- emphasize here that either the table has an alias or the input name does not match the field name end$$delimiter;-- Test set @ dept_no = 100 DALLAS',@dept_no sp_param02 ('DALLAS',@dept_no); select @ dept_no -- INOUT type delimiter $$create procedure sp_param03 (inout name varchar (49)) begin set name = concat ('hello', name); end$$delimiter; set @ user_name =' Xiao Ming'; call sp_param03 (@ user_name); select @ user_name;3.3 process Control-judgment

Description on the official website

Https://dev.mysql.com/doc/refman/5.6/en/flow-control-statements.html

If

-- syntax IF search_condition THEN statement_list [ELSEIF search_condition THEN statement_list]... [ELSE statement_list] END IF

For example:

-- pre-knowledge points: timestampdiff (unit,exp1,exp2) takes the difference exp2-exp1 difference, the unit is unitselect timestampdiff (year,e.hiredate,now ()) from emp e where e.empno = '7499'; delimiter $--DROP PROCEDURE IF EXISTS sp_param04;create procedure sp_param05 (in ages timestamp) begin declare result varchar (32); if timestampdiff (year,ages,now ()) > 40 then set result = 'senator' Elseif timestampdiff (year,ages,now ()) > 38 then set result = 'veteran'; ELSE SET result = 'novice'; end if; select result;end $delimiter; call sp_param05 ('1970-02-26 10 then set result 0015');-- Note: the MYSQL timestamp must start in 1970.

Case

This syntax can be used not only in stored procedures, but also in query statements!

-- Grammar one (switch for analogy java): CASE case_value WHEN when_value THEN statement_list [WHEN when_value THEN statement_list]. [ELSE statement_list] END CASE-- grammar 2: CASE WHEN search_condition THEN statement_list [WHEN search_condition THEN statement_list]. [ELSE statement_list] END CASE

For example:

-- demand: entry age 38 and 40 yuan old delimiter $$create procedure sp_hire_case () begin declare result varchar (32); declare message varchar (64); case when timestampdiff (year,'2001-01-01 century now ()) > 40 then set result = 'old man'; set message = 'grandfather'; when timestampdiff (year,'2001-01-01-01 minute now ()) > 38 then set result = 'veteran'; set message = 'greasy middle-aged' Else set result = 'novice'; set message = 'Mengxin'; end case; select result;end$$delimiter; 3.4process control-cycle

Loop

-- Syntax [begin_label:] LOOP statement_listEND LOOP [end_label]

For example:

To be clear, loop is an endless loop. You need to exit the loop manually. We can use leave to exit.

You can think of leave as the counterpart of the break; in our java, and there is iterate-- the continue of the analogy java.

-- demand: loop printing 1 to 10-leave control loop exit delimiter $$create procedure sp_flow_loop () begin declare c_index int default 1; declare result_str varchar (256) default'1; cnt:loop if c_index > = 10 then leave cnt; end if; set c_index = c_index + 1; set result_str = concat (result_str,',',c_index); end loop cnt; select result_str End$$-- iterate + leave control cycle delimiter $$create procedure sp_flow_loop02 () begin declare c_index int default 1; declare result_str varchar default '1cycles; cnt:loop set c_index = c_index + 1; set result_str = concat (result_str,',',c_index); if c_index

< 10 then iterate cnt; end if; -- 下面这句话能否执行到？什么时候执行到？ 当c_index < 10为false时执行 leave cnt; end loop cnt; select result_str; end$$ repeat

[begin_label:] REPEAT statement_listUNTIL search_condition-until... So far, exit the loop END REPEAT [end_label]-- demand: loop print 1 to 10delimiter $$create procedure sp_flow_repeat () begin declare c_index int default 1;-- collect the result string declare result_str varchar (256) default'1'; count_lab:repeat set c_index = c_index + 1; set result_str = concat (result_str,',',c_index); until c_index > = 10 end repeat count_lab; select result_str;end$$

While

While () {} [begin_label:] WHILE search_condition DO statement_listEND WHILE [end_label] of analogy java-- demand: loop print 1 to 10delimiter $$create procedure sp_flow_while () begin declare c_index int default 1;-- collect the result string declare result_str varchar (256) default'1; while c_index

< 10 do set c_index = c_index + 1; set result_str = concat(result_str,',',c_index); end while; select result_str;end$$3.5 流程控制-退出、继续循环 leave 类比java的breake-- 退出 LEAVE can be used within BEGIN ... END or loop constructs (LOOP, REPEAT, WHILE).LEAVE label iterate 类比java的continue-- 继续循环 ITERATE can appear only within LOOP, REPEAT, and WHILE statementsITERATE label3.6 游标 用游标得到某一个结果集，逐行处理数据。 类比jdbc的ResultSet-- 声明语法DECLARE cursor_name CURSOR FOR select_statement-- 打开语法OPEN cursor_name-- 取值语法FETCH cursor_name INTO var_name [, var_name] ...-- 关闭语法CLOSE cursor_name-- 需求：按照部门名称查询员工，通过select查看员工的编号、姓名、薪资。（注意，此处仅仅演示游标用法）-- 更改结束符为$$delimiter $$-- 创造存储过程(带一个入参)create procedure sp_create_table02(in dept_name varchar(32))begin-- 必须先声明变量 declare e_no int; declare e_name varchar(32); declare e_sal decimal(7,2); declare lp_flag boolean default true;-- 其次声明游标:游标值为query（dept_name)得到的table(e.empno,e.ename,e.sal) declare emp_cursor cursor for select e.empno,e.ename,e.sal from emp e,dept d where e.deptno = d.deptno and d.dname = dept_name; -- 然后声明 handler 句柄：-- 关于句柄:https://blog.csdn.net/qq_43427482/article/details/109898934-- 看完还没理解，再看：https://www.cnblogs.com/phpper/p/7587556.html-- 这里涉及了SQL STATE:https://blog.csdn.net/u014653854/article/details/78986780-- 声明handler句柄:当每条SQL传递ERROR STATE为没有值的报错时，设定变量lp_flag为非真，同时继续执行SQL（如不声明，当某条循环报错时，整个SQL将直接停止循环） declare continue handler for NOT FOUND set lp_flag = false;-- 打开游标 open emp_cursor;-- 开启LOOP循环：emp_loop emp_loop:loop-- 将游标值传递给三个变量 fetch emp_cursor into e_no,e_name,e_sal;-- 如果变量lp_flag为真，则获取这三个参数的值；否则打断emp_loop循环 if lp_flag then select e_no,e_name,e_sal; else leave emp_loop; end if;-- 结束循环 end loop emp_loop;-- 定义用户变量并赋值（用户变量不需要提前声明、仅当前会话有效）>

I do not understand the meaning of this step set @ end_falg = 'exit_flag';-- closes the cursor close emp_cursor;-- to end the stored procedure end$$-- recovery; the Terminator delimiter;-- uses the stored procedure and passes the parameter call sp_create_table02 (' RESEARCH')

Pay special attention to:

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

3.7 handler in stored procedures

The handler handle is used to define conditional processing

DECLARE handler_action HANDLER FOR condition_value [, condition_value]... Statementhandler_action: {CONTINUE-- continue execution | EXIT-- exit execution | UNDO-- do nothing} CONTINUE: Execution of the current program continues. -- continue with the current program EXIT: Execution terminates for the BEGIN. END compound statement in which the handler is declared. This is true even if the condition occurs in an inner block. -- stop executing the BEGIN... declared in handler The combinator of END, even if this condition occurs within the program. Condition_value: {mysql_error_code | SQLSTATE [VALUE] sqlstate_value | condition_name | SQLWARNING | NOT FOUND | SQLEXCEPTION} SQLWARNING: Shorthand for the class of SQLSTATE values that begin with'01. -- that is, the collection where SQLSTATE begins with 01 is called NOT FOUND: Shorthand for the class of SQLSTATE values that begin with'02. The collection where SQLSTATE begins with O2 is called SQLEXCEPTION: Shorthand for the class of SQLSTATE values that do not begin with'00,'01, or'02. DECLARE exit HANDLER FOR SQLSTATE '42S01' set @ res_table =' EXISTS'; DECLARE continue HANDLER FOR 1050 set @ res_table = 'EXISTS'; DECLARE continue HANDLER FOR not found set @ res_table =' EXISTS';4. Practice

Note that business processes of stored procedures can also be implemented in java code. Our following requirements are to practice stored procedures.

4.1 use stored procedures to update data

Raise the salary by 100 for the staff of a department (to be designated); if you are the president of the company, there will be no raise.

Delimiter /-- define Terminator create procedure high_sal (in dept_name varchar (32))-- create stored procedure begin-- start stored procedure declare e_no int;-- declare variable declare e_name varchar (32); declare e_sal decimal (7Magne2); declare lp_flag boolean default true Declare emp_cursor cursor for-declares cursor select e.empnoree.enameree.sal from emp eGravity dept d where e.deptno = d.deptno and d.dname = dept_name;-declares handler handle (conditional processing) declare continue handler for NOT FOUND set lp_flag = false; open emp_cursor;-- opens cursor emp_loop:loop-- opens the loop fetch emp_cursor into -- variable assignment if lp_flag then-- process control if e_name = 'king' then iterate emp_loop;-- continue the loop else update emp e set e.sal = e.sal + 100 where e.empno = esignos;-- Update data end if; else leave emp_loop;-- leave the loop end if;-- end the process end loop emp_loop -- end loop set @ end_falg = 'exit_flag';-- declare user variable close emp_cursor;-- variable cursor end / /-- end stored procedure delimiter;-- restore Terminator call high_sal (' ACCOUNTING')

4.2 create tables in a loop

Create corresponding tables comp_2020_04_01, comp_2020_04_02,... for each day of next month.

(simulation) requirements description:

We need to use a table to record a lot of data, such as recording the search and purchase behavior of so-and-so users (note that it is assumed to be saved in a database here). When there are more records every day, if all the data are recorded in one table, it is too large to have a sub-table. our requirement is that one table a day and store today's statistics requires that these tables be produced ahead of time-- create tables for the next month at the end of each month!

-- knowledge point pre-processing prepare statement from after the use of local variables will report an error-- https://dev.mysql.com/doc/refman/5.6/en/sql-prepared-statements.html-- can not understand the English document to see this: https://www.cnblogs.com/geaozhang/p/9891338.html In other words, I should also do a little reading of English technical documents to improve PREPARE stmt_name FROM preparable_stmtEXECUTE stmt_name [USING @ var_name [, @ var_name].] {DEALLOCATE | DROP} PREPARE stmt_name-- post a preprocessing case, the requirement is: preprocessing SQL (right triangle beveled hypotenuse calculation) PREPARE stmt1 FROM 'SELECT SQRT (POW, 2) + POW (?, 2) AS hypotenuse' -- the POW (x _ ray) function, which is used to calculate the y power (http://c.biancheng.net/mysql/pow_power.html) of x The SQRT function is used to find the square root (https://blog.csdn.net/weixin_39554172/article/details/113124290) SET @ a = 3 set @ b = 4;-- the user variable is used to declare EXECUTE stmt1 USING @ a, @ b;-- the result is 5DEALLOCATE PREPARE stmt1 -- processing of knowledge point time-- specified location value of EXTRACT (unit FROM date) intercept time-- DATE_ADD (date) INTERVAL expr unit) date operation-- LAST_DAY (date) gets the date of the last day-- YEAR (date) returns the year of the date-- MONTH (date) returns the month of the date-- DAYOFMONTH (date) returns the day-- Note: according to https://stackoverflow.com/questions/35838321/day-vs-dayofmonth-in-mysql In fact, the effect of DAY (date) is the same-- idea: build table names comp_2020_05_01 to comp_2020_05_31 in a loop And execute the create statement. -Analysis: 1. Loop construction table, only the table name is different, consider using stored procedures to perform loop processing, the use of preprocessing to improve efficiency. two。 First, we need a variable to store and execute SQL;, then the year needs a variable, the month needs a variable, the date needs a variable, and the spliced table name needs a variable; in addition, we need a number to accumulate; so far, we need at least six variables. In order to complete date 0, the variables of two months and days are added to supplement 0 to form 01 and 02. 3. Considering the preprocessed format (there can be no local variables after from), write seven local variables and one user variable delimiter / /-- declaration Terminator create procedure sp_create_table () begin-- defines a bunch of local variables declare next_year int; declare next_month int; declare next_month_maxday int; declare next_month_str char (2); declare next_month_maxday_str char (2) -- processing daily table name declare table_name_str char (10);-- Statistical sequence declare t_index int default 1;-- declare create_table_sql varchar (200);-- getting the year of next month set next_year = year (date_add (now (), INTERVAL 1 month));-- getting what month next month is set next_month = month (date_add (now (), INTERVAL 1 month)) -- what date is the last day of next month set next_month_maxday = dayofmonth (date_add (now (), INTERVAL 1 month));-- add 0: 01,02,09 if next_month < 10 then set next_month_str = concat from January to September; else set next_month_str = concat ('', next_month); end if; while t_index

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.