楼主在开发工程中遇到这样一个需求,要求把数据在点击完成以后,根据表名和数据id'把数据封存起来,如果存在表就去插入如果不存再就先创建表再去更新,楼主写了一个存储过程
drop procedure if exists p_createtb;
CREATE PROCEDURE p_createtb(IN patientUniqueId varchar(32),IN topicId INT)
BEGIN
declare tbname varchar(100);
declare tbname1 varchar(100);
declare i_count int default 0;
declare done int default false;
declare cur cursor for select topic_copy_name from dcfphryun.tb_research_topic_copy_table ;
declare continue HANDLER for not found set done = true;
set tbname1="";
set i_count = 0;
open cur;
read_loop:loop
fetch cur into tbname;
if done then
leave read_loop;
end if;
set tbname1=concat(topicId,"_",substring(tbname,locate('_',tbname,4)+1));
select count(*) into i_count from `INFORMATION_SCHEMA`.`TABLES` where `TABLE_SCHEMA`='dcfphr_topic' and `TABLE_NAME`=tbname1;
if i_count<=0 then
set @sql = concat("CREATE TABLE dcfphr_topic.",tbname1," as","(select * from `dcfphryun`.",tbname," where patient_unique_id = '",patientUniqueId,"')");
prepare stmt from @sql;
execute stmt;
end if;
if i_count>=1 then
set @sql = concat("insert into dcfphr_topic.",tbname1,"(select * from `dcfphryun`.",tbname," where patient_unique_id = '",patientUniqueId,"')");
prepare stmt from @sql;
execute stmt;
end if;
end loop;
close cur;
END;