日初将TN表数据同步到T0表

最近参与了一个项目，发现里面的代码程序写的好厉害，摘取下来以便学习。

create or replace function DATA_TN_2_T0(
P_INIT_DATE NUMBER(10),-----交易日期
P_ERROR_NO OUT NUMBER(10),----错误代码
P_ERROR_INFO OUT VARCHAR2(100)-----错误信息
)
RETURN NUMBER AS
V_ROWCOUNT NUMBER(10);----记录数
V_COLUMNSTR VARCHAR2(4000);-----数据表属性
V_SQLSTR VARCHAR2(4000);  -----执行的sql语句
V_WHERESTR VARCHAR2(4000); -----where条件

BEGIN
   FOR V_CUR IN (  
       SELECT USER_NAME,
              TABLE_NAME,   
              BAK_TABLE_NAME,
              REMARK,
              WHERE_STR,
              INDEX_MODE
              FROM BAKCONFIG  ----从配置表里面查询需要同步的TN表及对应的T0表，及用户名等
              WHERE ...
            )LOOP
     SELECT COUNT(*) INTO V_ROWCOUNT
     FROM ALL_ALL_TABLES
     WHERE UPPER(OWNER)=UPPER(V_CUR.USER_NAME)
     AND UPPER(TABLE_NAME)=UPPER(V_CUR.BAK_TABLE_NAME);----查询用户是否有操作该数据表的权限
     IF V_ROWCOUNT>0 THEN
        BEGIN
        V_COLUMNSTR :=' ';
        FOR V_COLUMN IN (
           SELECT U.COLUMN_NAME
              FROM (SELECT COLUMN_NAME FROM ALL_TAB_COLUMNS 
                  WHERE UPPER(OWNER)=UPPER(V_CUR.USER_NAME)
                  AND TABLE_NAME=UPPER(V_CUR.TABLE_NAME))U,
                   (SELECT COLUMN FROM ALL_TAB_COLUMNS
                  WHERE UPPER(OWNER)=UPPER(V_CUR.USER_NAME)
                  AND TABLE_NAME=UPPER(V_CUR.BAK_TABLE_NAME))L
             WHERE U.COLUMN_NAME=L.COLUMN_NAME  ---找出TN与T0表共有的属性
           )LOOP
          ----拼接成字符串
          V_COLUMNSTR:=V_COLUMNSTR || V_COLUMN.COLUMN_NAME || ',';
          END LOOP;
          V_COLUMNSTR:=SUBSTR(V_COLUMNSTR,2,LENGTH(V_COLUMNSTR)-2);
         ----获取表的索引处理
         IF V_CUR.INDEX_MODE='1' THEN
           INSERT INTO TMP_INDEX(INDEX_NAME,USER_NAME,TABLE_NAME,REMARK)
           SELECT INDEX_NAME,TABLE_OWNER,TABLE_NAME,
           DBMS_METADATA.GET_DDL('INDEX',INDEX_NAME,TABLE_OWNER)
           FROM USER_INDEXES
           WHERE TABLE_OWNER=V_CUR.USER_NAME
           AND TABLE_NAME=UPPER(V_CUR.BAK_TABLE_NAME);
        END IF;
      ---删除T0表数据
      EXECUTE IMMEDIATE 'TRUNCATE TABLE '|| V_CUR.USER_NAME ||'.'||
      UPPER(V_CUR.BAK_TABLE_NAME);
      ----删除表索引
      FOR IDX_CUR IN(SELECT * FROM TMP_INDEX)LOOP
      V_SQLSTR:='DROP INDEX '|| IDX_INDEX.USER_NAME ||'.'||IDX_CUR.INDEX_NAME;
      EXECUTE IMMEDIATE V_SQLSTR;
      END LOOP;
      ----处理where条件
      IF RITRIM(V_CUR.WHERE_STR) IS NOT NULL THEN
        V_WHERESTR:='WHERE'||V_CUR.WHERE_STR;
      ELSE
        V_WHERESTR:=' ';
      END IF;
      -----将日期换成当前的交易日
      V_WHERESTR:=REPLACE(V_WHERESTR,'#INIT_DATE#',P_INIT_DATE);
      ------拼接执行语句  
      V_SQLSTR:='INSERT /*+APPEND*/INTO '|| V_CUR.USER_NAME ||'.'||
                UPPER(V_CUR.BAK_TABLE_NAME)||'nologging('||
                V_COLUMNSTR || ')'||'SELECT '|| V_COLUMNSTR||
                ' FROM  '||V_CUR.USER_NAME ||'.'||
                V_CUR.TABLE_NAME || V_WHERESTR;
      ------执行语句
      EXECUTE IMMEDIATE V_SQLSTR;
      ----创建索引
      FOR IDX_CUR IN (SELECT * FROM TMP_INDEX)LOOP
          V_SQLSTR:=IDX_CUR.REMARK;
          EXECUTE IMMEDIATE V_SQLSTR;
      END LOOP;
      EXECUTE IMMEDIATE 'TRUNCATE TABLE TMP_INDEX';
      COMMIT;
    END;
   END IF;
 END LOOP;
 COMMIT;
 RETURN (0);
 EXCEPTION
     WHEN OTHERS THEN
     ROLLBACK;
     P_ERROR_NO     :=101;
     P_ERROR_INFO  :='执行存储过程错误'||'DATA_TN_2_T0';
     RETURN (P_ERROR_NO);
END;