mysql 存储过程笔记

mysql 捕获异常

DROP PROCEDURE IF EXISTS sp_call_jobs;
CREATE PROCEDURE sp_call_jobs()
    NOT DETERMINISTIC
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
declare _row,_err,_count int default 0;
DECLARE CONTINUE  HANDLER FOR SQLEXCEPTION,SQLWARNING,NOT FOUND set _err=1;

set _err = 0;
while _row<3 DO
  START TRANSACTION;
     insert into t1(cond_val)values(null);
  COMMIT;
if _err=1 then
   set _count=_count+1;
end if;
set _row=_row+1;
end while;
select _count;
END;

delimiter 

告诉mysql解释器，该段命令是否已经结束了，mysql是否可以执行了

例子：

mysql> delimiter //

mysql> CREATE PROCEDURE simpleproc (OUT param1 INT)
-> BEGIN
-> SELECT COUNT(*) INTO param1 FROM t;
-> END;
-> //
Query OK, 0 rows affected (0.00 sec) 

1.   使用存储过程的优点有：

（1）存储过程在服务器端运行，执行速度快。

（2）存储过程执行一次后，其执行规划就驻留在高速缓冲存储器，在以后的操作中，只需从高速缓冲存储器中调用已编译好的二进制代码执行，提高了系统性能。

（3）确保数据库的安全。使用存储过程可以完成所有数据库操作，并可通过编程方式控制上述操作对数据库信息访问的权限。

2. 创建存储过程可以使用create procedure 语句。

要在MySQL 5.1中创建存储过程，必须具有CREATE routine权限。要想查看数据库中有哪些存储过程，可以使用SHOW PROCEDURE STATUS命令。要查看某个存储过程的具体信息，可使用SHOWCREATE 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的内置函数相同的名称，否则会发生错误。

 ●   proc_parameter：存储过程的参数，param_name为参数名，type为参数的类型，当有多个参数的时候中间用逗号隔开。存储过程可以 有0个、1个或多个参数。MySQL存储过程支持三种类型的参数：输入参数、输出参数和输入/输出参数，关键字分别是IN、OUT和INOUT。输入参数使数据可以传递给一个存储过程。当需要返回一个答案或结果的时候，存储过程使用输出参数。输入/ 输出参数既可以充当输入参数也可以充当输出参数。存储过程也可以不加参数，但是名称后面的括号是不可省略的 。

注意： 参数的名字不要等于列的名字，否则虽然不会返回出错消息，但是存储过程中的SQL语句会将参数名看做列名，从而引发不可预知的结果。

characteristic ：存储过程的某些特征设定，下面一一介绍 :

language sql ： 表明编写这个存储过程的语言为SQL 语言，目前来讲，MySQL存储过程还不能用外部编程语言来编写，也就是说，这个选项可以不指定。将来将会对其扩展，最有可能第一个被支持的语言是PHP 。

deterministic ： 设置为DETERMINISTIC表示存储过程对同样的输入参数产生相同的结果 ，设置为NOT DETERMINISTIC则表示会产生不确定的结果。默认为NOTDETERMINISTIC。

contains SQL ： 表示存储过程不包含读或写数据的语句 。NO SQL表示存储过程不包含SQL语句。reads SQL DATA表示存储过程包含读数据的语句，但不包含写数据的语句。modifies SQL DATA表示存储过程包含写数据的语句。如果这些特征没有明确给定，默认的是CONTAINS SQL 。

SQL SECURITY：SQL SECURITY特征可以用来指定存储过程使用创建该存储过程的用户（DEFINER）的许可来执行，还是使用调用者（INVOKER）的许可来执行。默认值是DEFINER。

COMMENT 'string'：对存储过程的描述，string为描述内容。这个信息可以用SHOWCREATE PROCEDURE语句来显示。

●   routine_body：这是存储过程的主体部分，也叫做存储过程体。里面包含了在过程调用的时候必须执行的语句，这个部分总是以begin 开始，以end结束。当然，当存储过程体中只有一个SQL 语句时可以省略BEGIN-END标志。

3.   在开始创建存储过程之前，先介绍一个很实用的命令，即delimiter 命 令。在MySQL中，服务器处理语句的时候是以分号为结束标志的。但是在创建存储过程的时候，存储过程体中可能包含多个SQL语句，每个SQL语句都是以 分号为结尾的，这时服务器处理程序的时候遇到第一个分号就会认为程序结束，这肯定是不行的。所以这里使用DELIMITER命令将MySQL语句的结束标 志修改为其他符号。

