快上网专注成都网站设计 成都网站制作 成都网站建设
成都网站建设公司服务热线：028-86922220

网站建设知识

十年网站开发经验 + 多家企业客户 + 靠谱的建站团队

量身定制 + 运营维护+专业推广+无忧售后，网站问题一站解决

怎么理解MySQL存储过程和触发器

这期内容当中小编将会给大家带来有关怎么理解MySQL存储过程和触发器，文章内容丰富且以专业的角度为大家分析和叙述，阅读完这篇文章希望大家可以有所收获。

成都网站建设、成都做网站的关注点不是能为您做些什么网站，而是怎么做网站，有没有做好网站，给创新互联一个展示的机会来证明自己，这并不会花费您太多时间，或许会给您带来新的灵感和惊喜。面向用户友好，注重用户体验，一切以用户为中心。

存储过程(stored procedure SP)是MySQL 5.0 版本中的最大创新。他们是一些由MySQL服务器直接存储和执行的定制过程 或 函数。SP的加入把SQL语言扩展成了一种程序设计语言，可以利用SP把一个客户--服务器体系的数据库应用软件中的部分逻辑保存起来供日后使用。

触发器(trigger) 是在INSERT ,UPDATE 或 DELETE 命令之前或者之后对SQL命令或SP的自动自动调用。

----------------------------------------
//输入都必须以 '$$'作为结束符号
delimiter $$

我们先来创建一个最简单的函数,
函数的功能是写入两个数，得出 和:

Create FUNCTION addition(v1 int(11),v2 int(11))
RETURNS int(11)
BEGIN

return (v1+v2);

END$$


//把结束符号换回来
delimiter ;


让我们来调用我们的函数:
select addition(11,15) ;  结果 26 正确。


让我们来查看一下数据库中有那些函数
show function status;

如何来查看addition的代码？
show create function addition;


现在让我们来删除那个函数：
drop function addition;


----------------------------------------


上面是小试牛刀。 现在开始我们来全面学习MYSQL中的存储过程 和 触发器


分3个类 FUNCTION , PROCEDURE ,TRIGGER 来学习研究。

&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
                                             FUNCTION
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&

语法 :

CREATE FUNCTION function_name (param1 datatype [,param2 datatype ,.....])
RETURNS datatype

BEGIN
commands;
END


---------------------------------------------------------------------------

SP 注释

"--" 开始并且一直到这一行的结尾都是注释

------------------------------------------


(1)FUNCTION 中的局部变量的定义 和 变量的赋值

* 变量的定义
语法 :
DECLARE varname1 datatype1 [DEFAULT value];
DECLARE varname1,varname2 .... datatype [DEFAULT value]; //多变量同类型的定义方式

$ 变量的定义要在BEGIN ... END  之间定义。
$ 如果在FUNCTION 体中定义了多个BEGIN ... END 嵌套，那么 变量的定义只能在当前 BEGIN .. END 或则 子 BEGIN ... END 中有效。
$ 子类 BEGIN ... END 中的变量定义可以覆盖父类 BEGIN ... END 中定义的变量。

* 对变量的赋值
对变量的赋值有两种方法。

[1]直接给变量赋给常量，或则把其他的变量赋值给当前变量
  set var = value;
 set var1 = value1,var2 = value2....;

[2]把SQL查询结果赋值给变量
 SELECT var := value  //一种以SELECT 方法 把常量或其他变量赋值给当前变量的方法
 SELECT nomalvalue INTO var  //又一种以SELECT 方法 把常量或其他变量赋值给当前变量的方法
 SELECT value FROM TABLE .. INTO var;
 SELECT value1,value2 FROM TABLE .. INTO var1,var2;


SELECT INTO 命令是SELECT 命令的一种变体。 它上一以 INTO varname 结束整条命令。
要求，SELECT命令返回并且只能返回一条记录。(不允许多条记录)


example-001:

use wyd

delimiter $$

----------------------
create table person(
id int primary key auto_increment,
age int
) $$

----------------------
insert into person(age)values(12);
insert into person(age)values(34);
insert into person(age)values(42);
insert into person(age)values(13);
insert into person(age)values(2)$$

-----------------------
drop function addtion$$

----------------------

CREATE FUNCTION getage(person_id int)
RETURNS int
BEGIN
DECLARE person_age int default 0;
SELECT age FROM person WHERE id = person_id INTO person_age;

RETURN person_age;

