SQL学习笔记4

SQL SQL
发布时间 :
字数:1.4k 阅读 :
  1. 10. 存储过程
    1. 10.1 存储过程 vs. 普通 SQL
    2. 10.2 基本语法
      1. MySQL / MariaDB
      2. PostgreSQL
    3. 10.3 参数类型
    4. 10.4 变量与赋值
    5. 10.5 条件判断 IF / CASE
    6. 10.6 循环 LOOP / WHILE / REPEAT
    7. 10.7 游标 CURSOR（逐行处理）
    8. 10.8 错误处理
    9. 10.9 查看与管理存储过程
    10. 10.10 什么时候用存储过程？
    11. 10.11 存储过程 vs. 函数 vs. 触发器

10. 存储过程

存储过程（Stored Procedure）是一组预编译的 SQL 语句，像一个存在数据库里的”函数”——一次编写，多次调用，比反复发 SQL 更高效。

10.1 存储过程 vs. 普通 SQL

对比维度 普通 SQL 存储过程
执行方式 每次发 SQL 文本，数据库解析编译 预编译好，直接执行
网络开销 每次传整段 SQL 只传 CALL proc 命令
逻辑能力 有限（单条或少量语句） 变量、循环、条件判断
安全性 SQL 拼接，易注入 参数化，防注入
维护 散落在应用代码里 集中在数据库里

10.2 基本语法

MySQL / MariaDB

-- 修改分隔符，避免存储过程中的分号提前结束
DELIMITER $$

CREATE PROCEDURE get_students_by_age(IN min_age INT)
BEGIN
    SELECT id, name, age FROM students WHERE age >= min_age;
END$$

DELIMITER ;

-- 调用
CALL get_students_by_age(18);

PostgreSQL

CREATE OR REPLACE FUNCTION get_students_by_age(min_age INT)
RETURNS TABLE(id INT, name VARCHAR, age INT) AS $$
BEGIN
    RETURN QUERY
    SELECT s.id, s.name, s.age FROM students s WHERE s.age >= min_age;
END;
$$ LANGUAGE plpgsql;

-- 调用
SELECT * FROM get_students_by_age(18);

💡 PostgreSQL 用 FUNCTION 代替 PROCEDURE（PG 11 之后也支持 PROCEDURE，但函数更常用）。MySQL/SQL Server 用 CREATE PROCEDURE。

10.3 参数类型

参数 说明 适用场景
IN 输入参数（默认） 传入过滤条件
OUT 输出参数 返回计算结果
INOUT 输入+输出 既传入又传出

MySQL 示例：OUT 参数

DELIMITER $$

CREATE PROCEDURE get_avg_score(
    IN  p_student_id INT,
    OUT p_avg_score  DECIMAL(5,2)
)
BEGIN
    SELECT AVG(score) INTO p_avg_score
    FROM scores
    WHERE student_id = p_student_id;
END$$

DELIMITER ;

-- 调用并获取返回值
CALL get_avg_score(1, @avg);
SELECT @avg;  -- 查看返回值

10.4 变量与赋值

DELIMITER $$

CREATE PROCEDURE count_high_scores(IN threshold INT)
BEGIN
    DECLARE total INT DEFAULT 0;          -- 声明局部变量
    DECLARE high_count INT DEFAULT 0;

    SELECT COUNT(*) INTO total FROM scores;
    SELECT COUNT(*) INTO high_count FROM scores WHERE score >= threshold;

    SELECT total AS ''总记录数'', high_count AS ''高分人数'',
           ROUND(high_count / total * 100, 2) AS ''高分占比'';
END$$

DELIMITER ;

10.5 条件判断 IF / CASE

DELIMITER $$

CREATE PROCEDURE get_grade_level(IN p_score INT)
BEGIN
    DECLARE grade VARCHAR(10);

    IF p_score >= 90 THEN
        SET grade = ''优秀'';
    ELSEIF p_score >= 80 THEN
        SET grade = ''良好'';
    ELSEIF p_score >= 60 THEN
        SET grade = ''及格'';
    ELSE
        SET grade = ''不及格'';
    END IF;

    SELECT grade AS ''评级'';
END$$

DELIMITER ;

CASE 写法：

CASE
    WHEN p_score >= 90 THEN SET grade = ''优秀'';
    WHEN p_score >= 80 THEN SET grade = ''良好'';
    WHEN p_score >= 60 THEN SET grade = ''及格'';
    ELSE SET grade = ''不及格'';
END CASE;

10.6 循环 LOOP / WHILE / REPEAT

-- WHILE 循环：插入 100 条测试数据
DELIMITER $$

