CREATE DATABASE IF NOT EXISTS BankSavingSystem;
USE BankSavingSystem;
CREATE TABLE des (
D_no VARCHAR(20) PRIMARY KEY,
D_adcard VARCHAR(20) NOT NULL,
D_name VARCHAR(20) NOT NULL,
D_address VARCHAR(20) NOT NULL,
D_banlance DECIMAL(10, 2) NOT NULL,
D_password VARCHAR(50) NOT NULL
);
CREATE TABLE cun(
C_no VARCHAR(20) PRIMARY KEY,
C_money DECIMAL(10, 2) NOT NULL,
C_way VARCHAR(20) NOT NULL
);
CREATE TABLE qu (
Q_no VARCHAR(20) PRIMARY KEY,
Q_money DECIMAL(10, 2) NOT NULL,
Q_way VARCHAR(20) NOT NULL
);
CREATE TABLE deposit (
V_day DATETIME PRIMARY KEY,
D_no VARCHAR(20) NOT NULL,
C_no VARCHAR(20) NOT NULL,
FOREIGN KEY (D_no) REFERENCES des(D_no),
FOREIGN KEY (C_no) REFERENCES cun(C_no)
);
CREATE TABLE draw (
R_day DATETIME PRIMARY KEY,
D_no VARCHAR(20) NOT NULL,
Q_no VARCHAR(20) NOT NULL,
FOREIGN KEY (D_no) REFERENCES des(D_no),
FOREIGN KEY (Q_no) REFERENCES qu(Q_no)
);
DELIMITER $$
CREATE PROCEDURE sp_deposit(
IN p_account VARCHAR(20),
IN p_depositNo VARCHAR(20),
IN p_depositDate DATETIME,
IN p_amount DECIMAL(10, 2),
IN p_way VARCHAR(20)
)
BEGIN
DECLARE v_count INT;
SELECT COUNT(*) INTO v_count FROM des WHERE D_no = p_account;
IF v_count = 0 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '储户不存在!';
END IF;
SELECT COUNT(*) INTO v_count FROM cun WHERE C_no = p_depositNo;
IF v_count = 0 THEN
INSERT INTO cun (C_no, C_money, C_way)
VALUES (p_depositNo, p_amount, p_way);
END IF;
INSERT INTO deposit (V_day, D_no, C_no)
VALUES (p_depositDate, p_account, p_depositNo);
UPDATE des
SET D_banlance = D_banlance + p_amount
WHERE D_no = p_account;
SELECT D_banlance FROM des WHERE D_no = p_account;
END$$
DELIMITER ;
DELIMITER $$
CREATE PROCEDURE sp_withdraw(
IN p_account VARCHAR(20),
IN p_withdrawNo VARCHAR(20),
IN p_withdrawDate DATETIME,
IN p_amount DECIMAL(10, 2),
IN p_way VARCHAR(20)
)
BEGIN
DECLARE v_balance DECIMAL(10, 2);
DECLARE v_count INT;
SELECT COUNT(*) INTO v_count FROM des WHERE D_no = p_account;
IF v_count = 0 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '储户不存在!';
END IF;
SELECT D_banlance INTO v_balance FROM des WHERE D_no = p_account;
IF v_balance < p_amount THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '余额不足!';
END IF;
SELECT COUNT(*) INTO v_count FROM qu WHERE Q_no = p_withdrawNo;
IF v_count = 0 THEN
INSERT INTO qu (Q_no, Q_money, Q_way)
VALUES (p_withdrawNo, p_amount, p_way);
END IF;
INSERT INTO draw (R_day, D_no, Q_no)
VALUES (p_withdrawDate, p_account, p_withdrawNo);
UPDATE des
SET D_banlance = D_banlance - p_amount
WHERE D_no = p_account;
SELECT D_banlance FROM des WHERE D_no = p_account;
END$$
DELIMITER ;
DELIMITER $$
CREATE PROCEDURE sp_transfer(
IN p_fromAccount VARCHAR(20),
IN p_toAccount VARCHAR(20),
IN p_amount DECIMAL(10, 2),
IN p_way VARCHAR(20)
)
BEGIN
DECLARE v_fromBalance DECIMAL(10, 2);
DECLARE v_count INT;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '转账失败,发生异常!';
END;
START TRANSACTION;
SELECT COUNT(*) INTO v_count FROM des WHERE D_no = p_fromAccount;
IF v_count = 0 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '转出账号不存在!';
END IF;
SELECT COUNT(*) INTO v_count FROM des WHERE D_no = p_toAccount;
IF v_count = 0 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '转入账号不存在!';
END IF;
SELECT D_banlance INTO v_fromBalance FROM des WHERE D_no = p_fromAccount;
IF v_fromBalance < p_amount THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '余额不足!';
END IF;
UPDATE des
SET D_banlance = D_banlance - p_amount
WHERE D_no = p_fromAccount;
UPDATE des
SET D_banlance = D_banlance + p_amount
WHERE D_no = p_toAccount;
COMMIT;
SELECT D_banlance FROM des WHERE D_no = p_fromAccount;
END$$
DELIMITER ;
DELIMITER $$
CREATE PROCEDURE sp_login(
IN p_account VARCHAR(20),
IN p_password VARCHAR(50)
)
BEGIN
SELECT * FROM des
WHERE D_no = p_account AND D_password = p_password;
END$$
DELIMITER ;
CREATE VIEW vw_customer_info AS
SELECT
D_no AS AccountNumber,
D_name AS AccountName,
D_address AS Address,
CAST(D_banlance AS DECIMAL) AS Balance
FROM des;
CREATE VIEW vw_deposit_record AS
SELECT
d.V_day AS DepositDate,
d.D_no AS AccountNumber,
c.C_no AS DepositNumber,
c.C_money AS DepositAmount,
c.C_way AS DepositMethod
FROM deposit d
JOIN cun c ON d.C_no = c.C_no;
CREATE VIEW vw_withdraw_record AS
SELECT
d.R_day AS WithdrawDate,
d.D_no AS AccountNumber,
q.Q_no AS WithdrawNumber,
q.Q_money AS WithdrawAmount,
q.Q_way AS WithdrawMethod
FROM draw d
JOIN qu q ON d.Q_no = q.Q_no;
INSERT INTO des (D_no, D_adcard, D_name, D_address, D_banlance, D_password) VALUES
('101', '110101199001011234', '吉安娜', '塞拉摩', 1000.00, '123456'),
('102', '110101199102022345', '加尔鲁什', '杜隆塔尔', 2000.00, '123456'),
('103', '110101199203033456', '洛瑟玛塞隆', '银月城', 3000.00, '123456'),
('104', '110101199304044567', '克尔苏加德', '卡里莫多', 4000.00, '123456');
SELECT*FROM des;
INSERT INTO cun (C_no, C_money, C_way) VALUES
('C001', 1000.00, 'ATM'),
('C002', 2000.00, '柜台'),
('C003', 1500.00, '手机银行');
INSERT INTO qu (Q_no, Q_money, Q_way) VALUES
('Q001', 500.00, '柜台'),
('Q002', 300.00, 'ATM'),
('Q003', 1000.00, '手机银行');
INSERT INTO deposit (V_day, D_no, C_no) VALUES
('2025-06-25', '101', 'C001'),
('2025-06-26', '102', 'C002'),
('2025-06-27', '103', 'C003');
INSERT INTO draw (R_day, D_no, Q_no) VALUES
('2025-06-28', '101', 'Q001'),
('2025-06-29', '102', 'Q002'),
('2025-06-30', '103', 'Q003');