最近写的一个简单的Oracle作业，学生选课系统

建表代码我就不放了，只放了包和几个简单的触发器，最后有一些测试代码，希望对新手的学习有帮助。

--学生表的包
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 ;