Jack Frost

MySQL优化笔记(六)–存储过程和存储函数

当一个大型系统在建立时,会发现,很多的SQL操作是有重叠的,个别计算是相同的,比如:业务系统中,计算一张工单的计算方式。当遇到这些情况时,我们运用存储过程就是一个非常棒的优化啦。

本系列demo下载

(一)MySQL优化笔记(一)–库与表基本操作以及数据增删改

(二)MySQL优化笔记(二)–查找优化(1)(非索引设计)

(三)MySQL优化笔记(二)–查找优化(2)(外连接、多表联合查询以及查询注意点)

(四) MySQL优化笔记(三)–索引的使用、原理和设计优化

(五) MySQL优化笔记(四)–表的设计与优化(单表、多表)

(六)MySQL优化笔记(五)–数据库存储引擎

本文章结构:(1)存储过程概述;(2)存储过程使用、优化;(3)存储函数概述;(4)存储函数使用、优化。


一、存储过程概述:

(1)什么是存储过程?

是一组为了完成特定功能的SQL 语句集,经编译后存储在数据库。用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。

是SQL 语句和可选控制流语句的预编译集合,以一个名称存储并作为一个单元处理。存储过程存储在数据库内,可由应用程序通过一个调用执行,而且允许用户声明变量、有条件执行以及其它强大的编程功能。存储过程在创建时即在服务器上进行编译,所以执行起来比单个SQL语句快。

(2)存储过程优点:

1)存储过程只在创造时进行编译,以后每次执行存储过程都不需再重新编译,而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。

2)当对数据库进行复杂操作时(如对多个表进行Update,Insert,Query,Delete时),可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。

存储过程可以包含程序流、逻辑以及对数据库的查询。同时可以实体封装和隐藏了数据逻辑。

3)存储过程可以重复使用,可减少数据库开发人员的工作量。

4)安全性高,可设定只有某此用户才具有对指定存储过程的使用权。

比如一个表不直接允许用户直接访问,但要求允许用户访问和修改其中一个或多个字段,那就可以通过一个存储过程来实现并允许该用户使用该存储过程。

(3)存储过程缺点:

1)如果更改范围大到需要对输入存储过程的参数进行更改,或者要更改由其返回的数据,则您仍需要更新程序集中的代码以添加参数、更新 GetValue() 调用,等等,这时候估计比较繁琐了。

2)可移植性差。因为存储过程必须跟你的业务逻辑紧密结合才合理,导致它的迁移性很差。


二、 存储过程使用、优化:

(1)存储过程的使用

(一)存储过程创建:

[parameter]指定存储过程的参数列表。格式如下:

其中in表示输入参数,out表示输出参数,inout表示既可以输入也可以输出;param_name表示参数名称;type表示参数的类型。

同时,虽然创建语法就这么简单(中括号里为可选),但是实际上,我们有些规避风险的要求:(针对中括号部分)

####

DELIMITER : 在创建存储过程的时候一般都会用下面这句放在开头和结束,目的就是避免mysql把存储过程内部的”;”解释成结束符号,最后通过“DELIMITER ;”来告知存储过程结束。注意:“DELIMITER //”语句的作用是将MYSQL的结束符设置为//,因为MYSQL默认的语句结束符为分号;,为了避免与存储过程中SQL语句结束符相冲突,需要使用DELIMITER 改变存储过程的结束符,并以“END//”(也可其他形式)结束存储过程。

当使用DELIMITER命令时,应该避免使用反斜杠(\)字符,因为反斜杠是MYSQL的转义字符!!!

LANGUAGE SQL:用来说明语句部分是SQL语句,未来可能会支持其它类型的语句。

NOT] DETERMINISTIC:如果程序或线程总是对同样的输入参数产生同样的结果,则被认为它是“确定的”,否则就是“非确定”的。如果既没有给定DETERMINISTIC也没有给定NOT DETERMINISTIC,默认的就是NOT DETERMINISTIC(非确定的)CONTAINS SQL:表示子程序不包含读或写数据的语句。

NO SQL:表示子程序不包含SQL语句。

READS SQL DATA:表示子程序包含读数据的语句,但不包含写数据的语句。

MODIFIES SQL DATA:表示子程序包含写数据的语句。

SQL SECURITY DEFINER:表示执行存储过程中的程序是由创建该存储过程的用户的权限来执行。

SQL SECURITY INVOKER:表示执行存储过程中的程序是由调用该存储过程的用户的权限来执行。(例如上面的存储过程我写的是由调用该存储过程的用户的权限来执行,当前存储过程是用来查询Employee表,如果我当前执行存储过程的用户没有查询Employee表的权限那么就会返回权限不足的错误,如果换成DEFINER如果存储过程是由ROOT用户创建那么任何一个用户登入调用存储过程都可以执行,因为执行存储过程的权限变成了root)

