数据库操作银行管理系统

本文主要是介绍数据库操作银行管理系统，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

-- --------------------------------------------------userinfo表、用户表
-- 1.1 创建userinfo表--
create table UserInfo(
  customerID int primary key,
  customerName varchar(10) not null,
  personID varchar(20) not null unique,
  telephone varchar(20) not null,
  address varchar(30)
);    
-- 1.2 为身份证号和电话增加正则表达式约束--
alter table userinfo add constraint CK_TELEPHONE
check(regexp_like(telephone,'^1[3,4,5,7,8][0-9]-[0-9]{8}$|^[0-9]{3,4}-[0-9]{8}$'));
alter table userinfo add constraint CK_PERSONID
check(regexp_like(personid,'^[0-9]{15}$|^[0-9]{17}[0-9,x]$'));
-- 1.3向userinfo表中插入3条记录
insert into userinfo(customerName,personID,telephone,address) values
            ('李四','370404066601022222','15000000111','山东济宁'),
            ('王五','370404066601023333','15000000222','山东枣庄'),
            ('赵六','370404066601024444','15000000111','山东菏泽'),
            ('祁七','370404066601025555','15000000222','山东泰安')

 -- 1.4 客户信息表视图
create or replace VIEW vw_userInfo 
AS 
select customerID as 客户编号,customerName as 开户名, personID as 身份证号,
telephone as 电话号码,address as 居住地址  from userInfo;

-- 1.5查询挂失帐号的客户信息
SELECT * FROM vw_cardInfo;
SELECT customerName as 客户姓名,telephone as 联系电话 FROM userInfo WHERE customerID IN (SELECT customerID FROM cardInfo WHERE IsReportLoss=1);

-- --------------------------------------------------------cardinfo表、银行卡
-- 2.1 创建cardinfo表--
create table CardInfo(
  cardID varchar(30)  primary key,
  curID varchar(5) default 'RMB' not null,
  savingID varchar(5) not null,
    openDate DATE NOT NULL,
  openMoney decimal(10,2) not null check(openMoney>=1),
  balance decimal(10,2) not null check(balance>=1),
  pwd varchar(10) default '888888' not null ,
  IsReportLoss char(2) default '否' not null,
  customerID int not null references UserInfo(customerID)
);
-- 2.2 为卡号和密码增加正则表达式约束--
alter table cardinfo add constraint CK_PWD check
(regexp_like(pwd,'^[0-9]{6}$'));
alter table cardinfo add constraint CK_CARDID check
(regexp_like(cardid,'^1010[[:space:]]3576[[:space:]][0-9]{4}[[:space:]][0-9]{4}$'));
-- 2.3 向cardinfo表中插入3条记录
insert into cardinfo(cardID,savingID,openMoney,balance,pwd,customerID) values
('1010 3576 0000 0000','活期',10,2,'000000',1),
('1010 3576 0000 0001','活期',100,20,'000001',2),
('1010 3576 0000 0010','活期',1000,200,'000010',3),
('1010 3576 0000 0011','活期',10000,2000,'000011',4),
('1010 3576 0000 0012','活期',10000,20,'000100',5)
-- 2.4 银行卡信息表视图
create or replace VIEW vw_cardInfo
AS 
select c.cardID as 卡号,u.customerName as 客户,c.curID as 货币种类,c.openMoney as 存取金额,
     c.balance as 余额,c.pwd 密码,
     case c.IsReportLoss when 0 then '正常'
                                             when 1 then '挂失'
     end as 是否挂失
from cardInfo c, userinfo u
where c.customerID=u.customerID;

-- 2.5修改密码
-- 1.张三（卡号为1010 3576 0000 0000）修改银行卡密码为123456
-- 2.李四（卡号为1010 3576 0000 0001）修改银行卡密码为123123
update cardInfo set pwd='123456' WHERE cardID='1010 3576 0000 0000' ;
update cardInfo set pwd='123123' WHERE cardID='1010 3576 0000 0001' ;
-- 查询账户信息
SELECT * FROM vw_cardInfo;

