--创建表空间
CREATE TABLESPACE tablespace_bank
DATAFILE 'e:\bankatm.dbf'
SIZE 10M
AUTOEXTEND ON ;
--创建用户
create user hai
identified by hai
default tablespace tablespace_bank;
--用户授权
grant connect,resource to userBank;
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
--drop CONSTRAINT CK_telephone
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 货币 必填，默认为RMB
savingType 存款种类 活期/定活两便/定期
openDate 开户日期 必填，默认为系统当前日期
openMoney 开户金额 必填，不低于1元
balance 余额 必填，不低于1元,否则将销户
pass 密码 必填，6位数字，默认为6个8
IsReportLoss 是否挂失 必填，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');
--CONSTRAINT CK_savingType CHECK(savingType IN ('活期','定活两便','定期')),
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 交易金额 必填，大于0
tradeDate 交易日期 必填，默认为系统当前日期
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);
--存款类型
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-********','北京海淀');
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-********');
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-********');
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-********');
INSERT INTO cardInfo(cardID,savingID,openMoney,balance,customerID)
VALUES('1010 3576 1212 1004',2,1,1,4);
SELECT * FROM cardInfo;
/*
张三的卡号（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;
--delete from tradeInfo;
--delete from cardInfo;
--delete from userInfo;
-----------------银行ATM业务需求----------------------
---------1.修改密码-----
--1.张三（卡号为1010
3576 1234 5678）修改银行卡密码为123456
--2.李四（卡号为1010 3576 1212 1134）修改银行卡密码为123123
update cardInfo set pass='123456' WHERE cardID='1010 3576 1234 5678' ;
update cardInfo set pass='123123' WHERE cardID='1010 3576 1212 1134' ;
--查询账户信息
SELECT * FROM cardInfo;
---------2.挂失帐号---------
--李四（卡号为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, UserInfo
WHERE CardInfo.savingID=Deposit.savingID and CardInfo.customerID = UserInfo.customerID;
--------3.统计银行的资金流通余额和盈利结算------------------------------
--统计说明:存款代表资金流入,取款代表资金.假定存款利率为千分之3,贷款利率为千分之8
--单一货币RMB--
DECLARE
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;
--------4.查询本周开户的卡号,显示该卡相关信息-----------------
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
---------5.查询本月交易金额最高的卡号----------------------
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'));
---------6.查询挂失帐号的客户信息---------------------
SELECT customerName as 客户姓名,telephone as 联系电话 FROM userInfo
WHERE customerID IN (SELECT customerID FROM cardInfo WHERE IsReportLoss=1);
------7.催款提醒：例如某种业务的需要，每个月末，如果发现用户帐上余额少于200元，将致电催款。-------
SELECT customerName as 客户姓名,telephone as 联系电话,balance as 存款余额
FROM userInfo INNER JOIN cardInfo ON userInfo.customerID=cardInfo.customerID
WHERE balance<200;
----------------------------------------视图操作----------------------------------
-------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;
-----------------------------------使用存储过程来实现业务处理-----------------------------------------------
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;
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_CheckSheet-------------------------
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_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-d
d 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
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_getWithoutTrade-------------------------------------
create 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_getTradeInfo---------------------------
create 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)
AS
BEGIN
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;
---------------------------------------------------------------------------------------------------
----------------------转帐的事务存储过程-------------------------
---------------------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_out
money ,'存入');
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;