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

来源：互联网发布：数据异常怎么回事编辑：程序博客网时间：2024/05/13 12:51

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

Sql代码

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;

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代码

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;
/

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;/