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

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表示输入参数，要求在调用存储过程时，必须为该参数传入一个确定的值。

        OUT表示输出参数，要求在调用存储过程时，必须为该参数传入一个用户会话变量（全局变量），用于将存储过程运算中的结果带出到调用处使用。该种参数的功能是将值从存储过程中带出。

        INOUT表示输入输出参数，要求在调用存储过程时，必须为该参数传入一个有确定值的用户会话变量（全局变量），用于在存储过程运算中使用，同时，又可利用该参数将值从存储过程中带出。

【例4.29】创建带有两个输入参数的存储过程P1，输入班级名称和课程名称，检索该班级没有参加该课程考试的学生。

DROP PROCEDURE IF EXISTS P1;
DELIMITER @@
CREATE PROCEDURE P1(IN bjmc VARCHAR(20), IN kcmc VARCHAR(20))
BEGIN 
SELECT * FROM student WHERE clsno= 
(SELECT clsno FROM class WHERE clsname=bjmc)
    AND  sno NOT IN
 (SELECT sno FROM score WHERE cno IN 
              (SELECT cno FROM course WHERE cname=kcmc)); 
END @@

 【例4.30】创建带有一个输入参数和一个输出参数的存储过程P2，输入学生的学号，返回姓名。

DROP PROCEDURE IF EXISTS P2;
DELIMITER @@
CREATE PROCEDURE P2(IN xh CHAR(12), OUT xm VARCHAR(20) )
BEGIN
  SELECT sname into xm 
  FROM student
  WHERE sno = xh ;
END @@

 【例4.31】创建带有多个输入参数和多个输出参数的存储过程P3，输入学生的学号和课程号，返回姓名和成绩。

DROP PROCEDURE IF EXISTS P3;
DELIMITER @@
CREATE PROCEDURE P3 (IN xh CHAR (12), IN kch CHAR (10), OUT xm CHAR (20) , OUT cj INT)
BEGIN
  SELECT sname into xm FROM student WHERE sno = xh; 
  SELECT grade into cj  FROM score WHERE sno=xh AND cno=kch;
END @@

 【例4.32】创建带有两个输入/输出参数的存储过程P4，实现两个数的交换。

DROP PROCEDURE IF EXISTS P4;
CREATE PROCEDURE P4 (INOUT num1 int, INOUT num2 int)
BEGIN
  DECLARE var_temp int; 
  SET var_temp = num1;
  SET num1 = num2;
  SET num2 = var_temp;
END @@

 存储过程与函数一样，都是经过编译并保存在数据库中的SQL语句的集合，可以随时被调用。它们之间有如下几点区别：

• 1）函数有且只有一个返回值，而存储过程可以有多个返回值，也可以没有返回值；
• 2）函数只能有输入参数，而且不能带in，而存储过程可以有多个in、out、inout参数；
• 3）存储过程中的语句功能更强大，存储过程可以实现很复杂的业务逻辑，而函数有很多限制，如不能在函数中使用insert、update、delete、create等语句；
• 4）函数只完成查询的工作，可接受输入参数并返回一个结果，也就是函数实现的功能针对性比较强； 5）存储过程可以调用存储函数。但函数不能调用存储过程；
• 6）存储过程一般是作为一个独立的部分来执行(call调用)。而函数可以作为查询语句的一个部分来调用。

9.2 触发器

9.3 异常处理

9.4 游标