存储过程，游标，触发器

一、触发器

1.创建触发器

CREATE TRIGGER trigger_name
trigger_time
trigger_event ON tbl_name
FOR EACH ROW
trigger_stmt
1
2
3
4
5

trigger_name：标识触发器名称，用户自行指定；
trigger_time：标识触发时机，取值为 BEFORE 或 AFTER；
trigger_event：标识触发事件，取值为 INSERT、UPDATE 或 DELETE；
tbl_name：标识建立触发器的表名，即在哪张表上建立触发器；
trigger_stmt：触发器程序体，可以是一句SQL语句，或者用 BEGIN 和 END 包含的多条语句。

由此可见，可以建立6种触发器，即：BEFORE INSERT、BEFORE UPDATE、BEFORE DELETE、AFTER INSERT、AFTER UPDATE、AFTER DELETE。

另外有一个限制是不能同时在一个表上建立2个相同类型的触发器，因此在一个表上最多建立6个触发器。

2.触发器实例

BEGIN
[statement_list]
END
1
2
3

而在MySQL中，分号是语句结束的标识符，遇到分号表示该段语句已经结束，MySQL可以开始执行了。因此，解释器遇到statement_list 中的分号后就开始执行，然后会报出错误，因为没有找到和 BEGIN 匹配的 END。

这时就会用到 DELIMITER 命令（DELIMITER 是定界符，分隔符的意思），它是一条命令，不需要语句结束标识，语法为：
DELIMITER new_delemiter
new_delemiter 可以设为1个或多个长度的符号，默认的是分号（;），我们可以把它修改为其他符号，如$：
DELIMITER $
在这之后的语句，以分号结束，解释器不会有什么反应，只有遇到了$，才认为是语句结束。注意，使用完之后，我们还应该记得把它给修改回来。

DELIMITER $
create trigger tri_stuInsert after insert
on student for each row
begin
declare c int;
set c = (select stuCount from class where classID=new.classID);
update class set stuCount = c + 1 where classID = new.classID;
end$
DELIMITER ;
1
2
3
4
5
6
7
8
9

3.NEW 与 OLD 详解

上述示例中使用了NEW关键字，和 MS SQL Server 中的 INSERTED 和 DELETED 类似，MySQL 中定义了 NEW 和 OLD，用来表示

触发器的所在表中，触发了触发器的那一行数据。
具体地：
在 INSERT 型触发器中，NEW 用来表示将要（BEFORE）或已经（AFTER）插入的新数据；
在 UPDATE 型触发器中，OLD 用来表示将要或已经被修改的原数据，NEW 用来表示将要或已经修改为的新数据；
在 DELETE 型触发器中，OLD 用来表示将要或已经被删除的原数据；
使用方法： NEW.columnName （columnName 为相应数据表某一列名）
另外，OLD 是只读的，而 NEW 则可以在触发器中使用 SET 赋值，这样不会再次触发触发器，造成循环调用（如每插入一个学生前，都在其学号前加“2013”）。

4.查看触发器

DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name
1

5.删除触发器

DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name
1

二、存储过程

1.创建与调用过程

创建

-- 创建存储过程 
create procedure mypro(in a int,in b int,out sum int) 
begin 
set sum = a+b; 
end;
1
2
3
4
5

调试

call mypro(1,2,@s);-- 调用存储过程 
select @s;-- 显示过程输出结果
1
2

create procedure 用来创建过程；
mypro 用来定义过程名称；
(in a int,in b int,out sum int)表示过程的参数，其中 in 表示输入参数，out 表示输出参数。类似于 Java 定义方法时的形参和返回值；
begin 与end 表示过程主体的开始和结束，相当于 Java 定义方法的一对大括号；
call用来调用过程，@s 是用来接收过程输出参数的变量

INOUT输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）。
1
2
3
4
5
6
7

2.显示存储过程

显示存储过程
SHOW PROCEDURE STATUS;
显示特定数据库的存储过程
SHOW PROCEDURE status where db = 'schooldb';
显示存储过程“mypro1”的源码
SHOW CREATE PROCEDURE mypro1;

1
2
3
4
5
6
7

3.删除存储过程

drop PROCEDURE mypro1;
1

三、游标

1. 游标创建和调用

游标往往结合循环使用，进行批量操作

create procedure p1()
begin
    declare id1 int DEFAULT 0;
    declare name1 varchar(100);
		DECLARE count int DEFAULT(4);
		
    -- 声明游标
    declare mc cursor for select Id, `name` from account ;
		declare exit handler for SQLSTATE '02000' close mc;
    -- 打开游标
    open mc;
    -- 使用游标
WHILE true DO
	    fetch mc into id1,name1;
			select id1,name1;
			set count := count-1; 
END WHILE;
    -- 关闭游标
    close mc;
end ;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

CALL p1()
1

四、循环

1.WHILE……DO……END WHILE

create procedure pro10()
 begin
 declare i int;
 set i=0;
 while i<5 do
     insert into t1(filed) values(i);
     set i=i+1;
 end while;
 end;//
1
2
3
4
5
6
7
8
9

在这个例子中，INSERT和SET语句在WHILE和END WHILE之间，当变量i大于等于5的时候就退出循环。使用set i=0；语句是为了防止一个常见的错误，如果没有初始化，i默认变量值为NULL，而NULL和任何值操作的结果都是NULL。

2.REPEAT……UNTIL END REPEAT

 create procedure pro11()
 begin
 declare i int default 0;
 repeat
     insert into t1(filed) values(i);
     set i=i+1;
     until i>=5
 end repeat;
 end;//
1
2
3
4
5
6
7
8
9

这个REPEAT循环的功能和前面WHILE循环一样，区别在于它的执行后检查是否满足循环条件（until i>=5），而WHILE则是执行前检查（while i<5 do）。
不过要注意until i>=5后面不要加分号，如果加分号，就是提示语法错误。

3.LOOP……END LOOP

create procedure pro12()
 begin
 declare i int default 0;
 loop_label: loop
     insert into t1(filed) values(i);
     set i=i+1;
     if i>=5 then
         leave loop_label;
     end if;
 end loop;
 end;//
1
2
3
4
5
6
7
8
9
10
11

从上面这个例子可以看出，使用LOOP编写同样的循环控制语句要比使用while和repeat编写的要复杂一些：在循环内部加入了IF……END IF语句，在IF语句中又加入了LEAVE语句，LEAVE语句的意思是离开循环，LEAVE的格式是：LEAVE 循环标号。