目录
- 基本概念
- 存储过程操作
- 定义存储过程
- 变量定义
- 局部变量
- 用户变量
- 系统变量
- 全局变量
- 会话变量
- 参数传递
- in 关键字
- out 关键字
- inout 关键字
- 流程控制
- 判断
- 分支语句 if
- 分支语句 case
- 循环
- 循环语句 while
- 循环语句 repeat
- 循环语句 loop
- 游标
- 异常处理
- 存储函数
基本概念
概述
- 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] 参数名 数据类型)
beginsql语句
end 自定义结束符号
delimiter ;
代码示例：
delimiter $
create procedure proc1()
beginselect 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()
begindeclare 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()
beginset @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)
beginselect * 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))
beginselect 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)
beginselect concat(deptno,'_',inout_ename) into inout_ename from empwhere 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))
begindeclare result varchar(20);declare var_sal decimal(7,2);select sal into var_sal from emp where ename=in_ename;if var_sal<10000then set result='试用薪资';elseif var_sal<30000then set result='转正薪资';elseset result='元老薪资';end if;select result;
end $
delimiter ;
call proc8('张飞');
结果如下：
分支语句 case
case 语句类似于编程语言中的 switch 语句块
格式如下：
-- 语法一 类比switch
case case_valuewhen when_value then statement_list[when when_value then statement_list]......[else statement_list]
end case-- 语法二
casewhen 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))
begindeclare var_sal decimal(7,2);select sal into var_sal from emp where ename=in_ename;case var_salwhen 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)
begindeclare i int default 1;label:while i<=insertcount doinsert 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)
begindeclare i int default 1;label:while i<=insertcount doinsert into user(uid, username, password)values (i,concat('user-',i),'123456');if i=5 thenleave 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)
begindeclare i int default 1;label:repeatinsert into user(uid, username, password)values (i,concat('user-',i),'123456');set i=i+1;until i>inserconutend repeat label;
end $
delimiter ;
call proc11(10);
结果如下：
循环语句 loop
格式如下：
[标签:] loop循环体;if 条件表达式 thenleave [标签];end if;
end loop;
代码示例：
delimiter $
create procedure proc12(in insertcount int)
begindeclare i int default 1;label:loopinsert into user(uid, username, password)values (i,concat('user-',i),'123456');set i=i+1;if i>insertcountthen 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 forselect empno,ename,salfrom dept a,emp bwhere a.deptno=b.deptno and a.dname=in_dname;-- 打开游标open my_cursor;-- 通过游标获取值label:loopfetch 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 handlerfor condition_value [, condition_value] ...statement
/*
handler_action:{continue -- 继续执行剩余代码|exit -- 直接终止程序|undo -- 不支持
}
condition_value:{mysql_error_code|condition name|sqlwarning|not found|sqlexception
}
/*
注意事项：在语法中，变量声明、游标声明、handler 声明是必须按照先后顺序书写的，否则创建存储过程出错
定义句柄的思路：
- 异常处理完之后程序该怎么执行
- continue – 继续执行剩余代码
- exit – 直接终止程序
- undo – 不支持
- 触发条件
- 条件码：报错前的数字
- 条件名：
- mysql_error_code
- condition name
- sqlwarning
- not found
- sqlexception
- 异常触发之后执行什么代码
- 设置 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 forselect empno,ename,salfrom dept a,emp bwhere a.deptno=b.deptno and a.dname=in_dname;-- 定义句柄：定义异常的处理方式declare continue handler for 1329 set flag=0;-- 打开游标open my_cursor;-- 通过游标获取值label:loopfetch my_cursor into var_empno,var_ename,var_sal;-- 判断flagif flag=1 thenselect var_empno,var_ename,var_sal;elseleave 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 ...]
beginroutine_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
begindeclare 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)
begindeclare out_ename varchar(50);select ename into out_ename from emp where empno=in_empno;return out_ename;
end $
delimiter ;
select func2_emp(1008);
结果如下：