-- (2)注销账户
delete from cardInfo where cardID='1010 3576 0000 0000'
-- (3)挂失帐号。李四（卡号为22222）因银行卡丢失，申请挂失
update cardInfo set IsReportLoss=1 WHERE cardID='1010 3576 0000 0001' ;
SELECT * FROM vw_cardInfo;
-- 查看修改密码和挂失结果
SELECT cardid 卡号,curID 货币,opendate 开户日期,openmoney 开户金额,balance 余额,pwd 密码,
    case IsReportLoss WHEN 1 THEN '挂失'  WHEN 0 THEN '未挂失' ELSE NULL end 是否挂失, 
    customerName 客户姓名
FROM CardInfo, UserInfo
WHERE  CardInfo.customerID = UserInfo.customerID;
-- (4)查询本周开户的卡号,显示该卡相关信息
SELECT c.cardID 卡号,u.customerName 姓名,c.curID 货币,c.openDate 开户日期,c.openMoney 开户金额,c.balance 存款余额,
       CASE c.IsReportLoss WHEN 0 THEN '正常账户'
                           WHEN 1 THEN '挂失账户'
                           ELSE NULL
       END 账户状态
FROM cardInfo c INNER JOIN userInfo u ON (c.customerID = u.customerID)
WHERE  openDate between trunc(sysdate,'DAY') and trunc(sysdate,'DAY')+6;
-- (5)、按银行卡余额降序创建索引。
create index money_desc_index on cardInfo(balance desc);
show index from cardInfo;

-- ------------------------------------------------------tradeinfo表、交易表
-- 3.1 创建tradeinfo表
create table TradeInfo(
    tradeDate  DATE NOT NULL,
  cardID varchar(30) not null references CardInfo(cardID),
  transType varchar(10) not null,
  transMoney decimal(10,2) not null,
  remark varchar(50)
);
-- 3.2 为transtype增加约束--
alter table tradeinfo add constraint CK_TRANSTYPE check (transtype in('支取','存入'));
-- 3.3 向transinfo表中插入3条记录
insert into tradeinfo(cardID,transType,transMoney) values
('1010 3576 0000 0000','存入',100),
('1010 3576 0000 0001','存入',100),
('1010 3576 0000 0000','存入',100),
('1010 3576 0000 0001','存入',100),
('1010 3576 0000 0010','支取',100)

-- 3.4 创建视图：查看交易信息
create VIEW vw_tradeInfo
AS 
select tradeDate as 交易日期, cardID as 卡号, transType as 交易类型,transMoney as 交易金额,
remark as 备注  from tradeInfo ;

-- 3.5查询本月交易金额最高的卡号
SELECT DISTINCT cardID  FROM TradeInfo 
WHERE  transMoney=(SELECT MAX(transMoney) FROM TradeInfo);

 
-- ------------------------------------创建存储过程通过随机数产生卡号(未成功)-------------------------
create proc randomcardid
@cardid char(19)output
as
select @cardid='1010 3576 '+substring(convert(char(10),RAND()*10000),0,5)+' '+substring(convert(char(10),RAND()*10000),0,5)
-- -----------------------------------------------------------------DML(未成功)
create or replace procedure usp_randCardID(v_randCardID OUT char)
AS
    v_r number(5);
BEGIN
  v_r:='1010 3576 0000 '+round(dbms_random.value(1000,9999));
  v_randCardID:=substr(v_r,1,4);
end;
-- <2>开户的存储过程
CREATE SEQUENCE seq_customerID
        START WITH 10 
        INCREMENT BY 1
        NOMAXVALUE 
        NOCYCLE
        CACHE 30;
create or replace
procedure usp_openAccount
( v_customerName varchar,
  v_PID varchar,
  v_telephone varchar,
  v_openMoney number,
  v_savingID number,
  v_address varchar default '' )
AS
   v_mycardID char(19);
   v_cur_customerID int;
   v1 int;
 begin

