[优化存储过程]XA Transaction 不允许使用DDL,EJB3.0调存储过程报错……

beamofsoul 2011-05-31 10:07:47
rt! 前两天费劲巴力的在论坛上咨询了几位高人好不容易把过程写出来了，调的时候发现项目EJB3.0用的XA事务，不能用DDL语句，其中EXECUTE IMMEDIATE V_STR;和commit是过不去的~ 再次请教高人这个过程能改成不用DDL的么？小弟先谢了……
-------------------------------------
CREATE OR REPLACE PROCEDURE COLLECT(
I_FORM_ID IN NUMBER,
I_PERIOD_ID IN NUMBER,
I_AREA_CODE IN VARCHAR2,
I_TYPE IN NUMBER, --判断是1区县、2市、3省
I_ORG_ID IN NUMBER, --RECORD所需表单组织结构与用户组织机构ID
I_USER_ID IN NUMBER, --RECORD所需用户ID
I_REPORT_RECORD_ID IN NUMBER --RECORD表ID，为0对该表进行插入，否则针对内容对该表进行更新
)
IS
V_STR VARCHAR2(8000); --将被执行的动态语句
V_VAL NUMBER; --插入到FORM_DATA中具体列的VALUE值
V_F_TO_D_ID NUMBER; --FORM_TO_DIC表列
V_DIC_CN_NAME VARCHAR2(100); --FORM_TO_DIC表列
V_DIC_TYPE NUMBER; --FORM_TO_DIC表列
V_FORM_ID NUMBER; --根据传入I_TYPE解析出的，指定插入具体表的表ID

V_NUMBER NUMBER; --判断SST临时表是否存在

BEGIN

/***********如果临时表SST存在，则删除并重新生成该临时表************/

SELECT COUNT(1) INTO V_NUMBER FROM ALL_TABLES WHERE TABLE_NAME = 'SST';
IF V_NUMBER=1 THEN
EXECUTE IMMEDIATE 'DROP TABLE SST';
END IF;

V_STR:='CREATE GLOBAL TEMPORARY TABLE SST (
COLLECT_ITEM_1 NUMBER,
COLLECT_ITEM_2 NUMBER,
COLLECT_ITEM_3 NUMBER,
COLLECT_ITEM_4 NUMBER,
COLLECT_ITEM_5 NUMBER,
COLLECT_ITEM_6 NUMBER,
COLLECT_ITEM_7 NUMBER,
COLLECT_ITEM_8 NUMBER,
COLLECT_ITEM_9 NUMBER,
COLLECT_ITEM_11 NUMBER,
COLLECT_ITEM_12 NUMBER,
COLLECT_ITEM_13 NUMBER,
COLLECT_ITEM_14 NUMBER,
COLLECT_ITEM_15 NUMBER,
COLLECT_ITEM_16 NUMBER,
COLLECT_ITEM_17 NUMBER,
COLLECT_ITEM_18 NUMBER,
COLLECT_ITEM_19 NUMBER,
COLLECT_ITEM_20 NUMBER,
COLLECT_ITEM_21 NUMBER,
COLLECT_ITEM_22 NUMBER
)
ON COMMIT PRESERVE ROWS';
EXECUTE IMMEDIATE V_STR;

/***********向临时表SST中插入查询出的汇总数据(应为一条)************/