DELIMITER语法格式为：DELIMITER $$

说明：$$是用户定义的结束符，通常这个符号可以是一些特殊的符号，如两个“#”，一个“￥”、数字、字母等都可以。当使用DELIMITER命令时，应该避免使用反斜杠（“\” ）字符，因为那是MySQL的转义字符。

例：创建存储过程，实现的功能是删除一个特定学生的信息。

DELIMITER $$

CREATE PROCEDURE DELETE_STUDENT(IN XH CHAR(6))

BEGIN

DELETE FROM XS WHERE 学号=XH;

END $$

DELIMITER ;

说明：当调用这个存储过程时，MySQL根据提供的参数XH的值，删除对应在XS表中的数据。

在关键字BEGIN和END之间指定了存储过程体，当然，BEGIN-END复合语句还可以嵌套使用。

4.     局部变量

在存储过程中可以声明局部变量，它们可以用来存储临时结果。要声明局部变量必须使用declare语句。在声明局部变量的同时也可以对其赋一个初始值。

DECLARE语法格式：DECLARE var_name[,...] type [DEFAULT value]

说明：var_name为变量名；type为变量类型；default子句给变量指定一个默认值，如果不指定默认为NULL的话。可以同时声明多个类型相同的局部变量，中间用逗号隔开。

例： 声明一个整型变量和两个字符变量。

DECLARE num INT(4);

DECLARE str1, str2 VARCHAR(6);

declare n char(10) default ‘abcdefg’;

说明：局部变量只能在BEGIN…END 语句块中声明。

局部变量必须在存储过程的开头就声明 ，声明完后，可以在声明它的BEGIN…END语句块中使用该变量，其他语句块中不可以使用它。

在存储过程中也可以声明用户变量，不过千万不要将这两个混淆。局部变量和用户变量的区别在于：局部变量前面没有使用@ 符号，局部变量在其所在的BEGIN…END语句块处理完后就消失了，而用户变量存在于整个会话当中。

5.    使用SET 语句赋值

要给局部变量赋值可以使用SET语句，SET语句也是SQL本身的一部分。语法格式为：SET  var_name = expr [,var_name = expr] ...

例: 在存储过程中给局部变量赋值。

SET num=1, str1= 'hello';

说明：与声明用户变量时不同，这里的变量名前面没有@符号。注意，例中的这条语句无法单独执行，只能在存储过程和存储函数中使用。

6.   SELECT...INTO语句（重点）

使用这个SELECT…INTO语法可以把选定的列值直接存储到变量中。 因此，返回的结果只能有一行。语法格式为：

SELECT col_name[,...] INTO var_name[,...]  table_expr

说明：col_name是列名，var_name是要赋值的变量名。table_expr是SELECT语句中的FROM子句及后面的部分，这里不再叙述。

例: 在存储过程体中将XS表中的学号为081101的学生姓名和专业名的值分别赋给变量name和project。

SELECT 姓名,专业名 INTO name, project

   FROMXS;  WHERE 学号= '081101';

7.  流程控制语句

在MySQL中，常见的过程式SQL语句可以用在一个存储过程体中。例如：IF语句、CASE语句、LOOP语句、WHILE语句、iterate语句和LEAVE语句。

（1 ）IF语句

IF-THEN-ELSE语句可根据不同的条件执行不同的操作。

语法格式为：

IF 判断的条件THEN 一个或多个SQL语句

[ELSEIF判断的条件THEN一个或多个SQL语句] ...

[ELSE一个或多个SQL语句]

END IF

说明：当判断条件为真时，就执行相应的SQL 语句。

IF语句不同于系统的内置函数IF()函数，IF()函数只能判断两种情况，所以请不要混淆。

例： 创建XSCJ数据库的存储过程，判断两个输入的参数哪一个更大。

DELIMITER $$

CREATE PROCEDURE XSCJ.COMPAR

(IN K1INTEGER, IN K2 INTEGER, OUT K3 CHAR(6) )

BEGIN

IF K1>K2 THEN

    SET K3= '大于';

ELSEIF K1=K2 THEN

    SET K3= '等于';

