Mysql-存储过程

1、简单的存储过程

-- 设置结束符号
delimiter $$

-- 创建存储，命名为hello_world，可以传参，就跟封装方法一样
CREATE PROCEDURE hello_world()
BEGIN
    SELECT 'hello world';
END $$

-- 调用
CALL hello_world()

2、设置变量的存储过程

delimiter $$
CREATE PROCEDURE sp_var01()
BEGIN
    -- DECLARE对变量描述，设置类型
    DECLARE var01 VARCHAR(32) DEFAULT 'SF';
    SELECT var01;  -- SF
    -- 设置变量初始值，该变量为局部变量，只作用域begin里
    SET var01 = 100;
    SELECT var01; -- 100
END$$

-- 删除存储
DROP PROCEDURE sp_var01;

CALL sp_var01();

3、传参的存储过程

delimiter //
CREATE PROCEDURE sp_var_into()
BEGIN
    -- @xx用户变量赋值
    SELECT  d.cname into @clname FROM t_class d WHERE d.cno=2;
END//

CALL  sp_var_into();
SELECT @clname;
-- ==================================================================

delimiter //

/*
IN:入参
OUT:出参
age：形参名称
INT:参数类型，如果是char和varchar必须加上范围（）
*/
CREATE PROCEDURE sp_param(IN age INT)
BEGIN
    set @use_age = age;  -- 相当于实例属性
END//

CALL sp_param(998);
SELECT @use_age;
-- ==================================================================

delimiter //
/*
t_class表中：传入room，返回一个cname
*/
-- DESC t_class;
CREATE PROCEDURE sp_param02(IN croom CHAR(10),OUT clname VARCHAR(10))
BEGIN
    -- 将t_class表里的room传参给入参的croom，然后结果赋值给变量clname
    SELECT cname INTO clname from t_class WHERE room=croom;
END//
-- DROP PROCEDURE sp_param02;
CALL sp_param02('r101',@clname);
-- CALL sp_param02('r102',clname);  -- 1054 - Unknown column 'clname' in 'field list'
SELECT @clname;

4、if判断的存储过程

/*
需求：
入职年限<=38是新手 >38并且<=40老员工 其它元老
*/
SELECT * FROM t_student;
-- 查询张三至今的年龄:函数TIMESTAMPDIFF(单位，起，始值)
SELECT TIMESTAMPDIFF(YEAR,e.hiredate,NOW()) FROM t_student e WHERE e.sname = "张三";
delimiter //

CREATE PROCEDURE sp_hire_if()
BEGIN
    -- 声明一个变量接受结果是什么员工
    DECLARE result VARCHAR(32);
    -- 将年龄传参给year
    DECLARE years int;
    SELECT TIMESTAMPDIFF(YEAR,e.hiredate,NOW()) INTO years FROM t_student e WHERE e.sname = "张三";
    
    -- 判断
    IF years >40 THEN
    set result = '元老';
    ELSEIF years > 38 THEN
    set result = '老员工';
    ELSE
    set result = '新手';
    END IF;

  SELECT result;
    SELECT  years;
END//
-- DROP PROCEDURE sp_hire_if;
-- 调用call
CALL sp_hire_if();

5、while循环的存储过程

while单循环

delimiter //
CREATE PROCEDURE sp_flow_while()
BEGIN
    DECLARE c_index INT DEFAULT 1;
    -- 收集结果字符串
    DECLARE result_str VARCHAR(256) DEFAULT '1';
    
    WHILE c_index<10 DO
    set c_index = c_index + 1;
    set result_str = CONCAT(result_str,',',c_index);
    END WHILE;
    SELECT result_str;
END //
CALL sp_flow_while();

SELECT DATE_ADD(now(),INTERVAL 1 MONTH);  -- 获取一个月后的日期
SELECT LAST_DAY(NOW()); -- 获取日期的最后一天
SELECT YEAR(LAST_DAY(NOW()));  -- 获取年
SELECT MONTH(LAST_DAY(NOW()));  -- 获取月
SELECT DAYOFMONTH(LAST_DAY(DATE_ADD(now(),INTERVAL 1 MONTH)));  -- 下个月月最后一天是几号

while嵌套循环

delimiter //
CREATE PROCEDURE add_team()
BEGIN
    DECLARE team_num INT DEFAULT 1;
    DECLARE org_num INT DEFAULT 711;
    DECLARE create_by INT DEFAULT 4857;
    -- 设置外层循环次数，机构id为边界
    WHILE org_num < 769 DO
        WHILE team_num < 5001 DO
        INSERT INTO team(team_name,hospital_id,create_by) 
        VALUES(CONCAT("班组",team_num,org_num),org_num,create_by);
        set team_num = team_num + 1;
        -- 内层循环第一个机构创建6个班组后，退出循环
        END WHILE;
        -- 机构id增加，对应机构创建者id增加
        SET org_num =org_num + 3;
        SET create_by = create_by + 15;
        -- 此时必须重置内层循环次数的变量，否则team_num直接从6开始，就无法再insert数据
        SET team_num = 1;
    END WHILE;
END //
CALL add_team();

6、游标：cursor

• 在 MySQL 中，存储过程或函数中的查询有时会返回多条记录，而使用简单的 SELECT 语句，没有办法得到第一行、下一行或前十行的数据，这时可以使用游标来逐条读取查询结果集中的记录。游标在部分资料中也被称为光标。
• 关系数据库管理系统实质是面向集合的，在 MySQL 中并没有一种描述表中单一记录的表达形式，除非使用 WHERE 子句来限制只有一条记录被选中。所以有时我们必须借助于游标来进行单条记录的数据处理。
• 一般通过游标定位到结果集的某一行进行数据修改

delimiter //
CREATE PROCEDURE fetch_test()
BEGIN
 DECLARE tellph INT;
 DECLARE num_id INT;
 DECLARE user_id VARCHAR(22);
 -- 定义一个退出flag
 DECLARE has_data INT DEFAULT 1;
 
 -- 声明一个游标名称供那个查询使用
 DECLARE user_result CURSOR FOR SELECT * FROM t_user;
 -- mysql自带的预警退出机制
 DECLARE EXIT HANDLER FOR NOT FOUND set has_data=0;
 
 -- 打开游标
 OPEN user_result;
 REPEAT
    FETCH user_result INTO num_id,tellph,user_id;
    SELECT CONCAT('cid=',num_id,'cellphone=',tellph,'userid=',user_id);
    UNTIL has_data =0 
    END REPEAT;

    -- 关闭游标
    CLOSE user_result;
END //
DROP PROCEDURE fetch_test;
CALL fetch_test();

repeat语法

• 语法： REPEAT doSomething UNTIL condition END REPEAT ;
• 说明：doSomething 先执行相关操作 ，再判断是否满足条件，满足，结束循环。(和WHILE循环相反) --- 先执行，后判断