关♥生活，关注互联网。
《数据库(oracle 10g)开发技术》课程实验及参考答案
《数据库开发技术》课程之实验七
PL/SQL 过程和函数
一、 实验目的
1． 掌握正确使用异常处理的方法。
2． 掌握局部（本地）过程和存储过程的正确使用方法。
3． 掌握局部（本地）函数和存储函数的正确使用方法。
二、 实验内容和要求
1. 创建一个显示 EMP 表中雇员总人数的存储过程。然后在程序块中和 SQL*Plus 环境中调用该存储过
程。
CREATE OR REPLACE PROCEDURE EMP_COUNT
AS
V_TOTAL NUMBER(10);
BEGIN
SELECT COUNT(*) INTO V_TOTAL FROM EMP;
DBMS_OUTPUT.PUT_LINE(‘雇员总人数为：’||V_TOTAL);
END;
/* 在匿名块中调用：*/
BEGIN
EMP_COUNT;
END;
/* 在 SQL*Plus 环境中调用：*/
Exec[ute] EMP_COUNT;
执行结果：
雇员总人数为：14
2. 编写一个存储函数函数，用于判断 DEPT 表中某一编号的部门是否存在，若存在此部门编号，则返
回 TRUE，否则返回 FALSE。
CREATE OR REPLACE FUNCTION GET_DNO(P_DNO IN NUMBER)
RETURN BOOLEAN IS
V_DNO EMP.DEPTNO%TYPE;
BEGIN
SELECT DISTINCT DEPTNO INTO V_DNO FROM EMP
WHERE DEPTNO=P_DNO;
RETURN TRUE;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN FALSE;
END;
3. 编写一过程，调用第 2 题的函数判断某一编号的部门是否存在，存在则输出该部门员工的姓名、工
作，否则提示不存在此部门或此部门无员工。
CREATE OR REPLACE PROCEDURE DEMP_OUT (P_DNO IN NUMBER) IS
BEGIN
IF GET_DNO(P_DNO) THEN
FOR EMP_REC IN (SELECT ENAME,JOB FROM EMP
WHERE DEPTNO=P_DNO) LOOP
DBMS_OUTPUT.PUT_LINE(‘姓名: ’|| EMP_REC.ENAME||lpad(‘ ‘,3)||‘工作: ’|| EMP_REC.JOB);
END LOOP;
ELSE
DBMS_OUTPUT.PUT_LINE(‘没有此部门号！’);
END IF;
END;
4. 编写一个 PL/SQL 程序块调用第 2 题的存储函数，查询并显示 30 号部门的人数。
DECLARE
v_deptno dept.deptno%type;
v_count int;
e_null exception;
BEGIN
v_deptno:=30;
IF GET_DNO(v_deptno) THEN
SELECT COUNT(*) INTO v_count FROM EMP WHERE deptno= v_deptno;
DBMS_OUTPUT.PUT_LINE(to_char(v_deptno)||’号部门的人数为：’|| to_char (v_count)|| ‘人’);
END IF;
EXCEPTION
when others then
raise_application_error(-20002,’程序运行过程中出现错误号为’||sqlcode||’错误信息为’||sqlerrm||’的错
误，请与系统管理员联系，以便尽快解决问题。’);
END;
执行结果：
30 号部门的人数为：6 人
PL/SQL 过程已成功完成。
5. 有一个权限控制的表中有三个字段，分别存放用户姓名、密码和权限级别。创建一个用户登录存储
函数，用户登录时核对用户名和密码，正确则函数返回真，否则返回假。要求有适当的异常处理，
还要求进行适当的测试以验证函数的正确性。
Create table login(uname varchar(12), pw varchar(12), right_lel varchar(12));
insert into login values(‘xiaoli’,’12345′,’DBA’);
insert into login values(‘zhangsan’,’zzz’,’GUEST’);
create or replace function func_login(in_uname login.uname%type, in_pw login.pw%type)
return boolean
is
v_pw login.pw%type;
begin
select pw into v_pw from login where uname=in_uname;
if v_pw=in_pw then
return true;
else
return false;
end if;
exception
when no_data_found then
raise_application_error(-20001,’不存在用户名为’|| in_uname||’的用户!’);
when others then
raise_application_error(-20002,’程序运行过程中出现错误号为’||sqlcode||’错误信息为’||sqlerrm||’的错
误，请与系统管理员联系，以便尽快解决问题。’);
end func_login;
–登录函数测试
Set serveroutput on
Set verify off
Declare
v_uname login.uname%type:=’&user_name’;
v_password login.pw%type:=’&password’;
begin
if func_login(v_uname, v_password) then
dbms_output.put_line(‘登录成功!’);
else
dbms_output.put_line(‘用户密码有误!’);
end if;
end;
/
输入 user_name 的值: xiaoli
输入 password 的值: 12345
登录成功!
PL/SQL 过程已成功完成。
6. *阅读、理解并执行以下程序，并在注释处完成填空，指明当条语句的作用或意义，一行不够写自行
加一行注释。最后要求写出你的上机执行结果，对此执行结果写出一句总结。
DECLARE
TYPE EmpTabTyp IS TABLE OF emp%ROWTYPE; — 定义一个嵌套表类型 EmpTabTyp
emp_tab EmpTabTyp := EmpTabTyp(NULL); — 显式调用构造函数进行表 emp_tab 的初始化
t1 NUMBER(5);
t2 NUMBER(5);
t3 NUMBER(5);
PROCEDURE get_time (t OUT NUMBER) IS — 过程头：创建过程 get_time，过程有一个输出
–参数 t , 参数模式为 OUT 模式，参数类型是 NUMBER 型。
/* ‘SSSSS’日期/时间格式的含义为：距离午夜的秒数（0 ~ 86399）。*/
BEGIN –以下语句将调用瞬间距离午夜的秒数以字符类型的形式赋给输出参数 t。
SELECT TO_CHAR(SYSDATE,’SSSSS’) INTO t FROM dual;
END;
PROCEDURE do_nothing1 (tab IN OUT EmpTabTyp) IS –过程有一个 IN OUT 模式的参数 tab，数
— 据类型是嵌套表类型 EmpTabTyp，根据 PL/SQL 的默认方式，实参与形参间是按值传递的。
BEGIN
NULL; — 过程 do_nothing1 是一个什么也不做的过程
END;
PROCEDURE do_nothing2 (tab IN OUT NOCOPY EmpTabTyp) IS –过程有一个 IN OUT 模式的参
— 数 tab，实参与形参间由于有编译提示 NOCOPY，所以是按引用传递的。
BEGIN NULL; END;
BEGIN
SELECT * INTO emp_tab(1) FROM emp WHERE empno = 7788; — 将雇员 7788 的信息存入表元
— 素 1 中
emp_tab.EXTEND(249999, 1); — 向表 emp_tab 追加第 1 个元素的 249999 个拷贝，即将元素 1 的
—值拷贝到表的 2..250000 位置中。
get_time(t1);
do_nothing1(emp_tab); — 将嵌套表 emp_tab 传递给过程 do_nothing1 的 IN OUT 形参
get_time(t2);
do_nothing2(emp_tab); — 将嵌套表 emp_tab 传递给过程 do_nothing2 的 IN OUT NOCOPY 形参
get_time(t3);
dbms_output.put_line(‘Call Duration (secs)’);
dbms_output.put_line(‘——————–‘);
dbms_output.put_line(‘Just IN OUT: ‘ || TO_CHAR(t2 – t1)); —显示出用值传递参数方式的执行用时（秒）。
dbms_output.put_line(‘With NOCOPY: ‘ || TO_CHAR(t3 – t2)); –显示出用引用传递方式的执行用时（秒）。
END;
/
Call Duration (secs) –250000 条记录，即 25 万条记录
——————–
Just IN OUT: 2
With NOCOPY: 0
PL/SQL 过程已成功完成。
Call Duration (secs) –2500000 条记录，即 250 万条记录
——————–
Just IN OUT: 381
With NOCOPY: 0
PL/SQL 过程已成功完成。
三、 实验报告
记录实验过程中遇到的困难和解决问题的方法。
四、 实验小结
分析本次实验主要综合运用了哪些知识点，你的运用情况如何等。
