首先描述一下业务处理存储过程功能:删除除系统管理员创建的角色和对应的业务表数据。
[size=medium]-- 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]
执行脚本生成的函数
[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]
分享到:
2015-12-28 16:34
浏览 1816
分类:数据库
评论