END$$
----------------------
test the result:

select getage(1)$$ --> result = 12
select getage(2)$$ --> result = 34

运行正常

------------------------------------------------

 

&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&

(2)FUNCTION 中的分支

[1] IF - THEN - ELSE 分支
 语法 :
 IF comdition THEN
  commands;
 END IF;
 -------------------------
 IF comdition THEN
  commands;
 ELSE
  commands
 END IF;
 -------------------------
嵌套 IF comdition THEN
  commands;
 ELSE  IF comdition THEN
   commands;
  [ELSE commands;]
  END IF;
 END IF;
 -------------------------
 
 
[2] CASE 分支
语法:
--------------------------------
CASE expression

WHEN value1 THEN commands;

WHEN value2 THEN commands;

.......

WHEN value_n THEN commands;
 
ELSE commands;

END CASE;
--------------------------------

example-002:

CREATE FUNCTION personstate (age int)
RETURNS varchar(30)
BEGIN
 DECLARE personstate varchar(30) DEFAULT "UNKNOWN";
 IF age < 0 THEN SET personstate = "UNBORN";
 ELSE
  SET personstate = "BORN";
 END IF;  
RETURN personstate;

END$$

-----------------
select personstate(-3)   ----> result = UNBORN;
select personstate(3)    ----> result =  BORN;



------------------------------------


 example-003:

DROP FUNCTION  personstate$$

CREATE FUNCTION personstate (age int)
RETURNS varchar(30)
BEGIN
 DECLARE personstate varchar(30) DEFAULT "UNKNOWN";
 IF age < 0 THEN SET personstate = "UNBORN";
 ELSE
  IF age >0 && age<=14 THEN SET personstate ="CHILD"; END IF;
  IF age >14 && age <=22 THEN SET personstate ="YANG"; END IF;
  IF age >22 && age<60 THEN SET personstate = "STRONG"; END IF;
IF age >60 THEN SET personstate = "OLD"; END IF;
 END IF;  
RETURN personstate;

END$$

---------

select personstate(-3)$$ result = UNBORN
select personstate(5)$$ result = CHILD
select personstate(16)$$ result = YANG
select personstate(28)$$ result = STRONG
select personstate(66)$$ result = OLD


TEST IS OK.

----------------------------------------
example-004:

DROP FUNCTION showIn$$

CREATE FUNCTION showIn( valueIn int)
RETURNS VARCHAR(50)
BEGIN

DECLARE str varchar(30) DEFAULT "UNKNOWN";

 CASE valueIn
 
 WHEN 1 THEN SET str = "you input is 1";
 WHEN 2 THEN SET str = "you input is 2";
 WHEN 3 THEN SET str = "you input is 3";
 WHEN 4 THEN SET str = "you input is 4";
 WHEN 5 THEN SET str = "you input is 5";
 
 ELSE SET str = "you input is not 1,2,3,4,5";
 
 END CASE;
RETURN str;
 
END$$

-------------------

select showIn(1)$$  result = you input is 1
select showIn(2)$$  result = you input is 2
select showIn(6)$$  result = you input is not 1,2,3,4,5

TEST IS OK


----------------------------------------



&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&

(3)FUNCTION 中的循环

[1] REPEAT-UNTIL 循环
[2] WHILE 循环
[3]  LOOP 循环


*　REPEAT-UTIL　循环

语法:
   [loopname:] REPEAT
    commands;
   UNTIL condition
   END REPEAT [loopname];
   
   说明：
   和 do {} while(condition)　语句的功能一样。先运行，后判断。
   当condition 为true的时候　放弃循环　
   
   
 * WHILE　循环
 
 　语法:
 
   [loopname :] WHILE condition DO
    commands;
   END WHILE [loopname];
 
   说明: 和　while(condition) {commands; }　语句功能一样。 先判断，后执行。
   当condition 为 false 的时候　放弃循环
   
 
 * LOOP 循环
 
 　语法:
   
   loopname: LOOP
    commands;
    IF condition THEN LEAVE loopname ; END IF;
   END LOOP loopname;
   
   说明　: 这是一个没有条件判断的循环。可以认为是一个死循环。
   除非执行LEAVE　命令来跳出循环，否则循环将永远被执行。
   
 
 * LEAVE
 
   语法:
   LEAVE loopname ;
   
   说明:
   LEAVE loopname 命令见是程序代码的执行流程跳出并且结束一个循环。
   LEAVE loopname 命令还可以用来提前退出BEGIN - END 语句块。
   LEAVE loopname 命令相当于 C 或则 JAVA中 跳出循环的 BREAK  命令
   
   
 * ITERATE
 
   语法:
   ITERATE loopname ;
   
   说明:
   TERATE loopname 命令是跳出当次循环，接下来执行下一次循环。
   TERATE loopname 命令只能在循环体内运行。
   TERATE loopname 命令相当于 C 或则 JAVA中 跳出循环的 CONTINUE  命令
   
