MySQL(基础篇)之存储过程

绝地灬酷狼 2023-10-17 01:27 19阅读 0赞

本文转载至[Mysql学习总结（11）——MySql存储过程与函数][Mysql_11_MySql]

摘要：存储过程和函数是在[数据库][Link 1]中定义一些SQL语句的集合，然后直接调用这些存储过程和函数来执行已经定义好的SQL语句。存储过程和函数可以避免开发人员重复的编写相同的SQL语句。而且，存储过程和函数是在[MySQL][Link 1]服务器中存储和执行的，可以减少客户端和服务器端的数据传输。

## 一、存储过程 ##

### 1.1、基本语法 ###

**\[sql\]** [view plain][] [copy][view plain]

1.  **CREATE** **PROCEDURE** sp\_name (\[proc\_parameter\[,...\]\]) 
2.  
3.  　　\[characteristic ...\] routine\_body

Sp\_name:存储过程的名称，默认在当前数据库中创建。这个名称应当尽量避免与MySQL的内置函数相同的名称  
  
 **Proc\_parameter:存储过程的参数列表  
格式\[IN|OUT|INOUT\]param\_name type  
Param\_name为参数名，type为参数的数据类型。多个参数彼此间用逗号分隔。输入参数、输出参数和输入/输出参数，分别用in/out/inout标识。参数的取名不要与数 据表的列名相同。**

Characteristic:存储过程的某些特征设定，分别介绍  
1 COMMENT’string’:用于对存储过程的描述，其中string为描述内容,comment为关键字。  
2 LANGUAGE SQL:指明编写这个存储过程的语言为SQL语言。这个选项可以不指定。  
3 DETERMINISTIC:表示存储过程对同样的输入参数产生相同的结果;NOT DETERMINISTIC，则表示会产生不确定的结果（默认）。

4 contains sql | no sql | reads sql data | modifies sql data Contains sql表示存储过程包含读或写数据的语句（默认）  
No sql表示不包含sql语句  
Reads sql data表示存储过程只包含读数据的语句  
Modifies sql data 表示存储过程只包含写数据的语句  
5 sql security:这个特征用来指定存储过程使用创建该存储过程的用户(definer)的许可来执行，还是使用调用者(invoker)的许可来执行。默认是definer

Routine\_body:存储过程的主体部分，包含了在过程调用的时候必须执行的sql语句。以begin开始，以end结束。如果存储过程体中只有一条sql语句,可以省略begin-end标志。

### 1.2、数据准备 ###

**\[sql\]** [view plain][] [copy][view plain]

1.  **CREATE** **TABLE**
2.   t\_user 
3.   ( 
4.   USER\_ID **INT** NOT NULL AUTO\_INCREMENT, 
5.   USER\_NAME **CHAR**(30) NOT NULL, 
6.   USER\_PASSWORD **CHAR**(10) NOT NULL, 
7.   USER\_EMAIL **CHAR**(30) NOT NULL, 
8.  **PRIMARY** **KEY** (USER\_ID), 
9.  **INDEX** IDX\_NAME (USER\_NAME) 
10.  ) 
11.  ENGINE=InnoDB **DEFAULT** CHARSET=utf8;

然后这是插入的一些数据：

![20150804144310735][]

### 1.3 IN、OUT、INOUT参数 ###

（1）、带IN的存储过程

**\[sql\]** [view plain][] [copy][view plain]

1.  //创建储存过程.cmd 中运行 
2.  **CREATE** **PROCEDURE** SP\_SEARCH(IN p\_name **CHAR**(20)) 
3.  **BEGIN**
4.  IF p\_name **is** null or p\_name='' **THEN**
5.  **SELECT** \* **FROM** t\_user; 
6.  **ELSE**
7.  **SELECT** \* **FROM** t\_user **WHERE** USER\_NAME LIKE p\_name; 
8.  **END** IF; 
9.  **END**

因为;分会冲突，所以要加delimiter //。将//设置为结束运行符号

如下：

![20150804195818649][]

调用：

**\[sql\]** [view plain][] [copy][view plain]

1.  //调用并输出结果 
2.  CALL SP\_SEARCH('林炳文')

结果

![20150804195922199][]

（2）、带OUT的存储过程

