oracle存储过程每次只能执行一个事务,存储过程调用 自治事务 函数 退出不正常（循环执行几遍）...

CREATE OR REPLACE FUNCTION CRMSII_TAB_LINE(IN_TAB  VARCHAR2,

IN_DATE VARCHAR2)

RETURN VARCHAR2 IS

PRAGMA AUTONOMOUS_TRANSACTION;  -- 自治事务

V_SQL    VARCHAR(2000);

V_SUCCES VARCHAR(40);

BEGIN

---对传入表各个字段类型进行判断: INDEX_VALUE 指标值

--字段类型是日期型

BEGIN

for rec0 in (select distinct CCPI.SMALL_RULE_CD

from USER_TAB_COLUMNS UTC, CRMS_CO_PERSONALITY_INDEX CCPI

WHERE UPPER(UTC.TABLE_NAME) = UPPER(IN_TAB)

AND UPPER(UTC.TABLE_NAME) = UPPER(CCPI.TAB_NAME)

order by CCPI.SMALL_RULE_CD) loop

FOR REC1 IN (SELECT *

FROM USER_TAB_COLUMNS          UTC,

CRMS_CO_PERSONALITY_INDEX CCPI

WHERE UPPER(UTC.TABLE_NAME) = UPPER(IN_TAB)

AND UPPER(UTC.TABLE_NAME) = UPPER(CCPI.TAB_NAME)

AND UPPER(UTC.COLUMN_NAME) = UPPER(CCPI.INDEX_CODE)

and CCPI.SMALL_RULE_CD = rec0.SMALL_RULE_CD) LOOP

IF UPPER(REC1.DATA_TYPE) = 'DATE' THEN

V_SQL := 'INSERT INTO CRMS_CO_PERSONALITY_INDEXDATA(

INDEXDATA_ID,

BIG_RULE_CD       ,  --大规则号

SMALL_RULE_CD     ,  --小规则号

INDEX_CODE        ,  --指标代码

INDEX_NAME        ,  --指标名称

INDEX_VALUE       ,  --指标值

RULE_TYPE_CD      ,  --规则类别

BUSS_TYPE_CD      , --类型(对公，对私)rec0.SMALL_RULE_CD

DATA_INDEX        , ---粒度主键

ALARM_DATE )

