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;