存储过程动态创建试图

CREATE OR REPLACE PROCEDURE CREATE_VIEW_P
/********************************************************************************
  中文名称：根据数据字典动态创建视图
  功能描述：根据指定的数据库用户、表名信息从数据字典中提取字段名及字段描述，
            并按照指定的视图名动态创建视图
  客    户：AEPCS5
  设 计 人：ZHANGHL
  编 码 人：ZHANGHL
  编码日期：2010-06-20
  修改记录：
  ******************************************************************************/
(I_USER             IN VARCHAR2, --用户名
 I_TABLE_NAME       IN VARCHAR2, --表名字
 I_ALIAS_TABLE_NAME IN VARCHAR2, --表的别名(视图中的表别名)
 I_GEN_VIEW_NAME    IN VARCHAR2, --生成的视图名
 O__RETURN_INT      OUT NUMBER, --返回值：0失败，1成功
 O_RETURN_STR       OUT VARCHAR2, --返回字符串，操作成功或失败
 O_RETURN_CLOB      OUT VARCHAR2) IS
  V_USER            VARCHAR2(4000); --用户名，需要进行大写转换
  V_TABLE_NAME      VARCHAR2(4000); --表名字，需要进行大写转换
  V_MAX_COL_ID      VARCHAR2(4000);
  V_GEN_VIEW_SQL    VARCHAR2(4000);
  V_COMMENTS        VARCHAR2(1000); --记载处理后的字段描述
  V_SPACE_MARK      VARCHAR2(30); --字段名和字段描述之间的间隔符(--)
  V_CREATE_TIME_STR VARCHAR2(30); --系统时间
  V_REMARKS         VARCHAR2(4000); --注释
  V_GRANT_SQL       VARCHAR2(100); --授权语句
  V_CREATE_SYN      VARCHAR2(100); --创建同义词语句
  V_LOC_FLAG        NUMBER(10) := 0; --记录异常位置的标记符
  --V_REMARKS作为动态的添加每个视图的说明，可以根据自己的需要进行修改
  --定义读取表中定义的列的信息的游标:(列名字，列注释，列编号)
  CURSOR CUR_TAB_COLS IS
    SELECT ACC.COLUMN_NAME, --列名字
           ACC.COMMENTS, --列注释
           ALC.COLUMN_ID --列编号
      FROM ALL_COL_COMMENTS ACC
     INNER JOIN ALL_TAB_COLS ALC
        ON ACC.COLUMN_NAME = ALC.COLUMN_NAME
       AND ACC.OWNER = ALC.OWNER
       AND ACC.TABLE_NAME = ALC.TABLE_NAME
     WHERE ACC.OWNER = V_USER
       AND ACC.TABLE_NAME = V_TABLE_NAME
     ORDER BY ALC.COLUMN_ID;
