MySQL 存储过程与存储函数

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

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

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

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

  • 存储过程的分类 存储过程的参数类型可以是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+存储过程名img OUT类型imgimg img IN类型 img 带IN和OUT

img

img 带INOUT img img

语法

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

img 注意:

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

对比存储函数和存储过程

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

本文为互联网自动采集或经作者授权后发布,本文观点不代表立场,若侵权下架请联系我们删帖处理!文章出自:https://zengyihong.blog.csdn.net/article/details/125081737
-- 展开阅读全文 --
安全面试之XSS(跨站脚本攻击)
« 上一篇 07-24

发表评论

成为第一个评论的人

热门文章

标签TAG

最近回复