存储过程概念
存储过程(Stored Procedure)是在大型数据库系统中,一组为了完成特定功能的SQL 语句集,它存储在数据库中,一次编译后永久有效,用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。存储过程是数据库中的一个重要对象。在数据量特别庞大的情况下利用存储过程能达到倍速的效率提升
存储过程结构
CREATE
[DEFINER = user]
PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body
CREATE
[DEFINER = user]
FUNCTION sp_name ([func_parameter[,...]])
RETURNS type
[characteristic ...] routine_body
proc_parameter:
[ IN | OUT | INOUT ] param_name type
func_parameter:
param_name type
type:
Any valid MySQL data type
官方参考文档地址 :https://dev.mysql.com/doc/refman/5.7/en/create-procedure.html
proc_parameter:三个参数解析
IN 输入参数:表示调用者向过程传入值(传入值可以是字面量或变量)
OUT 输出参数:表示过程向调用者传出值(可以返回多个值)(传出值只能是变量)
INOUT 输入输出参数:既表示调用者向过程传入值,又表示过程向调用者传出值(值只能是变量)
案例分析
#此为in 和out 案例 in传入值可以是变量也可以是字面量 但是out传出值必须是变量
#根据部门编号查询部门地址
delimiter //
create procedure sp_test3(in deptno int , out loc varchar(32))
begin
select d.LOC into loc from dept d where d.DEPTNO = deptno;
end //
call sp_test3(10 , @loc);
select @loc;
#查询雇员表7788员工的姓名并在拼接hello
delimiter //
create procedure sp_test4(in empno int , inout word varchar(32))
begin
declare e_name varchar(32);
select e.ENAME into e_name from emp e where e.EMPNO = empno;
select concat(word, '-', e_name );
end //
set @word = 'hello';
call sp_test4(7788, @word);
调用存储过程
CALL sp_name([parameter[,...]])
CALL sp_name[()]
查看存储过程
# 查询db_name数据库中的所有的存储过程
select name from mysql.proc where db='db_name';
# 查询存储过程的状态信息
show procedure status;
#查询某个存储过程的定义
show create procedure test.pro_test1 \G;
删除存储过程
DROP PROCEDURE [IF EXISTS] sp_name ;
if条件判断
语法结构
if search_condition then statement_list
[elseif search_condition then statement_list] ...
[else statement_list]
end if;
search_condition 判断条件
案例分析
#if条件判断 根据传入编号查询雇员入职年限判定为是否是老员工 判断条件 35年以下为铜牌员工
#35- 38年为银牌员工 39年以上为金牌员工
delimiter //
create procedure sp_hiredate(in empno int)
begin
declare hire_year int ;
declare result varchar(32);
select timestampdiff(year, e.HIREDATE , now()) into hire_year from emp e where e.EMPNO = empno;
if hire_year <35 then
set result = '铜牌员工';
elseif hire_year > 35 && hire_year < 39 then
set result = '银牌员工';
else
set result = '金牌员工';
end if ;
select result;
end //
call sp_hiredate(7369);
case选择
语法结构
方式一 :
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 sp_case_month(in month int)
begin
declare result varchar(32);
case
when month>=1 && month <= 3 then
set result = '第一季度';
when month >= 4 && month <= 6 then
set result = '第二季度';
when month >=7 && month <= 9 then
set result = '第三季度';
when month >10 && month <= 12 then
set result = '第四季度';
end case ;
select concat('您输入的月份为 :', month , ' , 该月份为 : ' , result) as content ;
end //
Loop循环
LOOP 实现简单的循环,退出循环的条件需要使用其他的语句定义,通常可以使用 LEAVE 语句实现
[begin_label:] LOOP
statement_list
END LOOP [end_label]
案例分析
#循环打印1到10
delimiter //
create procedure sp_num_loop()
begin
declare num_index int default 1;
num_loop :loop
select num_index;
if num_index >=10 then
leave num_loop;
end if ;
set num_index = num_index + 1;
end loop ;
end //
repeat循环
repeat 类似do while 循环 先进行逻辑运算在进行判断 如果满足条件 则直接退出
[begin_label:]REPEAT
statement_list
UNTIL search_condition
END REPEAT [end_label];
delimiter //
案例分析
#通过 repeat 循环打印一到十
create procedure sp_num_repeat()
begin
declare num_index int default 1;
num_repeat : repeat
select num_index;
set num_index = num_index +1 ;
until num_index >= 10
end repeat num_repeat ;
end //
while循环
类比java while 循环
while search_condition do
statement_list
end while;
案例分析
#循环打印一到十 while
delimiter //
create procedure sp_num_while()
begin
declare num_index int default 1;
while num_index <= 10 do
select num_index ;
set num_index = num_index +1 ;
end while;
end //
光标
handler参考链接资料
https://www.mysqlzh.com/doc/225/508.html
案例分析
#根据传入雇员部门信息 查询部门雇员编号 雇员姓名 雇员薪资
#存储过程中 declare顺序依次为 普通变量 游标/光标 句柄 如果顺序错误则会整个存储过程错误
delimiter //
create procedure sp_show_emp(in d_dname varchar(32))
begin
declare e_empno int ;
declare e_ename varchar(32);
declare e_sal decimal(7,2);
declare flag boolean default true;
#声明游标
declare e_cursor cursor for
select e.EMPNO , e.ENAME , e.SAL
from emp e, dept d
where e.DEPTNO = d.DEPTNO and d.DNAME = d_dname;
#handler 句柄
declare continue handler for 1329 set flag = false ;
#打开游标
open e_cursor;
#循环遍历游标
cursor_loop : loop
fetch e_cursor into e_empno, e_ename, e_sal;
if flag then
select e_empno, e_ename, e_sal;
else
leave cursor_loop;
end if ;
end loop cursor_loop;
#关闭游标
close e_cursor;
end //
仅供参考