头歌oracle实验报告代码合集

Oracle实验报告（合集）

实验一 创建数据环境

1.用户及权限

创建用户utest,密码为utest1

create user utest identified by utest1;

为utest用户赋予connect权和dba权

grant connect to utest;
grant dba to utest;

收回utest用户dba权限

revoke dba from utest;

使用utest用户登录（使用connect命令）

connect utest/utest1;

2.创建表格

创建student表

create table student(
  sno char(10) primary key,--学号--
  sname varchar(20),--姓名--
  sage smallint,--年龄--
  ssex char(2),--性别--
  sdept varchar(20)--所在系--
)

创建course表

create table course(
cno char(10) primary key,--课程号--
cname varchar(20),--课程名--
cpno char(10),--先行课号--
ccredit smallint--课程学分--
);

创建sc表

create table sc(
sno char(10),--学号--
cno char(10),--课程号--
grade smallint,--成绩--
primary key(cno,sno)
)

3.修改表结构

为course表添加一列snumber(最大选课人数)，类型为char(10)

alter table course add snumber char(10);

修改course表中snumber列，将其类型改为int

alter table course modify  snumber int;

4.删除表

删除course表中的snumber列

alter table course drop column snumber;

删除表

drop table sc;
drop table student;
drop table course;

实验二 sql复习

1.DML之增删改

向Course表中插入一行数据

insert into course(cno,cname,ccredit,snumber)
  values('C50','Oracle',4,100);

使用Insert语句为CS系学生选择必修课C50

insert into sc(sno,cno)
       select sno,'C50' from student
              where sdept ='cs' and sno not in(select sc.sno from sc,student
                    where student.sno=sc.sno and cno='C50');

将Oracle课程的先行课修改为“DB”课程的课程号（使用子查询完成）

update course set cpno = (select cno from course where cname='DB') 
          where cname='Oracle';

将CS系“DB”课程成绩为空的学生选课信息删除

delete from sc where sno in (select sno from student where sdept='cs') 
  and cno in(select cno from course where cname='DB') and grade is null;

2.DML之查询

查询CS系年龄大于20岁的学生，列出学号，姓名，性别

select sno,sname,ssex from student 
             where sdept='cs' and sage>20;

查询选了’DB’课程的学生的学号

select sno from sc where cno in(select cno from course where cname='DB')；

查询CS系没有选’DB’课程的学生的学号，姓名，系

select sno,sname,sdept from student 
             where sdept='cs' and sno not in(select sno from sc 
                   where cno in (select cno from course
                         where cname='DB'));

查询男（‘m’）同学选择了’DB’课程，但是没有选’Oracle’课程的学生，列出学号，姓名。(课程名要区分大小写)

 select sno,sname from student
      where ssex='m' and sno in(select sno from sc 
            where cno in(select cno from course 
                  where cname='DB')) and sno not in(select sno from sc 
                        where cno in(select cno from course 
                              where cname='Oracle'));

3.DML之查询统计

查询每个学生的平均分，列出学生的学号，平均分（列名为savg）,并按平均分降序排列

select sno,avg(grade) savg from sc 
             group by sno 
               order by avg(grade) desc;

查询选课人数大于等于3人的课程，列出课程号，课程名，选课人数（列名为scnt）,并按课程号升序排列

select sc.cno,cname,count(*) scnt from course,sc 
             where sc.cno=sc.cno 
                   group by sc.cno,cname
                         having count(sno)>=3
                                order by cno;

查询选课人数最多的课程。列出课程号，课程名

select sc.cno,cname from course,sc
      where course.cno=sc.cno
          group by course.cno,cname
                 having count(*)>=all(select count(*) from sc group by sc.cno);

查询CS系选课人数最多的课程。列出课程号，课程名，CS系的选课人数（列名为CScnt）

select sc.cno,cname,count(*) CScnt from sc,course,student
       where student.sno=sc.sno and sc.cno=course.cno and sdept='cs' 
             group by sc.cno,cname
                  having count(*)>=all(select count(*) from sc
                        where sno in(select sno from student where sdept='cs')
                                      group by cno);

实验三 PL/SQL匿名块

1.PLSQL匿名块1

打印001号学生的姓名和年龄。打印结果格式（忽略行标号，其中冒号为中文状态下的冒号）： 001号同学的姓名：查询出的姓名 001号同学的年龄：查询出的年龄

declare 
  vsname varchar(20);
  vsage smallint;
begin
  select sname,sage into vsname,vsage from student
         where sno='001';
  dbms_output.put_line('001号同学的姓名：'||vsname);
  dbms_output.put_line('001号同学的年龄：'||vsage);
end;

