PL/SQL 训练04--事务

--pl/sql通过SQL和ORACLE数据库紧密的整合在一起
--在pl/sql中可以执行任何操作语句（DML语句）,包括INSERT,UPDATE,DELETE,MERGE,也包括查询语句
--可否执行DDL语句呢？
--不可以直接执行，但可以通过动态SQL的方式执行，关于动态SQL，后面课程会专门拿一节课来讲

--事务的ACID原则：原子性，一致性，隔离性，持久性

--原子性：事务所涉及的改变是原子的：这些改变或者全部发生或者全部不发生

--一致性：一个事务必须是一个正确的状态转换。事务中发生的行为作为一个整体不能违反状态的任何完整性约束

--隔离：很多事务可以同时发生，不过从任何一个事务的角度看，其他的事务看起来都在它之前或之后发生的

--持久性：一旦一个事务成功结束，状态的改变是永久的，可能经受住以后发生的任何故障

--COMMIT或ROLLBACK，一个事务可以通过执行COMMIT保存，或者ROLLBACK回滚。
--资源上的锁释放
--事务和会话的关系：默认每个会话中只有一个事务。所有修改都属于当前事务的一部分。
--自制事务特性：可以在会话的住事务中嵌套其它事务

--建立日志表

create table ma_user_log

( created_by     varchar2(100) default 'system' not null,

  created_date   date default sysdate not null,

  updated_by     varchar2(100) default 'system' not null,

  updated_date   date  default sysdate not null,

  id_ma_user_log varchar2(32) default sys_guid() not null,

  user_name      varchar2(100) not null,

  trace_mark     varchar2(1000) );

create or replace procedure test_pragma(i_user         in varchar2,

                                        i_trace_remark in varchar2) is

  pragma autonomous_transaction; --定义自制事务

begin

  insert into ma_user_log

    (user_name, trace_mark)

    select i_user, i_trace_remark from dual;

  --insert into ma_user_log(user_name, trace_mark)values(user_name,i_trace_remark);

  commit;

end test_pragma;

/

declare

  cursor cur_users is

    select * from ma_users r where r.user_status = ''; --当前用户对表有查询权限

begin

  for v in cur_users loop

    update ma_users r --注意对此表进行操作需要有UPDATE的权限

       set r.user_point = 100

     where r.user_name = v.user_name;

    test_pragma(v.user_name, '数据修改，用户积分初始值100');

  end loop;

end;

/

select * from ma_user_log;

--dml语句的快速入门

--insert语句：向表中插入一条或者多条记录了

insert into ma_user_log(user_name, trace_mark)values('test','test test'); --插入一条数据

insert into  ma_user_log(user_name, trace_mark)select 'test1', 'test1test1' from dual;--插入一条数据

insert into  ma_user_log(user_name, trace_mark)

select t.user_name,t.user_remark from ma_users t where t.user_status ='';--插入多条数据

insert into ma_user_log--必须写出所有的列且一一对应

  select 'system',

         sysdate,

         'system',

         sysdate,

         sys_guid(),

         t.user_name,

         t.user_remark

    from ma_users t

   where t.user_status = '';--插入多条数据

--UPDATE语句:更新一行或多行的一或多列

update ma_users t

set t.user_point = 100,t.user_status =''

where t.user_name ='乱世佳人';

--DELETE语句：删除一个表的一行、多行、或者所有记录行

delete from ma_user_log ;

delete from ma_user_log where 1=1;

create or replace procedure del_user_log(i_date in date,o_log_num out number)

is

begin

   delete from ma_user_log t where t.created_date < i_date ;

   o_log_num := sql%rowcount ;

end ;

/

declare

   v_num  number ;

begin 

  del_user_log(sysdate,v_num);

  dbms_output.put_line('删除'||v_num||'个记录');

end ;

/

--MERGE 语句:指定一个匹配条件，然后针对匹配和不匹配的记录分别采取不同的行为

declare

begin

  merge into ma_user_log mu

  using (select * from ma_users) t

  on (mu.user_name = t.user_name)

  when matched then

    update set mu.trace_mark = 'hello tt'

  when not matched then

    insert (mu.user_name, mu.trace_mark) values (t.user_name, 'hello'||t.user_name);

end;

/

select * from ma_user_log;

