基本概念

• 存储过程就是一组SQL语句集，实现一些比较复杂的逻辑功能，存储过程在数据库中创建，在其他程序中调用。
• 存储过程就是数据库SQL语言层面的代码封装，可以重复调用。
• 特性

1. 有输入输出参数，参数可以有默认值，可以声明变量。
2. 有一些控制语句，比如if、while等。通过编写存储过程，可以完成一些复杂的SQL逻辑操作。
3. 模块化、封装、代码复用。
4. 执行速度快，因为存储过程在创建时就已经编译和优化过，执行效率高。

• 语法：

delimiter 自定义结束符号
create procedure 存储过程名( in|out 参数名 数据类型, ...)
begin
    SQL语句
end 自定义结束符号
delimiter ; -> 恢复默认结束符号';'

• 创建存储过程

delimiter $$

create procedure p1()
begin
    select a.ename, a.empno from emp a;
end $$
delimiter ;

• 删除存储过程

drop procedure if exists p1;

• 调用存储过程

call p1();

变量定义

/*
局部变量: 只在begin...end中有效
    在begin...end中定义，使用declare关键字
    语法：declare var_name 类型 [default value];
用户变量：当前会话（连接）有效
    不需要提前声明
    语法：@var_name
系统变量：分为全局变量和会话变量
    全局变量：在MySQL启动时服务器自动初始化为默认值，默认值可以通过my.ini文件更改
        语法：@@global.var_name
    会话变量：每次建立新的连接的时候，由MySQL将全局变量的值复制给会话变量
        语法：@@session.var_name
    区别：修改全局变量会影响MySQL服务器，而会话变量只会影响当前会话（连接）
    系统变量有的可以利用语句更改，有的是只读的
*/

• 局部变量

    delimiter $$
    create procedure p2()
    begin
        declare var1 varchar(20) default 'abc'; -- 定义/声明变量
        declare var2 varchar(20);
        set var1 = 'def'; -- 赋值方式1
        select a.ename into var2 from emp a where a.empno = 7369; -- 赋值方式2
        select var1, var2;    -- 输出变量
    end $$
    delimiter ;

    call p2();

• 用户变量

    delimiter $$
    create procedure p3()
    begin
        set @var1 = 'abc'; -- 定义/声明变量
        select @var1;    -- 输出变量
    end $$
    delimiter ;
    call p3();

    select @var1;   -- 也可以查询到变量

• 系统变量

        -- 全局变量
        show global variables;  -- 查看全局变量
        select @@global.auto_increment_offset; -- 查看某全局变量
        -- 修改某全局变量的值
            set @@global.auto_increment_offset = 2;
            set global auto_increment_offset = 2;

        -- 会话变量
        show session variables;  -- 查看会话变量
        select @@session.auto_increment_offset; -- 查看某会话变量
        -- 修改某会话变量的值
            set @@session.auto_increment_offset = 2;
            set session auto_increment_offset = 2;

参数传递

in: 传入参数

• 传入员工编号，查找员工姓名

        delimiter $$
        create procedure p4(in parameter_empno int)
        begin
            select a.ename, a.empno from emp a where a.empno = parameter_empno;
        end $$
        delimiter ;
        call p4(7900);

• 传入部门名称和工资，查找该部门中工资高于指定工资的员工

        delimiter $$
        create procedure p5(in parameter_dname varchar(20), in parameter_salary double)
        begin
            select a.ename, b.dname, a.sal
            from emp a, dept b
            where b.dname = parameter_dname and a.sal > parameter_salary and a.deptno = b.deptno;
        end $$
        delimiter ;
        call p5('researach', 1000);

out：传出参数

• 传入员工编号，传出该员工的名字

        delimiter $$
        create procedure p6(in parameter_empno int, out parameter_ename varchar(20))
        begin
            select a.ename into parameter_ename from emp a where a.empno = parameter_empno;
        end $$
        delimiter ;
        call p6(7902, @var1);
        select @var1;   -- 输出传出参数