-- ---------------------------------------------------------
#生成N个随机数字函数
CREATE FUNCTION usp_randCardID(N int) RETURNS VARCHAR(255)
begin
    # 定义一个默认获取的值0-9
    DECLARE chars_str varchar(20) DEFAULT '0123456789';
    #将随机获取后得值保存在 return_str
    DECLARE return_str varchar(255) DEFAULT '';
    # 定义一个变量用来判断循环的参次数
    DECLARE i INT DEFAULT 0;
    WHILE i < n
        DO
             # 使用随机函数将 chars_str随机获取一个数字进行累加
            SET return_str = concat(return_str, substring(chars_str, FLOOR(1 + RAND() * 10), 1));
             #变量加1
            SET i = i + 1;
        END WHILE;
    #最终结果返回
    RETURN return_str;
END

-- ----------------------调用产生随机卡号的存储过程获得随机卡号
usp_randCardID (v_mycardID);
   SELECT count(*) into v1 FROM cardInfo WHERE cardID=v_mycardID;
   while (v1<>0) loop
      usp_randCardID (v_mycardID);
      SELECT count(*) into v1 FROM cardInfo WHERE cardID=v_mycardID;            
   end loop;
   dbms_output.put_line('尊敬的客户,开户成功!系统为您产生的随机卡号为:'||v_mycardID);
   dbms_output.put_line('开户日期'||to_char(sysdate,'yyyy-mm-dd')||'  开户金额:'||to_char(v_openMoney));
   select count(*) into v1 from userInfo where PID=v_PID;
   dbms_output.put_line('v1='|| to_char(v1));
   if v1 = 0 then

      INSERT INTO userInfo(customerID,customerName,PID,telephone,address) VALUES(seq_customerID.nextval,v_customerName,v_PID,v_telephone,v_address);

   end if;
   select customerID into v_cur_customerID from userInfo where PID=v_PID;
   INSERT INTO cardInfo(cardID,savingID,openMoney,balance,customerID)
         VALUES(v_mycardID,v_savingID,v_openMoney,v_openMoney,v_cur_customerID);
exception
    when no_data_found then
        raise_application_error(-20000,'开户失败,请重新输入!');
    when others then
        raise_application_error(-20001,'其他错误,请重新输入!');
end;
-- <2>开户的存储过程---------------------------------------------------------
CREATE SEQUENCE seq_customerID
        START WITH 10 
        INCREMENT BY 1
        NOMAXVALUE 
        NOCYCLE
        CACHE 30;
create or replace
procedure usp_openAccount
( v_customerName varchar,
  v_PID varchar,
  v_telephone varchar,
  v_openMoney number,
  v_savingID number,
  v_address varchar default '' )
AS
   v_mycardID char(19);
   v_cur_customerID int;
   v1 int;
 begin
   --调用产生随机卡号的存储过程获得随机卡号
   usp_randCardID (v_mycardID);
   SELECT count(*) into v1 FROM cardInfo WHERE cardID=v_mycardID;
   while (v1<>0) loop
      usp_randCardID (v_mycardID);
      SELECT count(*) into v1 FROM cardInfo WHERE cardID=v_mycardID;            
   end loop;
   dbms_output.put_line('尊敬的客户,开户成功!系统为您产生的随机卡号为:'||v_mycardID);
   dbms_output.put_line('开户日期'||to_char(sysdate,'yyyy-mm-dd')||'  开户金额:'||to_char(v_openMoney));
   select count(*) into v1 from userInfo where PID=v_PID;
   dbms_output.put_line('v1='|| to_char(v1));
   if v1 = 0 then

      INSERT INTO userInfo(customerID,customerName,PID,telephone,address) VALUES(seq_customerID.nextval,v_customerName,v_PID,v_telephone,v_address);

   end if;
   select customerID into v_cur_customerID from userInfo where PID=v_PID;
   INSERT INTO cardInfo(cardID,savingID,openMoney,balance,customerID)
         VALUES(v_mycardID,v_savingID,v_openMoney,v_openMoney,v_cur_customerID);
exception
    when no_data_found then
        raise_application_error(-20000,'开户失败,请重新输入!');
    when others then
        raise_application_error(-20001,'其他错误,请重新输入!');
end;

-- 调用存储过程开户-------------------------------------------------------------------------------------------------------
begin
    usp_openAccount ('老五','334456889012678000','010-44433572',1000,1,'河南');
    commit;
