【MySQL】存储过程

基本概念

概述

• MySQL 5.0 版本开始支持存储过程
• 简单地说，存储过程就是一组 SQL 语句集，功能强大，可以实现一些比较复杂的逻辑功能，类似于 Java 中的方法
• 存储过程就是数据库 SQL 语言的代码封装和复用

特性：

• 有输入输出参数，可以声明变量，有流程控制语句，通过编写存储过程，可以实现复杂的逻辑功能
• 函数的普遍特性：模块化、封装、代码复用
• 速度快，只有首次执行需要经过编译和优化步骤，后续被调用可以直接执行，省去重复步骤

存储过程操作

下面的存储过程操作基于下表：

代码示例：

-- 创建部门表
create table dept (
    deptno int,
    dname varchar(50),
    loc varchar(50)
);
-- 插入数据
insert into dept (deptno, dname, loc) values
    (10, '教研部', '北京'),(20, '学工部', '上海'),
    (30, '销售部', '广州'),(40, '财务部', '武汉');
-- 创建员工表
create table emp (
    empno int,
    ename varchar(50),
    job varchar(50),
    mgr int,
    hiredate date,
    sal decimal(10, 2),
    comm decimal(10, 2),
    deptno int
);
-- 插入数据
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) values
    (1001, '甘宁', '文员', 1013, '2000-12-17', 8000.00, null, 20),
    (1002, '黛绮丝', '销售员', 1006, '2001-02-20', 16000.00, 3000.00, 30),
    (1003, '殷天正', '销售员', 1006, '2001-02-22', 12500.00, 5000.00, 30),
    (1004, '刘备', '经理', 1009, '2001-04-02', 29750.00, null, 20),
    (1005, '谢逊', '销售员', 1006, '2001-09-28', 12500.00, 14000.00, 30),
    (1006, '关羽', '经理', 1009, '2001-05-01', 28500.00, null, 10),
    (1007, '张飞', '经理', 1009, '2001-09-01', 24500.00, null, 10),
    (1008, '诸葛亮', '分析师', 1004, '2007-04-19', 30000.00, null, 20),
    (1009, '曾阿牛', '董事长', null, '2001-11-17', 50000.00, null, 10),
    (1010, '韦一笑', '销售员', 1006, '2001-09-08', 15000.00, 0.00, 30),
    (1011, '周泰', '文员', 1008, '2007-05-23', 11000.00, null, 20),
    (1012, '程普', '文员', 1006, '2001-12-03', 9500.00, null, 30),
    (1013, '庞统', '分析师', 1004, '2001-12-03', 30000.00, null, 20),
    (1014, '黄盖', '文员', 1007, '2002-01-23', 13000.00, null, 10);
-- 创建工资等级表
create table salgrade (
    grade int,
    losal decimal(10, 2),
    hisal decimal(10, 2)
);
-- 插入数据
insert into salgrade (grade, losal, hisal) values
    (1, 7000, 12000),(2, 12010, 14000),(3, 14010, 20000),
    (4, 20010, 30000),(5, 30010, 99990);

结果如下：

定义存储过程

格式如下：

delimiter 自定义结束符号
create procedure 存储名 ([in|out|inout] 参数名 数据类型)
begin
	sql语句
end 自定义结束符号
delimiter ;

代码示例：

delimiter $
create procedure proc1()
begin
    select empno,ename from emp;
end $
delimiter ;
-- 调用存储过程
call proc1();

结果如下：

变量定义

局部变量

用户自定义，在 begin-end 块中有效

格式如下：

declare var_name type [default var_value];

代码示例：

delimiter $
create procedure proc2()
begin
	-- 定义局部变量
    declare var_name varchar(20) default 'null';
    set var_name = 'Jack';
    select var_name;
end $
delimiter ;
call proc2();

结果如下：

还可以使用 select...into 语句为变量赋值，格式如下：

select col_name [...] into var_name [,...]
from table_name where condition

