PL/SQL 事务处理（Transaction Processing）

最新推荐文章于 2023-04-17 22:39:55 发布

chenglaozong

最新推荐文章于 2023-04-17 22:39:55 发布

阅读量801

点赞数

开启事务

当执行一组 SQL 语句的时候，Oracle 会自动帮我们开启一个事务。我们也可以通过 SET TRANSACTION 语句手动开启一个事务，下面是一个简单的例子。


-- 手动开启事务 tran
SET TRANSACTION NAME 'tran';
--SET TRANSACTION READ WRITE NAME 'tran'; -- 这条语句和上面的语句完全相同，表明它是一个读写事务
INSERT INTO TEST VALUES ('Scott');

SET TRANSACTION 还有许多其他参数，让我们可以对事务进行更精准的控制。


-- 我们可以设置事务为只读事务，这在生成报告，账单等时特别有用
SET TRANSACTION READ ONLY NAME 'tran';
SELECT * FROM TEST;
COMMIT; -- 提交事务，只读事务也需要提交的哦
 
 
-- 我们还可以指定事务的隔离级别
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE NAME 'tran';
-- SET TRANSACTION ISOLATION LEVEL READ COMMITTED NAME 'tran'; -- 事务默认的隔离级别是 READ COMMITTED
SELECT * FROM TEST;
COMMIT; -- 提交事务
 
-- 我们还可以指定当事务失败时，将事务回滚到指定的回滚段
-- Oracle 不推荐我们这样做，尽量使用自动回滚
SET TRANSACTION USE ROLLBACK SEGMENT test NAME 'tran';
INSERT INTO TEST VALUES ('Scott');

提交事务

Oracle 使用 COMMIT 语句提交事务，它也有许多参数，让我们可以根据不同的情况，对提交事务进行更精准的控制。


INSERT INTO TEST VALUES ('Scott');
COMMIT; -- 提交事务
COMMIT COMMENT 'test'; -- COMMENT 语句为事务添加注释，但Oracle不推荐我们使用它，它的存在是为了向后兼容。
COMMIT WORK; -- COMMIT 和 COMMIT WORK 完全等价
COMMIT WRITE WAIT IMMEDIATE; -- 这条语句和上面的语句完全相同
COMMIT WRITE WAIT BATCH;
COMMIT WRITE NOWAIT BATCH;
COMMIT WRITE NOWAIT IMMEDIATE

上面的语句中出现了几个关键字 WRITE，WAIT，NOWAIT，IMMEDIATE 和 BATCH。那么它们是什么意思呢？这还要从 Oracle 是如何保证事务一致性说起。通常，Oracle 会先写入日志，然后提交事务，如果事务在提交的过程中挂了，Oracle 会根据日志来恢复数据库，如果事务提交成功了，Oracle 会返回成功的消息。上面的语句中 WAIT 表示等待日志输入完成，然后返回成功的消息。NOWAIT 表示不等待日志输入完成就返回给成功的消息。IMMEDIATE 表示立刻输出日志到磁盘，BATCH 表示批量输出日志到磁盘。所以下面的语句表示批量输出日志到磁盘，但不等输出日志完成就返回给成功的消息。这样可以极大的提高性能，但是是有风险的，在极端情况下，事务失败了，但用户收到了事务提交成功的消息。

COMMIT WRITE NOWAIT BATCH;

此外，我们还可以为 COMMIT 添加 FORCE 语句，它可以让我们对分布式事务进行手动控制。更多信息参见 Oracle 手册。

回滚事务

Oracle 使用 ROLLBACK 来回滚事务。

ROLLBACK;
ROLLBACK WORK; -- ROLLBACK 和 ROLLBACK WORK 完全相同
ROLLBACK TO SAVEPOINT test; -- 回滚到指定的回滚点
ROLLBACK FORCE '25.32.87'; -- FORCE 语句用来手动控制分布式事务回滚。