• 传入员工编号，传出该员工的名字和工资

        delimiter $$
        create procedure p7(in parameter_empno int, out parameter_ename varchar(20), out paraneter_sal double)
        begin
            select a.ename, a.sal into parameter_ename, paraneter_sal from emp a where a.empno = parameter_empno;
        end $$
        delimiter ;
        call p7(7902, @var1, @var2);
        select @var1, @var2;   -- 输出传出参数

inout：传入传出参数

从外部传入参数，在存储过程中改变它，最后将结果返回

• 传入一个数字，传出他的10倍

        delimiter $$
        create procedure p8(inout parameter_num int)
        begin
            set parameter_num = parameter_num * 10;
        end $$
        delimiter ;
        set @var1 = 5;
        call p8(@var1);
        select @var1;   -- 输出

• 传入员工姓名和工资，将员工的名字拼接部门编号，工资乘以12

        delimiter $$
        create procedure p9(inout parameter_ename varchar(20), inout parameter_sal double)
        begin
            select concat(a.deptno, '-', parameter_ename) into parameter_ename from emp a where a.ename = parameter_ename;
            select parameter_sal * 12 into parameter_sal;
        end $$
        delimiter ;
        set @var1 = 'jack';
        set @var2 = 8000;
        call p9(@var1, @var2);
        select @var1, @var2;   -- 输出

流程控制

if-else

• 成绩等级

        /*
            输入学生成绩，判断成绩等级
            score < 60: 不及格
            score >= 60 and score < 80: 及格
            score >= 80 and score < 90: 良好
            score >= 90 and score <= 100: 优秀
            score > 100: 成绩错误
        */
        delimiter $$
        create procedure p10(in parameter_score int, out parameter_grade varchar(20))
        begin
            if parameter_score < 60
                then set parameter_grade = '不及格';
            elseif parameter_score >= 60 and parameter_score < 80
                then set parameter_grade = '及格';
            elseif parameter_score >= 80 and parameter_score < 90
                then set parameter_grade = '良好';
            elseif parameter_score >= 90 and parameter_score <= 100
                then set parameter_grade = '优秀';
            else
                set parameter_grade = '成绩错误';
            end if;
        end $$
        delimiter ;
        call p10(85, @var1);
        select @var1;   -- 输出

• 传入员工姓名，判断该员工的工资等级

        delimiter $$
        create procedure p11(in parameter_ename varchar(20), out parater_sal_level int)
        begin
            declare var_sal, var_losal, var_hisal double;
            select a.sal into var_sal from emp a where a.ename = parameter_ename;

            select b.losal, b.hisal into var_losal, var_hisal from salgrade b where b.grade = 1;
            if var_sal >= var_losal and var_sal <= var_hisal
                then set parater_sal_level = 1;
            end if;

            select c.losal, c.hisal into var_losal, var_hisal from salgrade c where c.grade = 2;
            if var_sal > var_losal and var_sal <= var_hisal
                then set parater_sal_level = 2;
            end if;

            select d.losal, d.hisal into var_losal, var_hisal from salgrade d where d.grade = 3;
            if var_sal > var_losal and var_sal <= var_hisal
                then set parater_sal_level = 3;
            end if;

            select e.losal, e.hisal into var_losal, var_hisal from salgrade e where e.grade = 4;
            if var_sal > var_losal and var_sal <= var_hisal
                then set parater_sal_level = 4;
            end if;

            select f.losal, f.hisal into var_losal, var_hisal from salgrade f where f.grade = 5;
            if var_sal > var_losal and var_sal <= var_hisal
                then set parater_sal_level = 5;
            end if;
        end $$
        delimiter ;

        call p11('jack', @var1);
        select @var1;   -- 输出