ELSE

    SET K3= '小于';

ENDIF ;

END$$

DELIMITER ;

说明：存储过程中K1和K2是输入参数，K3是输出参数。

（2 ）CASE语句

前面已经介绍过了，这里介绍CASE语句在存储过程中的用法，与之前略有不同。语法格式为：

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后面的语句。与第一种格式相比，这种格式能够实现更为复杂的条件判断，使用起来更方便。

例： 创建一个存储过程，针对参数的不同，返回不同的结果。

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='无';

   ENDCASE;

END$$

DELIMITER ;

例： 用第二种格式的CASE语句创建以上存储过程。程序片段如下：

CASE

   WHEN str='M' THEN SET sex='男';

   WHEN str='F' THEN SET sex='女';

   ELSE  SET sex='无';

END CASE;

（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不能被给出，并且如果两者都出现，它们的名字必须是相同的。

例: 创建一个带WHILE循环的存储过程。

DELIMITER $$

CREATE PROCEDURE dowhile()

BEGIN

   DECLARE v1 INT DEFAULT5;

   WHILE  v1 > 0 DO

         SET v1 = v1-1;

   END WHILE;

END $$

DELIMITER ;

●   repeat语句格式如下：

[begin_label:] REPEAT

     statement_list

UNTIL search_condition

END REPEAT [end_label]

说明：REPEAT语句首先执行statement_list中的语句，然后判断search_condition是否为真，为真则停止循环，不为真则继续循环。REPEAT也可以被标注。

例: 用REPEAT语句创建一个如例7.9的存储过程。程序片段如下：

REPEAT

    v1=v1-1;

    UNTIL v1<1;

END REPEAT;

说明：REPEAT 语句和WHILE语句的区别在于：REPEAT语句先执行 语句，后 进行判断 ；而WHILE语句是先判断 ，条件为真时才执行 语句。

●   LOOP语句语法格式如下：

[begin_label:] LOOP

         statement_list

END LOOP [end_label]

说明：LOOP允许某特定语句或语句群的重复执行，实现一个简单的循环构造，statement_list是需要重复执行的语句。在循环内的语句一直重复至循环被退出，退出时通常伴随着一个LEAVE 语句。

LEAVE 语句经常和BEGIN...END 或循环一起使用。结构如下：

LEAVE label ; label是语句中标注的名字，这个名字是自定义的。加上LEAVE关键字就可以用来退出被标注的循环语句。

例: 创建一个带LOOP语句的存储过程。

DELIMITER $$

CREATE PROCEDURE doloop()

BEGIN

    SET @a=10;

    Label: LOOP

          SET @a=@a-1;

          IF @a<0 THEN

              LEAVELabel;

          END IF;

    END LOOPLabel;

END$$

DELIMITER ;

循环语句中还有一个iterate 语句，它只可以出现在LOOP、REPEAT和WHILE语句内，意为“再次循环”。它的格式为：ITERATE label

说明：该语句格式与LEAVE 差不多，区别在于：LEAVE语句是离开一个循环 ，而ITERATE语句是重新开始 一个循环。

 

8. 我们调用此存储过程来查看最后结果。调用该存储过程使用如下命令：CALL doloop();

接着，查看用户变量的值：  SELECT@a;

   语法格式：CALL sp_name([parameter[,...]])

说明：sp_name为存储过程的名称，如果要调用某个特定数据库的存储过程，则需要在前面加上该数据库的名称。parameter为调用该存储过程使用的参数，这条语句中的参数个数必须总是等于存储过程的参数个数。

例：创建一个存储过程，有两个输入参数：XH和KCM，要求当某学生某门课程的成绩小于60分时将其学分修改为零，大于等于60分时将学分修改为此课程的学分。

DELIMITER $$

CREATE PROCEDURE XSCJ.DO_UPDATE(IN XHCHAR(6), IN KCM CHAR(16))

BEGIN

   DECLARE  KCH CHAR(3);

   DECLARE  XF TINYINT;

   DECLARE  CJ TINYINT;

 SELECT课程号, 学分 INTO KCH, XFFROM KC WHERE 课程名=KCM;

 SELECT成绩 INTO CJ FROM XS_KC WHERE 学号=XH AND 课程号=KCH;

   IF CJ<60 THEN

     UPDATE XS_KC SET 学分=0 WHERE 学号=XH AND 课程号=KCH;

   ELSE

     UPDATE XS_KC SET 学分=XF WHERE 学号=XH AND 课程号=KCH;

   END IF;