-------------------------

example-005:

  DROP FUNCTION getString$$

CREATE FUNCTION getString(number int(11))
  RETURNS VARCHAR(50)
  BEGIN
   declare str varchar(50) default '';
   declare i int default 0;
   
   myloop: REPEAT
    SET i = i+1;
    set str = concat(str,"*");
   
   UNTIL i>=number
   END REPEAT myloop;
  RETURN str;
  END $$
 
  -------------
  select getString(3)$$
  select getString(4)$$
  select getString(8)$$
 
  TEST IS OK
 
 

-------------------------

example-006:

  DROP FUNCTION getString$$
 
  CREATE FUNCTION getString(number int(11))
  RETURNS VARCHAR(50)
  BEGIN
 
   declare str varchar(50) default "";
   declare i int default 0;
   myloop: WHILE iset i = i+1;
    set str = concat(str,"@");
   END WHILE myloop;
   
   return str;
   
  END$$
 
  --------
  select getString(3)$$
  select getString(4)$$
  select getString(8)$$
 
  TEST IS OK
 
  -------------------------
  example-007:
 
  DROP FUNCTION getString$$
 
  CREATE FUNCTION getString(number int(11))
  RETURNS VARCHAR(50)
  BEGIN
   declare str varchar(50) default "";
   declare i int default 0;
   
   myloop:LOOP
    set i = i+1;
    IF i>number THEN LEAVE myloop; END IF;
   
    set str= concat(str,"# ");
   
   END LOOP myloop;
   
   RETURN str;
 
  END$$
 
  ---------
  select getString(3)$$
  select getString(4)$$
  select getString(8)$$
 
  TEST IS OK
 
  -----------------------------
  -----------------------------
 
  example-iterate :
 
  DROP FUNCTION getString$$
 
  CREATE FUNCTION getString(number int(11))
  RETURNS VARCHAR(50)
  BEGIN
   declare str varchar(50) default "";
   declare i int default 0;
   
   myloop:LOOP
    set i = i+1;
    IF i%2 = 0 THEN ITERATE myloop ; END IF;
    IF i>number THEN LEAVE myloop; END IF;
   
   
    set str= concat(str,"# ");
   
   END LOOP myloop;
   
   RETURN str;
 
  END$$
 
 
     
 
 
   
   &&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
               基本语法规则   <摘录mysql 5.0="" p="" 296-297="">
   &&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&

通过对FUNCTION的了解，我们已经对SP的语法规则有了大体的了解。
在FUNCTION 中的变量定义规则，变量赋值规则，分支规则 和 循环规则 同样也适用于 PROCEDURE。

现在我们来对SP的语法规则进行规范的认识:

# 分号 (;) 。 同一个SP可以包含任意多条SQL命令。这些命令必须用分号格开，就连分支和循环的控制结构也必须用分号结束。

# BEGIN - END 。 没有落在SP关键字之间(如 THEN 和 END IF 之间) 的多条SQL命令必须放在关键字BEGIN 和 END 之间。
 这就意味着由多条SQL命令构成的SP的代码都必须以BEGIN开始,以END结束。

# 换行符。 换行符在SP代码中的语意效果与空格字符相同。这意味着把 IF-THEN-ELSE-END-IF 结构连续写在同一行或分开写在多行上都是可以的。

# 变量 。 供SP内部使用的局部变量 和 局部参数不加 "@" 前缀。 在SP内允许使用普通的SQL变量，但是他们必须加上"@"前缀。
  (加"@"前缀的变量是普通全局变量。对变量疑问，可以参考 《MYSQL变量》 这个部分。)
 
# 字母大小写情况。 SP 在定义 和调用时均不分字母大小写情况。它写成(比如说)shorten , SHORTEN , Shorten 的效果都是一样的。

# 特殊字符。 在SP中避免使用特殊字符。 总之MYSQL对特殊字符的支持还不是很好。

