56,687
社区成员
发帖
与我相关
我的任务
分享
CREATE PROCEDURE oldCursor(out oldName TEXT,in domainName varchar(50),in tableName varchar(50))
BEGIN
-- 定义变量
DECLARE tmpName TEXT default '';
DECLARE allName TEXT default '';
DECLARE amount int(11) DEFAULT 0;
DECLARE curl CURSOR FOR select COLUMN_NAME from information_schema.COLUMNS where table_name = 'tbl' and table_schema = 'date';
-- 申明 循环操作 游标
DECLARE continue HANDLER FOR SQLSTATE '02000' set tmpname = null;
OPEN curl;
FETCH curl into tmpName;
WHILE(tmpname is not null)DO
set tmpName = CONCAT(tmpName,',OLD.');
set allName = CONCAT(allName,tmpName);
set amount=amount+1;
FETCH curl into tmpName;
end WHILE;
CLOSE curl;
set allName = substring_index(allName,',OLD.',amount);
set oldName = concat('OLD.',allName);
-- llocate prepare stmt;
END;
CREATE PROCEDURE oldCursor(out oldName TEXT,in domainName varchar(50),in tableName varchar(50))
BEGIN
-- 定义变量
DECLARE tmpName TEXT default '';
DECLARE allName TEXT default '';
DECLARE amount int(11) DEFAULT 0;
-- 两个变量用于接收传入的值 并且在传入的值中处理预编译的sql
@sysName = domainName;
@listName = tableName;
-- 希望处理的效果是select COLUMN_NAME from information_schema.COLUMNS where table_name = 'tbl' and table_schema = 'date';
set @curlName = concat('select COLUMN_NAME from information_schema.COLUMNS where table_name = ''',@listName,''' and table_schema = ''',@sysName,'''');
-- 然后我用预编译的方式线处理curlNamesql语句
prepare stmt from @curlName;
execute stmt;
set @ee = stme;
-- 根据获取的编译好的sql 放到游标中处理-
DECLARE curl CURSOR FOR @ee;
-- 申明 循环操作 游标
DECLARE continue HANDLER FOR SQLSTATE '02000' set tmpname = null;
OPEN curl;
FETCH curl into tmpName;
WHILE(tmpname is not null)DO
set tmpName = CONCAT(tmpName,',OLD.');
set allName = CONCAT(allName,tmpName);
set amount=amount+1;
FETCH curl into tmpName;
end WHILE;
CLOSE curl;
set allName = substring_index(allName,',OLD.',amount);
set oldName = concat('OLD.',allName);
-- 解除资源
DEALLOCATE prepare stmt;
END;