MySQL 存储过程与存储函数

拼搏现实的明天。 2023-09-28 08:48 113阅读 0赞

####  ####

*  初识存储过程
 *  存储过程语法
 *  存储过程调用
 *  存储函数的使用
 *   *  语法
     *  函数的调用
     *  对比存储函数和存储过程

文章是看尚硅谷的MySQL所写的笔记

## 初识存储过程 ##

*  理解:
    
     *  含义: 存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集(这些SQL语句已经编译过了)，它存储在数据库中，一次编译后永久有效，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。
     *  执行过程:存储过程预先存储在MySQL服务器，当需要执行的时候，客户端向服务器端发送执行的命令，服务器端就可以把预先存储好的这一系列sql语句全部执行。
 *  好处
    
     *  简化操作，提高sql语句复用性
     *  减少网络传输量(客户端不需要把所有的SQL语句通过网络发送给服务器端)
     *  减少sql语句暴露在网上的风险，提高数据查询的安全性

存储过程和视图与函数的对比

> 存储过程可以直接操作底层数据表，视图是虚拟表，存储过程一旦创建出来，我们直接通过存储过程名调用就可以了，就像用函数一样，相对于函数，存储过程是没有返回值的

接下来看看存储过程的分类，现在不懂没关系，等文章后面内容看完再回过来看，其实分类和函数的分类差不多，就是根据有无参数和有无返回值来划分。

*  存储过程的分类  
    存储过程的参数类型可以是IN,OUT,INOUT，根据这样可以分类
    
     *  ①没有参数(无参无返回)
     *  ②仅仅带IN类型(有参数无返回)
     *  ③仅仅带OUT类型(无参数有返回)
     *  ④既有IN，又有OUT(有参有返回)
     *  ⑤带INOUT(有参有返回)

IN,OUT,INOUT都可以在一个存储过程中带多个

## 存储过程语法 ##

CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名 参数类型,...)
    [characteristics ...]
    BEGIN
    	存储过程体
    
    END

类似于Java的语法

修饰符 返回类型 方法名(参数类型 参数名,...){
        
    
    	方法体;
    }

接下来对参数类型前面的IN,OUT,INOUT进行说明

*  IN:当前参数是输入参数，如果没有定义参数的话，默认就是参数类型是IN
 *  OUT:输出参数
 *  INOUT:可以表示为输入参数，也可以是输出参数

characteristics表示创建存储过程时指定的对存储过程的约束条件，其取值信息如下：

LANGUAGE SQL
    | [NOT] DETERMINISTIC
    | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
    | SQL SECURITY { DEFINER | INVOKER }
    | COMMENT 'string'

*  `LANGUAGE SQL`：说明存储过程执行体是由SQL语句组成的，当前系统支持的语言为SQL。
 *  `[NOT] DETERMINISTIC`：指明存储过程执行的结果是否确定。DETERMINISTIC表示结果是确定的。每次执行存储过程时，相同的输入会得到相同的输出。NOT DETERMINISTIC表示结果是不确定的，相同的输入可能得到不同的输出。如果没有指定任意一个值，默认为NOT DETERMINISTIC。
 *  `{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }`：指明子程序使用SQL语句的限制。
    
     *  CONTAINS SQL表示当前存储过程的子程序包含SQL语句，但是并不包含读写数据的SQL语句；
     *  NO SQL表示当前存储过程的子程序中不包含任何SQL语句；
     *  READS SQL DATA表示当前存储过程的子程序中包含读数据的SQL语句；
     *  MODIFIES SQL DATA表示当前存储过程的子程序中包含写数据的SQL语句。
     *  默认情况下，系统会指定为CONTAINS SQL。
 *  `SQL SECURITY { DEFINER | INVOKER }`：执行当前存储过程的权限，即指明哪些用户能够执行当前存储过程。
    
     *  `DEFINER`表示只有当前存储过程的创建者或者定义者才能执行当前存储过程；
     *  `INVOKER`表示拥有当前存储过程的访问权限的用户能够执行当前存储过程。
     *  如果没有设置相关的值，则MySQL默认指定值为DEFINER。
 *  `COMMENT 'string'`：注释信息，可以用来描述存储过程。

需要注意的地方:

*  mysql默认以；作为语句结束的标识
 *  为了避免与存储过程中SQL语句结束符相冲突，需要使用DELIMITER改变存储过程的结束符。
 *  存储过程定义完毕之后再使用“DELIMITER ;”恢复默认结束符。DELIMITER也可以指定其他符号作为结束符。

DELIMITER $
    
    CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名  参数类型,...)
    [characteristics ...]
    BEGIN
    	sql语句1;
    	sql语句2;
    
    END $

## 存储过程调用 ##

准备工作

-- 准备工作，创建新数据库
     create database  db15;
    use db15;
    create table emps
    as select * from atguigudb.employees;
    create table departments
    as
        select * from atguigudb.departments;
    select * from emps;

