oracle INSERT INTO + COMMIT 在包中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4105910/
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
INSERT INTO + COMMIT in packages
提问by LeftyX
what is the best solution to write a oracle package for record persistence? I've always written something like this:
编写用于记录持久性的 oracle 包的最佳解决方案是什么？我一直在写这样的东西：
create or replace
PACKAGE BODY "USP_PRICELIST" AS
PROCEDURE usp_TABLE1Save
(
pErrorCode OUT NUMBER,
pMessage OUT VARCHAR2,
pPARAM1 IN CHAR,
pPARAM2 IN CHAR
)
IS
BEGIN
pErrorCode := 0;
INSERT INTO TABLE1
(PARAM1, PARAM2)
VALUES
(pPARAM1, pPARAM2);
EXCEPTION
WHEN OTHERS THEN pErrorCode := SQLCODE; pMessage := SQLERRM;
END usp_TABLE1Save;
END USP_PRICELIST;
and I was wondering if I have to COMMIT after the INSERT INTO.
我想知道我是否必须在 INSERT INTO 之后提交。
Alberto
阿尔贝托
回答by Tony Andrews
I would notput a commit in the procedure, and leave that to the code that calls the procedure. This allows the procedure to be used as part of a larger transaction. The insert is not implicitly committed.
我会不会把在程序中提交，并留给调用程序代码。这允许将过程用作更大事务的一部分。插入不是隐式提交的。
回答by vc 74
It really depends on whether you want your operation to take part in a transaction or to be atomic.
这实际上取决于您希望操作参与事务还是原子操作。
回答by Harrison
Be careful, if you place the commit in the package it will commit the entire transaction
请注意，如果您将提交放在包中，它将提交整个事务
create table testcommit (colA varchar2(50)) ;
DECLARE
PROCEDURE SELFCOMMIT(VAL IN TESTCOMMIT.COLA%TYPE) AS
BEGIN
INSERT INTO TESTCOMMIT(COLA) VALUES(VAL);
COMMIT ;
END SELFCOMMIT ;
PROCEDURE NOCOMMIT(VAL IN TESTCOMMIT.COLA%TYPE) AS
BEGIN
INSERT INTO TESTCOMMIT(COLA) VALUES(VAL);
END NOCOMMIT ;
BEGIN
INSERT INTO TESTCOMMIT(COLA) VALUES('INITIAL');
SELFCOMMIT('FIRST SELF COMMIT');
ROLLBACK ; --KILL TRANSACTION
INSERT INTO TESTCOMMIT(COLA) VALUES('SECOND MAIN INSERT');
NOCOMMIT('NO AUTO COMMIT');
ROLLBACK;
END ;
/
SELECT * FROM TESTCOMMIT;
-->
COLA
--------------------------------------------------
INITIAL
FIRST SELF COMMIT
-->NOTE THE SELFCOMMIT AFFECTS THE ENTIRE TRANSACTION, THUS RENDERING THE ROLLBACK MOOT
--drop table testcommit;
回答by EvilTeach
You should also look at the concept of autonomous transactions
你还应该看看自主交易的概念
回答by heximal
By default Oracle has no auto-commit, so you have to.
默认情况下，Oracle 没有自动提交，因此您必须这样做。
