PL/SQL 事务处理(Transaction Processing)

开启事务

当执行一组 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 
版权声明:本文为博主原创文章,转载请附上博文链接!

展开阅读全文

没有更多推荐了,返回首页