实验五：数据库编程

一、实验目的

1．掌握Oracle数据库编程语言PL/SQL的基础知识，会用PL/SQL语言编写基本程序块。

2．理解游标的概念，掌握游标使用的四个步骤，能够使用游标实现在PL/SQL程序中处理查询结果集。

3．掌握存储过程的定义方法和执行方法。

4．理解触发器的原理，掌握触发器的使用方法，学会定义行级触发器、语句级触发器。

二、实验内容

在实验一创建的表中用PL/SQL语言完成以下内容：

1．创建存储过程，根据调用时提供的学生姓名查询该学生所修课程的课程信息，

在过程体中将课程号、课程名和成绩输出到输出窗口，在SQL窗口中给出过程调用语句块。

存储过程p1:

create or replace procedure p1(v_sname in student.sname%type) is

    v_cno  sc.cno%type;

    v_cname course.cname%type;

    v_grade sc.grade%type;

    cursor c1 is

    select sc.cno,course.cname,sc.grade

    from sc,student,course

    where  sc.sno = student.sno and sc.cno=course.cno and student.sname=v_sname;

begin

   open c1;

    loop

        fetch c1 into v_cno,v_cname,v_grade;

       exit when c1%notfound;

       dbms_output.put_line(v_cno||'，'||v_cname||'，'||v_grade);

    end loop;

    close c1;

end;

sql窗口调用语句：

declare

p1_sname student.sname%type:='赵伟';

begin

p1(p1_sname);

end;

2．创建存储过程，查询供应量在指定范围内的零件名称和供应商名，在过程体中将结果输出到输出窗口，并在SQL窗口中给出过程调用语句块。

存储过程p2:

create or replace procedure p2(v_qty_down in spj.qty%type,v_qty_up in spj.qty%type)

is

    v_pname p.pname%type;

    v_jno  j.jno%type;

    cursor c2 is

    select  p.pname,j.jno

    from p,j,spj

    where p.pno = spj.pno and spj.jno = j.jno and qty between v_qty_down and v_qty_up;

begin

open c2;

   loop

       fetch c2 into v_pname,v_jno;

       exit when c2%notfound;

       dbms_output.put_line(v_pname||'，'||v_jno);

   end loop;

   close c2;

end p2;

sql窗口调用语句：

begin

  p2(100,400);

end;

 

3.创建存储过程，将指定零件的重量增加指定的值，在SQL窗口中给出过程调用语句块。

存储过程p3:

create or replace procedure p3(v_pno in p.pno%type)

is

begin

   update spj

   set qty = qty + 3

   where spj.pno=v_pno;

   commit;

end;

sql窗口语句调用：

declare

   p3_pno spj.pno%type := 'p4';

begin

   p3(p3_pno);

end;

 

4．创建存储过程，在学生表Student中插入一条完整的元组，在SQL窗口中给出过程调用语句块。

存储过程p4:

create or replace procedure p4(v_sno  s.sno%type,v_sname s.sname%type,

         v_ssex student.ssex%type,v_snation student.snation%type,

         v_spolitical student.spolitical%type,

         v_sbirth student.sbirth%type,v_scollege student.scollege%type,

         v_smajor student.smajor%type,v_sclass student.sclass%type)

as

begin

      insert into student(sno,

                          sname,

                          ssex,

                          snation,

                          spolitical,

                          sbirth,

                          scollege,

                          smajor,

                          sclass)

       values(v_sno,v_sname,v_ssex,v_snation,v_spolitical,v_sbirth,

       v_scollege,v_smajor,v_sclass);

       commit;

end;

Sql窗口调用语句：

begin

  p4('111','孙悟空','男',null,null,to_date('500/1/1','yyyy/mm/dd'),null,null,null);

end;

5．创建存储过程，统计指定学生学号的平均成绩和选课门数，将统计结果用输出参数

传递给主程序，在SQL窗口中调用存储过程，输出过程的返回结果。

存储过程p5:

