MYSQL数据库管理-存储过程与函数\触发器

概述内容

触发器

###查看triggers####
show triggers
show triggers like '%TRI%'\G
select *from infomation_schema.triggers;
drop trigger if exists trigger_name;




范例1：
create trigger triname
before/after
inert/update/delete
on table_name for each row
begin
sql_statement;
end;




支持new.，old.信息insert只有new.。
范例2：
create trigger t1
after update on empsa
for each row
begin
insert into A values(:NEW.EMPNO,:NEW.EMPSI);
END;

存储过程流控制

存储过程范例：

deIimiter //
CREATE PROCEDURE emp_age_count(IN age1 int, IN age2 int,OUT count INT )
  BEGIN
   DECLARE temp FLOAT;
   DECLARE emp_age CURSOR FOR SELECT empage FROM emp;
   DECLARE EXIT HANDLER FOR NOT FOUND CLOSE emp_age;
     SET @sum=0;
    SELECT COUNT(*) INTO count FROM emp
     WHERE empage>age1 AND empage<age2;
        OPEN emp_age;
        REPEAT
         FETCH emp_age INTO temp;
          IF temp>age1 AND temp<age2
          THEN SET @sum=@sum+temp;  --@sum为会话变量
          END IF;
          UNTIL 0 
        END REPEAT;
        CLOSE emp_age;
          END//



调用 call emp_age_count(22,21,@c);
mysql>SELECT @C,@sum;

存储过程和自定义函数中使用流控制语句执行，mysql用来构造控制流程语句有if、case、loop、leave、iterate、repeat、while。

if grade>=60 then
select NOW();
else select dateadd(now(),interval 1 1:1:1）;
END IF;

CASE DEPTNO
WHEN 1 THEN SELECT NOW();
WHEN 2 THEN SELECT DATEADD(NOW(),INTERVAL 1 1:1:1 day_second);
when 3 then select dateadd(now(),INTER 1 DAY);
END CASE;

loop 与leave

declare ss int default 0;
add_sum:loop
Set ss=ss+1;
if ss>100 then leave add_sum;
end if;
end loop add_sum;

inerate语句也可以跳出循环，但inerate语句是跳出本次循环，直接进入下一次循环，inerate意思是'再次循环’

CREATE PROCEDURE PP(a int)
begin
la:loop
set a=a+1
if a<10 then inerate la;
end if;
leave la;
end loop la;
set @x=a;
end

repeat语句创建的是带条件判断的循环语句。

declare aa int default 10;
repeat
set aa=aa+1;
until aa>=20;
end repeat;

while语句也是条件控制的巡检语句

declare aa int default 0;
while a<=20
set aa=aa+1;
end while;

光标使用-oracle叫游标

#定义光标
declare cursor_emp cursor for select username，status from mysql.USER;
#打开光标
open cursor_emp;
#光标赋予
fetch cursor_emp into eno,ename;
#关闭光标
close cursor_emp;