oracle -实战项目ATM（二）

最新推荐文章于 2024-09-12 14:35:28 发布

Luoxi_8

最新推荐文章于 2024-09-12 14:35:28 发布

阅读量357

点赞数 5

文章标签： oracle 数据库

本文链接：https://blog.csdn.net/Luoxi_8/article/details/141848190

版权

上一篇ATM的实战主要是关于ATM表的创建以及相关的视图，在本篇主要介绍的是使用触发器（trigger）实现某些功能，触发器一般是用来记录表相关的操作的，比如增删改查的记录，因此trigger类型选择使用DML触发器。

触发器（trigger）

语法：
CREATE [OR REPLACE] TRIGGER 触发器名称
[BEFORE|AFTER] 
[INSERT|UPDATE|UPDATE OF 列名称 [,列名称]|DELETE|] ON 表名称
[FOR EACH ROW]
[DISABLE]
[WHEN 触发条件]
[DECLARE]
    [程序声明部分;]
BEGIN
    程序代码部分;
END[触发器名称];
/
*：before、after是指触发的时间，操作之前还是操作之后触发。

**：disable：一般触发器创建后默认是启用的状态，使用此选项，将其定义为禁用的状态。

step1

首先创建一个自增的序列，来记录每条数据的操作，

create sequence u_id_seq --创建自增序列
start with 1 --序列的初始值从1开始
increment by 1 -- 表示递增序列加1
NOMINVALUE --默认情况下，序列有一个最小值，这里显示指定了没有最小值
NOMAXVALUE --默认情况下，序列有一个最大值，这里显示指定了没有最大值
cache 20; --设置序列缓存为20，将连续生成20个序列值

一般对自增序列没有特殊要求的话，用create sequence创建一个序列，其余值选择默认就好。

create sequence u_id_seq;

step2

（1）触发器的创建-------实现id自增，检查身份证号码是否符合要求

/*
* 创建触发器1
* 用户表，实现用户id自增，并且检验身份证号码是否是15位或18位
**/
  
create or replace trigger tri_user
  before insert on Atm_user
  for each row
begin
  if
    NOT (REGEXP_LIKE(:new.user_card, '^\d{15}$') 
        OR REGEXP_LIKE(:new.user_card, '^\d{18}$'))
   then 
     raise_application_error(-20001, '字符格式错误');
   end if;
     --自增
   if :new.user_id is null
     then 
       select user_id_seq.nextval into :new.user_id
         from dual;
   end if;
end;
/



/*
* 创建触发器2
* 银行卡信息表：在插入数据之前，当前账户余额与开户金额保持一致
**/
select * from ATM_CARDINFO
create or replace trigger tri_cardinfo
  before insert on ATM_cardinfo
  for each row
begin
     --atm_cur_account,atm_account表示银行卡信息表中国的账户余额和开户金额，与建表的字段名保持一致
     
   IF :new.atm_cur_account IS NULL
    THEN
      :new.atm_cur_account:=:new.atm_account;
   END IF;
END;
/


/*
* 创建触发器3-1
* 银行卡信息表：不允许修改卡号；当更新的是银行卡信息表ATM_cardinfo的卡号时提示“此列不允许修改”
**/ 
 

create or replace trigger tri_cardid
 AFTER UPDATE  ON ATM_cardinfo
  FOR EACH ROW
begin 
  if updating('card_id')
    then 
      raise_application_error('-20002','此列不允许修改');
  end if;
end;
/

drop trigger tri_cardid
commit
/*
* 创建触发器3-2
*使用 update of 字段名创建
* 银行卡信息表：不允许修改卡号；当更新的是银行卡信息表ATM_cardinfo的卡号时提示“此列不允许修改”
**/ 
 
create or replace trigger tri_cardid
 AFTER UPDATE of card_id ON ATM_cardinfo
  FOR EACH ROW
begin 
  if updating
    then 
      raise_application_error('-20002','此列不允许修改');
  end if;
end;
/

select * from AtM_cardinfo
--测试触发器3
UPDATE ATM_cardinfo SET card_id='1010 3576 4000 1000' where card_id ='1010 3576 4000 0000';

