数据库系统 第9节 存储过程

存储过程（Stored Procedure）是一种预编译的SQL脚本，它可以在数据库服务器上预先定义并保存下来。当需要执行特定任务时，可以调用存储过程而不是直接执行SQL语句。存储过程可以接受输入参数，并根据这些参数执行一系列操作，如查询、更新或删除数据，甚至可以返回输出参数。

存储过程的主要优点包括：

1. 性能提升：存储过程被编译一次后可以多次执行，减少了网络流量和解析时间。
2. 代码重用：存储过程可以被多次调用，避免了重复编写相同的代码。
3. 安全性：可以通过存储过程限制用户对底层数据表的直接访问，从而提高安全性。
4. 事务处理：存储过程可以包含事务处理逻辑，确保数据的一致性和完整性。
5. 复杂逻辑：可以实现复杂的业务逻辑，如循环、条件分支等。

存储过程的基本结构

一个典型的存储过程通常包含以下部分：

• 定义：创建存储过程的SQL语句。
• 参数：输入参数和输出参数。
• 主体：执行的具体SQL语句或逻辑。
• 调用：调用存储过程的命令。

创建存储过程

以MySQL为例，我们可以使用CREATE PROCEDURE语句来创建一个存储过程。下面是一个简单的例子，创建一个存储过程来查找特定年龄的学生：

DELIMITER //
CREATE PROCEDURE FindStudentsByAge(IN age INT)
BEGIN
    SELECT * FROM Students WHERE Age = age;
END //
DELIMITER ;

这里，DELIMITER命令用于改变默认的分隔符（通常是;），因为存储过程中可能包含分号。

使用存储过程

一旦创建了存储过程，就可以通过调用它来执行相应的逻辑：

CALL FindStudentsByAge(20);

输出参数

存储过程还可以包含输出参数，这些参数允许存储过程返回值给调用者。例如，我们可以创建一个存储过程来计算学生的平均年龄，并将结果返回给调用者。

DELIMITER //
CREATE PROCEDURE CalculateAverageAge(OUT avg_age DECIMAL(5,2))
BEGIN
    SELECT AVG(Age) INTO avg_age FROM Students;
END //
DELIMITER ;

然后调用此存储过程，并获取输出参数：

CALL CalculateAverageAge(@average_age);
SELECT @average_age;

示例1：学生选课存储过程

假设我们要创建一个存储过程来处理学生选课的过程，包括检查是否有足够的名额，以及更新学生和课程表。

创建存储过程

DELIMITER //
CREATE PROCEDURE EnrollStudent(IN student_id INT, IN course_id INT, OUT success BOOLEAN)
BEGIN
    DECLARE available_seats INT;
    
    -- 检查课程是否有足够的名额
    SELECT COUNT(*) INTO available_seats
    FROM Enrollments
    WHERE CourseID = course_id;
    
    IF (available_seats < (SELECT Credits FROM Courses WHERE CourseID = course_id)) THEN
        -- 如果有足够的名额，则插入新记录
        INSERT INTO Enrollments (StudentID, CourseID) VALUES (student_id, course_id);
        SET success = TRUE;
    ELSE
        SET success = FALSE;
    END IF;
END //
DELIMITER ;

调用存储过程

SET @success = FALSE;
CALL EnrollStudent(101, 101, @success);
SELECT @success;

在这个例子中，我们定义了一个名为 EnrollStudent 的存储过程，它接受学生ID和课程ID作为输入参数，并返回一个布尔值 success 表示是否成功选课。如果课程有足够的名额，则会在 Enrollments 表中插入一条新记录，并设置 success 为 TRUE；否则，设置 success 为 FALSE。

总结

存储过程是一种强大的工具，可以用来封装复杂的数据库逻辑，提高应用程序的性能和安全性。它们特别适用于需要频繁执行相同任务或执行复杂逻辑的情况。

接下来，我将为您提供更多关于存储过程的实际应用场景，以便您更深入地理解如何使用存储过程来处理各种数据库任务。

示例2: 更新学生信息存储过程

假设我们需要一个存储过程来更新学生的个人信息，比如姓名和年龄。这个存储过程应该验证输入，并确保更新操作成功。

创建存储过程