END$$

DELIMITER ;

9.        存储过程创建后需要删除时使用DROP PROCEDURE 语句。

在此之前，必须确认该存储过程没有任何依赖关系，否则会导致其他与之关联的存储过程无法运行。

语法格式为：  DROPPROCEDURE  [IF EXISTS] sp_name

说明：sp_name是要删除的存储过程的名称。IF EXISTS子句是MySQL的扩展，如果程序或函数不存在，它防止发生错误。

例： 删除存储过程dowhile：DROP PROCEDURE IF EXISTS dowhile;

10.  使用ALTER PROCEDURE 语句可以修改存储过程的某些特征。

语法格式为：ALTER PROCEDURE sp_name [characteristic ...]

其中，characteristic为：

{ CONTAINS SQL | NO SQL | READS SQLDATA | MODIFIES SQL DATA }

| SQL SECURITY { DEFINER | INVOKER }

| COMMENT 'string'

说明：characteristic是存储过程创建时的特征，在CREATE PROCEDURE语句中已经介绍过。只要设定了其中的值，存储过程的特征就随之变化。

如果要修改存储过程的内容，可以使用先删除再重新定义存储过程的方法。

例： 使用先删除后修改的方法修改例7.12中的存储过程。

DELIMITER $$

DROP PROCEDURE IF EXISTS DO_QUERY;

CREATE PROCEDURE DO_QUERY()

BEGIN

SELECT * FROM XS;

END$$

DELIMITER ;

   ***11  往后为选看内容。。非重点！！

11.  SQL语句中的错误提示

在存储过程中处理SQL语句可能导致一条错误消息。例如，向一个表中插入新的行而主键值已经存在，这条INSERT语句会导致一个出错消息，并且MySQL立即停止对存储过程的处理。每一个错误消息都有一个唯一代码和一个SQLSTATE 代码。例如，SQLSTATE 23000属于如下的出错代码：

Error 1022, "Can't write;duplicate（重复） key intable"

Error 1048, "Column cannot benull"

Error 1052, "Column is ambiguous（歧义）"

Error 1062, "Duplicate entry forkey"

MySQL手册的“错误消息和代码”一章中列出了所有的出错消息及它们各自的代码。

为了防止MySQL在一条错误消息产生时就停止处理，需要使用到DECLARE handler语句。该语句语句为错误代码声明了一个所谓的处理程序，它指明：对一条SQL语句的处理如果导致一条错误消息，将会发生什么。

DECLARE HANDLER语法格式为：

DECLARE handler_type HANDLER FOR condition_value[,...]sp_statement

其中，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代码的速记。当用户不想为每个可能的出错消息都定义一个处理程序时可以使用以上三种形式。

mysql_error_code是具体的SQLSTATE代码。除了SQLSTATE值，MySQL错误代码也被支持，表示的形式为：ERROR= 'xxxx'。

●   sp_statement：处理程序激活时将要执行的动作。

例： 创建一个存储过程，向XS表插入一行数据（'081101', '王民', '计算机', 1, '1990-02-10',50 , NULL, NULL），已知学号081101在XS表中已存在。如果出现错误，程序继续进行。

USE XSCJ;

DELIMITER $$

CREATE PROCEDURE MY_INSERT ()

BEGIN

   DECLARECONTINUE HANDLER FOR SQLSTATE '23000' SET @x2=1;

   SET@x=2;

   INSERTINTO XS VALUES('081101', '王民', '计算机', 1, '1990-02-10', 50 , NULL, NULL);

   SET@x=3;

END$$

DELIMITER ;

说明：在调用存储过程后，未遇到错误消息时处理程序未被激活，当执行 INSERT语句出现出错消息时，MySQL检查是否为这个错误代码定义了处理程序。如果有，则激活该处理程序，本例中，INSERT语句导致的错误消息 刚好是SQLSTATE代码中的一条。接下来执行处理程序的附加语句（SET @x2=1）。此后，MySQL检查处理程序的类型，这里的类型为CONTINUE，因此存储过程继续处理，将用户变量x赋值为3。如果这里的 INSERT语句能够执行，处理程序将不被激活，用户变量x2将不被赋值。