COMMENT ‘string’:备注,和创建表的字段备注一样。

characteristic使用注意:

在编写存储过程和函数时建议明确指定上面characteristic部分的状态,特别是存在复制的环境中,如果创建函数不明确指定这些状态会报错,从一个非复制环境将带函数的数据库迁移到复制环境的机器上如果没有明确指定DETERMINISTIC, NO SQL, or READS SQL DATA该三个状态也会报错。

WITH内部:

RECOMPILE: 表明 SQL Server 不会缓存该过程的计划,该过程将在运行时重新编译。在使用非典型值或临时值而不希望覆盖缓存在内存中的执行计划时,请使用 RECOMPILE 选项。

ENCRYPTION: 表示 SQL Server 加密 syscomments 表中包含 CREATE PROCEDURE 语句文本的条目。使用 ENCRYPTION 可防止将过程作为 SQL Server 复制的一部分发布。 说明 在升级过程中,SQL Server 利用存储在 syscomments 中的加密注释来重新创建加密过程。

FOR REPLICATION :指定不能在订阅服务器上执行为复制创建的存储过程。.使用 FOR REPLICATION 选项创建的存储过程可用作存储过程筛选,且只能在复制过程中执行。本选项不能和 WITH RECOMPILE 选项一起使用。

BEGIN–END :指定过程要执行的操作。

sql_statement :过程中要包含的任意数目和类型的 Transact-SQL 语句。但有一些限制。

创建例子:多个应用例子:

1)只返回单一记录集的存储过程–没传参数的例子 :

2)没有输入输出的有返回值的存储过程:

3)无返回值–有传参数的例子:

4)存储过程参数:

这里写图片描述 这里写图片描述

5)存储过程返回值(OUT使用):

6)存储过程返回集、输入输出参数使用(inout):

这里写图片描述

(二) 修改存储过程函数语法

(三)删除存储过程函数语法:(ptest是存储过程名,同下)

(四)查看存储过程和函数:

分别查出的是:数据库名、存储过程名、PROCEDURE参数表示查询存储过程、修改时间、创建时间、安全类型、备注(注释)、字符集、表编码、数据库字符集

这里写图片描述

(五)查看存储过程和函数的创建语法:

分别查出的是:存储过程名、SQL模式、创建的语句、字符集、表编码、数据库编码

这里写图片描述

(六)查看存储过程和函数详细信息:

红色的可以看到存储过程的SQL以及存储过程的权限。

这里写图片描述

(七)执行存储过程和存储函数需要拥有EXECUTE权限

存储过程和存储函数都是存储在服务器端的SQL语句的集合,要使用这些已经定义好的存储过程和存储函数就必须要通过调用的方式来实现

存储过程是通过CALL语句来调用的。而存储函数的使用方法与MySQL内部函数的使用方法是一样的。

EXECUTE权限的信息存储在information_schema数据库下面的USER_PRIVILEGES表中

(八)存储过程流程控制函数:

1. IF语句:

语法如下:具体例子,上面都有使用。

2.CASE语句:

CASE语句也用来进行条件判断,其可以实现比IF语句更复杂的条件判断。语法如下:

CASE语句还有另一种形式。如下:

CASE例子:

也可写成下面这样:

这里的CASE语句和“控制流程函数”里描述的SQL CASE表达式的CASE语句有轻微不同。这里的CASE语句不能有ELSE NULL子句,并且用END CASE替代END来终止!!

3.LOOP语句:

LOOP语句可以使某些特定的语句重复执行,实现一个简单的循环。但是LOOP语句本身没有中途停止循环的语句,必须是遇到LEAVE语句等才能停止循环(也就成了死循环)。语法如下:

4.LEAVE语句:

LEAVE语句主要用于跳出循环控制。其语法如下:

5.ITERATE语句:

ITERATE语句也是用来跳出循环的语句。但是,ITERATE语句是跳出本次循环,然后直接进入下一次循环。ITERATE语句只可以出现在LOOP、REPEAT、WHILE语句内。语法如下:

注意:LEAVE语句和ITERATE语句都用来跳出循环语句。但是两者是不同的。

LEAVE语句是跳出整个循环,然后执行循环后面的程序。而ITERATE语句是跳出本次循环,然后进入下一次循环。

6.REPEAT语句:

REPEAT语句是有条件控制的循环语句。当满足特定条件时,就会跳出循环语句。语法如下:

7.WHILE语句:

WHILE语句也是有条件控制的循环语句。但WHILE语句和REPEAT语句是不一样的。WHILE语句是当满足条件时,执行循环内的语句。语法如下:

8.光标。(存储过程的demo在本段最下面)

查询语句可能查询出多条记录,在存储过程和函数中使用光标来逐条读取查询结果集中的记录。

光标作用:

