MYSQL存储过程 注释详解

 // 把editUser类比成一个存储过程 public void editUser(User user,String username){ String a = "nihao"; user.setUsername(username); } main(){ User user = new User(); editUser(user,"张三"); user.getUseranme();   //java基础 } 

 delimiter $$ --声明结束符 

 #### 3.0 语法结构 ```sql -- 存储过程结构 CREATE [DEFINER = user] PROCEDURE sp_name ([proc_parameter[,...]]) [characteristic ...] routine_body -- 1. proc_parameter参数部分，可以如下书写： [ IN | OUT | INOUT ] param_name type -- type类型可以是MySQL支持的所有类型 -- 2. routine_body(程序体)部分，可以书写合法的SQL语句 BEGIN ... END 

 -- 声明结束符。因为MySQL默认使用‘;'作为结束符，而在存储过程中，会使用‘;'作为一段语句的结束，导致‘;'使用冲突 delimiter $$ CREATE PROCEDURE hello_procedure () BEGIN SELECT 'hello procedure'; END $$ call hello_procedure(); 

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

 -- 赋值 delimiter $$ create procedure sp_var02() begin set @nickname = 'ZS'; -- set nickname := 'SF'; end$$ call sp_var02() $$ select @nickname$$  --可以看到结果 

 show session variables; -- 查看会话变量 select @@session.unique_checks; -- 查看某会话变量 set @@session.unique_checks = 0; --修改会话变量 

 show global variables like '%char%'; -- 查看全局变量character_set_client的值 select @@global.character_set_client; 

 -- IN类型演示 delimiter $$ create procedure sp_param01(in age int) begin set @user_age = age; end$$ call sp_param01(10) $$ select @user_age$$ -- OUT类型，只负责输出！ -- 需求：输出传入的地址字符串对应的部门编号。 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; --此处强调，要么表起别名，要么入参名不与字段名一致 end$$ delimiter ; --测试 set @dept_no = 100; call sp_param02('DALLAS',@dept_no); select @dept_no; -- INOUT类型 delimiter $$ create procedure sp_param03(inout name varchar(49)) begin set name = concat('hello' ,name); end$$ delimiter ; set @user_name = '小明'; call sp_param03(@user_name); select @user_name; 

 -- 语法 IF search_condition THEN statement_list [ELSEIF search_condition THEN statement_list] ... [ELSE statement_list] END IF 

 -- 前置知识点：timestampdiff(unit,exp1,exp2) 取差值exp2-exp1差值，单位是unit select 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 = '元老'; elseif timestampdiff(year,ages,now())>38 then set result = '老员工'; ELSE SET result = '新手'; end if; select result; end $$ delimiter; call sp_param05('1970-02-26 10:00:25'); -- 注意：MYSQL时间戳必须从1970年开始。 

 -- 语法一（类比java的switch）： CASE case_value WHEN when_value THEN statement_list [WHEN when_value THEN statement_list] ... [ELSE statement_list] END CASE -- 语法二： CASE WHEN search_condition THEN statement_list [WHEN search_condition THEN statement_list] ... [ELSE statement_list] END CASE 

 -- 需求：入职年限年龄<=38是新手 >38并 <=40老员工 >40元老 delimiter $$ create procedure sp_hire_case() begin declare result varchar(32); declare message varchar(64); case when timestampdiff(year,'2001-01-01',now()) > 40 then set result = '元老'; set message = '老爷爷'; when timestampdiff(year,'2001-01-01',now()) > 38 then set result = '老员工'; set message = '油腻中年人'; else set result = '新手'; set message = '萌新'; end case; select result; end$$ delimiter ; 

 -- 语法 [begin_label:] LOOP statement_list END LOOP [end_label] 

 --需求：循环打印1到10 -- leave控制循环的退出 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控制循环 delimiter $$ create procedure sp_flow_loop02() begin declare c_index int default 1; declare result_str  varchar(256) default '1'; 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 loop select result_str; end$$ pre>

 [begin_label:] REPEAT statement_list UNTIL search_condition -- 直到…为止，才退出循环 END REPEAT [end_label] -- 需求：循环打印1到10 delimiter $$ create procedure sp_flow_repeat() begin declare c_index int default 1; -- 收集结果字符串 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$$ 

 类比java的while(){} [begin_label:] WHILE search_condition DO statement_list END WHILE [end_label] -- 需求：循环打印1到10 delimiter $$ create procedure sp_flow_while() begin declare c_index int default 1; -- 收集结果字符串 declare result_str varchar(256) default '1'; while c_index <10 do set c_index=c_index + 1; result_str=concat(result_str,',',c_index); end while; select result_str; end$$ < pre>

 类比java的breake -- 退出 LEAVE can be used within BEGIN ... END or loop constructs (LOOP, REPEAT, WHILE). LEAVE label 

 类比java的continue -- 继续循环 ITERATE can appear only within LOOP, REPEAT, and WHILE statements ITERATE label 

 类比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; -- 定义用户变量并赋值（用户变量不需要提前声明、仅当前会话有效）>鄙人没有理解这一步什么意义 set @end_falg = 'exit_flag'; -- 关闭游标 close emp_cursor; -- 结束存储过程 end$$ -- 恢复;结束符 delimiter; -- 使用该存储过程并传参 call sp_create_table02('RESEARCH'); 

 DECLARE handler_action HANDLER FOR condition_value [, condition_value] ... statement handler_action: { CONTINUE  -- 继续执行 | EXIT      -- 退出执行 | UNDO      -- 什么都不做 } CONTINUE: Execution of the current program continues. -- 继续执行当前程序 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. -- 停止执行在handler被声明的BEGIN... END的组合程序，即使是在程序内部发生该条件。 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'. -- 即SQL STATE以01开头的集合代称 NOT FOUND: Shorthand for the class of SQLSTATE values that begin with '02'. -- 即SQL STATE以O2开头的集合代称 SQLEXCEPTION: Shorthand for the class of SQLSTATE values that do not begin with '00', '01', or '02'. -- 即SQL STATE不以00、01、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'; 

 delimiter // -- 定义结束符 create procedure high_sal(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; 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句柄（条件处理） declare continue handler for NOT FOUND set lp_flag = false; open emp_cursor; -- 打开游标 emp_loop:loop -- 开启循环 fetch emp_cursor into e_no,e_name,e_sal; -- 变量赋值 if lp_flag then -- 流程控制 if e_name = 'king' then iterate emp_loop; -- 继续循环 else update emp e set e.sal = e.sal + 100 where e.empno = e_no; -- 更新数据 end if; else leave emp_loop; -- 离开循环 end if; -- 结束流程 end loop emp_loop; -- 结束循环 set @end_falg = 'exit_flag'; -- 声明用户变量 close emp_cursor; -- 变比游标 end // -- 结束存储过程 delimiter; -- 恢复结束符 call high_sal('ACCOUNTING'); 

 -- 知识点 预处理 prepare语句from后使用局部变量会报错 -- https://dev.mysql.com/doc/refman/5.6/en/sql-prepared-statements.html -- 看不懂英文文档的看这个：https://www.cnblogs.com/geaozhang/p/9891338.html；话说我也该去做一点英文技术文档的阅读提升了 PREPARE stmt_name FROM preparable_stmt EXECUTE stmt_name [USING @var_name [, @var_name] ...] {DEALLOCATE | DROP} PREPARE stmt_name -- 贴一段预处理的案例，需求是：利用字符串定义预处理 SQL (直角三角形斜边hypotenuse计算) PREPARE stmt1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse'; -- POW(x,y)函数,用于计算x的y次方（http://c.biancheng.net/mysql/pow_power.html）；SQRT函数用于求平方根（https://blog.csdn.net/weixin_39554172/article/details/113124290） SET @a = 3; SET @b = 4;  -- 用户变量使用即声明 EXECUTE stmt1 USING @a, @b; -- 结果为5 DEALLOCATE PREPARE stmt1; -- 知识点 时间的处理 -- EXTRACT(unit FROM date)截取时间的指定位置值 -- DATE_ADD(date,INTERVAL expr unit)  日期运算 -- LAST_DAY(date)  获取日期的最后一天的日期 -- YEAR(date) 返回日期中的年 -- MONTH(date) 返回日期的月 -- DAYOFMONTH(date) 返回日 -- 注：根据https://stackoverflow.com/questions/35838321/day-vs-dayofmonth-in-mysql，其实DAY(date)效果是一样的 -- 思路：循环构建表名 comp_2020_05_01 到 comp_2020_05_31；并执行create语句。 -- 分析：1. 循环构筑表，仅表名不同，考虑使用存储过程执行循环处理、使用预处理提高效率。 2. 首先需要一个变量存储执行SQL；然后年份需要一个变量、月份需要一个变量、日期需要一个变量、拼接出来的表名需要一个变量；除此之外，还需要一个数字用来累增；至此，我们得到了至少需要6个变量。为了补全日期0，增加两个月份、日的变量用来补充0形成01、02・・・。 3. 考虑到预处的格式（from后不能有局部变量）,写7个局部变量，1个用户变量 delimiter //  --声明结束符 create procedure sp_create_table() begin --  定义一堆局部变量 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); -- 处理每天的表名 declare table_name_str char(10); -- 统计序列 declare t_index int default 1; -- declare create_table_sql varchar(200); -- 获取下个月的年份 set next_year = year(date_add(now(),INTERVAL 1 month)); -- 获取下个月是几月 set next_month = month(date_add(now(),INTERVAL 1 month)); -- 下个月最后一天是几号 set next_month_maxday = dayofmonth(LAST_DAY(date_add(now(),INTERVAL 1 month))); -- 把1-9月补上0:  01,02・・・・，09 if next_month <10 then set next_month_str=concat('0',next_month); else concat('',next_month); end if; while t_index < do -- 同上，对天数补0 if (t_index 10) next_month_maxday_str=concat('0',t_index); concat('',t_index); 2020_05_01 table_name_str=concat(next_year,'_',next_month_str,'_',next_month_maxday_str); 拼接create sql语句（用户变量） @create_table_sql=concat( 'create table comp_', table_name_str, '(`grade` int(11) null,`losal` null,`hisal` null) collate=\'utf8_general_ci\' engine=InnoDB'); from后面不能使用局部变量！这就是我们为什么使用用户变量的原因 prepare create_table_stmt from @create_table_sql; execute create_table_stmt; deallocate + 1; while;>

 characteristic: COMMENT 'string' | LANGUAGE SQL | [NOT] DETERMINISTIC | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY { DEFINER | INVOKER } 

 -- 如有死循环处理，可以通过下面的命令查看并结束 show processlist; kill id; 

 https://dev.mysql.com/doc/refman/5.6/en/control-flow-functions.html select case when timestampdiff(year,e.hiredate,now()) <= 38 then '新手' when timestampdiff(year,e.hiredate,now()) <= 40 then '老员工' else '元老' end hir_loc, e.* from emp e; 

 create temporary table 表名( 　　字段名 类型 [约束], 　　name varchar(20) )Engine=InnoDB default charset utf8; -- 需求：按照部门名称查询员工，通过select查看员工的编号、姓名、薪资。（注意，此处仅仅演示游标用法） delimiter $$ create procedure sp_create_table02(in dept_name varchar(32)) begin declare emp_no int; declare emp_name varchar(32); declare emp_sal decimal(7,2); declare exit_flag int default 0; declare emp_cursor cursor for select e.empno,e.ename,e.sal from emp e inner join dept d on e.deptno = d.deptno where d.dname = dept_name; declare continue handler for not found set exit_flag = 1; -- 创建临时表收集数据 CREATE temporary TABLE `temp_table_emp` ( `empno` INT(11) NOT NULL COMMENT '员工编号', `ename` VARCHAR(32) NULL COMMENT '员工姓名' COLLATE 'utf8_general_ci', `sal` DECIMAL(7,2) NOT NULL DEFAULT '0.00' COMMENT '薪资', PRIMARY KEY (`empno`) USING BTREE ) COLLATE='utf8_general_ci' ENGINE=InnoDB; open emp_cursor; c_loop:loop fetch emp_cursor into emp_no,emp_name,emp_sal; if exit_flag != 1 then insert into temp_table_emp values(emp_no,emp_name,emp_sal); else leave c_loop; end if; end loop c_loop; select * from temp_table_emp; select @sex_res; -- 仅仅是看一下会不会执行到 close emp_cursor; end$$ call sp_create_table02('RESEARCH'); 

 CREATE TABLE `dept` ( `deptno` INT(11) NOT NULL COMMENT '部门编号', `dname` VARCHAR(32) NULL COMMENT '部门名称' COLLATE 'utf8_general_ci', `loc` VARCHAR(64) NULL COMMENT '部门地址' COLLATE 'utf8_general_ci', PRIMARY KEY (`deptno`) USING BTREE ) COLLATE='utf8_general_ci' ENGINE=InnoDB ; INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK'); INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS'); INSERT INTO DEPT VALUES (30,'SALES','CHICAGO'); INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON'); CREATE TABLE `emp` ( `empno` INT(11) NOT NULL COMMENT '员工编号', `ename` VARCHAR(32) NULL COMMENT '员工姓名' COLLATE 'utf8_general_ci', `job` VARCHAR(10) NULL COMMENT '职位' COLLATE 'utf8_general_ci', `mgr` INT(11) NULL COMMENT '上级编号', `hiredate` DATE NOT NULL COMMENT '入职时间', `sal` DECIMAL(7,2) NOT NULL DEFAULT '0.00' COMMENT '薪资', `comm` DECIMAL(7,2) NULL COMMENT '年终奖金', `deptno` INT(11) NOT NULL COMMENT '部门编号', PRIMARY KEY (`empno`) USING BTREE, INDEX `FK_emp_dept` (`deptno`) USING BTREE, CONSTRAINT `FK_emp_dept` FOREIGN KEY (`deptno`) REFERENCES `procedure_demo`.`dept` (`deptno`) ON UPDATE RESTRICT ON DELETE RESTRICT ) COLLATE='utf8_general_ci' ENGINE=InnoDB ; insert into emp values (7369,'smith','clerk',7902,'1980-12-17',800,null,20); insert into emp values (7499,'allen','salesman',7698,'1981-02-20',1600,300,30); insert into emp values (7521,'ward','salesman',7698,'1981-02-22',1250,500,30); insert into emp values (7566,'jones','manager',7839,'1981-02-04',2975,null,20); insert into emp values (7654,'martin','salesman',7698,'1981-09-28',1250,1400,30); insert into emp values (7698,'blake','manager',7839,'1981-05-01',2850,null,30); insert into emp values (7782,'clark','manager',7839,'1981-06-09',2450,null,10); insert into emp values (7788,'scott','analyst',7566,'1987-07-13')-85,3000,null,20); insert into emp values (7839,'king','president',null,'1981-11-17',5000,null,10); insert into emp values (7844,'turner','salesman',7698,'1981-09-08',1500,0,30); insert into emp values (7876,'adams','clerk',7788,'1987-07-13')-51,1100,null,20); insert into emp values (7900,'james','clerk',7698,'1981-12-03',950,null,30); insert into emp values (7902,'ford','analyst',7566,'1981-12-03',3000,null,20); insert into emp values (7934,'miller','clerk',7782,'1982-01-23',1300,null,10); CREATE TABLE `salgrade` ( `grade` INT(11) NULL, `losal` INT(11) NULL, `hisal` INT(11) NULL ) COLLATE='utf8_general_ci' ENGINE=InnoDB ; insert into salgrade values (1,700,1200); insert into salgrade values (2,1201,1400); insert into salgrade values (3,1401,2000); insert into salgrade values (4,2001,3000); insert into salgrade values (5,3001,9999);