目标
- 掌握Oracle数据库编程语言PL/SQL的基础知识，会用PL/SQL语言编写基本程序块。
- 理解游标的概念，掌握游标使用的四个步骤，能够使用游标实现在PL/SQL程序中处理查询结果集。
- 掌握存储过程的定义方法和执行方法。
- 理解触发器的原理，掌握触发器的使用方法，学会定义行级触发器、语句级触发器。
本文用到的关系模式
学生表Student(Sno,Sname,Ssex,Snation,Spolitical,Sbirth,Scollege,Smajor，Sclass)
课程表Course (Cno,Cname,Credit，Cproperty，Chour，Cterm)
选课表SC(Sno,Cno,Grade)
教师表Teacher（Tno，Tname，Tsex，Tbirth，Ttitle，Tcollege）
授课表TC（Id，Tno，Cno，Sclass，Semester，TimePlace）
- “学生”关系Student，由学号（Sno）、姓名（Sname）、性别（Ssex）、民族（Snation）、政治面貌（Spolitical）、出生日期（Sbirth）、学院（Scollege）、专业（Smajor）和班级（Sclass）组成。
- “课程”关系Course，由课程号（Cno）、课程名（Cname）、学分（Credit）、课程性质（Cproperty）、学时（Chour）和开设学期（Cterm）组成。
- “选课”关系SC由学号（Sno）、课程号（Cno）、成绩（Grade）组成。
- “教师”关系teacher，由职工号（Tno）、姓名（Tname），性别（Tsex），出生日期（Tbirth），职称（Ttitle）和所在学院（Tcollege）组成。
- “授课”关系TC，由课序号（Id），职工号（Tno），课程号（Cno），授课班级（Sclass），授课学期（Semester）和上课时间地点（TimePlace）组成。
供应商表s（sno, sname, city）
零件表p（pno, pname, color, weight）
工程项目表j（jno, jname, city）
供应情况表 spj（sno, pno, jno, qty）
- 供应商表 s 由供应商代码（sno）、供应商姓名（sname）、供应商所在城市（city）组成；
- 零件表 p 由零件代码（pno）、零件名（pname）、颜色（color）、重量（weight）组成；
- 工程项目表 j 由工程项目代码（jno）、工程项目名（jname）、工程项目所在城市（city）组成；
- 供应情况表 spj 由供应商代码（sno）、零件代码（pno）、工程项目代码（jno）、供应数量组成（qty），表示某供应商供应某种零件给某工程项目的数量为qty。
语句示例
存储过程部分
- 创建存储过程，根据调用时提供的学生姓名查询该学生所修课程的课程信息，在过程体中将课程号、课程名和成绩输出到输出窗口，在SQL窗口中给出过程调用语句块。
--procedure
create or replace procedure quary_course
(student_name in student.sname%type)
is
course_number course.cno%type;
course_name course.cname%type;
student_grade sc.grade%type;
cursor q_c is --游标创建
select course.cno, course.cname, sc.grade
from sc, course, student
where course.cno = sc.cno and student.sno = sc.sno and student_name = student.sname;
begin
open q_c; --利用游标实现功能
loop
fetch q_c into course_number, course_name, student_grade;
exit when q_c%notfound;
dbms_output.put_line(course_number||'，'||course_name||'，'||student_grade);--输出查询结果
end loop;
close q_c;
end quary_course;
--sql脚本
declare
a varchar2(40):='符世园';
begin
quary_course(a);
end;
- 创建存储过程，查询供应量在指定范围内的零件名称和供应商名，在过程体中将结果输出到输出窗口，并在SQL窗口中给出过程调用语句块。
--procedure
create or replace procedure quary_pnameAndsname
(RangeLeft in spj.qty%type,
RangeRight in spj.qty%type)
is
p_name p.pname%type;
s_name s.sname%type;
cursor q_ps is
select pname, sname
from s, p, spj
where s.sno = spj.sno and p.pno = spj.pno and spj.qty between RangeLeft and RangeRight;
begin
open q_ps;
loop
fetch q_ps into p_name, s_name;
exit when q_ps%notfound;
dbms_output.put_line(p_name||'，'||s_name);
end loop;
close q_ps;
end quary_pnameAndsname;
--sql脚本
declare
l number(10):=100;
r number(10):=200;
begin
quary_pnameAndsname(l, r);
end;
- 创建存储过程，将指定零件的重量增加指定的值，在SQL窗口中给出过程调用语句块。
--procedure
create or replace procedure Add_specifiedPweight
(sp_pno in p.pno%type,
sp_weight in p.weight%type) is
begin
update p
set weight = weight + sp_weight
where pno = sp_pno;
commit;
end Add_specifiedPweight;
--sql脚本
declare
p_no varchar2(10):='p6';
p_weight number(10):=10;
begin
Add_specifiedPweight(p_no, p_weight);
end;
- 创建存储过程，在学生表Student中插入一条完整的元组，在SQL窗口中给出过程调用语句块。
--procedure
create or replace procedure Insert_tuple_Student
(v_sno in student.sno%type,
v_sname in student.sname%type,
v_ssex in student.ssex%type,
v_nation in student.snation%type,
v_spolitical in student.spolitical%type,
v_sbirth in student.sbirth%type,
v_college in student.scollege%type,
v_smajor in student.smajor%type,
v_sclass in student.sclass%type) is
begin
insert into student
values(v_sno,v_sname,v_ssex,v_nation,v_spolitical,v_sbirth,v_college,v_smajor,v_sclass);
commit;
end Insert_tuple_Student;
--sql脚本
declare
v_sno varchar(40):='114514';
v_sname varchar(40):='华强';
v_ssex varchar(40):='男';
v_nation varchar(40):='汉族';
v_spolitical varchar(40):='群众';
v_sbirth date:=to_date('1992/11/16','yyyy/mm/dd');
v_college varchar(40):='信息工程学院';
v_smajor varchar(40):='数据科学与大数据技术';
v_sclass varchar(40):='大数据2班';
begin
Insert_tuple_Student(v_sno,v_sname,v_ssex,v_nation,v_spolitical,v_sbirth,v_college,v_smajor,v_sclass);
end;
- 创建存储过程，统计指定学生学号的平均成绩和选课门数，将统计结果用输出参数传递给主程序，在SQL窗口中调用存储过程，输出过程的返回结果。
--procedure
create or replace procedure Statistics_avgScoreAndcount
(v_sno in student.sno%type,
avg_score out number,
count_course out number) is
begin
select avg(sc.grade), count(sc.cno)
into avg_score, count_course
from sc
where sno = v_sno
group by sno;
end Statistics_avgScoreAndcount;
--sql脚本
declare
v_sno varchar(40):='105918';
avg_score number;
count_course number;
begin
Statistics_avgScoreAndcount(v_sno,avg_score,count_course);
dbms_output.put_line(avg_score||','||count_course);
end;
触发器部分
-
-
删除SPJ关系中所有数据。
delete from spj; commit; -
在插入和修改SPJ表中QTY属性列的值时用触发器实现约束：如果供应商编号为“S4”，供应任何零件的数量不能少于300，如果少于则自动改为300。
--trigger create or replace trigger UpdateSpj_Trigger 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 UpdateSpj_Trigger; -
在SPJ表中录入值进行验证。
--sql脚本 insert into spj values('s4','p2','j2',10); commit; update spj set qty = 20 where sno = 's4'; commit;
-
-
-
删除SC关系中的所有数据。
delete from sc; commit; -
在SC关系中增加新属性列Status，用来记录课程成绩的等级
alter table sc add Status varchar2(10); -
用触发器实现自动记录成绩等级，当插入和修改grade列的值时，如果grade在0－59分，status自动填写为“不及格”；grade在60－69分，status自动填写为“及格”；grade在70－89分，status自动填写为“良好”；grade在90以上status自动填写为“优秀”。
--trigger create or replace trigger Automatically_record_grades before insert or update on sc for each row declare -- local variables here v_status varchar(10); begin if :new.grade<60 then :new.status := '不及格'; elsif :new.grade>=60 and :new.grade<70 then :new.status := '及格'; elsif :new.grade>=70 and :new.grade<90 then :new.status := '良好'; else :new.status := '优秀'; end if; end Automatically_record_grades; -
在SC表中录入值进行验证。
insert into sc (sno, cno, grade) values('101388','1091102',55); commit;
-
-
-
创建转专业记录表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)-- Create table create table Change_major ( id varchar2(40), sno varchar2(40), sname varchar2(40), major_out varchar2(40), major_in varchar2(40), ctime date ); alter table Change_major add constraint pk_CM_ID primary key (ID); alter table Change_major add constraint fk_CM_SNO foreign key (SNO) references student (SNO); -
创建触发器，实现在转专业记录表change_major中自动登记转专业信息，即当修改学生表中属性列major的值时，系统自动在change_major中插入一条转专业的信息,转专业时间可以通过sysdate系统函数获取系统时间。
-- Create sequence create sequence CHANGE_LOG minvalue 1 maxvalue 999999 start with 1 increment by 1 cache 20; --trigger create or replace trigger Automatic_registration_to_professional_information after update on student for each row declare -- local variables here begin insert into change_major values(Change_log.Nextval,:new.sno,:new.sname,:old.smajor,:new.smajor,sysdate); end Automatic_registration_to_professional_information; --sql语句 update student set smajor = '水土保持与荒漠化防治' where sno = '101123'; commit; update student set smajor = '园林' where sno = '101123'; commit;
-
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
