PL/SQL函数执行DML报错：无法在查询内执行DML操作

错误原因 #

你遇到的这个错误，是因为你在SQL查询语句里调用了这个函数（比如SELECT AP_EXT_MNT_BAM62(...) FROM DUAL;）。默认的PL/SQL函数属于查询上下文，不允许执行INSERT这类DML操作——SQL查询的设计是只读的，不能在里面修改数据。

两种解决方案 #

方案一：改用存储过程（推荐） #

存储过程的设计目的就是执行数据操作，不存在查询上下文的限制。把原函数改成过程即可：

CREATE OR REPLACE PROCEDURE AP_EXT_MNT_BAM62
(
        var_typ_entity              CHAR,
        var_cod_acct_no             CHAR,
        var_cod_cust_id             NUMBER,
        var_cod_ao_business         varchar2,
        var_cod_ao_operations       varchar2,
        var_cod_lob                 NUMBER
)
AS
BEGIN
    IF var_typ_entity = 'A' THEN
        BEGIN
            INSERT INTO ba_cust_acct_ao_lob_xref
            (typ_entity,
             Cod_cust_id,
             cod_acct_no,
             Cod_ao_business,
             cod_ao_operations,
             cod_lob,
             flg_mnt_status,
             cod_mnt_action,
             cod_last_mnt_makerid,
             cod_last_mnt_chkrid,
             dat_last_mnt,
             ctr_updat_srlno,
             COD_ENTITY_VPD)
            SELECT var_typ_entity,
                   var_cod_cust_id,
                   var_cod_acct_no,
                   var_cod_ao_business,
                   var_cod_ao_operations,
                   var_cod_lob,
                   flg_mnt_status,
                   cod_mnt_action,
                   cod_last_mnt_makerid,
                   cod_last_mnt_chkrid,
                   SYSDATE,
                   ctr_updat_srlno,
                   COD_ENTITY_VPD
              FROM CH_ACCT_MAST
             WHERE cod_acct_no = var_cod_acct_no;
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                NULL;
        END;
    END IF;

    IF var_typ_entity = 'C' THEN
        BEGIN
            INSERT INTO ba_cust_acct_ao_lob_xref
            (typ_entity,
             Cod_cust_id,
             cod_acct_no,
             Cod_ao_business,
             cod_ao_operations,
             cod_lob,
             flg_mnt_status,
             cod_mnt_action,
             cod_last_mnt_makerid,
             cod_last_mnt_chkrid,
             dat_last_mnt,
             ctr_updat_srlno,
             COD_ENTITY_VPD)
            SELECT var_typ_entity,
                   var_cod_cust_id,
                   var_cod_acct_no,
                   var_cod_ao_business,
                   var_cod_ao_operations,
                   var_cod_lob,
                   flg_mnt_status,
                   cod_mnt_action,
                   cod_last_mnt_makerid,
                   cod_last_mnt_chkrid,
                   SYSDATE,
                   ctr_updat_srlno,
                   COD_ENTITY_VPD
              FROM CI_CUSTMAST
             WHERE Cod_cust_id = var_Cod_cust_id;
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                NULL;
        END;
    END IF;
END;

调用方式改成：

BEGIN
    AP_EXT_MNT_BAM62('A', '账号值', 客户ID值, '业务编码', '操作编码', LOB编码);
    COMMIT; -- 按需提交事务
END;
/

方案二：给函数添加自治事务 #

如果必须保留函数形式，可以在函数声明部分添加PRAGMA AUTONOMOUS_TRANSACTION;，让函数在独立的事务中执行DML。注意：自治事务的提交/回滚不会影响主事务，需要手动处理事务。

修改后的函数代码：

CREATE OR REPLACE FUNCTION AP_EXT_MNT_BAM62
(
        var_typ_entity              CHAR,
        var_cod_acct_no             CHAR,
        var_cod_cust_id             NUMBER,
        var_cod_ao_business         varchar2,
        var_cod_ao_operations       varchar2,
        var_cod_lob                 NUMBER
)
RETURN NUMBER
AS
    PRAGMA AUTONOMOUS_TRANSACTION; -- 添加自治事务声明
BEGIN
    IF var_typ_entity = 'A' THEN
        BEGIN
            INSERT INTO ba_cust_acct_ao_lob_xref
            (typ_entity,
             Cod_cust_id,
             cod_acct_no,
             Cod_ao_business,
             cod_ao_operations,
             cod_lob,
             flg_mnt_status,
             cod_mnt_action,
             cod_last_mnt_makerid,
             cod_last_mnt_chkrid,
             dat_last_mnt,
             ctr_updat_srlno,
             COD_ENTITY_VPD)
            SELECT var_typ_entity,
                   var_cod_cust_id,
                   var_cod_acct_no,
                   var_cod_ao_business,
                   var_cod_ao_operations,
                   var_cod_lob,
                   flg_mnt_status,
                   cod_mnt_action,
                   cod_last_mnt_makerid,
                   cod_last_mnt_chkrid,
                   SYSDATE,
                   ctr_updat_srlno,
                   COD_ENTITY_VPD
              FROM CH_ACCT_MAST
             WHERE cod_acct_no = var_cod_acct_no;
            COMMIT; -- 自治事务必须手动提交
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                NULL;
        END;
    END IF;

    IF var_typ_entity = 'C' THEN
        BEGIN
            INSERT INTO ba_cust_acct_ao_lob_xref
            (typ_entity,
             Cod_cust_id,
             cod_acct_no,
             Cod_ao_business,
             cod_ao_operations,
             cod_lob,
             flg_mnt_status,
             cod_mnt_action,
             cod_last_mnt_makerid,
             cod_last_mnt_chkrid,
             dat_last_mnt,
             ctr_updat_srlno,
             COD_ENTITY_VPD)
            SELECT var_typ_entity,
                   var_cod_cust_id,
                   var_cod_acct_no,
                   var_cod_ao_business,
                   var_cod_ao_operations,
                   var_cod_lob,
                   flg_mnt_status,
                   cod_mnt_action,
                   cod_last_mnt_makerid,
                   cod_last_mnt_chkrid,
                   SYSDATE,
                   ctr_updat_srlno,
                   COD_ENTITY_VPD
              FROM CI_CUSTMAST
             WHERE Cod_cust_id = var_Cod_cust_id;
            COMMIT; -- 自治事务必须手动提交
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                NULL;
        END;
    END IF;
    RETURN 1;
END;

注意事项 #

• 方案一优先，因为存储过程更适合执行数据修改操作，逻辑更清晰，也避免自治事务带来的事务独立性问题。
• 如果用自治事务，必须在函数内部手动COMMIT或ROLLBACK，否则会导致事务悬挂。

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