数据库实验七：存储过程实验详解与实战指南

1. 存储过程概述

存储过程（Stored Procedure）是数据库系统中一组预先编译的SQL语句集合，存储在数据库中，可以通过名称调用执行。它是数据库编程中的重要组成部分，具有以下特点：

• 预编译性：存储过程在首次创建时进行编译，后续调用直接执行编译后的代码，提高执行效率
• 模块化：将复杂的业务逻辑封装为可重用的模块
• 安全性：通过权限控制限制对底层数据的直接访问
• 减少网络流量：客户端只需传递调用命令而非完整SQL语句

2. 存储过程实验环境准备

2.1 实验环境要求

• 数据库管理系统：MySQL 8.0+/SQL Server/Oracle等主流数据库
• 客户端工具：如MySQL Workbench、Navicat等
• 测试数据：准备适当的测试表和数据

2.2 基础表创建

-- 创建学生表
CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    age INT,
    gender CHAR(1),
    score DECIMAL(5,2)
);
-- 创建课程表
CREATE TABLE courses (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    credit INT
);
-- 创建选课关系表
CREATE TABLE student_course (
    student_id INT,
    course_id INT,
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (course_id) REFERENCES courses(id)
);

3. 存储过程创建与调用

3.1 基本语法结构

不同数据库系统的存储过程语法略有差异，以下是MySQL中的基本语法：

DELIMITER //
CREATE PROCEDURE procedure_name([参数列表])
BEGIN
    -- 存储过程体
    -- SQL语句
END //
DELIMITER ;

3.2 创建简单存储过程

示例1：无参数存储过程

DELIMITER //
CREATE PROCEDURE get_all_students()
BEGIN
    SELECT * FROM students;
END //
DELIMITER ;

调用方法：

CALL get_all_students();

示例2：带输入参数的存储过程

DELIMITER //
CREATE PROCEDURE get_student_by_id(IN student_id INT)
BEGIN
    SELECT * FROM students WHERE id = student_id;
END //
DELIMITER ;

调用方法：

CALL get_student_by_id(1);

3.3 带输出参数的存储过程

DELIMITER //
CREATE PROCEDURE get_student_count(OUT total INT)
BEGIN
    SELECT COUNT(*) INTO total FROM students;
END //
DELIMITER ;

调用方法：

CALL get_student_count(@count);
SELECT @count;

4. 存储过程高级特性

4.1 流程控制语句

存储过程中可以使用条件判断和循环等流程控制语句：

IF-THEN-ELSE语句

DELIMITER //
CREATE PROCEDURE update_student_score(IN s_id INT, IN score_change DECIMAL(5,2))
BEGIN
    DECLARE current_score DECIMAL(5,2);
    SELECT score INTO current_score FROM students WHERE id = s_id;
    IF current_score + score_change > 100 THEN
        UPDATE students SET score = 100 WHERE id = s_id;
    ELSEIF current_score + score_change < 0 THEN
        UPDATE students SET score = 0 WHERE id = s_id;
    ELSE
        UPDATE students SET score = score + score_change WHERE id = s_id;
    END IF;
END //
DELIMITER ;

WHILE循环

