Oracle存储过程动态批量创建视图

背景介绍

需求：在数据库中有些结果表保存在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;