ORACLE之触发器和函数

触发器

触发器（trigger）是SQL server 提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，比如当对一个表进行操作（ insert，delete， update）时就会激活它执行。触发器经常用于加强数据的完整性约束和业务规则等

1.触发器语句

--指定触发器定时，事件,表名及类型

2.触发器主体

--PL/SQL语句或过程调用

3.触发器限制

--通过WHEN子句实现 

create or replace trigger <触发器名称>
before/after
insert/delete/update<列名> on <表名(与数据库关联的数据表)>
[for each row]
when(<条件>)
    <PL/SQL 程序块>

数据库触发器


*表级触发器


create or replace trigger Tristudinfo
after insert or update or delete
on studinfo
begin
    dbms_output.put_line("在Studinfo表上执行了DML语句操作");

*行级触发器


create or replace trigger Tristudinfo
after insert or update or delete
on studinfo
for each row
begin
    dbms_output.put_line("在Studinfo表上执行了DML语句操作");

  • 行级触发器引用值
    • 

    作用：实现多表之间的级联，例如学生选课系统，当学生选择了某门课程那么相应的任课老师，他的课程下面的学生记录也要添加该学生的学生记录，并且控制选课人数
    

    1.:new 引用插入操作的某列值,如 :new.classid
    

    2.:old 引用删除操作的某列值,如 :old.classid
    

    只能在For each row即行级触发器中使用:new;:old
    
执行更新操作时，可以使用:new获取新值(更新后的值);
    
使用:old获取旧值(更新前的值)
    

    create or replace trigger trigclassinfo
after update
on classinfo
for each row
begin
    dbms_output.put_line(:old.classid||:old.classname);
    dbms_output.put_line(:new.classid||:new.classname);
  update TecherCourse T set
  RemainPersonCount = RemainPersonCount+1
  where T.Teacherno = :new.Teacherno
  and T.courseID = :new.courseID；

    退选(delect)
    

    create or replace trigger TrigElect_Delete
  after delect
on electcoursesult
for each row
begin
  update Techercourse T set
  Remainpersoncount = RemainpersonCount-1;
  where T.Teacherno = :old.Teacherno and
  courseID=:old.courseID;

    使用触发器实现自动编号
    
作用:实现表的ID自增
    

    //创建表
create table TestSeq
AutoID int,
Name varchar2(20)
//创建序列
create sequence SeqID
start with 1001//初始值
increment by 1 //步长
//触发器实现自动编号
create or replace trigger trig_Testseq
before insert on TestSeq
for each row
declare AID int;
begin
  select seqid.nextval into AID from dual;
  :new.AutoID:=AID;

    deleting:执行delete操作
    

    create or repalce trigger TriClassinfo
after insert or update or delete
on Classinfo
begin
    if inserting then
      dbms_output.put_line('执行了insert操作');
    elsif updating then
      dbms_output.put_line('执行了updata操作');
    elsif deleting then
      dbms_output.put_line('执行了delete操作');
    end if;
