MySQL教程 / 第 110 节

第11章：视图、存储过程与函数

提高代码复用性和安全性的高级特性

11.1 视图（View）⭐⭐⭐⭐

11.1.1 什么是视图？

定义：

虚拟表，基于SQL查询结果
不存储数据，只存储查询语句
简化复杂查询，提高安全性

示例：

-- 创建视图
CREATE VIEW v_user_orders AS
SELECT 
    u.id AS user_id,
    u.name AS user_name,
    o.id AS order_id,
    o.amount,
    o.create_time
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- 使用视图
SELECT * FROM v_user_orders WHERE user_id = 1;

11.1.2 视图操作

创建视图：

-- 基本语法
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

-- 创建或替换视图
CREATE OR REPLACE VIEW v_active_users AS
SELECT id, name, email
FROM users
WHERE status = 'active';

-- 创建视图并指定列名
CREATE VIEW v_user_info(user_id, user_name, user_email) AS
SELECT id, name, email FROM users;

查看视图：

-- 查看所有视图
SHOW FULL TABLES WHERE Table_type = 'VIEW';

-- 查看视图定义
SHOW CREATE VIEW v_user_orders;

-- 查看视图结构
DESC v_user_orders;

修改视图：

-- 方法1：CREATE OR REPLACE
CREATE OR REPLACE VIEW v_active_users AS
SELECT id, name, email, phone
FROM users
WHERE status = 'active';

-- 方法2：ALTER VIEW
ALTER VIEW v_active_users AS
SELECT id, name, email, phone, address
FROM users
WHERE status = 'active';

删除视图：

DROP VIEW IF EXISTS v_user_orders;

11.1.3 可更新视图

可更新视图条件：

不包含聚合函数
不包含DISTINCT
不包含GROUP BY
不包含UNION

示例：

-- 可更新视图
CREATE VIEW v_users AS
SELECT id, name, email FROM users;

-- 可以INSERT、UPDATE、DELETE
INSERT INTO v_users(name, email) VALUES ('张三', 'test@example.com');
UPDATE v_users SET name = '李四' WHERE id = 1;
DELETE FROM v_users WHERE id = 1;

-- 不可更新视图（包含聚合函数）
CREATE VIEW v_user_count AS
SELECT COUNT(*) AS total FROM users;

-- 无法INSERT、UPDATE、DELETE

11.1.4 视图的优缺点

优点：

✅ 简化复杂查询
✅ 提高安全性（隐藏敏感字段）
✅ 逻辑独立性（表结构变化，视图不变）

缺点：

❌ 性能可能较差
❌ 不能创建索引

11.2 存储过程（Stored Procedure）⭐⭐⭐⭐

11.2.1 什么是存储过程？

定义：

一组预编译的SQL语句
存储在数据库中
可重复调用

优点：

✅ 减少网络传输
✅ 提高性能（预编译）
✅ 代码复用
✅ 安全性（权限控制）

11.2.2 创建存储过程

基本语法：

DELIMITER $$

CREATE PROCEDURE procedure_name(
    [IN|OUT|INOUT] parameter_name datatype,
    ...
)
BEGIN
    -- SQL语句
END$$

DELIMITER ;

示例1：无参数存储过程

DELIMITER $$

CREATE PROCEDURE sp_get_user_count()
BEGIN
    SELECT COUNT(*) AS total FROM users;
END$$

DELIMITER ;

-- 调用
CALL sp_get_user_count();

示例2：IN参数（输入参数）

DELIMITER $$

CREATE PROCEDURE sp_get_user_by_id(
    IN p_user_id INT
)
BEGIN
    SELECT * FROM users WHERE id = p_user_id;
END$$

DELIMITER ;

-- 调用
CALL sp_get_user_by_id(1);

示例3：OUT参数（输出参数）

DELIMITER $$

CREATE PROCEDURE sp_get_user_count_out(
    OUT p_count INT
)
BEGIN
    SELECT COUNT(*) INTO p_count FROM users;
END$$

DELIMITER ;

-- 调用
CALL sp_get_user_count_out(@count);
SELECT @count;

示例4：INOUT参数（输入输出参数）

DELIMITER $$

CREATE PROCEDURE sp_double_value(
    INOUT p_value INT
)
BEGIN
    SET p_value = p_value * 2;
END$$

DELIMITER ;

-- 调用
SET @value = 10;
CALL sp_double_value(@value);
SELECT @value;  -- 20

11.2.3 存储过程中的流程控制

IF语句：

DELIMITER $$

CREATE PROCEDURE sp_check_age(
    IN p_age INT,
    OUT p_result VARCHAR(20)
)
BEGIN
    IF p_age < 18 THEN
        SET p_result = '未成年';
    ELSEIF p_age < 60 THEN
        SET p_result = '成年';
    ELSE
        SET p_result = '老年';
    END IF;
