Mysql專案實戰-ATM
一、專案需求說明
二、專案代碼
專案需求
步驟一：創建表，并為各表添加約束
儲戶表：儲戶ID，姓名，身份證，聯系電話，聯系地址
要求：
1.儲戶ID為主鍵且自增
2.身份證號碼唯一，15或18位
銀行卡資訊表：卡號，幣種，存盤型別（定期，活期），開戶時間，開戶金額，當前賬戶余額，密碼，是否掛失，儲戶ID
要求：
1.卡號主鍵，卡號必須是0-9的數字，格式xxxx xxxx xxxx xxxx以1010 3576開頭
2.存盤型別只能是下面三種之一：活期，定期，活定兩便
3.開戶金額必須大于1元
4.密碼必須大于或者等于6位
5.是否掛失只能是下面兩種之一：是，否
6.儲戶ID是外鍵，關聯儲戶表的主鍵，
7.幣種默認RMB
8.開戶時間默認當前時間
9.密碼默認888888
10.是否掛失默認：否
11.根據卡號創建銀行卡資訊表的索引優化表查詢，
交易資訊表：交易日期，卡號，交易型別，交易金額，備注
要求：
1.卡號為外鍵，關聯銀行卡資訊表主鍵
2.交易型別只能是下面兩種之一：存入，支取
3.交易金額必須大于0
4.交易時間默認為當前時間
測驗：至少添加10條儲戶表和銀行卡資訊表資料，
步驟二：創建視圖,要求如下：
1.創建儲戶表視圖
2.創建銀行卡資訊表視圖
3.創建交易資訊表視圖
4.創建查詢掛失的客戶資訊視圖
5.創建查詢本周開戶的卡號顯示相關資訊視圖
6.創建查詢本月交易金額最高的卡號的視圖
測驗：分別測驗上面各個視圖是否顯示正常
步驟三：創建觸發器，要求如下：
1.不允許修改卡號：當更新的是銀行卡資訊表的卡號時提示“此列不允許修改”
2.當交易資訊表有插入或者修改記錄，銀行卡資訊表跟隨改變：如交易資訊表新增一條支取，那么對應的銀行資訊卡余額應該減少對應支取的金額，同理如交易資訊表新增一條存入，那么對應的銀行資訊卡余額應該增加對應存入的金額，注意：如果支取先判斷余額，不足則提示，如交易成功列印恭喜資訊，
測驗：分別針對交易資訊表做支取和存入的操作，
步驟四：創建針對儲戶資訊的函式和存盤程序
1.創建函式：卡號隨機生成，注意卡號的格式1010 3576 XXXX XXXX
2.創建開戶的存盤程序：注意卡號呼叫上面函式，開戶程序包含插入資訊到儲戶表及銀行卡資訊表
3.創建修改密碼的存盤程序
4.創建掛失賬號的存盤程序
測驗：執行開戶的存盤程序，執行修改密碼的存盤程序，執行掛失的存盤程序，
步驟五：創建針對銀行卡資訊表的存盤程序
1.創建支取和存入的存盤程序，注意只需要操作交易表，而銀行卡資訊表的余額由上面步驟三中的觸發器維護
2.創建查詢余額存盤程序
3.創建轉賬存盤程序，注意：判斷要轉賬及要轉入的賬戶是否都存在，轉賬金額是否充足，發生例外交易失敗要回滾
4.創建銷戶存盤程序，注意銷戶要洗掉儲戶資訊表，銀行卡資訊表，交易資訊表中與之相關的所有記錄
測驗：執行取錢和存錢的存盤程序，執行查詢余額的存盤程序，執行轉賬的存盤程序，執行銷戶的存盤程序，
步驟一：創建表，并為各表添加約束
儲戶表：儲戶ID，姓名，身份證，聯系電話，聯系地址
要求：
1.儲戶ID為主鍵且自增
2.身份證號碼唯一，15或18位
銀行卡資訊表：卡號，幣種，存盤型別（定期，活期），開戶時間，開戶金額，當前賬戶余額，密碼，是否掛失，儲戶ID
要求：
1.卡號主鍵，卡號必須是0-9的數字，格式xxxx xxxx xxxx xxxx以1010 3576開頭
2.存盤型別只能是下面三種之一：活期，定期，活定兩便
3.開戶金額必須大于1元
4.密碼必須大于或者等于6位
5.是否掛失只能是下面兩種之一：是，否
6.儲戶ID是外鍵，關聯儲戶表的主鍵，
7.幣種默認RMB
8.開戶時間默認當前時間
9.密碼默認888888
10.是否掛失默認：否
11.根據卡號創建銀行卡資訊表的索引優化表查詢，
交易資訊表：交易日期，卡號，交易型別，交易金額，備注
要求：
1.卡號為外鍵，關聯銀行卡資訊表主鍵
2.交易型別只能是下面兩種之一：存入，支取
3.交易金額必須大于0
4.交易時間默認為當前時間
測驗：至少添加10條儲戶表和銀行卡資訊表資料，
步驟二：創建視圖,要求如下：
1.創建儲戶表視圖
2.創建銀行卡資訊表視圖
3.創建交易資訊表視圖
4.創建查詢掛失的客戶資訊視圖
5.創建查詢本周開戶的卡號顯示相關資訊視圖
6.創建查詢本月交易金額最高的卡號的視圖
測驗：分別測驗上面各個視圖是否顯示正常
步驟三：創建觸發器，要求如下：
1.不允許修改卡號：當更新的是銀行卡資訊表的卡號時提示“此列不允許修改”
2.當交易資訊表有插入或者修改記錄，銀行卡資訊表跟隨改變：如交易資訊表新增一條支取，那么對應的銀行資訊卡余額應該減少對應支取的金額，同理如交易資訊表新增一條存入，那么對應的銀行資訊卡余額應該增加對應存入的金額，注意：如果支取先判斷余額，不足則提示，如交易成功列印恭喜資訊，
測驗：分別針對交易資訊表做支取和存入的操作，
步驟四：創建針對儲戶資訊的函式和存盤程序
1.創建函式：卡號隨機生成，注意卡號的格式1010 3576 XXXX XXXX
2.創建開戶的存盤程序：注意卡號呼叫上面函式，開戶程序包含插入資訊到儲戶表及銀行卡資訊表
3.創建修改密碼的存盤程序
4.創建掛失賬號的存盤程序
測驗：執行開戶的存盤程序，執行修改密碼的存盤程序，執行掛失的存盤程序，
步驟五：創建針對銀行卡資訊表的存盤程序
1.創建支取和存入的存盤程序，注意只需要操作交易表，而銀行卡資訊表的余額由上面步驟三中的觸發器維護
2.創建查詢余額存盤程序
3.創建轉賬存盤程序，注意：判斷要轉賬及要轉入的賬戶是否都存在，轉賬金額是否充足，發生例外交易失敗要回滾
4.創建銷戶存盤程序，注意銷戶要洗掉儲戶資訊表，銀行卡資訊表，交易資訊表中與之相關的所有記錄
測驗：執行取錢和存錢的存盤程序，執行查詢余額的存盤程序，執行轉賬的存盤程序，執行銷戶的存盤程序，
-- 用戶表：用戶ID，姓名，身份證，聯系電話，聯系地址
/*------------------------------------
1. 用戶ID為主鍵 自增
2. 身份證號碼唯一，15或18位
-------------------------------------*/
create table atm_user(
u_id int not null auto_increment,
u_name varchar(20) character set utf8 not null,
u_card_id varchar(18) not null,
u_phone varchar(11) not null,
u_address varchar(50) character set utf8,
primary key(u_id),
constraint u_card_id_unique unique(u_card_id)
);
-- drop trigger trg_tb_user_insert_check;
-- 創建觸發器 身份證校驗
create trigger trg_tb_user_insert_check before insert
on atm_user for each row
begin
declare msg varchar(100) character set utf8;
if new.u_card_id not regexp '[0-9]{18}|[0-9]{17}X|[0-9]{15}'
then
set msg = concat('用戶身份證輸入錯誤請重新輸入');
signal sqlstate 'ERROR' set message_text = msg;
end if;
end;
/*
銀行資訊表：卡號，幣種（人民幣），存盤型別（定期，活期，活定兩便）,開戶時間，開戶金額，當前賬戶金額，密碼，是否掛失，儲戶ID
*/
create table bank_card_message(
card_id char(21) not null primary key,
card_currency varchar(20) default 'RMB' not null,
card_store_type enum('活期','定期','活定兩便') character set utf8,
card_open_time timestamp not null default CURRENT_TIMESTAMP,
card_open_amount decimal(10,2) not null,
card_cur_balance decimal(10,2) default 0.00 not null,
card_password varchar(20) default '888888' not null,
card_if_loss enum('是','否') character set utf8 default '否',
u_id int not null,
constraint bc_u_fk foreign key(u_id) references atm_user(u_id) on delete cascade
);
-- drop trigger trg_tb_bank_insert_check;
-- 銀行資訊表約束觸發器： 卡號，開戶金額，密碼
create trigger trg_tb_bank_insert_check before insert
on bank_card_message for each row
begin
declare b_msg varchar(100) character set utf8;
if new.card_id not regexp '^1010 3576 [0-9]{4} [0-9]{4}$'
then
set b_msg = concat('卡號輸入錯誤請重新輸入');
signal sqlstate 'ERROR' set message_text = b_msg;
elseif new.card_open_amount < 1
then
set b_msg = concat('開戶金額必須大于1');
signal sqlstate 'ERROR' set message_text = b_msg;
elseif CHAR_LENGTH(new.card_password) < 6
then
set b_msg = concat('密碼必須大于或等于6位');
signal sqlstate 'ERROR' set message_text = b_msg;
else
set new.card_cur_balance = new.card_open_amount; -- 設定當前余額為開戶金額
end if;
end;
/*
交易資訊表：交易日期，卡號，交易型別，交易金額，備注
*/
create table atm_tra_info(
card_id char(21) not null,
tr_type enum('存入','支取') character set utf8 default '存入',
tr_price decimal(10,2) not null,
tr_date timestamp not null default CURRENT_TIMESTAMP,
ty_remark varchar(100),
constraint tri_bc_fk foreign key(card_id) references bank_card_message(card_id) on delete cascade
);
-- drop trigger trg_tb_tri_insert_check;
-- 交易資訊表觸發器：交易金額
create trigger trg_tb_tri_insert_check before insert
on atm_tra_info for each row
begin
declare t_msg varchar(100) character set utf8;
set @oldloss = (select bank_card_message.card_if_loss from bank_card_message where bank_card_message.card_id = new.card_id limit 1);
set @oldprice = (select bank_card_message.card_cur_balance from bank_card_message where bank_card_message.card_id = new.card_id limit 1);
if @oldloss = '是'
then
set t_msg = concat('該銀行卡已掛失無法進行交易');
signal sqlstate 'ERROR' set message_text = t_msg;
elseif new.tr_price <= 0
then
set t_msg = concat('交易金額必須大于0');
signal sqlstate 'ERROR' set message_text = t_msg;
-- 關聯銀行卡資訊表存入支取操作觸發器
elseif new.tr_type = '存入'
then
update bank_card_message set bank_card_message.card_cur_balance = new.tr_price+@oldprice where bank_card_message.card_id=new.card_id;
-- 只能發送錯誤的提示信號，所以無法提示恭喜
elseif new.tr_type = '支取'
then
if new.tr_price > @oldprice
then
set t_msg = concat('支取金額大于余額');
signal sqlstate 'ERROR' set message_text = t_msg;
elseif new.tr_price < @oldprice
then
update bank_card_message set bank_card_message.card_cur_balance = @oldprice-new.tr_price where bank_card_message.card_id=new.card_id;
end if;
end if;
end;
-- 洗掉觸發器
-- drop trigger trg_tb_tri_update_check;
-- 創建觸發器
create trigger trg_tb_tri_update_check before update
on atm_tra_info for each row
begin
declare p_msg varchar(100) character set utf8;
set @oldprice = (select bank_card_message.card_cur_balance from bank_card_message where bank_card_message.card_id = new.card_id limit 1);
set @oldlossif = (select bank_card_message.card_if_loss from bank_card_message where bank_card_message.card_id = new.card_id limit 1);
if @oldlossif = '是'
then
set p_msg = concat('該銀行卡已掛失，無法進行操作');
signal sqlstate 'ERROR' set message_text = p_msg;
elseif new.tr_price <= 0
then
set p_msg = concat('交易金額必須大于0');
signal sqlstate 'ERROR' set message_text = p_msg;
end if;
-- 關聯銀行卡資訊表存入支取修改操作觸發器
if new.tr_type = old.tr_type
then
if new.tr_type = '存入'
then
update bank_card_message set bank_card_message.card_cur_balance = new.tr_price+@oldprice-old.tr_price where bank_card_message.card_id=new.card_id;
-- -- 只能發送錯誤的提示信號，所以無法提示恭喜
elseif new.tr_type = '支取'
then
if new.tr_price > @oldprice
then
set p_msg = concat('支取金額大于余額');
signal sqlstate 'ERROR' set message_text = p_msg;
elseif new.tr_price < @oldprice
then
update bank_card_message set bank_card_message.card_cur_balance = @oldprice-new.tr_price+old.tr_price where bank_card_message.card_id=new.card_id;
end if;
end if;
elseif new.tr_type <> old.tr_type
then
if new.tr_type = '存入'
then
update bank_card_message set bank_card_message.card_cur_balance = new.tr_price+@oldprice+old.tr_price where bank_card_message.card_id=new.card_id;
-- 只能發送錯誤的提示信號，所以無法提示恭喜
elseif new.tr_type = '支取'
then
if new.tr_price > @oldprice
then
set p_msg = concat('支取金額大于余額');
signal sqlstate 'ERROR' set message_text = p_msg;
elseif new.tr_price < @oldprice
then
update bank_card_message set bank_card_message.card_cur_balance = @oldprice-new.tr_price-old.tr_price where bank_card_message.card_id=new.card_id;
end if;
end if;
end if;
end;
-- 插入交易資訊觸發器 交易金額大于0測驗
-- insert into atm_tra_info(card_id,tr_type,tr_price) values('1010 3576 4000 0001','存入',0);
-- 插入支取金額大于余額測驗
-- insert into atm_tra_info(card_id,tr_type,tr_price) values('1010 3576 4000 0001','支取',10000);
-- 插入交易資訊成功測驗
-- insert into atm_tra_info(card_id,tr_type,tr_price) values('1010 3576 4000 0001','存入',100);
-- 更新金額為0觸發器測驗
-- update atm_tra_info set tr_price = 0 where card_id = '1010 3576 4000 0001';
-- 更新交易型別一樣，金額修改測驗
-- update atm_tra_info set tr_price = 200 where card_id = '1010 3576 4000 0001';
-- 更新改變交易型別測驗
-- update atm_tra_info set tr_type = '支取' where card_id = '1010 3576 4000 0001';
-- 更新改變交易型別和金額
-- update atm_tra_info set tr_type = '存入',tr_price = 300 where card_id = '1010 3576 4000 0001';
-- 用戶表測驗
-- 身份證輸入錯誤
-- insert into atm_user(u_name,u_card_id,u_phone,u_address) values('張三','123','10086','廣東省深圳市');
-- 用戶正確插入
-- insert into atm_user(u_name,u_card_id,u_phone,u_address) values('張三','350203200003074416','10086','廣東省深圳市');
-- 用戶身份證唯一
-- insert into atm_user(u_name,u_card_id,u_phone,u_address) values('李四','350203200003074416','10010','廣東省深圳市');
-- 插入多個資料
-- insert into atm_user(u_name,u_card_id,u_phone,u_address) values('李四','110105200008025157','10010','北京市朝陽區');
-- insert into atm_user(u_name,u_card_id,u_phone,u_address) values('郭通','120101199707139648','10000','天津市和平區');
-- insert into atm_user(u_name,u_card_id,u_phone,u_address) values('王五','210281200309195489','15650565566','遼寧省大連市');
-- insert into atm_user(u_name,u_card_id,u_phone,u_address) values('劉六','31011220050108037X','13866688855','上海市閔行區');
--
-- 銀行卡資訊表
-- 卡號輸入錯誤
-- insert into bank_card_message(card_id,card_store_type,card_open_amount,card_password,u_id) values('123','活期','1000.00','123456','1');
-- 開戶金額錯誤
-- insert into bank_card_message(card_id,card_store_type,card_open_amount,card_password,u_id) values('1010 3576 4000 0000','活期','0.00','654321','2');
-- 密碼格式錯誤
-- insert into bank_card_message(card_id,card_store_type,card_open_amount,card_password,u_id) values('1010 3576 4000 0001','活期','100.00','1','1');
-- 活期定期，是否選擇輸入
-- insert into bank_card_message(card_id,card_store_type,card_open_amount,card_password,u_id) values('1010 3576 4000 0002','活期吧','1000.00','123123','1');
-- insert into bank_card_message(card_id,card_store_type,card_open_amount,card_password,card_if_loss,u_id) values('1010 3576 4000 0002','活期','1000.00','123123','不知道','1');
-- 正確插入
-- insert into bank_card_message(card_id,card_store_type,card_open_amount,card_password,u_id) values('1010 3576 4000 0001','活期','1000.00','123456','1');
-- insert into bank_card_message(card_id,card_store_type,card_open_amount,card_password,u_id) values('1010 3576 4000 0002','活期','2000.00','654321','2');
-- insert into bank_card_message(card_id,card_store_type,card_open_amount,card_password,u_id) values('1010 3576 4000 0003','活期','3000.00','159357','1');
-- insert into bank_card_message(card_id,card_store_type,card_open_amount,u_id) values('1010 3576 4000 0004','活期','1200.00','4');
-- insert into bank_card_message(card_id,card_store_type,card_open_amount,card_password,u_id) values('1010 3576 4000 0005','活期','5000.00','666666','5');
-- insert into bank_card_message(card_id,card_store_type,card_open_amount,card_password,u_id) values('1010 3576 4000 0006','活期','6000.00','789456','6');
-- 創建儲戶表視圖
create or replace view atm_user_view as select * from atm_user;
-- 測驗
-- select * from atm_user_view;
--
-- 創建銀行卡資訊表視圖
create or replace view bc_view as select * from bank_card_message;
-- 測驗
-- select * from bc_view;
-- 創建交易資訊表視圖
create or replace view tra_info_view as select * from atm_tra_info;
-- 測驗
-- select * from tra_info_view;
-- 創建查詢掛失的客戶資訊視圖
create or replace view rep_loss_view as select atm_user.*,bank_card_message.card_id,bank_card_message.card_cur_balance from bank_card_message,atm_user where bank_card_message.card_if_loss='是' and bank_card_message.u_id=atm_user.u_id;
-- 測驗
-- select * from rep_loss_view;
-- 創建查詢本周開戶的卡號顯示相關資訊視圖
create or replace view bc_tweek_view as select * from bank_card_message where YEARWEEK(date_format(card_open_time,'%Y-%m-%d')) = YEARWEEK(now());
-- 測驗
-- select * from bc_tweek_view;
-- 創建查詢本月交易金額最高的卡號的視圖
create or replace view bc_mmax_atm as
select distinct card_id from atm_tra_info
where tr_price = (
select max(tr_price) from atm_tra_info
where month(now())
);
-- 測驗
-- select * from bc_mmax_atm;
-- 不允許修改卡號觸發器
create trigger atm_bc_update_id
before update on bank_card_message for each row
begin
declare u_msg varchar(100) character set utf8;
set @oldprice = (select bank_card_message.card_cur_balance from bank_card_message where bank_card_message.card_id = new.card_id limit 1);
set @olddate = (select atm_tra_info.tr_date from atm_tra_info where atm_tra_info.card_id = new.card_id limit 1);
set @oldlossup = (select bank_card_message.card_if_loss from bank_card_message where bank_card_message.card_id = new.card_id limit 1);
if @oldlossup = '是'
then
set u_msg = concat('該卡號已經掛失，無法進行修改操作');
signal sqlstate 'ERROR' set message_text = u_msg;
elseif new.card_id <> old.card_id
then
set u_msg = concat('此列不允許修改');
signal sqlstate 'ERROR' set message_text = u_msg;
end if;
end;
-- 不允許修改卡號測驗
-- update bank_card_message set card_id = 500 where u_id = 1;
-- drop trigger atm_bc_update_id;
-- 創建函式，卡號隨機生成
create FUNCTION get_user_card_id() returns varchar(21)
begin
declare card_id varchar(21) default '';
set card_id = concat('1010 3576 ',ROUND(rand()*10000));
set card_id = concat(card_id,' ');
set card_id = concat(card_id,ROUND(rand()*10000));
return card_id;
end;
-- 開戶的存盤程序
-- drop procedure open_bank_atm;
create procedure open_bank_atm(in
user_name varchar(20) character set utf8, -- 開戶姓名
user_card_id varchar(18), -- 用戶身份證號碼
user_phone varchar(11), -- 用戶電話
user_address varchar(50) character set utf8, -- 用戶地址
card_sto_type varchar(10) character set utf8, -- 存盤型別
card_open_price decimal(10,2), -- 金額
card_psw varchar(20) -- 密碼
)
begin
declare the_user_id_check varchar(18);
declare num int;
-- 根據身份證判斷此用戶是否存在
select count(u_card_id) into num from atm_user where atm_user.u_card_id = user_card_id;
if num = 0
then
-- 沒有這個用戶名，在用戶資訊表加入這個用戶資訊
insert into atm_user(u_name,u_card_id,u_phone,u_address) values(user_name,user_card_id,user_phone,user_address);
else
-- 有此用戶，此用戶開多個表
select u_id into the_user_id_check from atm_user where atm_user.u_card_id = user_card_id limit 1;
end if;
-- 插入到銀行資訊表
insert into bank_card_message(card_id,card_store_type,card_open_amount,card_password,u_id) values(get_user_card_id(),card_sto_type,card_open_price,card_psw,the_user_id_check);
commit;
end;
-- 測驗開戶存盤程序
-- call open_bank_atm('張三','350203200003074416','18080456123','福建省福州市','活期',1000,'663366');
-- call open_bank_atm('張三','350203200003074416','10086','福建省廈門市','定期',2000,'868686');
-- call open_bank_atm('李四','350982200601060130','1000011','上海市閔行區','隨便',3000,'111555');
-- 創建修改密碼的存盤程序
create procedure update_card_psw(in
the_up_u_card_id varchar(21), -- 要修改的銀行卡
new_card_psw varchar(20) -- 新密碼
)
begin
declare num int;
declare p_ucard_message varchar(100) character set utf8;
-- 根據銀行卡號判斷此卡是否存在
select count(card_id) into num from bank_card_message where bank_card_message.card_id = the_up_u_card_id;
if num = 0
then
-- 沒有這個銀行卡，警報
set p_ucard_message = concat('此卡號不存在，請重新嘗試！');
signal sqlstate 'ERROR' set message_text = p_ucard_message;
else
-- 有此卡號，進行修改操作
update bank_card_message set card_password = new_card_psw where bank_card_message.card_id = the_up_u_card_id;
end if;
commit;
end;
-- 修改密碼測驗
-- 卡號存在
-- call update_card_psw('1010 3576 9767 2679','1008611');
-- 卡號不存在
-- call update_card_psw('1010 3576 9767 1111','123456');
-- 創建掛失賬號的存盤程序
create procedure go_loss_id(in
the_loss_card_id varchar(21) -- 要掛失的銀行卡號
)
begin
declare num int;
declare p_goloss_message varchar(100) character set utf8;
-- 根據卡號判斷此卡是否存在
select count(card_id) into num from bank_card_message where bank_card_message.card_id = the_loss_card_id;
if num = 0
then
-- 沒有這個銀行卡，警報
set p_goloss_message = concat('此卡號不存在，請重新嘗試');
signal sqlstate 'ERROR' set message_text = p_goloss_message;
else
-- 卡號存在，進行掛失操作
update bank_card_message set card_if_loss = '是' where bank_card_message.card_id = the_loss_card_id;
end if;
commit;
end;
-- 卡號掛失測驗
-- 卡號存在
-- call go_loss_id('1010 3576 9767 2679');
-- 卡號不存在
-- call go_loss_id('1010 3576 9767 1234');
-- 支取和存入的存盤程序
create procedure in_out_tri(in
the_tr_card_id varchar(21), -- 卡號
the_tr_type varchar(12) character set utf8, -- 交易型別
the_tr_price decimal(10,2) -- 交易金額
)
begin
declare num int;
declare p_gotr_message varchar(100) character set utf8;
-- 根據卡號判斷此卡是否存在
select count(card_id) into num from bank_card_message where bank_card_message.card_id = the_tr_card_id;
if num = 0
then
-- 沒有這個銀行卡，警報
set p_gotr_message = concat('此卡號不存在，請重新嘗試');
signal sqlstate 'ERROR' set message_text = p_gotr_message;
else
-- 卡號存在，進行交易操作
insert into atm_tra_info(card_id,tr_type,tr_price) values(the_tr_card_id,the_tr_type,the_tr_price);
end if;
commit;
end;
-- 交易支取和存入測驗
-- 支取金額大于余額
-- call in_out_tri('1010 3576 9767 2679','支取',30000);
-- 存入金額為0
-- call in_out_tri('1010 3576 4000 0001','存入',0);
-- 正確存入
-- call in_out_tri('1010 3576 4000 0001','存入',100);
-- 正確支取
-- call in_out_tri('1010 3576 4000 0003','支取',200);
-- 創建查詢余額存盤程序
create procedure check_balance(in
the_check_ba_card_id varchar(21), -- 查詢余額的卡號
the_check_ba_card_psw varchar(20) -- 查詢余額的密碼
)
begin
declare num int;
declare psw varchar(20);
declare p_check_message varchar(100) character set utf8;
-- 根據卡號判斷此卡是否存在
select count(card_id) into num from bank_card_message where bank_card_message.card_id = the_check_ba_card_id;
if num = 0
then
-- 沒有這個銀行卡，警報
set p_check_message = concat('此卡號不存在，請重新嘗試');
signal sqlstate 'ERROR' set message_text = p_check_message;
else
-- 卡號存在，進行查詢操作
select card_password into psw from bank_card_message where bank_card_message.card_id = the_check_ba_card_id;
if the_check_ba_card_psw = psw
then
select card_cur_balance from bank_card_message where bank_card_message.card_id = the_check_ba_card_id;
else
-- 密碼錯誤，警報
set p_check_message = concat('輸入的銀行卡密碼錯誤，請重新嘗試！');
signal sqlstate 'ERROR' set message_text = p_check_message;
end if;
end if;
commit;
end;
-- 查詢余額測驗
-- call check_balance('1010 3576 4000 0001','654321');
-- call check_balance('1010 3576 4000 0001','123456');
-- 創建轉賬存盤程序
create procedure do_transf(in
the_out_card_id varchar(21), -- 轉出賬號
the_in_card_id varchar(21), -- 轉入賬號
the_tran_price decimal(10,2) -- 轉賬金額
)
begin
declare out_num int; -- 轉出賬號是否存在
declare in_num int; -- 轉入賬號是否存在
declare out_price decimal(10,1); -- 轉出賬號余額
declare p_tran_message varchar(100) character set utf8;
set @outoldprice = (select bank_card_message.card_cur_balance from bank_card_message where bank_card_message.card_id = the_out_card_id limit 1);
set @inoldprice = (select bank_card_message.card_cur_balance from bank_card_message where bank_card_message.card_id = the_in_card_id limit 1);
-- 根據卡號判斷此卡是否存在
select count(card_id) into out_num from bank_card_message where bank_card_message.card_id = the_out_card_id;
select count(card_id) into in_num from bank_card_message where bank_card_message.card_id = the_in_card_id;
if out_num = 0 || in_num = 0
then
-- 沒有這個銀行卡，警報
set p_tran_message = concat('卡號不存在，請重新嘗試');
signal sqlstate 'ERROR' set message_text = p_tran_message;
else
-- 卡號存在，進行轉賬操作
select card_cur_balance into out_price from bank_card_message where bank_card_message.card_id = the_out_card_id;
if the_tran_price > out_price
then
set p_tran_message = concat('交易金額大于余額，交易失敗');
signal sqlstate 'ERROR' set message_text = p_tran_message;
else
-- 轉賬操作
update bank_card_message set card_cur_balance = @outoldprice-the_tran_price where bank_card_message.card_id = the_out_card_id;
update bank_card_message set card_cur_balance = @inoldprice+the_tran_price where bank_card_message.card_id = the_in_card_id;
end if;
end if;
commit;
end;
-- 轉賬測驗
-- call do_transf('1010 3576 4000 0001','1010 3576 4000 0000',100.00);
-- call do_transf('1010 3576 4000 0004','1010 3576 4000 0002',10000);
-- call do_transf('1010 3576 4000 0001','1010 3576 4000 0002',100.00);
-- 創建銷戶存盤程序
create procedure del_user(in
the_del_user_id varchar(18) -- 要洗掉的用戶身份證
)
begin
declare tmp int default 0;
declare sum int default 0;
declare done int default -1;
declare u_num int; -- 身份證是否存在
declare id_num int; -- u_id
declare check_card_id varchar(21); -- 游標投入的銀行卡號
declare d_check_message varchar(100) character set utf8;
declare cursor_card_id cursor for select bank_card_message.card_id from bank_card_message; -- 游標遍歷該用戶的所有銀行卡
declare continue handler for not found set done = 1;
select atm_user.u_id into id_num from atm_user where atm_user.u_card_id = the_del_user_id limit 1; -- 得到用戶自增u_id
-- 根據身份證判斷此卡是否存在
select count(u_id) into u_num from atm_user where atm_user.u_card_id = the_del_user_id;
if u_num = 0
then
-- 沒有這個用戶，警報
set d_check_message = concat('此用戶不存在，請重新嘗試');
signal sqlstate 'ERROR' set message_text = d_check_message;
else
-- 身份證存在，進行銷戶操作
open cursor_card_id; -- 打開游標
posloop:LOOP
if done=1 then
leave posloop;
end if;
fetch cursor_card_id into check_card_id;
delete from atm_tra_info where atm_tra_info.card_id = check_card_id; -- 洗掉交易資訊表
delete from bank_card_message where bank_card_message.u_id = id_num; -- 洗掉銀行資訊表
end loop posloop;
close cursor_card_id;
delete from atm_user where atm_user.u_card_id = the_del_user_id; -- 洗掉用戶資訊表
end if;
end;
-- 銷戶測驗
-- call del_user('350203200003074416');
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/252198.html
標籤：其他
上一篇：Java中的集合類包括ArrayList、LinkedList、HashMap等，下列關于集合類描述錯誤的是（C）