plsql programming 14 DML和事务管理

我们可以把多个SQL语句集中在一起, 在逻辑上组成一个事务, 从而保证这些操作或者全部被保存到数据库(用sql的说法就是”提交”), 或者被整体驳回(用sql的说法是“回滚”).

事务: ACID

原子性: 改变或者全部发生, 或者全部不发生.

一致性: 正确的状态转换, 不能违反任何完整性约束, 例如 银行汇款

隔离: 从任何一个事务的角度来看, 其他事务看起来都是在它之前或之后发生的.

持久性: 一旦一个事务成功结束, 状态的改变就是永久的了.

DML 语句, insert, update, delete, merge (具体细节已经熟悉, 这里就不罗列了)

DML 操作的游标属性

oracle 准许我们通过一些特殊的隐式游标属性访问最后一次运行的隐式游标的信息.

利用隐式游标属性可以得到最近执行的 insert, update, delete, merge 或者 select into 语句的返回信息.

首先, 隐式游标属性的值总是属于最后一次执行的 sql 语句, 而不管隐式游标是在哪一个块中执行. 在oracle会话打开第一个sql游标之前, 所有隐式游标属性都是 null.(%ISOPEN是个例外, 这个属性返回false)

-- chap14_01.sql 看究竟修改了多少条记录
create or replace procedure change_author_name( old_name_in in books.author%type,
                                                new_name_in in books.author%type,
                                                rename_count_out out pls_integer)
is
begin
    update books
       set author = new_name_in
     where author = old_name_in;

    rename_count_out := sql%rowcount;  -- 只针对DML改变的计数
end;
/
show errors;

假设我们在做 delete, update 等 DML 操作时, 我们还需要提取一些信息做另外处理, 这时我们不用另外的select语句, 直接使用 returning 语句, 就可以提取我们想要的信息, 要知道另外的select语句是需要另外建立游标的, 所以我们这样做减少了游标数量, 降低了CPU的消耗, 参考下例: ( returning 当前游标的任何列 )

declare
    myname    employees.last_name%type;
    mysal    employees.salary%type;
begin
    for rec in (select * from employees)  -- 行记录, 也不需要定义
    loop
        update employees
           set salary = salary * 1.5
         where employee_id = rec.employee_id
        returning salary, last_name into mysal, myname;  -- 以前没用过, 可以return当前游标的任何列

        dbms_output.put_line('New salary for ' || myname || ' = ' || mysal);
    end loop;
end;
/

不仅可以将内容返回在变量中, 当 dml 操作多余 1 条记录时, 也可以将结果返回给一个集合, 使用批量绑定技术, 以前也没用过, 向后再确认吧. 貌似用不到

DML 和 异常处理

• 如果我们的代码使用的是自治事务, 发生异常时必须要执行一个回滚或者提交
• 我们可以通过保存点来控制回滚范围
• 如果一个异常传播到最外层的代码块(也就是说”未处理”的异常), 大多数pl/sql宿主执行环境, 比如 sql*plus, 会自动进行回滚, 所有变化都会被撤销.

DML 和 记录

create or replace procedure set_book_info( book_in in books%rowtype)
is
begin
    insert into books values book_in;
exception
    when dup_val_on_index then
        update books set row = book_in
            where isbn = book_in.isbn;
end;
/
show errors;

上例中, 居然可以直接使用 set row = book_in, 直接整行进行更改…

在 returning 中也可以使用一整行, 例如:

returning isbn, title, summary, author, date_published, page_count into my_book_return_info

( my_book_return_info 类型是 books%rowtype; )

事务管理

commit : 提交

rollback : 回滚

rollback to savepoint : 撤销从指定的保存点以来的所有改变, 并释放这一部分代码使用的锁资源

savepoint : 创建一个保存点, 有了保存点之后我们就可以进行部分回滚操作( SAVEPOINT savepoint_name)

set transaction : 让我们可以启动一个只读或者读写会话, 构建一个隔离级别, 或者为当前的事务分配一个专门的会滚段. (用处不大, 不用理会, 就用默认的就可以了)

lock table : 让我们用指定的模式锁定整个数据库表, 这个命令覆盖了表缺省使用的行级锁. (用处不大)

在我们执行 DML 之前, plsql 都会隐含的生成一个保存点. 如果这个dml语句失败, 会自动放生一个回滚动作, 回滚到隐含的保存点.

set transaction read only :

这个语句把当前事务定义为只读, 在一个只读事务中, 后续的查询能看到的只是在事务开始之前已经提交的变化, 当我们执行一个运行时间很长的, 多个查询组成的报表时, 我们希望确保报表中的数据是一致的, 这个语句就非常有用.

set transaction read write : ( 缺省设置 )

LOCK TABLE table_reference_list IN lock_mode MODE[NOWAIT];

lock_mode : ROW SHARE, ROW EXCLUSIVE, SHARE UPDATE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE

我们应该尽可能地依靠 oracle 缺省的锁定行为, 应用程序应该把 lock table 作为最后一根稻草, 小心使用.