SELECT  SEQ_CO_PERSONALITY_INDEX.NEXTVAL,''' ||

REC1.BIG_RULE_CD || ''',''' || REC1.SMALL_RULE_CD ||

''',''' || REC1.INDEX_CODE || ''',''' || REC1.INDEX_NAME ||

''', TO_CHAR(' || REC1.INDEX_CODE ||

',''YYYY-MM-DD''),''' || REC1.RULE_TYPE_CD || ''',''' ||

REC1.BUSS_TYPE_CD || ''',' || rec1.data_index ||

', TO_DATE(''' || IN_DATE || ''',''YYYY-MM-DD'')

FROM  ' || REC1.TABLE_NAME || ' WHERE  SMALL_RULE=' ||

rec0.SMALL_RULE_CD || '  AND   REPORT_DATE=TO_DATE(''' ||

IN_DATE || ''',''YYYY-MM-DD'')';

EXECUTE IMMEDIATE V_SQL;

--字段类型是数据型

ELSIF UPPER(REC1.DATA_TYPE) = 'NUMBER' THEN

V_SQL := 'INSERT INTO CRMS_CO_PERSONALITY_INDEXDATA(

INDEXDATA_ID,

BIG_RULE_CD       ,  --大规则号

SMALL_RULE_CD     ,  --小规则号

INDEX_CODE        ,  --指标代码

INDEX_NAME        ,  --指标名称

INDEX_VALUE       ,  --指标值

RULE_TYPE_CD      ,  --规则类别

BUSS_TYPE_CD      , --类型(对公，对私)

DATA_INDEX        , ---粒度主键

ALARM_DATE )

SELECT  SEQ_CO_PERSONALITY_INDEX.NEXTVAL,''' ||

REC1.BIG_RULE_CD || ''',''' || REC1.SMALL_RULE_CD ||

''',''' || REC1.INDEX_CODE || ''',''' || REC1.INDEX_NAME ||

''', TO_CHAR(' || REC1.INDEX_CODE || '),''' ||

REC1.RULE_TYPE_CD || ''',''' || REC1.BUSS_TYPE_CD ||

''',' || rec1.data_index || ', TO_DATE(''' || IN_DATE ||

''',''YYYY-MM-DD'')

FROM  ' || REC1.TABLE_NAME || ' WHERE  SMALL_RULE=' ||

rec0.SMALL_RULE_CD || '  AND  REPORT_DATE=TO_DATE(''' ||

IN_DATE || ''',''YYYY-MM-DD'')';

EXECUTE IMMEDIATE V_SQL;

--字段类型是时间戳

ELSIF SUBSTR(UPPER(REC1.DATA_TYPE), 1, 9) = 'TIMESTAMP' THEN

V_SQL := 'INSERT INTO CRMS_CO_PERSONALITY_INDEXDATA(

INDEXDATA_ID,

BIG_RULE_CD       ,  --大规则号

SMALL_RULE_CD     ,  --小规则号

INDEX_CODE        ,  --指标代码

INDEX_NAME        ,  --指标名称

INDEX_VALUE       ,  --指标值

RULE_TYPE_CD      ,  --规则类别

BUSS_TYPE_CD      , --类型(对公，对私)

DATA_INDEX        , ---粒度主键

ALARM_DATE )

SELECT  SEQ_CO_PERSONALITY_INDEX.NEXTVAL,''' ||

REC1.BIG_RULE_CD || ''',''' || REC1.SMALL_RULE_CD ||

''',''' || REC1.INDEX_CODE || ''',''' || REC1.INDEX_NAME ||

''', TO_CHAR(' || REC1.INDEX_CODE ||

',''YYYY-MM-DD HH24:MI:SS''),''' || REC1.RULE_TYPE_CD ||

''',''' || REC1.BUSS_TYPE_CD || ''',' || rec1.data_index ||

', TO_DATE(''' || IN_DATE || ''',''YYYY-MM-DD'')

FROM  ' || REC1.TABLE_NAME || ' WHERE  SMALL_RULE=' ||

rec0.SMALL_RULE_CD || '  AND  REPORT_DATE=TO_DATE(''' ||

IN_DATE || ''',''YYYY-MM-DD'')';

EXECUTE IMMEDIATE V_SQL;

--字段类型是字符型

ELSIF UPPER(REC1.DATA_TYPE) LIKE '%CHAR%' THEN

V_SQL := 'INSERT INTO CRMS_CO_PERSONALITY_INDEXDATA(

INDEXDATA_ID,

BIG_RULE_CD       ,  --大规则号

SMALL_RULE_CD     ,  --小规则号

INDEX_CODE        ,  --指标代码

INDEX_NAME        ,  --指标名称

INDEX_VALUE       ,  --指标值

RULE_TYPE_CD      ,  --规则类别

BUSS_TYPE_CD      , --类型(对公，对私)

DATA_INDEX        , ---粒度主键

ALARM_DATE )

SELECT  SEQ_CO_PERSONALITY_INDEX.NEXTVAL,''' ||

REC1.BIG_RULE_CD || ''',''' || REC1.SMALL_RULE_CD ||

''',''' || REC1.INDEX_CODE || ''',''' || REC1.INDEX_NAME ||

''',' || REC1.INDEX_CODE || ',''' || REC1.RULE_TYPE_CD ||

''',''' || REC1.BUSS_TYPE_CD || ''',' || rec1.data_index ||

', TO_DATE(''' || IN_DATE || ''',''YYYY-MM-DD'')

FROM  ' || REC1.TABLE_NAME || ' WHERE  SMALL_RULE=' ||

rec0.SMALL_RULE_CD || '  AND  REPORT_DATE=TO_DATE(''' ||

IN_DATE || ''',''YYYY-MM-DD'')';

EXECUTE IMMEDIATE V_SQL;

END IF;

END LOOP;

end loop;

COMMIT;

V_SUCCES := '0';

RETURN(V_SUCCES);

EXCEPTION

WHEN OTHERS THEN

BEGIN

V_SUCCES := '1';

rollback;

RETURN(V_SUCCES);

END;

END;

END CRMSII_TAB_LINE;