CREATE DATABASE IF NOT EXISTS student_score_db;
USEstudent_score_db;
CREATE TABLE user (
userid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL,
deptname VARCHAR(50),
phone VARCHAR(20) NOT NULL
);
CREATE TABLE leaveinfo (
leaveid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
userid INT NOT NULL,
leavetype VARCHAR(50),
starttime DATETIME NOT NULL,
endtime DATETIME NOT NULL,
reason VARCHAR(50),
submittime DATETIME,
status VARCHAR(20) DEFAULT'待审批',
CHECK (status IN ('待审批', '已批准', '已驳回', '已销假')),
FOREIGN KEY (userid) REFERENCES user(userid)
);
CREATE TABLE checkinfo (
checkid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
leaveid INT NOT NULL,
userid INT NOT NULL,
checkresult VARCHAR(50),
suggest VARCHAR(50),
checktime DATETIME,
FOREIGN KEY (leaveid) REFERENCES leaveinfo(leaveid),
FOREIGN KEY (userid) REFERENCES user(userid)
);
CREATE TABLE back (
backid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
leaveid INT NOT NULL,
backtime DATETIME,
FOREIGN KEY (leaveid) REFERENCES leaveinfo(leaveid)
);
ALTER TABLE user MODIFY COLUMN phone VARCHAR(20);
-- ----------------------------
-- 函数:计算请假时长(分钟)
-- ----------------------------
DELIMITER $$
CREATE FUNCTIONGetLeaveDuration(start_time DATETIME, end_time DATETIME)
RETURNSINTDETERMINISTICBEGINRETURNTIMESTAMPDIFF(MINUTE, start_time, end_time);
END $$
DELIMITER ;
-- ----------------------------
-- 存储过程:通过姓名插入请假记录
-- ----------------------------
DELIMITER $$
CREATE PROCEDURE AddLeaveWithDetails(
IN p_username VARCHAR(50),
IN p_phone VARCHAR(20),
IN p_leavetype VARCHAR(50),
IN p_starttime DATETIME,
IN p_endtime DATETIME,
IN p_reason VARCHAR(200)
)
BEGIN
DECLARE v_userid INT;
SELECT userid INTO v_userid FROM user WHERE username = p_username;
IF v_userid IS NULL THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '用户不存在';
END IF;
IF p_endtime <= p_starttime THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '结束时间必须晚于开始时间';
END IF;
INSERT INTO leaveinfo (userid, leavetype, starttime, endtime, reason)
VALUES (v_userid, p_leavetype, p_starttime, p_endtime, p_reason);
SELECT LAST_INSERT_ID() AS leaveid;
END $$
DELIMITER ;
-- ----------------------------
-- 存储过程:通过姓名销假
-- ----------------------------
DELIMITER $$
CREATE PROCEDURE CancelLeaveByUsername(
IN p_username VARCHAR(50),
IN p_leaveid INT,
IN p_backtime DATETIME
)
BEGIN
DECLARE v_userid INT;
DECLARE v_endtime DATETIME;
DECLARE v_status VARCHAR(20);
SELECT userid INTO v_userid FROM user WHERE username = p_username;
IF v_userid IS NULL THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '用户不存在';
END IF;
SELECT endtime, status INTO v_endtime, v_status
FROM leaveinfo
WHERE leaveid = p_leaveid AND userid = v_userid;
IF v_status IS NULL THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '请假记录不存在';
END IF;
IF v_status != '已批准' THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '只有已批准的申请才能销假';
END IF;
IF p_backtime <= v_endtime THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '销假时间必须晚于结束时间';
END IF;
INSERT INTO back (leaveid, backtime) VALUES (p_leaveid, p_backtime);
UPDATE leaveinfo SET status = '已销假' WHERE leaveid = p_leaveid;
END $$
DELIMITER ;
-- ----------------------------
-- 存储过程:通过姓名删除请假记录
-- ----------------------------
DELIMITER $$
CREATE PROCEDURE DeleteLeaveByUsername(
IN p_username VARCHAR(50),
IN p_leaveid INT
)
BEGIN
DECLARE v_userid INT;
DECLARE v_status VARCHAR(20);
SELECT userid INTO v_userid FROM user WHERE username = p_username;
IF v_userid IS NULL THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '用户不存在';
END IF;
SELECT status INTO v_status
FROM leaveinfo
WHERE leaveid = p_leaveid AND userid = v_userid;
IF v_status IS NULL THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '请假记录不存在';
END IF;
IF v_status != '待审批' THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '只有待审批的申请可以删除';
END IF;
DELETE FROM leaveinfo WHERE leaveid = p_leaveid;
END $$
DELIMITER ;
-- ----------------------------
-- 存储过程:查询用户请假统计(不显示手机号)
-- ----------------------------
DELIMITER $$
CREATE PROCEDURE GetUserLeaveStats(IN p_username VARCHAR(50))
BEGIN
DECLARE v_userid INT;
DECLARE v_leave_count INT;
DECLARE v_total_duration INT;
DECLARE v_unbacked_count INT;
DECLARE v_has_unbacked VARCHAR(10);
SET v_leave_count = 0;
SET v_total_duration = 0;
SET v_unbacked_count = 0;
SET v_has_unbacked = '否';
SELECT userid INTO v_userid FROM user WHERE username = p_username;
IF v_userid IS NULL THEN
SELECT CONCAT('用户 "', p_username, '" 不存在') AS 提示;
ELSE
SELECT COUNT(1) INTO v_leave_count
FROM leaveinfo
WHERE userid = v_userid AND status = '已批准';
SELECT SUM(GetLeaveDuration(starttime, endtime)) INTO v_total_duration
FROM leaveinfo
WHERE userid = v_userid AND status = '已批准';
SELECT COUNT(1) INTO v_unbacked_count
FROM leaveinfo li
LEFT JOIN back b ON li.leaveid = b.leaveid
WHERE li.userid = v_userid AND li.status = '已批准'AND b.backtime IS NULL;
IF v_unbacked_count > 0 THEN
SET v_has_unbacked = '是';
END IF;
SELECT
p_username AS 姓名,
v_leave_count AS 外出次数,
CONCAT(FLOOR(v_total_duration / 60), '小时', v_total_duration % 60, '分钟') AS 请假时长,
v_has_unbacked AS 是否有未销假记录;
END IF;
END $$
DELIMITER ;
-- ----------------------------
-- 存储过程:查询用户详细请假记录(不显示手机号)
-- ----------------------------
DELIMITER $$
CREATE PROCEDURE GetUserLeaveDetails(IN p_username VARCHAR(50))
BEGIN
DECLARE v_userid INT;
SELECT userid INTO v_userid FROM user WHERE username = p_username;
IF v_userid IS NULL THEN
SELECT CONCAT('用户 "', p_username, '" 不存在') AS 提示;
ELSE
SELECT
li.leaveid AS 请假ID,
li.leavetype AS 请假类型,
DATE_FORMAT(li.starttime, '%Y-%m-%d %H:%i') AS 开始时间,
DATE_FORMAT(li.endtime, '%Y-%m-%d %H:%i') AS 结束时间,
CONCAT(FLOOR(GetLeaveDuration(li.starttime, li.endtime) / 60), '小时',
GetLeaveDuration(li.starttime, li.endtime) % 60, '分钟') AS 请假时长,
li.reason AS 请假原因,
li.status AS 审批状态,
IF(b.backtime IS NOT NULL, '已销假', '未销假') AS 销假状态,
DATE_FORMAT(b.backtime, '%Y-%m-%d %H:%i') AS 销假时间
FROM leaveinfo li
LEFT JOIN back b ON li.leaveid = b.leaveid
WHERE li.userid = v_userid
ORDER BY li.starttime DESC;
END IF;
END $$
DELIMITER ;
-- ----------------------------
-- 测试数据
-- ----------------------------
DELETE FROM back;
DELETE FROM checkinfo;
DELETE FROM leaveinfo;
DELETE FROM user;
INSERT INTO user (username, deptname, phone)
VALUES ('张三', '一班', '18812341234'), ('李四', '二班', '18856785678'), ('王五', '三班', '15035932703');
INSERT INTO leaveinfo (userid, leavetype, starttime, endtime, reason, submittime, status)
VALUES
(1, '事假', '2025-06-28 14:00', '2025-06-28 18:00', '个人原因', '2025-06-28 08:20', '已批准'),
(1, '病假', '2025-07-01 09:00', '2025-07-01 17:00', '身体不适', '2025-06-30 10:00', '已批准'),
(2, '事假', '2025-07-02 10:00', '2025-07-02 15:00', '家庭事务', '2025-07-01 08:00', '已批准'),
(3, '事假', '2025-07-02 10:00', '2025-07-02 15:00', '出差', '2025-07-01 08:00', '已批准'); -- 修正:添加缺失的分号
INSERT INTO back (leaveid, backtime)
VALUES (1, '2025-06-28 18:30'), (2, '2025-07-01 17:30'), (3, '2025-07-01 17:30'); -- 修正:删除多余的记录
-- ----------------------------
-- 功能测试示例
-- ----------------------------
CALL GetUserLeaveStats('张三');
CALL GetUserLeaveDetails('张三');
CALL GetUserLeaveStats('李四');
CALL GetUserLeaveDetails('李四');
CALL GetUserLeaveStats('王五');
CALL GetUserLeaveDetails('王五');