DECLARE
--声明变量
SQL_ALLTABLES LONG;
SQL_INSERT LONG;
TYPE THE_CURSOR_TYPE IS REF CURSOR; --定义引用游标的数据类型
CURSOR_D THE_CURSOR_TYPE; --定义游标
DATAUP VARCHAR2(200);
BEGIN
--井筒文档
SQL_ALLTABLES := 'SELECT DISTINCT (TABLE_NAME) FROM USER_TAB_COLUMNS WHERE COLUMN_NAME = ''数据确认状态'' ';
OPEN CURSOR_D FOR SQL_ALLTABLES;
LOOP
FETCH CURSOR_D
INTO DATAUP;
EXIT WHEN CURSOR_D%NOTFOUND;
SQL_INSERT := 'INSERT INTO DB2.TEST (文件名称,资料类别,采集人,单位名称) SELECT 文件名称,资料类别,数据录入人,单位名称 FROM DB2.' || DATAUP || ' WHERE 数据确认状态=''1'' ';
BEGIN
EXECUTE IMMEDIATE SQL_INSERT;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END LOOP;
END;