无参无返回

存储过程的调用，用call+存储过程名![在这里插入图片描述][b676887292934487b6d1fab9c192aadb.png]  
OUT类型![在这里插入图片描述][d069faf703354499ba9f9f319307da83.png]![在这里插入图片描述][c087607c4ba94d6f91e384f66aadd371.png]  
![在这里插入图片描述][66fdf707e42041dab83c32d86058508f.png]  
IN类型  
![在这里插入图片描述][43af5ed2ff9a420fbc2db820e8141e44.png]  
带IN和OUT

![在这里插入图片描述][509dc4cba87b4e06acd5ca22af09a92b.png]

![在这里插入图片描述][c44aafbbb3ba488bb4ade1e775d08725.png]  
带INOUT  
![在这里插入图片描述][0c885a30f74c4c0ba9d9523537f17761.png]  
![在这里插入图片描述][4c234c99af1242f0803382352df0a924.png]

## 存储函数的使用 ##

### 语法 ###

CREATE FUNCTION 函数名(参数名 参数类型,...) 
    RETURNS 返回值类型
    [characteristics ...]
    BEGIN
    	函数体   #函数体中肯定有 RETURN 语句
    
    END

说明：

*  1、参数列表：指定参数为IN、OUT或INOUT只对PROCEDURE是合法的，FUNCTION中总是默认为IN参数。
 *  2、RETURNS type 语句表示函数返回数据的类型；

RETURNS子句只能对FUNCTION做指定，对函数而言这是`强制`的。它用来指定函数的返回类型，而且函数体必须包含一个`RETURN value`语句。

*  3、characteristic 创建函数时指定的对函数的约束。取值与创建存储过程时相同，这里不再赘述。
 *  4、函数体也可以用BEGIN…END来表示SQL代码的开始和结束。如果函数体只有一条语句，也可以省略BEGIN…END。

### 函数的调用 ###

SELECT 函数名(实参列表)

![在这里插入图片描述][c9d3a25917f341f68638f02bdcb0e467.png]  
**注意：**

若在创建存储函数中报错“`you might want to use the less safe log_bin_trust_function_creators variable`”，有两种处理方法：

*  方式1：加上必要的函数特性“\[NOT\] DETERMINISTIC”和“\{CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA\}”
 *  方式2：

SET GLOBAL log_bin_trust_function_creators = 1;

### 对比存储函数和存储过程 ###

<table> 
 <thead> 
  <tr> 
   <th></th> 
   <th>关键字</th> 
   <th>调用语法</th> 
   <th>返回值</th> 
   <th>应用场景</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>存储过程</td> 
   <td>PROCEDURE</td> 
   <td>CALL 存储过程()</td> 
   <td>理解为有0个或多个</td> 
   <td>一般用于更新</td> 
  </tr> 
  <tr> 
   <td>存储函数</td> 
   <td>FUNCTION</td> 
   <td>SELECT 函数()</td> 
   <td>只能是一个</td> 
   <td>一般用于查询结果为一个值并返回时</td> 
  </tr> 
 </tbody> 
</table>

此外，**存储函数可以放在查询语句中使用，存储过程不行**。反之，存储过程的功能更加强大，包括能够执行对表的操作（比如创建表，删除表等）和事务操作，这些功能是存储函数不具备的。

[b676887292934487b6d1fab9c192aadb.png]: https://img-blog.csdnimg.cn/b676887292934487b6d1fab9c192aadb.png
[d069faf703354499ba9f9f319307da83.png]: https://img-blog.csdnimg.cn/d069faf703354499ba9f9f319307da83.png
[c087607c4ba94d6f91e384f66aadd371.png]: https://img-blog.csdnimg.cn/c087607c4ba94d6f91e384f66aadd371.png
[66fdf707e42041dab83c32d86058508f.png]: https://img-blog.csdnimg.cn/66fdf707e42041dab83c32d86058508f.png
[43af5ed2ff9a420fbc2db820e8141e44.png]: https://img-blog.csdnimg.cn/43af5ed2ff9a420fbc2db820e8141e44.png
[509dc4cba87b4e06acd5ca22af09a92b.png]: https://img-blog.csdnimg.cn/509dc4cba87b4e06acd5ca22af09a92b.png
[c44aafbbb3ba488bb4ade1e775d08725.png]: https://img-blog.csdnimg.cn/c44aafbbb3ba488bb4ade1e775d08725.png
[0c885a30f74c4c0ba9d9523537f17761.png]: https://img-blog.csdnimg.cn/0c885a30f74c4c0ba9d9523537f17761.png
[4c234c99af1242f0803382352df0a924.png]: https://img-blog.csdnimg.cn/4c234c99af1242f0803382352df0a924.png
[c9d3a25917f341f68638f02bdcb0e467.png]: https://img-blog.csdnimg.cn/c9d3a25917f341f68638f02bdcb0e467.png