# 注释。 "--" 开始并且一直到这一行的结尾都是注释





    &&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
                                         查看和删除SP的方法
    &&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&

(1) 查看FUNCTION
 show function status
 
 show CREATE FUNCTION functionname
 
 select routine_name ,routine_type,routine_schema,created from information_schema.routines where routine_schema = '你的数据库名' and routine_type='FUNCTION'
 
 例如:
 select routine_name ,routine_type,routine_schema,created from information_schema.routines where routine_schema = 'WYD' and routine_type='function'
 
 
 DROP FUNCTION [IF EXISTS] function_name
 
(2) 查看PROCEDURE

 SHOW PROCEDURE STATUS
 
 show CREATE PROCEDURE functionname
 
 select routine_name ,routine_type,routine_schema,created from information_schema.routines where routine_schema = '你的数据库名' and routine_type='PROCEDURE'
 
 例如:
 select routine_name ,routine_type,routine_schema,created from information_schema.routines where routine_schema = 'WYD' and routine_type='PROCEDURE'
 
 
 DROP PROCEDURE [IF EXISTS] procedure_name

(3) 查看所有SP
 desc information_schema.routines
 
 select routine_name ,routine_type,routine_schema,created from information_schema.routines
 
 



    &&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
                                                    MYSQL变量
    &&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
   
    MYSQL允许人们把简单的值(离散值,不是象SELECT查询结果那样的集合或列表)保存在变量里。在日常应用里，需要用到MySQL变量的时候不多；但是对存储过程来说，变量却是非常重要的SQL元素。MySQL里的变量可以分为3类:
    $ 普通变量。
     这类变量的标志是以字符@开头，他们在SQL连接被关闭时将失去内容。
   
    $ 系统变量和服务器变量。
     这类变量的内容是MySQL服务器的工作状态或属性，他们的标志是以"@@"字符串开头。
   
    $ 存储过程里的局部变量。
     这些变量是在存储过程内部声明的，只在存储过程内有效。他们没有统一的特殊标志，但是变量名必须与数据表和数据列名区别。
     局部变量在使用前必须要用DECLARE命令对他们做出声明。局部变量的内容在过程或函数退出的时候丢失。
   
   普通全局变量的声明和赋值:
   例子: set @varname = 3
   
    select @total :=count(*) from table_a
   
    select money from book where id =3 into @bookmoney
   
   查询:
   例子: select @varname
   
   
   
   

&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
                                                       PROCEDURE
    &&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
   
    PROCEDURE [databasename.]procedure_name([IN or OUT or INOUT ] parametername datatype )
    BEGIN
   
     commands;
     
    END
   
   
    ----------------
   
    存储过程的参数:
    关键字 IN ,OUT ,INOUT 用来区分有关参数的用途是仅限制于输入数据、仅限于输出数据 还是 输入输出数据均可的。(默认设置是IN)
   
   
    ----------------
   
    PROCEDURE局部变量的定义和局部变量的赋值
   
     :同FUNCTION的局部变量的定义和局部变量的赋值
     
   
    -----------------
    example-007:
    题目：写一个加法的PROCEDURE, 输入两个数字。PROCEDURE输出他们的和
   
    delimiter $$
   
    DROP PROCEDURE IF EXISTS p_addition
   
    CREATE PROCEDURE p_addition(IN v1 int, IN v2 int ,OUT sum int)
    BEGIN
     set sum = v1+v2;
    END$$
   
    ---------
    CALL p_addition(12,45,@sum)$$
   
    select @sum $$
   
    TEST IS OK
   
    ----------------------------------
   
    ----------------------------------
    example-008
   
    题目: 有一张student表，有学生名字段和总分字段。
    我们写一个PROCEDURE，只要调用这个PROCEDURE ，输入 学生名 和 总分。 它就帮助我们把该学生写入数据表中。
   
    delimiter $$
   
    CREATE TABLE student(
    id int(11) primary key auto_increment,
    name varchar(50),
    score int(5)
    )$$
   
    -----
   
    DROP PROCEDURE IF EXISTS p_addStudent$$
   
    -----
   
    CREATE PROCEDURE p_addStudent(IN p_name VARCHAR(50) ,IN p_score INT)
    BEGIN
     insert into student(name,score)values(p_name,p_score);
   
    END$$
   
    -----
    CALL p_addStudent('Petter',199)$$
    CALL p_addStudent('Helen',209)$$
    CALL p_addStudent('Jacker',238)$$
   
    select * from student $$
   
    --------TEST IS OK
   
    --------------------------------------
    --------------------------------------
   
    example-009
   
    题目: 有一张person表 表中有多个字段。
    name ,age , state
    我们写一个PROCEDURE，只要调用这个PROCEDURE ，输入人名 和 年龄。 它就帮我们判断state,并写入数据库。
    如果年龄<0 ，state = 'UNBORN'
