Oracle数据笔记-【6】触发器和内置程序包

---------------第八章 触发器

--回顾
--子程序(存储过程)
--存储过程的语法
create procedure proc_test
as
--[定义局部变量]
begin
       select * from student
end;

--根据编号查询姓名的存储过程
create procedure proc_namebyid(myid number)
as
myname varchar(20);
begin
       select s_name into myname from student where s_id = myid;
       dbms_output.put_line('编号为：'||myid||'的歌手名：'||myname);
end;

--调用存储过程
begin
     proc_namebyid(&请输入歌手编号);
end;

--函数（必须要有返回值）
--语法
create function fun_test
return varchar
as
begin

end;

--带参数的函数
create function fun_sum(num1 number,num2 number)
return number
as
mysum number := 0;
begin
       for i in num1..num2
           loop
                mysum := mysum + i;           
           end loop;
           return mysum;
end;


select fun_sum(1,100) from dual

---------------触发器

--1、什么是触发器

--2、触发器的分类
--数据库触发器 和 DML触发器（insert update delete）

--3、触发器的语法
create trigger tri_test
after 
on 表名

old
new 

select * from student;
select * from exam

create trigger tri_addstu
before insert on student
for each row
begin
    if :new.s_id >= 5 then
       raise_application_error(-20001,'超过一班的人数');
    end if;
end;

insert into student values(s_id.nextval,'狮子合唱团','男',6)


--insert 触发器
create trigger tri_addstu2
after insert on student
for each row
begin
    insert into exam values(e_id.nextval,:new.s_id,'笔试',0);
    insert into exam values(e_id.nextval,:new.s_id,'机试',0);
end;

--update触发器
create trigger tri_update
after update on exam
for each row
begin
    update student set s_id=:new.s_id where s_id = :old.s_id;
end;

update exam set s_id = 666 where s_id = 12;


--delete触发器
create trigger tri_del
after delete on student
for each row 
begin
      delete from exam where s_id = :old.s_id;
end;


delete from student where s_name = '狮子合唱团'