项目6总结：创建与使用存储过程

目录

【任务6.1】创建简单存储过程

MySQL的存储过程

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

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

2.调用存储过程

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

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

MySQL 带游标的存储过程

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

1.事务的ACID特性

2.事务控制语句

---

【任务6.1】创建简单存储过程

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 SAVEPOINT<保存点名称＞；

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