第九课:MySQL 存储过程和存储函数

一、存储过程

存储过程是一组为了完成某特定功能的SQL语句集。一个存储过程是一个可编程的函数,同时可以看做是在数据库编程中对面向对象方法的模拟,允许控制数据的访问方式

存储过程通常具有的一些优点:

可增强SQL语句的功能和灵活性

良好的封装性

高性能

减少网络流量

存储过程可作为一种安全机制来确保数据库的安全性和数据的完整性

二、创建存储过程

基本语法结构:

create

procedure sp_name([proc_parameter[...]])

[characteristic...] routine_boby;

其中proc_parameter的格式:

[in | out |inout] prarm_name type

其中type格式:

any valid mysql data type

characteristi的格式:

comment ‘string’

| language sql

| [not] deterministic

| {contains sql | no sql | reads sql data | modifies sql data}

| sql security {definer | invoker}

routine_boby的格式:

valid sql routine dtatement

sp_name:存储过程的名称。

proc_paramenter:存储过程的参数列表。prarm_name为参数名,type为参数类型。存储过程可以没有参数,也可以有1或多个参数。MySQL存储过程支持三种类型的参数,输入参数、输出参数和输入/输出参数,分别用IN、OUT和INOUT三个关键字标识。

characteristi:存储过程的某些特征设定

comment ‘string’:对存储过程进行描述,其中string为描述内容。

 language sql:指明编写此存储过程的语言为SQL语言

deterministic:表示存储过程对相同的输入参数产生相同的结果;not deterministic(默认)表示会产生不确定的结果

contains sql | no sql | reads sql data | modifies sql data:contains sql(默认)表示存储过程包含读或写数据的语句; no sql表示存储过程不包含SQL语句;reads sql data表示存储过程包含读数据的语句,但不含写数据的语句;modifies sql data表示存储过程包含写数据的语句

sql security:指定存储过程使用创建该存储过程的用户definer(默认)的许可来执行,还是使用调用者invoker的许可来执行

routine_boby:存储过程的主体部分(存储过程体)。包含在过程调用的时候必须执行的SQL语句。若存储过程体中有多条SQL语句,以关键词begin开始,end结束

使用delimiter命令,可以将MySQL语句的结束标志临时修改为其他符号,具体语法结构:

delimiter $$

$$;用户定义的结束符,避免使用反斜杠“\”字符

三、存储过程体

存储过程体可以使用各种SQL语句与过程式语句的组合,来封装数据表应用中复杂的业务逻辑和处理规则,以实现数据库应用的灵活编程

1、局部变量

局部变量,用于存储存储过程中的临时变量

基本语法结构有:

declare var_name [,...] type [default value];

2、set语句

使用set语句为局部变量赋值,基本语法:

set var_name = exrp[, var_name = expr]...

3、select...into语句

选定列的值直接存储到局部变量中,基本语法格式:

select col_name [,...] into var_name [,...] table_expr

col_name:指定列名

var_name:指定要赋值的变量名

table_expr:表示select语句中的from子句及后面的语法部分

返回的结果集只能有一行数据

4、流程控制语句

4.1、条件判断语句

条件判断语句有两种语句:

4.1.1、if-then-else语句

if search_condition then statement_list

[elseif search_condition then statement_list]...

[else statement_list]

end if

search_condition:指定判断条件

statement_list:表示包含一条或多条的SQL语句

只有判断条件search_condition为真,才执行相应的SQL语句

4.1.2、case语句

case case_value

when when_value then statement_list

[when when_value then statement_list]...

[else statement_list]

end case

或者

case

when search_condition then statement_list

[when search_condition then statement_list]...

[else statement_list]

end case

case_value:用于指定要被判断的值或表达式,每一个when语句中的参数when_value与case_value进行比较值,比较的结果为真,则执行对应的statement_list中的SQL语句,若每一个都不为真,则执行else中的语句

search_condition:指定一个表达式。若表达式为真,则执行对应的statement_list中的SQL语句,若每一个都不为真,则执行else中的语句

4.2、循环语句

常用的循环语句有三种:

4.2.1、while语句

[begin_label:]while search_condition do statement_list end whlie [end_label]

先判断条件search_condition是否为真,为真则执行statement_list语句,然后再判断,若为真则继续循环,直至不为真后结束

begin_label、end_label是while的标注,必须同名、成对出现

4.2.2、repeat语句

[begin_label:]repeat statement_list until search_condition end repeat [end_label]

先执行statement_list语句,然后判断条件search_condition是否为真,为真结束循环,不为真则继续循环

4.2.3、loop语句

[begin_label:]loop statement_list end loop [end_label]

允许重复执行某个特定语句或语句块,实现一个简单的循环结构,statement_list循环一直重复执行,直至循环使用leave语句退出

leave语句的语法格式为:leave label;

5、游标

