背景介绍
需求:在数据库中有些结果表保存在JW_DS_BASE_INFO
表的DS_TABLE_CASE
字段中,现在需要将这些表取出,根据这些表批量创建视图。
实现
CREATE OR REPLACE PROCEDURE "CREATE_MULTIPLE_VIEW_PROCEDURE" IS
PRAGMA AUTONOMOUS_TRANSACTION;
CURSOR C_CURSOR IS
SELECT SUBSTR(DS_TABLE_CASE, 4, LENGTH(DS_TABLE_CASE) - 8) || '_V' AS VIEW_NAME, --有时表名过长导致视图名过长因此截取一下
DS_ID,
DS_TABLE_CASE
FROM JW_DS_BASE_INFO T
WHERE T.DS_TYPE IN ('01', '02', '06');
V_VIEW_NAME VARCHAR2(1000); --待创建的视图名
V_DS_ID VARCHAR2(1000);
V_CASE_TABLE_NAME VARCHAR2(1000);
V_VIEW_SQL VARCHAR2(32767);
V_TABLE_COUNT NUMBER;
BEGIN
OPEN C_CURSOR;
FETCH C_CURSOR
INTO V_VIEW_NAME, V_DS_ID, V_CASE_TABLE_NAME;
DBMS_OUTPUT.ENABLE(1000000);
WHILE C_CURSOR%FOUND LOOP
SELECT COUNT(*)
INTO V_TABLE_COUNT
FROM USER_TABLES
WHERE TABLE_NAME = V_CASE_TABLE_NAME;
IF V_TABLE_COUNT > 0 THEN
V_VIEW_SQL := 'create or replace view ' || V_VIEW_NAME || ' as select * from ' || V_CASE_TABLE_NAME;
DBMS_OUTPUT.PUT_LINE('创建语句:' || V_VIEW_SQL);
EXECUTE IMMEDIATE V_VIEW_SQL;
COMMIT;
ELSE
DBMS_OUTPUT.PUT_LINE('结果表:' || V_CASE_TABLE_NAME || '不存在'); --如果表不存在无法创建视图
END IF;
FETCH C_CURSOR
INTO V_VIEW_NAME, V_DS_ID, V_CASE_TABLE_NAME; --提取下一条游标记录
END LOOP;
CLOSE C_CURSOR;
END;