过程式数据库对象

《过程式数据库对象》由会员分享，可在线阅读，更多相关《过程式数据库对象（58页珍藏版）》请在金锄头文库上搜索。

1、第7章 过程式数据库对象 7.1 存储过程 7.2 存储函数 7.3 触发器 7.4 事件7.1 存储过程使用存储过程的优点有： （1）存储过程在服务器端运行，执行速度快。 （2）存储过程执行一次后，其执行规划就驻留在高速缓冲存储器，在以后的操作中 ，只需从高速缓冲存储器中调用已编译好的二进制代码执行，提高了系统性能。 （3）确保数据库的安全。使用存储过程可以完成所有数据库操作，并可通过编程方 式控制上述操作对数据库信息访问的权限。7.1.1 创建存储过程创建存储过程可以使用CREATE PROCEDURE语句。要在MySQL 5.1中创建存储过 程，必须具有CREATE ROUTINE权限。

2、要想查看数据库中有哪些存储过程，可以使用 SHOW PROCEDURE STATUS命令。要查看某个存储过程的具体信息，可使用SHOW CREATE PROCEDURE sp_name命令，其中sp_name是存储过程的名称。 CREATE PROCEDURE的语法格式： CREATE PROCEDURE sp_name (proc_parameter,.)characteristic . routine_body 其中，proc_parameter的参数如下： IN | OUT | INOUT param_name type characteristic特征如下：LANGUAGE SQL|

3、NOT DETERMINISTIC| CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA | SQL SECURITY DEFINER | INVOKER | COMMENT string 说明： sp_name：存储过程的名称，默认在当前数据库中创建。需要在特定数据库中创 建存储过程时，则要在名称前面加上数据库的名称，格式为：db_name.sp_name。值 得注意的是，这个名称应当尽量避免取与MySQL的内置函数相同的名称，否则会发生错误 。7.1.1 创建存储过程 proc_parameter：存储过程的参数，param

4、_name为参数名，type为参数的类 型，当有多个参数的时候中间用逗号隔开。存储过程可以有0个、1个或多个参数。 MySQL存储过程支持三种类型的参数：输入参数、输出参数和输入/输出参数，关键字分 别是IN、OUT和INOUT。输入参数使数据可以传递给一个存储过程。当需要返回一个答 案或结果的时候，存储过程使用输出参数。输入/输出参数既可以充当输入参数也可以充当 输出参数。存储过程也可以不加参数，但是名称后面的括号是不可省略的。 注意：参数的名字不要等于列的名字，否则虽然不会返回出错消息，但是存储过程中 的SQL语句会将参数名看做列名，从而引发不可预知的结果。 characteristic：

5、存储过程的某些特征设定，下面一一介绍。 LANGUAGE SQL：表明编写这个存储过程的语言为SQL语言，目前来讲，MySQL 存储过程还不能用外部编程语言来编写，也就是说，这个选项可以不指定。将来将会对其 扩展，最有可能第一个被支持的语言是PHP。 DETERMINISTIC：设置为DETERMINISTIC表示存储过程对同样的输入参数产生 相同的结果，设置为NOT DETERMINISTIC则表示会产生不确定的结果。默认为NOT DETERMINISTIC。 CONTAINS SQL：表示存储过程不包含读或写数据的语句。NO SQL表示存储过程 不包含SQL语句。READS SQL DAT

6、A表示存储过程包含读数据的语句，但不包含写数据 的语句。MODIFIES SQL DATA表示存储过程包含写数据的语句。如果这些特征没有明 确给定，默认的是CONTAINS SQL。7.1.1 创建存储过程SQL SECURITY：SQL SECURITY特征可以用来指定存储过程使用创建该存储过 程的用户（DEFINER）的许可来执行，还是使用调用者（INVOKER）的许可来执行。 默认值是DEFINER。 COMMENT string：对存储过程的描述，string为描述内容。这个信息可以用 SHOW CREATE PROCEDURE语句来显示。 routine_body：这是存储过程的主体

7、部分，也叫做存储过程体。里面包含了在 过程调用的时候必须执行的语句，这个部分总是以BEGIN开始，以END结束。当然，当 存储过程体中只有一个SQL语句时可以省略BEGIN-END标志。 在开始创建存储过程之前，先介绍一个很实用的命令，即DELIMITER命令。在 MySQL中，服务器处理语句的时候是以分号为结束标志的。但是在创建存储过程的时 候，存储过程体中可能包含多个SQL语句，每个SQL语句都是以分号为结尾的，这时服 务器处理程序的时候遇到第一个分号就会认为程序结束，这肯定是不行的。所以这里使 用DELIMITER命令将MySQL语句的结束标志修改为其他符号。 DELIMITER语法格式

8、为： DELIMITER $ 说明：$是用户定义的结束符，通常这个符号可以是一些特殊的符号，如两个“#” ，两个“￥”等。当使用DELIMITER命令时，应该避免使用反斜杠（“”）字符，因为那 是MySQL的转义字符。7.1.1 创建存储过程【例7.1】 将MySQL结束符修改为两个斜杠“/”符号。 DELIMITER / 说明：执行完这条命令后，程序结束的标志就换为双斜杠符号 “/”了。 用下列语句检验一下： SELECT 姓名 FROM XS WHERE 学号=081101 / 结果为：要想恢复使用分号“;”作为结束符，运行下面命令即可： DELIMITER ; 【例7.2】 下面是一个存

