Oracle课后练习-7

练习目的：

1、区分隐式游标和显式游标，掌握如何创建游标

2、会编写游标式的FOR循环

3、在游标中使用FOR UPDATE子句和WHERE CURRENT OF子句

4、掌握PL/SQL异常和自定义PL/SQL异常的使用

5、掌握过程、函数的创建方法，熟悉参数的各种传递方式之间的区别

 

 

准备工作：

添加数据到emp表

Insert into emp values(8000, '你的名字', 'CLERK', 7698, SYSDATE, 10000, NULL, 10);
COMMIT;

 

练习

1、创建一个PL/SQL块，要求在键盘中输入部门号，屏幕上显示该部门的所有雇员的雇员号、雇员姓名和工资（要求定义有参游标实现，要求分别使用两种方式遍历游标：while、for）

例如，输入10，输出结果如下：

部门10的雇员具体信息如下：
雇员号为7782的姓名是CLARK，工资是2450
雇员号为7839的姓名是KING，工资是5000
雇员号为7934的姓名是MILLER，工资是1300
该部门雇员总人数有3人

提示：不换行的输出语句dbms_output.put()

换行的输出语句dbms_output.put_line()

--while方式遍历游标

declare
 cursor cur_emp(p_deptno emp.deptno%type:=10)
 is 
  select empno,ename,sal
  from emp
  where deptno=p_deptno;
  v_emp cur_emp%rowtype;
  v_peo number:=0;
begin
  open cur_emp(&p_deptno);
  fetch cur_emp into v_emp;
  while cur_emp%found loop
	  dbms_output.put('雇员号为' || v_emp.empno );
	  dbms_output.put('的姓名是：' || v_emp.ename);
	  dbms_output.put_line(' ,工资是：' || v_emp.sal );
	  v_peo :=v_peo+1;
	  fetch cur_emp into v_emp;
  end loop;
  dbms_output.put_line('该部门雇员总人数有'||v_peo||'人');
  close cur_emp;
end;
/

--for方式遍历游标

declare
 cursor cur_emp(p_deptno emp.deptno%type:=10)
 is 
  select empno,ename,sal
  from emp
  where deptno=p_deptno;
  v_peonum number:=0;
begin
  for v_emp in cur_emp(&v_deptno) loop
	  dbms_output.put('雇员号为' || v_emp.empno );
	  dbms_output.put('的姓名是：' || v_emp.ename);
	  dbms_output.put_line(' ,工资是：' || v_emp.sal );
	  v_peonum:=v_peonum+1;
	  exit when cur_emp%notfound;
	  dbms_output.put_line('该部门雇员总人数有'||v_peonum||'人');
  end loop;
end;
/

2、创建一个PL/SQL匿名块，检索出工资最高的前n名雇员的姓名及工资（如果有多名雇员工资相同的情况，则每一个雇员都占n名中的其中一个名额，即最后只显示n个人）。

（1）先使用下面的SQL语句创建一个临时表top_dogs，用于保存雇员的姓名和工资。

CREATE TABLE top_dogs
( tname     VARCHAR2(25),
  salary   NUMBER(11,2));

（2）在匿名块中，要求如下：

1. 通过SQL*Plus替代变量读取要检索的雇员人数n。
2. 通过循环，从EMP表中获取工资最高的前n名雇员的姓名及工资。
3. 将检索出的雇员姓名和工资插入TOP_DOGS表。
4. 准备向TOP_DOGS写入数据前都需要清空该表。
5. 测试三种情况：输入n值正常情况n=3,特殊情况n=0、n=18。（每次测试后均要查询表TOP_DOGS以验证操作是否正确）

--匿名块

declare
 cursor cur_emp(p_n number)
 is
  select ename,sal
  from 
  (select * from emp order by sal desc)
  where rownum<=p_n;
 v_clean varchar2(200):='truncate table top_dogs'; 
begin
 execute immediate v_clean;
 for v_emp in cur_emp(&v_rownum) loop
  dbms_output.put('雇员名：'|| v_emp.ename);
  dbms_output.put_line(' 工资为：'|| v_emp.sal);
  insert into top_dogs values(v_emp.ename,v_emp.sal);
  exit when cur_emp%notfound;  
 end loop;
end; 
/

3、创建一个PL/SQL匿名块，在块中使用游标删除emp表中工资低于1000的雇员信息。要求如下：

• 请用for update of子句和where current of子句完成本道题
• 操作结束后要查询表emp以验证是否正确
• 验证正确后，请撤销刚才的删除操作

--匿名块

declare
 cursor emp_cursor
 is
 select *
 from emp
 where sal<1000
 for update of sal;
begin
 for record in emp_cursor loop
  delete from emp where current of emp_cursor;
 end loop;
end;
/