将年龄最大的同学的学号，姓名，年龄打印出来（注：年龄最大的同学只有一个）。其格式为（忽略行标号）： 查询出来的学号，查询出来的姓名，查询出来的年龄，性别（查询出来的性别，若是’f’，显示：美女。若是’m’显示：帅哥。） 例如：（其中逗号为英文状态下的逗号） 001，张三，20，美女

declare 
  vsno char(10);
  vsname varchar(20);
  vsage smallint;
  vssex char(2);
begin 
  select sno,sname,sage,ssex into vsno,vsname,vsage,vssex from student
         where sage>=all(select sage from student);
         
  if vssex='f' then
     dbms_output.put_line(vsno||'，'||vsname||'，'||vsage||'，'||'美女');
  else
     dbms_output.put_line(vsno||','||vsname||','||vsage||','||'帅哥');
  end if;
end;

2.PLSQL匿名块2

将‘004’号同学的年龄改为18岁，系别改为‘E’，为CS系年龄最小的同学选‘C01’号课程

declare

begin
  update student set sage=18,sdept='E' where sno='004';
  insert into sc(sno,cno) 
    select sno,'C01' from student
           where sdept='cs' and sage<=all(select sage from student where sdept='cs')
                 and not exists(select * from sc where sno=student.sno and cno='C01')
end;

为‘002’号同学选‘C02’课程。将选课人数最多的课程的最大选课人数（snumber）改为105

declare

begin
  insert into sc(sno,cno) values('002','C02');
  update course set snumber=105 
          where cno in(select cno from sc 
                group by cno
                      having count(*)>=all(select count(*) from sc 
                             group by cno));
   commit;
end;

实验四 异常处理

第一关

查询‘111’号同学的姓名，使用异常判断，若没有这个同学，则添加一条学号为‘111’的学生信息，姓名为‘张三’，其他属性为空，并输出：‘111’号同学已被成功添加。 若存在该同学，则输出（冒号为中文状态下的冒号）： 111号同学的姓名 ：查询出存在该同学，则输出： 111号同学已被成功添加。

declare   
vsname student.sname%type;--姓名  
vsno student.sno%type:='111';--学号  
begin  
select sname into vsname from student where sno=vsno;  
 dbms_output.put_line(vsno||'号同学的姓名：'||vsname);  
exception when no_data_found then  
vsname:='张三';    
insert into student(sno,sname) values (vsno,vsname);  
dbms_output.put_line('111号同学已被成功添加');  
commit;  
end;  

打印‘C50’号课程的当前选课人数和最大选课人数（Snumber）。若出现异常，则判断C50号课程是否存在，若不存在，则添加C50课程（‘C50’,‘NewCourse’,null,3,100），再让001号同学和003号同学选修C50号课程，成绩分别为80分和90分，最后打印C50号课程的当前选课人数和最大选课人数。打印格式如下： 若C50存在，则打印格式如下（其中的逗号和冒号是中文状态下的）： （查询出的C50的课程名）课程当前选课人数：（查出来的人数），最大选课人数：（查出来的最大人数） 如：DB课程当前选课人数：80，最大选课人数：100 若C50不存在，则打印格式（其中的逗号和冒号是中文状态下的）： 添加NewCourse课程成功，课程当前选课人数：（查出来的人数），最大选课人数：（查出来的最大人数） 如：添加NewCourse课程成功，课程当前选课人数：10，最大选课人数：100

declare   
  vcno course.cno%type:='C50';--课程号  
  vcname course.cname%type;--课程名  
  vsnumber course.snumber%type;--最大选课人数  
  cnt course.snumber%type;--选课人数  
begin  
 select cname,snumber into vcname,vsnumber from course where cno=vcno;  
 select count(*) into cnt from sc where cno=vcno;  
 dbms_output.put_line(vcname||'课程当前选课人数：'||cnt||'最大选课人数：'||vsnumber);  
 exception when no_data_found then  
   insert into course(cno,cname,cpno,ccredit,snumber) values('C50','NewCourse',null,'3','100');  
   insert into sc values('001','C50',80);  
   insert into sc values('003','C50',90);  
   commit;  
 select cname,snumber into vcname,vsnumber from course where cno=vcno;  
 select count(*) into cnt from sc where cno=vcno;  
 dbms_output.put_line('添加'||vcname||'课程成功，课程当前选课人数：'||cnt||'最大选课人数：'||vsnumber);  
end;  

第二关

为English系年龄最大的同学选C52课程，并打印选课结果和该同学的选课数，若该生已经选择了C52课程，则打印该生的平均分。使用嵌套块中的异常判断，若没有English系，则将Student表中的E系修改为English系，并打印：已成功将E系修改为English系，然后退出程序。若没有C52课程，请添加C52课程（‘C52’,‘NC’,null,2,100）并打印：C52课程添加成功，然后退出程序。