BEGIN
  --备注信息的初始化
  SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS')
    INTO V_CREATE_TIME_STR
    FROM DUAL;
  V_REMARKS := '/********************************************************************************' ||
               CHR(10) || '  功能描述：' || I_TABLE_NAME || '表的视图' || CHR(10) ||
               '  编 码 人：' || CHR(10) || '  编码日期：' || V_CREATE_TIME_STR ||
               CHR(10) || '  修改记录：' || CHR(10) ||
               '******************************************************************************/';
  --输入的参数验证
  IF I_USER IS NULL THEN
    O__RETURN_INT := 0;
    O_RETURN_STR  := '传入的参数:用户名不允许为空';
    RETURN;
  END IF;
  IF I_TABLE_NAME IS NULL THEN
    O__RETURN_INT := 0;
    O_RETURN_STR  := '传入的参数:表名字不允许为空';
    RETURN;
  END IF;
  IF I_ALIAS_TABLE_NAME IS NULL THEN
    O__RETURN_INT := 0;
    O_RETURN_STR  := '传入的参数:表的别名不允许为空';
    RETURN;
  END IF;
  IF I_GEN_VIEW_NAME IS NULL THEN
    O__RETURN_INT := 0;
    O_RETURN_STR  := '传入的参数:生成的视图名字不允许为空';
    RETURN;
  END IF;
  --对用户名和表名、表的别名进行大写转换
  V_USER       := UPPER(I_USER);
  V_TABLE_NAME := UPPER(I_TABLE_NAME);


  --判断输入的表或者用户名是否正确
  --(可以通过查询列的最大编号实现，顺便也查询除了列的最大编号，供后面使用)
  SELECT MAX(ATC.COLUMN_ID)
    INTO V_MAX_COL_ID
    FROM ALL_TAB_COLS ATC
   WHERE ATC.OWNER = V_USER
     AND ATC.TABLE_NAME = V_TABLE_NAME;
  --验证列的最大编号
  IF V_MAX_COL_ID IS NULL THEN
    O__RETURN_INT := 0;
    O_RETURN_STR  := '传入的参数:用户名或者表名有误';
    RETURN;
  END IF;
  --进行创建视图的语句拼写
  V_GEN_VIEW_SQL := 'CREATE OR REPLACE VIEW ' || I_USER || '.' ||
                    I_GEN_VIEW_NAME || ' AS ' || CHR(10) || 'SELECT ' ||
                    CHR(10) || V_REMARKS || CHR(10) || '       ';
  --打开游标循环处理列的注释信息
  FOR V_CUR_TAB_COLS IN CUR_TAB_COLS LOOP


    --处理注释信息
    --(假如注释过多，可以以'^'结束，暂时注释掉)对注释中的'^'信息进行处理，只取出'^'之前的信息
    /**
    IF INSTR(V_CUR_TAB_COLS.COMMENTS, '^') != 0 THEN
      V_COMMENTS := TRIM(SUBSTR(V_CUR_TAB_COLS.COMMENTS,
                                1,
                                INSTR(V_CUR_TAB_COLS.COMMENTS, '^') - 1));
    ELSE
      V_COMMENTS := TRIM(V_CUR_TAB_COLS.COMMENTS);
    END IF;
    **/


    --删除空格
    V_COMMENTS := TRIM(V_CUR_TAB_COLS.COMMENTS);


    --分隔符的初始化，用于拼接注释
    IF V_COMMENTS IS NULL THEN
      V_SPACE_MARK := '';
    ELSE
      V_SPACE_MARK := ' --';
    END IF;
    --对于表中的最后一个字段，不能添加','分隔:
    IF V_CUR_TAB_COLS.COLUMN_ID != V_MAX_COL_ID THEN
      V_SPACE_MARK := ',' || V_SPACE_MARK;
    END IF;
    --进行语句的拼写
    IF V_CUR_TAB_COLS.COLUMN_ID = V_MAX_COL_ID THEN
      V_GEN_VIEW_SQL := V_GEN_VIEW_SQL || I_ALIAS_TABLE_NAME || '.' ||
                        V_CUR_TAB_COLS.COLUMN_NAME || V_SPACE_MARK ||
                        V_COMMENTS || CHR(10);
    ELSE
      V_GEN_VIEW_SQL := V_GEN_VIEW_SQL || I_ALIAS_TABLE_NAME || '.' ||
                        V_CUR_TAB_COLS.COLUMN_NAME || V_SPACE_MARK ||
                        V_COMMENTS || CHR(10) || '       ';
    END IF;
  END LOOP;
  --拼写FROM子句
  V_GEN_VIEW_SQL := V_GEN_VIEW_SQL || ' FROM ' || V_TABLE_NAME || ' ' ||
                    I_ALIAS_TABLE_NAME;


  --存储创建语句(存储到表中)
  --INSERT INTO TEST VALUES (O_RETURN_CLOB);
  --COMMIT;
  --执行语句
  V_LOC_FLAG    := 1;
  O_RETURN_CLOB := V_GEN_VIEW_SQL;
  EXECUTE IMMEDIATE V_GEN_VIEW_SQL;


/**
  --执行表和视图的授权语句
  V_GRANT_SQL := 'GRANT ALL ON ' || V_TABLE_NAME ||
                 ' TO APPS WITH GRANT OPTION';
  V_LOC_FLAG  := 2;
  --EXECUTE IMMEDIATE V_GRANT_SQL;


  V_GRANT_SQL := 'GRANT ALL ON ' || I_GEN_VIEW_NAME ||
                 ' TO APPS WITH GRANT OPTION';
  V_LOC_FLAG  := 3;
  --EXECUTE IMMEDIATE V_GRANT_SQL;


  --执行创建同义词的语句
  V_CREATE_SYN := 'CREATE  OR REPLACE SYNONYM APPS.' || V_TABLE_NAME ||


                  ' FOR ' || I_USER || '.' || V_TABLE_NAME;
  V_LOC_FLAG   := 4;
  --EXECUTE IMMEDIATE V_CREATE_SYN;


  V_CREATE_SYN := 'CREATE OR REPLACE SYNONYM APPS.' || I_GEN_VIEW_NAME ||
                  ' FOR ' || I_USER || '.' || I_GEN_VIEW_NAME;
  V_LOC_FLAG   := 5;
  --EXECUTE IMMEDIATE V_CREATE_SYN;


  O__RETURN_INT := 1;
  O_RETURN_STR  := '创建视图成功,表和视图向APPS授权和创建同义词成功';
  **/
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    --异常信息判断
    O__RETURN_INT := 0;
    IF V_LOC_FLAG = 1 THEN
      O_RETURN_STR := '创建视图失败、授权、建立同义词均失败，失败信息--' || SQLCODE || '--' ||
                      SQLERRM;
    ELSIF V_LOC_FLAG = 2 THEN
      O_RETURN_STR := '创建视图成功、表授权失败、建立同义词均失败，失败信息--' || SQLCODE || '--' ||
                      SQLERRM;
    ELSIF V_LOC_FLAG = 3 THEN
      O_RETURN_STR := '创建视图成功、表授权成功、视图授权失败、同义词建立失败，失败信息--' || SQLCODE || '--' ||
                      SQLERRM;
    ELSIF V_LOC_FLAG = 4 THEN
      O_RETURN_STR := '创建视图成功、表和视图授权成功，表同义词建立失败、视图建立同义词失败，失败信息--' ||
                      SQLCODE || '--' || SQLERRM;
    ELSIF V_LOC_FLAG = 5 THEN
      O_RETURN_STR := '创建视图成功、授权成功、表同义词建立成功、视图建立同义词失败，失败信息--' || SQLCODE || '--' ||
                      SQLERRM;
    END IF;
END CREATE_VIEW_P;