![picfive.PNG](https://upload-images.jianshu.io/upload_images/8176895-d9bd866b9055dee9.PNG?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240)

    游标（Cursor）是处理数据的一种方法，为了查看或者处理结果集中的数据，游标提供了在结果集中一次一行或者多行前进或向后浏览数据的能力。可以把游标当作一个指针，它可以指定结果中的任何位置，然后允许用户对指定位置的数据进行处理。
    

    Cursor <游标名> is
<SQL语句>

    由Oracle内部声明，用于处理每个SQL语句
    

    用于处理DML语句，返回单行的查询
    

    OPEN:打开游标，在查询返回任何行之前必须打开游标
    

    OPEN <游标名>;

    FETCH：一次只能在一个数据集中检索行，可以重复执行，直到检索完了所有行
    

    FETCH <游标名> into var1,var2...varn;

    CLOSE：关闭游标
    

    CLOSE<游标名>;

    游标的属性
    

    用于访问游标的环境区域，判断是否还有数据
    

    --%NOTFOUD 变量最后从游标中获取记录的时候，在结果集中没有找到了记录
    

    --%FOUND 变量最后从游标中获取记录的时候，在结果集中找到了记录
    

    --%ROWCOUNT 变量最后从游标中获取记录的时候，在结果集中找到了记录数
    

    --%ISOPEN
    

    将属性添加到游标名后面即可使用游标
    

    显示游标示列
    

    declare
    Cid classinfo.classid%Type;
    Cname classinfo.classname%Type;
cursor mycur is select classid,classname from classinfo;
begin
    open mycur;
    fetch mycur into Cid,Cname;
    while mycur%found
      dbms_output.put_line(Cid||'  '||Cname);
      fetch mycur into Cid,Cname;
    end loop;
    dbms_output.put_line('记录数:'||mycur%rowcount);
    close mycur;
    TName TeacherInfo.TeacherName%type;
CURSOR mycur is
    select TeacherNo,TeacherName From TeacherInfo
    Where TeacherBirthDay>=To_Date('1981-2-5','YYYY-MM-DD');
begin
    open mycur;
    fetch mycur into TID,TName;
    while mycur%found
        dbms_output.put_line(TID||TNAME);
        fetch mycur into TID,TName;
    end loop;
    close mycur;

    这样的游标过于繁琐，先要定义数据类型，还要打开游标，判断数据集中是否还有数据数据，最后还要关闭游标,是否有一种更简单的方法呢，答案是：有的，它就是循环游标，显示游标的替代方法
    

  • FOR循环游标
    • 

    1.将它的循环索引声明为%ROWTYPE的记录
    

    2.隐式的打开游标
    

    3.处理完所有行，推出循环，并且隐式的关闭游标
    

    FOR循环游标示列
    

    declare 
    cursor mycur is select classid,classname from classinfo;
begin
for mrec in mycur
    dbms_output.put_line(mrec.classid||'   '||mrec.classname||'    '||mycur%Rowcount);//输出当前的索引
end loop;

    优点：实现同样的功能，可以发现他的代码量明显减少了,简化大码
    

    [图片上传失败...(image-5a5f17-1525171760401)]
    

  • REF游标
    • 

    1.编写简单触发器，实现当在学生信息表（StudInfo）中添加记录后，在SQLPLUS 中显
    
示添加的各项值，当在学生信息表（StudInfo）中删除记录后，在SQL    PLUS 中显示删
    
除的各项值，当在学生信息表（StudInfo）中更新记录后，在SQL*PLUS 中显示更新前
    
和更新后的各项值。
    

    create or replace trigger Tristudinfo
after insert or update or delete
on studinfo
begin
if inserting then
  dbms_output.put_line(:new.studno||:new.studname||:new.studsex||:new.studbirthday||:new.classid);  elsif   updating then
  dbms_output.put_line(:new.studno||:new.studname||:new.studsex||:ne w.studbirthday||:new.classid);
  dbms_output.put_line(:old.studno||:old.studname||:old.studsex||:old.studbirthday||:old.classid);
elsif deleting the
  dbms_output.put_line(:old.studno||:old.studname||:old.studsex||:old.studbirthday||:old.classid);
end if;  

    2． 定义一个简单游标，在 SQL*PLUS 环境中使用 WHILE 循环实现学生信息表（StudInfo）
    
1986 年以后出生的男学生信息。
    

    declare
sstudno studInfo.studno%Type;
sstudname studInfo.studname%Type;
sstudsex studInfo.studsex%Type;
sstudbirthday studInfo.studbirthday%Type;
sclassid studInfo.classid%Type;
CURSOR mycur is
select studno,studName,studsex,studbirthday,classid From studInfo
Where studBirthDay>=To_Date('1986-01-01','YYYY-MM-DD');
begin
open mycur;
fetch mycur into sstudno,sstudName,sstudsex,sstudbirthday,sclassid;
while mycur%found
  dbms_output.put_line(sstudno||sstudName||sstudsex||sstudbirthday||sclassid);
  fetch mycur into sstudno,sstudName,sstudsex,sstudbirthday,sclassid;
end loop;
close mycur;

    3． 定义一个游标，使用 FOR 循环将班级编号为 20010505 的学生平均分按从高到低排名，
    
要求在 SQL*PLUS 环境中显示学号，姓名，性别，班级名称，平均分，名次字段。
    

    declare
CURSOR mycur is
select rank() over(order by avg(studscore) desc) mc,studno,studname,studsex,avg(studscore) avgscore,classname
from studinfo  join studscoreinfo using(studno)
join classinfo c using(classid)
where classid = '20010505'
group by studno,studname,studsex,classname
order by avg(studscore) desc;
begin
for mrec in mycur
  dbms_output.put_line(mrec.mc||mrec.studno||mrec.studName||mrec.studsex||mrec.avgscore||mrec.classname);
end loop;
select rank() over(order by avg(studscore) desc) paiming,studno,studname,studsex,avg(studscore) avgscore,classname
from studinfo  join studscoreinfo using(studno)
join classinfo c using(classid)
where classid = '20010505'
group by studno,studname,studsex,classname
order by avg(studscore) desc

    4． 使用 CREATE TABLE tablename as subquery 子查询创建一个空表（StudInfo_Back），包
    
括（学号，姓名，性别，出生日期，班级名称）字段，其各字段数据类型与学生信息表
    
和班级信息表中对应字段相同。
    

    create table tablename StudInfo_Back
select * from sutudinfo
where 1=2

    5． 使用游标循环，将1986 年以后出生的学生一条条添加到题目4 中创建的表 StudInfo_Back
    

    declare
sstudno studInfo.studno%Type;
sstudname studInfo.studname%Type;
sstudsex studInfo.studsex%Type;
sstudbirthday studInfo.studbirthday%Type;
sclassid studInfo.classid%Type;
CURSOR mycur is
select studno,studName,studsex,studbirthday,classid From studInfo
Where studBirthDay>=To_Date('1986-01-01','YYYY-MM-DD');
begin
open mycur;
fetch mycur into sstudno,sstudName,sstudsex,sstudbirthday,sclassid;
while mycur%found
  insert into studinfo_back values (sstudno,sstudName,sstudsex,sstudbirthday,sclassid);
  fetch mycur into sstudno,sstudName,sstudsex,sstudbirthday,sclassid;
end loop;
close mycur;

    6． 查询 StudInfo_Back 表记录，查询结果将性别为男的显示为 MALE，为女的显示为FEMALE。
    

    select studno,studname,
case when studsex='男' then 'MALE'
else 'FEMLE' end studsex，
studbirthday,classid
from studinfo_back

    7． 将学生信息表（StudInfo）中的其它记录（即不在 Studinfo_back 表中的记录）添加到StudInfo_Back 表中（使用 IN 子查询和 Merge 两种方法实现）
    

    B.Merge方式
merge into studinfo_back sb
using studinfo s
on(sb.studno=s.studno)
when not matched then
insert(sb.studno,sb.studname,sb.studsex,sb.studbirthday,sb.classid)
values (s.studno,s.studname,s.studsex,s.studbirthday,s.classid)

    8． 创建一个存储过程，使用游标循环找出班级编号为 20010505 班的平均分最高的学生和
    
最低的学生。（注：不能使用 MAX，MIN）
    

    CREATE OR REPLACE PROCEDURE FINDMAXANDMIN
CURSOR MYCUR
SELECT S.STUDNO,STUDNAME,ROUND(AVG(SS.STUDSCORE),2) AVGSCORE
FROM STUDINFO S INNER JOIN STUDSCOREINFO SS
ON S.STUDNO=SS.STUDNO
WHERE S.CLASSID='20010505'
GROUP BY S.STUDNO,S.STUDNAME;
MAXSTUDNO STUDINFO.STUDNO%TYPE;
MINSTUDNO STUDINFO.STUDNO%TYPE;
MAXSCORE NUMBER:=0;
MINSCORE NUMBER:=100;
BEGIN
FOR MYROWS IN MYCUR
  IF MYROWS.AVGSCORE>MAXSCORE THEN
    MAXSCORE:=MYROWS.AVGSCORE;
    MAXSTUDNO:=MYROWS.STUDNO;
  END IF;
  IF MYROWS.AVGSCORE<MINSCORE THEN
    MINSCORE:=MYROWS.AVGSCORE;
    MINSTUDNO:=MYROWS.STUDNO;
  END IF;
END LOOP;
FOR MYROWS IN MYCUR
  IF MYROWS.STUDNO=MAXSTUDNO THEN
    DBMS_OUTPUT.PUT_LINE('MAX:'||MYROWS.STUDNO||','||MYROWS.STUDNAME||','||MYROWS.AVGSCORE);
  END IF;
  IF MYROWS.STUDNO=MINSTUDNO THEN
    DBMS_OUTPUT.PUT_LINE('MIN:'||MYROWS.STUDNO||','||MYROWS.STUDNAME||','||MYROWS.AVGSCORE);
  END IF;
END LOOP;

    9． 统计各班同年出生的学生人数。
    

    select classid,extract(year from studbirthday),count(*)
from studinfo
group by classid,extract(year from studbirthday)

    10．统计各班同年同姓的学生人数。
    

    select classid,substr(studname,-1) 姓,extract(year from studbirthday) 出生年,count(*)
from studinfo