---dml操作的游标属性
--通过一些特殊的隐式游标属性访问最后一次运行的隐式游标的信息
--sql%found:如果有一行或多行记录被成功修改（包括创建、修改、删除）返回TRUE
--sql%notfound：如果DML语句没有修改任何行则返回TRUE
--sql%rowcount:返回DML语句修改的记录行数
--sql%isopen:对于隐式游标(及DML语句)总是返回FALSE，因为ORACLE数据库会自动打开和关闭这些游标

create or replace procedure update_point(i_user in varchar2,

                                         o_bool out boolean,

                                         o_num  out number)

 is

begin

  update ma_users t set t.user_point = 100 where t.user_name = i_user;

  o_bool := sql%found;

  o_num  := sql%rowcount;

end;

/

declare

  v_bool boolean;

  v_num  number;

begin

  update_point('乱世佳人', v_bool, v_num);

  if v_bool then

    dbms_output.put_line('true');

  else

    dbms_output.put_line('false');

  end if;

  dbms_output.put_line(v_num);

  update_point('乱世佳', v_bool, v_num);

  if v_bool then

    dbms_output.put_line('true');

  else

    dbms_output.put_line('false');

  end if;

  dbms_output.put_line(v_num);

end;

/

--returning:从dml语句返回信息
--可以从insert,update,delete,merge语句中添加一个RETURNING字句,返回信息到一个变量中
--不需要单调去查询

declare

  v_phone ma_users.user_name%type;

  v_email ma_users.user_email%type;

begin

  for v in (select * from ma_users) loop

    update ma_users r

       set r.user_point = 1000

     where r.id_ma_users = v.id_ma_users

    returning r.user_phone, r.user_email into v_phone, v_email;

    --select r.user_phone, r.user_email into v_phone, v_email from ma_users r where r.id_ma_users = v.id_ma_users

    dbms_output.put_line(v.user_name || '-' || v_phone || '-' || v_email);

  end loop;

end;

/

--如果UPDATE语句修改返回的记录函数多于一行，可以使用BULK COLLECT返回到一个集合中

declare

  type point_t is table of number;

  v_point point_t;

begin

  update ma_users t

     set t.user_point = 200*(sysdate - t.user_birth_date)

   where 1 = 1

  returning t.user_point bulk collect into v_point;

  for i in v_point.first..v_point.last loop

      dbms_output.put_line(v_point(i));

  end loop ;

end;

/

--异常处理
--如果一个PL/SQL块出现异常时,oracle数据库不会回滚这个块中DML语句所做的修改
--需要我们去决定采取什么行动

create or replace procedure del_users(i_user in varchar2,o_count out number )

is 

begin 

   select count(1) into o_count from ma_users ;

   --o_count:= 3;

   delete from ma_users mu where mu.user_name = i_user;

   raise no_data_found ;

end ;

/

declare

  v_count number :=-1;

begin

  del_users('乱世佳人1', v_count);

  dbms_output.put_line('1=='||v_count);--不会被打印，直接进入异常

exception

  when others then

    dbms_output.put_line('2=='||v_count);

    select count(1) into v_count from ma_users;

     dbms_output.put_line('3=='||v_count);

end;

/

declare

  v_count number :=-1;

begin

  del_users('乱世佳人1', v_count);

  dbms_output.put_line(v_count);

end;

/

select count(1) from ma_users;

--有几点需要注意
--如果代码块中使用的是自治事务，在发生异常时需要执行回滚或者提交
--可以通过SAVEPOINT来控制回滚的范围。可以回滚到某个特殊SAVEPOINT，
--从而把会话所做出的改变部分保存下来
--如果一个异常传播到最外层的代码块，多数PL/SQL执行环境比如SQL*PLUS，都会自动回滚
--所有变化都会被撤销

--基于记录的DML

create or replace procedure save_user(i_user ma_users%rowtype) is

begin

  insert into ma_users values i_user;

exception

  when dup_val_on_index then

    update ma_users t

       set row = i_user

     where t.user_name = i_user.user_name;

end;

/

declare

  v_user ma_users%rowtype;

begin

  v_user.created_by      := 'system';

  v_user.created_date    := sysdate;

  v_user.updated_by      := 'system';

  v_user.updated_date    := sysdate;

  v_user.id_ma_users     := sys_guid();

  v_user.user_name       := '幸运小子2';

  v_user.user_password   := 'text123';

  v_user.user_sex        := '';

  v_user.user_phone      := '223aa3333';

  v_user.real_name       := 'xinyuan';

  v_user.identity_no     := '';

  v_user.user_email      := 'test@163.com';

  v_user.user_address    := 'hhhhhhh';

  v_user.user_birth_date := date '1986-01-01';

  v_user.user_status     := '';

  v_user.user_remark     := 'sss';

  v_user.user_point      := 1000;

  v_user.register_date   := sysdate;

  save_user(v_user);