V_STR:='
INSERT INTO SST (
SELECT
COUNT(CASE WHEN T.DIC_NAME=''ORG_ITEM_1'' THEN 1 END) COLLECT_ITEM_1,
COUNT(CASE WHEN T.DIC_NAME=''ORG_ITEM_18'' AND T.FORM_DIC_VALUE=1 THEN 1 END) COLLECT_ITEM_2,
COUNT(CASE WHEN T.DIC_NAME=''ORG_ITEM_18'' AND T.FORM_DIC_VALUE=7 THEN 1 END) COLLECT_ITEM_3,
COUNT(CASE WHEN T.DIC_NAME=''ORG_ITEM_18'' AND T.FORM_DIC_VALUE=6 THEN 1 END) COLLECT_ITEM_4,
COUNT(CASE WHEN T.DIC_NAME=''ORG_ITEM_66'' AND T.FORM_DIC_VALUE=1 THEN 1 END) COLLECT_ITEM_5,
COUNT(CASE WHEN T.DIC_NAME=''ORG_ITEM_88'' AND T.FORM_DIC_VALUE=1 THEN 1 END) COLLECT_ITEM_6,
COUNT(CASE WHEN T.DIC_NAME=''ORG_ITEM_92'' AND T.FORM_DIC_VALUE=1 THEN 1 END) COLLECT_ITEM_7,
COUNT(CASE WHEN T.DIC_NAME=''ORG_ITEM_67'' AND T.FORM_DIC_VALUE=1 THEN 1 END) COLLECT_ITEM_8,
SUM(DECODE(T.DIC_NAME, ''ORG_ITEM_70'', T.FORM_DIC_VALUE, 0)) COLLECT_ITEM_9,
COUNT(CASE WHEN T.DIC_NAME=''ORG_ITEM_75'' AND T.FORM_DIC_VALUE=1 THEN 1 END) COLLECT_ITEM_11,
SUM(DECODE(T.DIC_NAME, ''ORG_ITEM_76'', T.FORM_DIC_VALUE, 0)) COLLECT_ITEM_12,
SUM(DECODE(T.DIC_NAME, ''ORG_ITEM_77'', T.FORM_DIC_VALUE, 0)) COLLECT_ITEM_13,
SUM(DECODE(T.DIC_NAME, ''ORG_ITEM_78'', T.FORM_DIC_VALUE, 0)) COLLECT_ITEM_14,
COUNT(CASE WHEN T.DIC_NAME=''ORG_ITEM_79'' AND T.FORM_DIC_VALUE=1 THEN 1 END) COLLECT_ITEM_15,
COUNT(CASE WHEN T.DIC_NAME=''ORG_ITEM_82'' AND T.FORM_DIC_VALUE=1 THEN 1 END) COLLECT_ITEM_16,
SUM(DECODE(T.DIC_NAME, ''ORG_ITEM_89'', T.FORM_DIC_VALUE, 0)) COLLECT_ITEM_17,
COUNT(CASE WHEN T.DIC_NAME=''ORG_ITEM_84'' AND T.FORM_DIC_VALUE=1 THEN 1 END) COLLECT_ITEM_18,
COUNT(CASE WHEN T.DIC_NAME=''ORG_ITEM_86'' AND T.FORM_DIC_VALUE=1 THEN 1 END) COLLECT_ITEM_19,
COUNT(CASE WHEN T.DIC_NAME=''ORG_ITEM_87'' AND T.FORM_DIC_VALUE=1 THEN 1 END) COLLECT_ITEM_20,
COUNT(CASE WHEN T.DIC_NAME=''ORG_ITEM_87'' AND T.FORM_DIC_VALUE=0 THEN 1 END) COLLECT_ITEM_21,
COUNT(CASE WHEN T.DIC_NAME=''ORG_ITEM_90'' AND T.FORM_DIC_VALUE=1 THEN 1 END) COLLECT_ITEM_22
FROM FORM_DATA T,REPORT_RECORD R
WHERE R.FORM_ID='||I_FORM_ID||'
AND R.PERIOD_ID='||I_PERIOD_ID||'
AND R.RECORD_FLAG = 3
AND R.ORGANIZATION_ID IN (SELECT O.ID
FROM ORGANIZATION O WHERE O.IS_OPEN = 1
START WITH O.AREA_CODE='''||I_AREA_CODE||'''
CONNECT BY O.PARENT_ID= PRIOR O.ID)
AND R.RECORD_ID = T.RECORD_ID
)';
/*
AND EXISTS (
SELECT 'X'
FROM ORGANIZATION O
WHERE R.ORGANIZATION_ID = O.ID
AND O.IS_OPEN = 1
START WITH O.AREA_CODE = I_AREA_CODE
CONNECT BY O.PARENT_ID = PRIOR O.ID
)
*/
--Disp_Long_String(V_STR);
EXECUTE IMMEDIATE V_STR;
COMMIT;

/***********根据传入值判断具体操作的表单，1为区县、2为市、3为省************/

