目录
【任务6.1】创建简单存储过程
1.PL/SQL的变量
2.PL/SQL的运算符及表达式
3.PL/SQL的控制语句
4.MySQL的存储过程
【任务6.2】创建带输入参数的存储过程
1.MySQL带输入参数的存储过程的创建
2.调用存储过程
【任务6.3】创建带输入和输出参数的存储过程
【任务6.4】创建应用游标的存储过程
MySQL带游标的存储过程
【任务6.5】创建与使用事务
1.事务的acid特性
（1）原子性。一个事务（transaction）中的所有操作，要么全部执行，要么全部不执行。
2.事务控制语句
END
SQL语句没有流程控制，无法实现复杂的应用，PL/SQL语言（Procedural Lan-guage/SQL）是将结构化查询与数据库过程控制结合为一体的强大语言，PL/SQL不但支持更多的数据类型，拥有变量声明、赋值语句，而且有选择、循环等流程控制语句 。
系统开发过程中，经常会有同一个功能模块多次调用的情况，如果每次都编写代码会浪费大量的时间，为了解决这类问题，MySQL5.0开始引入存储过程。存储过程是一组为了完成特定功能的PL/SQL语句集，经编译后存储在数据库中，用户可以重复使用该存储过程，这样可以降低数据库开发人员的工作量。
游标是处理数据的一种方法，为了查看或处理结果集中的数据，游标提供了在结果集中一次一行浏览数据的能力。
事务是一个操作序列，这些操作要么都执行，要么都不执行，是一个不可分割的工作单元。
变量是指在程序运行过程中其值可以改变的量，包括用户变量、系统变量、和局部变量。
（1）用户变量。用户可以在PL/SQL中使用自己定义的变量，这样的变量称为用户变量。可以先在用户变量中保存一个数据，然后在以后的语句中引用该变量，这样就可以将数据从一条语句传递到另一条语句。用户变量在使用前必须定义和初始化，如果使用没有初始化的变量其值为null。
定义和初始化一个用户变量可以使用set语句，其语法格式为：
set@<变量名1>=<表达式1>[,@<变量名2>=<表达式2>,...];
说明：
①用户变量以“@”开始，形式为“@变量名”，以便将用户变量和字段名予以区别。变量名必须符合MySQL标识符的命名规则，即变量可以由当前字符集的字母、数字、“。”、“_”和“$”组成，默认字符集是cp1252（Latin1）。
②<表达式>可以为整数、实数、字符串或null值，例如：
set@name=“OPPO” ；
③一条定义语句中，可以同时定义多个用户变量，使用半角逗号分隔，例如：
set@name，@number，@unit；
（2）系统变量。MySQL可以访问许多系统变量和连接变量，当服务器运行许多变量可以动态更改。这样通常允许修改服务器操作而不需要停止并重启服务器。服务器维护两种变量，全局变量影响服务器整体操作，会话变量影响具体客户端连接的操作。
系统变量一般都已“@@”为前缀，如@@Version返回MySQL的版本。但某些特定的系统变量可以省略“@@”符合，如Current_Date、Current_Time和Current_User。
（3）局部变量。在语句块（begin到end之间）中定义的变量为局部变量，局部变量可以保存特定类型数据，其有效作用范围在存储过程和自定义函数的语句块中，在语句块结束以后，局部变量就失效了。
MySQL的局部变量必须先声明后使用。使用declare语句声明局部变量，其声明语法格式为：
declare <变量名称> <数据类型> [default<默认值>];
说明：
①“default”子句为变量指定默认值，若不指定则默认为null。
②变量名称必须符合MySQL标识符的命名规则，在局部变量前面不使用“@”符合。
例如：
declare unit char（2）；
（1)运算符。运算符用于执行程序代码运算，会针对一个以上操作数项目进行运算。MySQL中的运算符主要有以下类型。
①算术运算符。算术运算符用于对表达式执行数学运算，操作数可以是任何数值类型。
MySQL中的算术运算符有+（加）、-（减）、*（乘）、/（除）、%(取模)。
②赋值运算符。“=”是MySQL中的赋值运算符，可以将表达式的一个值赋给一个变量。
③比较运算符。比较运算符用于对两个表达式进行比较，数字以浮点值进行比较，字符串以不区分大小写的方式进行比较，若表达式成立则返回1，否则返回0。
MySQL中的比较运算符有=（等于）、>（大于）、<（小于）、>=（大于等于）、 <=(小于等于)、<>(不等于)、！=（不等于）、<=>（相等或都等于空）。
④逻辑运算符。逻辑运算符用于对某些条件进行测试，以返回其真假。
MySQL中的逻辑运算符有and（与）、or（或）、not（非）。
⑤位运算符。位运算符用于对两个表达式执行二进制操作。
MySQL中的位运算符有&（位与）、|（位或）、^（位异或）、~（位取反）、>>（位右移）、<<（位左移）。
⑥一元运算符。一元运算符对一个操作数执行运算，该操作数可以是任何一种数据类型。
MySQL中的一元运算符有+（正）、-（负）和~（位取反）。
（2）表达式。表达式是由操作数、运算符、分组符合（括号）和函数构成的组合，MySQL可以对表达式进行运算以获取结果，一个表达式通常可以得到一个值。
表达式的值同样具有字符类型、数值类型、日期时间类型等，根据表达式的值类型可分为字符型表达式、数值表达式和日期表达式。
（3）运算符的优先级。当一个复杂的表达式有多个运算符时，运算符优先级决定执行运算的先后次序。执行的次序有时会影响所得到的运算结果。MySQL运算符优先级如下图所示，当一个表达式中的两个运算符有相同的优先级时，根据它们在表达式中的位置，一般而言，一元运算符按从右到左（即右结合性）的顺序运算，二元运算符按从左到右（即左结合性）的顺序运算。
(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语句是跳出本次循环，然后进行下一次循环。
（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 <存储过程名>;
创建带输入参数的存储过程的语法格式为：
create procedure 存储过程名([形参列表名])
begin
<存储过程体>
end；
说明：存储过程可以不使用参数，也可以带一个或多个参数。
如果有多个参数，各个参数之间使用半角逗号分隔。参数的定义格式为：
[in] <参数名> <参数类型>
存储过程创建完成后，可以在程序、触发器或其他存储过程中被调用，其语法格式为：
call 存储过程名 ([<实参列表>])；
说明：如果定义存储过程时使用了参数，那么调用该存储过程时，也要使用参数，并且参数个数和顺序必须一一对应。
MySQL带输入和输出参数的存储过程创建的语法格式如下：
create procedure 存储过程名 ([形参列表])
begin
<存储过程体>
end;
说明：存储过程可以使用输入、输出和输入/输出参数。
参数的定义格式为：
[in|out|inout] < 参数名> <参数类型>
MySQL的存储过程支持3种类型的参数：输入类型、输出类型和输入/输出类型，关键字分别使用in、out、inout，省略参数传递类型默认为in。
为了方便用户对结果中单条的记录进行访问、MySQL提供了游标这种特殊的访问机制，它具有在查询结果集中向前或向后浏览数据、处理结果集中数据的能力。
（1）声明游标。其语法格式为：
declare <游标名> cursor for <select语句> ；
说明：游标名称必须符合MySQL标识符的命名规则，select返回一行或多行记录数据，但不能用into子句。
（2）打开游标。其语法格式为：
open <游标名>;
说明：打开一个已经声明过的游标。
（3）读取游标，其语法格式为：
fetch <游标名> into 变量名1[，变量名2]...
说明：在指定打开的游标读取一行数据并赋给对应的变量，并且游标指针下移，指向结果集的下一行。
（4）关闭游标。其语法格式为：
close <游标名> ;
说明：关闭一个之前打开的游标。
在MySQL操作过程中，一般简单的业务逻辑或中小学程序不用考虑应用事务。但在比较复杂的情况下，如果可能出现多项并行业务逻辑，就必须保证命令执行的同步性。使执行序列中，有关联的语句执行能够全部操作成功或全部返回初始状态。
（2）一致性。在事务开始之前和事务结束以后，数据库的完整性没有被破坏。
（3）隔离性。MySQL数据库允许多个并发事务，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。
（4）持久性。事务处理结束后，对数据的修改就是永久的。
（1）开始事务。其语法格式为：
start transaction ；
说明：用于显示地启动一个事务。
（2）提交事务。其语法格式为：
commit ；
说明：用于提交事务，将事务对数据所做的修改进行保存。
（3）设置保存点。其语法格式为：
savepoint <保存点名称> ;
说明：用于在事务内设置保存点。
（4）撤销事务。其语法格式为：
rollback ; | rollback to savapoint <保存点名称> ;
说明：撤销事务又称为事务回滚，即事务被执行后，如果执行的SQL语句导致业务逻辑不符或数据库操作错误，rollback语句撤销事务中所有的执行语句。rollback to savapoint语句撤销事务中保存点之后的执行语句