end;

/

select * from ma_users;

declare

  type test_record is record(

    user_name  ma_users.user_name%type,

    user_point ma_users.user_point%type);

  v_record test_record;

begin

  update ma_users t

     set t.user_point = t.user_point + 100

   where t.user_name = '幸运小子'

  returning t.user_name, t.user_point into v_record;

  dbms_output.put_line(v_record.user_name || '累计积分' || v_record.user_point);

end;

/

--事务管理

--commit:保存上一个commit或者rollback以来发生的所有变化，并且释放锁资源
--commit会释放会话中使用的任何行锁和表锁，比如使用SELECT FOR UPDATE添加的
--同时会把自上一个COMMIT或ROLLBACK语句以来创建的所有SAVEPOINT都清除
COMMIT ;
COMMIT WORK;
COMMIT COMMENT 'THIS IS A COMMENT';

--ROLLBACK 语句
--撤销从上一个commit或者rollback以来发生的所有变化,并且释放锁资源
rollback ;
rollback work;
rollback to savepoint_name;

--savepoint:创建一个保存点，有了保存点后可以进行部分回滚操作
savepoint savepoint_name;
--rollback回滚到某个保存点，这个保存点之后的改变全部撤销并释放资源
--不过在这点之前的改变以及锁仍然保留

--savepoin没有所谓作用范围一说

DECLARE

  test_savepoint_exp EXCEPTION;

  v_count number;

BEGIN

  update ma_users t

     set t.user_point = t.user_point + 100

   where t.user_name = '幸运小子';

  SAVEPOINT TEST_SAVEPOINT;

   update ma_users t

     set t.user_point = t.user_point + 100

   where t.user_name = '幸运小子';

  SAVEPOINT TEST_SAVEPOINT;

   update ma_users t

     set t.user_point = t.user_point + 100

   where t.user_name = '幸运小子';

  raise test_savepoint_exp;

exception

  when test_savepoint_exp then

    rollback to TEST_SAVEPOINT;

    select t.user_point

      into v_count

      from ma_users t

     where t.user_name = '幸运小子';

    dbms_output.put_line(v_count);

END;

/

--set transaction

--启动一个只读或者读写会话，构建一个隔离级别，或者为当前的事务分配一个专门的回滚段

set transaction read only

--把当前事务定义成只读的，后续的查询看到的只是这个事务开始之前的已经提交的变化

set transaction read write

--把当前事务定义成可读写的，并且这是缺省设置

set transaction isolation level serializable | read commit ;

--定义修改数据库的事务是如何处理的

--如果是serializable，则dml语句已经被另一个尚未提交的事务修改了，这个语句就会失败。

--这个命令要求数据库的初始化参数COMPATIBLE必须设置为7.3.0或者更高的值

--如果是READ COMMIT,一个dml语句请求的行级已经被另一个事务持有，则这个语句要一直等待锁被释放

--缺省行为

set transaction use rollback segment rollback_segname;

--为当前事务指定一个专门的回滚段，并把事务设置成可读写。不能和第一个命令一起使用

declare

  v_count number;

begin

  set transaction read only;

  select t.user_point

    into v_count

    from ma_users t

   where t.user_name = '幸运小子';

  dbms_output.put_line(v_count);

end;

/

 select t.user_point

    from ma_users t

   where t.user_name = '幸运小子';

--lock talbe
--用指定的模式锁定这个数据库表
lock table table_reference_list in lock_mode mode [nowait];
--lock mode
row share --行共享允许其他用户同时更新其他行，允许其他用户同时加共享锁，不允许有独占（排他性质）的锁
row exclusive --行独占行排他，允许其他用户同时更新其他行，只允许其他用户同时加行共享锁或者行排他锁
share --共享锁不允许其他用户同时更新任何行，只允许其他用户同时加共享锁或者行共享锁
share row exclusive --共享行排他，允许其他用户同时更新其他行，只允许其他用户同时加行共享锁
exclusive --排他，其他用户禁止更新任何行，禁止其他用户同时加任何锁
--自治事务
--定义自治事务
--在声明单元加上以下语句
pragma autonomous_transaction;

