数据库总结之触发器存储过程

1、触发器

定义触发器：

create trigger <触发器名> 

|before|after| <触发事件> on <表名>

for each |row|statement|

[when <触发条件>]

<触发动作体>

1）触发器名在同一模式下，必须唯一；

2）触发事件：

触发事件可以是Insert/delete/update,也可以是几个事件的组合，如insert or delete等，update 后面还可以有

of<触发列，。。。>即进一步指明修改那些列时触发器激活；

3）触发器类型：

行级触发器：for each row:每行事件发生即触发事件；

语句级触发器：for each statement :每执行一次语句，触发一次；

如：有1000条记录的teacher表中，有一个after update触发器；当执行下面的语句：

update teacher set deptno=5;

        如果语句级触发器：执行完该语句，触发动作只发生一次；

        如果行级触发器，触发动作将执行1000次；

4）触发条件：触发器激活时，只有触发条件为真时动作体才会执行；

5）触发动作体：

如果行级触发器，在两种情况下，用户可以在过程体中使用NEW和OLD引用update/insert事件之后的新值

和update/insert事件之前的旧值；如果是语句级触发器则不可以使用new/old进行引用；

触发器动作体执行失败，激活触发器的事件将会终止执行，触发器的目标表或可能影响的对象不发生任何变化；

6）实例：定义一个before行级触发器，教授工资不得低于4000，当低于4000时自动改为4000；

create trigger Inserte_or_update_sal 

before insert or update on teacher for each row

begin

if (new.job='教授') and (new.sal<4000) then

new.sal=4000;

end if;

end;    

2、存储过程：

1）PL/SQL是编写数据库存储过程的一种过程语言，他结合了sql的数据操作能力和过程化语言的流程控制能力，

      是sql的过程化控制；

2）存储过程和函数被编译后保存在数据库中，可以反复被调用，运行速度较快；

      存储过程经过编译和优化后存储在数据库服务器中，每次调用即可，执行效率高；

3）mysql存储过程的实例：

-- delimiter//定义存储过程开始标志

delimiter //
create procedure sethomechannelidx()
  begin
declare ER_NO_SUCH_TABLE condition for 1146;
declare ER_DUP_ENTRY condition for 1062;
declare done integer default false;
declare table_existed integer default true;
declare myparentid bigint(20) unsigned;
declare mychannelid bigint(20) unsigned;
declare myidx integer;
declare parentids_cur cursor for select distinct `parent` from `homenewschannel`;
declare channels_cur1 cursor for select `id` from `homenewschannel` where `parent` is null;
declare channels_cur2 cursor for select `id` from `homenewschannel` where `parent` = myparentid;
declare continue handler for not found set done=1;
declare continue handler for ER_NO_SUCH_TABLE set table_existed=false;


set done=false;
open parentids_cur;
read_loop:loop
fetch parentids_cur into myparentid;
if done then
set done=false;
close parentids_cur;
leave read_loop;
end if;
set myidx=0;
if myparentid is null then
open channels_cur1;
read_loop1:loop
fetch channels_cur1 into mychannelid;
if done then
set done=false;
close channels_cur1;
leave read_loop1;
end if;
update `homenewschannel` set `idx`=myidx where `id`=mychannelid;
set myidx=myidx+1;
end loop;
else
open channels_cur2;
read_loop1:loop
fetch channels_cur2 into mychannelid;
if done then
set done=false;
close channels_cur2;
leave read_loop1;
end if;

update `homenewschannel` set `idx`=myidx where `id`=mychannelid;
set myidx=myidx+1;
end loop;
end if;
end loop;
  end//
delimiter ;
call sethomechannelidx();
drop procedure sethomechannelidx;