自治事务(个人感觉, 没什么用处, 先放着, 还是有一些用处)

在后面的触发器, 以及动态执行sql语句时, 可以用到自治事务.

触发器: 将触发器的动作写到一个自治事务中, 这样可以独立于操作的DML本身操作的事务管理.

动态SQL: 因为动态SQL可以执行DDL, 而DDL是自动提交的, 这样, 如果在DDL之前有一些DML, 而我们不想提交这些DML操作, 就可以通过自治事务, 将DDL独立起来.

一个PLSQL块被叫做自治事务, 我们实际是把这个块中的DML语句和调用程序的事务环境完全的隔离开. 这个块就称为一个由其他事务启动的独立的事务, 前一个事务就叫做主事务, 在自治事务中, 主事务是挂起的, 即 主事务调用自治事务, 我们执行 sql 操作, 提交或回滚这些操作, 然后重启主事务.

自治事务, 类似 linux 中的 shellscript . 个人感觉: 所谓主事务重启, 其实就是中断返回.

定义自治事务:

PRAGMA AUTONOMOUS_TRANSACTION;  -- 在声明部分 ;

这个编译指令告诉PLSQL编译器把一个PLSQL块编译成自治的或者独立的. 作为自治事务, 可以是下面中的一种:

• 最顶层(不是嵌套的)匿名PLSQL块(这样, 这个块就是一个自治事务)
• 函数或过程, 或者在一个包里定义或者是一个独立的程序(这样, 这个过程和函数就是自治事务)
• 对象类型的方法(函数或过程)
• 数据库触发器(这样, 这个触发器所触发的操作就是自治事务)

自治事务规则和限制

• 如果自治事务要访问的资源已经被主事务(主事务已经暂停, 要等自治过程退出才能继续)持有, 我们的程序就放生了死锁. 例如:

procedure update_salary(dept_in IN number)
is
  PRAGMA AUTONOMOUS_TRANSACTION;
  cursor myemps is
    select empno from emp
    where deptno = dept_in
      for update nowait;
begin
  for rec in myemps loop
    update emp set sal = sal * 2
     where empno = rec.empno;
  end loop;
  commit;   -- 因为是自治事务
end;

-- 运行
begin
  update emp set sal = sal * 2;
  update_salary(10);
end;

以上代码的运行部分在一个块内, 以上代码运行时, 会出错. 因为上例中, 资源已经被主事务所占有, 即 update 已经修改了 emp 表, 这时候如果自治事务再去抢夺主环境已经占有的资源, 就会出错.

• 我们不能只用一个 PRAGMA 声明就把一个包中的所有子程序(或者一个对象类型中的所有方法)全部标成自治的, 我们必须对位于包体的每个程序的声明单元都明确的指定自治事务. 这个规则的一个后果是我们无法通过包规范来区分到底哪一个程序是自治事务的.
• 如果想从一个已经执行了至少一个 insert, update, merge, delete 语句的自治事务程序没有任何错误的退出, 我们必须明确的执行一个提交或者回滚.
• commit 和 rollback 语句只结束了活动的自治事务, 但不会终止自治例程, 即程序还要继续向下走(这是当然)实际上, 我们可以在一个自治块中使用多个commit或rollback.
• 我们只能回滚到当前会话创建的保存点, 如果是在一个自治事务中, 我们不能回滚到主事务创建的保存点.
• 数据库参数文件中的 TRANSACTIONS 参数指定的是一个会话内可以并发的最大事务数量.

事务的可见性

自治事务的缺省行为是, 只要在自治事务中执行了 commit 或者 rollback, 这些改变立即对主事务可见, 但是如果我们想对主事务隐藏, 就需要设置 set transaction isolation level serializable; 这个设置影响整个会话.

什么时候使用自治事务

只要我们想把模块内的修改和外层调用者的事务环境隔离开, 就需要对我们的程序模块定义自治事务.

日志机制: 一方面我们需要把错误信息保存(commit), 另一方面, 我们需要回滚当前错误所造成的数据库数据错误(rollback)

触发器执行提交和回滚: 通过把触发器定义成自治事务, 我们就可以在触发器内部执行提交和回滚, 而不影响触发该触发器的事务, 比如, 我们想对表上的每一个操作进行跟踪, 不管这个操作完成与否, 我们甚至还想知道哪些操作失败了.

可重用的应用组件: 自治事务的核心价值, 现在的internet世界已经分散的多层架构, 用不影响调用环境的独立工作单元处理任务变得越来越重要.

避免查询时出现突变表触发器错误: 如果一个行级触发器中试图读取或者写入触发表就会发生突变表触发器错误. 不过, 如果通过使用 PRAGMA AUTONOMOUS_TRANSACTION 让触发器成为自治事务, 并且在触发器内部提交, 我们就可以查询出发表的内容( 只是查询, 不能修改)

在修改这个表的SQL中调用用户自定义函数：

重试计数器: 假设我们在彻底拒绝用户对某个资源的访问之前, 准许用户做 N 次尝试.