Plsql 简明教程

PL/SQL - Transactions

在本章中，我们将讨论 PL/SQL 中的事务。数据库 transaction 是一个工作原子单元，可能由一个或多个相关的 SQL 语句构成。称为原子单元是因为构成事务的 SQL 语句所产生的数据库修改可以共同提交，即永久保存在数据库中或从数据库回滚（撤销）。

In this chapter, we will discuss the transactions in PL/SQL. A database transaction is an atomic unit of work that may consist of one or more related SQL statements. It is called atomic because the database modifications brought about by the SQL statements that constitute a transaction can collectively be either committed, i.e., made permanent to the database or rolled back (undone) from the database.

成功执行的 SQL 语句和提交的事务不同。即使 SQL 语句成功执行，但除非提交包含该语句的事务，否则可以回滚该语句并撤销该语句所做的所有更改。

A successfully executed SQL statement and a committed transaction are not same. Even if an SQL statement is executed successfully, unless the transaction containing the statement is committed, it can be rolled back and all changes made by the statement(s) can be undone.

Starting and Ending a Transaction

事务有 beginning 和 end 。当发生以下事件之一时，事务开始：

A transaction has a beginning and an end. A transaction starts when one of the following events take place −

  1. The first SQL statement is performed after connecting to the database.

  2. At each new SQL statement issued after a transaction is completed.

当发生以下事件之一时，事务结束：

A transaction ends when one of the following events take place −

  1. A COMMIT or a ROLLBACK statement is issued.

  2. A DDL statement, such as CREATE TABLE statement, is issued; because in that case a COMMIT is automatically performed.

  3. A DCL statement, such as a GRANT statement, is issued; because in that case a COMMIT is automatically performed.

  4. User disconnects from the database.

  5. User exits from SQL*PLUS by issuing the EXIT command, a COMMIT is automatically performed.

  6. SQL*Plus terminates abnormally, a ROLLBACK is automatically performed.

  7. A DML statement fails; in that case a ROLLBACK is automatically performed for undoing that DML statement.

Committing a Transaction

通过发出 SQL 命令 COMMIT 使事务永久生效。COMMIT 命令的常规语法为：

A transaction is made permanent by issuing the SQL command COMMIT. The general syntax for the COMMIT command is −

COMMIT;

例如，

For example,

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2, 'Khilan', 25, 'Delhi', 1500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (3, 'kaushik', 23, 'Kota', 2000.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (4, 'Chaitali', 25, 'Mumbai', 6500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (5, 'Hardik', 27, 'Bhopal', 8500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (6, 'Komal', 22, 'MP', 4500.00 );

COMMIT;

Rolling Back Transactions

在不执行 COMMIT 的情况下对数据库所做的更改可以使用 ROLLBACK 命令撤销。

Changes made to the database without COMMIT could be undone using the ROLLBACK command.

ROLLBACK 命令的常规语法为：

The general syntax for the ROLLBACK command is −

ROLLBACK [TO SAVEPOINT < savepoint_name>];

当事务由于系统故障等前所未有的情况而中止时，从上次提交后整个事务都会自动回滚。如果不使用 savepoint ，则只需使用以下语句回滚所有更改：

When a transaction is aborted due to some unprecedented situation, like system failure, the entire transaction since a commit is automatically rolled back. If you are not using savepoint, then simply use the following statement to rollback all the changes −

ROLLBACK;

Savepoints

保存点是一种标记，有助于通过设置一些检查点将一个较长的事务拆分为较小的单元。通过在较长的事务中设置保存点，可以在需要时回滚到检查点。这是通过发出 SAVEPOINT 命令来完成的。

Savepoints are sort of markers that help in splitting a long transaction into smaller units by setting some checkpoints. By setting savepoints within a long transaction, you can roll back to a checkpoint if required. This is done by issuing the SAVEPOINT command.

SAVEPOINT 命令的一般语法是：

The general syntax for the SAVEPOINT command is −

SAVEPOINT < savepoint_name >;

例如

For example

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (7, 'Rajnish', 27, 'HP', 9500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (8, 'Riddhi', 21, 'WB', 4500.00 );
SAVEPOINT sav1;

UPDATE CUSTOMERS
SET SALARY = SALARY + 1000;
ROLLBACK TO sav1;

UPDATE CUSTOMERS
SET SALARY = SALARY + 1000
WHERE ID = 7;
UPDATE CUSTOMERS
SET SALARY = SALARY + 1000
WHERE ID = 8;

COMMIT;

ROLLBACK TO sav1 − 此语句回滚至标记了保存点 sav1 的那一点的所有更改。

ROLLBACK TO sav1 − This statement rolls back all the changes up to the point, where you had marked savepoint sav1.

之后，您所做的更改将开始。

After that, the new changes that you make will start.

Automatic Transaction Control

要设置只要执行 COMMIT 或 INSERT, UPDATE 命令时，自动执行 DELETE ，您可以设置 AUTOCOMMIT 环境变量，如下所示：

To execute a COMMIT automatically whenever an INSERT, UPDATE or DELETE command is executed, you can set the AUTOCOMMIT environment variable as −

SET AUTOCOMMIT ON;

您可以使用以下命令关闭自动提交模式：

You can turn-off the auto commit mode using the following command −

SET AUTOCOMMIT OFF;