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

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

--回顾--子程序(存储过程)--存储过程的语法create procedure proc_testas--[定义局部变量]begin       select * from studentend;--根据编号查询姓名的存储过程create procedure proc_namebyid(myid number)asmyname 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_testreturn varcharasbeginend;--带参数的函数create function fun_sum(num1 number,num2 number)return numberasmysum 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_testafter on 表名oldnew select * from student;select * from examcreate trigger tri_addstubefore insert on studentfor each rowbegin    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_addstu2after insert on studentfor each rowbegin    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_updateafter update on examfor each rowbegin    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_delafter delete on studentfor each row begin      delete from exam where s_id = :old.s_id;end;delete from student where s_name = '狮子合唱团'