• col_name 参数：表示查询的字段名称
• var_name 参数：表示变量的名称
• table_name 参数：表示表的名称
• 注意条件：当参数查询结果赋值给变量时，该查询语句的返回结果只能是单行单列

代码示例：

delimiter $
create procedure proc3()
begin
    declare my_name varchar(20);
    select ename into my_name from emp where empno='1001';
    select my_name;
end $
delimiter ;
call proc3();

结果如下：

用户变量

用户自定义，当前会话（连接）有效，相当于 Java 中得成员变量

格式如下：

@var_name

不需要提前声明，使用即声明

代码示例：

delimiter $
create procedure proc4()
begin
    set @var_name2='China';
    select @var_name2;
end $
delimiter ;
call proc4();

结果如下：

系统变量

• 系统变量又分为全局变量与会话变量。
• 全局变量在 MySQL 启动时由服务器自动初始化为默认值，默认值可通过更改 my.ini 文件修改。
• 会话变量在每次新建连接时由 MySQL 初始化，MySQL 会复制当前所有全局变量的值作为会话变量。
• 若建立会话后未手动更改会话变量与全局变量的值，所有这些变量的值一致。
• 全局变量与会话变量的区别：修改全局变量影响整个服务器，修改会话变量仅影响当前会话（即当前数据库连接）。
• 部分系统变量值可利用语句动态更改，部分系统变量值为只读；对可更改的系统变量，可利用 set 语句进行更改。

全局变量

由系统提供，在整个数据库有效

格式如下：

@@global.var_name

代码示例：

-- 查看全局变量
show global variables ;
-- 查看某个全局变量
select @@global.auto_increment_increment;
-- 修改全局变量的值
set global sort_buffer_size = 40000;
set @@global.sort_buffer_size = 35000;
select @@global.sort_buffer_size;

会话变量

由系统提供，当前会话（连接）有效

格式如下：

@@session.car_name

代码示例：

-- 查看会话变量
show session variables ;
-- 查看某个会话变量
select @@session.auto_increment_increment;
-- 修改会话变量的值
set session sort_buffer_size = 40000;
set @@session.sort_buffer_size = 35000;
select @@session.sort_buffer_size;

参数传递

in 关键字

in 表示传入的参数，可以传入数值或者变量，即使传入变量也不会更改变量的值，可以内部更改，但作用仅在函数范围内

代码示例：

delimiter $
create procedure proc5(in param_empno int)
begin
    select * from emp where empno = param_empno;
end $
delimiter ;
call proc5('1001');

结果如下：

注意事项：

• param_empno 是形参，形参取名尽量不要跟实参名一样
• 如果形参名和实参名一样，要在实参名前指定表，比如：emp.empno

out 关键字

out 表示从存储过程内部传值给调用者

代码示例：

delimiter $
create procedure proc6(in in_empno int ,out out_ename varchar(20))
begin
    select ename into out_ename from emp where empno = in_empno;
end $
delimiter ;
call proc6(1001,@o_ename);

结果如下：

inout 关键字

inout 表示从外部传入的参数经过修改后可以返回的变量，既可以使用传入变量的值，也可以修改变量的值（即使函数执行完）

代码示例：

delimiter $
create procedure proc7(inout inout_ename varchar(20),inout inout_sal int)
begin
    select concat(deptno,'_',inout_ename) into inout_ename from emp
        where ename=inout_ename;
    set inout_sal = inout_sal*12;
end $
delimiter ;

set @inout_ename='关羽';
set @inout_sal=3000;
call proc7(@inout_ename,@inout_sal);
select @inout_ename,@inout_sal;

结果如下：

流程控制

判断

分支语句 if

if 语句包含多个条件判断，根据结果为 true，false 执行语句，与编程语言中的 if、if-else 语法类似

格式如下：

if search_condition_1 then statement_list_1
	[elseif search_condition_2 then statement_list_2]
	......
	[else statement_list_n]
