mysql_存储过程

1.查看存储过程信息的三个方式

(1)#查看存储过程的 状态

SHOW PROCEDURE STATUS like 'ydfbop_oper_loadincome';

(2)#查询存储过程创建信息

SHOW create PROCEDURE ydfbop_oper_loadincome;

(3)#通过 information_schema.ROUTINES查询存储过程信息

SELECT * from information_schema.ROUTINES  WHERE ROUTINE_NAME ='ydfbop_oper_loadincome';

2.变量的定义
DECLARE date_start DATE;

3.变量的赋值
直接赋值使用SET,可以赋值常量或者表达式

查询结果赋值给变量,要求返回结果必须只有一行

CREATE FUNCTION get_customer_balance(p_customer_id INT,p_effective_date datetime)
    RETURNS DECIMAL(5,2)
    DETERMINISTIC
    READS SQL DATA
BEGIN
    ...
    DECLARE v_payments DECIMAL(5,2);
    ...
    SELECT IFNULL(SUM(payment.amount),0) INTO v_payments FROM payment
    WHERE payment.payment_date <= p_effec_date
    AND payment.customer_id = p_customer_id;
    ...
RETURN v_rentfees + v_overfees - v_payments;
END;

4.定义条件和处理

DECLARE condition_name  CONDITION  FOR condition_value

condition_value: SQLSTATE[value] sqlstate_value | mysql_error_code

示例:主键重复时会退出,不再执行其他语句
CREATE PROCEDURE actor_insert()
BEGIN
    set @x=1;
    INSERT INTO actor(actor_id,first_name,last_name) values (201,'test','201');
    set @x=2;
    INSERT INTO actor(actor_id,first_name,last_name) values (1,'test','1');
    set @x=3;
END;


解决方案:
CREATE PROCEDURE actor_insert()
BEGIN
    DECLARE CONTINUE HANDLER FOR SQLSTATE '2300'
    set @x2=1;
    set @x=1;
    INSERT INTO actor(actor_id,first_name,last_name) values (201,'test','201');
    set @x=2;
    INSERT INTO actor(actor_id,first_name,last_name) values (1,'test','1');
    set @x=3;
END;

如是:

DECLARE CONTINUE HANDLER FOR SQLSTATE '2300'

改写
1.捕获mysql_error_code
DECLARE CONTINUE HANDLER FOR 1062

2.事先定义
DECLARE duplicatekey CONDITION FOR SQLSTATE '23000'
DECLARE CONTINUE HANDLER FOR duplicatekey

3.捕获 SQLEXCEPTION
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION

5.光标
作用:对结果集进行循环处理
使用:
1.声明
    DECLARE cursor_name CURSOR FOR select_statemtn
2.打开
    OPEN cursor_name
3.抓取
    FETCH cursor_name INTO var_name[,var_name]...
4.关闭
    CLOSE cursor_name

示例:
使用光标:对payment表按照行进行循环的处理,按照staff_id值的不同累加amount的值,
                判断循环结束的条件是捕获 not found 的条件,
                当fetch光标找不到下一条记录的时候,就会关闭光标,然后退出过程.
CREATE PROCEDURE payment_stat()
BEGIN
    DECLARE i_staff_id INT;
    DECLARE d_amount DECIMAL(5,2);
    DECLARE cur_payment CURSOR FOR SELECT staff_id,amount FROM payment;
    DECLARE EXIT HANDLER FOR not found CLOSE cur_payment;

    set @x1=0;
    set @x2=0;
    
    OPEN cur_payment;
    
    REPEAT
        FETCH cur_payment INTO i_staff_id, d_amount;
        IF i_staff_id=2 THEN
            set @x1=@x1+d_amount;
        ELSE
            set @x2=@x2+d_amount;
        END IF;
    UNTIL 0 END REPEAT;
    CLOSE cur_payment;
END;

6.流程控制
1.IF

if search_condition then statement_list
ELSEIF search_condition then statement_list
ELSE statement_list
end if;

2.CASE

CASE case_value
    when when_value THEN statement_list
    WHEN when_value THEN statement_list
ELSE statement_lsit
END CASE

CASE i_staff_id
WHEN 2 THEN
        set @x1=@x1+d_amount;
ELSE
        set @x2=@x2+d_amount;
END CASE;


CASE
    WHEN search_condition then statement_list
    WHEN search_condition then statement_list
ELSE statement_list
END CASE

CASE
WHEN i_staff_id=2 THEN
        set @x1=@x1+d_amount;
ELSE
        set @x2=@x2+d_amount;
END CASE;

3.LOOP
BEGIN
    LOOP
        statement_list
    END LOOP
END

4.LEAVE

示例:
CREATE PROCEDURE actor_insert()
BEGIN
    set @x=0;
    ins: loop
        set @x=@x+1;
        IF @x=100 THEN
        LEAVE ins;
    END IF;
    INSERT INTO actor(first_name,last_name) values('test','111');
    END loop ins;
END;


5.ITERATE
须用在循环中,
作用:跳到当前循环的剩下的语句,直接进入到下一轮循环.
示例:当@x变量为偶时,不再执行循环中剩下的语句,而直接进行下一轮的循环.
create PROCEDURE actor_insert()
BEGIN
    set @x=0;
    ins:loop
        set @x=@x+1;
        if @x = 10 THEN
        LEAVE ins;
        ELSEIF `MOD`(@x,2)=0 THEN
        ITERATE ins;
        END IF;
        INSERT INTO actor(actor_id,first_name,last_name) values(@x+200,'test',@x);
    END loop ins;
end;

6.REPEAT
有条件的循环控制语句,当满足条件的时候退出循环
语法:
BEGIN
    REPEAT
        statement_list
    UNTIL search_condition
    END REPEAT

示例:
REPEAT
        FETCH cur_payment INTO i_staff_id, d_amount;
        IF i_staff_id=2 THEN
            set @x1=@x1+d_amount;
        ELSE
            set @x2=@x2+d_amount;
        END IF;
    UNTIL 0 END REPEAT;

7.WHILE
作用于有条件的循环控制语句,当满足条件时,执行相关内容
BIGINT
    while search_condition DO
        statement_list
    END WHILE
END;
七.事件调度器
简单示例:首先创建了myevent调度事件,然后执行更新操作,起始执行时间为调度器创建时间,后续在起始时间的基础上每隔1小时触发一次.
create event myevent
on schedule at CURRENT_TIMESTAMP + INTERVAL 1 HOUR
DO
update myschema.mytable set mycol=mycol+1;

myevent是事件名
on SCHEDULE 子句指定 事件在何时执行及执行频次
do 子句指定要执行的具体操作或事件

示例2:
每隔5秒向表test中插入一条记录
CREATE EVENT test_event_1
ON SCHEDULE EVERY 5 SECOND
DO
insert into test.test(id1,create_time) values('test',NOW());

查看调度器状态:
SHOW EVENTS \G;

查看事件调度器状态
show VARIABLES LIKE '%scheduler%';

打开事件调度器状态
SET GLOBAL event_scheduler =1;

示例3:每隔一分钟,清空一次test表:
CREATE EVENT trunc_test
ON SCHEDULE EVERY 1 MINUTE
DO TRUNCATE TABLE test;

禁用event
alter event test_event_1 DISABLE;

删除event
drop event test_event_1;