Mysql-存储过程&存储函数

重庆求内推

已于 2024-08-13 13:46:32 修改

阅读量495

点赞数 10

文章标签： mysql 数据库

于 2024-08-13 13:35:19 首次发布

本文链接：https://blog.csdn.net/sunhaihan99/article/details/141131439

版权

一、存储过程

一、概述

1、MySQL 存储过程是一种在 MySQL 数据库中存储的预编译 SQL 代码块，它可以接受参数并执行一系列 SQL 操作。

2、存储过程通常用于封装和重用复杂的 SQL 逻辑，从而简化应用程序的代码，提升性能，并提供更高的安全性。

3、减少数据在数据库和应用服务器之间的传输，提高数据处理的效率。

4、存储过程没有返回值，但可以通过 OUT 参数来获取。

优点：

代码复用：存储过程允许重复使用相同的 SQL 逻辑，减少代码冗余。
性能优化：存储过程在创建时已编译，可以减少 SQL 语句的编译时间，从而提高执行效率。
安全性：通过限制用户对数据库表的直接操作，提高数据安全性。
事务管理：能够在存储过程中管理事务，确保数据的一致性和完整性。
简化应用程序逻辑：将复杂的逻辑封装在存储过程中，使应用程序代码更简洁。
参数化：支持输入参数，可以通过参数化查询减少 SQL 注入的风险。

缺点：

调试困难：存储过程的调试和错误处理较为复杂，不如应用层代码易于调试。
可移植性差：存储过程通常是数据库特定的，可能会影响数据库的迁移和升级。
复杂性增加：存储过程中的复杂逻辑可能导致管理和维护的困难，尤其是在大规模系统中。
版本控制：存储过程的版本控制和管理可能不如应用程序代码方便。
学习曲线：需要学习和掌握存储过程的语法和最佳实践，这可能增加开发的学习成本。

1、创建存储过程

-- 创建一个新的存储过程
CREATE PROCEDURE 

    -- 存储过程的名称
    procedure_name ()

-- 存储过程体的开始标记，后续的语句将在这个块内执行
begin

    -- 存储过程的语句块

-- 存储过程体的结束标记，表示存储过程的逻辑结束
end ;

1.2、示例：

DELIMITER $
CREATE PROCEDURE pro_test1()
BEGIN
SELECT 'Hello Mysql' ;
END$
DELIMITER ;

PS：

DELIMITER：

1、该关键字用来声明SQL语句的分隔符 , 告诉 MySQL 解释器，该段命令是否已经结束了，mysql是否可以执行了。

2、默认情况下，delimiter是分号 ; 。在命令行客户端中，如果有一行命令以分号结束，那么回车后，mysql将会执行该命令。

3、不改的情况下，会出现以下问题：

打出 ; 后，mysql 会直接执行该命令，导致语法错误。

2、调用存储过程

CALL pro_test1();

3、删除存储过程

DROP PROCEDURE [IF EXISTS] pro_test1;

4、查看存储过程

-- 查询db_name数据库中的所有的存储过程
select name from mysql.proc where db='db_name';

二、语法

1、变量

DECLARE ：通过 DECLARE 可以定义一个局部变量，该变量的作用范围只能在 BEGIN…END 块中。

DECLARE var_name[,...] type [DEFAULT value]

DECLARE：这是声明局部变量的关键字，用于在存储过程或函数中定义新的变量。

var_name：这是变量的名称。变量名称应该遵循数据库系统的命名规则，通常以字母开头，后面可以跟字母、数字或下划线。

type：这是变量的数据类型。例如，INT、VARCHAR(100)、DATE等。它指定了变量可以存储的数据类型。

DEFAULT value：这是可选部分。它用于为变量指定一个默认值。如果在变量声明时不提供初始值，则该变量的值将为NULL。通过DEFAULT value，你可以在声明时为变量赋一个初始值。

示例：

DELIMITER $

CREATE PROCEDURE pro_test2()
BEGIN
	DECLARE num INT DEFAULT 5;
	SELECT num + 10;
END $

DELIMITER ;

SET：直接赋值使用 SET，可以赋常量或者赋表达式。

SET var_name = expr [, var_name = expr] ...

SET：这是赋值操作的关键字，用于将一个或多个变量设置为指定的值。

var_name：这是要赋值的变量的名称。它必须是事先在存储过程或函数中声明的变量。

