过程式数据库对象.ppt

《过程式数据库对象.ppt》由会员分享，可在线阅读，更多相关《过程式数据库对象.ppt（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 typecharacteristic特征如下：LANGUAGE SQL|NOT DETERMINISTI

3、C|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_name为参数名，type为参数的类型，当有多个参数的时候中间用

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

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

6、过程包含写数据的语句。如果这些特征没有明确给定，默认的是CONTAINS SQL。,7.1.1 创建存储过程,SQL SECURITY：SQL SECURITY特征可以用来指定存储过程使用创建该存储过程的用户（DEFINER）的许可来执行，还是使用调用者（INVOKER）的许可来执行。默认值是DEFINER。COMMENT string：对存储过程的描述，string为描述内容。这个信息可以用SHOW CREATE PROCEDURE语句来显示。routine_body：这是存储过程的主体部分，也叫做存储过程体。里面包含了在过程调用的时候必须执行的语句，这个部分总是以BEGIN开始，以END结

7、束。当然，当存储过程体中只有一个SQL语句时可以省略BEGIN-END标志。在开始创建存储过程之前，先介绍一个很实用的命令，即DELIMITER命令。在MySQL中，服务器处理语句的时候是以分号为结束标志的。但是在创建存储过程的时候，存储过程体中可能包含多个SQL语句，每个SQL语句都是以分号为结尾的，这时服务器处理程序的时候遇到第一个分号就会认为程序结束，这肯定是不行的。所以这里使用DELIMITER命令将MySQL语句的结束标志修改为其他符号。DELIMITER语法格式为：DELIMITER$说明：$是用户定义的结束符，通常这个符号可以是一些特殊的符号，如两个“#”，两个“￥”等。当使用D