注意：不能为同一个出错消息在同一个BEGIN-END语句块中定义两个或更多的处理程序。

为了提高可读性，可以使用DECLARE CONDITION语句为一个SQLSTATE或出错代码定义一个名字，并且可以在处理程序中使用这个名字。

DECLARE CONDITION语法格式为：

DECLARE condition_name CONDITION FORcondition_value

其中，condition_value：

 SQLSTATE [VALUE] sqlstate_value

| mysql_error_code

说明：condition_name是处理条件的名称，condition_value为要定义别名的SQLSTATE或出错代码。

例： 修改上例中的存储过程，将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;

12.   游标

一条SELECT...INTO语句返回的是带有值的一行，这样可以 把数据读取到存储过程中。但是常规的SELECT语句返回的是多行数据，如果要处理它需要引入游标这一概念。MySQL支持简单的游标。在MySQL中， 游标一定要在存储过程或函数中使用，不能单独在查询中使用。

使用一个游标需要用到4条特殊的语句：DECLARE CURSOR（声明游标）、OPEN CURSOR（打开游标）、FETCH CURSOR（读取游标）和CLOSE CURSOR（关闭游标）。

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

（1 ）声明游标

语法格式：DECLARE cursor_name cursor for select_statement

说明：cursor_name是游标的名称，游标名称使用与表名同样的规则。select_statement是一个SELECT语句，返回的是一行或多行的数据。这个语句声明一个游标，也可以在存储过程中定义多个游标，但是一个块中的每一个游标必须有唯一的名字。

注意：这里的SELECT 子句不能有INTO子句。

下面的定义符合一个游标声明：

DECLARE XS_CUR1 CURSOR FOR

   SELECT 学号,姓名,性别,出生日期,总学分

       FROM XS

       WHERE 专业名 = '计算机';

注意：游标只能在存储过程或存储函数中使用，例中语句无法单独运行。

（2 ）打开游标

声明游标后，要使用游标从中提取数据，就必须先打开游标。在MySQL中，使用OPEN语句打开游标，其格式为：OPEN cursor_name

在程序中，一个游标可以打开多次，由于其他的用户或程序本身已经更新了表，所以每次打开结果可能不同。

（3 ）读取数据

游标打开后，就可以使用fetch…into语句从中读取数据。

语法格式：FETCH cursor_nameINTO var_name [, var_name] ...

说明：FETCH ...INTO语句与SELECT...INTO语句具有相同的意义，FETCH语句是将游标指向的一行数据赋给一些变量，子句中变量的数目必须等于声明游标时SELECT子句中列的数目。var_name是存放数据的变量名。

（4 ）关闭游标

游标使用完以后，要及时关闭。关闭游标使用CLOSE语句，格式为：

CLOSE cursor_name语句参数的含义与OPEN语句中相同。

例如： CLOSE XS_CUR2  将关闭游标XS_CUR2。

例： 创建一个存储过程，计算XS表中行的数目。

DELIMITER $$

CREATE PROCEDURE compute (OUT NUMBERINTEGER)

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 ;

 

 

创建函数：

1.   存储函数也是过程式对象之一，与存储过程很相似。

它们都是由SQL和过程式语句组成的代码片断，并且可以从应用程序和SQL中调用。然而，它们也有一些区别：

（1）存储函数不能拥有输出参数，因为存储函数本身就是输出参数 ；

（2）不能用CALL 语句来调用存储函数；

（3）存储函数必须包含一条RETURN 语句，而这条特殊的SQL语句不允许包含于存储过程中。

2. 创建存储函数使用CREATE  FUNCTION 语句。

要查看数据库中有哪些存储函数，可以使用show function satus 命令(与存储过程类似)。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为存储函数的返回值。这是存储过程体中没有的。

例1： 创建一个存储函数，它返回XS表中学生的数目作为结果。

DELIMITER $$

CREATE FUNCTION NUM_OF_XS()

RETURNS INTEGER

BEGIN

   RETURN (SELECT COUNT(*)FROM XS);

END$$

DELIMITER ;

例2: 创建一个存储函数来删除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_KCWHERE 学号=XH;

    RETURN TRUE;

ELSE

    RETURN FALSE;

END IF;

END$$

DELIMITER ;

3.    调用创建的函数

