oracle练习及答案

declare

var1 number; var2 number;

val_comm number; begin

select max(nvl(comm,0)) into var1 from myemp; select min(nvl(comm,0)) into var2 from myemp; val_comm:=var1-var2;

dbms_output.put_line(val_comm); end;

4． 根据表myEMP中deptno字段的值，为姓名为‘JONES’的雇员修改工资；若部门号为

10，则工资加100；部门号为20，加200；其他部门加400。 declare

c1 number; c2 number; begin

select deptno into c1 from emp where ename=?JONES?; if c1=10 then c2:=100; elsif c1=20 then c2:=200; else c2:=400; end if;

update emp set sal=sal+c2 where ename=?JONES?; commit; end;

5． 计算显示部门人数最多的部门号、人数、工资总和，以及部门人数最少的部门号、人数、

工资总和。

6． 计算myEMP中所有雇员的所得税总和。假设所得税为累进税率，所得税算法为：工资

收入为0－1000为免税；收入1000－2000者，超过1000的部分税率10％；2000－3000者超过2000部分按20％税率计算；3000－4000者超过3000部分按30％税率计算；4000以上收入，超过4000部分按40％税率计算。（请查阅累进税率的概念） declare

sum_xx number:=0; xx number; begin

--计算收入为1000－2000的所得税总额

select sum((sal-1000)*0.1) into xx from emp where sal >1000 and sal<=2000; sum_xx:=sum_xx+xx;

--计算收入为2000－3000的所得税总额

select sum((sal-2000)*0.2+100) into xx from emp where sal >2000 and sal<=3000; sum_xx:=sum_xx+xx;

--计算收入为3000－4000的所得税总额

select sum((sal-3000)*0.3+300) into xx from emp where sal >3000 and sal<=4000; sum_xx:=sum_xx+xx;

--计算收入为4000以上的所得税总额

select sum((sal-4000)*0.4+600) into xx from emp where sal >4000; sum_xx:=sum_xx+xx;

dbms_output.put_line(sum_xx); end;

7． *（可选做，难题）假设有个表如myEMP，未建立主键，含有多条记录重复（列值完全

相同），试编制一个PL/SQL，将多余的重复记录删除。 实验六、

1． 用外部变量，实现两个PL/SQL程序间的数据交换。 SQL> variable a1 number; SQL> begin 2 :a1:=1000; 3 end; 4 /

PL/SQL 过程已成功完成。

SQL> begin

2 dbms_output.put_line(:a1); 3 end; 4 / 1000

PL/SQL 过程已成功完成。

2． 插入myEMP表中的数据记录，考虑可能出现的例外，并提示。 主要的例外提示：唯一性索引值重复DUP_VAL_ON_INDEX

3． 删除myDEPT表中的数据记录一条，考虑例外情况，并提示。 主要的例外提示：违反完整约束条件

4． 将下列PL/SQL改为FOR游标 declare

cursor cur_myemp is select * from emp; r emp%rowtype; begin

open cur_myemp;

fetch cur_myemp into r; while cur_myemp%found loop

dbms_output.put_line(r.ename); fetch cur_myemp into r; end loop;

close cur_myemp; end;

5． 工资级别的表salgrade，列出各工资级别的人数。（用游标来完成） declare v1 number;

cursor cur1 is select * from salgrade; begin

for c1 in cur1 loop

select count(*) into v1 from emp where sal between c1.losal and c1.hisal; dbms_output.put_line('grade'||c1.grade||' '||v1); end loop; end;

实验七、

1． 在myEMP表中增加一个字段，字段名为EMPPASS，类型为可变长字符。

2． 建立一个存储过程，用于操作用户登录的校验，登录需要使用EMPNO和EMPPASS，

并需要提示登录中的错误，如是EMPNO不存在，还是EMPNO存在而是EMPPASS错误等。

create or replace procedure p_login( in_empno in emp.empno%type, in_emppass in emp.emppass%type, out_code out number, out_desc out varchar2) is

x1 emp.ename%type; x2 number; begin

select ename into x1 from emp where empno=in_empno;

select count(*) into x2 from emp where empno=in_empno and emppass=in_emppass; if x2=1 then out_code:=0; out_desc:=x1; else

out_code:=2;

out_desc:=?用户登陆密码错误！?; end if; exception

when NO_DATA_FOUND then out_code:=1;

out_desc:=?该用户号存在!?;

when TOO_MANY_ROWS then out_code:=3;

out_desc:=?该用户号有重复值!?; when others then out_code:=100;

out_desc:=?其他错误!?; end;

3． 建立一个存储过程，实现myEMP表中指定雇员的EMPPASS字段的修改，修改前必须

进行EMPPASS旧值的核对。

CREATE OR REPLACE PROCEDURE P_CHANGEPASS( IN_EMPNO IN EMP.EMPNO%TYPE, IN_OLDPASS IN EMP.EMPPASS%TYPE, IN_NEWPASS IN EMP.EMPPASS%TYPE, OUT_CODE OUT NUMBER, OUT_DESC OUT VARCHAR2) IS

X1 NUMBER; BEGIN

SELECT COUNT(*) INTO X1 FROM EMP WHERE EMPNO=IN_EMPNO AND EMPPASS=IN_OLDPASS; IF X1=1 THEN

update emp set emppass=in_newpass where empno=in_empno; commit;

OUT_CODE:=0;

OUT_DESC:=?修改口令成功?; ELSE

OUT_CODE:=1;

OUT_DESC:=?修改口令不成功?; END IF; exception

when others then out_code:=100;

out_desc:=?其他错误?; END;

4． 建立一个函数，输入一个雇员号，返回该雇员的所在同一部门的最高级别上司姓名。

create or replace function f_leader(

in_empno in emp.empno%type) return varchar2 is

v1 number; v2 number;

v3 emp.ename%type; v4 emp.deptno%type;