MySQL存储过程详解

SQL数据库开发 2021-10-15

218

点击关注上方“SQL数据库开发”，

设为“置顶或星标”，第一时间送达干货

SQL专栏

SQL基础知识第二版
SQL高级知识第二版

经常有小伙伴问我这个存储过程该如何写？作为过来人我刚开始也有这样的苦恼，今天就给大家说说这个存储过程该如何创建和使用。

什么是存储过程

存储过程是一组可编程的函数，是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。

关键词：可编程，特定功能，调用

创建存储过程

我们以表customers为例，通过传递客户ID的值来查询客户的具体信息：

表customers

示例：

CREATE PROCEDURE sp_customers(IN cusid INT)BEGIN   SELECT * FROM customers WHERE `客户ID`=cusid;END;
复制

上面这是一个比较简单的存储过程，主要的功能就是用来查询客户信息。这里我们先简单解释一下：

CREATE PROCEDURE：这是创建存储过程的关键字，属固定语法。

sp_customers：这是存储过程名称，当我们执行了该存储过程后，系统就会出现一个该名称的存储过程，可以自定义。

IN：这是输入参数的意思，当然也有输出参数关键字OUT，同时也可以不定义参数，直接让参数为空。

cusid INT：这是定义参数名和类型，这里我们定义了一个名为cusid，类型为INT的参数名。

BEGIN ... END ：这是存储过程过程体的固定语法，你需要执行的SQL功能就写在这中间。

调用存储过程

上面我们创建好了存储过程以后，就可以调用了。调用存储过程的语法很简单：

CALL sp_name([参数])

下面我们来调用上面的存储过程sp_customers

CALL sp_customers(1);
复制

解释：

上面的代码的意思就是将客户ID为1的数据，传递给存储过程sp_customers，通过CALL来调用该存储过程来执行。

结果为：

细心的小伙伴可能已经发现了，这不就是一个简单的WHERE查询语句吗？是的，刚开始使用存储过程时，其实不必把它神秘化，你越觉得它神秘越会觉得难以熟练使用。复杂的东西先简单化，方可更进一步掌握。

过程体

过程体即我们在调用时必须执行的SQL语句，上面的SELECT查询即为一个简单的过程体。
过程体包含DML、DDL语句，if-then-else和while-do语句、声明变量的declare语句等
过程体的格式上面也已经演示过，以BEGIN开始，以END结尾（可以嵌套）。

例如：

BEGIN  BEGIN    BEGIN      -- SQL代码;    END  ENDEND
复制

注意：每个嵌套块及其中的每条SQL语句，必须以分号(;)结束。表示过程体结束的BEGIN-END块(又叫做复合语句compound statement)，即END后面，则不需要分号。

标签

标签通常是与BEGIN-END一起使用，用来增强代码的可读性。语法为：

[label_name:] BEGIN
[statement_list]
END [label_name]

例如：

label1: BEGIN  label2: BEGIN    label3: BEGIN      --SQL代码;     END label3 ;  END label2;END label1
复制

该功能不常用，了解即可。

存储过程的参数

上面我们大致的说了一下存储过程参数定义，下面我们再详细给大家讲述参数该如何使用。

参数类型

IN输入参数：表示调用者向过程传入值（传入值可以是字面量或变量）
OUT输出参数：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）
INOUT输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）

IN输入参数

上面的示例就是一个输入参数的示例，这里不赘述。

OUT输出参数

CREATE PROCEDURE sp_customers_out(OUT cusname VARCHAR(20))BEGIN  SELECT cusname;  SELECT `姓名` INTO cusname FROM customers WHERE `客户ID`=1;  SELECT cusname;END
复制

调用上面的存储过程：

CALL sp_customers_out(@cusname);
复制

结果为：

结果1

结果2

上面我们定义了一个输出参数为cusname的参数（这里参数类型如果有长度必须给定长度）。

然后在过程体里面，我们输出了两次参数的结果，结果1为NULL，是因为我们的输出参数cusname还没有接收任何值，所以为NULL；

结果2里面有了客户姓名，是因为我们将客户ID为1的客户姓名传递给了输出参数cusname。

INOUT输入输出参数

这个不常见，但是也有使用，即同一个参数既为输入参数，也为输出参数，我们把上面的存储过程稍微修改一下就可以看出区别了。

CREATE PROCEDURE sp_customers_inout(INOUT cusname VARCHAR(20))BEGIN  SELECT cusname;  SELECT `姓名` INTO cusname FROM customers WHERE `客户ID`=2;  SELECT cusname;END
复制

调用上述存储过程之前我们先给定一个输入参数：张三

SET @cusname='张三';CALL sp_customers_inout(@cusname);
复制

结果为：

结果1

结果2

上面我们定义了一个输入输出参数为cusname的参数。然后在过程体里面，我们输出了两次参数的结果：

第一次我们将先定义好的“张三”（SET @cusname='张三'）传递给参数cusname，此时它为输入参数。进入过程体后首先输出结果1为“张三”，此时参数cusname为输出参数；

然后通过查询将客户ID为2的客户姓名再次传递给cusname，来改变它的值，此时它同样为输出参数，只是输出结果发生了改变。

以上就是三个参数的用法，建议：

需要输入值时使用IN参数；
需要返回值时使用OUT参数；
INOUT参数尽量少用。

最后给大家分享我写的SQL两件套：《SQL基础知识第二版》和《SQL高级知识第二版》的PDF电子版。里面有各个语法的解释、大量的实例讲解和批注等等，非常通俗易懂，方便大家跟着一起来实操。

有需要的读者可以下载学习，在下面的公众号「数据前线」(非本号)后台回复关键字：SQL，就行
数据前线

后台回复关键字：1024，获取一份精心整理的技术干货
后台回复关键字：进群，带你进入高手如云的交流群。

推荐阅读
终于有人把内卷和囚徒困境讲明白了
SQL常用脚本整理
图解 SQL 基础知识
真给 IT 人丢脸啊
校招黑名单
复制

mysql

文章转载自SQL数据库开发，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

MySQL存储过程详解

评论

相关阅读