MySQL存储过程备忘 – Interact^Influence

突然要弄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);

1 2	`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

1	`call fix_data_trash_magic()//`

另外还有常用的几个命令

1 2	`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

1 2	`show triggers//` `drop trigger insert_after_addr//`

1	`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

Leave a Reply Cancel reply