--把一个块定义成自治事务，实际是把这个块中的DML语句和调用程序的事务环境完全的隔离开
--这个块就成为一个由其它事务启动的独立事务，前一个事务叫做主事务

--被定义程自治事务的块可以是
--最顶层的匿名块
--函数或者过程，或者在包里定义或者是一个独立的程序
--对象类型的方法（函数或者方法）
--数据库触发器

--自治事务的规则和限制
--如果自治事务的要访问的资源已经被主事务持有，程序就会发生死锁
--不能只用一个PRAGMA声明一个包中所有的子程序全部标识程自治的。必须对于包体中每个程序声明单元
--都明确指定自治事务

--如果想从一个已经执行了至少一个INSERT、update,merge,delete语句的自治事务程序没有任何
--错误的退出，必须明确的执行一个提交或者回滚

--commit和rollback语句只是结束了活动的自治事务，但不会终止自治例程。在一个自治块中
--可以使用多个commit或者ROLLBACK语句

--在一个自治事务中，不能回滚到主事务创建的SAVEPOINT

--自治事务提交后，对主事务可见

create or replace PROCEDURE UPDATE_USER(I_USER  IN VARCHAR2,

                                        i_point in number) IS

  PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

  UPDATE MA_USERS R

     SET R.USER_POINT = R.USER_POINT + i_point

   WHERE R.USER_NAME = I_USER;

    dbms_output.put_line('test1');

  COMMIT;

END UPDATE_USER;

DECLARE

BEGIN

  update ma_users t

     set t.user_point = t.user_point + 100

   where t.user_name = '幸运小子';

  UPDATE_USER('幸运小子2', 1000);

  dbms_output.put_line('test2');

END;

/

select * from ma_users

declare

  v_user ma_users%rowtype;

begin

  v_user.created_by      := 'system';

  v_user.created_date    := sysdate;

  v_user.updated_by      := 'system';

  v_user.updated_date    := sysdate;

  v_user.id_ma_users     := sys_guid();

  v_user.user_name       := '幸运小子1';

  v_user.user_password   := 'text123';

  v_user.user_sex        := '';

  v_user.user_phone      := '233sss33';

  v_user.real_name       := 'xinyuan';

  v_user.identity_no     := '';

  v_user.user_email      := 'test@163.com';

  v_user.user_address    := 'hhhhhhh';

  v_user.user_birth_date := date '1986-01-01';

  v_user.user_status     := '';

  v_user.user_remark     := 'sss';

  v_user.user_point      := 100;

  v_user.register_date   := sysdate;

  save_user(v_user);

  UPDATE_USER('幸运小子1', 1000);

end;

/

select * from ma_users

--自治事务的缺省行为是，只要在自治事务中执行了COMMIT或者ROLLBACK，这些改变立即对主事务可见

--什么时候使用自治事务

--自治事务的日志机制

------------------------------------------------------------------

1. 第三课作业中第二题的异常记录方法，大家可以完善下方法，将之改成支持自治事务的方法。
2. 这段程序中，ma_users是第一课作业建立的用户表，这段程序的目的是，一旦用户注册成功，在其默认积分基础上送1000积分。但程序里有BUG，请大家找出来，并且优化这个程序

create or replace procedure save_user(i_user ma_users%rowtype) is

begin

  insert into ma_users values i_user;

exception

  when dup_val_on_index then

    update ma_users t

       set row = i_user

     where t.user_name = i_user.user_name;

end;

/

create or replace PROCEDURE UPDATE_USER(I_USER  IN VARCHAR2,

                                        i_point in number) IS

  PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

  UPDATE MA_USERS R

     SET R.USER_POINT = R.USER_POINT + i_point

   WHERE R.USER_NAME = I_USER;

    dbms_output.put_line('test1');

  COMMIT;

END UPDATE_USER;

declare

  v_user ma_users%rowtype;

begin

  v_user.created_by      := 'system';

  v_user.created_date    := sysdate;

  v_user.updated_by      := 'system';

  v_user.updated_date    := sysdate;

  v_user.id_ma_users     := sys_guid();

  v_user.user_name       := '幸运小子1';

  v_user.user_password   := 'text123';

  v_user.user_sex        := '';

  v_user.user_phone      := '233sss33';

  v_user.real_name       := 'xinyuan';

  v_user.identity_no     := '';

  v_user.user_email      := 'test@163.com';

  v_user.user_address    := 'hhhhhhh';

  v_user.user_birth_date := date '1986-01-01';

  v_user.user_status     := '';

  v_user.user_remark     := 'sss';

  v_user.user_point      := 100;

  v_user.register_date   := sysdate;

  save_user(v_user);

  UPDATE_USER('幸运小子1', 1000);