=：赋值操作符，用于将右边的表达式的结果赋给左边的变量。

expr：这是一个表达式，用于计算要赋给变量的值。表达式可以是常量、计算结果、函数调用等。

,：逗号，用于分隔多个赋值操作。在一条 SET 语句中可以同时对多个变量进行赋值。

例如：SET age = 25, name = 'Alice';

示例：

DELIMITER $

CREATE PROCEDURE pro_test3()
BEGIN
	DECLARE NAME VARCHAR(20);
	SET NAME = 'MYSQL';
	SELECT NAME ;
END$

DELIMITER ;

INTO：同为赋值操作

DELIMITER $

CREATE PROCEDURE pro_test5()
BEGIN
	DECLARE countnum INT;

	-- 统计 tab_name 表总数并通过 INTO 赋值给 countnum
	SELECT count(*) INTO countnum FROM tab_name;
	SELECT countnum;
END$

DELIMITER ;

2、if-条件判断

if search_condition then statement_list
    [elseif search_condition then statement_list] ...
    [else statement_list]
end if;

if：开始条件语句的标记。
search_condition：条件表达式，用于判断是否执行相应的语句列表。
then：表示如果条件为真，则执行后面的语句列表。
statement_list：当条件满足时执行的语句集合。
elseif：用于检查另一个条件，如果之前的条件不成立则进行检查。
else：所有条件都不满足时执行的默认语句列表。
end if：结束条件语句的标记。

2.2、示例

DELIMITER $

CREATE PROCEDURE pro_test6()
BEGIN

	DECLARE height INT DEFAULT 175;
	DECLARE description VARCHAR(50);

	IF height >= 180 THEN 
		SET description = '身材高挑';
	ELSEIF height >= 170 AND height < 180 THEN 
		SET description = '标准身材';
	ELSE
		SET description = '一般身材';
	END IF;

	SELECT description ;
END$

DELIMITER ;

2.3、传递参数

CREATE PROCEDURE procedure_name([IN/OUT/INOUT] 参数名 参数类型)

IN : 该参数可以作为输入，也就是需要调用方传入值（默认）
OUT: 该参数作为输出，也就是该参数可以作为返回值
INOUT: 既可以作为输入参数，也可以作为输出参数

2.3.2、IN-输入

需求：根据定义的身高变量，判定当前身高的所属的身材类型

DELIMITER $

CREATE PROCEDURE pro_test7(IN height INT)
BEGIN

	DECLARE description VARCHAR(50);

	IF height >= 180 THEN
		SET description='身材高挑';

	ELSEIF height >= 170 and height < 180 THEN
		SET description='标准身材';

	ELSE
		SET description='一般身材';

	END if;

	SELECT CONCAT('身高 ', height , '对应的身材类型为:',description);

END$

DELIMITER ;

2.3.3、OUT-输出

需求：根据传入的身高变量，获取当前身高的所属的身材类型

DELIMITER $

CREATE PROCEDURE pro_test1(IN height INT, OUT description VARCHAR(100))
BEGIN

	IF height >= 180 THEN
		SET description='身材高挑';

	ELSEIF height >= 170 and height < 180 THEN
		SET description='标准身材';

	ELSE
		SET description='一般身材';

	END if;

END$

DELIMITER ;

调用：

 CALL pro_test1(180, @description);

 SELECT @description;

PS：

@description : 这种变量要在变量名称前面加上“@”符号，叫做用户会话变量，代表整个会话过程他都是有作用的，可以在会话中临时存储和使用数据。（退出数据库再登录时，SELECT 时数据会为 NULL）

@@global.sort_buffer_size : 这种在变量前加上 "@@" 符号, 叫做系统变量

2.3.4、INOUT-输入输出

-- 先创建一个数据库
CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL(10,2)
);

-- 插入一些示例数据
INSERT INTO products (id, name, price) VALUES
(1, 'Product A', 100.00),
(2, 'Product B', 150.00),
(3, 'Product C', 200.00);

-- 创建存储过程

DELIMITER $