0 <=如果年龄<12，state = 'CHILD'
12<=如果年龄<22，state = 'YANG'
22<=如果年龄<60，state = 'STRONG'
60<=如果年龄，state = 'OLD'
   
   
    delimiter $$
   
    drop table person$$
   
    create table person(
    id int(11) primary key auto_increment,
    name varchar(50),
    age int(3),
    state varchar(50)
    )$$
   
   
    DROP PROCEDURE IF EXISTS p_addPerson $$
   
   
    CREATE PROCEDURE p_addPerson(IN p_name varchar(50),IN p_age INT(3))
    BEGIN
     declare p_state varchar(50) default "UN_KNOW";
     
     IF p_age < 0 THEN SET p_state = "UNBORN";
 ELSE
  IF p_age >0   && p_age<12 THEN SET p_state ="CHILD";   END IF;
IF p_age >=12 && p_age<22 THEN SET p_state ="YANG";    END IF;
IF p_age >=22 && p_age<60 THEN SET p_state = "STRONG"; END IF;
IF p_age >=60              THEN SET p_state = "OLD";    END IF;
 END IF;
 
 INSERT INTO person(name,age,state) values(p_name,p_age,p_state) ;
   
    END$$
   
    ------------------
    CALL p_addPerson('Pet',11) $$
    CALL p_addPerson('Tom',21) $$
    CALL p_addPerson('Joy',74) $$
    CALL p_addPerson('Soy',-4) $$
   
    SELECT * from person $$
   
    ----- TEST IS OK ----
   
   
   
   
   
   
    --------------------------------------------------
    --------------------------------------------------
   
    example-010
   
    题目:有一张表 goods ，3个字段 id ,name,price. 表中有很多记录。
    现在我们要写一个PROCEDURE ，把里面的每个商品的价格都修改为原来的80%.
   
   
    delimiter $$
   
    drop table goods$$
   
    create table goods(
     id int(11) primary key auto_increment,
     name varchar(50),
     price float(6,2) default 0000.00
    )$$
   
   
    insert into goods(name,price)values('goods_01',77.56)$$
    insert into goods(name,price)values('goods_02',147.56)$$
    insert into goods(name,price)values('goods_03',156.36)$$
    insert into goods(name,price)values('goods_04',58.36)$$
    insert into goods(name,price)values('goods_05',458.68)$$
    insert into goods(name,price)values('goods_06',485.55)$$
    insert into goods(name,price)values('goods_07',785.22)$$
    insert into goods(name,price)values('goods_08',45.36)$$
    insert into goods(name,price)values('goods_09',47.36)$$
    insert into goods(name,price)values('goods_10',456.36)$$
    insert into goods(name,price)values('goods_11',654.85)$$
    insert into goods(name,price)values('goods_12',785.25)$$
   
   
    ------------
    DROP PROCEDURE IF EXISTS p_goods $$
   
    CREATE PROCEDURE p_goods()
    BEGIN
     
     DECLARE p_id INT DEFAULT 0;
     DECLARE p_id_min INT DEFAULT 0;
     DECLARE p_id_max INT DEFAULT 0;
     DECLARE p_id_current INT DEFAULT 0;
     DECLARE p_name_current VARCHAR(50) DEFAULT "UNKNOW";
     DECLARE p_price FLOAT(6,2) DEFAULT 0;
     
     select min(id),max(id) from goods into p_id_min ,p_id_max;
     
     SET p_id = p_id_min;
     
     goods_loop : LOOP
     
      select id,name,price from goods where id = p_id into p_id_current,p_name_current,p_price;
     
      IF p_id_current!=0 THEN
     
       set p_price = p_price * 0.8;
       
       update goods set price = p_price where id = p_id;
       
       set p_id_current=0;
       
      END IF;
     
      set p_id = p_id + 1;
     
      IF p_id > p_id_max THEN LEAVE goods_loop; END IF;
     END LOOP goods_loop;
   
   
    END $$
   