end;
-- 2. 修改某个账户密码的存储过程-------------------------------------------
-- <1>修改密码的存储过程
create or replace
procedure usp_updatePass 
  (v_card char, --卡号
  v_newPass char,
  v_inputPass char default NULL) --密码
as
    v_myPass char(6);--原密码
begin
    SELECT pass  into v_myPass   FROM cardInfo WHERE cardID=v_card and pass=v_inputPass;
       if (v_myPass=v_inputPass) then
           update cardInfo set pass=v_newPass  WHERE cardID=v_card;
           dbms_output.put_line('修改成功！');
       else
            dbms_output.put_line('原密码输入有误！');
            raise_application_error(-20000,'密码输入错误！');
        end if;
    commit;
exception
   when no_data_found then
      raise_application_error(-20001,'卡号或密码错误!');
end;
-- <2>调用修改密码的存储过程
begin
    usp_updatePass ('52315','123456','888888');
    commit;
end;

-- 4. 创建转帐事务存储过程
-- <1>创建取钱或存钱的存储过程
create or replace procedure usp_takeMoney 
  (v_card char, --卡号
  v_m number,  --存取金额
  v_type char,  --存取类型
  v_inputPass char default NULL) --密码
as
    v1 number(1);  --临时变量
    v_mybalance  number; --余额
begin
    dbms_output.put_line('交易正进行,请稍后......');
    if (v_type='支取') then
       SELECT 1 into v1 FROM cardInfo WHERE cardID=v_card and pass=v_inputPass;
    end if;
    SELECT balance into v_mybalance 
    FROM cardInfo 
    WHERE cardID=v_card;
    if (v_type='支取') then
       if (v_mybalance>=v_m+1) then
           update cardInfo set balance=balance-v_m WHERE cardID=v_Card;
       else
            dbms_output.put_line('卡号'||v_card||'  余额：'||to_char(v_mybalance));
            raise_application_error(-20000,'交易失败！余额不足！');
        end if;
    else
         update cardInfo set balance=balance+v_m WHERE cardID=v_card;
    end if;
    dbms_output.put_line('交易成功！交易金额：'||to_char(v_m));
    SELECT balance into v_mybalance FROM cardInfo WHERE cardID=v_card;
    dbms_output.put_line('卡号'||v_card||'  余额：'||to_char(v_mybalance)); 
    INSERT INTO tradeInfo(tradeType,cardID,tradeMoney) VALUES(v_type,v_card,v_m);
    commit;
exception
   when no_data_found then
      raise_application_error(-20001,'卡号或密码错误!');
end;

-- <2>创建打印对账单的存储过程
CREATE OR REPLACE PROCEDURE usp_CheckSheet(
  v_cardID varchar2,
  v_date1 date:=NULL,
  v_date2 date:=NULL)
AS
  v_custName varchar2(20);
  v_curName varchar2(20);
  v_openDate date;
  TYPE cursor_type IS REF CURSOR;
  --声明一个游标变量
   c1 CURSOR_TYPE;
   v_trade tradeinfo%rowtype;
   v_sqlStr varchar2(2000);
