-- --------------------------------------------------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'本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