IF I_TYPE = 1 THEN
V_FORM_ID:=58;
END IF;
IF I_TYPE = 2 THEN
V_FORM_ID:=59;
END IF;
IF I_TYPE = 3 THEN
V_FORM_ID:=60;
END IF;

IF I_REPORT_RECORD_ID=0 THEN

/***********向状态关系表中插入汇总数据状态************/

INSERT INTO REPORT_RECORD VALUES(
REPORT_RECORD_SEQ.Nextval,
I_ORG_ID,
(SELECT TO_NUMBER(TO_CHAR(SYSDATE,'YYYY')) FROM DUAL),
V_FORM_ID,
I_PERIOD_ID,
I_USER_ID,
(SELECT TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD') FROM DUAL),
I_ORG_ID,
1,
NULL
);
--sun COMMIT;

ELSE

/***********如果数据已存在，修改该表数据状态为1已提交，并删除FORM_DATA中针对该表的数据***********/

UPDATE REPORT_RECORD R
SET R.RECORD_FLAG = 1
WHERE R.RECORD_ID = I_REPORT_RECORD_ID;
COMMIT;

V_STR:='TRUNCATE TABLE FORM_DATA WHERE FORM_ID='||V_FORM_ID||'';
EXECUTE IMMEDIATE V_STR;

END IF;

/***********遍历临时表向FORM_DATA表中插入具体列名和列值数据************/

FOR RS IN (SELECT DISTINCT COLUMN_NAME FROM USER_TAB_COLUMNS WHERE TABLE_NAME='SST')
LOOP
EXECUTE IMMEDIATE 'SELECT '||RS.COLUMN_NAME||' FROM SST' INTO V_VAL;

SELECT
FTD.F_TO_D_ID,
FTD.DIC_CN_NAME,
FTD.DIC_TYPE
INTO V_F_TO_D_ID,
V_DIC_CN_NAME,
V_DIC_TYPE
FROM FORM_TO_DIC FTD
WHERE FTD.DIC_NAME = RS.COLUMN_NAME
AND ROWNUM <=1;

INSERT INTO FORM_DATA VALUES(
REPORT_RECORD_SEQ.CURRVAL,
V_FORM_ID,
V_F_TO_D_ID,
V_VAL,
RS.COLUMN_NAME,
V_DIC_CN_NAME,
V_DIC_TYPE,
0,
1,
NULL,
1
);
COMMIT;
END LOOP;

COMMIT;

/***********删除临时表************/

V_STR:='TRUNCATE TABLE SST';
EXECUTE IMMEDIATE V_STR;
V_STR:='DROP TABLE SST';
EXECUTE IMMEDIATE V_STR;


END COLLECT;
...全文
160 7 打赏 收藏 转发到动态 举报
写回复
用AI写文章
7 条回复
切换为时间正序
请发表友善的回复…
发表回复
tangren 2011-05-31
  • 打赏
  • 举报
 回复
[Quote=引用 2 楼 beamofsoul 的回复:]

感谢tangren！
不过 先在外面创建临时表 再在过程中调用临时表的时候 引用不到啊? 我在网上查有人说临时表必须在过程中创建才能引用得到，是这样么？如果是这样那我在外面创建的临时表不就没用了么？那也就是说我这个主要问题还是没解决啊~
[/Quote]
错误的说法。
create global .... on commit reserved rows
数据是基于会话的，如果这个会话未结束，则肯定是引用到数据的。
如果你想通过临时表来进行数据传递，是不好的方案。

要从存储过程返回数据集，最好返回一个游标。
在存储过程中加一个输出参数
create or replace procedure COLLECT(......, o_return out sys_refcursor) is
........
begin
..........
open o_return for select * from SST;
end;
beamofsoul 2011-05-31
  • 打赏
  • 举报
 回复
顶起~ 求助……
beamofsoul 2011-05-31
  • 打赏
  • 举报
 回复
感谢tangren！
不过 先在外面创建临时表 再在过程中调用临时表的时候 引用不到啊? 我在网上查有人说临时表必须在过程中创建才能引用得到，是这样么？如果是这样那我在外面创建的临时表不就没用了么？那也就是说我这个主要问题还是没解决啊~
tangren 2011-05-31
  • 打赏
  • 举报
 回复
1、先在oracle中建立好临时表（oracle临时表就应该这么用，不要在过程中创建临时表
oracle临时表本质是：表不临时，数据临时！）。

2、存储过程
CREATE OR REPLACE PROCEDURE COLLECT(I_FORM_ID          IN NUMBER,

                                    I_PERIOD_ID        IN NUMBER,

                                    I_AREA_CODE        IN VARCHAR2,

                                    I_TYPE             IN NUMBER, --判断是1区县、2市、3省

                                    I_ORG_ID           IN NUMBER, --RECORD所需表单组织结构与用户组织机构ID

                                    I_USER_ID          IN NUMBER, --RECORD所需用户ID

                                    I_REPORT_RECORD_ID IN NUMBER --RECORD表ID，为0对该表进行插入，否则针对内容对该表进行更新

                                    ) IS

  V_STR         VARCHAR2(8000); --将被执行的动态语句

  V_VAL         NUMBER; --插入到FORM_DATA中具体列的VALUE值

  V_F_TO_D_ID   NUMBER; --FORM_TO_DIC表列

  V_DIC_CN_NAME VARCHAR2(100); --FORM_TO_DIC表列

  V_DIC_TYPE    NUMBER; --FORM_TO_DIC表列

  V_FORM_ID     NUMBER; --根据传入I_TYPE解析出的，指定插入具体表的表ID



  V_NUMBER NUMBER; --判断SST临时表是否存在



BEGIN

  

  --删除临时表数据

  DELETE FROM sst;



  /***********向临时表SST中插入查询出的汇总数据(应为一条)************/



  V_STR := '

  INSERT INTO SST (

  SELECT 

  COUNT(CASE WHEN T.DIC_NAME=''ORG_ITEM_1'' THEN 1 END) COLLECT_ITEM_1,

  COUNT(CASE WHEN T.DIC_NAME=''ORG_ITEM_18'' AND T.FORM_DIC_VALUE=1 THEN 1 END) COLLECT_ITEM_2,

  COUNT(CASE WHEN T.DIC_NAME=''ORG_ITEM_18'' AND T.FORM_DIC_VALUE=7 THEN 1 END) COLLECT_ITEM_3,

  COUNT(CASE WHEN T.DIC_NAME=''ORG_ITEM_18'' AND T.FORM_DIC_VALUE=6 THEN 1 END) COLLECT_ITEM_4,

  COUNT(CASE WHEN T.DIC_NAME=''ORG_ITEM_66'' AND T.FORM_DIC_VALUE=1 THEN 1 END) COLLECT_ITEM_5,

  COUNT(CASE WHEN T.DIC_NAME=''ORG_ITEM_88'' AND T.FORM_DIC_VALUE=1 THEN 1 END) COLLECT_ITEM_6,

  COUNT(CASE WHEN T.DIC_NAME=''ORG_ITEM_92'' AND T.FORM_DIC_VALUE=1 THEN 1 END) COLLECT_ITEM_7,

  COUNT(CASE WHEN T.DIC_NAME=''ORG_ITEM_67'' AND T.FORM_DIC_VALUE=1 THEN 1 END) COLLECT_ITEM_8,

  SUM(DECODE(T.DIC_NAME, ''ORG_ITEM_70'', T.FORM_DIC_VALUE, 0)) COLLECT_ITEM_9,

  COUNT(CASE WHEN T.DIC_NAME=''ORG_ITEM_75'' AND T.FORM_DIC_VALUE=1 THEN 1 END) COLLECT_ITEM_11,

  SUM(DECODE(T.DIC_NAME, ''ORG_ITEM_76'', T.FORM_DIC_VALUE, 0)) COLLECT_ITEM_12,

  SUM(DECODE(T.DIC_NAME, ''ORG_ITEM_77'', T.FORM_DIC_VALUE, 0)) COLLECT_ITEM_13,

  SUM(DECODE(T.DIC_NAME, ''ORG_ITEM_78'', T.FORM_DIC_VALUE, 0)) COLLECT_ITEM_14,

  COUNT(CASE WHEN T.DIC_NAME=''ORG_ITEM_79'' AND T.FORM_DIC_VALUE=1 THEN 1 END) COLLECT_ITEM_15,

  COUNT(CASE WHEN T.DIC_NAME=''ORG_ITEM_82'' AND T.FORM_DIC_VALUE=1 THEN 1 END) COLLECT_ITEM_16,

  SUM(DECODE(T.DIC_NAME, ''ORG_ITEM_89'', T.FORM_DIC_VALUE, 0)) COLLECT_ITEM_17,

  COUNT(CASE WHEN T.DIC_NAME=''ORG_ITEM_84'' AND T.FORM_DIC_VALUE=1 THEN 1 END) COLLECT_ITEM_18,

  COUNT(CASE WHEN T.DIC_NAME=''ORG_ITEM_86'' AND T.FORM_DIC_VALUE=1 THEN 1 END) COLLECT_ITEM_19,

  COUNT(CASE WHEN T.DIC_NAME=''ORG_ITEM_87'' AND T.FORM_DIC_VALUE=1 THEN 1 END) COLLECT_ITEM_20,

  COUNT(CASE WHEN T.DIC_NAME=''ORG_ITEM_87'' AND T.FORM_DIC_VALUE=0 THEN 1 END) COLLECT_ITEM_21,

  COUNT(CASE WHEN T.DIC_NAME=''ORG_ITEM_90'' AND T.FORM_DIC_VALUE=1 THEN 1 END) COLLECT_ITEM_22

  FROM FORM_DATA T,REPORT_RECORD R

  WHERE R.FORM_ID=' || I_FORM_ID || '

  AND R.PERIOD_ID=' || I_PERIOD_ID || '

  AND R.RECORD_FLAG = 3

  AND R.ORGANIZATION_ID IN (SELECT O.ID

  FROM ORGANIZATION O WHERE O.IS_OPEN = 1

  START WITH O.AREA_CODE=''' || I_AREA_CODE || '''

  CONNECT BY O.PARENT_ID= PRIOR O.ID)

  AND R.RECORD_ID = T.RECORD_ID

  )';

  /*

  AND EXISTS (

  SELECT 'X' 

  FROM ORGANIZATION O 

  WHERE R.ORGANIZATION_ID = O.ID

  AND O.IS_OPEN = 1

  START WITH O.AREA_CODE = I_AREA_CODE

  CONNECT BY O.PARENT_ID = PRIOR O.ID

  )

  */

  --Disp_Long_String(V_STR);

  EXECUTE IMMEDIATE V_STR;

  COMMIT;



  /***********根据传入值判断具体操作的表单，1为区县、2为市、3为省************/



  IF I_TYPE = 1 THEN

    V_FORM_ID := 58;

  END IF;

  IF I_TYPE = 2 THEN

    V_FORM_ID := 59;

  END IF;

  IF I_TYPE = 3 THEN

    V_FORM_ID := 60;

  END IF;



  IF I_REPORT_RECORD_ID = 0 THEN

  

    /***********向状态关系表中插入汇总数据状态************/

  

    INSERT INTO REPORT_RECORD

    VALUES

      (REPORT_RECORD_SEQ.Nextval, I_ORG_ID,

       (SELECT TO_NUMBER(TO_CHAR(SYSDATE, 'YYYY')) FROM DUAL), V_FORM_ID,

       I_PERIOD_ID, I_USER_ID,

       (SELECT TO_DATE(TO_CHAR(SYSDATE, 'YYYY-MM-DD'), 'YYYY-MM-DD')

           FROM DUAL), I_ORG_ID, 1, NULL);

    --sun COMMIT;

  

  ELSE

  

    /***********如果数据已存在，修改该表数据状态为1已提交，并删除FORM_DATA中针对该表的数据***********/

  

    UPDATE REPORT_RECORD R

       SET R.RECORD_FLAG = 1

     WHERE R.RECORD_ID = I_REPORT_RECORD_ID;

    COMMIT;

  

    V_STR := 'TRUNCATE TABLE FORM_DATA WHERE FORM_ID=' || V_FORM_ID || '';

    EXECUTE IMMEDIATE V_STR;

  

  END IF;



  /***********遍历临时表向FORM_DATA表中插入具体列名和列值数据************/



  FOR RS IN (SELECT DISTINCT COLUMN_NAME

               FROM USER_TAB_COLUMNS

              WHERE TABLE_NAME = 'SST') LOOP

    EXECUTE IMMEDIATE 'SELECT ' || RS.COLUMN_NAME || ' FROM SST'

      INTO V_VAL;

  

    SELECT FTD.F_TO_D_ID, FTD.DIC_CN_NAME, FTD.DIC_TYPE

      INTO V_F_TO_D_ID, V_DIC_CN_NAME, V_DIC_TYPE

      FROM FORM_TO_DIC FTD

     WHERE FTD.DIC_NAME = RS.COLUMN_NAME

       AND ROWNUM <= 1;

  

    INSERT INTO FORM_DATA

    VALUES

      (REPORT_RECORD_SEQ.CURRVAL, V_FORM_ID, V_F_TO_D_ID, V_VAL,

       RS.COLUMN_NAME, V_DIC_CN_NAME, V_DIC_TYPE, 0, 1, NULL, 1);

    COMMIT;

  END LOOP;



  COMMIT;



END COLLECT;

jianmingshu 2011-05-31
  • 打赏
  • 举报
 回复
顶起~
beamofsoul 2011-05-31
  • 打赏
  • 举报
 回复
努力~顶起！
beamofsoul 2011-05-31
  • 打赏
  • 举报
 回复
那我这个问题的具体情况应该怎么解决呢？请问？
1.我不想返回数据集，只想操纵数据集像过程中那样遍历后插入到指定物理表中
2.XA Transaction中不允许使用DDL语句，所以诸如commit、EXECUTE IMMEDIATE V_STR;等显示或是隐式的commit事物操作都将会报错
使用XA Transaction不能在存储过程中使用DDL语句
本文讨论了在业务中调用包含创建临时表的无返回值存储过程时，如何避免在事务会话中进行COMMIT操作导致的错误。通过删除存储过程中的COMMIT和DDL语句，以及使用EXECUTEIMMEDIATEV_STR动态操作，可以提高性能并减少缓存数据回滚的影响。同时，文章指出细节处理对于避免潜在错误至关重要。
How to Trace an XA Transaction Holding a Metadata Lock
当尝试执行如ALTERTABLE这样的DDL语句时，如果挂起等待元数据锁，可以检查性能模式下的metadata_locks表以获取详细信息。通常，这可能涉及到一个未完成的XA事务。通过XARECOVER和CONVERTXID可以找到并处理这个事务，从而释放锁。
【MySQL精通之路】SQL语句(3)-锁和事务语句
本文围绕MySQL的锁和事务语句展开，介绍了START TRANSACTION等事务控制语句，分析了无法回滚及导致隐含COMMIT的语句，阐述了SAVEPOINT等相关语句，还讲解了LOCK TABLE等表锁操作，以及SET TRANSACTION语句和XA事务，包括其SQL语句、状态和限制。
MySQL 8.0 事务全面讲解
本文全面讲解 MySQL 8.0 事务，涵盖事务核心概念（ACID）、基本操作、示例、隔离级别等内容。介绍了事务性 DDL 这一 8.0 新特性及 XA 分布式事务，还分析了事务失败回滚原因与解决方案，给出使用事务的注意事项和实践建议。
ORA-24761: transaction rolled back
这篇博客讨论了Oracle数据库中遇到的事务处理错误，即应用尝试提交事务但被服务器回滚。解决方案包括检查数据库配置，确保分布式锁超时大于XA连接超时且小于JTAT超时。还提到了一些Oracle的事务相关参数，如DDL_LOCK_TIMEOUT，DISTRIBUTED_LOCK_TIMEOUT等。建议修改数据源配置并重启WebLogic集群以测试修复效果。
Oracle 高级技术

3,499

社区成员

18,709

社区内容

发帖
与我相关
我的任务
社区描述
Oracle 高级技术相关讨论专区
社区管理员
  • 高级技术社区
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章