mysql 存储过程 after_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||

1 drop procedure if existsinsert_hd_id_gen;2 create procedure insert_hd_id_gen(table_name varchar(100))3 begin

4 declare v_every_table_name varchar(100);5 declare v_auto_increment int;6 declare v_insert_sql_upper varchar(1000);7 declare v_insert_sql_lower varchar(1000);8

9 declare notfound int default 0; #定义一个辅助变量用于判断10

11 #定义游标12 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;13 declare continue handler for sqlstate '02000' set notfound=1;#定义declare continue handler，这个会根据上下文是否有结果判断是否执行set notfound = 1, 必须在游标定义后定义14

15 #打开游标16 openselecttab1_cursor;17 #首次填充数据到变量18 fetch selecttab1_cursor intov_every_table_name, v_auto_increment;19 while notfound<>1do20 set v_insert_sql_upper = concat('insert into bpmapp53.hd_id_gen values (\'', upper(v_every_table_name),'\',', v_auto_increment, ');');21 set @v_insert_sql_upper=v_insert_sql_upper; #注意很重要，将连成成的字符串赋值给一个变量(可以之前没有定义，但要以@开头)22 prepare stmt_insert_sql_upper from @v_insert_sql_upper; #预处理需要执行的动态sql，其中stmt是一个变量23 executestmt_insert_sql_upper; #执行sql语句24 deallocate preparestmt_insert_sql_upper; #释放掉预处理段25

26 set v_insert_sql_lower = concat('insert into bpmapp53.hd_id_gen values (\'', lower(v_every_table_name),'\',', v_auto_increment, ');');27 set @v_insert_sql_lower=v_insert_sql_lower; #注意很重要，将连成成的字符串赋值给一个变量(可以之前没有定义，但要以@开头)28 prepare stmt_insert_sql_lower from @v_insert_sql_lower; #预处理需要执行的动态sql，其中stmt是一个变量29 executestmt_insert_sql_lower; #执行sql语句30 deallocate preparestmt_insert_sql_lower; #释放掉预处理段31

32 #再次填充数据到变量33 fetch selecttab1_cursor intov_every_table_name, v_auto_increment;34 end while;35 #关闭游标36 closeselecttab1_cursor;37 end

38

39

40 drop procedure if existsalter_table_auto_increment;41 create procedure alter_table_auto_increment(table_name varchar(100))42 begin

43 declare v_every_table_name varchar(100);44 declare v_alter_table varchar(1000);45

46 declare notfound int default 0; #定义一个辅助变量用于判断47

48 #定义游标49 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;50 declare continue handler for sqlstate '02000' set notfound=1;#定义declare continue handler，这个会根据上下文是否有结果判断是否执行set notfound = 1, 必须在游标定义后定义51

52 #打开游标53 openselecttab1_cursor;54 #首次填充数据到变量55 fetch next from selecttab1_cursor intov_every_table_name;56 while notfound<>1do57 set v_alter_table= concat('alter table bpmapp53.', v_every_table_name, 'modify column id bigint(20) not null auto_increment first;');58 set @v_alter_table=v_alter_table; #注意很重要，将连成成的字符串赋值给一个变量(可以之前没有定义，但要以@开头)59 prepare stmt_select_now_id from @v_alter_table; #预处理需要执行的动态sql，其中stmt是一个变量60 executestmt_select_now_id; #执行sql语句61 deallocate preparestmt_select_now_id; #释放掉预处理段62

63 call insert_hd_id_gen(v_every_table_name);64

65 #再次填充数据到变量66 fetch next from selecttab1_cursor intov_every_table_name;67 end while;68 #关闭游标69 closeselecttab1_cursor;70 end

71

72 drop procedure if existsselect_all_table;73 create procedureselect_all_table()74 begin

75 declare table_name varchar(100);76 declare notfound int default 0; #定义一个辅助变量用于判断77

78 #定义游标79 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;80 declare continue handler for sqlstate '02000' set notfound=1;#定义declare continue handler，这个会根据上下文是否有结果判断是否执行set notfound = 1, 必须在游标定义后定义81

82 #清空hd_id_gen表数据83 delete frombpmapp53.hd_id_gen;84

85 #打开游标86 openselecttab1_cursor;87 #首次填充数据到变量88 fetch next from selecttab1_cursor intotable_name;89 while notfound<>1do90 call alter_table_auto_increment(table_name);91

92 #再次填充数据到变量93 fetch next from selecttab1_cursor intotable_name;94 end while;95 #关闭游标96 closeselecttab1_cursor;97 end