MySQL进阶篇存储过程

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);

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/elian91/p/15802715.html