目录
一、触发器
1.三种操作： delete update insert
2.两个临时表： :new :old
3.两个时间点： after before
4.两种重要模式： 行级 语句级
5.条件： when
注意：
Oracle中的触发器比SQL Server要复杂；
SQL Server中增删改操作只会触发一个事件，但在Oracle中会触发两个事件。
以delete为例，Oracle有个删除前事件 before 、删除后事件 after。
Oracle触发器分：前触发、后触发
1.什么是触发器（概念）？
(1) 触发器是一种特殊的存储过程；
(2) 触发器不能被直接调用，也不是手动启动，而是由事件来触发；--增删改
(3) 触发器经常用于加强数据的完整性约束和业务规则等。
(4) 触发器具备事务的功能。1.1.触发器的关键词：trigger
1.2.概述：是一个特殊的存储过程，而存储过程又是一个特殊的函数
当执行DML（数据操纵语言）语句时，触发器就会自动触发并且自动执行，而不是像函数那样需要手动调用
简单点说：触发器就是个开关，负责灯的亮与灭，你动了它就亮了。Oracle触发器比SQL Server的要复杂，有触发前before与触发后after。
2.创建触发器语法（史上最难的oracle语法，没有之一）
create [or replace] trigger trigger_name
after|before|instead of --instead反向
[insert][[or] update [of 列列表]][[or] delete]
on table表或view --视图使用率
[referencing{:old [as] old/:new [as] new}] --引用新表老表
[for each row] --行级模式
[when(condition)] --条件
pl/sql_block; --pl/sql语句(begin...end)insert after before
案例：
insert into emp values(…,张三,…,0,……);
==>去 :new
create or replace trigger trig_name
after insert
on emp
for each row
begin
if(:new.sal=0) then
dbms_output.put_line('警告：干活不能不给薪水');
else
dbms_output.put_line('已插入记录');
end if;
end;3. 触发器组成三部分：
a. 触发器语句（事件）--定义激活触发器的DML事件和DDL事件；
b. 触发器限制 ---执行触发器的条件，该条件为真才能激活触发器；
c. 触发器操作（主体）--包含SQL语句和代码，它们在发出了触发语句且触发限制的值为真是才运行。
注示：序列通过前触发保存到数据库中。
4. 前触发与后触的原理图
5. 在级联表中创建触发器案例
--李斯文不能删
(什么时候触发？a.delete时; b.when 条件满足时; c.代码中写明)
create or replace trigger t_studel
after delete
on stuinfo
for each row
begin
if :old.stuname='李斯文' then
--抛出异常
raise_application_error(-20010,'该学生不能删！！！');
end if;
end;
delete from emp;
--李斯文不能删，不能改
after delete or update
--怎么知道是(insert\delete\update)哪个操作？
……
begin
case
when deleting then
--删除时
if :old.stuname='李斯文' then
raise_application_error(-20010,'该学生不能删！！！');
end if;
when updating then
--修改时
if :old.stuname='李斯文' then
raise_application_error(-20011,'该学生不能修改！！！');
end if;
when inserting then
--插入时
if :old.stuname='张杨' then
raise_application_error(-20012,'该学生你也敢招！！！');
end if;
end case;
end;
insert 李超 :new
insert before ----> if (:old 李超 ---> 插入到表中去 ==没用
if (:new 李超 ---> 没插入到表中
before--->:new
insert after ----> if (:old 李超) ---> 插入到表中去
if (:new 李超) ---> 没插入到表中
before | after insert ----> :new
before | after delete ----> :old思考：
一个表能创建几个insert\update\delete触发器？
高级点的触发器多表联接:
1.确定触发器写在哪个表上 on table
2.确定使用哪个触发器insert delete update
3.用after 还是 before，用:new :old
--在stuinfo表中写个delete触发器，指定删除的人名，
直接删除stumarks表中该人的成绩。
create or replace tirgger t_mydel
after delete
on stuinfo
for each row
declare
stu varchar2(22);
begin
stu:=:old.stuno; --将删除的学生的学号赋值给该变量
delete from stumarks where stuno=stu;
end;
--运行下
delete from stuinfo where stuname='李斯文';注意：
表连接的触发器是比较复杂的，对A表操作触发A表的触发器，同时影到到了B表触发了B表的触发器，依次类推下去...建议：
表跟表关联频繁时，建议甚用触发器。
触发器类型： 
行级触发器与语句级触发器：
--案例2
create or replace tirgger t_mydel
after delete
on stuinfo
for each row
begin
dbms_output.put_line('删的好！');
end;
--执行（删除了一条记录，结果显示一条'删的好'）
delete from stuinfo where stuname='李文才';
--执行（删除了整个表中的记录，显示N条'删的好'）
delete from stuinfo
--说明：这就是讲解行级触发器。
--行级触发器：一条条的删，删一条就触发一个行级触发器。思考：
如果将[for each row]代码删除，执行看效果。--语句级触发器。
语句级触发器：执行一条触发一次。
标识列：序列+触发器
--创建个新表，准备往里面插入记录，使用触发器和序列。
create table tb_715(
sid number,
sname varchar2(22)
)
--执行插入操作
insert into tb_715(sid,sname) values(1,'刘帅')
--需求：不写sid,1能帮我自动产生一个序列。
--解决：(1)创建序列
create sequence seq715;
(2)创建个触发器，用前触发，用new表
create or replace trigger t_insert
before insert
on tb_715
for each row
begin
--新表中的id=序列的下一个值
select seq715.nextval into :new.sid from dual;
end; oracle 11g的语法：
:new.sid:=seq715.nextval;
oracle 10g的语法：
select seq715.nextval into :new.sid from dual;
别用变量，用变量的方法就是11g的写法。
:new表，将插入的数据先放入到:new表中，确认后放到要更新的表。
:old表，将不要的数据先放入到:old表中，确认不要了再清除:old表。
注意：
:new表和:old表中至始至终就只有一条数据，那请问有多少个列？触发器的表有多少个列，:new表和:old表就有多少个列。
思考：
1.触发器删掉，表还在不在？
2.表删掉了，触发器还不在？
3.表删掉了，视图还不在？
SQL Server不在；Oracle在，oracle中是分开的。commit; 提交（用于数据更新进行交互时刻获取最新数据）
二、程序包中使用游标
程序包中使用游标：
1.创建包头，并在包头中声明游标（可以强类型，可是弱类型）
2.填充一个游标，在包体中去填充。
--程序包规范代码（包头）
create or replace package pack_715
is
--声明游标（显示游标、强类型、带参）
cursor myc(dno number)
return emp%rowtype;
--声明存储过程
proceduer pname(dno number);
end pack_715;
--程序包主体（包体）
create or replace package body pack_715
is
--游标
cursor myc(dno number)
return emp%rowtype is --返回游标中的行记录
select * from emp where empno=dno;
--存储过程
procedure pname(dno number)
is
myr emp%rowtype; --与游标返回的类型一致，因为从游标中提取出来的行类型
begin
open myc(dno); --打开游标并传入值
loop
fetch myc into myr; --提取
exit when myc%notfound;
dbms_output.put_line('返回的值为：'||myr.ename);
end loop;
end pname;
end pack_715;
--调用存储过程
begin
pack_715.pname(7369);
end;三、触发器案例
#触发器的语法：
/*
create or replace trigger t_name
before|after
insert|delete|update
on table|view
for each row
begin
触发器的操作
end;
*/
------2322数字
--2个时间点
#1.before 在新增或者修改或者删除操作执行前被触发
#2.after 在新增或者修改或者删除操作执行后被触发
--3个特定事件
#insert|delete|update
--2个触发操作：
#行级触发器
#语句级触发器
--2个触发属性
# :old
# :new
------------------------------------------------------------------
--创建学生信息表
create table stuInfo
(
stuNo varchar2(8) not null primary key,
stuName varchar2(10) not null,
stuSex varchar2(2) not null,
stuAge number(6) not null,
stuSeat number(6) not null,
strAddress varchar2(255) default('地址不详')
)
go
--创建学生成绩表
create table stuMarks
(
ExamNo varchar2(7) not null primary key,
stuNo varchar2(6) not null references stuInfo(stuNo),
writtenExam number(6) null,
LabExam number(6) null
)
go
--往学生信息表内插入测试数据
insert into stuInfo(stuNo, stuName, stuSex, stuAge,stuSeat,strAddress)
select 's25301', '张秋丽', '男', 18,1, '北京海淀' from dual union
select 's25303', '李斯文', '女', 22,2, '河阳洛阳' from dual union
select 's25302', '李文才', '男', 85, 3,'地址不详' from dual union
select 's25304', '欧阳俊雄', '男', 28, 4,'新疆' from dual union
select 's25318', '梅超风', '女', 23, 5,'地址不详' from dual
go
commit;
--往学生成绩表内插入测试数据
insert into stuMarks(ExamNo, stuNo, writtenExam, LabExam)
select 's271811', 's25303', 93, 59 from dual union
select 's271813', 's25302', 63, 91 from dual union
select 's271816', 's25301', 90, 83 from dual union
select 's271817', 's25318', 63, 53 from dual
go
--需求：创建一个触发器，作用：欧阳俊雄不能开除
delete from stuInfo where stuName = '欧阳俊雄'
--撤销当前执行的操作
rollback;
select * from stuInfo;
#创建一个触发器
create or replace trigger t_oydel
after
delete
on stuInfo
for each row
begin
--利用:old对象去调用老表中已经存在的字段做判断
if :old.stuName = '欧阳俊雄' then --状态码的范围：-20000 - -20099
--错误提示 raise_application_error(状态码,错误内容提示);
raise_application_error(-20001,'级别不够，无法删除');
end if;
end;
delete from stuInfo where stuName = '欧阳俊雄'
insert into stuInfo(stuNo, stuName, stuSex, stuAge,stuSeat,strAddress)
select 's25322', '杨睿', '男', 18,1, '北京海淀' from dual
rollback;
--创建一个触发 杨睿不能加入本表
create or replace trigger t_yr
after
insert
on stuInfo
for each row
begin
if :new.stuName = '杨睿' then
raise_application_error('-20002','黑户，不能加入公司');
end if;
end;
--修改
update stuInfo set stuName = '小李飞刀' where stuName = '张秋丽'
select * from stuInfo;
--不能修改张秋丽的信息
create or replace trigger t_zql
after
update
on stuInfo
for each row
begin
if :old.stuName = '张秋丽' then
raise_application_error(-20003,'张秋丽不能修改');
end if;
end;
--一个触发器同时触发多个事件
--张秋丽不能修改，欧阳俊雄不能删除，陈涛不能新增
create or replace trigger t_demo
after
insert or update or delete
on stuInfo
for each row
begin
if updating then
if :old.stuName = '张秋丽' then
raise_application_error('-20001','张秋丽不能修改');
end if;
elsif deleting then
if :old.stuName = '欧阳俊雄' then
raise_application_error('-20002','欧阳俊雄不能删除');
end if;
elsif inserting then
if :new.stuName = '陈涛' then
raise_application_error('-20003','陈涛不能增加');
end if;
end if;
end;
update stuInfo set stuName = '小李飞刀' where stuName = '张秋丽'
delete from stuInfo where stuName = '欧阳俊雄'
insert into stuInfo(stuNo, stuName, stuSex, stuAge,stuSeat,strAddress)
select 's25323', '陈涛', '男', 18,1, '北京海淀' from dual
=======================================================================
--模拟标识列的实现（序列+触发器）
--复制表
create table demo_demp
as
select empno,ename,sal from emp where 1=0;
--查询
select * from demo_demp;
/*实现标识列的步骤1.创建序列 2.创建触发器即可*/
#1.创建序列
create sequence s_empno;
#2.创建一个触发器
create or replace trigger t_emp_no
before
insert
on demo_demp
for each row
begin
--oracle 10g
--赋值：查询序列的下一个值赋值给当前demo_demp表中的新增的数据的字段
--select s_empno.nextval into :new.empno from dual;
--oracle 11g
:new.empno:=s_empno.nextval;
end;
insert into demo_demp(ename,sal) values('黄睿',10000);
insert into demo_demp(ename,sal) values('黄睿3',10000);
select * from demo_demp;
create or replace trigger t_ok
before
delete
on demo_demp
for each row
begin
dbms_output.put_line('OK');
end;
delete from demo_demp where ename = '黄睿'
create or replace trigger t_ok2
before
delete
on demo_demp
begin
dbms_output.put_line('OK');
end;
delete from demo_demp where ename = '黄睿3'
内置程序包 dbms_output.put_line('OK');
begin
for i in 1..9 loop
for j in 1..i loop
--不跨行System.out.print();
dbms_output.put(j || ' * ' || i || ' = ' || ( i * j ) || ' ');
end loop;
dbms_output.new_line();--跨行System.out.println();
end loop;
end;
853
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
