建表代码我就不放了,只放了包和几个简单的触发器,最后有一些测试代码,希望对新手的学习有帮助。
--学生表的包
create or replace
package student_pack
is
--添加纪录
procedure add_student(stpk_record student%rowtype);
--删除记录
procedure del_student(stpk_id student.stu_id%type);
--修改记录
procedure update_student(stpk_record student%rowtype);
--查询学生信息(学生编号)(单表查询)
function get_stu(p_stu_id in student.stu_id%type)
return student%rowtype;
--根据学号查询所在院系及专业(单条件查询)
procedure get_stu_yz(p_stu_id in student.stu_id%type,
p_stu_dept out student.stu_dept%type,
p_major out student.major%type);
--判断学生是否存在(true_存在;false_不存在)
function exists_student_id(p_stu_id in student.stu_id%type)
return boolean;
end student_pack;
--学生包的主体
create or replace
package body student_pack
is
--判断学生是否存在(true_存在;false_不存在)
function exists_student_id(p_stu_id in student.stu_id%type) return boolean
is
stu_count int;
begin
select count(*) into stu_count from student where stu_id=p_stu_id;
if stu_count>0 then
return true;
else
return false;
end if;
end exists_student_id;
--添加纪录
procedure add_student(stpk_record student%rowtype)
is
begin
if exists_student_id(stpk_record.stu_id)=true then
dbms_output.put_line('添加失败,学生编号重复');
return;
end if;
insert into student
values(stpk_record.stu_id,stpk_record.stu_name,stpk_record.sex,
stpk_record.major,stpk_record.year,stpk_record.stu_dept);
dbms_output.put_line('添加成功');
end add_student;
--删除记录
procedure del_student(stpk_id student.stu_id%type)
is
begin
if exists_student_id(stpk_id)=false then
dbms_output.put_line('修改失败,没有该条记录');
return;
end if;
delete from student where stu_id=stpk_id;
dbms_output.put_line('删除成功');
end del_student;
--修改记录
procedure update_student(stpk_record student%rowtype)
is
begin
if exists_student_id(stpk_record.stu_id)=false then
dbms_output.put_line('修改失败,没有该条记录');
return;
end if;
update student set stu_id=stpk_record.stu_id,
stu_name=stpk_record.stu_name,
sex=stpk_record.sex,
major=stpk_record.major,
year=stpk_record.year,
stu_dept=stpk_record.stu_dept
where stu_id=stpk_record.stu_id;
dbms_output.put_line('修改成功');
end update_student;
--查询学生信息(学生编号)(单表查询)
function get_stu(p_stu_id in student.stu_id%type) return student%rowtype
is
stu_record student%rowtype;
no_result exception;
begin
if exists_student_id(p_stu_id)=false then
raise no_result;
else
select * into stu_record from student where stu_id=p_stu_id;
return stu_record;
end if;
exception
when no_result then
raise_application_error(-20000,'查询的学生不存在');
end get_stu;
--根据学号查询所在院系及专业(单条件查询)
procedure get_stu_yz(p_stu_id in student.stu_id%type,
p_stu_dept out student.stu_dept%type,
p_major out student.major%type)
is
begin
select stu_dept,major into p_stu_dept,p_major from student
where stu_id=p_stu_id;
end get_stu_yz;
end student_pack;
--学生包触发器
create or replace trigger trg_stu
before insert or update or delete on student
declare
v_now varchar2(30);
begin
v_now:=to_char(sysdate,'YYYY-mm-DD hh24:Mi:ss');
case
when inserting then
dbms_output.put_line(v_now||'对student表进行了insert操作');
when updating then
dbms_output.put_line(v_now||'对student表进行了update操作');
when deleting then
dbms_output.put_line(v_now||'对student表进行了delete操作');
end case;
end;
--教师表的包
create or replace
package teacher_pack
is
--添加纪录
procedure add_teacher(thpk_record teacher%rowtype);
--删除记录
procedure del_teacher(thpk_id teacher.th_id%type);
--修改记录
procedure update_teacher(thpk_record teacher%rowtype);
--查询教师信息(教师编号)(单表查询)
function get_th(p_th_id in teacher.th_id%type)
return teacher%rowtype;
--根据教师编号查询其学历,毕业院校(单条件查询)
procedure get_th_mm(p_th_id in teacher.th_id%type,
p_th_edu out teacher.th_edu%type,
p_th_univ out teacher.th_univ%type);
--判断教师是否存在(true_存在;false_不存在)
function exists_teacher_id(p_th_id in teacher.th_id%type)
return boolean;
end teacher_pack;
--教师包的主体
create or replace
package body teacher_pack
is
--判断教师是否存在(根据教师编号)(true_存在;false_不存在)
function exists_teacher_id(p_th_id in teacher.th_id%type)
return boolean
is
v_count int;
begin
select count(*) into v_count from teacher
where th_id=p_th_id;
if v_count>0 then
return true;
else
return false;
end if;
end exists_teacher_id;
--添加纪录
procedure add_teacher(thpk_record teacher%rowtype)
is
begin
if exists_teacher_id(thpk_record.th_id)=true then
dbms_output.put_line('添加失败,教师编号已存在');
return;
end if;
insert into teacher
values(thpk_record.th_id,
thpk_record.th_name,
thpk_record.age,
thpk_record.sex,
thpk_record.th_edu,
thpk_record.th_univ
);
dbms_output.put_line('添加成功');
end add_teacher;
--删除记录
procedure del_teacher(thpk_id teacher.th_id%type)
is
begin
if exists_teacher_id(thpk_id)=false then
dbms_output.put_line('删除失败');
return;
end if;
delete from teacher where th_id=thpk_id;
dbms_output.put_line('删除成功');
end del_teacher;
--修改记录
procedure update_teacher(thpk_record teacher%rowtype)
is
begin
if exists_teacher_id(thpk_record.th_id)=false then
dbms_output.put_line('修改失败');
return;
end if;
update teacher set th_name=thpk_record.th_name,
age=thpk_record.age,
sex=thpk_record.sex,
th_edu=thpk_record.th_edu,
th_univ=thpk_record.th_univ
where th_id=thpk_record.th_id;
dbms_output.put_line('修改成功');
end update_teacher;
--查询教师信息(教师编号)(单表查询)
function get_th(p_th_id in teacher.th_id%type)
return teacher%rowtype
is
thm teacher%rowtype;
no_result exception;
begin
if exists_teacher_id(p_th_id)=false then
raise no_result;
else
select * into thm from teacher
where th_id=p_th_id;
return thm;
end if;
exception
when no_result then
RAISE_APPLICATION_ERROR(-20001, '查询的教师不存在.');
end get_th;
--根据教师编号查询其学历,毕业院校(单条件查询)
procedure get_th_mm(p_th_id in teacher.th_id%type,
p_th_edu out teacher.th_edu%type,
p_th_univ out teacher.th_univ%type)
is
begin
select th_edu,th_univ into p_th_edu,p_th_univ
from teacher
where th_id=p_th_id;
end get_th_mm;
end teacher_pack;
--教师包触发器
create or replace trigger trg_th
before insert or update or delete on teacher
declare
v_now varchar2(30);
begin
v_now:=to_char(sysdate,'YYYY-mm-DD hh24:Mi:ss');
case
when inserting then
dbms_output.put_line(v_now||'对teacher表进行了insert操作');
when updating then
dbms_output.put_line(v_now||'对teacher表进行了update操作');
when deleting then
dbms_output.put_line(v_now||'对teacher表进行了delete操作');
end case;
end;
---课程表的包
create or replace
package course_pack
is
--添加纪录
procedure add_course(cspk_record course%rowtype);
--删除记录
procedure del_course(cspk_id course.cs_id%type);
--修改记录
procedure update_course(cspk_record course%rowtype);
--查询课程信息(课程编号)(单表查询)
function get_cs(p_cs_id in course.cs_id%type)
return course%rowtype;
--根据课程编号查询其学分,任课教师(单条件查询)
procedure get_cs_mm(p_cs_id in course.cs_id%type,
p_cs_credit out course.cs_credit%type,
p_cs_teacher out course.cs_teacher%type);
--判断课程是否存在(true_存在;false_不存在)
function exists_course_id(p_cs_id in course.cs_id%type)
return boolean;
end course_pack;
--课程表的包的主体
create or replace
package body course_pack
is
--判断课程是否存在(true_存在;false_不存在)
function exists_course_id(p_cs_id in course.cs_id%type)
return boolean
is
v_count int;
begin
select count(*) into v_count from course where cs_id=p_cs_id;
if v_count>0 then
return true;
else
return false;
end if;
end exists_course_id;
--添加纪录
procedure add_course(cspk_record course%rowtype)
is
begin
if exists_course_id(cspk_record.cs_id) then
dbms_output.put_line('添加失败');
return;
end if;
insert into course
values(cspk_record.cs_id,
cspk_record.cs_name,
cspk_record.cs_credit,
cspk_record.cs_teacher);
dbms_output.put_line('添加成功');
end add_course;
--删除记录
procedure del_course(cspk_id course.cs_id%type)
is
begin
if exists_course_id(cspk_id)=false then
dbms_output.put_line('删除失败');
else
delete from course where cs_id=cspk_id;
dbms_output.put_line('删除成功');
end if;
end del_course;
--修改记录
procedure update_course(cspk_record course%rowtype)
is
begin
if exists_course_id(cspk_record.cs_id)=false then
dbms_output.put_line('修改失败');
return;
end if;
update course set cs_name=cspk_record.cs_name,
cs_credit=cspk_record.cs_credit,
cs_teacher=cspk_record.cs_teacher
where cs_id=cspk_record.cs_id;
dbms_output.put_line('修改成功');
end update_course;
--查询课程信息(课程编号)(单表查询)
function get_cs(p_cs_id in course.cs_id%type)
return course%rowtype
is
coursem course%rowtype;
no_result exception;
begin
if exists_course_id(p_cs_id)=false then
raise no_result;
else
select * into coursem from course
where cs_id=p_cs_id;
return coursem;
end if;
exception
when no_result then
RAISE_APPLICATION_ERROR(-20001, '查询的信息不存在.');
end get_cs;
--根据课程编号查询其学分,任课教师(单条件查询)
procedure get_cs_mm(p_cs_id in course.cs_id%type,
p_cs_credit out course.cs_credit%type,
p_cs_teacher out course.cs_teacher%type)
is
begin
select cs_credit,cs_teacher into p_cs_credit,p_cs_teacher from course
where cs_id=p_cs_id;
end get_cs_mm;
end course_pack;
--课程包触发器
create or replace trigger trg_cs
before insert or update or delete on course
declare
v_now varchar2(30);
begin
v_now:=to_char(sysdate,'YYYY-mm-DD hh24:Mi:ss');
case
when inserting then
dbms_output.put_line(v_now||'对course表进行了insert操作');
when updating then
dbms_output.put_line(v_now||'对course表进行了update操作');
when deleting then
dbms_output.put_line(v_now||'对course表进行了delete操作');
end case;
end;
--成绩表的包
create or replace
package grade_pack
is
--添加纪录
procedure add_grade(gdpk_record grade%rowtype);
--删除记录
procedure del_grade(gdpk_stu_id grade.stu_id%type,gdpk_cs_id grade.cs_id%type);
--修改记录
procedure update_grade(gdpk_record grade%rowtype);
--查询成绩(学生编号,课程编号)(多条件查询)
function get_gradem(p_stu_id in grade.stu_id%type,p_cs_id in grade.cs_id%type)
return grade.g_grade%type;
--判断成绩是否存在(true_存在;false_不存在)
function exists_grade_id(p_stu_id in grade.stu_id%type,p_cs_id in grade.cs_id%type)
return boolean;
end grade_pack;
---成绩包的主体
create or replace
package body grade_pack
is
--判断成绩是否存在(true_存在;false_不存在)
function exists_grade_id(p_stu_id in grade.stu_id%type,p_cs_id in grade.cs_id%type)
return boolean
is
v_count int;
begin
select count(*) into v_count from grade where stu_id=p_stu_id and cs_id=p_cs_id;
if v_count>0 then
return true;
else
return false;
end if;
end exists_grade_id;
--添加纪录
procedure add_grade(gdpk_record grade%rowtype)
is
begin
if exists_grade_id(gdpk_record.stu_id,gdpk_record.cs_id) then
dbms_output.put_line('添加失败');
return;
end if;
insert into grade
values(gdpk_record.stu_id,gdpk_record.cs_id,gdpk_record.g_grade);
dbms_output.put_line('添加成功');
end add_grade;
--删除记录
procedure del_grade(gdpk_stu_id grade.stu_id%type,gdpk_cs_id grade.cs_id%type)
is
begin
if exists_grade_id(gdpk_stu_id,gdpk_cs_id)=false then
dbms_output.put_line('删除失败');
else
delete from grade where stu_id=gdpk_stu_id and cs_id=gdpk_cs_id;
dbms_output.put_line('删除成功');
end if;
end del_grade;
--修改记录
procedure update_grade(gdpk_record grade%rowtype)
is
begin
if exists_grade_id(gdpk_record.stu_id,gdpk_record.cs_id)=false then
dbms_output.put_line('修改失败');
return;
end if;
update grade set g_grade=gdpk_record.g_grade
where stu_id=gdpk_record.stu_id and
cs_id=gdpk_record.cs_id;
dbms_output.put_line('修改成功');
end update_grade;
--查询成绩(学生编号,课程编号)(多条件查询)
function get_gradem(p_stu_id in grade.stu_id%type,p_cs_id in grade.cs_id%type)
return grade.g_grade%type
is
gradem grade.g_grade%type;
no_result exception;
begin
if exists_grade_id(p_stu_id,p_cs_id)=false then
raise no_result;
else
select g_grade into gradem from grade
where stu_id=p_stu_id and
cs_id=p_cs_id;
return gradem;
end if;
exception
when no_result then
raise_application_error(-20001,'信息不存在');
end get_gradem;
end grade_pack;
--成绩包触发器
create or replace trigger trg_g
before insert or update or delete on grade
declare
v_now varchar2(30);
begin
v_now:=to_char(sysdate,'YYYY-mm-DD hh24:Mi:ss');
case
when inserting then
dbms_output.put_line(v_now||'对grade表进行了insert操作');
when updating then
dbms_output.put_line(v_now||'对grade表进行了update操作');
when deleting then
dbms_output.put_line(v_now||'对grade表进行了delete操作');
end case;
end;
--综合包
create or replace
package zh_pack
is
--判断学生是否存在(true false)(根据学号)
function exists_stu_id(p_id student.stu_id%type)
return boolean;
--判断学生是否存在(true false)(根据学生姓名)
function exists_stu_name(p_name student.stu_name%type)
return boolean;
--判断课程是否存在(根据课程名称)
function exists_cs_name(p_name course.cs_name%type)
return boolean;
--判断教师是否存在(根据教师姓名)
function exists_th_name(p_name teacher.th_name%type)
return boolean;
--查询指定学生姓名查询其所选的课程名称和成绩(多表查询)
procedure get_kcmcj(p_stu_name in student.stu_name%type,
p_cs_name out course.cs_name%type,
p_g_grade out grade.g_grade%type);
--查询指定课程名称的任课老师的信息(多表查询)
function get_teachermm(p_cs_name course.cs_name%type)
return teacher%rowtype;
--查询指定教师所教的学生的信息(多表查询)
function get_studentmm(p_th_name teacher.th_name%type)
return student%rowtype;
--查询指定学生编号所选课程的总学分(多表查询)
procedure get_zxf(p_stu_id in student.stu_id%type,
zxfmm out number);
end zh_pack;
--综合包的主体
create or replace
package body zh_pack
is
--判断学生是否存在(true false)(根据学生姓名)
function exists_stu_name(p_name student.stu_name%type)
return boolean
is
v_count int;
begin
select count(*) into v_count from student
where stu_name=p_name;
if v_count>0 then
return true;
else
return false;
end if;
end exists_stu_name;
--判断学生是否存在(true false)(根据学号)
function exists_stu_id(p_id student.stu_id%type)
return boolean
is
v_count int;
begin
select count(*) into v_count from student
where stu_id=p_id;
if v_count>0 then
return true;
else
return false;
end if;
end exists_stu_id;
--判断课程是否存在(根据课程名称)
function exists_cs_name(p_name course.cs_name%type)
return boolean
is
v_count int;
begin
select count(*) into v_count from course
where cs_name=p_name;
if v_count>0 then
return true;
else
return false;
end if;
end exists_cs_name;
--判断教师是否存在(根据教师姓名)
function exists_th_name(p_name teacher.th_name%type)
return boolean
is
v_count int;
begin
select count(*) into v_count from teacher
where th_name=p_name;
if v_count>0 then
return true;
else
return false;
end if;
end exists_th_name;
--查询指定学生姓名查询其所选的课程名称和成绩(单条件查询)
procedure get_kcmcj(p_stu_name in student.stu_name%type,
p_cs_name out course.cs_name%type,
p_g_grade out grade.g_grade%type)
is
begin
if exists_stu_name(p_stu_name)=false then
dbms_output.put_line('不存在信息');
return;
end if;
select course.cs_name,kcbhfs.g_grade into p_cs_name,p_g_grade
from (select cs_id,g_grade from grade
where stu_id =(select stu_id from student where stu_name=p_stu_name)) kcbhfs,course
where kcbhfs.cs_id=course.cs_id;
end get_kcmcj;
--查询指定课程名称的任课老师的信息(多表查询)
function get_teachermm(p_cs_name course.cs_name%type)
return teacher%rowtype
is
no_result exception;
thmm teacher%rowtype;
begin
if exists_cs_name(p_cs_name)=false then
raise no_result;
else
select * into thmm from teacher
where th_name=(
select cs_teacher from course
where cs_name=p_cs_name);
return thmm;
end if;
exception
when no_result then
raise_application_error(-20001,'信息不存在');
end get_teachermm;
--查询指定教师所教的学生的信息(多表查询)
function get_studentmm(p_th_name teacher.th_name%type)
return student%rowtype
is
stumm student%rowtype;
no_result exception;
begin
if exists_th_name(p_th_name)=false then
raise no_result;
else
select * into stumm from student
where stu_id=(
select stu_id from course,grade
where cs_teacher=p_th_name
and course.cs_id=grade.cs_id);
return stumm;
end if;
exception
when no_result then
raise_application_error(-20001,'不存在');
end get_studentmm;
--查询指定学生编号所选课程的总学分(多表查询)
procedure get_zxf(p_stu_id in student.stu_id%type,
zxfmm out number)
is
no_result exception;
begin
if exists_stu_id(p_stu_id)=false then
raise no_result;
else
select count(cs_credit) into zxfmm from course,grade
where course.cs_id=grade.cs_id
AND stu_id=p_stu_id;
end if;
exception
when no_result then
raise_application_error(-20001,'不存在');
end get_zxf;
end zh_pack;
--测试代码
---添加纪录
declare
stpk_record student%rowtype;
begin
stpk_record.stu_id:=&add_stu_id;
stpk_record.stu_name:='&stu_name';
stpk_record.sex:='&sex';
stpk_record.major:='&major';
stpk_record.year:=&year;
stpk_record.stu_dept:='&stu_dept';
student_pack.add_student(stpk_record);
commit;
end;
--删除记录
declare
stpk_record student%rowtype;
begin
stpk_record.stu_id:='&del_stu_id';
student_pack.del_student(stpk_record.stu_id);
end;
--查询学生信息根据学生编号(单表查询)
declare
stpk_record student%rowtype;
begin
stpk_record.stu_id:='&query_stu_id';
stpk_record:=student_pack.get_stu(stpk_record.stu_id);
dbms_output.put_line(stpk_record.stu_id||','||stpk_record.stu_name||','||stpk_record.sex
||','||stpk_record.major||','||stpk_record.year||','||stpk_record.stu_dept);
end;
--根据学号查询所在院系及专业(单条件查询)
declare
stpk_record student%rowtype;
begin
stpk_record.stu_id:='&query_stu_id';
stpk_record:=student_pack.get_stu(stpk_record.stu_id);
dbms_output.put_line(stpk_record.major||','||stpk_record.stu_dept);
end;
--查询成绩(学生编号,课程编号)(多条件查询)
declare
gdpk_record grade%rowtype;
begin
gdpk_record.stu_id:='&query_stu_id ';
gdpk_record.stu_id:='&query_cs_id ';
gdpk_record:=course_pack.get_cs(gdpk_record.stu_id );
gdpk_record:=course_pack.get_cs(gdpk_record.cs_id );
dbms_output.put_line(gdpk_record.g_grade);
end;
--查询指定教师所教的学生的信息(多表查询)
declare
stpk_record student%rowtype;
thpk_record teacher%rowtype;
begin
thpk_record.th_name:='&query_th_name';
thpk_record:=zh_pack.get_studentmm(p_th_name teacher.th_name);
dbms_output.put_line(stpk_record.stu_id||','||stpk_record.stu_name||','||stpk_record.sex
||','||stpk_record.major||','||stpk_record.year||','||stpk_record.stu_dept);
end;
--查询计算机系的同学信息(单条件查询)
select * from student where stu_dept = '计算机系';
--查询计算机系的男同学信息(多条件查询)
select * from student where stu_dept = '计算机系'and sex='男';
--根据学生姓名查询所学科目的成绩(左连接查询)
select stu_name,g_grade from student left outer join grade
on student.stu_id = grade.stu_id ;