数据库案例分析

档案管理系统——学籍翻译系统
一．案例介绍
　　（部分举例）学籍管理数据库有两张表，是关于某高校学生信息和成绩信息的，它们分别是emp表和

dept表，两张表的结构如下：
    （1）学生基本信息表Student
    （2）成绩表Grade
　　要求如下：
　　1、按照上表结构建立相应的表，并每张表写入5组合法数据。
　　2、操纵相关表，使得“成绩优秀（90分以上）的”的学生的奖学金补助上涨20%。
　　3、建立日志，追踪补助变动情况。
　　4、建立测试包。
二．案例的分析与实现
    从前面案例的介绍不难看出，要求1考察点为基本SQL语句；要求2主要考察复合查询；要求3是考察

触发器的应用；要求4的考察面相对多一些，不仅考察了包的创建，而且也考察了在PL/SQL中的测试方法

。了解了这些考察的知识点，就可以一一去解决。
　　要求1：
　　首先根据前面表的结构可以创建两张表：
　　——创建学生基本信息表
　　create table emp (emp_id number(5), emp_name varchar2(20), emp_salary number(4));
　　——创建成绩表
　　create table dept (dept_id number(3), dept_name varchar2(20), emp_id number(5));
　　建立了表之后就可以往表里面写数据了，这里把添加表记录的代码写入到相应的存储过程。
　　/*给emp表添加记录的存储过程*/
　　create or replace procedure ins_table_emp(p_emp_id number,p_emp_name

varchar2,p_emp_salary number) as
　　v_emp_id number:=p_emp_id;
　　v_emp_name varchar2(20):=p_emp_name;
　　v_emp_salary number:=p_emp_salary;
　　begin
　　　insert into emp values (v_emp_id,v_emp_name,v_emp_salary);
　　end ins_table_emp;
　　/*给dept表添加记录的存储过程*/
　　create or replace procedure ins_table_dept(p_dept_id number,p_dept_name

varchar2,p_emp_id number) as
　　　v_dept_id number:=p_dept_id;
　　　v_dept_name varchar2(20):=p_dept_name;
　　　v_emp_id number:=p_emp_id;
　　begin
　　　insert into dept values (v_dept_id,v_dept_name,v_emp_id);
　　end ins_table_emp;
　　/*调用相应的存储过程实现记录添加*/
　　begin
　　　ins_table_emp(10000,'',4000);
　　　ins_table_emp(10001,'??èy',2300);
　　　ins_table_emp(10002,'3?t',3500);
　　　ins_table_emp(10003,'à???',3500);
　　　ins_table_emp(10004,'á?ò?',3500);
　　　ins_table_dept(111,'DD?t2?',10000);
　　　ins_table_dept(111,'DD?t2?',10001);
　　　ins_table_dept(111,'DD?t2?',10002);
　　　ins_table_dept(112,'??ê?2?',10003);
　　　ins_table_dept(113,'êD3?2?',10004);
　　end;
　　要求2：
　　给指定部门的员工加薪，这实际上是一个复合查询，首先需要把所有该部门的员工塞选出来，然后

对这些员工的薪水进行相应的改动。依照这一思路，代码如下：
　　（需要注意的是：将要加薪的部门作为参数，这样的存储过程更有灵活性。）
　　create or replace procedure add_salary(p_dept_name varchar2) as
　　v_dept_name varchar2(20):=p_dept_name;
　　begin
　　　update emp set emp.EMP_SALARY=emp.EMP_SALARY*1.2 where emp.EMP_ID in (select

emp.EMP_ID from emp,dept where emp.EMP_ID=dept.EMP_ID and dept.DEPT_ID='??ê?2?');
　　end add_salary;
　　要求3：
　　建立日志对薪水的变动情况形成一个追踪，也就是说，如果对某个职员的薪水进行变更就应该将其

相应的变更记录全部记下来。如果对emp表的salary字段创建一个触发器，来监视对salary的更改，把每

次更改进行记录，这样就达到了要求3的目的了。
　　create or replace trigger print_salary_change
　　before delete or insert or update on emp--触发事件
　　for each row-- 每修改一行都需要调用此过程
　　declare --只有触发器的声明需要declare，过程和函数都不需要
　　salary_balance number;
　　begin
　　--:new 与:old分别代表该行在修改前和修改后的记录
　　salary_balance=:new.salary=:old.salary;
　　dbms_output.PUT_LINE('old salary is: '|| :old.salary);
　　dbms_output.PUT_LINE('old salary is: '|| :new.salary);
　　dbms_output.PUT_LINE('old salary is: '|| to_char(salary_balance));
　　end print_salary_change;
　　要求4：
　　与其他语言（c/c++等）相比，PL/SQL的测试有其不同之处，归纳下来有三种方法：
　　1、使用DBMS_OUTPUT包的PUT_LINE方法来显示中间变量，以此来观察程序是否存在逻辑错误。
　　2、插入测试表的方法。即创建一个临时的中间表，然后把所有涉及到的中间变量的结果都作为记录

插入到中间表中，这样可以查询表中的结果来观察程序的执行情况。
　　3、使用异常处理手段，对可疑的程序段使用begin … end ，然后可以在exception里进行异常捕获

处理。
　　这里准备使用第二种方法来建立一个测试包，PL/SQL里包的概念类似于面向对象里的类的概念，包

将一组操作和属性封装在一起，不仅增强了程序的模块化，而且由于封装了更多的操作和属性而提高了

执行效能。建立一个PL/SQL需要两个步骤：首先要建立包头，类似于建立一个类的头文件，里面主要对

包中的过程，函数和变量的声明；第二部分主要是包体部分，实现前面声明的过程和函数，另外还需要

对包进行初始化等工作。
　　根据这一思路，建立测试包如下：
　　/*包头部分*/
　　create or replace package debug as
　　procedure debug(v_description varchar2, v_valueOfvariable varchar2)
　　　procedure reset;
　　　v_numberOfLine number;
　　end debug;
　　/*包体部分*/
　　create or replace package body debug as
　　procedure debug(v_description varchar2, v_valueOfvariable varchar2) is
　　begin
　　　insert into debugtable
　　　values(v_numberOfLine,v_description, v_valueOfvariable);
　　　v_numberOfLine:=v_numberOfLine+1;
　　end debug;
　　procedure reset is
　　begin
　　　v_numberOfLine:=1;
　　　delete from debugtable;
　　end reset;
　　/*初始化部分*/
　　begin
　　　reset;
　　end debug;