1. 介绍
- MySQL 5.0 版本开始支持存储过程。
- 简单的说，存储过程就是一组SQL语句集，功能强大，可以实现一些比较复杂的逻辑功能，类似于JAVA语言中的方法；
- 存储过就是数据库 SQL 语言层面的代码封装与重用。
2. 特性
- 有输入输出参数，可以声明变量，有if/else, case,while等控制语句，通过编写存储过程，可以实现复杂的逻辑功能；
- 函数的普遍特性：模块化，封装，代码复用；
- 速度快，只有首次执行需经过编译和优化步骤，后续被调用可以直接执行，省去以上步骤；
- 存储过程一旦创建，就跟数据库一样存在于数据库服务器
3. 调用和删除存储过程
调用：CALL 存储过程名称(参数)；例如：CALL my_procedure1()，CALL my_procedure1(10，...)
删除：drop procedure 存储过程名称。删除存储过程不需要括号和参数。
4. 创建存储过程
①简单创建
格式：delimiter就是改变语句的分号‘;’为其他符号，在存储过程结束后将其他符号改回‘;’
原因：MySQL执行过程中，遇到分号‘;’就执行了，但是这时候存储过程还没创建完成，所以需要使用delimiter
1 delimiter 自定义结束符号 2 create procedure 储存名([ in ,out ,inout ] 参数名 数据类形...) 3 begin 4 sql语句 5 end 自定义的结束符号 6 delimiter ;
实现：
delimiter $$ CREATE PROCEDURE pro1() BEGIN SELECT empno,ename FROM emp; END $$ -- 存储过程创建结束，delimiter将‘$$’改回‘;’ delimiter; -- 调用 CALL pro1();
②局部变量--DECLARE,SET
局部变量概述：用户自定义，在begin/end内有效
语法：declare 变量名 type [default 变量值]
举例：declare empname VARCHAR(20) DEFAULT 'IT部门'
实现：
1 delimiter $$ 2 CREATE PROCEDURE pro2() 3 BEGIN 4 DECLARE var_name1 VARCHAR(20) DEFAULT 'abc'; -- 定义局部变量并设置默认值 5 SET var_name1='jack'; 6 SELECT var_name1; 7 END $$ 8 delimiter;
③局部变量--SELECT...INTO
-- 格式：SELECT 字段名 [...] INTO var_name1[...] FROM 表名 WHERE 条件
-- 注意：当将查询结果赋值给变量时，该查询语句的返回结果只能是单行单列。
实现：
delimiter $$ CREATE PROCEDURE pro3() BEGIN DECLARE my_ename VARCHAR(20); SELECT ename INTO my_ename FROM emp WHERE empno=1001; SELECT my_ename; END $$ delimiter ;
④用户变量
用户变量概述：当前会话(连接)有效。类比java的成员变量
语法：@变量名。不需要提前声明，使用即声明
实现：
1 delimiter $$ 2 CREATE PROCEDURE pro4() 3 BEGIN 4 SET @var_name1 = 'ZS'; 5 END $$ 6 delimiter; 7 8 CALL pro4(); 9 SELECT @var_name1; -- 当前会话(连接)关闭后，值“ZS”消失，只有再次调用存储过程才存在
⑤系统变量
系统变量又分为全局变量与会话变量
全局变量：系统提供，在整个数据库有有效
在MYSQL启动的时候由服务器自动将它们初始化为默认值，这些默认值可以通过更改my.ini这个文件来更改。
有些系统变量的值是可以利用语句来动态进行更改的，但是有些系统变量的值却是只读的，对于那些可以更改的系统变量，我们可以利用set语句进行更改。
1 -- 语法：@@global.变量名 2 show global variables;-- 查看全局变量 3 -- 查看某全局变量 4 select @@global.auto_increment_increment; 5 -- 修改全局变量的值 6 set global sort_buffer_size = 40000; 7 set @@global.sort_buffer_size = 40000;
会话变量：当前会话(连接)有效
会话变量在每次建立一个新的连接的时候，由MYSQL来初始化。MYSQL会将当前所有全局变量的值复制一份。来做为会话变量。
也就是说，如果在建立会话以后，没有手动更改过会话变量与全局变量的值，那所有这些变量的值都是一样的。
全局变量与会话变量的区别就在于，对全局变量的修改会影响到整个服务器，但是对会话变量的修改，只会影响到当前的会话（也就是当前的数据库连接）。
1 -- 语法：@@session.变量名 2 -- 查看会话变量 3 show session variables; 4 -- 查看某会话变量 5 select @@session.auto_increment_increment; 6 -- 修改会话变量的值 7 set session sort_buffer_size = 50000; 8 set @@session.sort_buffer_size = 50000 ;
⑥存储过程传参
IN
in 输入参数：意思说你的参数要传到存过过程的过程里面去，在存储过程中修改该参数的值不能被返回
1 -- in 表示传入的参数， 可以传入数值或者变量，即使传入变量，并不会更改变量的值，可以内部更改，仅仅作用在函数范围内。 2 -- 封装有参数的存储过程，传入员工编号，查找员工信息 3 delimiter $$ 4 CREATE PROCEDURE dec_param1(IN param_empno VARCHAR(20)) 5 BEGIN 6 SELECT * FROM emp WHERE empno = param_empno; 7 END $$ 8 delimiter; 9 10 CALL dec_param1('1001'); 11 12 -- 封装有参数的存储过程，可以通过传入部门名和薪资，查询指定部门，并且薪资大于指定值的员工信息 13 delimiter $$ 14 CREATE PROCEDURE dec_param2(IN dname VARCHAR(20) ,IN sal DECIMAL(7,2)) 15 BEGIN 16 SELECT * FROM dept a,emp b WHERE b.sal>sal AND a.dname=dname; 17 END $$ 18 delimiter; 19 20 CALL dec_param2('学工部',10000);
OUT
out 输出参数：该值可在存储过程内部被改变，并向外输出
-- out 表示从存储过程内部传值给调用者 -- 封装有参数的存储过程，传入员工编号，返回员工名字 delimiter $$ create procedure dec_param3(in empno int ,out out_ename varchar(50) ) begin select ename into out_ename from emp where emp.empno = empno; end $$ delimiter ; call dec_param3(1001, @o_ename); select @o_ename; -- 封装有参数的存储过程，传入员工编号，返回员工名字和薪资 delimiter $$ create procedure dec_param4(in empno int ,out out_ename varchar(50) ,out out_sal decimal(7,2)) begin select ename,sal into out_ename,out_sal from emp where emp.empno = empno; end $$ delimiter ; call dec_param4(1001, @o_dname,@o_sal); select @o_dname; select @o_sal;
INOUT
inout 输入输出参数：既能输入一个值又能传出来一个值)
1 -- inout 表示从外部传入的参数经过修改后可以返回的变量，既可以使用传入变量的值也可以修改变量的值（即使函数执行完） 2 -- 传入员工名，拼接部门号，传入薪资，求出年薪 3 delimiter $$ 4 create procedure dec_param5(inout inout_ename varchar(50),inout inout_sal int) 5 begin 6 select concat(deptno,"_",inout_ename) into inout_ename from emp where ename = inout_ename; 7 set inout_sal = inout_sal * 12; 8 end $$ 9 delimiter ; 10 set @inout_ename = '关羽'; 11 set @inout_sal = 3000; 12 call dec_param5(@inout_ename, @inout_sal) ; 13 select @inout_ename ; 14 select @inout_sal ;
⑦流程控制-判断
if判断
IF语句包含多个条件判断，根据结果为TRUE、FALSE执行语句，与编程语言中的if、else if、else语法类似。
语法：
1 if search_condition_1 then statement_list_1 2 [elseif search_condition_2 then statement_list_2] ... 3 [else statement_list_n] 4 end if
实现：
1 -- 输入学生的成绩，来判断成绩的级别： 2 /* 3 score < 60 :不及格 4 score >= 60 , score <80 :及格 5 score >= 80 , score < 90 :良好 6 score >= 90 , score <= 100 :优秀 7 score > 100 :成绩错误 8 */ 9 delimiter $$ 10 create procedure proc_12_if(in score int) 11 begin 12 if score < 60 13 then 14 select '不及格'; 15 elseif score < 80 16 then 17 select '及格' ; 18 elseif score >= 80 and score < 90 19 then 20 select '良好'; 21 elseif score >= 90 and score <= 100 22 then 23 select '优秀'; 24 else 25 select '成绩错误'; 26 end if; 27 end $$ 28 delimiter ;
case
CASE是另一个条件判断的语句，类似于编程语言中的switch语法
语法一：
1 case case_value 2 when when_value then statement_list 3 [when when_value then statement_list] ... 4 [else statement_list] 5 end case
实现：
1 -- 语法一 2 delimiter $$ 3 create procedure proc14_case(in pay_type int) 4 begin 5 case pay_type 6 when 1 7 then 8 select '微信支付' ; 9 when 2 then select '支付宝支付' ; 10 when 3 then select '银行卡支付'; 11 else select '其他方式支付'; 12 end case ; 13 end $$ 14 delimiter ;
语法二：
1 case 2 when search_condition then statement_list 3 [when search_condition then statement_list] ... 4 [else statement_list] 5 end case
实现：
1 -- 语法二 2 delimiter $$ 3 create procedure proc_15_case(in score int) 4 begin 5 case 6 when score < 60 7 then 8 select '不及格'; 9 when score < 80 10 then 11 select '及格' ; 12 when score >= 80 and score < 90 13 then 14 select '良好'; 15 when score >= 90 and score <= 100 16 then 17 select '优秀'; 18 else 19 select '成绩错误'; 20 end case; 21 end $$ 22 delimiter ;
⑧流程控制-循环
leave 类似于 break，跳出，结束当前所在的循环
iterate类似于 continue，继续，结束本次循环，继续下一次
循环的标签是自己定义的，可以是中文可以是英文，尽量英文
while循环
格式：
【标签:】while 循环条件 do
循环体;
end while【 标签】;
1 -- -------存储过程-while 2 -- 循环往user表插入十条数据 3 delimiter $$ 4 create procedure proc16_while1(in insertcount int) 5 begin 6 declare i int default 1; 7 label:while i<=insertcount do 8 insert into user(uid,username,`password`) values(i,concat('user-',i),'123456'); 9 set i=i+1; 10 end while label; 11 end $$ 12 delimiter ; 13 14 -- --存储过程-while + leave 15 -- 当i=5时结束本次循环 16 truncate table user; 17 delimiter $$ 18 create procedure proc16_while2(in insertcount int) 19 begin 20 declare i int default 1; 21 标签:while i<=insertcount do 22 insert into user(uid,username,`password`) values(i,concat('user-',i),'123456'); 23 if i=5 then leave 标签; 24 end if; 25 set i=i+1; 26 end while 标签; 27 end $$ 28 delimiter ; 29 30 -- 存储过程-while+iterate 31 truncate table user; 32 delimiter $$ 33 create procedure proc16_while3(in insertcount int) 34 begin 35 declare i int default 0; 36 label:while i<=insertcount do 37 set i = i +1; 38 if i=5 then iterate label; 39 end if; 40 insert into user(uid,username,`password`) values(i,concat('user-',i),'123456'); 41 end while label; 42 end $$ 43 delimiter ;
repeat循环
格式：
[标签:]repeat
循环体;
until 条件表达式
end repeat [标签];
1 -- --存储过程-循环控制-repeat 2 truncate table user; 3 delimiter $$ 4 create procedure proc18_repeat(in insertCount int) 5 begin 6 declare i int default 1; 7 label:repeat 8 insert into user(uid, username, password) values(i,concat('user-',i),'123456'); 9 set i = i + 1; 10 until i > insertCount 11 end repeat label; 12 select '循环结束'; 13 end $$ 14 delimiter ;
loop循环
格式：
[标签:] loop
循环体;
if 条件表达式 then
leave [标签];
end if;
end loop;
1 -- --存储过程-循环控制-loop+leave 2 truncate table user; 3 delimiter $$ 4 create procedure proc19_loop(in insertCount int) 5 begin 6 declare i int default 1; 7 label:loop 8 insert into user(uid, username, password) values(i,concat('user-',i),'123456'); 9 set i = i + 1; 10 if i > 5 11 then 12 leave label; 13 end if; 14 end loop label; 15 select '循环结束'; 16 end $$ 17 delimiter ;
⑨游标
游标(cursor)是用来存储查询结果集的数据类型 , 在存储过程和函数中可以使用光标对结果集进行循环的处理。光标的使用包括光标的声明、OPEN、FETCH 和 CLOSE。
1 -- 声明语法 2 declare cursor_name cursor for select_statement 3 -- 打开语法 4 open cursor_name 5 -- 取值语法 6 fetch cursor_name into var_name [, var_name] ... 7 -- 关闭语法 8 close cursor_name
操作：
1 delimiter $$ 2 create procedure proc20_cursor(in in_dname varchar(50)) 3 begin 4 -- 定义局部变量 5 declare var_empno varchar(50); 6 declare var_ename varchar(50); 7 declare var_sal decimal(7,2); 8 9 -- 声明游标 10 declare my_cursor cursor for 11 select empno , ename, sal 12 from dept a ,emp b 13 where a.deptno = b.deptno and a.dname = in_dname; 14 15 -- 打开游标 16 open my_cursor; 17 -- 通过游标获取每一行数据 18 label:loop 19 fetch my_cursor into var_empno, var_ename, var_sal; 20 select var_empno, var_ename, var_sal; 21 end loop label; 22 23 -- 关闭游标 24 close my_cursor; 25 end 26 -- 调用存储过程 27 call proc20_cursor('销售部');
⑩异常处理--Handle句柄
官方文档：https://dev.mysql.com/doc/refman/5.7/en/declare-handler.html
格式：
1 DECLARE handler_action HANDLER 2 FOR condition_value [, condition_value] ... 3 statement 4 5 handler_action: { 6 CONTINUE 7 | EXIT 8 | UNDO 9 } 10 11 condition_value: { 12 mysql_error_code 13 | condition_name 14 | SQLWARNING 15 | NOT FOUND 16 | SQLEXCEPTION
注意：在语法中，变量声明、游标声明、handler声明是必须按照先后顺序书写的，否则创建存储过程出错。
实现：
1 -- 需求：输入一个部门名，查询该部门员工的编号、名字、薪资 ，将查询的结果集添加游标 2 delimiter $$ 3 create procedure proc20_cursor(in in_dname varchar(50)) 4 begin 5 -- 定义局部变量 6 declare var_empno int; 7 declare var_ename varchar(50); 8 declare var_sal decimal(7,2); 9 10 declare flag int default 1; -- --------------------- 11 12 -- 声明游标 13 declare my_cursor cursor for 14 select empno,ename,sal 15 from dept a, emp b 16 where a.deptno = b.deptno and a.dname = in_dname; 17 18 -- 定义句柄，当数据未发现时将标记位设置为0 19 declare continue handler for NOT FOUND set flag = 0; 20 21 -- 打开游标 22 open my_cursor; 23 -- 通过游标获取值 24 label:loop 25 fetch my_cursor into var_empno, var_ename,var_sal; 26 -- 判断标志位 27 if flag = 1 then 28 select var_empno, var_ename,var_sal; 29 else 30 leave label; 31 end if; 32 end loop label; 33 34 -- 关闭游标 35 close my_cursor; 36 end $$; 37 38 delimiter ;
5. 练习用的表
CREATE TABLE `dept` ( `deptno` int(11) NOT NULL, `dname` varchar(20) DEFAULT NULL, `loc` varchar(20) DEFAULT NULL, PRIMARY KEY (`deptno`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC; CREATE TABLE `emp` ( `empno` int(11) NOT NULL, `ename` varchar(20) DEFAULT NULL, `job` varchar(20) DEFAULT NULL, `mgr` int(11) DEFAULT NULL, `hiredate` date DEFAULT NULL, `sal` decimal(8,2) DEFAULT NULL, `comm` decimal(8,2) DEFAULT NULL, `deptno` int(11) DEFAULT NULL, PRIMARY KEY (`empno`) USING BTREE, KEY `deptno` (`deptno`) USING BTREE, CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`deptno`) REFERENCES `dept` (`deptno`) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC; CREATE TABLE `salgrade` ( `grade` int(11) NOT NULL, `losal` int(11) DEFAULT NULL, `hisal` int(11) DEFAULT NULL, PRIMARY KEY (`grade`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC; INSERT INTO `dept`(`deptno`, `dname`, `loc`) VALUES (10, '教研部', '北京'); INSERT INTO `dept`(`deptno`, `dname`, `loc`) VALUES (20, '学工部', '上海'); INSERT INTO `dept`(`deptno`, `dname`, `loc`) VALUES (30, '销售部', '广州'); INSERT INTO `dept`(`deptno`, `dname`, `loc`) VALUES (40, '财务部', '武汉'); INSERT INTO `emp`(`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) VALUES (1001, '甘宁', '文员', 1013, '2000-12-17', 8000.00, NULL, 20); INSERT INTO `emp`(`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) VALUES (1002, '黛绮丝', '销售员', 1006, '2001-02-20', 16000.00, 3000.00, 30); INSERT INTO `emp`(`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) VALUES (1003, '殷天正', '销售员', 1006, '2001-02-22', 12500.00, 5000.00, 30); INSERT INTO `emp`(`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) VALUES (1004, '刘备', '经理', 1009, '2001-04-02', 29750.00, NULL, 20); INSERT INTO `emp`(`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) VALUES (1005, '谢逊', '销售员', 1006, '2001-09-28', 12500.00, 14000.00, 30); INSERT INTO `emp`(`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) VALUES (1006, '关羽', '经理', 1009, '2001-05-01', 28500.00, NULL, 30); INSERT INTO `emp`(`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) VALUES (1007, '张飞', '经理', 1009, '2001-09-01', 24500.00, NULL, 10); INSERT INTO `emp`(`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) VALUES (1008, '诸葛亮', '分析师', 1004, '2007-04-19', 30000.00, NULL, 20); INSERT INTO `emp`(`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) VALUES (1009, '曾阿牛', '董事长', NULL, '2001-11-17', 50000.00, NULL, 10); INSERT INTO `emp`(`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) VALUES (1010, '韦一笑', '销售员', 1006, '2001-09-08', 15000.00, 0.00, 30); INSERT INTO `emp`(`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) VALUES (1011, '周泰', '文员', 1008, '2007-05-23', 11000.00, NULL, 20); INSERT INTO `emp`(`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) VALUES (1012, '程普', '文员', 1006, '2001-12-03', 9500.00, NULL, 30); INSERT INTO `emp`(`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) VALUES (1013, '庞统', '分析师', 1004, '2001-12-03', 30000.00, NULL, 20); INSERT INTO `emp`(`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) VALUES (1014, '黄盖', '文员', 1007, '2002-01-23', 13000.00, NULL, 10); INSERT INTO `salgrade`(`grade`, `losal`, `hisal`) VALUES (1, 7000, 12000); INSERT INTO `salgrade`(`grade`, `losal`, `hisal`) VALUES (2, 12010, 14000); INSERT INTO `salgrade`(`grade`, `losal`, `hisal`) VALUES (3, 14010, 20000); INSERT INTO `salgrade`(`grade`, `losal`, `hisal`) VALUES (4, 20010, 30000); INSERT INTO `salgrade`(`grade`, `losal`, `hisal`) VALUES (5, 30010, 99990);
6. 存储函数
介绍：
MMySQL存储函数（自定义函数），函数一般用于计算和返回一个值，可以将经常需要使用的计算或功能写成一个函数。
存储函数和存储过程一样，都是在数据库中定义一些 SQL 语句的集合。
存储函数和存储过程的区别：
1.存储函数有且只有一个返回值，而存储过程可以有多个返回值，也可以没有返回值。
2.存储函数只能有输入参数，而且不能带in, 而存储过程可以有多个in,out,inout参数。
3.存储过程中的语句功能更强大，存储过程可以实现很复杂的业务逻辑，而函数有很多限制，如不能在函数中使用insert,update,delete,create等语句；
4.存储函数只完成查询的工作，可接受输入参数并返回一个结果，也就是函数实现的功能针对性比较强。
5.存储过程可以调用存储函数。但函数不能调用存储过程。
6.存储过程一般是作为一个独立的部分来执行(call调用)。而函数可以作为查询语句的一个部分来调用.
创建格式：
1 create function func_name ([param_name type[,...]]) 2 returns type 3 [characteristic ...] 4 begin 5 routine_body 6 end;
参数说明：
（1）func_name ：存储函数的名称。
（2）param_name type：可选项，指定存储函数的参数。type参数用于指定存储函数的参数类型，该类型可以是MySQL数据库中所有支持的类型。
（3）RETURNS type：指定返回值的类型。
（4）characteristic：可选项，指定存储函数的特性。
（5）routine_body：SQL代码内容。
实现：
1 -- 创建存储函数-没有输输入参数 2 drop function if exists myfunc1_emp; 3 delimiter $$ 4 create function myfunc1_emp() returns int 5 begin 6 declare cnt int default 0; 7 select count(*) into cnt from emp; 8 return cnt; 9 end $$ 10 delimiter ; 11 -- 调用存储函数 12 select myfunc1_emp(); 13 14 -- 创建存储过程-有输入参数 15 drop function if exists myfunc2_emp; 16 delimiter $$ 17 create function myfunc2_emp(in_empno int) returns varchar(50) 18 begin 19 declare out_name varchar(50); 20 select ename into out_name from emp where empno = in_empno; 21 return out_name; 22 end $$ 23 delimiter ; 24 25 select myfunc2_emp(1008);