**\[sql\]** [view plain][] [copy][view plain]

1.  //带**OUT**返回的 
2.  **CREATE** **PROCEDURE** SP\_SEARCH2(IN p\_name **CHAR**(20),**OUT** p\_int **INT**) 
3.  **BEGIN**
4.  IF p\_name **is** null or p\_name='' **THEN**
5.  **SELECT** \* **FROM** t\_user; 
6.  **ELSE**
7.  **SELECT** \* **FROM** t\_user **WHERE** USER\_NAME LIKE p\_name; 
8.  **END** IF; 
9.  **SELECT** FOUND\_ROWS() **INTO** p\_int; 
10. **END**

调用输出：统计带林开头的人数

**\[sql\]** [view plain][] [copy][view plain]

1.  //调用并输出结果 
2.  CALL SP\_SEARCH2('林%',@p\_num); 
3.  **SELECT** @p\_num;

![20150804200248945][]

（3）、带INOUT的存储过程

**\[sql\]** [view plain][] [copy][view plain]

1.  //带INOUT的存储过程 
2.  **CREATE** **PROCEDURE** sp\_inout(INOUT p\_num **INT**) 
3.  **BEGIN**
4.  **SET** p\_num=p\_num\*10; 
5.  **END**
6.  //调用并输出结果 
7.  **SET** @p\_num=2; 
8.  call sp\_inout(@p\_num); 
9.  **SELECT** @p\_num;

输出结果：

![20150804202815528][]

### 1.4、存储过程体 ###

存储过程体中可以使用各种sql语句和过程式语句的组合，来封装数据库应用中复杂的业务逻辑和处理规则，以实现数据库应用的灵活编程。下面主要介绍几个用于构造存储过程体的常用语法元素。  
**1、局部变量**  
在存储过程体中可以声明局部变量，用来存储存储过程体中临时结果。

**\[sql\]** [view plain][] [copy][view plain]

1.  **DECLARE** var\_name\[,…\] type \[**DEFAULT** value\] 
2.  Var\_name:指定局部变量的名称 
3.  Type:用于声明局部变量的数据类型 
4.  **default**子句:用于为局部变量指定一个默认值。若没有指定，默认为null.

如：

**\[sql\]** [view plain][] [copy][view plain]

1.  **Declare** cid **int**(10);

使用说明：  
局部变量只能在存储过程体的begin…end语句块中声明。  
局部变量必须在存储过程体的开头处声明。  
局部变量的作用范围仅限于声明它的begin..end语句块，其他语句块中的语句不可以使用它。  
局部变量不同于用户变量，两者区别：局部变量声明时，在其前面没有使用@符号，并且它只能在begin..end语句块中使用；而用户变量在声明时，会在其名称前面使用@符号，同时已声明的用户变量存在于整个会话之中。  
2、set语句  
使用set语句为局部变量赋值

**\[sql\]** [view plain][] [copy][view plain]

1.  **Set** var\_name=expr 
2.  **Set** cid=910;

3、select … into 语句  
把选定列的值直接存储到局部变量中，语法格式

**\[sql\]** [view plain][] [copy][view plain]

1.  **Select** col\_name\[,…\] **into** var\_name\[,…\] table\_expr 
2.  Col\_name:用于指定列名 
3.  Var\_name:用于指定要赋值的变量名 
4.  Table\_expr:表示**select**语句中的**from**字句及后面的语法部分

说明:存储过程体中的select…into语句返回的结果集只能有一行数据。

**4、定义处理程序**  
是事先定义程序执行过程中可能遇到的问题。并且可以在处理程序中定义解决这些问题的办法。这种方式可以提前预测可能出现的问题，并提出解决方法。

**\[sql\]** [view plain][] [copy][view plain]

1.  **DECLARE** handler\_type HANDLER **FOR** condition\_value\[,…\] sp\_statement 
2.  handler\_type:**CONTINUE** | EXIT | UNDO 
3.  Condition\_value:Sqlwarning | not found | sqlexception

5、流程控制语句  
（1）条件判断语句  
If语句

**\[sql\]** [view plain][] [copy][view plain]

1.  If search\_condition **then** statement\_list 
2.  \[elseif search\_condition **then** statement\_list\]… 
3.  \[**else** statement\_list\] 
4.  **End** if