回滚点

Oracle 使用 SAVEPOINT 来设置回滚点。

INSERT INTO TEST VALUES ('Scott');
SAVEPOINT sp1;
INSERT INTO TEST VALUES ('Tom');
ROLLBACK TO SAVEPOINT sp1; -- 回滚到sp1，'Tom' 记录会被回滚
COMMIT;

手动加锁（LOCK TABLE）
通常，Oracle 会根据需要自动锁定表或行，如果我们要对一个表的大部分记录进行操作，锁定每一行会严重消耗系统资源，这个时候我们可以通过 LOCK TABLE 语句手动锁定整个表。例如下面的语句表示使用 EXCLUSIVE 模式锁定 TEST 表，如果其他事务已经锁定了该表，立刻返回。

-- EXCLUSIVE 表示使用独占模式锁定表，其他事务只允许读表。
-- NOWAIT 表示如果其他事务已经锁定了该表，立刻返回。
LOCK TABLE TEST IN EXCLUSIVE MODE NOWAIT;

SELECT FOR UPDATE

如果我们查询某些记录是为了更新它，那么我们可以给 SELECT 语句加上 FOR UPDATE 子句，这样 Oracle 会返回结果的同时锁定它，从而防止别人更新这些记录, 下面是一个简单的例子。

DECLARE
 
  CURSOR c1 IS
  SELECT employee_id, salary
  FROM employees FOR UPDATE OF salary;
  
BEGIN
  NULL;
END;

自主事务
通常，如果子程序 A 调用 B，那么 A 和 B 将在同一个事务中，A 或 B 中的任意一个 COMMIT 语句将会使 A 和 B 所做的所有更改全部提交。有时候，这不是我们想要的结果，我们想让 B 在自己的事务中，无论 A 成功或失败都不影响 B，怎么办？很简单，我们只需在 B 的申明部分加上 PRAGMA AUTONOMOUS_TRANSACTION 语句即可，怎么样，简单吧，下面是一个简单的例子。

-- 存储过程 B    
CREATE OR REPLACE PROCEDURE INSERT_LOG (      
  MESSAGE VARCHAR2    
)      
IS    
-- 表示该存储过程是一个自主事务存储过程    
PRAGMA AUTONOMOUS_TRANSACTION;    
BEGIN    
  INSERT INTO LOG VALUES (MESSAGE);    
  COMMIT;    
END;   
  
-- 存储过程 A  
CREATE OR REPLACE PROCEDURE INSERT_EMPLOYEE (    
  FIRST_NAME         VARCHAR2,    
  LAST_NAME          VARCHAR2,     
  SALARY             NUMBER  
)    
IS    
BEGIN  
  -- 开启事务  
  COMMIT;  
  SET TRANSACTION NAME 'tran';  
    
  -- 设置回滚点  
  SAVEPOINT sp1;  
    
    -- 调用存储过程  
    INSERT_LOG('Inserting EMPLOYEES');  
  
    INSERT INTO EMPLOYEES VALUES (NULL, FIRST_NAME, LAST_NAME, SALARY, CURRENT_TIMESTAMP);  
      
    EXCEPTION    
    WHEN OTHERS THEN    
      DBMS_OUTPUT.PUT_LINE('ERROR CODE:' || SQLCODE || ', ERROR MESSAGE: ' || SQLERRM);    
      ROLLBACK TO sp1; -- 回滚  
END;  
  
-- 测试  
DECLARE  
  FIRST_NAME    VARCHAR2(20) := 'BO';  
  LAST_NAME    VARCHAR2(20) := 'SHANG';   
    
BEGIN    
  INSERT_EMPLOYEE(FIRST_NAME, LAST_NAME, 8888.88);      
END;

作者：shangboerds
来源：CSDN
原文：https://blog.csdn.net/shangboerds/article/details/43282115
版权声明：本文为博主原创文章，转载请附上博文链接！