MYSQL经过SQL脚本创建存储过程(游标删除多表数据)

--  CALL P_INTSYS('%_10006%','10006');drop procedure if exists wmscloud_test.P_INTSYS;CREATE PROCEDURE wmscloud_test.P_INTSYS(IN likeTableName VARCHAR(30),IN esCorCode VARCHAR(30))BEGIN   -- 需要定义接收游标数据的变量   DECLARE table_ VARCHAR(50);  -- 遍历数据结束标志  DECLARE done INT DEFAULT FALSE;  -- 游标  DECLARE cur CURSOR FOR SELECT TABLE_NAME FROM (SELECT TABLE_NAME from information_schema.tables WHERE TABLE_NAME LIKE likeTableName ) allTables;  -- 将结束标志绑定到游标  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;  -- 打开游标  OPEN cur;   -- 开始循环  read_loop: LOOP  -- 提取游标里的数据，这里只有一个，多个的话也一样；  FETCH cur INTO table_;  -- 声明结束的时候  IF done THEN    LEAVE read_loop;  END IF;  -- 这里做你想做的循环的事件    SET @delete_table=CONCAT(' DELETE FROM ',table_,';');    PREPARE delete_table FROM @delete_table;    EXECUTE delete_table;    END LOOP;  -- 关闭游标  CLOSE cur;  -- 删除,除去系统管理员创建的外其余用户和角色  SET @delete_table=CONCAT(' DELETE FROM es_user WHERE CREATOR!=1 and ES_COR_CODE=',esCorCode);  PREPARE delete_table FROM @delete_table;  EXECUTE delete_table;  SET @delete_table=CONCAT(' DELETE FROM es_role WHERE CREATOR!=1 and ES_COR_CODE=',esCorCode);  PREPARE delete_table FROM @delete_table;  EXECUTE delete_table;END

[size=medium]BEGIN   -- 需要定义接收游标数据的变量   DECLARE table_ VARCHAR(50);  -- 遍历数据结束标志  DECLARE done INT DEFAULT FALSE;  -- 游标  DECLARE cur CURSOR FOR SELECT TABLE_NAME FROM (SELECT TABLE_NAME from information_schema.tables WHERE TABLE_NAME LIKE likeTableName ) allTables;  -- 将结束标志绑定到游标  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;  -- 打开游标  OPEN cur;   -- 开始循环  read_loop: LOOP  -- 提取游标里的数据，这里只有一个，多个的话也一样；  FETCH cur INTO table_;  -- 声明结束的时候  IF done THEN    LEAVE read_loop;  END IF;  -- 这里做你想做的循环的事件    SET @delete_table=CONCAT(' DELETE FROM ',table_,';');    PREPARE delete_table FROM @delete_table;    EXECUTE delete_table;    END LOOP;  -- 关闭游标  CLOSE cur;  -- 删除,除去系统管理员创建的外其余用户和角色  SET @delete_table=CONCAT(' DELETE FROM es_user WHERE CREATOR!=1 and ES_COR_CODE=',esCorCode);  PREPARE delete_table FROM @delete_table;  EXECUTE delete_table;  SET @delete_table=CONCAT(' DELETE FROM es_role WHERE CREATOR!=1 and ES_COR_CODE=',esCorCode);  PREPARE delete_table FROM @delete_table;  EXECUTE delete_table;END[/size]