case: 类似Switch

        delimiter $$
        create procedure p12(in pay_type int)
        begin
            case pay_type
                when 1 then select '现金支付';
                when 2 then select '银行卡支付';
                when 3 then select '支付宝支付';
                when 4 then select '微信支付';
                else select '其他支付方式';
            end case;
        end $$
        delimiter ;
        call p12(3);   -- 输出

循环

• 分类：while, loop, repeat
• 控制：leave类似于break，iterate类似于continue
• 新建一个表

        create table if not exists user
        (
            uid int,
            username varchar(20),
            password varchar(20)
        );

• 向表中添加指定条数的数据

1. while循环

                -- while
                truncate user;  -- 清空表

                delimiter $$
                create procedure p13(in parameter_InsertCount int)
                begin
                    declare i int default 1;
                    while i <= parameter_InsertCount do
                            insert into user(uid, username, password) values(i, concat('user', i), concat('pwd', i));
                            set i = i + 1;
                        end while;
                end $$
                delimiter ;
                call p13(5);

                -- while + leave
                truncate user;  -- 清空表

                delimiter $$
                create procedure p14(in parameter_InsertCount int)
                begin
                    declare i int default 1;
                    label: while 1 do
                            insert into user(uid, username, password) values(i, concat('user', i), concat('pwd', i));
                            if i >= parameter_InsertCount
                                then leave label;
                            end if;
                            set i = i + 1;
                        end while label;
                end $$
                delimiter ;
                call p14(5);

2. reapeat循环

                truncate user;  -- 清空表

                delimiter $$
                create procedure p15(in parameter_InsertCount int)
                begin
                    declare i int default 1;
                    label: repeat
                        insert into user(uid, username, password) values(i, concat('user', i), concat('pwd', i));
                        set i = i + 1;
                    until i > parameter_InsertCount
                    end repeat label;
                end $$
                delimiter ;
                call p15(5);

3. loop循环

            truncate user;  -- 清空表
            delimiter $$
            create procedure p16(in parameter_InsertCount int)
            begin
                declare i int default 1;
                label: loop
                    insert into user(uid, username, password) values(i, concat('user', i), concat('pwd', i));
                    set i = i + 1;
                    if i > parameter_InsertCount
                        then leave label;
                    end if;
                end loop label;
            end $$
            delimiter ;
            call p16(5);

游标（cursor）

• cursor是用来储存查询结果集的数据类型，在存储过程中使用游标可以对结果集中的数据进行循环操作，包括声明、open、fetch和close
• 语法

/*
语法：
    -- 声明游标：declare cursor_name cursor for select_statement;
    -- 打开游标：open cursor_name;
    -- 获取游标数据：fetch cursor_name into variable [,variable] 。。。;
    -- 关闭游标：close cursor_name;
*/

• 需求：输入部门名称，查询该部门员工的编号、姓名和工资，将查询结果添加游标，然后逐行输出

	drop procedure if exists p17;   -- 删除存储过程
	delimiter $$
	create procedure p17(in parameter_dname varchar(20))
	begin
	    declare var_empno int;
	    declare var_ename varchar(20);
	    declare var_sal double;
	    -- 声明游标
	    declare my_cursor cursor for
	        select a.empno, a.ename, a.sal
	            from emp a, dept b
	            where a.deptno = b.deptno and b.dname = parameter_dname;
	    -- 打开游标
	    open my_cursor;
	    -- 获取数据
	    label: loop		-- 有正确结果，但是会报错(not found)
	        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 ;
	call p17('sales');

异常处理

在程序中，如果发生错误，则程序会停止执行。为了防止这种情况的发生，可以使用异常处理机制

• 语法

    /*
        语法：
            -- 声明异常处理：declare handler_type handler
                            for condition_value [,condition_value] ...
                            statement;
            -- 异常处理类型(handler_type)：
                -- continue：继续执行
                -- exit：退出执行
                -- undo：撤销事务
            -- 异常处理条件(condition_value)：
                -- mysql错误代码
                -- condition_name：异常名称
                    -- sqlwarning：警告
                    -- not found：未找到
                    -- sqlexception：异常
     */

