MySQL存储过程

MySQL存储过程

MySQL 存储过程（Stored Procedure）是一组预编译的 SQL 语句，它们存储在数据库中并且可以被重复调用。存储过程帮助简化复杂的 SQL 查询和提高代码复用性，也可以提升性能，因为数据库服务器可以优化存储过程中的 SQL 语句执行。

查看存储过程

mysql> select * from mysql.proc\G

创建存储过程

DELIMITER $$

CREATE PROCEDURE procedure_name (param1 datatype, param2 datatype, ...)
BEGIN
    -- SQL 语句
    SELECT * FROM table_name WHERE column_name = param1;
    -- 更多 SQL 语句
END $$

DELIMITER ;

DELIMITER $$ 和 DELIMITER ; 是为了更改 MySQL 命令分隔符，通常默认是 ;，为了能够在存储过程中使用 ; 作为 SQL 语句的结尾，需要临时更改命令分隔符。

CREATE PROCEDURE 用于创建存储过程。

procedure_name 是存储过程的名称。

(param1 datatype, param2 datatype, ...) 是存储过程的参数，参数可以是输入、输出或输入输出类型。

BEGIN ... END 之间是存储过程的 SQL 语句。

MySQL 存储过程支持三种类型的参数，即输入参数、输出参数和输入/输出参数，分别用 IN、OUT 和 INOUT 三个关键字标识。参数的取名不要与数据表的列名相同，否则尽管不会返回出错信息，但是存储过程的 SQL 语句会将参数名看作列名，从而引发不可预知的结果。

无参数的存储过程

mysql> create procedure test()
    -> begin
    -> select * from mysql.user;
    -> end$
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

调用无参数存储过程

mysql> call test();

In参数(传入参数)创建存储过程

输入参数是读取外部变量值，传递给存储过程，在存储过程中修改该参数的值不能被返回。

定义一个存储过程 getOneBook，当输入某书籍 id 后，可以调出对应书籍记录

mysql> delimiter $
mysql> create procedure getbook(in bookid int)
    -> begin
    -> select bid,bname,price from books.books where bid=bookid;
    -> end $
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call getbook(1);
+-----+-----------------------+-------+
| bid | bname                 | price |
+-----+-----------------------+-------+
|   1 | 网站制作直通车        |    34 |
+-----+-----------------------+-------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Out参数(传出参数)创建存储过程

可以在存储过程中更改OUT参数的值，并将其更改后的新值传递回调用程序。不接受外部传入的数据，仅返回计算之后的值。

out示例：

mysql> create procedure out123(out a int) 
begin 
select a;
set a = (select price from books.books where bid=1); 
select a; 
select * from books.books where price = a; 
end$
Query OK, 0 rows affected (0.00 sec)

mysql> call out123(@a)
    -> $
+------+
| a    |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

+------+
| a    |
+------+
|   34 |
+------+
1 row in set (0.00 sec)

+-----+-----------------------+---------+--------------------------+-------+------------+--------+------------+
| bId | bName                 | bTypeId | publishing               | price | pubDate    | author | ISBN       |
+-----+-----------------------+---------+--------------------------+-------+------------+--------+------------+
|   1 | 网站制作直通车        | 2       | 电脑爱好者杂志社         |    34 | 2004-10-01 | 苗壮   | 7505380796 |
+-----+-----------------------+---------+--------------------------+-------+------------+--------+------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

in+out示例：

 mysql> delimiter %
 mysql> create procedure get_bname(in book_id int,out out_id varchar(255))
     -> begin
     -> select bname into out_id from books where bid=book_id;
     #使用“select  into 变量”的形式对out 参数进行赋值
     -> end %
 mysql> delimiter ;
 mysql> call get_bname(4,@out_id);
 mysql> select @out_id;

inout参数（传入传出参数）创建存储过程

需要数据传入存储过程经过调用计算后，再传出返回值。可当做传入转出参数

定义变量

mysql> set @tto=123;
Query OK, 0 rows affected (0.00 sec)

mysql> select @tto;
+------+
| @tto |
+------+
|  123 |
+------+
1 row in set (0.00 sec)

存储过程变量的使用

局部变量（Local Variables）

局部变量是仅在存储过程或函数内部有效的变量。它们在存储过程的开始部分定义，通常用于存储计算中间结果。

DECLARE variable_name datatype [DEFAULT value];

variable_name：变量名。

datatype：数据类型，如 INT、VARCHAR、DATE 等。

DEFAULT value：可选，初始化变量的值。

局部变量例子

mysql> DELIMITER $$
mysql> create procedure test() 
begin declare id int;
set id = 1; 
select * from database_name.books where bid=id;
end$$
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ;
mysql> call test();
+-----+-----------------------+---------+--------------------------+-------+------------+--------+------------+
| bId | bName                 | bTypeId | publishing               | price | pubDate    | author | ISBN       |
+-----+-----------------------+---------+--------------------------+-------+------------+--------+------------+
|   1 | 网站制作直通车        | 2       | 电脑爱好者杂志社         |    34 | 2004-10-01 | 苗壮   | 7505380796 |
+-----+-----------------------+---------+--------------------------+-------+------------+--------+------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

