ORDS调用PL/SQL存储过程事务不回滚问题的解决咨询

问题根源 #

• 存储过程未包含异常处理逻辑，当第二个MERGE操作失败时直接抛出异常，但第一个MERGE的修改已处于未提交事务中。
• ORDS默认开启自动提交（p_auto_commit=TRUE），即使捕获到异常，若未显式执行ROLLBACK，ORDS仍会提交事务中已完成的修改。
• SQL Developer等客户端在遇到未处理的存储过程异常时，会自动回滚事务，因此表现正常。

具体解决方案 #

方案一：在ORDS处理块中添加异常回滚 #

修改ORDS的HANDLER定义，在异常分支中加入ROLLBACK语句，确保异常发生时回滚所有未提交的修改：

BEGIN
ORDS.ENABLE_SCHEMA(
      p_enabled             => TRUE,
      p_schema              => 'DB_USER',
      p_url_mapping_type    => 'BASE_PATH',
      p_url_mapping_pattern => 'db_user',
      p_auto_rest_auth      => FALSE);    

ORDS.DEFINE_MODULE(
      p_module_name    => 'test_payment_request',
      p_base_path      => '/prtest/',
      p_items_per_page =>  25,
      p_status         => 'PUBLISHED',
      p_comments       => NULL);    
      
ORDS.DEFINE_TEMPLATE(
      p_module_name    => 'test_payment_request',
      p_pattern        => 'savePayReq',
      p_priority       => 0,
      p_etag_type      => 'HASH',
      p_etag_query     => NULL,
      p_comments       => NULL);
ORDS.DEFINE_HANDLER(
      p_module_name    => 'test_payment_request',
      p_pattern        => 'savePayReq',
      p_method         => 'POST',
      p_source_type    => 'plsql/block',
      p_items_per_page =>  0,
      p_mimes_allowed  => 'application/json',
      p_comments       => NULL,
      p_source         => 
'begin

save_pr(    p_pr_number_in => :pr_number_in, 
            p_org_name_in => :org_name_in, 
            p_amount_in => :amount_in, 
            p_cost_center_in => :cost_center_in, 
            p_line_number_in => :line_number_in
    );      

:return_status := ''SUCCESS'';
:return_message := ''Saved successfully.'';

EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK; -- 新增回滚语句
        :status := 400;
        :return_status := ''ERROR'';
        :return_message := ''System response: '' || sqlerrm;

end;'
      
END;

方案二：在存储过程中添加异常处理逻辑 #

修改存储过程，增加异常处理块，在异常发生时执行ROLLBACK并重新抛出异常，确保无论调用方式如何，事务都会回滚：

CREATE OR REPLACE PROCEDURE save_pr (
    p_pr_number_in   IN VARCHAR2,
    p_org_name_in    IN VARCHAR2,
    p_amount_in      IN NUMBER,
    p_cost_center_in IN VARCHAR2,
    p_line_number_in IN NUMBER
) AS
BEGIN
    MERGE INTO xxtb_payment_request xpr
    USING (
        SELECT
            p_pr_number_in AS pr_number_in,
            p_org_name_in  AS org_name_in,
            p_amount_in    AS amount_in
        FROM
            dual
    ) p_params ON ( xpr.pr_number = p_params.pr_number_in )
    WHEN MATCHED THEN UPDATE
    SET xpr.org_name = p_params.org_name_in,
        xpr.amount = p_params.amount_in
    WHEN NOT MATCHED THEN
    INSERT (
        pr_number,
        org_name,
        amount )
    VALUES
        ( p_params.pr_number_in,
          p_params.org_name_in,
          p_params.amount_in );

    MERGE INTO xxtb_pr_distribution xpd
    USING (
        SELECT
            p_pr_number_in   AS pr_number_in,
            p_line_number_in AS line_number_in,
            p_cost_center_in AS cost_center_in
        FROM
            dual
    ) d_params ON ( xpd.pr_number = d_params.pr_number_in
                    AND xpd.line_number = d_params.line_number_in )
    WHEN MATCHED THEN UPDATE
    SET xpd.cost_center = d_params.cost_center_in
    WHEN NOT MATCHED THEN
    INSERT (
        pr_number,
        line_number,
        cost_center )
    VALUES
        ( d_params.pr_number_in,
          d_params.line_number_in,
          d_params.cost_center_in );

    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK; -- 回滚所有未提交操作
        RAISE; -- 重新抛出异常，让调用方捕获并处理
END save_pr;

方案三：关闭ORDS自动提交 #

在定义ORDS HANDLER时，将p_auto_commit设置为FALSE，让存储过程完全控制事务的提交与回滚：

ORDS.DEFINE_HANDLER(
      p_module_name    => 'test_payment_request',
      p_pattern        => 'savePayReq',
      p_method         => 'POST',
      p_source_type    => 'plsql/block',
      p_items_per_page =>  0,
      p_mimes_allowed  => 'application/json',
      p_comments       => NULL,
      p_auto_commit    => FALSE, -- 关闭ORDS自动提交
      p_source         => 
'begin

save_pr(    p_pr_number_in => :pr_number_in, 
            p_org_name_in => :org_name_in, 
            p_amount_in => :amount_in, 
            p_cost_center_in => :cost_center_in, 
            p_line_number_in => :line_number_in
    );      

:return_status := ''SUCCESS'';
:return_message := ''Saved successfully.'';

EXCEPTION
    WHEN OTHERS THEN
        :status := 400;
        :return_status := ''ERROR'';
        :return_message := ''System response: '' || sqlerrm;

end;'
      
END;

验证方法 #

使用成本中心长度超限的测试脚本（p_cost_center_in => 'CC1033'）通过API调用，检查XXTB_PAYMENT_REQUEST表是否无新增/修改数据，确认事务已完整回滚。

内容的提问来源于stack exchange，提问作者rish