数据库总结（六）：创建与使用存储过程

（3）运算符的优先级。当一个复杂的表达式有多个运算符时，运算符优先级决定执行运算的先后次序。执行的次序有时会影响所得到的运算结果。MySQL运算符优先级如下图所示，当一个表达式中的两个运算符有相同的优先级时，根据它们在表达式中的位置，一般而言，一元运算符按从右到左（即右结合性）的顺序运算，二元运算符按从左到右（即左结合性）的顺序运算。

3.PL/SQL的控制语句

(1)begin...end语句。MySQL中begin...end语句用于将多个SQL语句组合成一个语句块，相当于一个整体，达到一起执行的目的。

begin...end语句的语法格式为：

begin

<语句1>;

<语句2>;

...

end

MySQL中允许嵌套使用begin...end语句。

（2）if...then...else语句。if...then...else语句用于进行条件判断，实现程序的选择结构。根据是否满足条件，将执行不同的语句，其语法格式为：

if <条件> then

<语句块1>

[ else

<语句块3>]

end if；

（3）case语句。case语句用于计算列表并返回多个可能结果表达式中的一个，可用于实现程序的多分支结构，虽然使用if...then...else语句也能够实现多分支结构，但是使用case语句的程序可读性更强。

在MySQL中，case语句有以下两种形式。

①简单case语句。简单case语句用于将某个表达式与一组简单表达式进行比较，以确定其返回值，其语法格式为：

case <测试表达式>

when <表达式1> then <SQL语句1>

when <表达式2> then <SQL语句2>

...

[ else <SQL语句n+1>]

end case；

简单case语句的执行过程是将“测试表达式”的值与各个when子句后面的“表达式n”进行比较，若相等，则执行对应的“SQL语句”，然后跳出case语句，不在执行后面的when子句；when子句中没有与“测试表达式”相等的“表达式n”，若指定了else子句，则执行else子句后面的“SQL语句n+1”。若没有指定else子句，则不执行case语句内任何一条SQL语句。

②搜索case语句。搜索case语句用于计算一组逻辑表达式以确定返回结果，其语法格式为：

case

when <逻辑表达式1> then <SQL语句1>

when <逻辑表达式2> then <SQL语句2>

...

[ else <SQL语句n+1>]

end case；

搜索case语句的执行过程是先计算第一个when子句后面的“逻辑表达式1”的值，若值为true，则case语句执行对应的“SQL语句1”；若值为false，则继续判断下面的when子句中的“逻辑表达式n”的值，若值为true则执行对应的“SQL语句n”。在所有“逻辑表达式”的值都为false的情况下，若指定了else子句，则执行else子句后面的“SQL语句n+1”。若没有指定else子句，则不执行case语句内任何一条SQL语句。

（4）while循环语句。while循环语句用于实现循环结构，是有条件的执行循环语句，当满足指定条件时执行循环体内的语句，其语法格式为：

[begin_label:]

while <条件> do

<语句块>

end while [end_label];

说明：先判断“条件”是否为true，若为true，则执行“语句块”，然后进行判断，若为true则继续循环，若为false则结束循环。“begin_label:”和“end_label”是while语句的标注，“begin_label:”与“end_label”同时存在，并且标注的名称必须相同。“begin_label:”和“end_label”通常都可以省略。

（5）loop循环语句。loop语句用于实现循环结构。但是loop语句本身没有停止循环的机制，必须遇到leave语句才能停止循环。

loop语句的语法格式为：

[begin_label:]

loop

<语句块>

end loop [end_label];

说明：loop语句允许语句块重复执行，实现一些简单的循环。在循环体内的语句一直重复执行直到循环被强迫终止，终止时通常使用leave语句。

（6）repeat循环语句。repeat循环语句是有条件控制的循环语句，当满足指定条件时，就会跳出循环语句，其语法格式为：

[begin_label:]

repeat

<语句块>

until <条件>

end repeat [end_label];

说明：先执行语句块，然后判断逻辑表达式的值是否为true，若为true则停止循环，若为false则继续循环repeat语句也可以被标注。

repeat语句与while语句的区别在于：repeat语句先执行语句，再进行条件判断；而while语句先进行条件判断，只有条件为true时才执行语句。

（7）leave语句。leave语句主要用于跳出循环控制，经常与循环一起使用，其语法格式为：

leave <标签>；

使用leave语句可以退出被标记的循环语句，标签是自定义的。

（8）iterate语句。iterate语句用于跳出本次循环，然后直接进入下一次循环，其语法格式为：

iterate <标签>；

iterate语句与leave语句都是用来跳出循环语句的，但两者的功能不一样。其中，leave语句用来跳出整个循环，然后执行循环语句后面的语句；而iterate语句是跳出本次循环，然后进行下一次循环。

4.MySQL的存储过程

（1）存储过程是一组为了完成特定功能的SQL语句块，经编译后存储在数据库中，用户通过指定存储过程的名称并给定参数（如果该存储过程带有参数）来调用并执行它，存储过程能重复使用，这样可以大大减少数据库开发人员的工作量。

存储过程主要有以下优点：

①执行效率高：存储过程编译后存储在数据库服务器端，可以直接调用从而提高了SQL语句的执行效率。

②灵活：存储过程可以用结构化语句编写，可以完成较复杂的判断和运算。

③数据独立：用户在程序中调用存储过程，存储过程能把数据同用户程序隔离开，其优点是当数据表结构变化时，可以随时修改存储过程，不用修改程序源代码。

④安全：存储过程可被作为一种安全机制来充分利用，系统管理员通过设置存储过程的访问权限，从而实现相应数据的访问权限限制，避免了用户对数据表的直接访问，保证了数据的安全。