------------------------------------  
   
mysql> select * from goods;
   -> $$
+----+----------+--------+
| id | name     | price  |
+----+----------+--------+
|  1 | goods_01 |  77.56 |
|  2 | goods_02 | 147.56 |
|  3 | goods_03 | 156.36 |
|  4 | goods_04 |  58.36 |
|  5 | goods_05 | 458.68 |
|  6 | goods_06 | 485.55 |
|  7 | goods_07 | 785.22 |
|  8 | goods_08 |  45.36 |
|  9 | goods_09 |  47.36 |
| 10 | goods_10 | 456.36 |
| 11 | goods_11 | 654.85 |
| 12 | goods_12 | 785.25 |
+----+----------+--------+

----------------------------------------
CALL  p_goods() $$

----------------------------------------
mysql> CALL p_goods() $$
Query OK, 1 row affected (0.13 sec)

mysql>  select *from goods$$
+----+----------+--------+
| id | name     | price  |
+----+----------+--------+
|  1 | goods_01 |  62.05 |
|  2 | goods_02 | 118.05 |
|  3 | goods_03 | 125.09 |
|  4 | goods_04 |  46.69 |
|  5 | goods_05 | 366.94 |
|  6 | goods_06 | 388.44 |
|  7 | goods_07 | 628.18 |
|  8 | goods_08 |  36.29 |
|  9 | goods_09 |  37.89 |
| 10 | goods_10 | 365.09 |
| 11 | goods_11 | 523.88 |
| 12 | goods_12 | 628.20 |
+----+----------+--------+
12 rows in set (0.00 sec)

---------------------------------------
example-010 TEST IS OK  ,Finished


---------------------------------------
---------------------------------------


$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
                                异常捕获
$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$


SP里面的SQL命令在执行的过程中可能会出错，所以MYSQL也像其他一些程序语言一样向程序员提供一种利用 ‘异常处理器’来响应和处理这类错误的机制。

在一个BEGIN - END 语句块里，对‘异常处理器’的定义必须出现在变量，光标，出错条件的声明之后。在其他SQL命令之前。

语法:

DECLARE type HANDLER FOR condition1[,condition2,condition3,.......] handler_action

下面对语法中的type , condition , handler_action 来进行解释:

<1>type(异常捕获处理类型) 。可以选择的类型目前只有 CONTINUE 和 EXIT 两种。(未来的MySQL版本可能会增加第3种选择:UNDO)
CONTINUE : 如果当前命令在执行时发生错误，继续执行下一条命令。
EXIT     : 如果当前命令在执行时发生错误，跳出当前的BEGIN - END 语句块。

<2>condition (捕获异常条件)。这里可以列出一个到多个捕获异常条件。它们是异常处理器要捕捉的目标。捕获异常条件可以用以下几种方式给出:

SQLSTATE 'errorcode'  单个SQL异常代码，编号是errorcode
SQLWARNING   含盖了SQLSTATE编号为01nnn的所有异常
NOT FOUND   含盖了所有其他的(即SQLSTATE编号不是01 和 02开头的)的异常
mysqlerrorcode   这个数字是MySQL异常的代码而不是一个SQLSTATE异常的代码
conditionname   用一个DECLARE CONDITION 命令定义的异常,conditionname是异常的名字

<3>handler_action 异常被抛出时要执行的命令。它将在异常抛出后， CONTINUE or EXIT 执行前运行。
  因为这里只能放上一条命令，所以通常它是一个变量赋值命令。
 
 
-------------------------------------------------------------------
声明异常捕获条件(自定义异常)
所谓的"声明异常捕获" 就是给异常编码定义一个简明易记的名字。
定义一定要在异常出现以前定义。定义出来的异常捕获名可以用在出错的异常捕获器定义中。

语法:
DECLARE condition_name CONDITION FOR {SQLSTATE sqlstate_code | MySQL_error_code};

例:
DECLARE foreign_key_error CONDITION FOR 1216;
DECLARE CONTINUE HANDLER FOR foreign_key_error MySQL_statements;

优先级:

当同时使用MySQl错误码,标准SQLSTATE错误码,命名条件(SQLEXCEPTION)来定义错误处理时，其捕获顺序是（只可捕获一条错误）:

MySQl错误码--->SQLSTATE错误码--->命名条件(SQLEXCEPTION)

具体的SQL_STATE 请参考