存储函数创建完后，就如同系统提供的内置函数（如version()），所以调用存储函数的方法也差不多，都是使用SELECT关键字。

语法格式为： SELECT sp_name ([func_parameter[,...]])

例： 调用例1中的存储函数　：SELECT NUM_OF_XS();

存储函数中还可以调用另外一个存储函数或者存储过程。

例：创建一个存储函数，通过调用存储函数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 ;

4.  删除与修改创建的函数

删除存储函数的方法与删除存储过程的方法基本一样，都使用DROP FUNCTION 语句。

语法格式为：  DROPFUNCTION [IF EXISTS] sp_name

例： 删除例1中的存储函数NUM_OF_XS。

DROP FUNCTION IF EXISTS NUM_OF_XS;

同样也是使用ALTER FUNCTION 语句可以修改存储函数的特征。

语法格式为： ALTER FUNCTION sp_name [characteristic ...]

当然，要修改存储函数的内容则要采用先删除后定义的方法。

触发器

1.    创建触发器

创建触发器使用CREATEtrigger 语句，要查看数据库中有哪些触发器可以使用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选项。在MySQL中区别不明显，before跟after用法差不多。

●  trigger_event ： 触发事件，指明了激活触发程序的语句的类型。trigger_event可以是下述值之一：

INSERT ：将新行插入表时激活触发器。例如，通过INSERT 、LOAD DATA和REPLACE语句。

UPDATE ：更改某一行时激活触发器。例如，通过UPDATE 语句。

DELETE ：从表中删除某一行时激活触发器。例如，通过DELETE 和REPLACE语句。

●  tbl_name ：与触发器相关的表名，在该表上发生触发事件才会激活触发器。同一个表不能拥有两个具有相同触发时刻和事件的触发器。例如，对于某一表，不能有两个BEFORE UPDATE 触发器，但可以有1个BEFORE UPDATE触发器和1个BEFOREINSERT触发器，或1个BEFORE UPDATE触发器和1个AFTER UPDATE触发器。

●  FOR EACH ROW ：这个声明用来指定，对于受触发事件影响的每一行，都要激活触发器的动作。例如，使用一条语句向一个表中添加一组行，触发器会对每一行执行相应触发器动作。

●   trigger_stmt：触发器动作，包含触发器激活时将要执行的语句。如果要执行多个语句， 可使用BEGIN... END 复合语句结构。这样，就能使用存储过程中允许的相同语句。

注意：触发器不能返回任何结果到客户端，为了阻止从触发器返回结果， 不要在触发器定义中包含SELECT 语句。同样，也不能调用将数据返回客户端的存储过程。

2.     new.列名、old. 列名用法

在MySQL 触发器中的SQL语句可以关联表中的任意列。但不能直接使用列的名称去标志，那会使系统混淆，因为激活触发器的语句可能已经修改、删除或添加了新的列名，而列的旧名同时存在。因此必须用这样的语法来标志：“NEW.column_name” 或者“OLD.column_name”。 NEW.column_name 用来引用新行的一列，OLD.column_name 用来引用更新或删除它之前的已有行的一列。

对于INSERT 语句，只有NEW是合法的；对于DELETE语句，只有OLD才合法；而UPDATE语句可以与NEW或OLD同时使用。

例： 创建一个触发器，当删除表XS 中某个学生的信息时，同时将XS_KC表中与该学生有关的数据全部删除。

DELIMITER $$

CREATE TRIGGERXS_DELETE AFTER DELETE

   ON XS FOR EACH ROW

BEGIN

   DELETE FROM XS_KC WHERE学号=OLD. 学号;

END$$

DELIMITER ;

现在验证一下触发器的功能：DELETE FROM XS WHERE 学号='081101';

使用SELECT 语句查看XS_KC表中的情况：SELECT * FROM XS_KC;

注意：当触发器涉及对触发表自身的更新操作时，只能使用BEFORE ，AFTER触发器将不被允许。

3. 删除触发器

和其他数据库对象一样，使用DROP 语句即可将触发器从数据库中删除。语法格式：DROP TRIGGER[schema_name.]trigger_name

说明：trigger_name ：指要删除的触发器名称。schema_name为所在数据库的名称，如果在当前数据库，可以省略。

例： 删除触发器XS_DELETE ：   DROP TRIGGERXS_DELETE;