8、ELIMITER命令时，应该避免使用反斜杠（“”）字符，因为那是MySQL的转义字符。,7.1.1 创建存储过程,【例7.1】将MySQL结束符修改为两个斜杠“/”符号。DELIMITER/说明：执行完这条命令后，程序结束的标志就换为双斜杠符号“/”了。用下列语句检验一下：SELECT 姓名 FROM XS WHERE 学号=081101/结果为：,要想恢复使用分号“;”作为结束符，运行下面命令即可：DELIMITER;【例7.2】下面是一个存储过程的简单例子，实现的功能是删除一个特定学生的信息。DELIMITER$CREATE PROCEDURE DELETE_STUDENT(IN XH C

9、HAR(6)BEGINDELETE FROM XS WHERE 学号=XH;END$DELIMITER;说明：当调用这个存储过程时，MySQL根据提供的参数XH的值，删除对应在XS表中的数据。调用存储过程的命令是CALL命令，后面会讲到。在关键字BEGIN和END之间指定了存储过程体，当然，BEGIN-END复合语句还可以嵌套使用，下面就来介绍存储过程体的内容。,7.1.2 存储过程体,1.局部变量在存储过程中可以声明局部变量，它们可以用来存储临时结果。要声明局部变量必须使用DECLARE语句。在声明局部变量的同时也可以对其赋一个初始值。DECLARE语法格式如下：DECLARE var_na

10、me,.type DEFAULT value说明：var_name为变量名；type为变量类型；DEFAULT子句给变量指定一个默认值，如果不指定默认为NULL的话。【例7.3】声明一个整型变量和两个字符变量。DECLARE num INT(4);DECLARE str1,str2 VARCHAR(6);说明：局部变量只能在BEGINEND语句块中声明。局部变量必须在存储过程的开头就声明，声明完后，可以在声明它的BEGINEND语句块中使用该变量，其他语句块中不可以使用它。前面已经学习过用户变量，在存储过程中也可以声明用户变量，不过千万不要将这两个混淆。局部变量和用户变量的区别在于：局部变量前

11、面没有使用符号，局部变量在其所在的BEGINEND语句块处理完后就消失了，而用户变量存在于整个会话当中。,7.1.2 存储过程体,2.使用SET语句赋值要给局部变量赋值可以使用SET语句，SET语句也是SQL本身的一部分。语法格式为：SET var_name=expr,var_name=expr.【例7.4】在存储过程中给局部变量赋值。SET num=1,str1=hello;说明：与声明用户变量时不同，这里的变量名前面没有符号。声明用户变量的方法已经介绍过，这里不再举例。注意，例中的这条语句无法单独执行，只能在存储过程和存储函数中使用。3.SELECT.INTO语句 使用这个SELECTIN

12、TO语法可以把选定的列值直接存储到变量中。因此，返回的结果只能有一行。语法格式为：SELECT col_name,.INTO var_name,.table_expr说明：col_name是列名，var_name是要赋值的变量名。table_expr是SELECT语句中的FROM子句及后面的部分，这里不再叙述。【例7.5】在存储过程体中将XS表中的学号为081101的学生姓名和专业名的值分别赋给变量name和project。SELECT 姓名,专业名 INTO name,project FROM XS;WHERE 学号=081101;,7.1.2 存储过程体,4.流程控制语句在MySQL中，常

13、见的过程式SQL语句可以用在一个存储过程体中。例如：IF语句、CASE语句、LOOP语句、WHILE语句、ITERATE语句和LEAVE语句。（1）IF语句IF-THEN-ELSE语句可根据不同的条件执行不同的操作。语法格式为：IF search_condition THEN statement_listELSEIF search_condition THEN statement_list.ELSE statement_listEND IF说明：search_condition是判断的条件，statement_list中包含一个或多个SQL语句。当search_condition的条件为真时，

14、就执行相应的SQL语句。IF语句不同于系统的内置函数IF()函数，IF()函数只能判断两种情况，所以请不要混淆。,7.1.2 存储过程体,【例7.6】创建XSCJ数据库的存储过程，判断两个输入的参数哪一个更大。DELIMITER$CREATE PROCEDURE XSCJ.COMPAR(IN K1 INTEGER,IN K2 INTEGER,OUT K3 CHAR(6)BEGINIF K1K2 THENSET K3=大于;ELSEIF K1=K2 THENSET K3=等于;ELSE SET K3=小于;END IF;END$DELIMITER;说明：存储过程中K1和K2是输入参数，K3是输出

15、参数。（2）CASE语句CASE语句在节介绍选择列的时候已经涉及。这里介绍CASE语句在存储过程中的用法，与之前略有不同。,7.1.2 存储过程体,语法格式为：CASE case_value WHEN when_value THEN statement_list WHEN when_value THEN statement_list.ELSE statement_listEND CASE或者：CASE WHEN search_condition THEN statement_list WHEN search_condition THEN statement_list.ELSE statemen

16、t_listEND CASE说明：一个CASE语句经常可以充当一个IF-THEN-ELSE语句。第一种格式中case_value是要被判断的值或表达式，接下来是一系列的WHEN-THEN块，每一块的when_value参数指定要与case_value比较的值，如果为真，就执行statement_list中的SQL语句。如果前面的每一个块都不匹配就会执行ELSE块指定的语句。CASE语句最后以END CASE结束。第二种格式中CASE关键字后面没有参数，在WHEN-THEN块中，search_condition指定了一个比较表达式，表达式为真时执行THEN后面的语句。与第一种格式相比，这种格式能

17、够实现更为复杂的条件判断，使用起来更方便。,7.1.2 存储过程体,【例7.7】创建一个存储过程，针对参数的不同，返回不同的结果。DELIMITER$CREATE PROCEDURE XSCJ.RESULT(IN str VARCHAR(4),OUT sex VARCHAR(4)BEGIN CASE strWHEN M THEN SET sex=男;WHEN F THEN SET sex=女;ELSE SET sex=无;END CASE;END$DELIMITER;【例7.8】用第二种格式的CASE语句创建以上存储过程。程序片段如下：CASE WHEN str=M THEN SET sex=

18、男;WHEN str=F THEN SET sex=女;ELSE SET sex=无;END CASE;,7.1.2 存储过程体,（3）循环语句MySQL支持3条用来创建循环的语句：WHILE、REPEAT和LOOP语句。在存储过程中可以定义0个、1个或多个循环语句。WHILE语句语法格式为：begin_label:WHILE search_condition DOstatement_listEND WHILE end_label说明：语句首先判断search_condition是否为真，不为真则执行statement_list中的语句，然后再次进行判断，为真则继续循环，不为真则结束循环。be

19、gin_label和end_label是WHILE语句的标注。除非begin_label存在，否则end_label不能被给出，并且如果两者都出现，它们的名字必须是相同的。【例7.9】创建一个带WHILE循环的存储过程。DELIMITER$CREATE PROCEDURE dowhile()BEGIN DECLARE v1 INT DEFAULT 5;WHILE v1 0 DO SET v1=v11;END WHILE;END$DELIMITER;,7.1.2 存储过程体,说明：当调用这个存储过程时，首先判断v1的值是否大于零，如果大于零则执行v11，否则结束循环。REPEAT语句格式如下：b

20、egin_label:REPEAT statement_listUNTIL search_conditionEND REPEAT end_label说明：REPEAT语句首先执行statement_list中的语句，然后判断search_condition是否为真，为真则停止循环，不为真则继续循环。REPEAT也可以被标注。【例7.10】用REPEAT语句创建一个如例7.9的存储过程。程序片段如下：REPEAT v1=v11;UNTIL v11;END REPEAT;说明：REPEAT语句和WHILE语句的区别在于：REPEAT语句先执行语句，后进行判断；而WHILE语句是先判断，条件为真时才

21、执行语句。,7.1.2 存储过程体,LOOP语句语法格式如下：begin_label:LOOP statement_listEND LOOP end_label说明：LOOP允许某特定语句或语句群的重复执行，实现一个简单的循环构造，statement_list是需要重复执行的语句。在循环内的语句一直重复至循环被退出，退出时通常伴随着一个LEAVE 语句。LEAVE语句经常和BEGIN.END或循环一起使用。结构如下：LEAVE labellabel是语句中标注的名字，这个名字是自定义的。加上LEAVE关键字就可以用来退出被标注的循环语句。【例7.11】创建一个带LOOP语句的存储过程。DELI

22、MITER$CREATE PROCEDURE doloop()BEGIN SET a=10;Label:LOOP SET a=a1;IF a0 THEN LEAVE Label;END IF;END LOOP Label;END$DELIMITER;,7.1.2 存储过程体,说明：语句中，首先定义了一个用户变量并赋值为10，接着进入LOOP循环，标注为Label，执行减1语句，然后判断用户变量a是否小于零，是则使用LEAVE语句跳出循环。我们调用此存储过程来查看最后结果。调用该存储过程使用如下命令：CALL doloop();接着，查看用户变量的值：SELECT a;结果为：,可以看到，用户变

23、量a的值已经变成1了。循环语句中还有一个ITERATE语句，它只可以出现在LOOP、REPEAT和WHILE语句内，意为“再次循环”。它的格式为：ITERATE label说明：该语句格式与LEAVE差不多，区别在于：LEAVE语句是离开一个循环，而ITERATE语句是重新开始一个循环。,7.1.2 存储过程体,在存储过程中处理SQL语句可能导致一条错误消息。例如，向一个表中插入新的行而主键值已经存在，这条INSERT语句会导致一个出错消息，并且MySQL立即停止对存储过程的处理。每一个错误消息都有一个唯一代码和一个SQLSTATE代码。例如，SQLSTATE 23000属于如下的出错代码：E

24、rror 1022,Cant write;duplicate key in tableError 1048,Column cannot be nullError 1052,Column is ambiguousError 1062,Duplicate entry for keyMySQL手册的“错误消息和代码”一章中列出了所有的出错消息及它们各自的代码。为了防止MySQL在一条错误消息产生时就停止处理，需要使用到DECLARE HANDLER语句。DECLARE HANDLER语句为错误代码声明了一个所谓的处理程序，它指明：对一条SQL语句的处理如果导致一条错误消息，将会发生什么。DECLAR

25、E HANDLER语法格式为：DECLARE handler_type HANDLER FOR condition_value,.sp_statement,7.1.2 存储过程体,其中，handler_type为：CONTINUE|EXIT|UNDOcondition_value为：SQLSTATE VALUE sqlstate_value|condition_name|SQLWARNING|NOT FOUND|SQLEXCEPTION|mysql_error_code说明：handler_type：处理程序的类型，主要有三种：CONTINUE、EXIT和UNDO。对CONTINUE处理程序，

26、MySQL不中断存储过程的处理。对于EXIT处理程序，当前BEGIN.END复合语句的执行被终止。UNDO处理程序类型语句暂时还不被支持。condition_value：给出SQLSTATE的代码表示。condition_name是处理条件的名称，接下来会讲到。SQLWARNING是对所有以01开头的SQLSTATE代码的速记。NOT FOUND是对所有以02开头的SQLSTATE代码的速记。SQLEXCEPTION是对所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE代码的速记。当用户不想为每个可能的出错消息都定义一个处理程序时可以使用以上三种形式。,7.1.2 存储过

27、程体,mysql_error_code是具体的SQLSTATE代码。除了SQLSTATE值，MySQL错误代码也被支持，表示的形式为：ERROR=xxxx。sp_statement：处理程序激活时将要执行的动作。【例7.12】创建一个存储过程，向XS表插入一行数据（081101,王民,计算机,1,1990-02-10,50,NULL,NULL），已知学号081101在XS表中已存在。如果出现错误，程序继续进行。USE XSCJ;DELIMITER$CREATE PROCEDURE MY_INSERT()BEGIN DECLARE CONTINUE HANDLER FOR SQLSTATE 23

28、000 SET x2=1;SET x=2;INSERT INTO XS VALUES(081101,王民,计算机,1,1990-02-10,50,NULL,NULL);SET x=3;END$DELIMITER;,调用存储过程查看结果的语法格式为：CALL MY_INSERT();SELECT X;结果为：,7.1.2 存储过程体,说明：在调用存储过程后，未遇到错误消息时处理程序未被激活，当执行INSERT语句出现出错消息时，MySQL检查是否为这个错误代码定义了处理程序。如果有，则激活该处理程序，本例中，INSERT语句导致的错误消息刚好是SQLSTATE代码中的一条。接下来执行处理程序的附

29、加语句（SET x2=1）。此后，MySQL检查处理程序的类型，这里的类型为CONTINUE，因此存储过程继续处理，将用户变量x赋值为3。如果这里的INSERT语句能够执行，处理程序将不被激活，用户变量x2将不被赋值。注意：不能为同一个出错消息在同一个BEGIN-END语句块中定义两个或更多的处理 程序。为了提高可读性，可以使用DECLARE CONDITION语句为一个SQLSTATE或出错代码定义一个名字，并且可以在处理程序中使用这个名字。DECLARE CONDITION语法格式为：DECLARE condition_name CONDITION FOR condition_value其

30、中，condition_value：SQLSTATE VALUE sqlstate_value|mysql_error_code,7.1.2 存储过程体,说明：condition_name是处理条件的名称，condition_value为要定义别名的SQLSTATE或出错代码。【例7.13】修改例7.12中的存储过程，将SQLSTATE 23000 定义成NON_UNIQUE，并在处理程序中使用这个名称。程序片段为：BEGIN DECLARE NON_UNIQUE CONDITION FOR SQLSTATE 23000;DECLARE CONTINUE HANDLER FOR NON_UNI

31、QUE SET x2=1;SET x=2;INSERT INTO XS VALUES(081101,王民,计算机,1,1990-02-10,50,NULL,NULL);SET x=3;END;6.游标一条SELECT.INTO语句返回的是带有值的一行，这样可以把数据读取到存储过程中。但是常规的SELECT语句返回的是多行数据，如果要处理它需要引入游标这一概念。MySQL支持简单的游标。在MySQL中，游标一定要在存储过程或函数中使用，不能单独在查询中使用。,7.1.2 存储过程体,使用一个游标需要用到4条特殊的语句：DECLARE CURSOR（声明游标）、OPEN CURSOR（打开游标）、

32、FETCH CURSOR（读取游标）和CLOSE CURSOR（关闭游标）。如果使用了DECLARE CURSOR语句声明了一个游标，这样就把它连接到了一个由SELECT语句返回的结果集中。使用OPEN CORSOR语句打开这个游标。接着，可以用FETCH CURSOR语句把产生的结果一行一行地读取到存储过程或存储函数中去。游标相当于一个指针，它指向当前的一行数据，使用FETCH CORSOR语句可以把游标移动到下一行。当处理完所有的行时，使用CLOSE CURSOR语句关闭这个游标。（1）声明游标语法格式：DECLARE cursor_name CURSOR FOR select_state

33、ment说明：cursor_name是游标的名称，游标名称使用与表名同样的规则。select_statement是一个SELECT语句，返回的是一行或多行的数据。这个语句声明一个游标，也可以在存储过程中定义多个游标，但是一个块中的每一个游标必须有唯一的名字。注意：这里的SELECT子句不能有INTO子句。,7.1.2 存储过程体,下面的定义符合一个游标声明：DECLARE XS_CUR1 CURSORFOR SELECT 学号,姓名,性别,出生日期,总学分 FROM XS WHERE 专业名=计算机;注意：游标只能在存储过程或存储函数中使用，例中语句无法单独运行。（2）打开游标声明游标后，要使

34、用游标从中提取数据，就必须先打开游标。在MySQL中，使用OPEN语句打开游标，其格式为：OPEN cursor_name 在程序中，一个游标可以打开多次，由于其他的用户或程序本身已经更新了表，所以每次打开结果可能不同。（3）读取数据游标打开后，就可以使用FETCHINTO语句从中读取数据。语法格式：FETCH cursor_name INTO var_name,var_name.说明：FETCH.INTO语句与SELECT.INTO语句具有相同的意义，FETCH语句是将游标指向的一行数据赋给一些变量，子句中变量的数目必须等于声明游标时SELECT子句中列的数目。var_name是存放数据的变

35、量名。,7.1.2 存储过程体,（4）关闭游标游标使用完以后，要及时关闭。关闭游标使用CLOSE语句，格式为：CLOSE cursor_name语句参数的含义与OPEN语句中相同。例如：CLOSE XS_CUR2将关闭游标XS_CUR2。【例7.14】创建一个存储过程，计算XS表中行的数目。DELIMITER$CREATE PROCEDURE compute(OUT NUMBER INTEGER)BEGIN DECLARE XH CHAR(6);DECLARE FOUND BOOLEAN DEFAULT TRUE;DECLARE NUMBER_XS CURSOR FOR SELECT 学号 F

36、ROM XS;DECLARE CONTINUE HANDLER FOR NOT FOUNDSET FOUND=FALSE;SET NUMBER=0;OPEN NUMBER_XS;FETCH NUMBER_XS INTO XH;WHILE FOUND DO SET NUMBER=NUMBER+1;FETCH NUMBER_XS INTO XH;END WHILE;CLOSE NUMBER_XS;END$DELIMITER;,7.1.2 存储过程体,调用此存储过程并查看结果：CALL compute(num);SELECT num;结果为：,说明：这个例子也可以直接使用COUNT函数来解决，这里只

37、是为了说明如何使用一个游标而已。,7.1.3 调用存储过程,存储过程创建完后，可以在程序、触发器或者存储过程中被调用，但是都必须使用到CALL语句，前面已经简单地介绍了CALL语句的形式，本节重点介绍它。语法格式：CALL sp_name(parameter,.)说明：sp_name为存储过程的名称，如果要调用某个特定数据库的存储过程，则需要在前面加上该数据库的名称。parameter为调用该存储过程使用的参数，这条语句中的参数个数必须总是等于存储过程的参数个数。【例7.15】创建存储过程，实现查询XS表中学生人数的功能，该存储过程不带参数。USE XSCJ;CREATE PROCEDURE

38、DO_QUERY()SELECT COUNT(*)FROM XS ORDER BY 学号;调用该存储过程：CALL DO_QUERY();结果为：,7.1.3 调用存储过程,说明：通常SELECT语句不会被直接用在存储过程中。【例7.16】假设例7.6中的存储过程已经创建，调用该存储过程。CALL COMPAR(3,6,K);SELECT K;结果为：,说明：3和6相当于输入参数K1和K2，用户变量K相当于输出参数K3。可以看到，由于36，输出参数K的值就为“小于”。前面创建的存储过程都是比较简单的，下面结合一些比较复杂的例子来学习如何调用存储过程。,7.1.3 调用存储过程,【例7.17】创

39、建一个存储过程，有两个输入参数：XH和KCM，要求当某学生某门课程的成绩小于60分时将其学分修改为零，大于等于60分时将学分修改为此课程的学分。DELIMITER$CREATE PROCEDURE XSCJ.DO_UPDATE(IN XH CHAR(6),IN KCM CHAR(16)BEGIN DECLARE KCH CHAR(3);DECLARE XF TINYINT;DECLARE CJ TINYINT;SELECT 课程号,学分 INTO KCH,XF FROM KC WHERE 课程名=KCM;SELECT 成绩 INTO CJ FROM XS_KC WHERE 学号=XH AND

40、课程号=KCH;IF CJ60 THEN UPDATE XS_KC SET 学分=0 WHERE 学号=XH AND 课程号=KCH;ELSE UPDATE XS_KC SET 学分=XF WHERE 学号=XH AND 课程号=KCH;END IF;END$DELIMITER;,7.1.3 调用存储过程,接下来向XS_KC表中输入一行数据：INSERT INTO XS_KC VALUES(081101,208,50,10);接下来调用存储过程并查询调用结果：CALL DO_UPDATE(081101,数据结构);SELECT*FROM XS_KC WHERE 学号=081101 AND 课程

41、号=208;结果为：,可以看到，成绩小于60分时，学分已经被修改为零。【例7.18】创建一个存储过程DO_INSERT1，作用是向XS表中插入一行数据。创建另外一个存储过程DO_INSERT2，在其中调用第一个存储过程，并根据条件处理该行数据。第一个存储过程：CREATE PROCEDURE XSCJ.DO_INSERT1()INSERT INTO XS VALUES(091101,陶伟,软件工程,1,1990-03-05,50,NULL,NULL);,7.1.3 调用存储过程,第二个存储过程：DELIMITER$CREATE PROCEDURE XSCJ.DO_INSERT2(IN X BI

42、T(1),OUT STR CHAR(8)BEGIN CALL DO_INSERT1();IF X=0 THEN UPDATE XS SET 姓名=刘英,性别=0 WHERE 学号=091101;SET STR=修改成功;ELSEIF X=1 THENDELETE FROM XS WHERE 学号=091101;SET STR=删除成功;END IF;END$DELIMITER;接下来调用存储过程DO_INSERT2来查看结果：CALL DO_INSERT2(1,str);SELECT str;结果为：,7.1.3 调用存储过程,CALL DO_INSERT2(0,str);SELECT str

43、;结果为：,7.1.4 删除存储过程,存储过程创建后需要删除时使用DROP PROCEDURE语句。在此之前，必须确认该存储过程没有任何依赖关系，否则会导致其他与之关联的存储过程无法运行。语法格式为：DROP PROCEDURE IF EXISTS sp_name说明：sp_name是要删除的存储过程的名称。IF EXISTS子句是MySQL的扩展，如果程序或函数不存在，它防止发生错误。【例7.19】删除存储过程dowhile。DROP PROCEDURE IF EXISTS dowhile;,7.1.5 修改存储过程,使用ALTER PROCEDURE语句可以修改存储过程的某些特征。语法格式

44、为：ALTER PROCEDURE sp_name characteristic.其中，characteristic为：CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA|SQL SECURITY DEFINER|INVOKER|COMMENT string说明：characteristic是存储过程创建时的特征，在CREATE PROCEDURE语句中已经介绍过。只要设定了其中的值，存储过程的特征就随之变化。如果要修改存储过程的内容，可以使用先删除再重新定义存储过程的方法。【例7.20】使用先删除后修改的方法修改例7.12中的存储过程。D

45、ELIMITER$DROP PROCEDURE IF EXISTS DO_QUERY;CREATE PROCEDURE DO_QUERY()BEGINSELECT*FROM XS;END$DELIMITER;,7.2 存储函数,存储函数也是过程式对象之一，与存储过程很相似。它们都是由SQL和过程式语句组成的代码片断，并且可以从应用程序和SQL中调用。然而，它们也有一些区别：（1）存储函数不能拥有输出参数，因为存储函数本身就是输出参数；（2）不能用CALL语句来调用存储函数；（3）存储函数必须包含一条RETURN语句，而这条特殊的SQL语句不允许包含于存储过程中。,7.2.1 创建存储函数,创建

46、存储函数使用CREATE FUNCTION语句。要查看数据库中有哪些存储函数，可以使用SHOW FUNCTION STATUS命令。CREATE FUNCTION语法格式：CREATE FUNCTION sp_name(func_parameter,.)RETURNS type characteristic.routine_body说明：存储函数的定义格式和存储过程相差不大。sp_name是存储函数的名称。存储函数不能拥有与存储过程相同的名字。func_parameter是存储函数的参数，参数只有名称和类型，不能指定IN、OUT和INOUT。RETURNS type子句声明函数返回值的数据类型

47、。routine_body是存储函数的主体，也叫存储函数体，所有在存储过程中使用的SQL语句在存储函数中也适用，包括流程控制语句、游标等。但是存储函数体中必须包含一个RETURN value语句，value为存储函数的返回值。这是存储过程体中没有的。下面举一些存储函数的例子。【例7.21】创建一个存储函数，它返回XS表中学生的数目作为结果。DELIMITER$CREATE FUNCTION NUM_OF_XS()RETURNS INTEGERBEGINRETURN(SELECT COUNT(*)FROM XS);END$DELIMITER;,7.2.1 创建存储函数,说明：RETURN子句中包

48、含SELECT语句时，SELECT语句的返回结果只能是一行且只能有一列值。【例7.22】创建一个存储函数，返回某个学生的姓名。DELIMITER$CREATE FUNCTION NAME_OF_STU(XH CHAR(6)RETURNS CHAR(8)BEGINRETURN(SELECT 姓名 FROM XS WHERE 学号=XH);END$DELIMITER;【例7.23】创建一个存储函数来删除XS_KC表中有但XS表中不存在的学号。DELIMITER$CREATE FUNCTION DELETE_STU(XH CHAR(6)RETURNS BOOLEANBEGINDECLARE STU

49、CHAR(6);SELECT 姓名 INTO STU FROM XS WHERE 学号=XH;IF STU IS NULL THENDELETE FROM XS_KC WHERE 学号=XH;RETURN TRUE;ELSE RETURN FALSE;END IF;END$DELIMITER;,7.2.2 调用存储函数,存储函数创建完后，就如同系统提供的内置函数（如VERSION()），所以调用存储函数的方法也差不多，都是使用SELECT关键字。语法格式为：SELECT sp_name(func_parameter,.)【例7.24】调用例7.21中的存储函数。SELECT NUM_OF_XS

50、();结果为：,【例7.25】调用例7.22中的存储函数。SELECT NAME_OF_STU(081106);结果为：,7.2.2 调用存储函数,存储函数中还可以调用另外一个存储函数或者存储过程。【例7.26】创建一个存储函数，通过调用存储函数NAME_OF_STU获得学号的姓名，判断姓名是否是“王林”，是则返回王林的出生日期，不是则返回“FALSE”。DELIMITER$CREATE FUNCTION IS_STU(XH CHAR(6)RETURNS CHAR(10)BEGINDECLARE NAME CHAR(8);SELECT NAME_OF_STU(XH)INTO NAME;IF N