Search\_condition参数:条件判断语句  
Statement\_list参数:不同条件的执行语句

多重IF的存储过程实例

数据准备

学生表：

**\[sql\]** [view plain][] [copy][view plain]

1.  **CREATE** **TABLE**
2.   t\_student 
3.   ( 
4.   STU\_ID **INT** NOT NULL, 
5.   STU\_NAME **CHAR**(10) NOT NULL, 
6.   STU\_CLASS **INT** NOT NULL, 
7.   STU\_SEX **CHAR**(2) NOT NULL, 
8.   STU\_AGE **INT** NOT NULL, 
9.  **PRIMARY** **KEY** (STU\_ID) 
10.  ) 
11.  ENGINE=InnoDB **DEFAULT** CHARSET=utf8;

数据如下：

![20150804201955420][]

成绩表（STU\_ID是学生表是外键关系）：

**\[sql\]** [view plain][] [copy][view plain]

1.  **CREATE** **TABLE**
2.   t\_grade 
3.   ( 
4.   STU\_ID **INT** NOT NULL, 
5.   STU\_SCORE **INT** NOT NULL, 
6.  **FOREIGN** **KEY** (STU\_ID) **REFERENCES** t\_student (STU\_ID), 
7.  **INDEX** STU\_ID (STU\_ID) 
8.   ) 
9.   ENGINE=InnoDB **DEFAULT** CHARSET=utf8;

![20150804200507198][]

然后写一个存储过程：返回各个分数等级的人

**\[sql\]** [view plain][] [copy][view plain]

1.  //带多重IF的存储过程 
2.  **CREATE** **PROCEDURE** SP\_SCHOLARSHIP\_LEVEL(IN p\_level **char**(1)) 
3.  **BEGIN**
4.  IF p\_level ='A' **THEN**
5.  **SELECT** \* **FROM** t\_grade **WHERE** STU\_SCORE >=90; 
6.  ELSEIF p\_level ='B' **THEN**
7.  **SELECT** \* **FROM** t\_grade **WHERE** STU\_SCORE <90 AND STU\_SCORE>=80; 
8.  ELSEIF p\_level ='C' **THEN**
9.  **SELECT** \* **FROM** t\_grade **WHERE** STU\_SCORE <80 AND STU\_SCORE>=70; 
10. ELSEIF p\_level ='D' **THEN**
11. **SELECT** \* **FROM** t\_grade **WHERE** STU\_SCORE <60; 
12. **ELSE**
13. **SELECT** \* **FROM** t\_grade; 
14. **END** IF; 
15. **END**

调用过程：

**\[sql\]** [view plain][] [copy][view plain]

1.  //调用并输出结果 
2.  CALL SP\_SCHOLARSHIP\_LEVEL('A');

![20150804200709191][]

Case 语句  
表达形式1

**\[sql\]** [view plain][] [copy][view plain]

1.  Case case\_value 
2.  **When** when\_value **then** statement\_list 
3.  \[**When** when\_value **then** statement\_list\]… 
4.  \[**else** statement\_list\] 
5.  **End** case

表达形式2

**\[sql\]** [view plain][] [copy][view plain]

1.  Case
2.  **When** search\_condition **then** statement\_list 
3.  **End** case

使用范例

**\[sql\]** [view plain][] [copy][view plain]

1.  **CREATE** **PROCEDURE** SP\_SCHOLARSHIP\_LEVEL3(IN p\_level **char**(1)) 
2.  **BEGIN**
3.  **DECLARE** p\_num **int** **DEFAULT** 0; 
4.  CASE p\_level 
5.  **WHEN** 'A' **THEN**
6.  **SET** p\_num=90; 
7.  **WHEN** 'B' **THEN**
8.  **SET** p\_num=80; 
9.  **WHEN** 'C' **THEN**
10. **SET** p\_num=70; 
11. **WHEN** 'D' **THEN**
12. **SET** p\_num=60; 
13. **ELSE**
14. **SET** p\_num=0; 
15. **END** CASE; 
16. **SELECT** \* **FROM** t\_grade g, t\_student s **WHERE** g.STU\_ID=s.STU\_ID AND g.STU\_SCORE >= p\_num ; 
17. **END**