/*
* 创建触发器4
* 请注意在触发器中选择操作的表，对应操作表中的字段
 2.当交易信息表有插入或者修改记录，银行卡信息表跟随改变：
 如交易信息表新增一条支取，那么对应的银行信息卡余额应该减少对应支取的金额。
 同理如交易信息表新增一条存入，那么对应的银行信息卡余额应该增加对应存入的金额。
 注意：如果支取先判断余额，不足则提示。如交易成功打印恭喜信息。
 
**/

create or replace trigger tri_transinfo
   after update or insert on ATM_TRANSINFO
   for each row
declare
   coumn number;
begin
  --使用case when判断是更新还是插入
  case 
    when updating then
      if :old.trans_account <= :new.trans_account
        then 
          update ATM_CARDINFO 
               set atm_cur_account 
                       = atm_cur_account + (:new.trans_account - :old.trans_account)
              where card_id =:new.atm_cardid;
       else
           update ATM_CARDINFO 
               set atm_cur_account 
                       = atm_cur_account + (:old.trans_account - :new.trans_account)
              where card_id =:new.atm_cardid;                            
       end if;
       
     when inserting then
       if :new.atm_style = '存入'
          then
            update ATM_CARDINFO 
               set atm_cur_account 
                       = atm_cur_account + :new.trans_account
              where card_id = :new.atm_cardid;
              dbms_output.put_line('恭喜存款成功！');
       
       else
          select atm_cur_account into coumn from ATM_CARDINFO where card_id = :new.atm_cardid;
          if coumn < :new.trans_account then
                 raise_application_error(-20003, '余额不足!你的余额为:' || coumn);
          else
                update ATM_CARDINFO
                    set atm_cur_account = atm_cur_account -
                                         :new.trans_account
                   where card_id = :new.atm_cardid; 
             
                DBMS_OUTPUT.PUT_LINE('恭喜取款成功!');
           end if;
     
        end if;
   end case;
  --创建异常
  --when others捕获的异常实际上是前面已经明确抛出的-20003错误,下面的异常会将上面抛出的异常覆盖
   exception
      
   when others then 
          raise_application_error(-20003,'输入的余额不足');
          --DBMS_OUTPUT.PUT_LINE('-20003');
          --DBMS_OUTPUT.PUT_LINE('SQLCODE'||SQLCODE);
          --DBMS_OUTPUT.PUT_LINE('-SQLERRM'||SQLERRM);
     rollback;
end;
/         

 --测试支取失败

insert into atm_transinfo(atm_cardid,atm_style,trans_account) 
values('1010 3576 4000 0000','支取','1200') 
commit;

函数（function 存储函数）

语法：
CREATE OR REPLACE FUNCTION 函数名([参数,[参数,...]])
RETURN 返回值类型
[AUTHID {DEFINER CURRENT_USER}]
AS | IS
        [PRAGMA AUTONOMOUS_TRANSACTION;]
        声明部分；
BEGIN
       程序部分;
       [RETURN 返回值;]
[EXCEPTION
         异常处理]
END 函数名;
*：authid定义的是函数的权限，definer(默认)表示为定义者权限执行，或者使用current_user覆盖程序的默认行为，变为使用者权限执行。

step3

创建函数，函数的功能为自动生成银行卡号，需要有return 进行值的返回

/*
* 创建函数 --存储过程:存储函数
* 1. 该函数的作用：银行卡信息表，card_id卡号随机生成，注意卡号的格式1010 3576 XXXX XXXX
**/
create or replace function fun_cardinfo
return varchar2
as 
  card_id varchar2(19):='1010 3576';
  --in_number number;
begin
  --floor：向下取整函数，产生一个不大于指定数值的最大整数；
   --id_number:= FLOOR(dbms_random.value * 10000);
   --id_number :=  TO_CHAR(id_number);
   card_id := CONCAT(CARD_ID, ' ');
   card_id := CONCAT(CARD_ID, TO_CHAR(FLOOR(DBMS_RANDOM.VALUE * 10000)));
   card_id := CONCAT(CARD_ID, ' ');
   card_id := CONCAT(CARD_ID, TO_CHAR(FLOOR(DBMS_RANDOM.VALUE * 10000)));
   RETURN CARD_ID;