--------------------------------------------------------------------
异常的触发

MYSQL中异常的出发只能靠执行非法代码来实现。 而不能如同Oracle，直接有"RAISE Exception"来实现的。

--------------------------------------------------------------------

SP中的打印语句。
我们在Oracle中写存储过程，会很常用到一个打印函数“DBMS_OUTPUT.PUT_LINE('要打印的内容');”
很可惜，在MySQL中没有类似的函数。
但是我们可以通过变通来实现该功能。
利用 SELECT '我们想要让计算机打印出来的内容'  来实现。
语法：

SELECT "Content" as result;
SELECT CONCAT(A1,A2[,A3,A4,.....]) as result ;

写一个例子：
example-011

delimiter $$


--------
DROP PROCEDURE IF EXISTS p_print $$


--------
CREATE PROCEDURE p_print()
BEGIN

DECLARE i int default 1;

myloop : LOOP

select concat("这是第",i,"次显示数据") as printResult;

set i=i+1;
IF i>10 THEN   LEAVE myloop; END IF;

END LOOP myloop;

END$$

---------
call p_print()$$

----------------------------------------
example-012

写一个循环 ，我们来循环捕捉错误。

delimiter $$
--------------
DROP PROCEDURE IF EXISTS p_exception $$
--------------
CREATE PROCEDURE p_exception()
BEGIN

DECLARE num int default 0;

DECLARE table_notfound_error CONDITION FOR 1146 ;


DECLARE CONTINUE HANDLER FOR table_notfound_error SELECT CONCAT("TABLE is not exit FOR ---",num) as message;


myloop:LOOP
set num = num + 1;
select * from exception; -- 1146 errorcode

IF num >= 10 THEN

LEAVE myloop;

END IF;

END LOOP myloop;

END$$

-----------------
TEST IS OK

-----------------------------------------------------
-----------------------------------------------------


$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
                             游标
$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$

说起游标，我就想起了Oracle中的游标。 如果你没有学习过Oracle中的游标，没有关系。因为MYSQL的游标更简单。

游标(CURSOR)：是构建在MYSQL中，用来查询数据，获得记录集合的指针。他可以让开发者一次访问结果集中一行。

MYSQL 中只有显式游标 这 一种游标。

--------------------------------
游标的使用方法(使用过程)。

(1)声明游标。
(2)打开游标。
(3)从游标中获取记录。
(4)关闭游标。

---------------------------------

(1)声明游标。
语法 :

DECLARE cursorname CURSOR FOR "YOUR SQL";

--------

(2)打开游标。

OPEN cursorname;

--------

(3)从游标中获取记录。

FETCH cursorname INTO v1,v2,....;

在ORACEL 中，游标中没有数值的时候 %FOUND 就会 返回一个 FALSE。
但是在MYSQL 中FETCH 到最后就会触发一个1329号错误 "No data to fetch".相应的SQLSTATE 为 02000。
这个异常是无法避免的，所以我们都会用异常捕捉器来捕捉它。（可以直接声明一个对应的异常捕捉器，也可以声明一个 NOT FOUND 的异常捕捉器）

---------

(4)关闭游标。

CLOSE cursorname 。

注释: 其实这样做 也就增加逻辑性。其实光标会在BEGIN - END 块结束的时候自动关闭。所以很多程序员都不会手动关闭游标。

-----------------------

实例练习:
example-013

题目，创建一个多字段的表 student ,有 id, name ,intime 三个字段。里面写入有多行记录。
用游标来获得里面的所有 记录，并且 一行一行的输出。

--------------
delimiter $$

--------------
DROP TABLE IF EXISTS student $$

--------------
CREATE TABLE student(
id int primary key auto_increment,
name varchar(50),
intime timestamp(14)
)$$

--------------

insert into student(name,intime) values('s-1','1999-08-25 12:30:30')$$
insert into student(name,intime) values('s-2','1999-08-25 12:30:30')$$
insert into student(name,intime) values('s-3','1999-08-25 12:30:30')$$
insert into student(name,intime) values('s-4','1999-08-25 12:30:30')$$
insert into student(name,intime) values('s-5','1999-08-25 12:30:30')$$
insert into student(name,intime) values('s-6','1999-08-25 12:30:30')$$
insert into student(name,intime) values('s-7','1999-08-25 12:30:30')$$
insert into student(name,intime) values('s-8','1999-08-25 12:30:30')$$
insert into student(name,intime) values('s-9','1999-08-25 12:30:30')$$