（1）若学生和课程都存在，则为其选课，并输出（冒号为中文状态下的冒号）： 已为（查询出的姓名）同学选择了课程（查询出来的课程名），该同学的选课门数为：（查询出的选课门数） 如：已为张三同学选择了课程DB，该同学的选课门数为：10

（2）若该生已经选择了课程C52，则输出： （查询出的姓名）同学的平均成绩为：（查询出的平均分） 如：张三同学的平均成绩为：85

（3）若系别不存在，则修改数据后，输出： 已成功将E系修改为English系 （4）若课程不存在，则添加数据后，输出： C52课程添加成功

declare   
  vsname student.sname%type;--姓名  
  vsno student.sno%type;--学号  
  vcname course.cname%type;--课程名  
  cnt course.snumber%type;--选课门数  
  avg_grade smallint;--平均成绩  
begin  
  --异常2  
  select sno,sname into vsno,vsname from student where sdept='English' and sage=(select max(sage) from student where sdept='English');  
  begin   
    select count(*) into cnt from sc where sno=vsno;  
    insert into sc(sno,cno) values(vsno,'C52');  
    select cname into vcname from course where cno='C52';    
    dbms_output.put_line('已为'||vsname||'同学选择了课程'||vcname||',该同学的选课门数为：'||cnt);    
   --捕获两个异常  
    exception   
      when no_data_found then  
        insert into course(cno,cname,cpno,ccredit,snumber) values('C52','NC',null,2,100);  
        commit;  
        dbms_output.put_line('C52课程添加成功');  
      when dup_val_on_index then  
       select avg(grade) into avg_grade from sc where sno=vsno;  
        dbms_output.put_line(vsname||'同学的平均成绩为：'||avg_grade);  
  end;    
  exception when no_data_found then  
    update student set sdept='English' where sdept='E';  
    commit;  
    dbms_output.put_line('已成功将E系修改为English系');   
end; 

实验五 游标

静态游标

使用静态游标，遍历所有选择了“数学”的CS系学生，列出学生的学号，姓名，成绩（按学号升序排列）。（使用rpad对齐打印，其中学号占10个长度，姓名占10个长度）打印格式要求如下： 学号 姓名 成绩 -------------------------- （一共25个减号） 001 张三 85 002 李四 90

declare   
  cursor c is select student.sno,sname,grade from student,sc,course  
         where student.sno=sc.sno and sc.cno=course.cno and sdept='cs' and cname='数学'  
               order by student.sno;  
  vc c%rowtype;  
begin  
  dbms_output.put_line(rpad('学号',10,' ')||rpad('姓名',10,' ')||rpad('成绩',10,' '));  
  dbms_output.put_line(rpad('-',25,'-'));  
  open c;  
  fetch c into vc;  
  while c%found   
    loop  
        dbms_output.put_line(rpad(vc.sno,10,' ')||rpad(vc.sname,10,' ')||rpad(vc.grade,10,' '));  
        fetch c into vc;  
    end loop;  
  close c;  
end; 

使用静态游标，遍历所有CS系学生的选课信息，打印学号，姓名，系别，课程名，成绩（按学号和课程名升序排列）。（使用rpad对齐打印，学号，姓名，系别和课程号都各占10个长度，学生变了用横线区分开）打印格式要求如下： 学号 姓名 系别 课程名 成绩 ============================================（一共45个等号） 001 张三 CS C语言 78 001 张三 CS 数据库 90 001 张三 CS 英语 80 --------------------------------------------（一共45个减号） 002 李四 CS 数学 85 002 李四 CS 英语 75

declare   
  vsno student.sno%type:='000';--学号  
  cursor c is select student.sno,sname,sdept,cname,grade from student,sc,course  
         where student.sno=sc.sno and sc.cno=course.cno and sdept='cs'  
               order by student.sno,cname;  
  vc c%rowtype;  
begin  
  dbms_output.put_line(rpad('学号',10,' ')||rpad('姓名',10,' ')||rpad('系别',10,' ')||rpad('课程名',10,' ')||rpad('成绩',10,' '));  
  dbms_output.put_line(rpad('=',45,'='));  
  open c;  
  fetch c into vc;  
  vsno:=vc.sno;  
  while c%found   
    loop  
      if(vsno!=vc.sno) then   
        dbms_output.put_line(rpad('-',45,'-'));  
        vsno:=vc.sno;  
      end if;  
        dbms_output.put_line(rpad(vc.sno,10,' ')||rpad(vc.sname,10,' ')||rpad(vc.sdept,10,' ')||rpad(vc.cname,10,' ')||rpad(vc.grade,10,' '));  
        fetch c into vc;  
    end loop;  
  close c;  
