存储过程和存储函数

存储过程

1.存储过程（Stored PROCEDURE）是为了完成特定功能，而在数据库中定义的SQL语句集合。
2.经过编译后存储在数据库中。
3.存储过程中可以包含流程控制语句和各种SQL语句。
4.存储过程可以接受参数，输出参数，返回单个或多个结果。
5.用户可以指定存储过程的名称，并给定参数来调用并执行它们。

⚠️条件和流控制语句必须定义在存储过程和存储函数中

存储过程的优点

😀存储过程将一组 SQL 语句封装为一个命名单元，可被多次调用而无需重复编写相同逻辑。

1.复用性。一次编写，多次调用。同一逻辑只需维护一份代码，各应用/客户端通过 CALL 调用即可

2.增强了SQL的功能性和灵活性。存储过程可以使用流程控制语句，可以完成复杂判断和运算

3.执行速度快。存储过程创建时即被编译并存入数据库的执行计划缓存，后续调用无需再次解析和优化。无需每次执行都进行优化。😀对高频调用的逻辑，性能提升尤为明显

4.减少网络流量。客户端只需发送一条调用语句，数据库内部自行执行全部操作。如执行 100 条 SQL需要 100 次网络往返，但是如果封装在存储过程中，只需1次网络返还（只需调用即可）

减少数据搬运。多步操作在同一服务器端完成，中间结果不需要传输到客户端再回传。

6.增强安全性。管理员可以对某一存储过程的权限进行限制，能够限制响应数据的访问权限，避免了非授权用户对数据的访问。

创建存储过程

使用CREATE PRODUCEDURE语句创建存储过程

语法格式：

CREATE PROCEDURE procedure_name([参数列表])
              [characteristic]
              BEGIN
                        过程体
                END;

其中：

procedure_name是存储过程的名称，数据库里唯一
([参数列表])是参数列表，形式如：[IN|OUT|INOUT] 参数名 type
其中：
IN表示输入参数，过程内只读
OUT 表示可写/输出参数，调用者接收结果
INOUT 双向：既传入又传出
type是参数数据类型
😀存储过程可以没有参数，但是()不能省略
[characteristic]是特性声明，于向 MySQL 描述该存储过程的行为特征、安全模式、注释信息，共 5 类可选：

语言声明。LANGUAGE SQL：表示存储过程的主体语言为 SQL。这是唯一合法的值，也是默认值
确定性。[NOT] DETERMINISTIC:告诉 MySQL，存储过程的执行结果是否是确定的（相同输入是否始终产生相同输出。）

例如：
1.DETERMINISTIC

-- 对相同输入，永远返回相同结果
CREATE PROCEDURE calc_tax(IN price DECIMAL(10,2))
DETERMINISTIC
BEGIN
    SELECT price * 0.13;
END;

2.not deterministic

-- 结果受时间、随机数、外部状态影响，相同输入可能不同输出
CREATE PROCEDURE get_now()
NOT DETERMINISTIC
BEGIN
    SELECT NOW();           -- 每次调用结果不同
END;

SQL 数据访问级别
声明存储过程对数据的访问程度，从最轻到最重共四级：
1.NO SQL:过程体不含 SQL 语句,仅纯计算，不访问数据，如数学运算、字符串处理
2.CONTAIN SQL:含 SQL 但不读写数据，如SET、DO、局部变量等
3.READS SQL DATA:只读不写,如SELECT
4.MODIFIES SQL DATA:可读可写,如SELECT / INSERT / UPDATE / DELETE

⚠️默认为CONTAIN SQL，表名存储过程使用了SQL语句，但是如果没有使用SQL语句，最好设置为NO SQL

安全模式

决定存储过程执行时，使用谁的权限来访问数据库对象。

1.SQL SECURITY DEFINER (定义者模式)
表示只有定义者才能执行
2.SQL SECURITY INVOKER(调用者模式)
调用者可以执行
默认为SQL SECURITY DEFINER

COMMENT '注释'

为存储过程添加注释说明，可通过 SHOW CREATE PROCEDURE 查看。
⚠️最好设置简单注释，以便理解代码

DELIMITER 重定义结束符

DELIMITER 问题：过程体内的 ; 会与命令行默认结束符冲突，必须临时修改
; 同时作为 SQL 语句分隔符和存储过程内部语句的结束符，导致 MySQL 客户端在遇到过程内第一个 ; 时就提前执行。必须临时更换分隔符。

在存储过程创建语句前加上DELIMITER $$

语法格式：

DELIMITER $$                    -- 将结束符改为 $$

CREATE PROCEDURE proc_name([参数列表])
      [特性列表]
              BEGIN
              END $$

DECIMITER ;                       -- 恢复结束符为 ;

创建存储过程示例

😀例子1
创建一个存储过程，从数据库gradem 的student 表中检索出所有籍贯为“青岛”的
学生的学号、姓名、班级号及家庭住址等信息

-- 打开数据库
USE gradem;
-- 创建存储过程
CREATE PROCEDURE proc_stud()
            BEGIN
                      SELECT sno,sname,classno,saddress 
                                  FROM student
                                  WHERE saddress LIKE '%青岛%'
                                  ORDER BY sno;
END;

🤔如果想让用户动态指定城市，可以改为带 IN 参数的版本：

-- 打开数据库
USE gradem;

-- 重定义结束符
DELIMITER $$

-- 创建存储过程
CREATE PROCEDURE proc_stud (IN city_name VARCHAR(20))
            BEGIN
                      SELECT sno,sname,classno,saddress 
                                  FROM student
                                  WHERE saddress LIKE CONCAT('%',city_name,'%')
                                  ORDER BY sno;
END$$

DELIMITER ;

-- 调用
CALL proc_stud;

