分类： Oracle
2009-07-23 16:14:15
oracle上机练习（六）
一． 案例
某数据库有两张表：emp表和dept表，两张表的结构如下：
emp (emp_id number(5), emp_name varchar2(20), emp_salary number(4))；
dept (dept_id number(3), dept_name varchar2(20), emp_id number(5));
要求如下：
1、按照上表结构建立相应的表，为每张表写入5组合法数据。
2、操纵相关表，使得“技术部”的员工的薪水上涨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;
三．习题
1、创建一个过程，打印出各个工资级别的人数。备注：显示的格式为三种情况1、工资少于2000的人数为：****；工资在2000——3000的人数为****;工资大于3000的人数为:****
2、创建一个表salary_change_record（empid,old_salary,new_salary,change_date）,old_salary:用来纪录员工原来的工资，new_salary：用来纪录更新后的工资，change_date：记录更新的系统时间。然后创建一个触发器，名称为 change_record，功能：每次更新员工工资之后，将更新纪录保存到表salary_change_record中。
oracle上机练习（七）
——创建员工表
create table emp (emp_id number(5), emp_name varchar2(20), emp_salary number(4),job varchar2(20), dept_id number(3));
——部门表
create table dept (dept_id number(3), dept_name varchar2(20), loc varchar2(20));
1、编写一个数据库触发器，当任何时候某个部门从"dept"中删除时，该触发器将从"emp"表中删除该部门的所有雇员。
2、编写一个数据包，它有两个函数和两个过程以操作"emp"表。该数据包要执行的任务为：插入一个新雇员；删除一个现有雇员；显示指定雇 员的整体薪水；显示指定雇员所在部门名称。
3、编写一个函数以检查所指定雇员的薪水是否有效范围内。不同职位的薪水范围为：
Designation Raise
Clerk 1500-2500
Salesman 2501-3500
Analyst 3501-4500
Others 4501 and above.
如果薪水在此范围内，则显示消息"Salary is OK"，否则，更新薪水为该范围内的最水值。
练习六
[0]
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));
[1]创建一个过程,打印出各个工资级别的人数。备注：显示的格式为三种情况1、工资少于2000的人数为：****；工资在2000——3000的人数为****;工资大于3000的人数为:****。
create or replace procedure show_number as
cursor emp_cursor is
select emp_id, emp_name emp_salary from emp;
IndRec emp_cursor%ROWTYPE;
firstclass int;
secondclass int;
thirdclass int;
BEGIN
firstclass:=0;
secondclass:=0;
thirdclass:=0;
open emp_cursor;
loop
fetch emp_cursor into IndRec;
exit when emp_cursor%NOTFOUND;
if(IndRec.emp_salary <2000) then
firstclass:=firstclass+1;
elsif (IndRec.emp_salary < 3000) then
secondclass:=secondclass+1;
else
thirdclass:=thirdclass+1;
end if;
end loop;
close emp_cursor;
dbms_output.put_line('The number of emp whose salary<2000 is');
dbms_output.put_line(firstclass);
dbms_output.put_line('The number of emp whose salary<3000 but >2000 is');
dbms_output.put_line(secondclass);
dbms_output.put_line('The number of emp whose salary>3000is');
dbms_output.put_line(thirdclass);
END;
/
[2]创建一个表salary_change_record。然后创建一个触发器，名称为 change_record，功能：每次更新员工工资之后，将更新纪录保存到表salary_change_record中。
create table salary_change_record
(
empid NUMBER(4,0) NOT NULL,
old_salary NUMBER(6,2),
new_salary NUMBER(6,2),
change_salary_date DATE
);
create trigger on_salary_change
after update on emp for each row
when (old.emp_salary<>new.emp_salary)
begin
insert into salary_change_record values(:old.emp_id,:old.emp_salary,:new.emp_salary,sysdate);
end;
/
练习七
[1]
create table emp (empno number(5), ename varchar2(20), sal number(4),job varchar2(20), deptno number(3));
CREATE OR REPLACE FUNCTION Sal_Level(no emp.empno%TYPE) RETURN CHAR AS
vjob emp.job%TYPE;
vsal emp.sal%TYPE;
vmesg CHAR(50);
BEGIN
SELECT job,sal INTO vjob,vsal FROM emp WHERE empno=no;
IF vjob='CLERK' THEN
IF vsal>=1500 AND vsal<=2500 THEN
vmesg:='Salary is OK.';
ELSE
vsal:=1500;
vmesg:='Have updated your salary to '||TO_CHAR(vsal);
END IF;
ELSIF vjob='SALESMAN' THEN
IF vsal>=2501 AND vsal<=3500 THEN
vmesg:='Salary is OK.';
ELSE
vsal:=2501;
vmesg:='Have updated your salary to '||TO_CHAR(vsal);
END IF;
ELSIF vjob='ANALYST' THEN
IF vsal>=3501 AND vsal<=4500 THEN
vmesg:='Salary is OK.';
ELSE
vsal:=3501;
vmesg:='Have updated your salary to '||TO_CHAR(vsal);
END IF;
ELSE
IF vsal>=4501 THEN
vmesg:='Salary is OK.';
ELSE
vsal:=4501;
vmesg:='Have updated your salary to '||TO_CHAR(vsal);
END IF;
END IF;
UPDATE emp SET sal=vsal WHERE empno=no;
RETURN vmesg;
END;
/
DECLARE
vmesg CHAR(50);
vempno emp.empno%TYPE;
BEGIN
vempno:=&empno;
vmesg:=Sal_Level(vempno);
DBMS_OUTPUT.PUT_LINE(vmesg);
END;
/
[2]
CREATE OR REPLACE PACKAGE emppack AS
PROCEDURE insrec(pempno emp.empno%TYPE,pename emp.ename%TYPE,
pjob emp.job%TYPE,psal emp.sal%TYPE,pdeptno emp.deptno%TYPE);
PROCEDURE delrec(pempno IN NUMBER);
FUNCTION selsal(pempno NUMBER) RETURN NUMBER;
FUNCTION seldname(pempno NUMBER) RETURN VARCHAR2;
END emppack;
/
CREATE OR REPLACE PACKAGE BODY emppack AS
PROCEDURE insrec(pempno emp.empno%TYPE,pename emp.ename%TYPE,
pjob emp.job%TYPE,psal emp.sal%TYPE,pdeptno emp.deptno%TYPE)
IS
BEGIN
INSERT INTO emp VALUES(pempno,pename,pjob,psal,pdeptno);
DBMS_OUTPUT.PUT_LINE('1 record is created.');
END insrec;
PROCEDURE delrec(pempno IN NUMBER)
IS
BEGIN
DELETE FROM emp WHERE empno=pempno;
DBMS_OUTPUT.PUT_LINE('1 record is deleted.');
END delrec;
FUNCTION selsal(pempno NUMBER) RETURN NUMBER
IS
vTotalSal NUMBER;
BEGIN
SELECT NVL(sal,0) INTO vTotalSal
FROM emp
WHERE empno=pempno;
RETURN vTotalSal;
END selsal;
FUNCTION seldname(pempno NUMBER) RETURN VARCHAR2
IS
vdname dept.dname%TYPE;
BEGIN
SELECT dname INTO vdname
FROM emp,dept
WHERE empno=pempno AND emp.deptno=dept.deptno;
RETURN vdname;
END seldname;
END emppack;
/
[3]
create table dept (deptno number(3), dname varchar2(20), loc varchar2(20));
CREATE OR REPLACE TRIGGER sys.del_emp_deptno
BEFORE DELETE ON dept
FOR EACH ROW
BEGIN
DELETE FROM emp WHERE deptno=:OLD.deptno;
END;
/