end; 

动态游标

使用系统动态弱游标(sys_refcursor，请使用小写以便于后台验证)，查询男同学(性别为’m’)的选课情况，将每个同学的选课信息打印出来（及格则得对应课程的学分），并汇总其选课课程数。打印格式如下（按学号和课程名升序排列，使用rpad对齐打印，其中学号占10个长度，课程名占10个长度，成绩占10个长度）： 学号 课程名 成绩 所得学分 ===========================================（40个等号） 001 数据库 70 3 001 C语言 55 0 （说明：没有及格，不能得学分） 001 数据结构 68 4 001 离散数学 40 0 --------------------------------------（35个减号） 001同学的选课数：4门 ===========================================（40个等号） 004 数据库 90 3 004 汇编语言 88 3

004 Java编程 92 3 --------------------------------------（35个减号） 004同学的选课数：3门

declare   
  c1 sys_refcursor;  
  lastsno student.sno%type;  
  vsno student.sno%type;  
  vcname course.cname%type;  
  vgrade sc.grade%type;  
  vcredit course.ccredit%type;  
  cnt smallint;  
begin  
  dbms_output.put_line(rpad('学号',10,' ')||rpad('课程名',10,' ')||rpad('成绩',10,' ')||rpad('所的学分',10,' '));  
  dbms_output.put_line(rpad('=',40,'='));  
  open c1 for select sno,cname,grade,ccredit from sc,course   
      where sc.cno=course.cno and sno in(select sno from student where ssex='m')  
      order by sno,cname;  
        
  fetch c1 into vsno,vcname,vgrade,vcredit;  
  lastsno:=vsno;  
  while c1%found   
    loop  
      if(lastsno!=vsno) then  
         dbms_output.put_line(rpad('-',35,'-'));  
         select count(*) into cnt from sc where sno=lastsno;  
         dbms_output.put_line(lastsno||'同学的选课数：'||cnt);  
         dbms_output.put_line(rpad('=',40,'='));  
         lastsno:=vsno;  
      else  
        if vgrade>60 then  
           dbms_output.put_line(rpad(vsno,10,' ')||rpad(vcname,10,' ')||rpad(vgrade,10,' ')||rpad(vcredit,10,' '));  
        else  
          dbms_output.put_line(rpad(vsno,10,' ')||rpad(vcname,10,' ')||rpad(vgrade,10,' ')||rpad('0',10,' '));  
        end if;  
      end if;  
      fetch c1 into vsno,vcname,vgrade,vcredit;  
    end loop;  
    close c1;  
end; 

实验六 存储过程和函数

第一关

创建函数Fs1，传入课程号，返回字符型。返回该课程的先行课名。若该课程不存在，则返回格式如下(3)所示，若该课程没有先行课，则返回格式如下（2）所示。

函数
create or replace function Fs1(vcno in course.cno%type)  
 return varchar  
  is  
    vcpno course.cpno%type;--先修课号  
    vcname course.cname%type;--课程名  
    vcpname course.cname%type;--先修课名  
  begin  
    select cname into vcname from course where cno=vcno;  
    begin  
      select cpno into vcpno from course where cno=vcno;  
      select cname into vcpname from course where cno=vcpno;  
        return (vcname||'课程的先行课为：'||vcpname);  
      exception when no_data_found then   
        return (vcname||'课程没有先行课。');  
    end;  
    exception when no_data_found then   
     return (vcno||'课程不存在。');  
  end;  
测试
declare   
  
begin  
     dbms_output.put_line(Fs1('c31'));  
     dbms_output.put_line(Fs1('c30'));  
    dbms_output.put_line(Fs1('c001'));  
end;  

创建函数Fs2，传入学生学号，返回数值型。返回该学生的选课门数，若该学生不存在，则返回-1

函数
create or replace function Fs2(vsno student.sno%type)  
 return number  
is  
 cnt number;  
 vssno student.sno%type;  
 begin  
   select sno into vssno from student where sno=vsno;  
   begin  
     select count(*) into cnt from sc where sno=vssno;  
     return cnt;  
   end;  
   exception when no_data_found then   
      return -1;  
 end;
测试
declare   
  
begin  
   if(Fs2('001')=-1) then  
   dbms_output.put_line('001号同学不存在。');  
   else   
    dbms_output.put_line('001号同学的选课门数为：'||Fs2('001'));  
   end if;  
     
   if(Fs2('018')=-1) then  
        dbms_output.put_line('018号同学不存在。');  
    else   
        dbms_output.put_line('018号同学的选课门数为：'||Fs2('018'));  
    end if;  
      
    if(Fs2('123')=-1) then  
        dbms_output.put_line('123号同学不存在。');  
    else   
        dbms_output.put_line('123号同学的选课门数为：'||Fs2('123'));  
    end if;  
