Mysql 存储过程

存储过程概念

存储过程（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 //

仅供参考