PL/SQL存储过程是否是事务？-ArePL/SQLstoredprocedurestransactions?

3  

Are PL/SQL stored procedures transactions? When a trigger, procedure or function runs, is it a transaction?

PL / SQL存储过程是否是事务?当触发器,过程或函数运行时,它是一个事务吗?

No. A transaction starts with the first DML encountered and ends when it encounters a COMMIT or ROLLBACK. A transaction can comprise many function and procedure calls and DML statements and trigger code. On the other hand, you can create a procedure that comprises of many transactions by issuing lots of COMMITs.

否。事务从遇到的第一个DML开始,在遇到COMMIT或ROLLBACK时结束。事务可以包含许多函数和过程调用以及DML语句和触发器代码。另一方面,您可以通过发出大量COMMIT来创建包含许多事务的过程。

I usually end my procedures with COMMIT and when an error occurs I use ROLLBACK! Is it wrong?

我通常用COMMIT结束我的程序,当发生错误时我使用ROLLBACK!这是错的吗?

Wrong is a strong word. Let's just say it's not a good practice. Making (packaged) functions and procedures is all about modularization: making reusable pieces of code. When a function/procedure contains ROLLBACK or COMMIT statements, it stops being reusable as it messes up the transaction of the caller. So it's better not to use ROLLBACK or COMMIT in your procedures and leave it to the topmost caller.

错误是一个强有力的词。我们只是说这不是一个好习惯。制作(打包)函数和过程都是关于模块化的:制作可重用的代码片段。当函数/过程包含ROLLBACK或COMMIT语句时,它会停止可重用,因为它会扰乱调用者的事务。因此,最好不要在程序中使用ROLLBACK或COMMIT,并将其留给最顶层的调用者。

You could use SAVEPOINTS throughout your code which makes sure a single function or procedure doesn't leave open parts of a transaction. But for esthetical reasons I prefer to not use SAVEPOINTS. For me, it's just five lines of unnecessary code, because I know my caller function will handle the transaction just nicely.

您可以在整个代码中使用SAVEPOINTS,这样可以确保单个函数或过程不会保留事务的开放部分。但出于美学原因,我宁愿不使用SAVEPOINTS。对我来说,这只是五行不必要的代码,因为我知道我的调用函数会很好地处理事务。

Exception is when you create an autonomous procedure, which is by definition a single transaction and thus needs to end with a COMMIT.

例外情况是您创建一个自治过程,根据定义,它是一个单独的事务,因此需要以COMMIT结束。

UPDATE

Note that a RAISE_APPLICATION_ERROR or a RAISE [exception name] statement will also automatically rollback your PL/SQL block as a single atomic unit. Which is of course a desirable effect as it doesn't leave you with uncommitted changes.

请注意,RAISE_APPLICATION_ERROR或RAISE [exception name]语句也会自动将PL / SQL块作为单个原子单元回滚。这当然是一个理想的效果,因为它不会给你带来未提交的更改。

SQL> create table mytable (id int)
  2  /

Table created.

SQL> create procedure p
  2  as
  3  begin
  4    insert into mytable values (2);
  5    raise_application_error(-20000,'My exception');
  6  end;
  7  /

Procedure created.

SQL> select *
  2    from mytable
  3  /

no rows selected

SQL> insert into mytable values (1)
  2  /

1 row created.

SQL> exec p
BEGIN p; END;

*
ERROR at line 1:
ORA-20000: My exception
ORA-06512: in "X.P", regel 5
ORA-06512: in regel 1


SQL> select *
  2    from mytable
  3  /

        ID
----------
         1

1 row selected.

0  

Off course its not a transaction but a modification. And you dont need to specify Commit everytime to save your transaction. In Oracle for example all individual DML statements are atomic (i.e. they either succeed in full, or rollback any intermediate changes on the first failure) (unless you use the EXCEPTIONS INTO option, which I won't go into here). Consider an example above: If you wish a group of statements to be treated as a single atomic transaction, you'd do something like this:

当然,这不是交易,而是修改。而且您不需要每次都指定Commit来保存您的交易。例如,在Oracle中,所有单独的DML语句都是原子的(即它们要么完全成功,要么在第一次失败时回滚任何中间更改)(除非你使用EXCEPTIONS INTO选项,我不会在这里讨论)。考虑上面的一个例子:如果您希望将一组语句视为单个原子事务,您可以执行以下操作:

BEGIN
  SAVEPOINT start_tran;
  INSERT INTO .... ; -- first DML
  UPDATE .... ; -- second DML
  BEGIN ... END; -- some other work
  UPDATE .... ; -- final DML
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK TO start_tran;
    RAISE;
END;

That way, any exception will cause the statements in this block to be rolled back, but any statements that were run prior to this block will not be rolled back.

这样,任何异常都将导致回滚此块中的语句,但不会回滚在此块之前运行的任何语句。

Note that I don't include a COMMIT - usually I prefer the calling process to issue the commit.

请注意,我不包含COMMIT - 通常我更喜欢调用进程来发出提交。

0  

PL/SQL is a Modification, not a transaction. A Transaction is a list of Modifications and Read-only operations. If you have a

PL / SQL是修改,而不是事务。事务是修改和只读操作的列表。如果你有

update
insert
pl/sql
update
rollback

the PL/SQL will not make a change to the database, even if the pl/sql is executed.

即使执行了pl / sql,PL / SQL也不会对数据库进行更改。