end; 

第二关

创建存储过程Ps1，传入课程名，查询该课程的选修情况（按学号升序排列），在存储过程中遍历游标，打印选修情况。使用异常捕获来处理课程不存在的情况。

函数
create or replace procedure Ps1 (vcname in course.cname%type)  
   is  
   c sys_refcursor;  
   c1 sc%rowtype;   
   vcno course.cno%type;--课程号变量 判断是否存在传入的课程       
begin  
     select cno into vcno from course where cname=vcname;  
     dbms_output.put_line(rpad('学号',10)||rpad('课程号',10)||'成绩');  
    dbms_output.put_line(rpad('=',25,'='));  
     open c for select sno,cno,grade from sc  
          where cno = vcno  
             order by sno;  
     fetch c into c1;  
    while(c%found) loop  
       if(c1.grade is null) then  
         dbms_output.put_line(rpad(c1.sno,10)||rpad(c1.cno,10)||-1);  
       else  
          dbms_output.put_line(rpad(c1.sno,10)||rpad(c1.cno,10)||c1.grade);  
       end if;  
       fetch c into c1;  
     end loop;  
     dbms_output.put_line(rpad('=',25,'='));  
      close c;  
     exception when no_data_found then  
       dbms_output.put_line(vcname||'课程不存在。');  
       dbms_output.put_line(rpad('=',25,'='));  
end; 
测试
begin  
Ps1('Oracle');  
Ps1('数据库');  
end;

第三关

创建存储过程Ps2，传入学生学号，通过字符型out参数输出操作结果。若该生选择了数据库课程，则返回数据库的成绩，返回格式如下(1)所示。若该生没有选择数据库，则判断是否为CS系，若是CS系，则为其选择“数据库”课程（成绩为空），并返回选课成功信息，返回格式如下（2）所示。否则返回该生未选择数据库课程，返回格式如下（3）所示。使用异常捕获来处理学生不存在的情况，若学生不存在，则返回格式如下（4）所示。

函数
create or replace procedure ps2(vsno in student.sno%type,v_line out varchar)   
is  
  vsname student.sname%type;--姓名 判断是否存在  
 vsdept student.sdept%type;--系别  
  vgrade sc.grade%type;--成绩  
 vcno course.cno%type;--数据库的课程号  
begin  
   select sname into vsname from student where sno=vsno;  
      begin  
        select cno into vcno from course where cname='数据库';  
        select grade into vgrade from sc where sno=vsno and cno=vcno;  
        begin  
          select sdept into vsdept from student where sno=vsno;  
           if vgrade is null then  
                v_line:=vsname||'同学的系别为：'||vsdept||'，数据库的成绩为：未考试';  
            else  
               v_line:=vsname||'同学的系别为：'||vsdept||'，数据库的成绩为：'||vgrade;  
            end if;  
        end;  
       exception when no_data_found then   
          select sdept into vsdept from student where sno=vsno;  
          if(vsdept!='cs') then  
            v_line:=vsname||'同学没有选择数据库课程。';  
          else  
            insert into sc(sno,cno) values(vsno,vcno);  
            commit;  
           v_line:='已成功为'||vsname||'同学选择了数据库课程。';  
          end if;  
                    
      end;  
    exception when no_data_found then  
     v_line:=vsno||'号同学不存在。';  
end ps2;  
测试
declare   
 output varchar(100);  
begin  
Ps2('002',output);  
dbms_output.put_line(output);  
Ps2('003',output);  
dbms_output.put_line(output);  
Ps2('004',v_line => output);  
dbms_output.put_line(output);  
Ps2('040',v_line => output);  
dbms_output.put_line(output);  
end;

第四关

创建存储过程pSCnumber，传入学号，首先判断SC_Number表是否存在，若不存在则创建该表（包括学号和选修门数（列名为Scnt）两列），并初始化该表数据。若表存在，则将SC_Number表中传入的学号对应的选课数加1，若该生不存在（使用隐试游标判断），则插入一行，使其选课数为1。返回相关信息。

create or replace procedure pSCnumber(vsno student.sno%type,v_line out varchar2)  
as  
 vsname student.sname%type;  
 cnt int;  