• 以上个例子为例，使用异常处理loop循环解决报错问题 -- 当遇到not found异常时将标记值flag赋值0，在loop循环中当flag为1时正常获取游标数据，为0时关闭循环并继续执行（continue）后面代码。

        delimiter $$
        create procedure p18(in parameter_dname varchar(20))
        begin
            declare var_empno int;
            declare var_ename varchar(20);
            declare var_sal double;

            -- 定义标记值
            declare flag int default 1;

            -- 声明游标
            declare my_cursor cursor for
                select a.empno, a.ename, a.sal
                    from emp a, dept b
                    where a.deptno = b.deptno and b.dname = parameter_dname;

            -- 定义句柄
            declare continue handler for not found set flag = 0;

            -- 打开游标
            open my_cursor;
            -- 获取数据
            label: loop   -- 正确
                fetch my_cursor into var_empno, var_ename, var_sal;
                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 p18('sales');

存储过程练习

• 需求：提前一个月创建该月的所有表（每天一个，表名格式：table_user_2019_07_31)

    create database if not exists procedure_practice;
    use procedure_practice;
    drop procedure if exists proc_test;

    delimiter $$
    create procedure proc_test()
    begin
        declare next_year int;  -- 下一个月的年份
        declare next_month int; -- 下一个月的月份
        declare next_month_day int; -- 下一个月的最后一天

        declare next_month_str varchar(2); -- 下一个月的月份字符串
        declare next_month_day_str varchar(2); -- 下一个月的日期字符串

        -- 每天的表名
        declare table_name_str varchar(50);

        declare t_index int default 1;

        -- 获取下一个月的年份
        set next_year = year(date_add(now(), interval 1 month));
        -- 获取下一个月的月份
        set next_month = month(date_add(now(), interval 1 month));
        -- 获取下一个月的最后一天
        set next_month_day = dayofmonth(last_day(date_add(now(), interval 1 month)));

        if next_month < 10 then
            set next_month_str = concat('0', next_month);
        else
            set next_month_str = concat('', next_month);
        end if;

        while t_index <= next_month_day do

            if t_index < 10 then
                set next_month_day_str = concat('0', t_index);
            else
                set next_month_day_str = concat('', t_index);
            end if;

            -- 拼接表的日期部分名称
            set table_name_str = concat(next_year, '_', next_month_str, '_', next_month_day_str);
            -- 拼接表名
            set table_name_str = concat('table_user_', table_name_str);

            -- 拼接创建表的sql语句
            set @create_table_sql = concat('create table if not exists ', table_name_str, '(uid int, uname varchar(50), information varchar(50)) collate=utf8mb4_general_ci engine=innodb');

            -- from 后面不能使用局部变量
            prepare create_table_stmt from @create_table_sql;   -- 根据存储在变量 @create_table_sql 中的SQL语句创建一个名为 create_table_stmt 的预编译语句。
            execute create_table_stmt;  -- 执行创建表的操作
            deallocate prepare create_table_stmt;   -- 取消准备之前创建的预编译语句，释放相关资源。

            -- 更新t_index
            set t_index = t_index + 1;
        end while;
    end $$
    delimiter ;

    call proc_test();

存储函数

• 格式

/*
    格式：
    create function 函数名(参数列表) returns 返回值类型
    begin
        函数体
    end;
*/

• 创建存储函数(无参数)

        drop function if exists my_f1;
        delimiter $$
        create function my_f1() returns int READS SQL DATA
        begin
            declare num int default 0;
            select count(*) into num from emp;
            return num;
        end $$
        delimiter ;

        select my_f1(); -- 调用存储函数

• 创建存储函数(有参数)

        drop function if exists my_f2;
        delimiter $$
        create function my_f2(id int) returns varchar(20) READS SQL DATA
        begin
            declare name varchar(20);
            select a.ename into name from emp a where a.empno = id;
            return name;
        end $$
        delimiter ;
        select my_f2(7369); -- 调用存储函数

