过程式数据库对象

单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,第,7,章 过程式数据库对象,7.1,存储过程,7.2,存储函数,7.3,触发器,7.4,事件,7.1,存储过程,使用存储过程的优点有：,（,1,）存储过程在服务器端运行，执行速度快。,（,2,）存储过程执行一次后，其执行规划就驻留在高速缓冲存储器，在以后的操作中，只需从高速缓冲存储器中调用已编译好的二进制代码执行，提高了系统性能。,（,3,）确保数据库的安全。使用存储过程可以完成所有数据库操作，并可通过编程方式控制上述操作对数据库信息访问的权限。,7.1.1,创建存储过程,创建存储过程可以使用,CREATE PROCEDURE,语句。要在,MySQL,5.1,中创建存储过程，必须具有,CREATE ROUTINE,权限。要想查看数据库中有哪些存储过程，可以使用,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,| 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_name,为参数名，,type,为参数的类型，当有多个参数的时候中间用逗号隔开。存储过程可以有,0,个、,1,个或多个参数。,MySQL,存储过程支持三种类型的参数：输入参数、输出参数和输入,/,输出参数，关键字分别是,IN,、,OUT,和,INOUT,。输入参数使数据可以传递给一个存储过程。当需要返回一个答案或结果的时候，存储过程使用输出参数。输入,/,输出参数既可以充当输入参数也可以充当输出参数。存储过程也可以不加参数，但是名称后面的括号是不可省略的。,注意：参数的名字不要等于列的名字，否则虽然不会返回出错消息，但是存储过程中的,SQL,语句会将参数名看做列名，从而引发不可预知的结果。,characteristic,：存储过程的某些特征设定，下面一一介绍。,LANGUAGE SQL,：表明编写这个存储过程的语言为,SQL,语言，目前来讲，,MySQL,存储过程还不能用外部编程语言来编写，也就是说，这个选项可以不指定。将来将会对其扩展，最有可能第一个被支持的语言是,PHP,。,DETERMINISTIC,：设置为,DETERMINISTIC,表示存储过程对同样的输入参数产生相同的结果，设置为,NOT DETERMINISTIC,则表示会产生不确定的结果。默认为,NOT DETERMINISTIC,。,CONTAINS SQL,：表示存储过程不包含读或写数据的语句。,NO SQL,表示存储过程不包含,SQL,语句。,READS SQL DATA,表示存储过程包含读数据的语句，但不包含写数据的语句。,MODIFIES SQL DATA,表示存储过程包含写数据的语句。如果这些特征没有明确给定，默认的是,CONTAINS SQL,。,7.1.1,创建存储过程,SQL SECURITY,：,SQL SECURITY,特征可以用来指定存储过程使用创建该存储过程的用户（,DEFINER,）的许可来执行，还是使用调用者（,INVOKER,）的许可来执行。默认值是,DEFINER,。,COMMENT string,：对存储过程的描述，,string,为描述内容。这个信息可以用,SHOW CREATE PROCEDURE,语句来显示。,routine_body,：这是存储过程的主体部分，也叫做存储过程体。里面包含了在过程调用的时候必须执行的语句，这个部分总是以,BEGIN,开始，以,END,结束。当然，当存储过程体中只有一个,SQL,语句时可以省略,BEGIN-END,标志。,在开始创建存储过程之前，先介绍一个很实用的命令，即,DELIMITER,命令。在,MySQL,中，服务器处理语句的时候是以分号为结束标志的。但是在创建存储过程的时候，存储过程体中可能包含多个,SQL,语句，每个,SQL,语句都是以分号为结尾的，这时服务器处理程序的时候遇到第一个分号就会认为程序结束，这肯定是不行的。所以这里使用,DELIMITER,命令将,MySQL,语句的结束标志修改为其他符号。,DELIMITER,语法格式为：,DELIMITER $,说明：,$,是用户定义的结束符，通常这个符号可以是一些特殊的符号，如两个“,#”,，两个“￥”等。当使用,DELIMITER,命令时，应该避免使用反斜杠（“,”,）字符，因为那是,MySQL,的转义字符。,7.1.1,创建存储过程,【,例,7.1,】,将,MySQL,结束符修改为两个斜杠“,/”,符号。,DELIMITER /,说明：执行完这条命令后，程序结束的标志就换为双斜杠符号 “,/”,了。,用下列语句检验一下：,SELECT,姓名,FROM XS WHERE,学号,=081101 /,结果为：,要想恢复使用分号“,;”,作为结束符，运行下面命令即可：,DELIMITER ;,【,例,7.2,】,下面是一个存储过程的简单例子，实现的功能是删除一个特定学生的信息。,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.,局部变量,在存储过程中可以声明局部变量，它们可以用来存储临时结果。要声明局部变量必须使用,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,语句块中声明。,局部变量必须在存储过程的开头就声明，声明完后，可以在声明它的,BEGINEND,语句块中使用该变量，其他语句块中不可以使用它。,前面已经学习过用户变量，在存储过程中也可以声明用户变量，不过千万不要将这两个混淆。局部变量和用户变量的区别在于：局部变量前面没有使用,符号，局部变量在其所在的,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,语句,使用这个,SELECTINTO,语法可以把选定的列值直接存储到变量中。因此，返回的结果只能有一行。,语法格式为：,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,中，常见的过程式,SQL,语句可以用在一个存储过程体中。例如：,IF,语句、,CASE,语句、,LOOP,语句、,WHILE,语句、,ITERATE,语句和,LEAVE,语句。,（,1,）,IF,语句,IF-THEN-ELSE,语句可根据不同的条件执行不同的操作。,语法格式为：,IF,search_condition,THEN,statement_list,ELSEIF,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,(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_value,WHEN,when_value,THEN,statement_list,WHEN,when_value,THEN,statement_list, .,ELSE,statement_list,END CASE,或者：,CASE,WHEN,search_condition,THEN,statement_list,WHEN,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,语句。如果前面的每一个块都不匹配就会执行,ELSE,块指定的语句。,CASE,语句最后以,END CASE,结束。,第二种格式中,CASE,关键字后面没有参数，在,WHEN-THEN,块中，,search_condition,指定了一个比较表达式，表达式为真时执行,THEN,后面的语句。与第一种格式相比，这种格式能够实现更为复杂的条件判断，使用起来更方便。,7.1.2,存储过程体,【,例,7.7,】,创建一个存储过程，针对参数的不同，返回不同的结果。,DELIMITER $,CREATE PROCEDURE XSCJ.RESULT,(IN,str,VARCHAR(4), OUT sex VARCHAR(4) ),BEGIN,CASE,str,WHEN 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=,男,;,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,DO,statement_list,END WHILE ,end_label,说明：语句首先判断,search_condition,是否为真，不为真则执行,statement_list,中的语句，然后再次进行判断，为真则继续循环，不为真则结束循环。,begin_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 = v1,1;,END WHILE;,END$,DELIMITER ;,7.1.2,存储过程体,说明：当调用这个存储过程时，首先判断,v1,的值是否大于零，如果大于零则执行,v1,1,，否则结束循环。,REPEAT,语句格式如下：,begin_label,: REPEAT,statement_list,UNTIL,search_condition,END REPEAT ,end_label,说明：,REPEAT,语句首先执行,statement_list,中的语句，然后判断,search_condition,是否为真，为真则停止循环，不为真则继续循环。,REPEAT,也可以被标注。,【,例,7.10,】,用,REPEAT,语句创建一个如例,7.9,的存储过程。程序片段如下：,REPEAT,v1=v1,1;,UNTIL v11;,END REPEAT;,说明：,REPEAT,语句和,WHILE,语句的区别在于：,REPEAT,语句先执行语句，后进行判断；而,WHILE,语句是先判断，条件为真时才执行语句。,7.1.2,存储过程体,LOOP,语句语法格式如下：,begin_label,: LOOP,statement_list,END LOOP ,end_label,说明：,LOOP,允许某特定语句或语句群的重复执行，实现一个简单的循环构造，,statement_list,是需要重复执行的语句。在循环内的语句一直重复至循环被退出，退出时通常伴随着一个,LEAVE,语句。,LEAVE,语句经常和,BEGIN.END,或循环一起使用。结构如下：,LEAVE label,label,是语句中标注的名字，这个名字是自定义的。加上,LEAVE,关键字就可以用来退出被标注的循环语句。,【,例,7.11,】,创建一个带,LOOP,语句的存储过程。,DELIMITER $,CREATE PROCEDURE,doloop,(),BEGIN,SET a=10;,Label: LOOP,SET a=a,1;,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;,结果为：,可以看到，用户变量,a,的值已经变成,1,了。,循环语句中还有一个,ITERATE,语句，它只可以出现在,LOOP,、,REPEAT,和,WHILE,语句内，意为“再次循环”。它的格式为：,ITERATE label,说明：该语句格式与,LEAVE,差不多，区别在于：,LEAVE,语句是离开一个循环，而,ITERATE,语句是重新开始一个循环。,7.1.2,存储过程体,在存储过程中处理,SQL,语句可能导致一条错误消息。例如，向一个表中插入新的行而主键值已经存在，这条,INSERT,语句会导致一个出错消息，并且,MySQL,立即停止对存储过程的处理。每一个错误消息都有一个唯一代码和一个,SQLSTATE,代码。例如，,SQLSTATE 23000,属于如下的出错代码：,Error 1022, Cant,write;duplicate,key in table,Error 1048, Column cannot be null,Error 1052, Column is ambiguous,Error 1062, Duplicate entry for key,MySQL,手册的“错误消息和代码”一章中列出了所有的出错消息及它们各自的代码。,为了防止,MySQL,在一条错误消息产生时就停止处理，需要使用到,DECLARE HANDLER,语句。,DECLARE HANDLER,语句为错误代码声明了一个所谓的处理程序，它指明：对一条,SQL,语句的处理如果导致一条错误消息，将会发生什么。,DECLARE HANDLER,语法格式为：,DECLARE,handler_type,HANDLER FOR,condition_value,.,sp_statement,7.1.2,存储过程体,其中，,handler_type,为：,CONTINUE,| EXIT,| UNDO,condition_value,为：,SQLSTATE VALUE,sqlstate_value,|,condition_name,| SQLWARNING,| NOT FOUND,| SQLEXCEPTION,|,mysql_error_code,说明：,handler_type,：处理程序的类型，主要有三种：,CONTINUE,、,EXIT,和,UNDO,。对,CONTINUE,处理程序，,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,存储过程体,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 23000 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,代码中的一条。接下来执行处理程序的附加语句（,SET x2=1,）。此后，,MySQL,检查处理程序的类型，这里的类型为,CONTINUE,，因此存储过程继续处理，将用户变量,x,赋值为,3,。如果这里的,INSERT,语句能够执行，处理程序将不被激活，用户变量,x2,将不被赋值。,注意：不能为同一个出错消息在同一个,BEGIN-END,语句块中定义两个或更多的处理 程序。,为了提高可读性，可以使用,DECLARE CONDITION,语句为一个,SQLSTATE,或出错代码定义一个名字，并且可以在处理程序中使用这个名字。,DECLARE CONDITION,语法格式为：,DECLARE,condition_name,CONDITION FOR,condition_value,其中，,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_UNIQUE 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,（打开游标）、,FETCH CURSOR,（读取游标）和,CLOSE CURSOR,（关闭游标）。,如果使用了,DECLARE CURSOR,语句声明了一个游标，这样就把它连接到了一个由,SELECT,语句返回的结果集中。使用,OPEN CORSOR,语句打开这个游标。接着，可以用,FETCH CURSOR,语句把产生的结果一行一行地读取到存储过程或存储函数中去。游标相当于一个指针，它指向当前的一行数据，使用,FETCH CORSOR,语句可以把游标移动到下一行。当处理完所有的行时，使用,CLOSE CURSOR,语句关闭这个游标。,（,1,）声明游标,语法格式：,DECLARE,cursor_name,CURSOR FOR,select_statement,说明：,cursor_name,是游标的名称，游标名称使用与表名同样的规则。,select_statement,是一个,SELECT,语句，返回的是一行或多行的数据。,这个语句声明一个游标，也可以在存储过程中定义多个游标，但是一个块中的每一个游标必须有唯一的名字。,注意：这里的,SELECT,子句不能有,INTO,子句。,7.1.2,存储过程体,下面的定义符合一个游标声明：,DECLARE XS_CUR1 CURSORFOR,SELECT,学号,姓名,性别,出生日期,总学分,FROM XS,WHERE,专业名,= ,计算机,;,注意：游标只能在存储过程或存储函数中使用，例中语句无法单独运行。,（,2,）打开游标,声明游标后，要使用游标从中提取数据，就必须先打开游标。在,MySQL,中，使用,OPEN,语句打开游标，其格式为：,OPEN,cursor_name,在程序中，一个游标可以打开多次，由于其他的用户或程序本身已经更新了表，所以每次打开结果可能不同。,（,3,）读取数据,游标打开后，就可以使用,FETCHINTO,语句从中读取数据。,语法格式：,FETCH,cursor_name,INTO,var_name,var_name, .,说明：,FETCH .INTO,语句与,SELECT.INTO,语句具有相同的意义，,FETCH,语句是将游标指向的一行数据赋给一些变量，子句中变量的数目必须等于声明游标时,SELECT,子句中列的数目。,var_name,是存放数据的变量名。,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,学号,FROM XS;,DECLARE CONTINUE HANDLER FOR NOT FOUND,SET 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,函数来解决，这里只是为了说明如何使用一个游标而已。,7.1.3,调用存储过程,存储过程创建完后，可以在程序、触发器或者存储过程中被调用，但是都必须使用到,CALL,语句，前面已经简单地介绍了,CALL,语句的形式，本节重点介绍它。,语法格式：,CALL,sp_name(parameter,.),说明：,sp_name,为存储过程的名称，如果要调用某个特定数据库的存储过程，则需要在前面加上该数据库的名称。,parameter,为调用该存储过程使用的参数，这条语句中的参数个数必须总是等于存储过程的参数个数。,【,例,7.15,】,创建存储过程，实现查询,XS,表中学生人数的功能，该存储过程不带参数。,USE XSCJ;,CREATE PROCEDURE 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,】,创建一个存储过程，有两个输入参数：,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,课程号,=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,课程号,=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 BIT(1), OUT STR CHAR(8),BEGIN,CALL DO_INSERT1();,IF X=0 THEN,UPDATE XS SET,姓名,=,刘英,性别,=0 WHERE,学号,=091101;,SET STR=,修改成功,;,ELSEIF X=1 THEN,DELETE 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,;,结果为,：,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,语句可以修改存储过程的某些特征。,语法格式为：,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,中的存储过程。,DELIMITER $,DROP PROCEDURE IF EXISTS DO_QUERY;,CREATE PROCEDURE DO_QUERY(),BEGIN,SELECT * FROM XS;,END$,DELIMITER ;,7.2,存储函数,存储函数也是过程式对象之一，与存储过程很相似。它们都是由,SQL,和过程式语句组成的代码片断，并且可以从应用程序和,SQL,中调用。然而，它们也有一些区别：,（,1,）存储函数不能拥有输出参数，因为存储函数本身就是输出参数；,（,2,）不能用,CALL,语句来调用存储函数；,（,3,）存储函数必须包含一条,RETURN,语句，而这条特殊的,SQL,语句不允许包含于存储过程中。,7.2.1,创建存储函数,创建存储函数使用,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,子句声明函数返回值的数据类型。,routine_body,是存储函数的主体，也叫存储函数体，所有在存储过程中使用的,SQL,语句在存储函数中也适用，包括流程控制语句、游标等。但是存储函数体中必须包含一个,RETURN value,语句，,value,为存储函数的返回值。这是存储过程体中没有的。,下面举一些存储函数的例子。,【,例,7.21,】,创建一个存储函数，它返回,XS,表中学生的数目作为结果。,DELIMITER $,CREATE FUNCTION NUM_OF_XS(),RETURNS INTEGER,BEGIN,RETURN (SELECT COUNT(*) FROM XS);,END$,DELIMITER ;,7.2.1,创建存储函数,说明：,RETURN,子句中包含,SELECT,语句时，,SELECT,语句的返回结果只能是一行且只能有一列值。,【,例,7.22,】,创建一个存储函数，返回某个学生的姓名。,DELIMITER $,CREATE FUNCTION NAME_OF_STU(XH CHAR(6),RETURNS CHAR(8),BEGIN,RETURN (SELECT,姓名,FROM XS WHERE,学号,=XH);,END$,DELIMITER ;,【,例,7.23,】,创建一个存储函数来删除,XS_KC,表中有但,XS,表中不存在的学号。,DELIMITER $,CREATE FUNCTION DELETE_STU(XH CHAR(6),RETURNS BOOLEAN,BEGIN,DECLARE STU CHAR(6);,SELECT,姓名,INTO STU FROM XS WHERE,学号,=XH;,IF STU IS NULL THEN,DELETE 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();,结果为：,【,例,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),BEGIN,DECLARE NAME CHAR(8);,SELECT NAME_OF_STU(XH) INTO NAME;,IF NAME= ,王林, THEN,RETURN(SELECT,出生日期,FROM XS WHERE,学号,=XH);,ELSE,RETURN FALSE;,END IF;,END$,DELIMITER ;,7.2.2,调用存储函数,接着调用存储函数,IS_STU,查看结果：,SELECT IS_STU(081102);,结果为：,SELECT IS_STU(081101);,结果为：,7.2.3,删除存储函数,删除存储函数的方法与删除存储过程的方法基本一样，都使用,DROP FUNCTION,语句。,语法格式为：,DROP FUNCTION IF EXISTS,sp_name,【,例,7.27,】,删除例,7.21,中的存储函数,NUM_OF_XS,。,DROP FUNCTION IF EXISTS NUM_OF_XS;,7.2.4,修改存储函数,使用,ALTER FUNCTION,语句可以修改存储函数的特征。,语法格式为：,ALTER FUNCTION,sp_name,characteristic .,characteristic,的格式已经在前面,7.1.1,节中介绍过。,当然，要修改存储函数的内容则要采用先删除后定义的方法。,7.2.5,界面方式操作存储函数与存储过程,通过,MySQL,Administrator,工具也可以对存储过程与存储函数进行操作。主要有以下 步骤。,（,1,）打开,MySQL,Administrator,，单击,Catalogs,下的,XSCJ,数据库，在右边的框内单击 “,Stored Procedures”,栏，如图,7.1,所示。,图,7.1,存储过程,7.2.5,界面方式操作存储函数与存储过程,（,2,）创建存储过程和存储函数：单击图,7.1,下方的“,Create Stored Proc”,按钮，弹出如图,7.2,所示对话框。,图,7.2,名称输入框,（,3,）输入名称后，单击“,Create PROCEDURE”,按钮创建存储过程，单击“,Create FUNCTION”,按钮创建存储函数。之后会弹出一个文本框，在其中输入存储过程或存储函数代码，输入完成后单击“,Execute SQL”,按钮即可。,（,4,）存储过程或存储函数创建完毕后，图,7.1,所示的存储过程列表中会出现所创建的存储过程或存储函数，此时，选择存储函数或存储过程，单击“,Edit Stored Proc”,按钮可以进行修改，单击“,Drop Stored Proc”,按钮可以删除存储过程或存储函数。,7.3,触发器,7.3.1,创建触发器,创建触发器使用,CREATE TRIGGER,语句，要查看数据库中有哪些触发器可以使用,SHOW TRIGGERS,命令。,CREATE TRIGGER,语法格式：,CREATE TRIGGER,trigger_name,trigger_time,trigger_event,ON,tbl_name,FOR EACH ROW,trigger_stmt,说明：,trigger_name,：触发器的名称，触发器在当前数据库中必须具有唯一的名称。如果要在某个特定数据库中创建，名称前面应该加上数据库的名称。,trigger_time,：触发器触发的时刻，有两个选项：,AFTER,和,BEFORE,，以表示触发器是在激活它的语句之前或之后触发。如果想要在激活触发器的语句执行之后执行几个或更多的改变，通常使用,AFTER,选项；如果想要验证新数据是否满足使用的限制，则使用,BEFORE,选项。,trigger_event,：触发事件，指明了激活触发程序的语句的类型。,trigger_event,可以是下述值之一：,INSERT,：将新行插入表时激活触发器。例如，通过,INSERT,、,LOAD DATA,和,REPLACE,语句。,7.3.1,创建触发器,UPDATE,：更改某一行时激活触发器。例如，通过,UPDATE,语句。,DELETE,：从表中删除某一行时激活触发器。例如，通过,DELETE,和,REPLACE,语句。,tbl_name,：与触发器相关的表名，在该表上发生触发事件才会激活触发器。同一个表不能拥有两个具有相同触发时刻和事件的触发器。例如，对于某一表，不能有两个,BEFORE UPDATE,触发器，但可以有,1,个,BEFORE UPDATE,触发器和,1,个,BEFORE INSERT,触发器，或,1,个,BEFORE UPDATE,触发器和,1,个,AFTER UPDATE,触发器。,FOR EACH ROW,：这个声明用来指定，对于受触发事件影响的每一行，都要激活触发器的动作。例如，使用一条语句向一个表中添加一组行，触发器会对每一行执行相应触发器动作。,trigger_stmt,：触发器动作，包含触发器激活时将要执行的语句。如果要执行多个语句，可使用,BEGIN . END,复合语句结构。这样，就能使用存储过程中允许的相同语句。,注意：触发器不能返回任何结果到客户端，为了阻止从触发器返回结果，不要在触发器定义中包含,SELECT,语句。同样，也不能调用将数据返回客户端的存储过程。,7.3.1,创建触发器,【,例,7.28,】,创建一个表,table1,，其中只有一列,a,。在表上创建一个触发器，每次插入操作时，将用户变量,str,的值设为“,TRIGGER IS WORKING”,。,CREATE TABLE table1(a INTEGER);,CREATE TRIGGER table1_insert AFTER INSERT,ON table1 FOR EACH ROW,SET ,str,= TRIGGER IS WORKING ;,向,table1,中插入一行数据：,INSERT INTO table1 VALUES(10);,查看,str,的值：,SELECT ,str,;,结果为：,7.3.1