调用：

**\[sql\]** [view plain][] [copy][view plain]

1.  //调用并输出结果 
2.  CALL SP\_SCHOLARSHIP\_LEVEL3('d');

![20150804202119154][]

**(2)循环语句**  
While语句、repeat语句和loop语句。  
**While语句**

**\[sql\]** [view plain][] [copy][view plain]

1.  \[begin\_label:\] 
2.  while search\_condition do 
3.  Statement\_list 
4.  **End** while 
5.  \[end\_label\]

判断条件search\_condition是否为真,若为真,则执行statement\_list中的语句，然后再进行判断，如若仍然为真则继续循环，直至条件判断不为真时循环结束。  
使用范例

**\[sql\]** [view plain][] [copy][view plain]

1.  //带while的存储过程 
2.  **CREATE** **PROCEDURE** sp\_cal(IN p\_num **INT**,**OUT** p\_result **INT**) 
3.  **BEGIN**
4.  **SET** p\_result=1; 
5.   WHILE p\_num > 1 DO 
6.  **SET** p\_result = p\_num \* p\_result; 
7.  **SET** p\_num = p\_num-1; 
8.  **END** WHILE; 
9.  **END**
10. //调用并输出结果 
11. CALL sp\_cal(5,@result); 
12. **SELECT** @result;

输出结果：计算5！

![20150804202458869][]

**Repeat语句语法格式**

**\[sql\]** [view plain][] [copy][view plain]

1.  \[begin\_label:\] 
2.  repeat 
3.  Statement\_list 
4.  Until search\_condition 
5.  **End** repeat 
6.  \[end\_label\]

Repeat语句首先执行statement\_list中的语句，然后判断条件search\_condition是否为真，倘若为真，则结束循环，若不为真，继续循环。  
Repeat先执行后判断，while先判断后执行。  
使用范例：

**\[sql\]** [view plain][] [copy][view plain]

1.  //带repeat的存储过程 
2.  **CREATE** **PROCEDURE** sp\_cal2(IN p\_num **INT**,**OUT** p\_result **INT**) 
3.  **BEGIN**
4.  **SET** p\_result=1; 
5.   REPEAT 
6.  **SET** p\_result = p\_num \* p\_result; 
7.  **SET** p\_num = p\_num-1; 
8.   UNTIL p\_num<=1 
9.  **END** REPEAT; 
10. **END**
11. //调用并输出结果 
12. CALL sp\_cal2(5,@result); 
13. **SELECT** @result;

![20150804202458869][]

### 1.5、 调用存储过程 ###

**\[sql\]** [view plain][] [copy][view plain]

1.  Call sp\_name(\[parameter\[,…\]\]); 
2.  Sp\_name被调用存储过程的名称 
3.  Parameter:指定调用存储过程所要使用的参数。

### 1.6、 修改存储过程 ###

**\[sql\]** [view plain][] [copy][view plain]

1.  **Alter** **procedure** proc\_name\[characteristic…\]

只能修改存储过程的特征，如果要修改存储过程的内容，可以先删除该存储过程，然后再重新创建

### 1.7、 删除存储过程 ###

**\[sql\]** [view plain][] [copy][view plain]

1.  **Drop** **procedure** \[if exists\] sp\_name;

[Mysql_11_MySql]: http://blog.csdn.net/u012562943/article/details/50454017
[Link 1]: http://lib.csdn.net/base/14
[view plain]: http://blog.csdn.net/evankaka/article/details/47276339#
[20150804144310735]: https://img-blog.csdn.net/20150804144310735
[20150804195818649]: https://img-blog.csdn.net/20150804195818649
[20150804195922199]: https://img-blog.csdn.net/20150804195922199
[20150804200248945]: https://img-blog.csdn.net/20150804200248945
[20150804202815528]: https://img-blog.csdn.net/20150804202815528
[20150804201955420]: https://img-blog.csdn.net/20150804201955420
[20150804200507198]: https://img-blog.csdn.net/20150804200507198
[20150804200709191]: https://img-blog.csdn.net/20150804200709191
[20150804202119154]: https://img-blog.csdn.net/20150804202119154
[20150804202458869]: https://img-blog.csdn.net/20150804202458869