You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
66 lines
2.2 KiB
66 lines
2.2 KiB
|
|
-- 添加新学员的存储过程
|
|
CREATE OR REPLACE FUNCTION AddNewStudent(
|
|
_student_id VARCHAR(255),
|
|
_name VARCHAR(255),
|
|
_password VARCHAR(255),
|
|
_phone VARCHAR(20),
|
|
_email VARCHAR(255)
|
|
) RETURNS VOID AS $$
|
|
DECLARE
|
|
_info_id INT;
|
|
BEGIN
|
|
INSERT INTO personal_info (phone, email) VALUES (_phone, _email) RETURNING info_id INTO _info_id;
|
|
INSERT INTO student (student_id, name, password, personal_info_id) VALUES (_student_id, _name, _password, _info_id);
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
-- 更新教练电话的存储过程
|
|
CREATE OR REPLACE FUNCTION UpdateCoachPhone(
|
|
_coach_id VARCHAR(255),
|
|
_new_phone VARCHAR(20)
|
|
) RETURNS VOID AS $$
|
|
BEGIN
|
|
UPDATE personal_info SET phone = _new_phone WHERE info_id = (SELECT personal_info_id FROM coach WHERE coach_id = _coach_id);
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
-- 获取某个学生的所有预约的存储过程
|
|
CREATE OR REPLACE FUNCTION GetStudentAppointments(
|
|
_student_id VARCHAR(255)
|
|
) RETURNS TABLE(appointment_id INT, coach_name VARCHAR(255), venue_name VARCHAR(255), period VARCHAR(50), status VARCHAR(50)) AS $$
|
|
BEGIN
|
|
RETURN QUERY
|
|
SELECT a.appointment_id, c.name AS coach_name, v.name AS venue_name, a.period, a.status
|
|
FROM appointment a
|
|
JOIN coach c ON a.coach_id = c.coach_id
|
|
JOIN venue v ON a.venue_id = v.venue_id
|
|
WHERE a.student_id = _student_id;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
-- 获取某个教练的所有反馈的存储过程
|
|
CREATE OR REPLACE FUNCTION GetCoachFeedbacks(
|
|
_coach_id VARCHAR(255)
|
|
) RETURNS TABLE(feedback_id INT, content TEXT, date DATE) AS $$
|
|
BEGIN
|
|
RETURN QUERY
|
|
SELECT f.feedback_id, f.content, f.date
|
|
FROM feedback f
|
|
JOIN training_record t ON f.feedback_id = t.feedback_id
|
|
WHERE t.coach_id = _coach_id;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
-- 获取某个场地的所有可用时间段的存储过程
|
|
CREATE OR REPLACE FUNCTION GetAvailableSlotsForVenue(
|
|
_venue_id INT
|
|
) RETURNS TABLE(slot_id INT, start_time TIME, end_time TIME) AS $$
|
|
BEGIN
|
|
RETURN QUERY
|
|
SELECT bs.slot_id, bs.start_time, bs.end_time
|
|
FROM booking_slot bs
|
|
LEFT JOIN appointment a ON bs.venue_id = a.venue_id AND bs.start_time <= a.period::time AND bs.end_time >= a.period::time
|
|
WHERE bs.venue_id = _venue_id AND a.appointment_id IS NULL;
|
|
END;
|
|
$$ LANGUAGE plpgsql; |