begin  
 select count(*) into cnt from user_tables where table_name = 'SC_NUMBER';  
  if(cnt=1) then  
      execute immediate  
      'select sname  from student   
        where sno in(select sno from SC_Number where sno=:1)'  
        into vsname  
        using vsno;  
      if sql%found then --学生存在  
         execute immediate  
         'update SC_Number set scnt=scnt+1 where sno=:1'using vsno;  
          commit;  
        v_line:=vsname||'同学新增一门课程成功！';  
      end if;   
  else  
    --没有表就创建表  
    execute immediate  
    'create table SC_Number(sno char(10) primary key,scnt number)';  
    --初始化数据  
    execute immediate  
    'insert into SC_number(sno,scnt) select sno,count(*) from sc group by sno';  
    v_line:='创建SC_Number成功！';  
    commit;  
  end if;  
    --表中没有学生  
       exception when no_data_found then  
          --先在SC_Number表插入新同学  
          execute immediate  
        'insert into SC_Number values(:1,1)' using vsno;  
         commit;  
         --再找名字  
         execute immediate  
         'select sname  from student   
         where sno in(select sno from SC_Number where sno=:1)'  
          into vsname  
         using vsno;  
         v_line:='已为SC_Number表新增'|| vsname||'同学的数据。';  
end pSCnumber;  

创建存储过程Ps3，传入学生姓名，课程名和成绩，使用字符型out参数输出选课结果。假设学生姓名和课程名都不能重复。若该生选择了该课程，则修改成绩为传入的成绩，输出格式如下（5）所示。若该生没有选择该课程，则为该生选择该课程，成绩为传入的成绩，输出格式如下（4）所示，并调用pSCnumber函数，让学生选课数更新。若学生或课程不存在，则输出格式如下（2）（3）所示。若成绩不在0到100分之间，则输出如下(1)所示。

create or replace procedure Ps3(vsname student.sname%type,  
                                 vcname course.cname%type,  
                                 vgrade sc.grade%type,  
                                 v_line out varchar)  
 is  
    vsno student.sno%type;--学号 判断是否存在  
    vcno course.cno%type;--课程号 方便选课  
    output varchar2(100);  
 begin  
   if(vgrade<0 or vgrade>100) then  
       dbms_output.put_line('成绩必须在0到100分之间。');  
       return ;  
   end if;  
   select sno into vsno from student where sname=vsname;  
   begin  
     select cno into vcno from course where cname=vcname;  
     --先插入 后捕捉异常  
     insert into sc(sno,cno,grade) values(vsno,vcno,vgrade);  
      commit;  
      v_line:='已经成功为'||vsno||'同学选择了数据库课程,成绩为'||vgrade;  
      --调用pSCnumber函数，将其选课数+1  
      pSCnumber(vsno,output);  
      v_line:=output;  
     exception when no_data_found then  
      v_line:=vcname||'课程不存在。';  
      when dup_val_on_index then  
        update sc set grade=vgrade where sno=vsno and cno=vcno;  
       if(vgrade is null) then  
        v_line:='已将'||vsno||'号同学'||vcname||'课程的成绩改为未考试';  
        else  
        v_line:='已将'||vsno||'号同学'||vcname||'课程的成绩改为'||vgrade;  
        end if;  
   end;  
   exception when no_data_found then  
      v_line:=vsname||'同学不存在。';  
 end; 
 测试
 declare   
 output varchar(100);  
begin  
Ps3('符志阶','数据库',86,output);  
dbms_output.put_line(output);  
Ps3('符志阶','数据库',null,output);  
dbms_output.put_line(output);  
Ps3('符志阶s','数据库',86,v_line => output);  
dbms_output.put_line(output);  
Ps3('符志阶','数据库s',86,v_line => output);  
dbms_output.put_line(output);  
Ps3('符志阶','数据库',120,v_line => output);  
dbms_output.put_line(output);  
end; 

实验七 触发器

抛出异常

创建触发器tr_Student_update

当修改Student表时，不能修改20岁以上(包括20岁)的同学的系别。抛出的异常提示为：不能修改20岁以上的同学系别。

create or replace trigger tr_Student_update
  before update sdept on student
  for each row
  when(old.sage >= 20)
declare

begin
  raise_application_error(-20001,'不能修改20岁以上的同学系别。');
end;

创建触发器tr_Course_Insert

当插入Course表数据是，课程号以X开头的课程，必须要有先行课。抛出异常提示为：课程号以X开头的课程，先行课不能为空。

create or replace  trigger tr_Course_Insert
  before insert on course
  for each row
  when(new.cno like 'X%' and new.cpno is null)
  declare
  
  begin
    raise_application_error(-20002,'课程以X开头的课程，先行课不能为空。');
  end;

创建触发器tr_SC_Delete

规定不能删除数据库课程的选课信息。抛出的异常提示为：不能删除数据库课程的选课信息。

1.create or replace trigger tr_SC_Delete  
2.before delete on sc  
3.for each row  
4.declare  
5.vcname varchar(20);   
6.begin  
7.  select cname into vcname from course where cno=:old.cno;  
8.  if vcname='数据库' then  
9.    raise_application_error(-20001,'不能删除数据库课程的选课信息。');  
10.  end if;  
11.end;  

