Oracle基本事务和ForAll执行批量DML练习

 基本事务的使用：
从账户一的余额中转100到账户二的余额中去，如果账户二不存在或账户一中的余额不足100则整笔交易回滚

Sql代码

1. select * from account;   
2. -- 创建一张账户表   
3. create table account(   
4.        -- 账户ID   
5.        id number(3) not null,   
6.        -- 账户名称   
7.        name varchar2(50) not null,   
8.        -- 账户余额   
9.        balance number(8,2) not null,   
10.        -- 开启时间   
11.        btime date not null  
12. )   
13. -- 插入数据   
14. insert into account(id,name,balance,btime) values(1,'张三',2000.23,to_date('2008-02-12','yyyy-mm-dd'));   
15. insert into account(id,name,balance,btime) values(2,'李四',530,to_date('2008-10-03','yyyy-mm-dd'));   
16. insert into account(id,name,balance,btime) values(3,'王五',1620.2,to_date('2007-08-20','yyyy-mm-dd'));   
17. insert into account(id,name,balance,btime) values(4,'小强',910.9,to_date('2009-01-23','yyyy-mm-dd'));   
18. insert into account(id,name,balance,btime) values(5,'小周',8700,to_date('2006-09-10','yyyy-mm-dd'));   
19.   
20. declare  
21.    -- 临时保存账户一的余额总数   
22.    v_balance account.balance%type;   
23. begin  
24.    update account set balance = balance - 100 where name = '&转出账号：' returning balance into v_balance;   
25.    if sql%notfound then  
26.       raise_application_error(-20001,'转出账号 不存在！');   
27.    end if;   
28.    if v_balance < 0 then  
29.       raise_application_error(-20002,'账户余额不足！');   
30.    end if;   
31.    update account set balance = balance + 100 where name = '&转入账号：';   
32.    if sql%notfound then  
33.       raise_application_error(-20003,'转入账号 不存在！');   
34.    end if;   
35.    commit;   
36.    dbms_output.put_line('转账成功！');   
37. exception   
38.    when others then rollback;   
39.    dbms_output.put_line(sqlerrm);   
40. end;  

select * from account;
-- 创建一张账户表
create table account(
       -- 账户ID
       id number(3) not null,
       -- 账户名称
       name varchar2(50) not null,
       -- 账户余额
       balance number(8,2) not null,
       -- 开启时间
       btime date not null
)
-- 插入数据
insert into account(id,name,balance,btime) values(1,'张三',2000.23,to_date('2008-02-12','yyyy-mm-dd'));
insert into account(id,name,balance,btime) values(2,'李四',530,to_date('2008-10-03','yyyy-mm-dd'));
insert into account(id,name,balance,btime) values(3,'王五',1620.2,to_date('2007-08-20','yyyy-mm-dd'));
insert into account(id,name,balance,btime) values(4,'小强',910.9,to_date('2009-01-23','yyyy-mm-dd'));
insert into account(id,name,balance,btime) values(5,'小周',8700,to_date('2006-09-10','yyyy-mm-dd'));

declare
   -- 临时保存账户一的余额总数
   v_balance account.balance%type;
begin
   update account set balance = balance - 100 where name = '&转出账号：' returning balance into v_balance;
   if sql%notfound then
      raise_application_error(-20001,'转出账号 不存在！');
   end if;
   if v_balance < 0 then
      raise_application_error(-20002,'账户余额不足！');
   end if;
   update account set balance = balance + 100 where name = '&转入账号：';
   if sql%notfound then
      raise_application_error(-20003,'转入账号 不存在！');
   end if;
   commit;
   dbms_output.put_line('转账成功！');
exception
   when others then rollback;
   dbms_output.put_line(sqlerrm);
end;

使用ForAll执行批量DML练习:
账户建立超过6个月的赠送100，超过12个月的赠送200，超过24个月的赠送500，建立时间未过6个月的不赠送

Sql代码

1. declare  
2.    -- 保存建立账户日期与当前日期相差的份数   
3.    v_monthbt number(5,2);   
4.    type str_table_type is table of varchar2(50) index by binary_integer;   
5.    type id_table_type is table of number(3) index by binary_integer;   
6.    -- 账户名称数组   
7.    name_table str_table_type;   
8.    -- 赠送金额数组   
9.    money_table str_table_type;   
10.    -- 账户ID数组   
11.    id_table id_table_type;   
12. begin  
13.    for i in 1..5 loop   
14.        select months_between(sysdate,btime) into v_monthbt from account where id=i;   
15.        if v_monthbt between 6 and 12 then  
16.           money_table(i) := 100;   
17.        elsif v_monthbt between 12 and 24 then  
18.           money_table(i) := 200;   
19.        elsif v_monthbt >= 24 then  
20.           money_table(i) := 500;   
21.        else  
22.           money_table(i) := 0;   
23.        end if;   
24.        id_table(i) := i;   
25.    end loop;   
26.    -- 使用ForAll批量更新数据   
27.    forall i in 1..money_table.count  
28.       update account set balance = balance + money_table(i) where id = id_table(i) returning name bulk collect into name_table;   
29.       for i in 1..name_table.count loop   
30.           dbms_output.put_line(name_table(i));   
31.       end loop;   
32.    commit;   
33. end;   
34. /  

declare
   -- 保存建立账户日期与当前日期相差的份数
   v_monthbt number(5,2);
   type str_table_type is table of varchar2(50) index by binary_integer;
   type id_table_type is table of number(3) index by binary_integer;
   -- 账户名称数组
   name_table str_table_type;
   -- 赠送金额数组
   money_table str_table_type;
   -- 账户ID数组
   id_table id_table_type;
begin
   for i in 1..5 loop
       select months_between(sysdate,btime) into v_monthbt from account where id=i;
       if v_monthbt between 6 and 12 then
          money_table(i) := 100;
       elsif v_monthbt between 12 and 24 then
          money_table(i) := 200;
       elsif v_monthbt >= 24 then
          money_table(i) := 500;
       else
          money_table(i) := 0;
       end if;
       id_table(i) := i;
   end loop;
   -- 使用ForAll批量更新数据
   forall i in 1..money_table.count
      update account set balance = balance + money_table(i) where id = id_table(i) returning name bulk collect into name_table;
      for i in 1..name_table.count loop
          dbms_output.put_line(name_table(i));
      end loop;
   commit;
end;
/