编辑代码



-- ----------------------------
-- 数据库创建
-- ----------------------------
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,   -- 存款余额(非空,使用DECIMAL类型)
    D_password VARCHAR(50) NOT NULL       -- 密码
);

-- ----------------------------
-- 存款单表
-- ----------------------------
CREATE TABLE cun(
    C_no VARCHAR(20) PRIMARY KEY,         -- 存款单号(主键)
    C_money DECIMAL(10, 2) NOT NULL,      -- 存款金额(非空,使用DECIMAL类型)
    C_way VARCHAR(20) NOT NULL            -- 存款方式(非空)
);

-- ----------------------------
-- 取款单表
-- ----------------------------
CREATE TABLE qu (
    Q_no VARCHAR(20) PRIMARY KEY,         -- 取款单号(主键)
    Q_money DECIMAL(10, 2) NOT NULL,      -- 取款金额(非空,使用DECIMAL类型)
    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;
    
    -- 1. 检查储户是否存在
    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;
    
    -- 2. 插入存款单(如果不存在)
    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;
    
    -- 3. 插入存款记录
    INSERT INTO deposit (V_day, D_no, C_no)
    VALUES (p_depositDate, p_account, p_depositNo);
    
    -- 4. 更新余额
    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;
    
    -- 1. 检查储户是否存在
    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;
    
    -- 2. 检查余额是否充足
    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;
    
    -- 3. 插入取款单(如果不存在)
    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;
    
    -- 4. 插入取款记录
    INSERT INTO draw (R_day, D_no, Q_no)
    VALUES (p_withdrawDate, p_account, p_withdrawNo);
    
    -- 5. 更新余额
    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;
    
    -- 1. 检查转出账号是否存在
    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;
    
    -- 2. 检查转入账号是否存在
    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;
    
    -- 3. 检查转出账号余额
    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;
    
    -- 4. 执行转账
    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');