MySQL存储过程详解

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

[[429020]]

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

什么是存储过程

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

关键词:可编程,特定功能,调用

创建存储过程

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

表customers

示例:

  1. CREATE PROCEDURE sp_customers(IN cusid INT
  2. BEGIN  
  3.   SELECT * FROM customers WHERE `客户ID`=cusid; 
  4. END

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

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

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

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

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

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

调用存储过程

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

CALL sp_name([参数])

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

  1. CALL sp_customers(1); 

解释:

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

结果为:

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

过程体

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

例如:

  1. BEGIN 
  2.   BEGIN 
  3.     BEGIN 
  4.       -- SQL代码; 
  5.     END 
  6.   END 
  7. END 

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

标签

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

  1. [label_name:] BEGIN 
  2.     [statement_list]  
  3. END [label_name] 

例如:

  1. label1: BEGIN 
  2.   label2: BEGIN 
  3.     label3: BEGIN 
  4.       --SQL代码;  
  5.     END label3 ; 
  6.   END label2; 
  7. END label1 

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

存储过程的参数

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

参数类型

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

IN输入参数

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

OUT输出参数

  1. CREATE PROCEDURE sp_customers_out(OUT cusname VARCHAR(20)) 
  2. BEGIN 
  3.   SELECT cusname; 
  4.   SELECT `姓名` INTO cusname FROM customers WHERE `客户ID`=1; 
  5.   SELECT cusname; 
  6. END 

调用上面的存储过程:

  1. CALL sp_customers_out(@cusname); 

结果为:

结果1

结果2

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

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

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

INOUT输入输出参数

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

  1. CREATE PROCEDURE sp_customers_inout(INOUT cusname VARCHAR(20)) 
  2. BEGIN 
  3.   SELECT cusname; 
  4.   SELECT `姓名` INTO cusname FROM customers WHERE `客户ID`=2; 
  5.   SELECT cusname; 
  6. END 

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

  1. SET @cusname='张三'
  2. CALL sp_customers_inout(@cusname); 

结果为:

结果1

结果2

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

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

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

以上就是三个参数的用法,建议:

 

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

 

责任编辑:武晓燕 来源: SQL数据库开发
相关推荐

2020-11-26 10:33:44

MySQL存储函数

2009-09-09 10:54:52

Linq存储过程返回

2009-07-08 17:17:16

JDBC调用存储过程

2018-09-28 14:28:28

MySQL存储过程

2010-10-09 17:08:15

MySQL存储过程

2020-11-02 13:24:49

MySQL数据库存储

2019-01-02 13:03:53

MySQL存储权限

2010-06-01 15:09:55

MySQL 存储过程

2018-04-18 09:18:44

数据库MySQL存储过程

2010-05-19 14:03:41

MySQL 存储过程

2010-10-08 16:55:44

MySql存储过程

2010-10-12 10:48:02

mysql存储过程

2010-10-09 16:26:59

mysql存储过程

2023-02-28 11:29:09

存储函数MySQL

2012-05-10 11:17:23

JavaSQL Server

2009-07-21 17:06:35

iBATIS.NET执

2009-01-19 08:59:04

PHP调用MySQL存储过程MySQLi扩展

2010-10-12 11:07:34

MySQL存储过程

2010-10-09 17:19:50

mysql存储过程

2016-08-31 14:01:31

MySQL存储数据库
点赞
收藏

51CTO技术栈公众号