4、编写一个PL/SQL匿名块，根据用户输入的工资，获得该工资的雇员姓名（此处要求返回结果只能一行，找不到数据行和查询到多行的都属于异常）。按照下面步骤完成：

(1) 先输入下面SQL语句，创建MESSAGES表。

create table messages

(results varchar2(200));

（2）在匿名块中要求如下：

如果正确的查询到1行结果，向MESSAGES表写入获得的该工资的雇员姓名及其工资数额，写入的字符串格式如后面的测试表。

如果查询的结果多于1行，则调用适当的异常处理。在异常处理器中，向MESSAGES表写入一条信息“有多个雇员工资都为***。”

如果没有查询到任何结果，则调用适当的异常处理。在异常处理器中，向MESSAGES表写入一条信息“没有雇员工资为***。”

加入其他异常处理，在异常处理器中，撤销之前的操作，并向MESSAGES表写入“其他错误发生。”

分别输入（10000,3000,6000）测试该匿名块。

在测试后查询MESSAGE表，其表格结果应为：

RESULTS

你的名字-10000

有多个雇员工资都为3000

没有雇员工资为6000

 

--匿名块

declare
 v_samesal emp.sal%type:=&v_sal;
 v_ename emp.ename%type;
 v_sal emp.sal%type;
begin
 select ename,sal into v_ename,v_sal
 from emp
 where sal=v_samesal;
 insert into messages values(v_ename||'-'||v_sal);
 exception
  when TOO_MANY_ROWS then
   dbms_output.put_line('有多个雇员工资都为：'||v_samesal);
   insert into messages values('有多个雇员工资都为：'||v_samesal);
  when NO_DATA_FOUND then
   dbms_output.put_line('没有雇员工资为：'|| v_samesal);
   insert into messages values('没有雇员工资为：'|| v_samesal);
end;
/

5、创建一个脚本lab12_5.sql，实现根据输入的部门编号来更新现有部门的地址。

（1）脚本内容包含如下：

（a）使用SQL *Plus替代变量输入部门编号，新的部门地址。输入格式要求如下：

请输入要更新的部门号：xx
请输入新的部门地址：xxxxx

（b）创建一个PL/SQL匿名块，在块中根据输入的部门号来更新部门地址为输入的新的部门地址。如果用户输入的部门号不存在，则调用异常处理，在异常处理器中将错误信息放入绑定变量g_message中（信息为“部门号***是不存在的部门！”）。

（2）执行该脚本，输入如下数据进行测试：

• 部门编号40，部门地址FU ZHOU，并用命令显示绑定变量的值。
• 部门编号50，部门地址HOUSTON，并用命令显示绑定变量的值。

--设置绑定变量g_message
Var g_message varchar2(50);

--------------------------------------
--匿名块

 accept v_deptno  prompt '请输入要更新的部门号:';
 accept v_loc  prompt '请输入新的部门地址:';
 
declare
 v_deptnoget dept.deptno%type:=&v_deptno;
 v_locget dept.loc%type:='&v_loc';
 exception_null exception;
 cursor dept_cursor(p_n dept.deptno%type)
 is
 select * from dept 
 where deptno=p_n;
 v_rowdept dept_cursor%rowtype;
begin
 open dept_cursor(v_deptnoget);
 fetch dept_cursor into v_rowdept;
 if (dept_cursor%notfound) then
  raise exception_null;
 end if;
 
 update dept set loc=v_locget where deptno=v_deptnoget;
 
 exception
  when exception_null then
   :g_message:=('部门号'||v_deptnoget||'是不存在的部门!');
  
end;

/

6、创建并调用过程add_dept，观察结果。具体步骤如下：

（1）先创建名为add_dept的过程，通过传入两个参数得到部门编号和部门名称，再将传入的该部门信息插入到表DEPT中。要求在该过程中要有事务提交处理和插入重复部门的异常处理。

--add_dept存储过程

create or replace procedure add_dept
(v_deptno in number,v_dname in varchar2)
is
begin
 insert into dept(deptno,dname) values(v_deptno,v_dname);
 Commit;
 exception
  when DUP_VAL_ON_INDEX then
   dbms_output.put_line('插入重复部门');
end add_dept;
/

（2）再创建一个匿名块，调用上面的过程add_dept。以你的本课程座位号为部门编号，姓名为部门名称调用该过程。结果如何？查询DEPT表验证显示结果。

--匿名块

declare 
begin
  add_dept(&v_deptno,'&v_dname');
end;
/

（3）再次执行第二步的匿名块（以你的本课程座位号为部门编号，姓名为部门名称再次调用过程add_dept）。结果又如何？请说明原因。

--再次调用匿名块

declare 
begin
  add_dept(&v_deptno,'&v_dname');
end;
/

7、创建并调用过程COUNT_EMP，观察结果。具体步骤如下：

（1）先创建一个过程COUNT_EMP，通过参数传入部门编号，并通过参数传出该部门的雇员人数。