commit $$
---------------

DROP PROCEDURE IF EXISTS p_readcursor $$

---------------

CREATE PROCEDURE p_readcursor()
BEGIN


DECLARE p_id int default 0;
DECLARE p_name varchar(50) default "unknow";
DECLARE p_intime timestamp(14) default '0000-00-00 00:00:00';

DECLARE student_cursor CURSOR FOR select id,name,intime from student;

DECLARE EXIT HANDLER FOR 1329 SELECT "CURSOR IS END --> OK" as message;

OPEN student_cursor;

myloop: LOOP

FETCH student_cursor into p_id,p_name,p_intime;

IF p_id=100 THEN LEAVE myloop; END IF;

select p_id,p_name,p_intime ;

END LOOP myloop;

CLOSE student_cursor;


END $$

----------------
call p_readcursor()$$


-------TEST IS OK----------
---------------------------


$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
  触发器
$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$


触发器的用途是 在INSERT 、UPDATE 、DELETE命令之前 或则 之后自动调动SQL命令或SP。比如说，可以为每一个UPDATE操作测试被修改的数据是否满足特定条件。
在MYSQL5.0里边触发器还很不完善。与SP相比，触发器还远没有成熟到可以用于实际应用程序中的地步。根据MySQL在线文档里的说法，MYSQL5.1版本中将提供更多触发器的功能。
在5.1版本出来以前，触发器只能完成一些很初级的任务。

(1)创建触发器
(2)查询数据库中的触发器
(3)删除触发器

------------------------------
(1) 创建触发器

语法:

CREATE TRIGGER trigger_name BEFORE|AFTER INSERT|UPDATE|DELETE
ON tablename [FOR EACH ROW]
BEGIN
commands;
END


注释： * 最多可以为同一个数据表定义6个触发器，分别为 INSERT , UPDATE 或 DELETE 命令的前 , 后各定义一个。
* 触发器的名字在同一个数据库中必须唯一。
* 触发器代码体要 以 BEGIN 开始， END 结束。

功能局限:
* 触发器代码里无法访问任何数据表，就连触发器为之定义的那个数据表也不能访问。自然就不能使用 DELETE,UPDATE,INSERT 来修改数据库表。
* MySQL没有提供可以用来取消DELETE，UPDATE，INSERT命令的命令或语法元素。
* 在触发器代码里不能调用事务命令。

OLD and NEW

在触发器代码里，可以通过以下方式去访问当前记录的各个字段。
OLD.columname 返回一条现有记录在被删除或修改之前的内容(UPDATE,DELETE).
NEW.columname 返回一条新记录或被修改记录的新内容(INSERT ,UPDATE).

---------------------------------
(2)查询数据库中的触发器

暂时还没相关命令来查看自定义的触发器。(他们做地太差了，HOHO)

---------------------------------
(3)删除触发器

语法:
DROP TRIGGER [databasename.]triggername

注释: 删除trigger不支持 IF EXISTS 变体。


---------------------------------
做一个例子:

example-014

delimiter $$

--------------
DROP TABLE IF EXISTS student_score$$

--------------
CREATE TABLE student_score (
id int primary key auto_increment,
name varchar(50),
score int
)$$

--------------

DROP TRIGGER student_score_insert_before$$

--------------

CREATE TRIGGER student_score_insert_before
BEFORE INSERT ON student_score FOR EACH ROW
BEGIN

IF NEW.score<0 or="" new.score="">100 THEN
SET NEW.score = 0;
END IF;

END$$

--------------

Insert into student_score(name,score)values('ZhangSan',12)$$
Insert into student_score(name,score)values('LiSi',-12)$$
Insert into student_score(name,score)values('WangWu',112)$$

mysql> select * from student_score$$
+----+----------+-------+
| id | name     | score |
+----+----------+-------+
|  1 | ZhangSan |    12 |
|  2 | LiSi     |     0 |
|  3 | WangWu   |     0 |
+----+----------+-------+

笔记结束，祝贺大家学习愉快.

上述就是小编为大家分享的怎么理解MySQL存储过程和触发器了，如果刚好有类似的疑惑，不妨参照上述分析进行理解。如果想知道更多相关知识，欢迎关注创新互联行业资讯频道。


网页题目：怎么理解MySQL存储过程和触发器
文章来源：http://6mz.cn/article/gjjihd.html

其他资讯