⑤降低网络流量：当在客户机上调用该存储过程时，网络中传送的只是该调用语句，而不是这一切功能的全部代码，从而大大降低了网络负载。

（2）delimiter命令。delimiter命令用于更改MySQL语句的结束符，如将默认结束符“；”更改为“$$”，避免与SQL语句默认结束符相冲突，其语法格式为：

delimiter <自定义结束符>

delimiter $$在存储过程编写结束后恢复使用MySQL的默认结束符“‘；”，例如：

delimiter ；

（3）创建简单存储过程。创建存储过程的语法格式为：

create procedure 存储过程名（）

begin

<存储过程体>

end；

说明：存储过程名应符合MySQL的命名规则，避免使用与MySQL的内置函数相同的名称。

（4）查看存储过程。查看存储过程的语法格式为：

show procedure status [like<存储过程模糊名>]；

show procedure status proc601;

MySQL中存储过程的信息存储在information_schema数据库下的routines表中，也可以通过查询该数据表的记录来查询存储过程的信息，例如：

select * from information_schema.routines where routine_name="proc601";

(5）调用简单存储过程。存储过程创建完成后，可以在程序、触发器或其他存储过程中被调用，其语法格式为：

call 存储过程名（）；

（6）删除存储过程。在命令行中删除存储过程的语法格式为：

drop procedure <存储过程名>;

【任务6.2】创建带输入参数的存储过程

1.MySQL带输入参数的存储过程的创建

创建带输入参数的存储过程的语法格式为：

create procedure 存储过程名([形参列表名])

begin

<存储过程体>

end；

说明：存储过程可以不使用参数，也可以带一个或多个参数。

如果有多个参数，各个参数之间使用半角逗号分隔。参数的定义格式为：

[in] <参数名> <参数类型>

2.调用存储过程

存储过程创建完成后，可以在程序、触发器或其他存储过程中被调用，其语法格式为：

call 存储过程名 ([<实参列表>])；

说明：如果定义存储过程时使用了参数，那么调用该存储过程时，也要使用参数，并且参数个数和顺序必须一一对应。

【任务6.3】创建带输入和输出参数的存储过程

MySQL带输入和输出参数的存储过程创建的语法格式如下：

create procedure 存储过程名 ([形参列表])

begin

<存储过程体>

end;

说明：存储过程可以使用输入、输出和输入/输出参数。

参数的定义格式为：

[in|out|inout] < 参数名> <参数类型>

MySQL的存储过程支持3种类型的参数：输入类型、输出类型和输入/输出类型，关键字分别使用in、out、inout，省略参数传递类型默认为in。

【任务6.4】创建应用游标的存储过程

为了方便用户对结果中单条的记录进行访问、MySQL提供了游标这种特殊的访问机制，它具有在查询结果集中向前或向后浏览数据、处理结果集中数据的能力。

MySQL带游标的存储过程

（1）声明游标。其语法格式为：

declare <游标名> cursor for <select语句> ；

说明：游标名称必须符合MySQL标识符的命名规则，select返回一行或多行记录数据，但不能用into子句。

（2）打开游标。其语法格式为：

open <游标名>;

说明：打开一个已经声明过的游标。

（3）读取游标，其语法格式为：

fetch <游标名> into 变量名1[，变量名2]...

说明：在指定打开的游标读取一行数据并赋给对应的变量，并且游标指针下移，指向结果集的下一行。

（4）关闭游标。其语法格式为：

close <游标名> ;

说明：关闭一个之前打开的游标。

【任务6.5】创建与使用事务

在MySQL操作过程中，一般简单的业务逻辑或中小学程序不用考虑应用事务。但在比较复杂的情况下，如果可能出现多项并行业务逻辑，就必须保证命令执行的同步性。使执行序列中，有关联的语句执行能够全部操作成功或全部返回初始状态。

1.事务的acid特性

（1）原子性。一个事务（transaction）中的所有操作，要么全部执行，要么全部不执行。

（2）一致性。在事务开始之前和事务结束以后，数据库的完整性没有被破坏。

（3）隔离性。MySQL数据库允许多个并发事务，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。

（4）持久性。事务处理结束后，对数据的修改就是永久的。

2.事务控制语句

（1）开始事务。其语法格式为：

start transaction ；

说明：用于显示地启动一个事务。

（2）提交事务。其语法格式为：

commit ；

说明：用于提交事务，将事务对数据所做的修改进行保存。

（3）设置保存点。其语法格式为：

savepoint <保存点名称> ;

说明：用于在事务内设置保存点。

（4）撤销事务。其语法格式为：

rollback ; | rollback to savapoint <保存点名称> ;

说明：撤销事务又称为事务回滚，即事务被执行后，如果执行的SQL语句导致业务逻辑不符或数据库操作错误，rollback语句撤销事务中所有的执行语句。rollback to savapoint语句撤销事务中保存点之后的执行语句

END

柳彧

关注

1
点赞
踩
9

收藏

觉得还不错? 一键收藏
0
评论
数据库总结（六）：创建与使用存储过程

SQL语句没有流程控制，无法实现复杂的应用，PL/SQL语言（Procedural Lan-guage/SQL）是将结构化查询与数据库过程控制结合为一体的强大语言，PL/SQL不但支持更多的数据类型，拥有变量声明、赋值语句，而且有选择、循环等流程控制语句。系统开发过程中，经常会有同一个功能模块多次调用的情况，如果每次都编写代码会浪费大量的时间，为了解决这类问题，MySQL5.0开始引入存储过程。存储过程是一组为了完成特定功能的PL/SQL语句集，经编译后存储在数据库中，用户可以重复使用...
复制链接

扫一扫