--count_emp过程

create or replace procedure count_emp
(v_deptno in number,v_number out number)
as
begin
 select count(*) into v_number from emp where deptno=v_deptno;
end count_emp;
/

（2）再创建一个匿名块。在匿名块中，实现功能：输入部门号并作为其中一个参数调用上面的过程COUNT_EMP，最后显示传出的该部门的雇员人数。显示格式如下：

部门10有3个雇员

--匿名块

declare
 v_number number;
 v_deptno number;
begin
 count_emp(&v_deptno,v_deptno);
 dbms_output.put('部门'||v_deptno);
 dbms_output.put_line('有'|| v_number || '个雇员');
end;
/

8、创建名为ANNUAL_COMP的函数，通过接收两个参数（某个员工的月工资pi_sal和奖金pi_comm）计算年薪并返回年薪。该函数中要求进行空值处理（即工资和奖金为null时都视为0）。

（1）创建函数ANNUAL_COMP，传递两个参数（某个员工的月工资pi_sal和奖金pi_comm），使用下面的公式计算年薪：年薪=(月工资*12)+奖金。要求这两个值要进行空值处理。

--ANNUAL_COMP 函数

create or replace function ANNUAL_COMP
(pi_sal in emp.sal%type,pi_comm in emp.comm%type)
return number
is
  v_sal emp.sal%type:=nvl(pi_sal,0);
  v_comm emp.comm%type:=nvl(pi_comm,0);
  v_yearsal emp.sal%type;
begin
  v_yearsal:=(v_sal*12)+v_comm; 
  return v_yearsal;
end ANNUAL_COMP;
/

（2）要求用SQL查询语句来调用函数，以显示20号部门所有的雇员编号、姓名、工资、奖金以及年薪（年薪要求调用函数获得）。

--查询语句

select empno,ename,sal,comm,ANNUAL_COMP(sal,comm)
from emp
where deptno=20
/

9、本题完成步骤如下：

（1）创建名为VALID_DEPTNO的函数，以确认指定的部门号是否存在于DEPT部门表中。该函数应返回一个BOOLEAN型值。

--valid_deptno函数

create or replace function valid_deptno
(pi_deptno in dept.deptno%type)
return boolean
is
  cursor cur_dept(v_deptno dept.deptno%type)
  is
  select * from dept
  where deptno=v_deptno;
  v_rowdept cur_dept%rowtype;
begin
  open cur_dept(pi_deptno);
  fetch cur_dept into v_rowdept;
  if cur_dept%notfound then
   return false;
  else 
   return true;
  end if;
  close cur_dept;
end valid_deptno;
/

（2）再创建过程NEW_EMP，向表EMP中新增一个雇员。具体要求如下：

• 这个过程应该包括对函数VALID_DEPTNO的调用，以检查表DEPT中是否存在新雇员的deptno。
• 如果函数VALID_DEPTNO返回值为TRUE，表EMP中就新增一行。如果函数返回值是FALSE，该过程就显示警告信息 “部门编号<deptno>不存在”。
• 插入重复的雇员信息时要有错误提示‘该雇员已存在！’
• 传入参数的个数为雇员表的列数，其中参数分别定义为pi_empno，pi_ename,pi_ hiredate，pi_deptno,pi_job,pi_mgr,pi_sal,pi_comm
• 为参数定义默认值：pi_comm的默认值为0，pi_sal的默认值为1000，pi_deptno默认值为20，pi_job的默认值为SALESMAN，pi_mgr的默认值为7566。

--new_emp过程

create or replace procedure new_emp
(pi_empno in emp.empno%type,
 pi_ename in emp.ename%type,
 pi_hiredate in emp.hiredate%type,
 pi_deptno in emp.deptno%type default 20,
 pi_job in emp.job%type default 'SALESMAN',
 pi_mgr in emp.mgr%type default 7566,
 pi_sal in emp.sal%type default 1000,
 pi_comm in emp.comm%type default 0
)
as
begin
 if valid_deptno(pi_deptno) then 
  insert into emp values
  (pi_empno,pi_ename,pi_job,pi_mgr,pi_hiredate,pi_sal,pi_comm,pi_deptno);
 else 
  dbms_output.put_line('部门编号:'||pi_deptno||'不存在!'); 
 end if;
 exception 
  when DUP_VAL_ON_INDEX then
   dbms_output.put_line('该雇员已存在!');
end new_emp;
/

（3）编写匿名块，使用命名表示法调用过程NEW_EMP，新增一名deptno为55的雇员，雇员编号为9021，名字为Joe，雇佣日期为当前系统时间。剩余的参数均使用默认值。运行后的结果是什么？

--匿名块

declare
begin
 new_emp(pi_deptno=>55,pi_empno=>9021,pi_ename=>'Joe',pi_hiredate=>sysdate);
end;
/