mysql存储过程实例

delimiter ||
-- after INSERT 触发器
drop TRIGGER if EXISTS insert1after2;
create trigger insert1after2 after INSERT on tab1 for each row
begin
insert into tab2(id, username) values(new.id,new.username);
end||

delimiter ||
-- 插入存储过程
drop procedure if exists insert1;
create procedure insert1()
begin
DECLARE i int;
set i = 0;
WHILE i<100 do
insert into tab1(id,username) values(i,CONCAT('username',i));
set i = i+1;
end while;
end||

delimiter ||
-- 查询存储过程
drop procedure if exists selecttab1;
create procedure selecttab1()
begin
DECLARE i int;
declare username VARCHAR(255);
-- 定义游标
declare selecttab1_cursor CURSOR for select id, username from tab1 ;
-- 打开游标
open selecttab1_cursor;
-- 首次填充数据到变量
FETCH next from selecttab1_cursor into i,username;
-- 循环游标
read_loop: LOOP
select i,username;
-- 再次填充数据到变量
FETCH next from selecttab1_cursor into i,username;
END LOOP;
-- 关闭游标
close selecttab1_cursor;
end||

 

 

 

delimiter ||
-- 存储过程动态执行sql
drop procedure if exists dongtaisql;
create procedure dongtaisql()
begin
declare v_sql varchar(500); -- 需要执行的SQL语句
DECLARE i int;
set i = 0;
WHILE i<10 do
set v_sql= concat('insert into tab2(id,username) values(',i,', \'username',i,'\');');
set @v_sql=v_sql; -- 注意很重要，将连成的字符串赋值给一个变量（可以之前没有定义，但要以@开头）
prepare stmt from @v_sql; -- 预处理需要执行的动态SQL，其中stmt是一个变量
EXECUTE stmt; -- 执行SQL语句
deallocate prepare stmt; -- 释放掉预处理段
set i = i+1;
end while;
end||

 

drop procedure if exists insert_hd_id_gen;
create procedure insert_hd_id_gen(table_name varchar(100))
    begin
        declare v_every_table_name varchar(100);
        declare v_auto_increment int;
        declare v_insert_sql_upper varchar(1000);
        declare v_insert_sql_lower varchar(1000);
        
        declare notfound int default 0;    #定义一个辅助变量用于判断
        
        #定义游标
        declare selecttab1_cursor cursor for select distinct t.table_name, t.auto_increment from information_schema.tables t where t.table_schema = 'bpmapp53' and t.table_name = table_name;
        declare continue handler for sqlstate '02000' set notfound=1;#定义declare continue handler，这个会根据上下文是否有结果判断是否执行set notfound = 1, 必须在游标定义后定义
        
        #打开游标
        open selecttab1_cursor;
            #首次填充数据到变量
            fetch selecttab1_cursor into v_every_table_name, v_auto_increment;
            while notfound<>1 do
                set v_insert_sql_upper = concat('insert into bpmapp53.hd_id_gen values (\'', upper(v_every_table_name), '\', ', v_auto_increment, ');');
                set @v_insert_sql_upper=v_insert_sql_upper;   #注意很重要，将连成成的字符串赋值给一个变量（可以之前没有定义，但要以@开头）
                prepare stmt_insert_sql_upper from @v_insert_sql_upper;  #预处理需要执行的动态sql，其中stmt是一个变量
                execute stmt_insert_sql_upper;      #执行sql语句
                deallocate prepare stmt_insert_sql_upper;     #释放掉预处理段
                
                set v_insert_sql_lower = concat('insert into bpmapp53.hd_id_gen values (\'', lower(v_every_table_name), '\', ', v_auto_increment, ');');
                set @v_insert_sql_lower=v_insert_sql_lower;   #注意很重要，将连成成的字符串赋值给一个变量（可以之前没有定义，但要以@开头）
                prepare stmt_insert_sql_lower from @v_insert_sql_lower;  #预处理需要执行的动态sql，其中stmt是一个变量
                execute stmt_insert_sql_lower;      #执行sql语句
                deallocate prepare stmt_insert_sql_lower;     #释放掉预处理段
                
                #再次填充数据到变量
                fetch selecttab1_cursor into v_every_table_name, v_auto_increment;
            end while;
        #关闭游标
        close selecttab1_cursor;
    end


drop procedure if exists alter_table_auto_increment;
create procedure alter_table_auto_increment(table_name varchar(100))
    begin
        declare v_every_table_name varchar(100);
        declare v_alter_table varchar(1000);
        
        declare notfound int default 0;    #定义一个辅助变量用于判断
        
        #定义游标
        declare selecttab1_cursor cursor for select distinct c.table_name from information_schema.columns c where c.column_name = 'id' and c.data_type = 'bigint' and c.column_key = 'pri' and c.table_schema = 'bpmapp53' and c.table_name = table_name;
        declare continue handler for sqlstate '02000' set notfound=1;#定义declare continue handler，这个会根据上下文是否有结果判断是否执行set notfound = 1, 必须在游标定义后定义
        
        #打开游标
        open selecttab1_cursor;
            #首次填充数据到变量
            fetch next from selecttab1_cursor into v_every_table_name;
            while notfound<>1 do
                set v_alter_table= concat('alter table bpmapp53.', v_every_table_name, ' modify column id bigint(20) not null auto_increment first;');
                set @v_alter_table=v_alter_table;   #注意很重要，将连成成的字符串赋值给一个变量（可以之前没有定义，但要以@开头）
                prepare stmt_select_now_id from @v_alter_table;  #预处理需要执行的动态sql，其中stmt是一个变量
                execute stmt_select_now_id;      #执行sql语句
                deallocate prepare stmt_select_now_id;     #释放掉预处理段
                
                call insert_hd_id_gen(v_every_table_name);
                
                #再次填充数据到变量
                fetch next from selecttab1_cursor into v_every_table_name;
            end while;
        #关闭游标
        close selecttab1_cursor;
    end

drop procedure if exists select_all_table;
create procedure select_all_table()
    begin
        declare table_name varchar(100);
        declare notfound int default 0;    #定义一个辅助变量用于判断
        
        #定义游标
        declare selecttab1_cursor cursor for select distinct t.table_name from information_schema.tables t  where t.table_schema = 'bpmapp53'  and t.auto_increment is null ;
        declare continue handler for sqlstate '02000' set notfound=1;#定义declare continue handler，这个会根据上下文是否有结果判断是否执行set notfound = 1, 必须在游标定义后定义
        
        #清空hd_id_gen表数据
        delete from bpmapp53.hd_id_gen;
        
        #打开游标
        open selecttab1_cursor;
            #首次填充数据到变量
            fetch next from selecttab1_cursor into table_name;
            while notfound<>1 do
                call alter_table_auto_increment(table_name);
                
                #再次填充数据到变量
                fetch next from selecttab1_cursor into table_name;
            end while;
        #关闭游标
        close selecttab1_cursor;
    end