end;

/

3.【可选做】 这节讲到了MERGE的语句，请大家改写这个语句，实现如果存在则更新，不存在则插入的逻辑，注意PLSQL程序要有异常处理

--

1 修改成自治事务

PROCEDURE exception_logs_p (

      i_option_users        IN exception_logs.option_users%TYPE,

      i_method_name         IN exception_logs.method_name%TYPE,

      i_exception_line      IN exception_logs.exception_line%TYPE,

      i_exception_code      IN exception_logs.exception_code%TYPE,

      i_exception_message   IN exception_logs.exception_message%TYPE--i_exception_level    IN exception_logs.exception_level%TYPE

      )

   IS

      PRAGMA AUTONOMOUS_TRANSACTION;

      v_sysdate           DATE DEFAULT SYSDATE;

      v_exception_level   NUMBER DEFAULT 0;

 BEGIN

  BEGIN

   SELECT   exception_level

      INTO v_exception_level

     FROM exception_level

      WHERE exception_code=i_exception_code;

   EXCEPTION

   WHEN OTHERS THEN

    v_exception_level:=3;

    END ;

   BEGIN

      INSERT INTO exception_logs (option_users,

                                  method_name,

                                  exception_time,

                                  exception_line,

                                  exception_code,

                                  exception_message,

                                  exception_level)

           VALUES (i_option_users,

                   i_method_name,

                   v_sysdate,

                   i_exception_line,

                   i_exception_code,

                   i_exception_message,

                   v_exception_level);

      COMMIT;

   EXCEPTION

      WHEN OTHERS

      THEN

         ROLLBACK;

   END;

  END;

END exception_logs_pkg;

/

2 程序bug
1 由于UPDATE_USER过程使用了自治事务，这与主事务隔离开，主事务的insert没有提交，
导致UPDATE_USER没有获取到数据，导致数据更新错误
修改：1 可以在save_user上加上commit
2 取消UPDATE_USER的自治事务

3 

declare

  v_user ma_users%rowtype;

  v_usero varchar2(32) default user;

  v_erroeline varchar2(100);--not a number

  v_sqlcode number;

  v_sqlerrm varchar2(100);

begin

  v_user.created_by      := 'system';

  v_user.created_date    := sysdate;

  v_user.updated_by      := 'system';

  v_user.updated_date    := sysdate;

  v_user.id_ma_users     := sys_guid();

  v_user.user_name       := '乱世佳人12';

  v_user.user_password   := 'text123';

  v_user.user_sex        := '';

  v_user.user_phone      := '233sss3311';

  v_user.real_name       := 'xinyuan';

  v_user.identity_no     := '';

  v_user.user_email      := 'test@163.com';

  v_user.user_address    := 'hhhhhhh';

  v_user.user_birth_date := date '1986-01-01';

  v_user.user_status     := '';

  v_user.user_remark     := 'sss';

  v_user.user_point      := 100;

  v_user.register_date   := sysdate;

begin

--register user

merge into MA_USERS m1

using( select count(id_ma_users) d from MA_USERS where user_name=v_user.user_name) m2

on (m2.d<>0)

--when matched then

 --update set m1.USER_POINT=m1.USER_POINT+1000,updated_date=sysdate

 --where  user_name=v_user.user_name

when not matched then

 insert   values v_user ;

 --update point

 merge into MA_USERS m1

using( select m2.id_ma_users d from MA_USERS m2 where m2.id_ma_users=v_user.id_ma_users ) m2

on (m2.d=m1.id_ma_users)

when matched then

 update set m1.USER_POINT=m1.USER_POINT+1000,updated_date=sysdate ;

  dbms_output.put_line('v_user.id_ma_users='||v_user.id_ma_users);

 dbms_output.put_line('t1');

exception

 when others then

 v_erroeline:=dbms_utility.format_error_backtrace;

    v_sqlcode:=sqlcode;

    v_sqlerrm:=substr(SQLERRM,1,100);

  exception_logs_pkg.exception_logs_p (v_usero,'testerror',v_erroeline,v_sqlcode, v_sqlerrm);

      RAISE;

end;

commit;

end;

/

秒客网

PL/SQL 训练04--事务

相关文章