😀例子2
创建一个名为 num_sc的存储过程，统计某位学生的考试门数

-- 打开数据库
USE gradem;

-- 重定义结束符
DELIMITER $$

-- 创建存储过程
CREATE PROCEDURE num_sc (IN temp_sno CHAR(20), OUT count_num INT)
            BEGIN
                      SELECT COUNT(*) INTO count_num 
                                  FROM sc
                                  WHERE sno = temp_sno;
END$$

DELIMITER ;

-- 调用
CALL num_sc('3022005175',@num)

调用存储过程

存储过程存储在数据库中，如果要执行其他数据库中的存储过程。需要打开相应的数据库或者指定数据库名称

使用CALL 语句调用存储过程

语法：

CALL [db_name.] sp_name [(参数列表)]

其中：
db_name 是sp所在数据库名称
sp_name 是存储过程名称

如果没有参数，一般是直接输出SELECT的查询结果，或者计算结果

存储函数

存储函数（Stored Function）是一组存储在数据库中的 SQL 语句，它与存储过程类似，但核心区别在于

存储函数必须通过 RETURN 语句返回一个单一的值，且可以直接嵌入 SQL 语句中像内置函数一样使用。

存储函数相当于自定义函数

创建存储函数

用户自己定义的存储函数与MYSQL内部函数性质相同。

MYSQL使用CREATE FUNCTION创建存储函数

语法：

CREATE FUNCTION func_name ([func_parameter[,...])
              RETURNS type
               [characteristic[,...]]
               BEGIN
                        -- 函数体（必须包含 RETURN 语句）
                        RETURN 返回值;
                END;

其中：
function_name 是存储函数的名称
func_parameter是参数列表，只允许IN模式，不能声明 OUT/INOUT
RETURNS type 指定返回值的类型
RETURN 返回值，只允许返回单一值

创建存储函数示例

😀例子1
创建一个名为 func_name 的存储函数，返回某班级的辅导员姓名

DELIMITER $$

CREATE FUNCTION func_name(class_no VARCHAR(8))
              RETURNS VARCHAR(8)
              READS SQL DATA
              BEGIN      
                        RETURN(SELECT header FROM class WHERE classno = class_no)
               END$$

DELIMITER ;

--调用（像内置函数一样直接用）
SELECT func_name(11234);

在上述代码中，该函数的参数为class_no，返回值是VARCHAR 类型的。

⚠️（1）PROCEDURE可以指定 IN、OUT或INOUT类型的参数，而FUNCTION的参数类
型默认为IN。RETURNS 子句只能包含在 FUNCTION 中，它用来指定函数的返回值类
型，而且函数体必须包含一个RETURN value语句。
⚠️(2)本例中的READS SQL DATA不能省略，如果省略则会出现错误提示。
原因是本例的代码实现仅仅涉及了读取表中的数据，而没有涉及数据的修改，这时需要
在函数体的开头处加上READS SQL DATA 声明。

调用存储函数

MYSQL使用SELECT调用存储函数

语法：

SELECT [db_name.] func_name ([参数列表])

或者直接嵌入SQL

如

SELECT name, salary, level,
       calc_bonus(salary, level) AS bonus
FROM employees;

查看存储过程和存储函数

可以使用SHOW STATUS或者SHOW CREATE查看基本信息，或者在information_schema.ROUTINES查看

1.SHOW STATUS

查看所有存储过程或者存储函数
语法：

SHOW [PROCEDURE|FUNCTION] STATUS ;

指定数据库筛选
SHOW [PROCEDURE|FUNCTION] STATUS WHERE Db = '';

按名称查找
SHOW [PROCEDURE|FUNCTION] STATUS WHERE Name LIKE '%proc%';
SHOW [PROCEDURE|FUNCTION] STATUS WHERE Name = proc;

2.SHOW CREATE

SHOW CREATE [PROCEDURE|FUNCTION] proc_stud;

3.从系统表查看详细信息

information_schema.ROUTINES 是 MySQL 的系统视图，存储了所有存储过程和存储函数的元数据，信息比 SHOW 命令更丰富。

SELECT ROUTINE_NAME, ROUTINE_TYPE, DEFINER, CREATED, LAST_ALTERED,
       SECURITY_TYPE, ROUTINE_COMMENT
FROM information_schema.ROUTINES
WHERE ROUTINE_SCHEMA = 'gradem' AND ROUTINE_TYPE = 'PROCEDURE';

⚠️建议使用ROUTINE_NAME指定存储过程或者存储函数的名称

存储过程和存储函数存储在 INFORMATION.ROUNTINES中，
字段包括
ROUTINE_SCHEMA 所属数据库
ROUTINE_NAME 名称
ROUTINE_TYPE PROCEDURE 或 FUNCTION
DTD_IDENTIFIER 函数的返回类型（仅函数有）
ROUTINE_BODY 主体语言（始终为 SQL）
ROUTINE_DEFINITION 完整的过程/函数体代码
IS_DETERMINISTIC 是否确定性（YES / NO）
SQL_DATA_ACCESS 数据访问级别
SECURITY_TYPE 安全模式（DEFINER / INVOKER）
DEFINER 创建者
CREATED 创建时间
LAST_ALTERED 最后修改时间
ROUTINE_COMMENT 注释
等

删除存储过程和存储函数

使用DROP [PROCEDURE|FUNCTION] 从当前数据库中删除用户定义的存储过程或者存储函数

语法：

DROP PROCEDURE|FUNCTION [IF EXISTS] 名称；

或者指定数据库：

DROP PROCEDURE|FUNCTION [IF EXISTS] db_name.名称；

posted @ 2026-08-01 17:04 Kaksno 阅读(1) 评论(0) 收藏举报

刷新页面返回顶部

Kano