物联网数字货币取款机（数据库——银行ATM取款机系统）
银行需要开发ATM取款机系统实现如下功能：
开户（到银行填写开户申请单，卡号自动生成）
取款
存款
挂失
修改密码
催款提醒
统计查询银行业务信息
转账
用例1：数据库设计
.jpg)
无
用例2：建表空间、建表、建约束
-- 创建表空间CREATE TABLESPACE tablespace_bank DATAFILE '；d！bankbank。dbf'； SIZE 10M AUTOEXTEND ON ； --创建用户create user chinasoft identified by chinasoft default tablespace tablespace_bank； --用户授权grant connect，resource to chinasoft；grant create view to chinasoft；grant debug any procedure，debug connect session to chinasoft；grant dba to chinasoft；-- 管理员权限 drop table tradeInfo；drop table userInfo；drop table cardInfo；drop table Deposit； CREATE TABLE userInfo --用户信息表( customerID number NOT NULL， customerName varCHAR2(8) NOT NULL， PID varCHAR(18) NOT NULL， telephone varCHAR(20) NOT NULL， address VARCHAR(50))； CREATE TABLE cardInfo --银行卡信息表( cardID CHAR(19) NOT NULL， curID VARCHAR(10) NOT NULL， savingID number NOT NULL， openDate DATE NOT NULL， openMoney number NOT NULL， balance number NOT NULL， pass CHAR(6) NOT NULL， IsReportLoss NUMBER(1) NOT NULL， customerID NUMBER NOT NULL)； CREATE TABLE tradeInfo --交易信息表( tradeDate DATE NOT NULL， tradeType CHAR(4) NOT NULL， cardID CHAR(19) NOT NULL， tradeMoney NUMBER NOT NULL， remark LONG )； CREATE TABLE Deposit --存款类型表( savingID NUMBER NOT NULL， savingName VARCHAR(20) NOT NULL， descrip VARCHAR(50))； -- 加约束ALTER TABLE Deposit ADD CONSTRAINT PK_savingID PRIMARY KEY(savingID)； /* userInfo表的约束customerID 顾客编号 主键customerName 开户名 必填PID 身份证号 必填，只能是18位或15位，身份证号唯一约束telephone 联系电话 必填，格式为xxxx-xxxxxxxx或手机号11位address 居住地址 可选输入*/ ALTER TABLE userInfo ADD CONSTRAINT PK_customerID PRIMARY KEY(customerID)； ALTER TABLE userInfo ADD CONSTRAINT CK_PID CHECK( length(PID)=18 or length(PID)=15 )； ALTER TABLE userInfo ADD CONSTRAINT UQ_PID UNIQUE(PID)； ALTER TABLE userInfo ADD CONSTRAINT CK_telephone CHECK(regexp_like(telephone，'；(^d{3，4}-d{8}$)(^d{11}$)'；))； /*cardInfo表的约束cardID 卡号 必填，主健 ， 银行的卡号规则和电话号码一样，一般前8位代表特殊含义， 如某总行某支行等。假定该行要求其营业厅的卡号格式为：1010 3576 xxxx xxx开始curType 货币 必填，默认为RMBopenDate 开户日期 必填，默认为系统当前日期openMoney 开户金额 必填，不低于1元balance 余额 必填，不低于1元，否则将销户pass 密码 必填，6位数字，默认为6个8IsReportLoss 是否挂失 必填，0/1值，默认为0未挂失customerID 顾客编号 必填，表示该卡对应的顾客编号，一位顾客可以办理多张卡*/ ALTER TABLE cardInfo ADD CONSTRAINT PK_cardID PRIMARY KEY(cardID)；ALTER TABLE cardInfo ADD CONSTRAINT CK_cardID CHECK(regexp_LIKE(cardID，'；1010 3576 d{4} d{4}'；))；ALTER TABLE cardInfo MODIFY (curID VARCHAR(10) DEFAULT '；RMB'；)；ALTER TABLE cardInfo MODIFY (openDate DATE DEFAULT sysdate)；ALTER TABLE cardInfo ADD CONSTRAINT CK_openMoney CHECK(openMoney>；=1)；ALTER TABLE cardInfo ADD CONSTRAINT CK_balance CHECK(balance>；=1)；ALTER TABLE cardInfo ADD CONSTRAINT CK_pass CHECK(regexp_LIKE(pass，'；^[0-9]{6}$'；))；ALTER TABLE cardInfo MODIFY (pass CHAR(6) DEFAULT '；888888'；)；ALTER TABLE cardInfo MODIFY (IsReportLoss NUMBER(1) DEFAULT 0)；ALTER TABLE cardInfo ADD CONSTRAINT FK_customerID FOREIGN KEY(customerID) REFERENCES userInfo(customerID)；ALTER TABLE cardInfo ADD CONSTRAINT FK_savingID FOREIGN KEY(savingID) REFERENCES deposit(savingID)； /* tradeInfo表的约束tradeType 必填，只能是存入/支取 cardID 卡号 必填，外健，可重复索引tradeMoney 交易金额 必填，大于0tradeDate 交易日期 必填，默认为系统当前日期remark 备注 可选输入，其他说明*/ ALTER TABLE tradeInfo ADD CONSTRAINT CK_tradeType CHECK(tradeType IN ('；存入'；，'；支取'；))；ALTER TABLE tradeInfo ADD CONSTRAINT FK_cardID FOREIGN KEY(cardID) REFERENCES cardInfo(cardID)；ALTER TABLE tradeInfo ADD CONSTRAINT CK_tradeMoney CHECK(tradeMoney>；0)；ALTER TABLE tradeInfo MODIFY (tradeDate DATE DEFAULT sysdate)；
用例3：插入测试数据
-- 存款类型INSERT INTO deposit (savingID，savingName，descrip) VALUES (1，'；活期'；，'；按存款日结算利息'；)；INSERT INTO deposit (savingID，savingName，descrip) VALUES (2，'；定期一年'；，'；存款期是1年'；)；INSERT INTO deposit (savingID，savingName，descrip) VALUES (3，'；定期二年'；，'；存款期是2年'；)；INSERT INTO deposit (savingID，savingName，descrip) VALUES (4，'；定期三年'；，'；存款期是3年'；)；INSERT INTO deposit (savingID，savingName) VALUES (5，'；定活两便'；)；INSERT INTO deposit (savingID，savingName) VALUES (6，'；通知'；)；INSERT INTO deposit (savingID，savingName，descrip) VALUES (7，'；零存整取一年'；，'；存款期是1年'；)；INSERT INTO deposit (savingID，savingName，descrip) VALUES (8，'；零存整取二年'；，'；存款期是2年'；)；INSERT INTO deposit (savingID，savingName，descrip) VALUES (9，'；零存整取三年'；，'；存款期是3年'；)；INSERT INTO deposit (savingID，savingName，descrip) VALUES (10，'；存本取息五年'；，'；按月支取利息'；)；SELECT * FROM DEPOSIT； INSERT INTO userInfo(customerID，customerName，PID，telephone，address ) VALUES(1，'；张三'；，'；123456789012345'；，'；010-67898978'；，'；北京海淀'；)；INSERT INTO cardInfo(cardID，savingID，openMoney，balance，customerID) VALUES('；1010 3576 1234 5678'；，1，1000，1000，1)； INSERT INTO userInfo(customerID，customerName，PID，telephone) VALUES(2，'；李四'；，'；321245678912345678'；，'；0478-44443333'；)；INSERT INTO cardInfo(cardID，savingID，openMoney，balance，customerID) VALUES('；1010 3576 1212 1134'；，2，1，1，2)； INSERT INTO userInfo(customerID，customerName，PID，telephone) VALUES(3，'；王五'；，'；567891234532124670'；，'；010-44443333'；)；INSERT INTO cardInfo(cardID，savingID，openMoney，balance，customerID) VALUES('；1010 3576 1212 1130'；，2，1601，1601，3)； INSERT INTO userInfo(customerID，customerName，PID，telephone) VALUES(4，'；丁六'；，'；567891321242345618'；，'；0752-43345543'；)；INSERT INTO cardInfo(cardID，savingID，openMoney，balance，customerID) VALUES('；1010 3576 1212 1004'；，2，1，1，4)； select * from userinfo；select * from cardinfo；select * from tradeinfo；select * from deposit； /*张三的卡号（1010 3576 1234 5678）取款900元，李四的卡号（1010 3576 1212 1134）存款5000元，要求保存交易记录，以便客户查询和银行业务统计。说明：当存钱或取钱（如300元）时候，会往交易信息表（tradeInfo）中添加一条交易记录， 同时应更新银行卡信息表（cardInfo）中的现有余额（如增加或减少500元）*/-- 交易信息表插入交易记录INSERT INTO tradeInfo(tradeType，cardID，tradeMoney) VALUES('；支取'；，'；1010 3576 1234 5678'；，900) ；-- 更新银行卡信息表中的现有余额UPDATE cardInfo SET balance=balance-900 WHERE cardID='；1010 3576 1234 5678'；；-- 交易信息表插入交易记录INSERT INTO tradeInfo(tradeType，cardID，tradeMoney) VALUES('；存入'；，'；1010 3576 1212 1130'；，300) ；-- 更新银行卡信息表中的现有余额UPDATE cardInfo SET balance=balance+300 WHERE cardID='；1010 3576 1212 1130'；；-- 交易信息表插入交易记录INSERT INTO tradeInfo(tradeType，cardID，tradeMoney) VALUES('；存入'；，'；1010 3576 1212 1004'；，1000) ；-- 更新银行卡信息表中的现有余额UPDATE cardInfo SET balance=balance+1000 WHERE cardID='；1010 3576 1212 1004'；；-- 交易信息表插入交易记录INSERT INTO tradeInfo(tradeType，cardID，tradeMoney) VALUES('；支取'；，'；1010 3576 1212 1130'；，1900) ；-- 更新银行卡信息表中的现有余额--报错UPDATE cardInfo SET balance=balance-1900 WHERE cardID='；1010 3576 1212 1130'；；-- 交易信息表插入交易记录INSERT INTO tradeInfo(tradeType，cardID，tradeMoney) VALUES('；存入'；，'；1010 3576 1212 1134'；，5000) ；-- 更新银行卡信息表中的现有余额UPDATE cardInfo SET balance=balance+5000 WHERE cardID='；1010 3576 1212 1134'；；commit；-- 检查测试数据是否正确SELECT * FROM cardInfo；SELECT * FROM tradeInfo；SELECT * FROM userInfo；select * from deposit；
用例4：模拟常规业务
-- 修改密码--1。张三（卡号为1010 3576 1234 5678）修改银行卡密码为123456--2。李四（卡号为1010 3576 1212 1134）修改银行卡密码为123123update cardInfo set pass='；123456'； WHERE cardID='；1010 3576 1234 5678'； ；update cardInfo set pass='；123123'； WHERE cardID='；1010 3576 1212 1134'； ；--查询账户信息SELECT * FROM cardInfo；-- 失帐号。李四（卡号为1010 3576 1212 1134）因银行卡丢失，申请挂失update cardInfo set IsReportLoss=1 WHERE cardID='；1010 3576 1212 1134'； ；SELECT * FROM cardInfo；--查看修改密码和挂失结果SELECT cardid 卡号，curID 货币，savingName 储蓄种类，opendate 开户日期，openmoney 开户金额，balance 余额，pass 密码， case IsReportLoss WHEN 1 THEN '；挂失'； WHEN 0 THEN '；未挂失'； ELSE NULL end 是否挂失， customerName 客户姓名FROM CardInfo， Deposit， UserInfoWHERE CardInfo。savingID=Deposit。savingID and CardInfo。customerID = UserInfo。customerID；-- 统计银行的资金流通余额和盈利结算，统计说明！存款代表资金流入，取款代表资金。假定存款利率为千分之3，贷款利率为千分之8，单一货币RMBDECLARE v_inMoney number； v_outMoney number； v_profit number；begin --SELECT * FROM tradeInfo SELECT sum(tradeMoney) into v_inMoney FROM tradeInfo WHERE (tradeType='；存入'；)； SELECT sum(tradeMoney) into v_outMoney FROM tradeInfo WHERE (tradeType='；支取'；)； dbms_output。put_line('；银行流通余额总计为！'；to_char(v_inMoney-v_outMoney)'；RMB'；)； v_profit！=v_outMoney*0。008-v_inMoney*0。003； dbms_output。put_line('；盈利结算为！'；to_char(v_profit)'；RMB'；)；end；-- 查询本周开户的卡号，显示该卡相关信息SELECT c。cardID 卡号，u。customerName 姓名，c。curID 货币，d。savingName 存款类型，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)INNER JOIN Deposit d ON (c。savingID = d。savingID ) WHERE openDate between trunc(sysdate，'；DAY'；) and trunc(sysdate，'；DAY'；)+6；-- 查询本月交易金额最高的卡号SELECT * FROM tradeInfo；SELECT DISTINCT cardID FROM tradeInfo WHERE tradeMoney=(SELECT Max(tradeMoney) FROM tradeInfo WHERE to_char(tradeDate，'；yyyy-mm'；)=to_char(sysdate，'；yyyy-mm'；))；-- 查询挂失帐号的客户信息SELECT customerName as 客户姓名，telephone as 联系电话 FROM userInfo WHERE customerID IN (SELECT customerID FROM cardInfo WHERE IsReportLoss=1)；-- 催款提醒：例如某种业务的需要，每个月末，如果发现用户帐上余额少于200元，将致电催款SELECT customerName as 客户姓名，telephone as 联系电话，balance as 存款余额 FROM userInfo INNER JOIN cardInfo ON userInfo。customerID=cardInfo。customerID WHERE balance<；200； select * from userinfo；
用例5、利用视图实现数据查询
--1。创建视图：为了向客户显示信息友好，查询各表要求字段全为中文字段名。 create or replace VIEW vw_userInfo --客户信息表视图 AS select customerID as 客户编号，customerName as 开户名， PID as 身份证号， telephone as 电话号码，address as 居住地址 from userInfo；--使用视图SELECT * FROM vw_userInfo；--2。创建视图：查询银行卡信息create or replace VIEW vw_cardInfo --银行卡信息表视图 AS select c。cardID as 卡号，u。customerName as 客户，c。curID as 货币种类， d。savingName as 存款类型，c。openDate as 开户日期， c。balance as 余额，c。pass 密码， case c。IsReportLoss when 0 then '；正常'； when 1 then '；挂失'； end as 是否挂失 from cardInfo c， deposit d，userinfo u where c。savingID=d。savingID and c。customerID=u。customerID；--使用视图SELECT * FROM vw_cardInfo；--3。创建视图：查看交易信息create VIEW vw_tradeInfo --交易信息表视图 AS select tradeDate as 交易日期，tradeType as 交易类型， cardID as 卡号，tradeMoney as 交易金额， remark as 备注 from tradeInfo ；--使用视图SELECT * FROM vw_tradeInfo；--4。根据客户登录名（采用实名制访问银行系统）查询该客户帐户信息的视图create or replace VIEW vw_oneUserInfo AS select customerID as 客户编号，customerName as 开户名， PID as 身份证号， telephone as 电话号码，address as 居住地址 from userInfo where UPPER(TRIM(customerName)) in (select UPPER(TRIM(username)) from user_users)；--使用视图select * from vw_oneUserInfo；
用例6、用存储过程实现业务处理
select * from tradeinfo；select * from cardinfo；-- 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；--调用存储过程取钱或存钱 张三取300，--现实中的取款机依靠读卡器读出张三的卡号，这里根据张三的名字查出考号来模拟 DECLARE emp_20000 EXCEPTION； PRAGMA EXCEPTION_INIT(emp_20000， -20000)； emp_20001 EXCEPTION； PRAGMA EXCEPTION_INIT(emp_20001， -20001)； v_card char(19)；BEGIN select cardID into v_card from cardInfo Inner Join userInfo ON cardInfo。customerID=userInfo。customerID where customerName='；张三'；； usp_takeMoney(v_card，300 ，'；支取'；，'；123456'；)；EXCEPTION WHEN emp_20000 THEN DBMS_OUTPUT。PUT_LINE('；交易失败！余额不足！'；)； rollback； WHEN emp_20001 THEN DBMS_OUTPUT。PUT_LINE('；密码错误！'；)； rollback； WHEN no_data_found THEN DBMS_OUTPUT。PUT_LINE('；用户名不存在！'；)； rollback； WHEN OTHERS THEN DBMS_OUTPUT。PUT_LINE('；出现了其他异常错误'；)； rollback；END； -- 调用存储过程，李四存500 DECLARE emp_20000 EXCEPTION； PRAGMA EXCEPTION_INIT(emp_20000， -20000)； emp_20001 EXCEPTION； PRAGMA EXCEPTION_INIT(emp_20001， -20001)； v_card char(19)；BEGIN select cardID into v_card from cardInfo Inner Join userInfo ON cardInfo。customerID=userInfo。customerID where customerName='；李四'；； usp_takeMoney(v_card，500 ，'；存入'；)；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； select * from vw_cardInfo；select * from vw_tradeInfo； -- 2。产生随机卡号的存储过程(dbms_random包来实现) create or replace procedure usp_randCardID(v_randCardID OUT char)AS v_r number(8)；BEGIN v_r！=round(dbms_random。value(10000000，99999999))； v_randCardID！='；1010 3576 '；substr(v_r，1，4)'； '；substr(v_r，5，8)；end； --测试产生随机卡号DECLARE v_mycardID char(19) ；BEGIN usp_randCardID(v_mycardID)； dbms_output。put_line('；产生的随机卡号为：'；v_mycardID)；END； -- 3。开户的存储过程select * from userInfo；CREATE SEQUENCE seq_customerID START WITH 10 INCREMENT BY 1 NOMAXVALUE NOCYCLE CACHE 30；create or replace procedure usp_openAccount( v_customerName char， v_PID char， v_telephone char， v_openMoney number， v_savingName char， v_address varchar default '；'； )AS v_mycardID char(19)； v_cur_customerID int； v_savingID 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； 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 savingID into v_savingID FROM deposit WHERE savingName =v_savingName； 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 ('；王老五'；，'；334456889012678'；，'；2222-63598978'；，1000，'；活期'；，'；河南新乡'；)； commit；end；--EXEC usp_openAccount('；赵小二'；，'；213445678912342222'；，'；0760-44446666'；，1，'；定期'；)；select * from vw_userInfo；select * from vw_cardInfo；select * from vw_tradeInfo；GO -- 4。输入页数和每页显示的记录数，实现分页显示--DROP PROCEDURE usp_pagingDisplay SELECT tradeDate 交易日期，tradeType 交易类型，cardID 卡号，trademoney 交易金额 FROM (SELECT t。*，rownum rn FROM (SELECT * FROM tradeInfo ) t)WHERE rn>；=4 and rn<；=6； CREATE OR REPLACE PROCEDURE usp_pagingDisplay( v_page number！= 1， v_records number！= 10)AS v_rec1 number； v_rec2 number； v_statement varchar2(200)； TYPE cursor_type IS REF CURSOR； --声明一个游标变量 c1 CURSOR_TYPE； v_trade tradeinfo%rowtype；begin v_rec1！= (v_page-1)*v_records+1； v_rec2！= v_page*v_records； v_statement！='；SELECT tradeDate，tradeType，cardID，trademoney，REMARK '；； v_statement！=v_statement'；FROM (SELECT t。*，rownum rn FROM (SELECT * FROM tradeInfo ) t) '；； v_statement！=v_statement'；WHERE rn>；='；v_rec1'； and rn<；='；v_rec2； --dbms_output。put_line(v_statement)； dbms_output。put_line('；交易日期 交易类型 卡号 交易金额 '；)； dbms_output。put_line('；---------------------------------------------------------'；)； open c1 for v_statement； 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。cardID'； '；v_trade。trademoney)； END LOOP； CLOSE c1；END； BEGIN usp_pagingDisplay(2，2)；END； -- 5。打印对账单--drop proc usp_CheckSheetCREATE 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_savingName 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，d。savingName ，c。openDate INTO v_curName，v_custName，v_savingName， v_openDate FROM cardInfo c inner join userInfo u on c。customerID=u。customerID inner join deposit d on c。savingID = d。savingID WHERE cardID = v_cardID； --and u。customerName = user_name() dbms_output。put_line('；卡号：'； v_cardID)； dbms_output。put_line('；姓名！'； v_custName)； dbms_output。put_line('；货币！'； v_curName)； dbms_output。put_line('；存款类型：'； v_savingName)； 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；--调用begin -- Test statements here usp_CheckSheet('；1010 3576 1212 1130'；，to_date('；2012-12-01'；，'；yyyy-mm-dd'；)，to_date('；2013-11-01'；，'；yyyy-mm-dd'；))；end；-- 6。查询、统计在指定时间段内没有发生交易的账户信息-- drop proc usp_getWithoutTradecreate or replace procedure usp_getWithoutTrade( v_Num out number ， v_Amount out number ， v_date1 date ！= NULL， v_date2 date ！= NULL)AS v_sd date； v_ed date；TYPE cursor_type IS REF CURSOR；c1 CURSOR_TYPE；v_cur userInfo%rowtype；BEGIN IF v_date1 IS NULL THEN v_sd！=trunc(sysdate，'；month'；)； END IF； IF v_date2 IS NULL THEN v_ed ！= sysdate； END IF； dbms_output。put_line('；客户号 '；'； 客户姓名'；'； 身份证号'；'； 电话'；'； 地址'；)； dbms_output。put_line('；---------------------------------------------------------------'；)； open c1 for SELECT distinct u。customerID，u。customerName，u。PID，u。telephone，address FROM userInfo u JOIN cardInfo c ON u。customerID = c。customerID WHERE c。cardID NOT IN (SELECT cardID FROM tradeInfo WHERE tradeDate Between v_sd and v_ed)； LOOP FETCH c1 INTO v_cur ； EXIT WHEN c1%NOTFOUND； DBMS_OUTPUT。PUT_LINE(v_cur。customerID'； '；v_cur。customerName'； '；v_cur。PID'； '；v_cur。telephone'； '；v_cur。address)； END LOOP； CLOSE c1； SELECT COUNT(customerID)，SUM(balance) into v_Num，v_Amount FROM cardInfo WHERE cardID NOT IN (SELECT cardID FROM tradeInfo WHERE tradeDate Between v_sd and v_ed)；end； --调用存储过程DECLARE v_NUM number； v_Amount number(18，2)； v_date1 date； v_date2 date；begin v_date1！= to_date('；2009-1-1'；，'；yyyy-mm-dd'；)； v_date2！= sysdate； usp_getWithoutTrade(v_NUM， v_Amount)；--， @date1， @date2 dbms_output。put_line('；统计未发生交易的客户'；)； dbms_output。put_line( '；---------------------------------------'；)； dbms_output。put_line( '；客户人数：'； v_NUM '； 客户总余额：'； v_Amount)；end； -- 7。统计银行卡交易量和交易额--drop proc usp_getTradeInfocreate or replace procedure usp_getTradeInfo( v_Num1 out number， v_Amount1 out number， v_Num2 out number， v_Amount2 out number， v_date1 date！=trunc(sysdate，'；year'；)， v_date2 date！=sysdate， v_address varchar！= NULL)ASBEGIN IF v_address IS NULL THEN SELECT COUNT(tradeMoney)， SUM(tradeMoney) into v_num1，v_Amount1 FROM tradeInfo WHERE tradeDate BETWEEN v_date1 AND v_date2 AND tradeType='；存入'；； SELECT COUNT(tradeMoney)， SUM(tradeMoney) into v_num2，v_Amount2 FROM tradeInfo WHERE tradeDate BETWEEN v_date1 AND v_date2 AND tradeType='；支取'；； ELSE SELECT COUNT(tradeMoney)， SUM(tradeMoney) into v_num1，v_Amount1 FROM tradeInfo JOIN cardInfo ON tradeInfo。cardID = cardInfo。cardID JOIN userInfo ON cardInfo。customerID = userInfo。customerID WHERE tradeDate BETWEEN v_date1 AND v_date2 AND tradeType='；存入'； AND address Like '；%'；v_address'；%'；； SELECT COUNT(tradeMoney)， SUM(tradeMoney) into v_num2，v_Amount2 FROM tradeInfo JOIN cardInfo ON tradeInfo。cardID = cardInfo。cardID JOIN userInfo ON cardInfo。customerID = userInfo。customerID WHERE tradeDate BETWEEN v_date1 AND v_date2 AND tradeType='；支取'； AND address Like '；%'；v_address'；%'；； END IF； v_num1！=nvl(v_num1，0)； v_num2！=nvl(v_num2，0)； v_Amount1！=nvl(v_Amount1，0)； v_Amount2！=nvl(v_Amount2，0)；end； --调用 declare v_CNT1 number； v_Total1 number(18，2)； v_CNT2 number； v_Total2 number(18，2)； v_date1 date； v_date2 date；begin v_date1 ！= to_date('；2009-1-1'；，'；yyyy-mm-dd'；)； v_date2 ！= sysdate； usp_getTradeInfo (v_CNT1， v_Total1， v_CNT2， v_Total2， v_date1，v_date2，'；北京海淀'；)；--， '；北京'；； dbms_output。put_line('；统计银行卡交易量和交易额'；)； dbms_output。put_line('；'；)； dbms_output。put_line('；起始日期：'； to_char(v_date1，'；yyyy-mm-dd'；) '； 截止日期：'； to_char(v_date2，'；yyyy-mm-dd'；))； dbms_output。put_line('；-----------------------------------------------------------'；)； dbms_output。put_line('；存入笔数：'； v_CNT1 '； 存入金额：'； v_Total1)； dbms_output。put_line( '；支取笔数：'； v_CNT2 '； 支取金额：'； v_Total2)； dbms_output。put_line('；-----------------------------------------------------------'；)； dbms_output。put_line('；发生笔数：'； (v_CNT1+v_CNT2) '； 结余金额：'； (v_Total1-v_Total2))；end；
用例7、实现较复杂的数据更新
--转帐的事务存储过程-- drop proc usp_tradefer 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； --测试上述事务存储过程--从李四的帐户转帐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； select * from vw_userInfo；select * from vw_cardInfo；select * from vw_tradeInfo；
评论