关于MySQL的存储过程与存储函数_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+存储过程名

关于MySQL的存储过程与存储函数
OUT类型

IN类型

带IN和OUT

关于MySQL的存储过程与存储函数

关于MySQL的存储过程与存储函数
带INOUT

存储函数的使用

语法

				?

									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 函数名(实参列表)

关于MySQL的存储过程与存储函数

注意：

若在创建存储函数中报错“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;

对比存储函数和存储过程

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