MySQL存储过程备忘

突然要弄MySQL存储过程
好久没有怎么接触过数据库存储过程了，都忘了，记在这里

create table email (id int auto_increment primary key, name text, content text, int size);
create table email_trash (id int auto_increment primary key, name text, content text, int size);

create table attachment (id int auto_increment primary key, email_id int, url text);
create table attachment_trash (id int auto_increment primary key, email_id int, url text);

delimiter //
drop procedure if exists fix_data_trash_magic //

CREATE PROCEDURE `fix_data_trash_magic`()
BEGIN

    DECLARE done INT DEFAULT 0;

    DECLARE _amount INT DEFAULT 0;
    DECLARE _size INT;
    DECLARE _id INT;
    DECLARE _newid LONG;

    DECLARE email_loop CURSOR FOR select id, size from email_trash where name = 'jemmy';

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN email_loop;
        REPEAT
        FETCH email_loop INTO _id, _size;
            IF NOT done THEN
                insert into email(name, content, size) select name,content,size from email_trash where id = _id;

                select last_insert_id() into _newid;

                insert into attachment(email_id, url) select _newid,url from attachment_trash where email_id = _id;
                
                SET _amount = _size + _amount;
            END IF;
        UNTIL done END REPEAT;
    CLOSE email_loop;
END

call fix_data_trash_magic()//

另外还有常用的几个命令

mysql> show procedure status;
mysql> show create procedure procedure_name; // SHOW THE CREATING SQL OF THE SP

包含异常处理，保存点的存储过程

DELIMITER ;;
CREATE PROCEDURE ps_with_exception_etc(in_domain varchar(50), in_account varchar(32), in_passwd varchar(32), OUT out_no int, OUT out_msg varchar(50))
BEGIN
    DECLARE s_fullid varchar(100);

    DECLARE EXIT HANDLER FOR SQLSTATE '23000'
    BEGIN
        SET out_no = -1;
        SET @prc_work_error = 'Repeated key';
        ROLLBACK TO sp_01;
    END;

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        SET out_no = -1;
        SET @prc_work_error = 'Unknown error';
        ROLLBACK TO sp_01;
    END;

    -- 声明之类的都应该放在前面

    SET AUTOCOMMIT = 0;
        SELECT concat(in_account, '@', in_domain) INTO s_fullid;
        START TRANSACTION;
        SAVEPOINT sp_01;
        -- DO OO HERE
        -- DO XX HERE
        COMMIT;
    SET out_no = 1;
END
;;
DELIMITER ;

存储过程中的动态SQL
这里只是演示动态SQL的写法，要是真这么写太浪费了，直接可以写出来的SQL就不需要动态SQL

DELIMITER ;;
CREATE PROCEDURE ps_with_dynamic_sql(in_domain varchar(50), in_account varchar(32), OUT out_no int, OUT out_msg varchar(50))
BEGIN
    DECLARE fullid varchar(100);

    DECLARE DROP_USER_TABLEK varchar(500);
    DECLARE DROP_USER_MAILS varchar(500);

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        SET out_no = -1;
        SET @prc_work_error = 'Unknown error';
        ROLLBACK TO sp_01;
    END;

    SET AUTOCOMMIT = 0;
    START TRANSACTION;
    SAVEPOINT sp_01;

        SET @MyQuery = concat('DELETE FROM tablek WHERE uuid = ', in_account);
        PREPARE DROP_USER_TABLEK from @MyQuery;

        SET @MyQuery = concat('DELETE FROM mails WHERE uuid = ', in_account);
        PREPARE DROP_USER_MAILS from @MyQuery;

        EXECUTE DROP_USER_TABLEK;
        EXECUTE DROP_USER_MAILS;
    COMMIT;
    SET out_no = 1;
END
;;
DELIMITER ;

还有个简单的

CREATE PROCEDURE `insert_data`(in item integer)
BEGIN
    DECLARE counter INT;
    SET counter = item;
    WHILE counter >= 1 DO
        // DO YOUR BIZ HERE
        SET counter = counter - 1;
    END WHILE;
END

另外在存储过程中声明变量，游标，处理器是有一定的顺序的，否则会报语法错误

触发器

create trigger insert_after_addr
after insert on addr
for each row
begin
set @aid=new.id;
insert into data(addr_id, type, value) values (@aid, 5, 'hello trigger');
end

show triggers//
drop trigger insert_after_addr//

alter table data modify id int(22) not null auto_increment

CREATE trigger insert_before_tablek
    before
        insert on tablek
    for each row
        begin
            set new.status = 25;
        end