DELIMITER //
CREATE PROCEDURE generate_test_data(IN num INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE i <= num DO
        INSERT INTO students(name, age, gender, score)
        VALUES (CONCAT('Student', i), FLOOR(15 + RAND() * 10), 
                IF(RAND() > 0.5, 'M', 'F'), ROUND(RAND() * 100, 2));
        SET i = i + 1;
    END WHILE;
END //
DELIMITER ;

4.2 异常处理

不同数据库系统的异常处理机制不同，以下是MySQL中的示例：

DELIMITER //
CREATE PROCEDURE safe_delete_student(IN s_id INT)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SELECT 'Error occurred, transaction rolled back' AS message;
    END;
    START TRANSACTION;
    DELETE FROM student_course WHERE student_id = s_id;
    DELETE FROM students WHERE id = s_id;
    COMMIT;
    SELECT 'Student deleted successfully' AS message;
END //
DELIMITER ;

5. 存储过程实战案例

5.1 学生成绩统计分析

DELIMITER //
CREATE PROCEDURE student_score_analysis(
    OUT avg_score DECIMAL(5,2),
    OUT max_score DECIMAL(5,2),
    OUT min_score DECIMAL(5,2),
    OUT pass_rate DECIMAL(5,2)
)
BEGIN
    SELECT AVG(score), MAX(score), MIN(score) 
    INTO avg_score, max_score, min_score
    FROM students;
    SELECT COUNT(*) INTO @total FROM students;
    SELECT COUNT(*) INTO @pass FROM students WHERE score >= 60;
    SET pass_rate = (@pass / @total) * 100;
END //
DELIMITER ;

5.2 复杂业务逻辑处理

DELIMITER //
CREATE PROCEDURE enroll_course(
    IN s_id INT, 
    IN c_id INT,
    OUT result VARCHAR(100)
)
BEGIN
    DECLARE credit_sum INT;
    DECLARE course_count INT;
    -- 检查学生是否存在
    IF NOT EXISTS (SELECT 1 FROM students WHERE id = s_id) THEN
        SET result = 'Error: Student not found';
        LEAVE proc_label;
    END IF;
    -- 检查课程是否存在
    IF NOT EXISTS (SELECT 1 FROM courses WHERE id = c_id) THEN
        SET result = 'Error: Course not found';
        LEAVE proc_label;
    END IF;
    -- 检查是否已选该课程
    IF EXISTS (SELECT 1 FROM student_course WHERE student_id = s_id AND course_id = c_id) THEN
        SET result = 'Error: Already enrolled in this course';
        LEAVE proc_label;
    END IF;
    -- 计算当前已选课程总学分
    SELECT SUM(c.credit), COUNT(*)
    INTO credit_sum, course_count
    FROM student_course sc
    JOIN courses c ON sc.course_id = c.id
    WHERE sc.student_id = s_id;
    -- 获取新课程学分
    SELECT credit INTO @new_credit FROM courses WHERE id = c_id;
    -- 检查学分限制（假设不超过30学分）
    IF (IFNULL(credit_sum, 0) + @new_credit) > 30 THEN
        SET result = 'Error: Credit limit exceeded';
        LEAVE proc_label;
    END IF;
    -- 检查课程数量限制（假设不超过8门）
    IF IFNULL(course_count, 0) >= 8 THEN
        SET result = 'Error: Course limit exceeded';
        LEAVE proc_label;
    END IF;
    -- 执行选课操作
    INSERT INTO student_course(student_id, course_id) VALUES (s_id, c_id);
    SET result = 'Success: Course enrolled successfully';
    proc_label: BEGIN END;
END //
DELIMITER ;

6. 存储过程优化与管理

6.1 性能优化建议

1. 减少数据库交互：将多个SQL语句合并到一个存储过程中
2. 合理使用参数：避免在存储过程中拼接动态SQL
3. 索引优化：确保存储过程中使用的查询条件有适当的索引
4. 避免过度使用游标：游标性能较差，尽量用集合操作替代

6.2 存储过程管理

查看存储过程

-- MySQL
SHOW PROCEDURE STATUS;
SHOW CREATE PROCEDURE procedure_name;
-- SQL Server
SELECT * FROM sys.procedures;

修改存储过程

-- MySQL
DROP PROCEDURE IF EXISTS procedure_name;
CREATE PROCEDURE procedure_name()
BEGIN
    -- 新定义
END;

删除存储过程

DROP PROCEDURE IF EXISTS procedure_name;

7. 常见问题与解决方案

7.1 权限问题

• 问题：执行存储过程时报权限错误
• 解决方案：确保用户有执行存储过程的权限

GRANT EXECUTE ON PROCEDURE db_name.procedure_name TO 'username'@'host';

7.2 调试困难

• 问题：存储过程调试不便
• 解决方案：
  • 使用SELECT输出中间结果
  • 将复杂存储过程拆分为多个简单过程
  • 使用专业的数据库开发工具

7.3 版本控制

• 问题：存储过程难以进行版本管理
• 解决方案：
  • 将存储过程定义脚本纳入版本控制系统
  • 使用数据库迁移工具（如Flyway、Liquibase）

8. 实验总结

通过本次存储过程实验，我们掌握了：

1. 存储过程的基本概念和优势
2. 存储过程的创建、调用和管理方法
3. 存储过程中的流程控制和异常处理
4. 存储过程在实际业务场景中的应用
5. 存储过程的优化和管理技巧

存储过程作为数据库编程的重要工具，能够显著提高数据库应用的性能、安全性和可维护性。在实际开发中，应根据业务需求合理使用存储过程，遵循模块化、规范化的开发原则。