mysql 初级篇(三) 触发器、存储过程、游标
mysql 初级篇(三)
触发器、存储过程、游标
触发器trigger
Creat trigger buggood
After
Insert
On ord
For each row
Begin
Update goods set num=num-new.much where id=new.gid
End
New insert 来引用,因为insert插入新的行,所以用new来引用
Old delete 来引用,因为delete删除记录,是因为记录原来就已经存在,所以用old来引用。
For each row的作用:每一行受影响,触发器都执行,叫做行级触发器。
查看触发器:show trigger
删除触发器:drop trigger triggerName
过程:封装了若干条语句,调用时,执行封装体,但没有返回值。
函数:是一个有返回值的“过程”。
存储过程:procedure;
Create procedure testName()
Begin
Sql语句;
end
查看存储过程:show procedure status
调用存储过程:call procedure()
Create procedure t1()
Begin
Declare age int;
Select concat(‘age:’,age)
End
变量可以sql语句中合法的运算;
Set 变量名:=expression
Create procedure t2()
Begin
Declare age int default 18;
Set age:=age+10;
Select userInfo(“real age : ”, age);
End
Create procedure t3()
Begin
Declare age int default 18;
If age>=18 then
Select ‘已成年’ ;
Else
Select “未成年” ;
End if ;
存储过程中声明参数,语法是:[in/out/inout] 参数名 参数类型
Create procedure t3(age int)
Begin
If age >=18 then
Selec “已成年” ;
Else
Select “未成年” ;
End if ;
end
控制结构:顺序,分支,循环
Create procedure t4()
Begin
Declare total int default 0 ;
Declare num int default 0 ;
While num <=100 do
Set total := total + num ;
Set num := num +1 ;
End while;
Select total ;
End
In 是指接收参数
Create procedure t5(in n int)
Begin
Declare total int default 0;
Declare num int default 0;
while num <=n do
Set :=total+sum ;
Set :=sum+ 1 ;
End while;
End
Call t5(100) --调用存储过程
Out 是指往外部发射一个参数,调用时,要提供参数用于保存结果的值
Create procedure t6(in n int,out total int)
Begin
Declare total int default 0;
Set total :=0;
while num <=n do
Set :=total+sum ;
Set :=sum+ 1 ;
End while;
End
Call t6(100,@sumary) --调用存储过程
Inout 是指即可以传递参数,也可以发射出参数。
Create procedure t7()
Begin
Declare age int default 1;
Case age
When 10 then select “chid” ;
When 20 then select “student” ;
Else select “not data”
End case;
end
Repeat 循环
语句:
Repeat
Sql 语句;
Until i>=100 end repeat; --结束循环条件
Create procedure t8()
Begin
Declare i :=0;
Declare total :=0;
Repeat
Set i:=i+1;
Set total:=total+i ;
Until i<=100 end repeat;
Select total;
End
游标 cursor ;
对应N条结果集的资源,取出资源的接口/句柄,就是游标,沿着游标,可以一次取1行。
Declare 声明游标;declare 游标名 cursor for select_statement;
Open 打开游标,open 游标名
Fetch 取值;fetch 游标名 into var1,var2....
Close 关闭;close 游标名
Create procedure t9()
Begin
Declare row_gid int;
Declare row_num int ;
Declare row_name varchar(20);
Declare getgoods cursor for select gid,num,name from goods;
Open cursor;
Fetch getgoods into row_gid,row_num,row_name;
Select row_gid,row_num ;
Close getgoods;
end
Create procedure t10()
Begin
Declare row_gid int ;
Declare row_num int;
Declare row_name int ;
Declare cnt int default 0;
Declare i int default 0;
Declare getgoods cursor for select gid,num,name from goods;
Select count(gid) into cnt from goods;
Open getgoods;
Repeat
Fetch getgoods into row_gid,row_num,row_name;
Set cnt:= cnt -1;
Select row_gid,row_num,row_name;
Until cnt=0 end repeat;
Close getgoods;
End
游标取值越界时,利用标识来结束,在mysql cursor 中,可以declare continue handle 来操作1个越界标识。
Declare continue handler for not Found statement;
Create procedure t10()
Begin
Declare row_gid int ;
Declare row_num int;
Declare row_name int ;
Declare cnt int default 0;
Declare yes int default 1;
Declare getgoods cursor for select gid,num,name from goods;
Declare continue handler for not Found set yes:=0;
Select count(gid) into cnt from goods;
Open getgoods;
Fetch getgoods into row_gid,row_num,row_name;
Repeat
Select row_gid,row_num,row_name;
Fetch getgoods into row_gid,row_num,row_name;
Until yes=0 end repeat;
Close getgoods;
End