当修改学生考试成绩时，若修改的学生是CS系的学生，则成绩只能增加，不能减少，并且不能将成绩修改为null。若不是CS系的同学，则不受控制。若成绩修改比原成绩小，则抛出的异常提示为：CS系学生的成绩只能增加不能减少。若将成绩修改为null，则抛出的异常提示为：CS系学生的成绩不能修改为空。

create or replace trigger tr_SC_Update  
  before update of grade on sc   
  for each row  
declare  
  vsdept student.sdept%type;  
begin  
  select sdept into vsdept from student where sno=:old.sno;  
  if vsdept='cs' then  
    if :new.grade<:old.grade then  
      raise_application_error(-20001,'CS系学生的成绩只能增加不能减少。');  
    end if;  
      
    if :new.grade is null then  
      raise_application_error(-20002,'CS系学生的成绩不能修改为空。');  
    end if;  
  end if;  
end; 

级联删除

创建student表上的删除触发器

当删除学生信息时，先将该学生的选课信息删除。

测试触发器：在Student表中删除001号同学

create or replace trigger tr_Student_delete  
  before delete on student  
  for each row  
begin  
     delete from sc where sno=:old.sno;  
end; 

创建SC表的增删改触发器

在Student表中新建Scnt列(学生选课门数，类型为int，初值为0)，并初始化Scnt列数据。在触发器中维护这一冗余列，保证Scnt列数据的正确。

测试触发器：

1、为019号同学选择C01课程，成绩为空。

2、将014同学选择C01课程记录的学号修改为015。

3、删除013学生的所有选课信息。

alter table student add Scnt int default 0 ;
update student set Scnt=(select count(*) from sc where sno=student.sno);
代码
create or replace trigger tr_sc_IDU  
  before insert or delete or update  
  on sc   
 for each row  
declare  
   
begin  
  if inserting then  
    --若插入的成绩为空  
    if :new.grade is null then  
      :new.grade:=0;  
    end if;  
    --更改student表中的数据  
    update student set Scnt=Scnt+1 where sno=:new.sno;  
  end if;  
    
  if deleting then  
    update student set Scnt=Scnt-1 where sno=:old.sno;  
  end if;  
    
 if updating then  
    --若更新的成绩为空  
    if :new.grade is null then  
      :new.grade:=0;  
    end if;  
     --更改student表中的数据  
    update student set Scnt=Scnt+1 where sno=:new.sno;  
    update student set Scnt=Scnt-1 where sno=:old.sno;  
  end if;  
end; 

触发器之总学分

.创建SC表上的触发器，维护冗余列的数据

在Student表中添加冗余列sumC列(学生的总学分，类型为int，初值为0)，并初始化sumC列数据，只有成绩及格时才能获得课程对应学分。在触发器中维护这一冗余列，保证sumC列数据的正确。

测试要求：请务必在创建了触发器后，在Oracle系统下分别为SC表进行增删改操作，并查看对应的SumC列的数据是否正确。要测试的部分包括：

关于插入的测试

（1）插入成绩为空的行，然后观察sumc的变化。

（2）插入成绩不及格的行，然后观察sumc的变化。

（3）插入成绩及格的行，然后观察sumc的变化。

关于删除的测试

（4）删除成绩为空的行，然后观察sumc的变化。

（5）删除成绩不及格的行，然后观察sumc的变化。

（6）删除成绩及格的行，然后观察sumc的变化。

（7）删除某一个同学的所有选课记录，然后观察sumc的变化。

关于修改的测试

（9）将一行的成绩从空修改为不及格，然后观察sumc的变化。

（10）将一行的成绩从空修改为及格，然后观察sumc的变化。

（11）将一行的成绩从及格修改为不及格，然后观察sumc的变化。

（12）将某一个同学的学号修改为另一个学号（例如，将001改为020，其中020是未选课的同学），然后观察sumc的变化。

create or replace trigger tr_Student_IU  
  before insert or update on student  
  for each row  
declare  
begin  
     if :new.sumC is null then  
      :new.sumC:=0;  
     end if;  
end; 
在Student表中添加冗余列sumC列(学生的总学分，类型为int，初值为0)，并初始化sumC列数据：
alter table student add sumC int default 0 ;   
update student set sumC=(select sum(ccredit) from sc,course where sno=student.sno and sc.cno=course.cno and grade >=60 );  


create or replace trigger tr_sc_sumC  
  before insert or delete or update  
  on sc   
  for each row  
declare  
   vccredit course.ccredit%type;--成绩及格加上的学分  