END$$

DELIMITER ;

CASE语句：

DELIMITER $$

CREATE PROCEDURE sp_get_grade(
    IN p_score INT,
    OUT p_grade CHAR(1)
)
BEGIN
    CASE
        WHEN p_score >= 90 THEN SET p_grade = 'A';
        WHEN p_score >= 80 THEN SET p_grade = 'B';
        WHEN p_score >= 70 THEN SET p_grade = 'C';
        WHEN p_score >= 60 THEN SET p_grade = 'D';
        ELSE SET p_grade = 'F';
    END CASE;
END$$

DELIMITER ;

WHILE循环：

DELIMITER $$

CREATE PROCEDURE sp_sum_1_to_n(
    IN p_n INT,
    OUT p_sum INT
)
BEGIN
    DECLARE i INT DEFAULT 1;
    SET p_sum = 0;
    
    WHILE i <= p_n DO
        SET p_sum = p_sum + i;
        SET i = i + 1;
    END WHILE;
END$$

DELIMITER ;

REPEAT循环：

DELIMITER $$

CREATE PROCEDURE sp_sum_repeat(
    IN p_n INT,
    OUT p_sum INT
)
BEGIN
    DECLARE i INT DEFAULT 1;
    SET p_sum = 0;
    
    REPEAT
        SET p_sum = p_sum + i;
        SET i = i + 1;
    UNTIL i > p_n
    END REPEAT;
END$$

DELIMITER ;

LOOP循环：

DELIMITER $$

CREATE PROCEDURE sp_sum_loop(
    IN p_n INT,
    OUT p_sum INT
)
BEGIN
    DECLARE i INT DEFAULT 1;
    SET p_sum = 0;
    
    loop_label: LOOP
        SET p_sum = p_sum + i;
        SET i = i + 1;
        
        IF i > p_n THEN
            LEAVE loop_label;
        END IF;
    END LOOP;
END$$

DELIMITER ;

11.2.4 存储过程管理

查看存储过程：

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

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

删除存储过程：

DROP PROCEDURE IF EXISTS sp_get_user_by_id;

11.3 函数（Function）⭐⭐⭐⭐

11.3.1 存储函数 vs 存储过程

特性	存储函数	存储过程
返回值	必须返回一个值	可以返回多个值（OUT参数）
调用方式	SELECT中调用	CALL调用
参数	只有IN参数	IN、OUT、INOUT参数
事务	不能包含事务	可以包含事务

11.3.2 创建函数

基本语法：

DELIMITER $$

CREATE FUNCTION function_name(
    parameter_name datatype,
    ...
)
RETURNS return_datatype
[DETERMINISTIC | NOT DETERMINISTIC]
BEGIN
    -- SQL语句
    RETURN value;
END$$

DELIMITER ;

示例：

DELIMITER $$

CREATE FUNCTION fn_get_user_name(p_user_id INT)
RETURNS VARCHAR(100)
DETERMINISTIC
BEGIN
    DECLARE v_name VARCHAR(100);
    SELECT name INTO v_name FROM users WHERE id = p_user_id;
    RETURN v_name;
END$$

DELIMITER ;

-- 调用
SELECT fn_get_user_name(1);
SELECT id, fn_get_user_name(id) AS name FROM orders;

11.3.3 函数管理

查看函数：

-- 查看所有函数
SHOW FUNCTION STATUS WHERE Db = 'mydb';

-- 查看函数定义
SHOW CREATE FUNCTION fn_get_user_name;

删除函数：

DROP FUNCTION IF EXISTS fn_get_user_name;

11.4 本章总结

本章学习内容：

✅ 视图（创建、使用、可更新视图）⭐⭐⭐⭐
✅ 存储过程（参数、流程控制）⭐⭐⭐⭐
✅ 函数（创建、使用）⭐⭐⭐⭐

重点掌握：

视图简化复杂查询
存储过程：IN、OUT、INOUT参数
流程控制：IF、CASE、WHILE、LOOP
函数必须返回值

面试重点：

视图和表的区别
存储过程和函数的区别
存储过程的优缺点

下一章预告： 触发器与事件

练习题

什么是视图？有什么优缺点？
如何创建可更新视图？
存储过程和函数有什么区别？
IN、OUT、INOUT参数有什么区别？
如何在存储过程中使用IF语句？
创建一个存储过程，计算1到N的和
创建一个函数，返回用户的订单数量
视图可以创建索引吗？
存储过程的优缺点是什么？
实战：创建一个视图和存储过程

继续学习： 第12章：触发器与事件