2. 用户定义的变量（User-Defined Variables）

用户定义的变量是全局性的，可以在会话中使用。这些变量可以在 SQL 查询中跨多个语句传递和使用。它们不需要在存储过程中声明，也不需要使用 DECLARE 语句。

定义和使用用户定义的变量

SET @variable_name = value;
SELECT @variable_name;

在外面定义变量

mysql> set @id=1;
Query OK, 0 rows affected (0.00 sec)

mysql> select @id;l
+------+
| @id  |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

调用

mysql>  create procedure test() begin  select * from database_name.books where bid=@id; end$$
Query OK, 0 rows affected (0.00 sec)

mysql> call test();
+-----+-----------------------+---------+--------------------------+-------+------------+--------+------------+
| bId | bName                 | bTypeId | publishing               | price | pubDate    | author | ISBN       |
+-----+-----------------------+---------+--------------------------+-------+------------+--------+------------+
|   1 | 网站制作直通车        | 2       | 电脑爱好者杂志社         |    34 | 2004-10-01 | 苗壮   | 7505380796 |
+-----+-----------------------+---------+--------------------------+-------+------------+--------+------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

3. 系统变量

系统变量是 MySQL 服务器提供的变量，包含数据库服务器的配置信息和状态信息。你可以在存储过程中读取这些系统变量，但不能直接修改它们。系统变量通常以 @@ 开头。

mysql> select @@global.version
    -> ;
+------------------+
| @@global.version |
+------------------+
| 5.7.38           |
+------------------+
1 row in set (0.00 sec)

循环

1. WHILE 循环

WHILE 循环会在每次迭代前进行条件判断。只有当条件为真时，才会继续执行循环。

语法：
WHILE 条件 DO
    -- 循环体
END WHILE;
示例：使用 WHILE 循环输出 1 到 5 的数字
DELIMITER $$

CREATE PROCEDURE WHILEExample()
BEGIN
    DECLARE counter INT DEFAULT 1;  -- 声明并初始化计数器

    WHILE counter <= 5 DO  -- 当计数器小于等于5时继续循环
        SELECT counter;  -- 输出当前计数器值
        SET counter = counter + 1;  -- 增加计数器的值
    END WHILE;
END $$

DELIMITER ;

2. REPEAT 循环

REPEAT 循环与 WHILE 类似，但它首先执行循环体，然后检查条件。如果条件为真，则继续执行；如果为假，则退出循环。

语法：
REPEAT
    -- 循环体
UNTIL 条件
END REPEAT;
示例：使用 REPEAT 循环输出 1 到 5 的数字
DELIMITER $$

CREATE PROCEDURE REPEATExample()
BEGIN
    DECLARE counter INT DEFAULT 1;  -- 声明并初始化计数器

    REPEAT
        SELECT counter;  -- 输出当前计数器值
        SET counter = counter + 1;  -- 增加计数器的值
    UNTIL counter > 5  -- 当计数器大于5时退出循环
    END REPEAT;
END $$

DELIMITER ;

3.LOOP 循环

LOOP 是一个无限循环，必须在循环体内部手动使用 LEAVE 语句来退出循环。通常用于当你不清楚循环执行次数时使用。

语法：
[标签] LOOP
    -- 循环体
    IF 条件 THEN
        LEAVE 标签;  -- 跳出循环
    END IF;
END LOOP;
示例：使用 LOOP 循环输出 1 到 5 的数字
DELIMITER $$

CREATE PROCEDURE LOOPExample()
BEGIN
    DECLARE counter INT DEFAULT 1;  -- 声明并初始化计数器

    loop_label: LOOP  -- 标签可以帮助标识循环，方便使用 LEAVE 语句跳出循环
        SELECT counter;  -- 输出当前计数器值
        SET counter = counter + 1;  -- 增加计数器的值

        IF counter > 5 THEN  -- 如果计数器大于5，跳出循环
            LEAVE loop_label;
        END IF;
    END LOOP;
END $$

DELIMITER ;

循环控制语句：

  • LEAVE 标签;：跳出循环，结束循环。
  • ITERATE 标签;：跳过当前循环的剩余部分，进入下一次循环。
posted @   pro111  阅读(69)  评论(0)  编辑  收藏  举报
相关博文：
· MySQL语法进阶
· MySQL字符集和优化
· MySQL存储过程入门使用
· MySQL存储过程
· MySQL 存储过程浅谈
阅读排行：
· 全程不用写代码，我用AI程序员写了一个飞机大战
· DeepSeek 开源周回顾「GitHub 热点速览」
· MongoDB 8.0这个新功能碉堡了，比商业数据库还牛
· 记一次.NET内存居高不下排查解决与启示
· 白话解读 Dapr 1.15：你的「微服务管家」又秀新绝活了
点击右上角即可分享
微信分享提示