end if

代码示例：

delimiter $
create procedure proc8(in in_ename varchar(20))
begin
    declare result varchar(20);
    declare var_sal decimal(7,2);
    select sal into var_sal from emp where ename=in_ename;
    if var_sal<10000
        then set result='试用薪资';
    elseif var_sal<30000
        then set result='转正薪资';
    else
        set result='元老薪资';
    end if;
    select result;
end $
delimiter ;
call proc8('张飞');

结果如下：

分支语句 case

case 语句类似于编程语言中的 switch 语句块

格式如下：

-- 语法一 类比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

代码示例：

delimiter $
create procedure proc9(in in_ename varchar(20))
begin
    declare var_sal decimal(7,2);
    select sal into var_sal from emp where ename=in_ename;
    case var_sal
        when var_sal<12000 and var_sal>7000 then select '实习薪资';
        when var_sal<14000 and var_sal>120010 then select '转正薪资';
        when var_sal<20000 and var_sal>140010 then select '部长薪资';
        when var_sal<30000 and var_sal>200010 then select '总经理薪资';
        else select '总裁薪资';
    end case;
end $
delimiter ;
call proc9('刘备');

结果如下：

循环

循环是一段在程序中只出现一次，但可能会连续多次的代码

循环中的代码会运行特定的次数，或者是运行到特定条件成立时结束循环

循环分类：

• while
• repeat
• loop

循环控制：

• leave 类似于 break，跳出，结束当前所在的循环
• iterate 类似于 continue，继续，结束本次循环，继续下一次

循环语句 while

格式如下：

[标签:] while 循环条件 do
	循环体;
end while [标签];

代码示例：

create table user(
    uid int primary key ,
    username varchar(50) ,
    password varchar(50)
);
delimiter $
create procedure proc10(in insertcount int)
begin
    declare i int default 1;
    label:while i<=insertcount do
        insert into user(uid, username, password)
            values (i,concat('user-',i),'123456');
        set i=i+1;
    end while label;
end $
delimiter ;
call proc10(10);

结果如下：

使用 leave 跳出循环，代码示例：

delimiter $
create procedure proc10(in insertcount int)
begin
    declare i int default 1;
    label:while i<=insertcount do
        insert into user(uid, username, password)
            values (i,concat('user-',i),'123456');
        if i=5 then
            leave label;
        end if;
        set i=i+1;
    end while label;
end $
delimiter ;
call proc10(10);

结果如下：

循环语句 repeat

格式如下：

[标签:] repeat
	循环体;
until 条件表达式
end repeat [标签];

代码示例：

delimiter $
create procedure proc11(in inserconut int)
begin
    declare i int default 1;
    label:repeat
        insert into user(uid, username, password)
            values (i,concat('user-',i),'123456');
        set i=i+1;
        until  i>inserconut
    end repeat label;
end $
delimiter ;
call proc11(10);

结果如下：

循环语句 loop

格式如下：

[标签:] loop
	循环体;
	if 条件表达式 then
		leave [标签];
	end if;
end loop;

代码示例：

delimiter $
create procedure proc12(in insertcount int)
begin
    declare i int default 1;
    label:loop
        insert into user(uid, username, password)
            values (i,concat('user-',i),'123456');
        set i=i+1;
        if i>insertcount
            then leave label;
        end if;
    end loop label;
end $
delimiter ;
call proc12(10);

结果如下：

游标

游标（cursor）是用来存储查询结果集的数据类型，在存储过程和函数中可以使用光标对结果集进行循环的处理。光标的使用包括光标的声明、OPEN、FETCH 和 CLOSE

格式如下：

-- 声明语法
declare cursor_name cursor for select_statement
-- 打开语法
open cursor_name
-- 取值语法
fetch cursor_name into var_name [, var_name] ...
-- 关闭语法
close cursor_name

代码示例：