CREATE PROCEDURE seed_test_data()
BEGIN
    DECLARE i INT DEFAULT 1;

    WHILE i <= 100 DO
        INSERT INTO scores (student_id, subject, score)
        VALUES (
            FLOOR(1 + RAND() * 10),               -- 随机学生 1-10
            ELT(FLOOR(1 + RAND() * 3), ''语文'', ''数学'', ''英语''),
            FLOOR(50 + RAND() * 51)                -- 随机分数 50-100
        );
        SET i = i + 1;
    END WHILE;
END$$

DELIMITER ;

三种循环对比：

循环类型 特点 典型场景
WHILE ... DO ... END WHILE 先判断后执行 遍历不确定次数
REPEAT ... UNTIL ... END REPEAT 先执行后判断（至少跑一次） 至少执行一次的循环
loop_label: LOOP ... END LOOP 手动 LEAVE 跳出 灵活控制的无限循环

10.7 游标 CURSOR（逐行处理）

当需要对查询结果的每一行做复杂处理时使用。

DELIMITER $$

CREATE PROCEDURE update_all_grades()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE sid INT;
    DECLARE avg_s DECIMAL(5,2);

    -- 声明游标
    DECLARE cur CURSOR FOR
        SELECT student_id, AVG(score) FROM scores GROUP BY student_id;

    -- 声明继续处理器：游标取完时 done = TRUE
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;

    read_loop: LOOP
        FETCH cur INTO sid, avg_s;
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- 根据平均分更新学生等级
        UPDATE students
        SET grade_level = CASE
            WHEN avg_s >= 90 THEN ''A''
            WHEN avg_s >= 80 THEN ''B''
            WHEN avg_s >= 60 THEN ''C''
            ELSE ''D''
        END
        WHERE id = sid;
    END LOOP;

    CLOSE cur;
END$$

DELIMITER ;

⚠️ 游标逐行处理性能较低，能用 UPDATE ... JOIN 解决的尽量用集合操作。

10.8 错误处理

DELIMITER $$

CREATE PROCEDURE safe_insert_score(
    IN p_student_id INT,
    IN p_subject    VARCHAR(20),
    IN p_score      INT
)
BEGIN
    -- 遇到错误不中断，继续执行
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        SELECT ''插入失败，请检查数据'' AS result;
        ROLLBACK;
    END;

    START TRANSACTION;

    IF p_score < 0 OR p_score > 100 THEN
        SIGNAL SQLSTATE ''45000'' SET MESSAGE_TEXT = ''分数必须在 0-100 之间'';
    END IF;

    INSERT INTO scores (student_id, subject, score)
    VALUES (p_student_id, p_subject, p_score);

    COMMIT;
    SELECT ''插入成功'' AS result;
END$$

DELIMITER ;

10.9 查看与管理存储过程

-- MySQL 查看所有存储过程
SHOW PROCEDURE STATUS WHERE Db = ''your_database'';

-- 查看存储过程定义
SHOW CREATE PROCEDURE get_students_by_age;

-- 删除存储过程
DROP PROCEDURE IF EXISTS get_students_by_age;

-- PostgreSQL 查看函数
SELECT routine_name FROM information_schema.routines
WHERE routine_type = ''FUNCTION'' AND specific_schema = ''public'';

10.10 什么时候用存储过程？

适合 ✅

  • 复杂的业务逻辑需要多步 SQL 操作（如转账：扣钱 → 记日志 → 更新余额）
  • 批量数据处理（如月底结算、数据归档）
  • 对性能要求高、需要减少网络往返的场景
  • 需要封装复用且不希望应用层处理的数据操作

不适合 ❌

  • 简单的单表 CRUD —— 杀鸡用牛刀
  • 需要频繁变更的业务逻辑 —— 调试和版本管理不如应用代码方便
  • 跨数据库移植的项目 —— 各数据库存储过程语法差异大
  • 高并发场景下的复杂计算 —— 会占用数据库 CPU，不如放应用层或消息队列

10.11 存储过程 vs. 函数 vs. 触发器

特性 存储过程 函数 触发器
调用方式 CALL proc() SELECT func() 事件触发（INSERT/UPDATE/DELETE）
返回值 可有多个 OUT 参数 必须返回一个值 无返回值
事务控制 可 COMMIT/ROLLBACK 不能 跟随触发语句的事务
在 SELECT 中使用 ❌ ✅ ❌
典型场景 批量处理、复杂业务逻辑 计算、转换、格式化 审计日志、数据校验
sunrtnj@163.com

©2019-2025

SunRT个人主页