存储过程,游标,触发器
一、触发器
1.创建触发器
CREATE TRIGGER trigger_name
trigger_time
trigger_event ON tbl_name
FOR EACH ROW
trigger_stmt
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
而在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 ;
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
5.删除触发器
DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name
二、存储过程
1.创建与调用过程
创建
-- 创建存储过程
create procedure mypro(in a int,in b int,out sum int)
begin
set sum = a+b;
end;
调试
call mypro(1,2,@s);-- 调用存储过程
select @s;-- 显示过程输出结果
create procedure 用来创建过程;
mypro 用来定义过程名称;
(in a int,in b int,out sum int)表示过程的参数,其中 in 表示输入参数,out 表示输出参数。类似于 Java 定义方法时的形参和返回值;
begin 与end 表示过程主体的开始和结束,相当于 Java 定义方法的一对大括号;
call用来调用过程,@s 是用来接收过程输出参数的变量
INOUT输入输出参数:既表示调用者向过程传入值,又表示过程向调用者传出值(值只能是变量)。
2.显示存储过程
显示存储过程
SHOW PROCEDURE STATUS;
显示特定数据库的存储过程
SHOW PROCEDURE status where db = 'schooldb';
显示存储过程“mypro1”的源码
SHOW CREATE PROCEDURE mypro1;
3.删除存储过程
drop PROCEDURE mypro1;
三、游标
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 ;
CALL p1()
四、循环
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;//
在这个例子中,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;//
这个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;//
从上面这个例子可以看出,使用LOOP编写同样的循环控制语句要比使用while和repeat编写的要复杂一些:在循环内部加入了IF……END IF语句,在IF语句中又加入了LEAVE语句,LEAVE语句的意思是离开循环,LEAVE的格式是:LEAVE 循环标号。