END;
--测试
SELECT fun_cardinfo FROM DUAL;

过程(procedure 子程序）

语法：steps

CREATE OR REPLACE PROCEDURE 过程名称([参数名称 [参数模式] NOCOPY 数据类型 [,参数名称 [参数模式] 
                                             NOCOPY 数据类型,...]] )
  [AUTHID {DEFINER CURRENT_USER}]
DECLARE
     [PRAGMA AUTONOMOUS_TRANSACTION;]
        声明部分；
BEGIN
    程序部分;
EXCEPTION
    异常处理;
END;
/

step4

创建子过程，在创建开户的过程中调用了上面的函数，进行银行卡卡号的自动生成

/*
* 创建过程 1--存储过程
* 2. 该过程的作用：创建开户的过程，调用上面的函数-fun_cardinfo；
*                 开户过程包含插入信息到用户表及银行卡信息表；
**/
commit

create or replace procedure open_account_card
     (new_name atm_user.user_name%type, --用户名
      new_card atm_user.user_card%type, --身份证号
      new_phone atm_user.user_phone%type, --联系方式
      new_address atm_user.user_loca%type, --联系地址
      new_bz varchar2, --币种
      new_style varchar2, --存款类型
      new_amount number, --金额
      new_password varchar2, --密码
      new_result out varchar2 --结果 要使用out，否则后面赋值时会报错
                       )
  as
    n_num number; --记录次数
    n_user_id number;
begin
  --根据身份证号码判断用户表是否存在此用户
  select count(user_id) into n_num 
          from atm_user where atm_user.user_card = new_card;
  if n_num = 0 
    --没有用户名，在用户信息表创建用户信息记录
    then    
      n_user_id := u_id_seq.nextval;
      insert into atm_user values(n_user_id,new_name,new_card,new_phone,new_address);
  else 
    --有此用户，则根据此用户的信息获得用户id，一个用户可以开多张卡
    select user_id into n_user_id from atm_user where atm_user.user_card = new_card;
  end if;
  
  --在银行卡信息表中添加信息,fun_cardinfo--上面创建卡号的函数，
  insert into atm_cardinfo 
     values
       (fun_cardinfo,new_style,new_amount,new_password,'否',n_user_id,new_bz,sysdate,new_amount);
  commit;
  new_result := '开户成功';

exception
  when others then
    new_result :='未知错误';
   rollback;

end;
/ 


--测试执行
select * from atm_user
select * from atm_cardinfo
select * from atm_transinfo

--测试开户的存储过程--开户n_num=0 的案例
--注意在调用开户存储过程时，插入的参数要符合建表中的约束条件
declare
mess varchar2(50);  --对应的是开户存储过程中输出结果对应的参数
begin
  open_account_card('fenny','376542789000129083','13406198891','北京','RMB','活期',200,'密码123456',mess);
  dbms_output.put_line(mess);
end;

--测试开户的存储过程--开户n_num=1的案例
declare
mess varchar2(50);  --对应的是开户存储过程中输出结果对应的参数
begin
  open_account_card('lidy','450921199601042491','18877572901','桃花岛','RMB','活期',100,'12456789',mess);
  dbms_output.put_line(mess);
end;


    
 /*
* 创建过程 2--存储过程
* 2. 该过程的作用：创建密码修改的过程，_
*                 首先根据卡号判断是否存在，然后在进行密码的修改；
**/

create or replace procedure alter_password(
    new_card_id atm_cardinfo.card_id%type,
    new_password atm_cardinfo.atm_password%type,
    new_result out varchar2  
      )
  as
    n_num number;
begin
  select count(card_id) into n_num from atm_cardinfo where card_id = new_card_id;
  if n_num = 1 then
    update atm_cardinfo set atm_password = new_password where card_id = new_card_id;
    commit;
    new_result := '修改密码成功';
  else
    new_result := '找不到数据';
  end if;
exception
  when others then
    new_result := '未知错误';
     rollback;
end;
/
     
--测试执行

select * from atm_cardinfo

--测试修改密码的过程，测试修改密码成功的案例
declare
  mess varchar2(50);
begin
  alter_password('1010 3576 4000 5856','修改新的密码',mess);
  dbms_output.put_line(mess);
end;
/

--测试修改密码的过程，测试修改密码失败的案例,将银行卡id设置为错误
declare
  mess varchar2(50);
begin
  alter_password('1010 3576 4000','修改新的密码',mess);
  dbms_output.put_line(mess);
end;
/

 /*
* 创建过程 3--存储过程
* 2. 该过程的作用：创建挂失账号的过程，_
*                 首先找到对应的挂失账号，然后再进行账号的挂失；
**/

create or replace procedure relo_up(
       new_card atm_cardinfo.card_id%type,
       new_result out varchar2
       )
  as 
    n_num number;
begin
  select count(card_id) into n_num from atm_cardinfo where card_id = new_card;
  if n_num = 1 then 
    update atm_cardinfo set atm_cardinfo.status = '是' where card_id = new_card;
    commit;
    new_result := '挂失成功';
  else
    new_result := '找不到数据';
  end if;
exception
  when others then 
    new_result := '未知错误';
end;
/



--测试执行

select * from atm_cardinfo;

--测试银行卡挂失的过程，显示挂失成功的
declare
  mess varchar2(50);
begin
  relo_up('1010 3576 4000 5856',mess);
  dbms_output.put_line(mess);
end;
/

--测试银行卡挂失的过程，显示挂失失败的
declare
  mess varchar2(50);
begin
  relo_up('1010 3576 4000 6',mess);
  dbms_output.put_line(mess);
end;
/

/*
* 1. 创建存入和支出的过程--存储过程
* 2. 只操作交易信息表，银行卡信息表的余额根据上面的触发器进行操作;
   *根据卡号的信息进行记录，不用进行卡号的判断，在触发器4中已经进行判断
   * 注意，在参数的声明中，卡号的类型与银行卡信息表一致
**/

create or replace procedure trans_in_atm(
               new_card_id atm_cardinfo.card_id%type, --卡号
               new_typle varchar2,  --操作类型
               new_amount number,  --余额
               new_remark varchar2,  --时间
               new_result out varchar2      
               )

as
  --n_num number;
begin
  
      insert into atm_transinfo values (new_card_id,new_typle,new_amount,sysdate,new_remark);
      commit;
      new_result := '成功';
      
exception
  when others then 
    new_result := '未知错误';
end;
/
    
--测试执行
select * from atm_cardinfo
--测试存入和支出的过程，注意这里测试的银行卡号必须要在原表中存在，才会测试成功！
declare
  mess varchar2(50);
begin
  trans_in_atm('1010 3576 4661 1643','存入',200,'12',mess);
  dbms_output.put_line(mess);
end;
/

/*
* 2. 创建查询余额的过程--存储过程
* 
**/
create or replace procedure current_account(
            new_card_id atm_cardinfo.card_id%type,
            new_result out varchar2)
as 
  acc_num varchar2(50);
begin
  select atm_cur_account into acc_num from atm_cardinfo where card_id = new_card_id;
  new_result := concat('当前的余额为：',acc_num);
exception
  when no_data_found then
    new_result := '找不到数据';
    rollback;
  when others then 
    new_result := '未知错误';
    rollback;
end;
/

--测试执行
select * from atm_cardinfo

declare
  mess varchar2(50);
begin
  current_account('1010 3576 4661 1643',mess);
  dbms_output.put_line(mess);
end;
/
  
/*
* 3. 创建转账存款过程，判断要转账及要转入的账户是否存在，转账金额是否充足，发生异常交易要回滚
* 上述的存储过程中已经创建了关于银行卡信息表的余额操作，在这个的创建中，主要是对交易信息表进行的操作
**/

create or replace procedure trans_atm(
                   new_card_id1 atm_cardinfo.card_id%type,--转出的账户
                   new_card_id2 atm_cardinfo.card_id%type,--转入的账户
                   new_amount number, --转账金额
                   new_result out varchar2 --只有out才能作为赋值语句进行赋值
                  )

as
  acc_num number;
  n_num number;

begin
  select atm_cur_account into acc_num from atm_cardinfo where card_id = new_card_id1;
  select count(card_id) into n_num from atm_cardinfo where card_id = new_card_id2;
  if n_num = 0 then 
      new_result := concat('没有查询到账户为：',concat(new_card_id2,'的用户'));
  elsif acc_num < new_amount then
      new_result := '你的账户余额不足此操作！';
      
  else
      insert into atm_transinfo values(new_card_id1,'支取',new_amount,sysdate,'转账到'||new_card_id2);
      insert into atm_transinfo values(new_card_id2,'存入',new_amount,sysdate,new_card_id1||'转入');
      commit;
      new_result := '成功';
  end if;
exception
  when no_data_found then
    new_result := '找不到数据';
    rollback;
  when others then
    new_result := '未知错误';
    rollback;
end;
/
   

--测试执行
select * from atm_transinfo   
 --测试转账存款过程，账户都存在的情况
declare
  mess varchar2(50);
begin
  trans_atm('1010 3576 4000 0000','1010 3576 4000 5890',4,mess);
  dbms_output.put_line(mess);
end;
/
  
  
 --测试转账存款过程，账户2都存在的情况
declare
  mess varchar2(50);
begin
  trans_atm('1010 3576 4000 0000','1010 3576 4000 5893',4,mess);
  dbms_output.put_line(mess);
end;
/

/*
* 4. 创建总存储的过程，
*  对于银行来说，流通余额 = 所有存入-所有支出
*  对于银行来说，盈利结算=所有存入*0.003-所有支取*0.008

**/

create or replace procedure sum_account_atm(
              new_result out varchar2)
as
  in_acc number; --存储存入的余额
  out_acc number; --存储支出的余额
begin
  select sum(trans_account) into in_acc from atm_transinfo where atm_style = '存入';
  select sum(trans_account) into out_acc from atm_transinfo where atm_style = '支取';
  new_result := concat(concat('银行的流通余额为：',in_acc-out_acc),
                concat('银行的盈利结算为：',in_acc*0.003-out_acc*0.008));
exception
  when no_data_found then
    new_result := '找不到数据';
    rollback;
  when others then
    new_result := '未知错误';
    rollback;
end;
/
  
--测试执行
select * from atm_transinfo;

declare
  mess varchar2(50);
begin
  sum_account_atm(mess);
  dbms_output.put_line(mess);
end;
/

/*
* 5. 创建存储销户过程，销户需要删除所有的记录，包括用户信息表，银行卡信息表和交易信息表的所有记录

**/

create or replace procedure delt_all(
                new_usercard atm_user.user_card%type,
                new_card_id atm_cardinfo.card_id%type,
                new_result out varchar2
                )
as
  n_num number;
begin
  --统计需要销户的用户的银行卡号，
  select count(card_userid) into n_num from atm_cardinfo 
               where card_id = new_card_id
                   and card_userid = (select user_id from atm_user where user_id = new_usercard);
  if n_num = 1 then
    --因为在atm_transinfo交易信息表中的外键atm_cardid的外键约束中，添加了联级删除的操作
    --删除银行卡信息表中的数据
    delete from atm_cardinfo where card_id = new_card_id;
    commit;
  end if;
  --统计销户的身份证号
  select count(card_userid) into n_num from atm_cardinfo 
               where card_userid = (select user_id from atm_user where user_id = new_usercard);
  if n_num = 0 then
    delete from atm_user where user_id = new_usercard;
    commit;
  end if;
  new_result := '成功';
exception
  when no_data_found then
    new_result := '找不到数据';
    rollback;
  when others then
    new_result := '未知错误';
    rollback;
end;
/
    
    
--测试执行         
select * from atm_user

select * from atm_cardinfo

declare 
  mess varchar2(50);
begin
  delt_all(1,'1010 3576 4000 0000',mess);
  dbms_output.put_line(mess);
end;
/