9、储过程的简单例子，实现的功能是删除一个特定学生的信息 。 DELIMITER $ CREATE PROCEDURE DELETE_STUDENT(IN XH CHAR(6) BEGIN DELETE FROM XS WHERE 学号=XH; END $ DELIMITER ; 说明：当调用这个存储过程时，MySQL根据提供的参数XH的值，删除对应在XS表中 的数据。调用存储过程的命令是CALL命令，后面会讲到。 在关键字BEGIN和END之间指定了存储过程体，当然，BEGIN-END复合语句还可 以嵌套使用，下面就来介绍存储过程体的内容。7.1.2 存储过程体1. 局部变量 在存储过程中可以声

10、明局部变量，它们可以用来存储临时结果。要声明局部变量必须 使用DECLARE语句。在声明局部变量的同时也可以对其赋一个初始值。 DECLARE语法格式如下： DECLARE var_name,. type DEFAULT value 说明：var_name为变量名；type为变量类型；DEFAULT子句给变量指定一个默认 值，如果不指定默认为NULL的话。 【例7.3】 声明一个整型变量和两个字符变量。 DECLARE num INT(4); DECLARE str1, str2 VARCHAR(6); 说明：局部变量只能在BEGINEND语句块中声明。 局部变量必须在存储过程的开头就声明，声

11、明完后，可以在声明它的BEGINEND语 句块中使用该变量，其他语句块中不可以使用它。 前面已经学习过用户变量，在存储过程中也可以声明用户变量，不过千万不要将这两 个混淆。局部变量和用户变量的区别在于：局部变量前面没有使用符号，局部变量在其 所在的BEGINEND语句块处理完后就消失了，而用户变量存在于整个会话当中。7.1.2 存储过程体2. 使用SET语句赋值 要给局部变量赋值可以使用SET语句，SET语句也是SQL本身的一部分。 语法格式为： SET var_name = expr , var_name = expr . 【例7.4】 在存储过程中给局部变量赋值。 SET num=1, s

12、tr1= hello; 说明：与声明用户变量时不同，这里的变量名前面没有符号。声明用户变量的方法 已经介绍过，这里不再举例。注意，例中的这条语句无法单独执行，只能在存储过程和存 储函数中使用。 3. SELECT.INTO语句 使用这个SELECTINTO语法可以把选定的列值直接存储到变量中。因此，返回的结 果只能有一行。 语法格式为： SELECT col_name,. INTO var_name,. table_expr 说明：col_name是列名，var_name是要赋值的变量名。table_expr是SELECT语 句中的FROM子句及后面的部分，这里不再叙述。 【例7.5】 在存储

13、过程体中将XS表中的学号为081101的学生姓名和专业名的值分别 赋给变量name和project。 SELECT 姓名,专业名 INTO name, project FROM XS; WHERE 学号= 081101;7.1.2 存储过程体4. 流程控制语句 在MySQL中，常见的过程式SQL语句可以用在一个存储过程体中。例如：IF语句、 CASE语句、LOOP语句、WHILE语句、ITERATE语句和LEAVE语句。 （1）IF语句 IF-THEN-ELSE语句可根据不同的条件执行不同的操作。 语法格式为： IF search_condition THEN statement_list E

14、LSEIF search_condition THEN statement_list . ELSE statement_list END IF 说明：search_condition是判断的条件，statement_list中包含一个或多个SQL语句 。当search_condition的条件为真时，就执行相应的SQL语句。 IF语句不同于系统的内置函数IF()函数，IF()函数只能判断两种情况，所以请不要混淆 。7.1.2 存储过程体【例7.6】 创建XSCJ数据库的存储过程，判断两个输入的参数哪一个更大。 DELIMITER $ CREATE PROCEDURE XSCJ.COMPAR (

15、IN K1 INTEGER, IN K2 INTEGER, OUT K3 CHAR(6) ) BEGIN IF K1K2 THEN SET K3= 大于; ELSEIF K1=K2 THEN SET K3= 等于; ELSE SET K3= 小于; END IF; END$ DELIMITER ; 说明：存储过程中K1和K2是输入参数，K3是输出参数。 （2）CASE语句 CASE语句在4.2.1节介绍选择列的时候已经涉及。这里介绍CASE语句在存储过程中 的用法，与之前略有不同。7.1.2 存储过程体语法格式为： CASE case_valueWHEN when_value THEN sta

16、tement_listWHEN when_value THEN statement_list .ELSE statement_list END CASE 或者： CASEWHEN search_condition THEN statement_listWHEN search_condition THEN statement_list .ELSE statement_list END CASE 说明：一个CASE语句经常可以充当一个IF-THEN-ELSE语句。 第一种格式中case_value是要被判断的值或表达式，接下来是一系列的WHEN-THEN 块，每一块的when_value参数指定要与case_value比较的值，如果为真，就执行 statement_list中的SQL语句。如果前面的每一个块都不匹配就会执