无法在plsql中的查询内部执行dml操作

Question

在下面的函数中，我想根据账号和客户id将值插入表ba_acct_cust_logb_xref中。账号im从ch_acct_master表获取，客户id从cl_custmast表获取。我如何使用plsql函数来实现这一点。当我尝试时，它显示了错误，比如无法在查询中执行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
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;
return 1;

END;

Answer 1

好吧，可以在函数中执行DML，并在查询中使用它，如果它是一个自治事务的话。不要那样做，不要在这里。

下面是一个例子，使用这个示例表：

SQL> SELECT * FROM test;
ID NAME
---------- --------------------
2 Little
3 Foot

函数应该在该表中插入一行(基本上就是这样(：

SQL> CREATE OR REPLACE FUNCTION f_test (par_id IN NUMBER, par_name IN VARCHAR2)
2     RETURN NUMBER
3  IS
4  BEGIN
5     INSERT INTO test (id, name)
6          VALUES (par_id, par_name);
7
8     RETURN 1;
9  END;
10  /
Function created.

你得到的错误是由于这样一个代码：

SQL> SELECT f_test (5, 'Karthiga') FROM DUAL;
SELECT f_test (5, 'Karthiga') FROM DUAL
*
ERROR at line 1:
ORA-14551: cannot perform a DML operation inside a query
ORA-06512: at "SCOTT.F_TEST", line 5

如果它是PL/SQL过程，那么您的函数将工作：

SQL> DECLARE
2     l_test  NUMBER;
3  BEGIN
4     l_test := f_test (5, 'Karthiga');
5  END;
6  /
PL/SQL procedure successfully completed.

或者，正如我之前所说，如果函数是一个自主事务，您可以在查询中使用它：

SQL> CREATE OR REPLACE FUNCTION f_test (par_id IN NUMBER, par_name IN VARCHAR2)
2     RETURN NUMBER
3  IS
4     PRAGMA AUTONOMOUS_TRANSACTION;
5  BEGIN
6     INSERT INTO test (id, name)
7          VALUES (par_id, par_name);
8
9     COMMIT;
10     RETURN 1;
11  END;
12  /
Function created.

SQL> SELECT f_test (7, 'Twitter') FROM DUAL;
F_TEST(7,'TWITTER')
-------------------
1
SQL>

看到了吗？现在它起作用了，但这不是你应该做的，真的。

结果：

SQL> select * from test;
ID NAME
---------- --------------------
2 Little
3 Foot
5 Karthiga      --> newly added
7 Twitter       --> rows
SQL>

---

为什么坚持使用函数？这显然是一个过程代码。是的，您返回1只是因为函数必须返回某些东西，但没有证据表明您确实需要函数。函数计算填充并返回值。你只是在插入一行；这就是程序的作用。

因此：

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;

如果必须返回1，则将其设为OUT参数。