BEGIN
  SELECT c.curID, u.customerName ,c.openDate
  INTO v_curName,v_custName, v_openDate FROM cardInfo c inner join  userInfo u on c.customerID=u.customerID  WHERE  cardID = v_cardID;
  dbms_output.put_line('卡号：' || v_cardID);
  dbms_output.put_line('姓名:'  || v_custName);
  dbms_output.put_line('货币:'  || v_curName);
  dbms_output.put_line('开户日期：'  || to_char(v_openDate,'yyyy"年"mm"月"dd"日"'));
  dbms_output.put_line(' ');
  dbms_output.put_line('--------------------------------------------------------------------');
  dbms_output.put_line('交易日   '||'   类型  '||'   交易金额  '||'   备注');
  v_sqlStr:='SELECT *  FROM tradeInfo  WHERE cardID='''||v_cardID||'''';
  IF v_date2 IS NOT NULL THEN
      v_sqlStr:=v_sqlStr||' AND tradeDate <=to_date('''||to_char(v_date2,'yyyy-mm-dd')||' 23:59:59'||''',''yyyy-mm-dd hh24:mi:ss'')';
  END IF;
  IF v_date1 IS NOT NULL THEN
      v_sqlStr:=v_sqlStr||' AND tradeDate >=to_date('''||to_char(v_date1,'yyyy-mm-dd')||' 00:00:00'||''',''yyyy-mm-dd hh24:mi:ss'')';       
  END IF;
  v_sqlStr:=v_sqlStr||' ORDER BY tradeDate';
  --dbms_output.put_line(v_sqlStr);
  open c1 for v_sqlStr;
  LOOP
      FETCH c1 INTO v_trade ;
      EXIT WHEN c1%NOTFOUND;  
      DBMS_OUTPUT.PUT_LINE(to_char(v_trade.tradeDate,'yyyy-mm-dd')||'     '||v_trade.tradeType||'     '||v_trade.tradeMoney||'     '||v_trade.remark);
  END LOOP;
  CLOSE c1;
END;

-- <3>转帐的事务存储过程
create or replace procedure usp_tradefer (
    v_card1 varchar2,
    v_pwd varchar2,
    v_card2 varchar2,
    v_outmoney number)
AS
   v_date1 date:= sysdate;
   v_date2 date:= sysdate;
   
   emp_20000 EXCEPTION;
   PRAGMA EXCEPTION_INIT(emp_20000, -20000);
   emp_20001 EXCEPTION;
   PRAGMA EXCEPTION_INIT(emp_20001, -20001);
BEGIN
    commit;
    dbms_output.put_line('开始转账，请稍后......');
    usp_takeMoney(v_card1,v_outmoney ,'支取',v_pwd);
    usp_takeMoney(v_card2,v_outmoney ,'存入');
    commit;
    dbms_output.put_line('转账成功！');
    v_date2 := sysdate;
    dbms_output.put_line('打印转出账户对账单');
    dbms_output.put_line('-------------------');
    usp_CheckSheet(v_card1,v_date1,v_date2);
    dbms_output.put_line('打印转入账户对账单');
    dbms_output.put_line( '-------------------');
    usp_CheckSheet(v_card2,v_date1,v_date2);
EXCEPTION
   WHEN emp_20000 THEN
      DBMS_OUTPUT.PUT_LINE('交易失败！余额不足！转账失败！');
      rollback;
   WHEN emp_20001 THEN
      DBMS_OUTPUT.PUT_LINE('卡号或密码错误! 转账失败！');
      rollback;
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('出现了其他异常错误,转账失败！');
      rollback;
END;
-- <4>测试上述事务存储过程
-- 从李四的帐户转帐2000到张三的帐户
DECLARE
   emp_20000 EXCEPTION;
   PRAGMA EXCEPTION_INIT(emp_20000, -20000);
   emp_20001 EXCEPTION;
   PRAGMA EXCEPTION_INIT(emp_20001, -20001);
   v_card1 char(19);
   v_card2 char(19);
BEGIN
    select cardID into v_card1 
    from cardInfo Inner Join userInfo ON cardInfo.customerID=userInfo.customerID 
    where customerName='李四';
    select cardID into v_card2 
    from cardInfo Inner Join userInfo ON cardInfo.customerID=userInfo.customerID 
    where customerName='张三';
    usp_tradefer(v_card1,'123123',v_card2,2000);
EXCEPTION
   WHEN emp_20000 THEN
      DBMS_OUTPUT.PUT_LINE('交易失败！余额不足！转账失败！');
   WHEN emp_20001 THEN
      DBMS_OUTPUT.PUT_LINE('密码错误!转账失败！');
   WHEN no_data_found THEN
      DBMS_OUTPUT.PUT_LINE('用户名不存在!转账失败！');      
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('出现了其他异常错误！');
END;
-- 1. 交易信息表中的交易日期不允许修改。
create or replace
TRIGGER tri_updataDate 
before update   ON tradeinfo
FOR EACH ROW
BEGIN
 if updating('tradedate') then
    raise_application_error(-20000,'不能更新交易时间字段');
 end if;
END;
-- 2. 测试触发器。
update tradeinfo set tradedate='22-6月-20'

这篇关于数据库操作银行管理系统的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！