最近参与了一个项目,发现里面的代码程序写的好厉害,摘取下来以便学习。
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;