begin  
  if inserting then  
    --成绩合格就更改student表中sumC的数据  
    if :new.grade >=60 then  
      select ccredit into vccredit from course where cno=:new.cno;  
      update student set sumC=sumC+ vccredit where sno=:new.sno;  
    end if;  
  end if;  
    
  if deleting then  
    --删除成绩及格的就更改student表中sumC的数据  
      if :old.grade >= 60 then  
        select ccredit into vccredit from course where cno=:old.cno;  
        update student set sumC=sumC - vccredit where sno=:old.sno;  
      end if;  
  end if;  
    
  if updating then  
    --一共三种情况：  
    --1：:old.grade < 60 and :new.garde > = 60  +  
    --2：:old.grade >= 60 and :new.garde >= 60  + -   
    --3：:old.grade >= 60 and :new.garde < 60  _  
    if :new.grade >=60 then  
     select ccredit into vccredit from course where cno=:new.cno;  
      update student set sumC=sumC+ vccredit where sno=:new.sno;  
    end if;  
      
      if :old.grade >= 60 then  
        select ccredit into vccredit from course where cno=:old.cno;  
        update student set sumC=sumC - vccredit where sno=:old.sno;  
      end if;  
        
  end if;  
end;  

触发器之总成绩

创建SC表上的触发器，维护冗余列的数据。

在Student表中添加冗余列sumG列(学生的总成绩，类型为int，初值为null)，并初始化sumG列数据。假设SC表中成绩不能为0（即Student表中的sumG总成绩不会为0）.在触发器中维护这一冗余列，保证sumG列数据的正确。

测试要求：请务必在创建了触发器后，在Oracle系统下分别为SC表进行增删改操作，并查看对应的SumG列的数据是否正确。要测试的部分包括：

关于插入的测试

（1）为某位没有选课的同学选课，插入成绩为空的行，然后观察sumG的变化。

（2）为某位没有选课的同学选课，插入成绩不为空的行，然后观察sumG的变化。

（3）为总成绩部位空的同学选课，插入成绩不为空的行，然后观察sumG的变化。

关于删除的测试

（1）删除成绩为空的行，然后观察sumG的变化。

（2）删除成绩不为的行，然后观察sumG的变化。

（3）删除某一个同学的所有选课记录，然后观察sumG的变化。

关于修改的测试

（1）将一行的成绩从空修改为一份分数，然后观察sumG的变化。

（2）将一行的成绩从有分数修改空，然后观察sumG的变化。

（3）将某一个同学的学号修改为另一个学号（例如，将001改为020，其中020是未选课的同学），然后观察sumG的变化。

alter table student add sumG int;  
update student set sumG=(select sum(grade) from sc where sno=student.sno);  

create or replace trigger tr_sc_sumG_IDU  
before insert or update or delete on sc  
for each row  
declare  
  vsumG student.sumG%type;--总成绩  
begin  
   if inserting then  
       --如果插入的成绩为0  
      if :new.grade=0 then  
          :new.grade:=null;  
      end if;  
      --有成绩就更改student表中sumG的数据  
      if :new.grade is not null then  
          select sumG into vsumG from student where sno=:new.sno;  
          if vsumG is null then --还没有成绩的同学  
             update student set sumG=:new.grade where sno=:new.sno;  
          else  
             update student set sumG=sumG+:new.grade where sno=:new.sno;  
          end if;  
      end if;  
    end if;  
    
    if deleting then  
      --删除成绩不为空就更改student表中sumG的数据  
       if :old.grade is not null then  
          update student set sumG=sumG-:old.grade where sno=:old.sno;  
       end if;  
       --看成绩是否减到0  
        select sumG into vsumG from student where sno=:old.sno;  
        if vsumG=0 then --还没有成绩的同学  
           update student set sumG=null where sno=:old.sno;  
        end if;  
    end if;  
      
    if updating then  
      --如果插入的成绩为0  
      if :new.grade=0 then  
          :new.grade:=null;  
      end if;  
      --有成绩就更改student表中sumG的数据  
      if :new.grade is not null then  
          select sumG into vsumG from student where sno=:new.sno;  
          if vsumG is null then --还没有成绩的同学  
             update student set sumG=:new.grade where sno=:new.sno;  
          else  
             update student set sumG=sumG+:new.grade where sno=:new.sno;  
          end if;  
      end if;  
      --删除成绩不为空就更改student表中sumG的数据  
       if :old.grade is not null then  
          update student set sumG=sumG-:old.grade where sno=:old.sno;  
       end if;  
        --看成绩是否减到0  
        select sumG into vsumG from student where sno=:old.sno;  
        if vsumG=0 then --还没有成绩的同学  
           update student set sumG=null where sno=:old.sno;  
       end if;  
    end if;  
end;