Mysql触发器存储

来源：互联网发布：泰州学院教务网络系统编辑：程序博客网时间：2024/06/07 12:27

测试插入数据： DELIMITER $$ //定义分界符 $$
USE `test`$$
DROP PROCEDURE IF EXISTS `test`$$
CREATE PROCEDURE `test`()
BEGIN
    DECLARE i INT DEFAULT 1001;

    WHILE (i <= 100000) DO
        INSERT INTO `test_user` (`user_id`, `user_name`, `rule_id`, `status`) VALUES
        (i,'xiaomiao',1,0);
        SET i = i + 1;

        end if;
    END WHILE;
END$$
DELIMITER ;

delimeter:定界符

show triggers:查看触发器

drop triiger xxxx:删除触发器

truncate:清理速度比delete快

Mysql不能对同一个表的同一个动作进行两次触发

create goods(
`gid` int,
`name` varchar(20),
`num` smallint
)Engine=MyISAM default charset=utf8;

create table ord(
`oid` int,
`gid` int,
`much` smallint
)Engine=MyISAM default charset=utf8;

insert into goods values(1,'cat',34),(2,'dog',65),(3,'pig',21);

delimiter $;//$符号作为结束标志

create trigger t2
after
insert
on ord
for each row
begin
update goods set num=num-new.much where gid=new.gid;
end$;

create trigger t3
after
delete
on ord
for each row
begin
update goods set num=num+old.much where gid=old.gid;
end$;

create trigger t4
before
update
on ord
for each row
begin
update goods set num=num-old.much+new.much where gid=old.gid;
end$;

create trigger t6
before
insert
on ord
for each row：声明行级触发器，行级引用，每插入一行就执行，在mysql中必写
begin
declare
rnum int;
select num into rnum from goods where gid=new.gid;
if new.much > rnum THEN
set new.much = rnum;
end if;
update goods set num=num-new.much where gid=new.gid;
end$;

create procedure p2()
begin
select 2+3 from ord;
end$;

存储过程创建语法：

--if/else结构

/**

if condition then

statement

else

end if;

create procedure p6()
begin
   declare age int default 18;
      set age:= age+20;
      if age>18 then
       select '已经成人了';
     else
       select '未成年';
   end if;
end$;

--存储过程传参

/**

存储过程的括号里，可以声明参数

讲法：【in/out/inout]参数名参数类型

**/

create procedure p8(width int,height int)
begin
    select concat('你的面积是',width*height) as area;
    if width>height
    then select '胖';
     else select 'thin';
   end if;
end$;

create procedure name()

begin

end

查看已经有的procedure

show procedure status;
调用存储过程

call procedurename()

call p2()

---存储过程中用declare声明变量

--格式 declare 变量名变量类型【default 默认值】

create procedure p3()
begin
declare age int default 18;
declare height int default 180;
select concat('年龄是',age,'身高是',height);
end$;

---变量可以参与sql合法运算

--set 变量名：=expression

seg age: = age+20

create procedure p5()
begin
declare age int default 18;
set age:= age+20;
select concat('年龄是',age);
end$;

--in:输入的参数

--out:输出的参数，输出型要赋初值，否则为null值不变

create procedure p19(in n int,out total int)
begin
    declare sum int default 0;
    declare i int default 1;
    set total=0;
    while i<n do
      set sum:=sum+1;
      set total:=total+sum;
   end while;
end$;

create procedure p12(in n int)
begin
    declare sum int default 0;
declare i int default 1;
    while i<n do
      set sum:=sum+i;
      set i:=i+1;
   end while;
   select concat('和',sum);
end$;

create procedure p12(in n int)begin    declare sum int default 0;declare i int default 1;    while i<n do      set sum:=sum+i;      set i:=i+1;   end while;   select concat('和',sum);end$;

--how to use case

create procedure p25()
begin
declare pos int default 0;
set pos :=floor(4*rand());
case pos

when 1 then select 'flying';
when 2 then select 'yindu';
else select 'fall';
end case;
end$;

---repeat 循环

、*

repeat

sql statment;

until condition end repeat;

*/

create procedure p26()
begin
declare total int default 0;
declare i int default 0;
repeat
set i := i+1;
set total := total + i;
until i>100
end repeat;

select total;

end$;

----游标

变量声明在游标之前

--cursor 游标游标的标志

--·1条sql,对应N条结果集的资源，取出资源的接口/句柄，就是游标

--沿着游标，可以一次取出一行

--declare 声明; declare 游标名 cursor for select_statement;

--open 打开; open 游标名

--fetch取值 , fetch 游标名 into var1,var2

--close 关闭; close 游标名;

create procedure p27()
begin
declare row_gid int;
declare row_num int;
declare row_name varchar(30);
declare getgoods cursor for select gid,num,name from goods;
open getgoods;

fetch getgoods into row_gid,row_num,row_name;
select row_num,row_name;
close getgoods;
end$;

变量声明在游标之前
create procedure p29()
begin
    declare row_gid int;
declare row_num int;
declare row_name varchar(30);
   declare cnt int default 0;
   declare i int default 0;
   declare getgoods cursor for select gid,num,name from goods;
   select count(*) into cnt from goods;
open getgoods;

   repeat
     set i := i+1;
     fetch getgoods into row_gid,row_num,row_name;
select row_gid,row_num,row_name;
   until i >= cnt end repeat;
close getgoods;
end$;

---游标越界时，有标识，利用标识来结束

--在mysql cursor中，可以declare continue handler操作越界标识

--declare continue handler for not found statement