MySQL-笔记-09.存储过程及触发器的使用

目录

9.1 存储过程（Stored Procedure）

9.1.1 存储过程的优点

9.1.2 存储过程的创建和执行

1. 创建存储过程

 2. 调用存储过程

 3. 查看存储过程

9.1.3 存储过程的参数

9.2 触发器（trigger）

9.2.1 创建触发器

9.2.2 查看触发器

 1）使用SHOW TRIGGERS语句

 2）使用SHOW CREATE语句

 3）使用information_schema.triggers表

9.2.3 删除触发器

9.3 异常处理

1. 定义处理程序     

2. 处理方式         

3.错误类型        

9.4 游标（Cursor）

9.4.1 声明游标

9.4.2 打开游标

9.4.3 使用游标（提取数据）

9.4.4 关闭游标

9.4.5 游标错误处理程序

9.1 存储过程（Stored Procedure）

        存储过程

        是一组完成特定功能的SQL语言代码段，经编译后存储在数据库中，可被触发器、其他存储过程、程序设计语言所调用。

        1）定义存储过程时，需指定其名称，用户名称并给出参数（如果该存储过程带有参数）调用执行指定的存储过程。

        2）存储过程的功能由其过程体中的代码来决定，过程体“BEGIN…END”语句指定，其功能代码写在该语句范围内。

9.1.1 存储过程的优点

1）减少网络流量。

        调用存储过程时，网络中传送的只是调用语句，而不需要在网络中传送存储过程中的SQL语句代码。

2）可作为一种安全机制来利用。

        只有受权的用户才具有对指定存储过程的使用权。

3）代码重复调用。

        存储过程经编译后被存储在其隶属的数据库中，可以被多次调用。

4）增强了SQL语言的功能和灵活性。

        过程体可通过流程控制语句对SQL语句的执行进行流程控制，能完成较复杂的运算。

5）实现更快的执行速度。

        存储过程创建时，MySQL就对其编译、分析和优化，并且给出最终被存储在系统表中的执行计划；且首次执行后，存储在服务器的内存中，可多次执行。

9.1.2 存储过程的创建和执行

1. 创建存储过程

MySQL中使用CREATE PROCEDURE语句创建存储过程，其基本语法格式如下：

CREATE PROCEDURE proc_name ( [ proc_parameter [ , … ] ] )
        routine_body


存储过程可以没有参数，也可以有一个或多个参数。其形式如下：
[ IN | OUT | INOUT ] param_name type

 【例4.27】创建一个不带参数的存储过程P0，从学生表、选课表中返回每位学生选修课程的平均分。

DROP PROCEDURE IF EXISTS P0;
DELIMITER @@
CREATE PROCEDURE P0( ) 
BEGIN
	SELECT sno, AVG(grade) AS AvgGrade
	FROM  score
	GROUP BY sno;
END @@

 2. 调用存储过程

        存储过程创建成功后，可以使用CALL语句来调用它，其基本语法格式如下：

CALL proc_name ( [ parameter [ , … ] ] )  

 说明：

        当调用没有参数的存储过程时，使用CALL proc_name()与使用CALL proc_name是相同的。

【例4.28】执行前面所创建的存储过程P0。

DELIMITER ;
CALL P0( );

 3. 查看存储过程

        成功创建存储过程后，可以使用SHOW STATUS语句或SHOW CREATE语句来查看存储过程，还可以直接从系统数据库information_schema的routines表中查询。

（1）使用SHOW STATUS语句

        使用SHOW STATUS语句可以查看存储过程和存储函数的状态，其基本语法格式如下：

SHOW PROCEDURE  STATUS [ LIKE 'pattern' ]
-- 用来匹配存储过程或存储函数的名称。

 （2）使用SHOW CREATE语句

        使用SHOW CREATE语句可以查看存储过程和存储函数的详细定义，其基本语法格式如下：

SHOW CREATE  PROCEDURE sp_name
-- 指定要查询的存储过程或存储函数的名称。

（3）使用information_schema.routines表

        routines表中存储了所有存储过程和存储函数的信息，查询语句如下： 

SELECT * FROM information_schema.routines
WHERE ROUTINE_NAME = 'sp_name' 
-- 指定要查询的存储过程或存储函数的名称。

说明：如果不使用WHERE子句，将查询出所有的存储过程或存储函数。 

4. 删除存储过程

        是可以使用DROP PROCEDURE语句删除存储过程，其基本语法格式如下：

DROP PROCEDURE [ IF EXISTS ] proc_name
-- 指定要删除的存储过程的名称。需要注意，它后面没有参数列表，也没有括号。

9.1.3 存储过程的参数

         存储过程的参数可以有IN、OUT、INOUT三种传递类型，省略时默认为IN类型。

        IN表示输入参数，要求在调用存储过程时，必须为该参数传入一个确定的值。