CREATE PROCEDURE AdjustPrice(
    INOUT product_id INT,
    IN discount DECIMAL(5,2)
)
BEGIN
    DECLARE original_price DECIMAL(10,2);
    
    -- 查询原始价格
    -- 详解：
    -- 查询 products 表中 id = product_id 的 price 并赋值给 original_price
    SELECT price INTO original_price
    FROM products
    WHERE id = product_id;
    
    -- 计算并更新价格
    UPDATE products
    SET price = original_price * (1 - discount / 100)
    WHERE id = product_id;
    
    -- 返回调整后的产品ID
    SELECT id INTO product_id
    FROM products
    WHERE id = product_id;
END $

DELIMITER ;

-- 声明变量
SET @prod_id = 1;
SET @discount = 10;

-- 调用存储过程
CALL AdjustPrice(@prod_id, @discount);

-- 查看更新后的结果
SELECT * FROM products WHERE id = @prod_id;

3、case-结构

方式一 :
CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END CASE;


方式二 :
CASE
    WHEN search_condition THEN statement_list
    [WHEN search_condition THEN statement_list] ...
    [ELSE statement_list]
END CASE;

方式一：

CASE: 开始一个条件表达式，用于根据不同的条件执行不同的代码块。
case_value: 用于在 CASE 表达式中进行比较的值。CASE 表达式将与这个值进行比较以决定接下来的操作。
WHEN: 定义一个条件分支，WHEN 后面跟的是一个条件表达式。
when_value: 在 WHEN 之后的值或条件，CASE 将这个值与 case_value 进行比较。如果匹配，则执行相关的 statement_list。
THEN: 指定在 WHEN 条件为真时要执行的语句块。
statement_list: 在特定的条件满足时要执行的一组 SQL 语句或表达式。
ELSE: 可选的分支，当所有的 WHEN 条件都不满足时，ELSE 后面的语句将会执行。
END CASE: 结束 CASE 表达式的定义。

方式二：

search_condition: 在 WHEN 后面的布尔表达式，用于判断是否满足条件。

在这种形式中，CASE 根据 search_condition 的结果选择执行 statement_list。如果没有条件满足，则执行 ELSE 部分的语句。

需求：给定一个月份, 然后计算出所在的季度

DELIMITER $

CREATE PROCEDURE pro_test1(month INT)
BEGIN
    DECLARE result VARCHAR(20);

    CASE
        WHEN month >= 1 AND month <= 3 THEN
            SET result = '第一季度';
        WHEN month >= 4 AND month <= 6 THEN
            SET result = '第二季度';
        WHEN month >= 7 AND month <= 9 THEN
            SET result = '第三季度';
        WHEN month >= 10 AND month <= 12 THEN
            SET result = '第四季度';
    END CASE;

    SELECT CONCAT('您输入的月份为 :', month , ' , 该月份为 : ' , result) AS content ;

END $

DELIMITER ;

4、循环

4.2、while

WHILE search_condition DO
    statement_list
END WHILE;

WHILE search_condition DO: 开始一个 WHILE 循环，search_condition 是一个布尔表达式，表示循环的条件。只要 search_condition 为真（TRUE），循环体 statement_list 就会不断执行。

statement_list: 循环体中要执行的一系列 SQL 语句。这些语句会在每次循环迭代时执行。

END WHILE;: 结束 WHILE 循环。

需求：计算从1加到n的值

DELIMITER $

CREATE PROCEDURE pro_test(n INT)
BEGIN
    DECLARE total INT DEFAULT 0;
    DECLARE num INT DEFAULT 1;

    WHILE num <= n DO
        SET total = total + num;
        SET num = num + 1;
    END WHILE;

    SELECT total;
    
END $

DELIMITER ;

4.3、for

Mysql 存储过程不支持 FOR 循环，看看就行

FOR variable IN start_value..end_value DO
    -- SQL statements
END FOR;

variable：循环变量，用于在每次迭代时持有当前值。
start_value：循环的起始值。
end_value：循环的结束值。
DO：开始执行循环体内的 SQL 语句。
END FOR：标记循环结束。

DELIMITER $

CREATE PROCEDURE pro_test(n INT)
BEGIN
    DECLARE total INT DEFAULT 0;
    DECLARE i INT;

    FOR i IN 1..n DO
        SET total = total + i;
    END FOR;

    SELECT total;

END $

DELIMITER ;

4.4、repeat

REPEAT
    statement_list
    UNTIL search_condition
END REPEAT;