create or replace procedure p5(v_sno in sc.sno%type,avg_grade out number, sum_c out number) is

begin

  select avg(grade),count(cno)

  into avg_grade,sum_c

  from sc,student

  where student.sno=v_sno and sc.sno=student.sno;

end;

sql窗口调用语句：

declare

   p5_sno  student.sno%type :='105256';

   avg_grad number;

   sum_course number;

begin

  p5(p5_sno,avg_grad,sum_course);

  dbms_output.put_line(avg_grad||'，'||sum_course);

end;

6．（1）删除SPJ关系中所有数据。

（2）在插入和修改SPJ表中QTY属性列的值时用触发器实现约束：如果供应商编号为“S4”，

  供应任何零件的数量不能少于300，如果少于则自动改为300。

（3）在SPJ表中录入值进行验证。

（1）

delete from spj;

select * from spj;

（2）创建的触发器t1:

create or replace trigger t1

  before insert or update

  on spj

  for each row

declare

  -- local variables here

begin

  if :new.sno='s4' and :new.qty<300 then

     :new.qty:=300;

  end if;

end t1;

（3）

插入时：

 

插入后，自动变为300：

 

插入时：

 

插入后，只有sno=’s4’且qty<300时，qty值改变，其他情况下，没有变化。

 

7．（1）删除SC关系中的所有数据。

（2）在SC关系中增加新属性列Status，用来记录课程成绩的等级。

（3）用触发器实现自动记录成绩等级，当插入和修改grade列的值时，如果grade在0－59分，status自动填写为“不及格”；

grade在60－69分，status自动填写为“及格”；grade在70－89分，status自动填写为“良好”；grade在90以上status

自动填写为“优秀”。

（4）在SC表中录入值进行验证。

（1）

delete from sc;

select *from sc;

 

（2）

alter table sc

add status varchar2(40);

（3）

创建触发器t2:

create or replace trigger t2

  before insert or update

  on sc

  for each row

declare

  -- local variables here

begin

  if :new.grade>=0 and :new.grade<=59 then

     :new.status:='不及格';

  end if;

  if :new.grade>=60 and :new.grade<=69 then

     :new.status:='及格';

  end if;

  if :new.grade>=70 and :new.grade<=89 then

     :new.status:='良好';

  end if;

  if :new.grade>=90  then

     :new.status:='优秀';

  end if;

end t2;

(4)

8.（1）创建转专业记录表change_major，转专业记录表中包括：编号属性列（ID），

学号属性列（sno），姓名属性列（sname），转出专业属性列（major_out）,转入专业属性列（major_in）

和转专业时间属性列（change_time），其中编号属性列ID为主码，学号属性列sno为外码，

参照学生表中主键sno，change_major的表结构为：

Change_major(ID,sno,sname,major_out,major_in,ctime)

（2）创建触发器，实现在转专业记录表change_major中自动登记转专业信息，即当修改学生表

中属性列major的值时，系统自动在change_major中插入一条转专业的信息,转专业时间可以通过

sysdate系统函数获取系统时间。

注意：change_major中的id属性列通过创建序列可设置为自动增长，有关序列详细的讲解见实验内容后的附录。

(1)建表

create table change_major (ID varchar2(20) primary key,sno varchar2(20) ,sname varchar2(50),

major_out varchar2(50),

major_in varchar2(50),ctime date,

foreign key(sno) references student(sno));

(2)

创建序列：

Create sequence seq_1

Increment by 1

Start with 1

Maxvalue 999999

Minvalue 1

Nocycle

Nocache;

创建触发器t3:

create or replace trigger t3

  after update

  on student

  for each row

declare

  -- local variables here

begin

   if (:new.smajor<>:old.smajor) then

         insert into change_major(id,

                                  sno,

                                  sname,

                                  major_out,

                                  major_in,

                                  ctime)

         values(seq_1.nextval,:new.sno,:new.sname,:old.smajor,:new.smajor,sysdate);

    end if;

end t3;

验证：

update student

set smajor='计算机科学与技术'

where sno='101417';