游标是一个被select语句检索出来的结果集,在存储了游标后,应用程序或用户就可以根据需要滚动或浏览其中的数据

游标不是一条select语句,游标只能用于存储过程或存储函数中,可以定义多个游标,但在一个begin...end语句块中每个游标名必须唯一

游标的具体使用步骤:

5.1、声明游标

decare cursor_name cursor for select_statement

cursor_name:游标名称

select_statement:指定一个select语句,返回一行或多行数据。select语句中不能有into子句

5.2、打开游标

open cursor_name

一个游标可以被多次打开

5.3、读取数据

fetch cursor_name into var_name[, var_name]...

var_name:指定存放数据的变量名

fetch...into...语句与select...into...语句具有相同的意义

5.4、关闭游标

close cursor_name

四、调用存储过程

基本语法结构:

call sp_name([paramenter[,...]])

call sp_name[( )]

sp_name:指定被调用的存储过程的名称

paramenter:指定调用存储过程所要使用的参数

五、删除存储过程

基本语法结构:

drop procedure function [if exists] sp_name;

六、存储函数

存储函数不能拥有输出参数,其本身就是输出参数。可以直接对存储函数调用,且不需使用call语句。存储函数必须包含一条return语句

1、创建存储函数

基本语法结构:

create

function sp_name (func_paramenter[,...]])

returns type

routine_body

其中,func_paramenter格式为:

param_name type

type格式为:

any valid mysql data type

routine_body格式为:

valid sql routine statement

func_paramenter:指定存储函数的参数,这里的参数只有名称和类型,不能指定关键字in、out、inout

returns子句:声明存储函数返回值的数据类型

routine_body:存储函数的主体(存储函数体),所有在存储过程中使用的SQL语句在存储函数中同样适用

2、调用存储函数

基本语法结构:

select sp_name ([func_paramenter[,...]])

3、删除存储函数

基本语法结构:

drop function [if exists] sp_name;

陈双义博客

打赏 支付宝打赏 微信打赏
本文标题:第九课:MySQL 存储过程和存储函数
本文链接:https://www.chenshuangyi.com/post/598.html
作者授权:除特别说明外,本文由 陈双义 原创编译并授权 陈双义博客 - 互联网Power 刊载发布。
版权声明:本文使用「署名-非商业性使用-相同方式共享」创作共享协议,转载或使用请遵守署名协议。

为您推荐

干货 | 软件性能测试基本测试概念

干货 | 软件性能测试基本测试概念

一、性能测试的目的1、 评估当前系统2、 寻找瓶颈3、 预测未来性能二、性能测试的前提:接...

  第十二课:MySQL 数据库的应用编程

第十二课:MySQL 数据库的应用编程

一、使用PHP进行MySQL数据库应用编程1、建立与MySQL数据库服务器的连接1.1、使用函数mysql_connec...

2019-03-21 标签:MySQL数据库软件测试
第十一课:MySQL 数据库备份与恢复

第十一课:MySQL 数据库备份与恢复

一、MySQL数据库备份与恢复1、使用SQL语句备份和恢复表数据1.1、select into...outfile语句导...

2019-03-21 标签:MySQL数据库软件测试
第十课:MySQL 访问控制与安全管理

第十课:MySQL 访问控制与安全管理

一、用户账户管理1、创建账户基本语法结构:create user user_specification[,us...

2019-03-20 标签:MySQL数据库软件测试
第八课:MySQL 事件

第八课:MySQL 事件

一、事件事件和触发器相似,都是在某些事情发生的时候启动,事件也叫作临时触发器事件基于特定时间周期触发来执行某些任务,而触...

2019-03-05 标签:MySQL数据库软件测试
第七课:MySQL 触发器

第七课:MySQL 触发器

一、触发器触发器是一个被指定关联到一个表的数据库对象。触发器用于保障数据库中数据的完整性,以及多个表之间数的一致性。触发...

2019-03-05 标签:MySQL数据库软件测试
第六课:MySQL 视图

第六课:MySQL 视图

一、视图视图是从一个或多个表或者视图中导出的表,也包含一系列带有名称的数据列和若干条数据行视图不同于数据库真实存在的表:...

2019-03-05 标签:MySQL数据库软件测试
第五课:MySQL 索引

第五课:MySQL 索引

一、索引数据表查询操作主要的两种搜索方式:一种全表扫描、检索,另一种利用数据表上建立的索引进行扫描MySQL主要的几种索...

2019-03-05 标签:MySQL数据库软件测试

发表评论

«   2019年7月   »
1234567
891011121314
15161718192021
22232425262728
293031
云服务器
轻量应用
站点信息
  • 文章总数:953
  • 页面总数:10
  • 分类总数:19
  • 标签总数:1542
  • 评论总数:153
  • 浏览总数:433035

当前非电脑浏览器正常宽度,请使用移动设备访问本站!