触发器

• 是特殊的存储过程，不能直接调用，而是由事件触发自动执行
• 只有执行insert(I), update(U), delete(D)操作时才能触发
• 协助应用在数据库端确保数据的完整性，日志记录，数据校验等
• 只支持行级触发，不支持语句级触发
• 关键字：OLD、NEW, OLD表示修改前的记录，NEW表示修改后的记录

/*
    注意事项：
    1. 触发器不能接受参数
    2. 不能对本表进行insert(I), update(U), delete(D)操作，可能会循环触发
    3. 触发器会降低效率，因为它是针对每一行的操作，对表中的每行记录都会执行一次，所以在增删改频繁的表上不要使用触发器
*/

• 创建触发器格式

    /*
        格式：
        -- 只有一个执行语句的触发器
        create trigger 触发器名 before|after 触发事件
        on 表名 for each row
        执行语句;

        -- 多个执行语句的触发器
        create trigger 触发器名 before|after 触发事件
        on 表名 for each row
        begin
            执行语句列表
        end;
    */

• 查看触发器show triggers;
• 数据准备

        create database if not exists mydb_trigger;
        use mydb_trigger;
        -- 用户表
        create table if not exists user(
            uid int primary key,
            uname varchar(50) not null,
            password varchar(50) not null
        );
        -- 用户信息操作日志表
        create table if not exists user_logs(
            id int primary key auto_increment,
            time timestamp,
            log_next varchar(255)
        );

• 需求1：user表中插入数据时，将操作日志记录到user_logs表中

            -- 创建触发器
            create trigger tri_user_insert
                after insert on user for each row
                insert into user_logs(time, log_next) values(now(), '有新用户添加');
            -- 插入数据，测试触发器
            insert into user(uid, uname, password) values(1001, 'zhangsan', '<PASSWORD>');
            -- 删除触发器
            drop trigger if exists tri_user_insert;

• 需求2：user表中更新数据时，将操作日志记录到user_logs表中

            -- 创建触发器
            delimiter $$
            create trigger tri_user_update
                after update on user for each row
                begin
                insert into user_logs(time, log_next) values(now(), '用户信息更新');
                end $$
            delimiter ;
            -- 更新数据，测试触发器
            update user set uname = 'lisi' where uid = 1001;
            -- 删除触发器
            drop trigger if exists tri_user_update;

• NEW和OLD的使用

1. insert 类型的触发器

                -- NEW
                create trigger tri_user_insert_new
                    after insert on user for each row
                    insert into user_logs(time, log_next) values(now(), concat('有新用户添加', NEW.uid, NEW.uname, NEW.password));
                insert into user(uid, uname, password) values(1002, 'wangwu', '<PASSWORD2>');

                drop trigger if exists tri_user_insert_new;

2. update 类型的触发器

                -- OLD
                create trigger tri_user_update_old
                    after update on user for each row
                    insert into user_logs(time, log_next) values(now(), concat_ws(',', '用户信息更新，更新前的信息为：', OLD.uid, OLD.uname, OLD.password));
                update user set uname = 'zhaoliu' where uid = 1002;

                drop trigger if exists tri_user_update_old;
                -- NEW
                create trigger tri_user_update_new
                    after update on user for each row
                    insert into user_logs(time, log_next) values(now(), concat_ws(',', '用户信息更新，更新后的信息为：', NEW.uid, NEW.uname, NEW.password));
                update user set uname = 'lilei' where uid = 1002;

                drop trigger if exists tri_user_update_new;

3. delete 类型的触发器

                -- OLD
                create trigger tri_user_delete_old
                    after delete on user for each row
                    insert into user_logs(time, log_next) values(now(), concat_ws(',', '用户删除，删除的用户信息为：', OLD.uid, OLD.uname, OLD.password));
                delete from user where uid = 1002;

                drop trigger if exists tri_user_delete_old;