REPEAT: 开始一个循环。
statement_list: 循环中要执行的一系列语句。
UNTIL: 循环结束的条件。
search_condition: 结束循环的条件，直到此条件为真时，循环才会停止。
END REPEAT;: 结束循环的标志。

需求: 计算从1加到n的值

DELIMITER $

CREATE PROCEDURE pro_test(n INT)
BEGIN
    DECLARE total INT DEFAULT 0;

    REPEAT
        SET total = total + n;
        SET n = n - 1;
    UNTIL n = 0
    
    END REPEAT;

    SELECT total;
END$

DELIMITER ;

4.5、loop、leave

LOOP： 实现简单的循环，退出循环的条件需要使用其他的语句定义，通常可以使用 LEAVE 语句实现。

LEAVE： LEAVE 用于在循环（如 LOOP、REPEAT 或 WHILE）中提前退出。它通常与标签配合使用。

[begin_label:] LOOP
    statement_list
END LOOP [end_label]

begin_label: 可选的标签名称，用于标记循环的开始。
LOOP: 开始一个循环块。
statement_list: 循环中要执行的一系列语句。
END LOOP: 结束循环块的标志。
[end_label]: 可选的标签名称，用于标记循环的结束，与 begin_label: 配对。

示例：

DELIMITER $

CREATE PROCEDURE pro_test(n int)
BEGIN
	DECLARE total INT DEFAULT 0;

	ins: LOOP
		IF n <= 0 THEN
			LEAVE ins;
		END IF;

	SET total = total + n;
	SET n = n - 1;

	END LOOP ins;

	SELECT total;

END$

DELIMITER ;

ins: LOOP：开始一个名为 ins 的循环。
IF n <= 0 ：如果 n 小于或等于 0，退出 ins 循环。
END LOOP ins;：结束循环。
SELECT total;：返回计算得到的总和。

二、存储函数

一、概述

存储函数（Stored Function）是数据库管理系统（DBMS）中的一种预编译的程序，它在数据库中存储并能够被多次调用。与存储过程类似，存储函数用于执行特定的任务，但它的关键区别在于存储函数总是有一个返回值。

优点：

返回值：存储函数总是返回一个值，适合需要返回计算结果的场景。
简化查询：可以在 SQL 查询中直接调用存储函数，使查询更简洁。
提高性能：预编译的存储函数可以减少 SQL 解析时间，提升性能。

缺点：

功能限制：存储函数通常不能执行数据修改操作（如 INSERT、UPDATE、DELETE），只能进行查询和计算。
复杂度：将复杂的业务逻辑放在存储函数中可能增加数据库维护的复杂度。

总结：

存储函数：适合需要计算和查询，返回单一值的场景。使用简单，但不适合执行数据修改操作。
存储过程：适合复杂的数据操作和处理，可以执行修改操作，但调用和使用相对复杂。

二、语法

CREATE FUNCTION function_name([param type ... ])
RETURNS type
BEGIN
    ...
END;

FUNCTION：指定要创建的对象是一个存储函数。
function_name：这是新创建的函数的名称，用于在调用函数时引用它。
param：这是函数的参数名称。
type：这是参数的类型，例如 INT、VARCHAR 等。函数可以有一个或多个参数。
RETURNS：指示函数将返回一个值。
type：指定函数返回值的数据类型，例如 INT、VARCHAR 等。

示例：

DELIMITER $

CREATE FUNCTION add_numbers(a INT, b INT)

RETURNS INT

BEGIN

    DECLARE result INT;

    SET result = a + b;
    
    RETURN result;

END $

DELIMITER ;

使用 SELECT 语句来调用它并获取结果

SELECT add_numbers(5, 10) AS result;

使用 Navicat 完成：

重庆求内推

关注

10
点赞
踩
5

收藏

觉得还不错? 一键收藏
0
评论
Mysql-存储过程&存储函数

1、MySQL 存储过程是一种在 MySQL 数据库中存储的预编译 SQL 代码块，它可以接受参数并执行一系列 SQL 操作。2、存储过程通常用于封装和重用复杂的 SQL 逻辑，从而简化应用程序的代码，提升性能，并提供更高的安全性。3、减少数据在数据库和应用服务器之间的传输，提高数据处理的效率。4、存储过程没有返回值，但可以通过 OUT 参数来获取。优点：代码复用：存储过程允许重复使用相同的 SQL 逻辑，减少代码冗余。性能优化。
复制链接

扫一扫