Oracle利用过程procedure块实现银行转账

--
create table account(
   id varchar(32) not null,
   name varchar(30),
   money numeric(10,2),
   constraint a_pk primary key(id)
);
--历史表
select * from history;
drop table history;
create table history(
   id varchar(32) not null
   aid varchar(32),
   dt varchar(19),
   money numeric(10,2),
   ty varchar(10),
   constraint h_pk primary key(id),
   constraint h_fk foreign key(aid) references account(id)

);





--写入用户
insert into account values(sys_guid(),'Jack',100);
insert into account values(sys_guid(),'Rose',50);
select * from account;
commit;
--BDD483DA232943A2BD7AFDEAA1D13015  Jack  100
--59D6EFC956DB447CB4F9A8BB6B188CCE  Rose  50
--创建过程

create or replace procedure trans(p1_id in varchar2,
                                  p2_id in varchar2,
                                  p_money in numeric)

as
  --声明一个变量
  v_count integer;
  --保存金额信息
  v_money account.money%type;
begin
    --限制p_money大于0
    if p_money<=0 then
        raise_application_error(-20000,'转账金额不能为0');
    end if;
    --先检查有没有转出的人
    select count(1) into v_count from account where id=p1_id;
    if v_count=0 then
        raise_application_error(-20000,'转出账号不正确');
    end if;
    --转入的账号
     select count(1) into v_count from account where id=p2_id;
     if v_count=0 then
        raise_application_error(-20000,'转入账号不正确');
    end if;
    --再检查金额情况
    select money into v_money from account where id=p1_id;
    --判断
    if p_money>v_money then
      raise_application_error(-20000,'转账金额不够');
    end if;
    dbms_output.put_line('开始转账');
    --开始转账
    --先减money
    update account set money=money-p_money where id=p1_id;
    --加钱
    update account set money=money+p_money where id=p2_id;
    --记录hist表
    --转出
    Insert Into History(Id,Aid,Dt,Money,Ty) 
        Values(Sys_Guid(),P1_Id,To_Char(Sysdate,'yyyy-mm-dd hh24:mi:ss'),
        0-p_Money,'转出');
    --再记录转入
    Insert Into History(Id,Aid,Dt,Money,Ty) 
        Values(Sys_Guid(),P2_Id,To_Char(Sysdate,'yyyy-mm-dd hh24:mi:ss'),
        P_Money,'转入');
    dbms_output.put_line('ok');
end;
select * from account;
Select * From History;

set serveroutput on;
Begin
  trans('BDD483DA232943A2BD7AFDEAA1D13015','59D6EFC956DB447CB4F9A8BB6B188CCE',100);
end;

commit