解决 Oracle 中的“无法在查询中执行 DML”错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9966245/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Solving "Cannot perform DML inside a query" error in Oracle
提问by Awlad Liton
I've created regular function. It has been created successfully. But when I run it with
我已经创建了常规功能。它已成功创建。但是当我运行它时
select reg('awlad','01968688680','545466455','12345') from dual
It gives me this error:
它给了我这个错误：
ORA-14551: cannot perform a DML operation inside a query
How can I solve this?
我该如何解决这个问题？
CREATE OR REPLACE FUNCTION reg(
name in varchar2,
cellNo in varchar2,
voterId in varchar2,
pass in varchar2
)
RETURN NUMBER
IS
succ NUMBER;
BEGIN
succ:=0;
insert into logInfo values(loginfo_seq.nextval,cellNo,pass,0);
succ:=1;
insert into passInfo values(name,cellNo,voterId);
succ:=2;
RETURN succ;
END;
回答by Justin Cave
A function is supposed to compute and return a result, not change the state of the database. If you want to do DMLin a function (i.e. if you want to insert rows into tables), you cannot call that function in a SELECTstatement since a SELECTstatement cannot change the state of the database. In general, you'd be better off creating this sort of thing as a stored procedure rather than a stored function.
函数应该计算并返回结果，而不是更改数据库的状态。如果要DML在函数中执行（即，如果要向表中插入行），则不能在SELECT语句中调用该函数，因为SELECT语句不能更改数据库的状态。一般来说，最好将这种东西创建为存储过程而不是存储函数。
You can call this function from a PL/SQL block just as you would call a stored procedure
您可以像调用存储过程一样从 PL/SQL 块中调用此函数
DECLARE
l_success_code NUMBER;
BEGIN
l_success_code := reg('awlad','01968688680','545466455','12345');
END;
If you want to create this as a procedure
如果您想将其创建为程序
CREATE OR REPLACE PROCEDURE reg( name in varchar2,
cellNo in varchar2,
voterId in varchar2,
pass in varchar2,
succ out NUMBER )
AS
BEGIN
succ:=0;
insert into logInfo values(loginfo_seq.nextval,cellNo,pass,0);
insert into passInfo values(name,cellNo,voterId);
succ:=1;
END;
then you'd need to call the procedure by passing in the OUTparameter
那么你需要通过传入OUT参数来调用过程
DECLARE
l_success_code NUMBER;
BEGIN
reg('awlad','01968688680','545466455','12345', l_success_code);
END;
回答by Vincent Malgrat
If all you want to do is log information, it would be appropriate to use an autonomous transactionto do the intermediate insert.
如果您只想记录信息，那么使用自治事务来执行中间插入是合适的。
CREATE OR REPLACE FUNCTION reg(NAME IN VARCHAR2,
cellNo IN VARCHAR2,
voterId IN VARCHAR2,
pass IN VARCHAR2)
RETURN NUMBER IS
--
PROCEDURE do_loginfo (p_id NUMBER,
p_cellNo VARCHAR2,
p_pass VARCHAR2,
p_x NUMBER) IS
PRAGMA AUTONOMOUS_TRANSACTION
BEGIN
INSERT INTO logInfo VALUES (p_id, p_cellNo, p_pass, p_x);
COMMIT;
END do_loginfo;
PROCEDURE do_passInfo (p_name VARCHAR2,
p_cellNo VARCHAR2,
p_voterId VARCHAR2) IS
PRAGMA AUTONOMOUS_TRANSACTION
BEGIN
INSERT INTO passInfo VALUES (p_name, p_cellNo, p_voterId);
COMMIT;
END do_passInfo;
--
succ NUMBER;
BEGIN
succ := 0;
do_logInfo (loginfo_seq.NEXTVAL, cellNo, pass, 0);
succ := 1;
do_passInfo (NAME, cellNo, voterId);
succ := 2;
RETURN succ;
END;
Note that it will be useful for debugging purpose, but since it is not transactional, it should not be used to record data (since the rows inserted will stay even if the main transaction is rolled back).
请注意，它对于调试目的很有用，但由于它不是事务性的，因此不应用于记录数据（因为即使主事务回滚，插入的行也将保留）。