光标的作用就是用于对查询数据库所返回的记录进行遍历,以便进行相应的操作。有以下属性:
a.光标是只读的,也就是不能更新它;
b.光标是不能滚动的,也就是只能在一个方向上进行遍历,不能在记录之间随意进退,不能跳过某些记录;
c.避免在已经打开光标的表上更新数据。

光标的使用包括声明光标、打开光标、使用光标和关闭光标。光标必须声明在处理程序之前,并且声明在变量和条件之后。

1. 声明光标:

MySQL中使用DECLARE关键字来声明光标。语法如下:

2. 打开光标:

MySQL中使用OPEN关键字来打开光标。语法如下:

3. 使用光标:

MySQL中使用FETCH关键字来使用光标。语法:

4. 关闭光标:

MySQL中使用CLOSE关键字来关闭光标。语法:

注意:MYSQL中,光标只能在存储过程和函数中使用!!

存储过程光标DEMO:将表test_cursor数据复制到test_cursor2。顺便给出SQL异常表

(2)存储过程的优化:

优化是方方面面的,这里即使罗列也很片面。我就罗列存储过程的开发要求规范吧。规范起来也是一种大优化。

(一)SQL优化。存储过程封装的SQL自身的优化;SQL优化详情请见本系列前几篇。

(二)操作前检查。在向表插入数据之前,要判断该条数据在表中是否已经存在了,如果存在,则不再插入数据。同理,在从表中查询数据之前,要先判断该条数据在表中是否存在,如果存在,才能从表中查找数据。

(三)结果标识规范化。不管向表插入数据的操作执行成功与否,都应该有一个标识值来表示执行的结果,这样也方便开发人员对程序流程的追踪和调试。比如,在每条leave语句之前,都应该有一个返回值,我们为此定义一个输出参数。

(四)列出具体的字段名。在以后的软件版本中,新增了字段,那么这条insert语句极有可能会报错。所以罗列出所需字段是规范化很重要的一点。

(五)失败返回。在执行insert语句之后,要用MySQL中自带的@error_count参数来判断插入数据是否成功,方便开发人员跟踪执行结果。比如:如果该参数的值不为0,表示插入失败,那么我们就用一个返回参数值来表示操作失败。

DEMO:

@error_count如果该参数的值不为0,表示插入失败,那么我们就用一个返回参数值来表示操作失败。

这里写图片描述

三、存储函数(自定义函数)概述:

(1)什么是存储函数??

封装一段sql代码,完成一种特定的功能,必须返回结果。其余特性基本跟存储过程相同。

(2)与存储过程的区别:

1) 存储函数有且只有一个返回值,而存储过程不能有返回值。就是说能不能使用return。(函数可返回返回值或者表对象,绝对不能返回结果集)

这里写图片描述

2) 函数只能有输入参数,而且不能带in, 而存储过程可以有多个in,out,inout参数。

3) 存储过程中的语句功能更强大,存储过程可以实现很复杂的业务逻辑,而函数有很多限制,如不能在函数中使用insert,update,delete,create等语句;存储函数只完成查询的工作,可接受输入参数并返回一个结果,也就是函数实现的功能针对性比较强。比如:工期计算、价格计算。

4)存储过程可以调用存储函数。但函数不能调用存储过程。

5)存储过程一般是作为一个独立的部分来执行(call调用)。而函数可以作为查询语句的一个部分来调用。

(3)语法:

创建:

流控制(Flow-of-control)语句(IF, CASE, WHILE, LOOP, WHILE, REPEAT, LEAVE,ITERATE)也是合法的.

变量声明(DECLARE)以及指派(SET)是合法的.

允许条件声明.

异常处理声明也是允许的.

但是在这里要记住函数有受限条件:就是不能返回结果集。不能返回结果集。(可以使用select、insert、update等…)

修改、删除和查看状态与存储过程语法基本一样。只不过把procedure替换成function而已。

本博主找到一份详细的MySQL默认函数集合

(4)DEMO:下载地址

(一)helloworld:

(二)带参数的函数,并且使用insert语句,同理update与delete

(三)带参数的函数,并使用查询语句,这样的返回是返回一个值–只针对单值返回

另一种针对单值返回的方式:把查询出来的用一个参数包装,然后再返回。

(四)针对多记录返回:

光标的使用,原理使用方式同上面存储过程中的描述。

四、存储函数使用、优化:

同上方第二点–存储过程使用、优化。只是有些语法不一样而已。


DEMO下载地址:戳此处

好了,MySQL优化笔记(六)–存储过程和存储函数讲完了,又是一篇MySQL优化笔记,这是积累的必经一步,我会继续出这个系列文章,分享经验给大家。欢迎在下面指出错误,共同学习!!你的点赞是对我最好的支持!!

更多内容,可以访问JackFrost的博客

码字很辛苦,转载请注明来自JackFrost《MySQL优化笔记(六)–存储过程和存储函数》

Leave a Reply

Your email address will not be published. Required fields are marked *