DELIMITER //
CREATE PROCEDURE UpdateStudentInfo(IN student_id INT, IN new_name VARCHAR(50), IN new_age INT, OUT success BOOLEAN)
BEGIN
    DECLARE old_name VARCHAR(50);
    DECLARE old_age INT;

    -- 验证学生是否存在
    SELECT Name, Age INTO old_name, old_age FROM Students WHERE StudentID = student_id;

    IF old_name IS NOT NULL THEN
        -- 更新学生信息
        UPDATE Students SET Name = new_name, Age = new_age WHERE StudentID = student_id;
        
        -- 设置成功标志
        SET success = TRUE;
    ELSE
        -- 设置失败标志
        SET success = FALSE;
    END IF;
END //
DELIMITER ;

使用存储过程

SET @success = FALSE;
CALL UpdateStudentInfo(101, 'John Doe', 22, @success);
SELECT @success;

示例3: 删除学生及其相关信息存储过程

有时我们需要一个存储过程来删除一个学生及其所有相关信息，包括他们在 Enrollments 表中的记录。

创建存储过程

DELIMITER //
CREATE PROCEDURE DeleteStudent(IN student_id INT, OUT success BOOLEAN)
BEGIN
    DECLARE student_name VARCHAR(50);

    -- 验证学生是否存在
    SELECT Name INTO student_name FROM Students WHERE StudentID = student_id;

    IF student_name IS NOT NULL THEN
        -- 删除学生在Enrollments表中的记录
        DELETE FROM Enrollments WHERE StudentID = student_id;
        
        -- 删除学生信息
        DELETE FROM Students WHERE StudentID = student_id;
        
        -- 设置成功标志
        SET success = TRUE;
    ELSE
        -- 设置失败标志
        SET success = FALSE;
    END IF;
END //
DELIMITER ;

使用存储过程

SET @success = FALSE;
CALL DeleteStudent(101, @success);
SELECT @success;

示例4: 计算课程平均成绩存储过程

假设我们需要一个存储过程来计算每门课程的平均成绩，并将结果存储在单独的表中。

创建结果表

首先，我们需要创建一个表来存储结果。

CREATE TABLE CourseAverages (
    CourseID INT PRIMARY KEY,
    AverageGrade DECIMAL(4,2)
);

创建存储过程

DELIMITER //
CREATE PROCEDURE CalculateCourseAverages()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE course_id INT;
    DECLARE avg_grade DECIMAL(4,2);

    DECLARE cur CURSOR FOR SELECT CourseID FROM Courses;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;

    read_loop: LOOP
        FETCH cur INTO course_id;
        IF done THEN
            LEAVE read_loop;
        END IF;

        SELECT AVG(Grade) INTO avg_grade FROM Enrollments WHERE CourseID = course_id;

        INSERT INTO CourseAverages (CourseID, AverageGrade) VALUES (course_id, avg_grade);
    END LOOP;

    CLOSE cur;
END //
DELIMITER ;

使用存储过程

CALL CalculateCourseAverages();
SELECT * FROM CourseAverages;

示例5: 批量更新课程学分存储过程

假设我们需要一个存储过程来批量更新多门课程的学分，同时确保数据的一致性和完整性。

创建存储过程

DELIMITER //
CREATE PROCEDURE UpdateCourseCredits(IN course_ids JSON, IN new_credits INT)
BEGIN
    DECLARE id INT;
    DECLARE id_json JSON;
    DECLARE total_updated INT DEFAULT 0;

    -- 解析JSON数组
    SET id_json = JSON_ARRAYAGG(JSON_EXTRACT(course_ids, CONCAT('$[', id, ']')));

    -- 开始事务
    START TRANSACTION;

    -- 循环更新课程学分
    WHILE id < JSON_LENGTH(id_json) DO
        SET id = id + 1;
        SET id = JSON_EXTRACT(id_json, CONCAT('$[', id, ']'));
        
        UPDATE Courses SET Credits = new_credits WHERE CourseID = id;
        SET total_updated = total_updated + ROW_COUNT();
    END WHILE;

    -- 检查是否有更新发生
    IF total_updated > 0 THEN
        COMMIT;
    ELSE
        ROLLBACK;
    END IF;
END //
DELIMITER ;

使用存储过程

CALL UpdateCourseCredits(JSON_ARRAY(101, 102, 103), 4);

通过这些案例，我们可以看到存储过程如何帮助我们执行复杂的数据库操作，包括更新、删除和计算汇总数据，同时还能确保事务的一致性和数据的完整性。存储过程的使用极大地简化了应用程序与数据库之间的交互，并提高了应用程序的整体性能和安全性。

接下来，我们将继续构建更多的案例来展示存储过程的不同用途。

示例6: 学生课程注册确认存储过程

假设我们需要一个存储过程来处理学生课程注册的确认流程，包括检查课程是否还有名额，更新学生和课程表，以及返回是否成功注册的结果。

