场景
测试系统升级改造会动现有库中的一些对象和关系,在对原有对象处理时需要定义一些规则降低不必要的风险。
尽力保证不要有删除操作、不要发生覆盖或丢失的情况。
示例
DECLARE
BERSQL VARCHAR2(100) := 'ALTER TABLE NSBRANCH RENAME TO NSBRANCH_BACK'; /*创建建视图前转储当前表*/
DBLINK VARCHAR2(40) := 'SMS_DBLINK'; /*DBLINK的名称*/
VIEWSQL VARCHAR2(400) := 'CREATE OR REPLACE VIEW NSBRANCH AS SELECT A.BRANCH_NO AS BRNO,A.BRANCH_NAME AS BRNAME,A.BRANCH_TYPE AS ISCENTER,NULL AS CTACNTNO,NULL AS BANKACNTNO,NULL AS OPENBANK,NULL AS ADDRESS FROM CPM_BRANCH@SMS_DBLINK A'; /*创建视图*/
TMP_STR VARCHAR2(80) := NULL; /*视图名称_NSBRANCH*/
BEGIN
SELECT A.DB_LINK INTO TMP_STR FROM USER_DB_LINKS A WHERE A.DB_LINK = DBLINK;
/*如果没有所需要的DB_LINK就抛异常*/
IF TMP_STR IS NULL THEN
RAISE_APPLICATION_ERROR(-20000,
'执行此语句所需要的DBLINK未创建或未正常工作请检查,DBLINK名称[' ||
DBLINK || ']');
END IF;
TMP_STR := NULL;
SELECT A.OBJECT_NAME INTO TMP_STR FROM USER_OBJECTS A
WHERE A.OBJECT_NAME = 'NSBRANCH'AND A.OBJECT_TYPE = 'TABLE';
/**如果表存在就转储此表,然后创建视图*/
IF TMP_STR IS NOT NULL THEN
EXECUTE IMMEDIATE BERSQL;
END IF;
EXECUTE IMMEDIATE VIEWSQL;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;