delimiter $
create procedure proc13(in in_dname varchar(50))
begin
    -- 声明局部变量
    declare var_empno varchar(50);
    declare var_ename varchar(50);
    declare var_sal decimal(7,2);
    -- 声明游标
    declare my_cursor cursor for
        select empno,ename,sal
            from dept a,emp b
            where a.deptno=b.deptno and a.dname=in_dname;
    -- 打开游标
    open my_cursor;
    -- 通过游标获取值
    label:loop
        fetch my_cursor into var_empno,var_ename,var_sal;
        select var_empno,var_ename,var_sal;
    end loop label;
    -- 关闭游标
close my_cursor;
end $
delimiter ;

游标类似于指针，每获取一行值后，就指向下一行

但是等到最后一行获取完后，就会产生报错

要解决这种问题，将用到下面的内容

异常处理

MySQL 存储过程提供了对异常处理的功能，HANDLER 句柄，通过定义 HANDLER 来完成异常声明的实现

格式如下：

declare handler_action handler
	for condition_value [, condition_value] ...
	statement
/*
handler_action:{
	continue -- 继续执行剩余代码
	|exit -- 直接终止程序
	|undo -- 不支持
}
condition_value:{
	mysql_error_code|condition name|sqlwarning|not found|sqlexception
}
/*

注意事项：在语法中，变量声明、游标声明、handler 声明是必须按照先后顺序书写的，否则创建存储过程出错

定义句柄的思路：

1. 异常处理完之后程序该怎么执行
   • continue – 继续执行剩余代码
   • exit – 直接终止程序
   • undo – 不支持
2. 触发条件
   • 条件码：报错前的数字
   • 条件名：
     • mysql_error_code
     • condition name
     • sqlwarning
     • not found
     • sqlexception
3. 异常触发之后执行什么代码
   • 设置 flag 的值

代码示例：

delimiter $
create procedure proc14(in in_dname varchar(50))
begin
    -- 声明局部变量
    declare var_empno varchar(50);
    declare var_ename varchar(50);
    declare var_sal decimal(7,2);
    -- 定义标记值
    declare flag int default 1;
    -- 声明游标
    declare my_cursor cursor for
        select empno,ename,sal
        from dept a,emp b
        where a.deptno=b.deptno and a.dname=in_dname;
    -- 定义句柄：定义异常的处理方式
    declare continue handler for 1329 set flag=0;
    -- 打开游标
    open my_cursor;
    -- 通过游标获取值
    label:loop
        fetch my_cursor into var_empno,var_ename,var_sal;
        -- 判断flag
        if flag=1 then
            select var_empno,var_ename,var_sal;
        else
            leave label;
        end if;
    end loop label;
    -- 关闭游标
    close my_cursor;
end $
delimiter ;
call proc14('销售部');

结果如下：

存储函数

在 MySQL 中，创建存储函数使用 create function 关键字

格式如下：

create function func_name ([param_name type[,...]])
returns type
[characteristic ...]
begin
	routine_body
end;

参数说明：

• func_name：存储函数的名称
• param_name type：可选项，指定存储函数的参数。type 参数用于指定存储函数的参数类型，该类型可以是 MySQL 数据库中所有支持的类型
• RETURNS type：指定返回值的类型
• characteristic：可选项，指定存储函数的特性
• routine_body：SQL 代码内容。

代码示例：

-- 允许创建函数权限信任
set global log_bin_trust_function_creators = true;
-- 创建无参存储函数
delimiter $
create function func_emp() returns int
begin
    declare cnt int default 0;
    select count(*) into cnt from emp;
    return cnt;
end $
delimiter ;
-- 调用存储函数
select func_emp();

结果如下：

有参存储函数代码示例：

delimiter $
create function func2_emp(in_empno int) returns varchar(50)
begin
    declare out_ename varchar(50);
    select ename into out_ename from emp where empno=in_empno;
    return out_ename;
end $
delimiter ;
select func2_emp(1008);

结果如下：