oracle 检查插入是否成功
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1965644/
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
Check if INSERT was successful
提问by user230752
I have some procedure that perform INSERT statement:
我有一些执行 INSERT 语句的过程：
CREATE OR REPLACE PROCEDURE potok_insert(
p_jfplate IN potok.jfplate%TYPE,
p_post IN potok.post%TYPE,
p_jfchan IN potok.jfchan%TYPE,
p_jfdatetime IN VARCHAR2
)
AS
t_jfdatetime TIMESTAMP:=TO_TIMESTAMP(p_jfdatetime,'DD.MM.YYYY HH24:MI:SS');
BEGIN
INSERT INTO potok (jfplate, post, jfchan, jfdate_y, jfdate_m, jfdate_d, jftime, jfdatetime,
dt_reg, ibd_arx)
VALUES (RTRIM(p_jfplate),
p_post,
RTRIM(p_jfchan),
EXTRACT(YEAR FROM t_jfdatetime),
EXTRACT(MONTH FROM t_jfdatetime),
EXTRACT(DAY FROM t_jfdatetime),
LPAD(EXTRACT(HOUR FROM t_jfdatetime),2,'0')||':'||
LPAD(EXTRACT(MINUTE FROM t_jfdatetime),2,'0')||':'||
LPAD(EXTRACT(SECOND FROM t_jfdatetime),2,'0'),
CAST(t_jfdatetime AS DATE),
SYSDATE,
1);
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END potok_insert;
Some triggers and constraints are applied to table, they can break INSERT. How can I check in procedure body - if INSERT was successful or not?
一些触发器和约束应用于表，它们可以破坏 INSERT。如何检查程序主体 - 如果插入成功与否？
Of course I can call count() in head and in end of procedure, but this will be not so graceful solution.
当然，我可以在程序的开头和结尾调用 count() ，但这不是那么优雅的解决方案。
回答by davek
You can use the RETURNING clause to return the rowid of the row you have just created, like this:
您可以使用 RETURNING 子句返回您刚刚创建的行的 rowid，如下所示：
CREATE SEQUENCE seq_emp;
set serveroutput on
DECLARE
x emp.empno%TYPE;
BEGIN
INSERT INTO emp
(empno, ename)
VALUES
(seq_emp.NEXTVAL, 'Morgan')
RETURNING empno
INTO x;
dbms_output.put_line(x);
END;
/
DECLARE
r rowid;
BEGIN
INSERT INTO emp
(empno, ename)
VALUES
(seq_emp.NEXTVAL, 'Morgan')
RETURNING rowid
INTO r;
dbms_output.put_line(r);
END;
/
DECLARE
x emp.empno%TYPE;
r rowid;
BEGIN
INSERT INTO emp
(empno, ename)
VALUES
(seq_emp.NEXTVAL, 'Morgan')
RETURNING rowid, empno
INTO r, x;
dbms_output.put_line(r);
dbms_output.put_line(x);
END;
Taken from this link:
取自此链接：
回答by Gary Myers
Remove the EXCEPTION...WHEN OTHERS.
删除例外......当其他人时。
Really the question should not be how to test to see if it succeeds, but a decision on what to do if it fails.
真正的问题不应该是如何测试以查看它是否成功，而是决定如果失败该怎么办。
回答by Peter Lang
When constraints are violated, an exception will be thrown and you are going to end up in your exception handling block.
当违反约束时，将抛出异常，您将最终进入异常处理块。
What kind of triggers are preventing you from inserting? Can you throw an exception in there too?
什么样的触发器阻止你插入？你也可以在那里抛出异常吗？
回答by Bob Jarvis - Reinstate Monica
In the exception block you should dump the contents of SQLCODE and SQLERRM so you can see what error(s) you're getting. Perhaps adding the following to your WHEN OTHERS handler would help:
在异常块中，您应该转储 SQLCODE 和 SQLERRM 的内容，以便您可以查看遇到的错误。也许将以下内容添加到您的 WHEN OTHERS 处理程序中会有所帮助：
DBMS_OUTPUT.PUT_LINE('SQLCODE=' || SQLCODE ||
' SQLERRM=''' || SQLERRM || '''');
Share and enjoy.
分享和享受。
回答by Ron Crisco
You can check the value of SQL%ROWCOUNTto find out if a row was actually inserted. I think it would be very bad practice to have a trigger which failed to (or decided not to) insert a record without raising an exception, but it could happen.
您可以检查SQL%ROWCOUNT的值以确定是否实际插入了一行。我认为如果触发器未能（或决定不）插入记录而不引发异常，那将是非常糟糕的做法，但它可能会发生。
Also, your "rollback;" statement is redundant, and should be changed to something like this:
此外，您的“回滚”；语句是多余的，应该改为这样：
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('SQLCODE=' || SQLCODE ||
' SQLERRM=''' || SQLERRM || '''');
RAISE;
END;
The RAISEcommand will raise the same exception that was caught from the trigger so that your calling program will know why it failed (in addition to the debugging of dbms_output).
该RAISE命令将提高这是从触发捕捉，使您的调用程序就会知道为什么会失败（除了DBMS_OUTPUT的调试）相同的异常。