创建存储过程

DELIMITER //
CREATE PROCEDURE ConfirmEnrollment(IN student_id INT, IN course_id INT, OUT success BOOLEAN)
BEGIN
    DECLARE seats_taken INT;
    DECLARE max_seats INT;

    -- 获取课程的最大容量
    SELECT Credits INTO max_seats FROM Courses WHERE CourseID = course_id;

    -- 计算已经注册的人数
    SELECT COUNT(*) INTO seats_taken FROM Enrollments WHERE CourseID = course_id;

    -- 检查是否有足够的名额
    IF seats_taken < max_seats THEN
        -- 插入新的注册记录
        INSERT INTO Enrollments (StudentID, CourseID) VALUES (student_id, course_id);
        
        -- 设置成功标志
        SET success = TRUE;
    ELSE
        -- 设置失败标志
        SET success = FALSE;
    END IF;
END //
DELIMITER ;

使用存储过程

SET @success = FALSE;
CALL ConfirmEnrollment(101, 101, @success);
SELECT @success;

示例7: 学生课程取消注册存储过程

假设我们需要一个存储过程来处理学生取消课程注册的过程，包括检查学生是否真的注册了该课程，然后删除注册记录。

创建存储过程

DELIMITER //
CREATE PROCEDURE CancelEnrollment(IN student_id INT, IN course_id INT, OUT success BOOLEAN)
BEGIN
    DECLARE enrolled BOOLEAN;

    -- 检查学生是否注册了该课程
    SELECT EXISTS(SELECT 1 FROM Enrollments WHERE StudentID = student_id AND CourseID = course_id) INTO enrolled;

    IF enrolled THEN
        -- 删除注册记录
        DELETE FROM Enrollments WHERE StudentID = student_id AND CourseID = course_id;
        
        -- 设置成功标志
        SET success = TRUE;
    ELSE
        -- 设置失败标志
        SET success = FALSE;
    END IF;
END //
DELIMITER ;

使用存储过程

SET @success = FALSE;
CALL CancelEnrollment(101, 101, @success);
SELECT @success;

示例8: 教师课程分配存储过程

假设我们需要一个存储过程来处理教师被分配教授新课程的过程，包括检查教师是否已经分配了该课程，然后更新 Teaches 表。

创建存储过程

DELIMITER //
CREATE PROCEDURE AssignCourseToTeacher(IN teacher_id INT, IN course_id INT, OUT success BOOLEAN)
BEGIN
    DECLARE assigned BOOLEAN;

    -- 检查教师是否已经分配了该课程
    SELECT EXISTS(SELECT 1 FROM Teaches WHERE TeacherID = teacher_id AND CourseID = course_id) INTO assigned;

    IF NOT assigned THEN
        -- 分配课程给教师
        INSERT INTO Teaches (TeacherID, CourseID) VALUES (teacher_id, course_id);
        
        -- 设置成功标志
        SET success = TRUE;
    ELSE
        -- 设置失败标志
        SET success = FALSE;
    END IF;
END //
DELIMITER ;

使用存储过程

SET @success = FALSE;
CALL AssignCourseToTeacher(201, 101, @success);
SELECT @success;

示例9: 教师课程取消分配存储过程

假设我们需要一个存储过程来处理取消教师教授某门课程的过程，包括检查教师是否真的分配了该课程，然后从 Teaches 表中删除记录。

创建存储过程

DELIMITER //
CREATE PROCEDURE UnassignCourseFromTeacher(IN teacher_id INT, IN course_id INT, OUT success BOOLEAN)
BEGIN
    DECLARE assigned BOOLEAN;

    -- 检查教师是否已经分配了该课程
    SELECT EXISTS(SELECT 1 FROM Teaches WHERE TeacherID = teacher_id AND CourseID = course_id) INTO assigned;

    IF assigned THEN
        -- 取消分配
        DELETE FROM Teaches WHERE TeacherID = teacher_id AND CourseID = course_id;
        
        -- 设置成功标志
        SET success = TRUE;
    ELSE
        -- 设置失败标志
        SET success = FALSE;
    END IF;
END //
DELIMITER ;

使用存储过程

SET @success = FALSE;
CALL UnassignCourseFromTeacher(201, 101, @success);
SELECT @success;

通过这些案例，我们可以看到存储过程如何帮助我们处理各种复杂的业务逻辑，包括学生课程注册、取消注册、教师课程分配和取消分配等操作。这些存储过程不仅可以提高数据处理的速度和效率，还可以确保数据的一致性和完整性。