Mysql 存储过程执行动态sql

自信，装载明天

于 2024-07-29 18:21:00 发布

阅读量116

点赞数 2

文章标签： mysql sql 数据库

本文链接：https://blog.csdn.net/weixin_52455398/article/details/140777915

版权

CREATE DEFINER=`root`@`%` PROCEDURE `cscb`.`update_follow_uid_tables_and_delete_tables`(OUT `msgno` int(11), OUT `errtext` varchar(4000))
begin
	
    DECLARE v_table_name VARCHAR(255);
    DECLARE not_found INT DEFAULT 0;
    declare v_count INT DEFAULT 0;
    DECLARE cur CURSOR FOR  -- 声明游标
        SELECT DISTINCT table_name as  table_name
        FROM information_schema.columns 
        WHERE column_name = 'follow_uid' AND table_name NOT LIKE '%bak';  -- 此查询语句是查询的包含此follow_uid字段的表
 Declare continue handler for not found set not_found = 1; -- 游标中没有结果集 执行此语句
   Declare exit handler for Sqlexception begin
	   GET DIAGNOSTICS CONDITION 1 
	   errtext = MESSAGE_TEXT;
	   set msgno = -1,errtext=concat(errtext,'update_follow_uid_tables_and_delete_tables');
	  Rollback; 
    End;
      
    OPEN cur; -- 打开游标
        FETCH cur INTO v_table_name; -- 取出数据
      
		   while not_found=0
		 do
        -- 更新操作 准备动态sql
	         if LOCATE('contact', v_table_name)>0 then
				SET @create_stmt= CONCAT('CREATE table ', v_table_name, '_bak_2024726 as ',' select * from ',v_table_name,'  where follow_uid=32;'); 
	         end if;
			PREPARE create_stmt FROM @create_stmt; -- 初始化一个sql 语句
	        EXECUTE create_stmt; -- 执行sql语句
	        DEALLOCATE PREPARE create_stmt; -- 释放资源
	    FETCH cur INTO v_table_name;   
	    End while;   
    CLOSE cur;
       
set msgno=0;
set errtext='ok';
       
END