oracle子程序和包

实验: 子程序和包
实验目的
（1）掌握函数和子程序的创建和使用。

（2）掌握带参数的子程序的使用。

（3）掌握包的创建和使用。

实验内容
1、编写一个包emp_package,用来对emp表进行操作。

其中应包含以上几个功能：
（1）编写一个不带参数的过程dept_pro，功能是输出每个部门名称和部门人数，建议使用游标。

SQL> ed
已写入file afiedt.buf
1 create procedure emp_pro
2 is
3 CURSOR dept_cur IS select dname,count(empno)人数from emp,dept
4 where emp.deptno=dept.deptno group by dname;
5 begin
6 for v_dept in dept_cur loop
7 dbms_output.put_line(v_dept.dname||' '||v_dept.人数);
8 end loop;
9* end;
SQL> /
过程已创建。

（2）输入职工编号，得到职工姓名的存储过程或函数emp_pro或emp_fun
CREATE OR REPLACE PROCEDURE emp_pro
(
p_name OUT emp.ename%type,
p_sal OUT emp.sal%type,
p_eno IN emp.empno%type
)
IS
begin
-- Test statements here
select ename,sal into p_name,p_sal
from emp where empno=p_eno;
end;
CREATE OR REPLACE FUNCTION emp_fun(p_eno emp.empno%type)
RETURN emp.ename%type
IS
v_ename emp.ename%type;
begin
-- Test statements here
select ename into v_ename
from emp where empno=p_eno;
return v_ename;
end;
（3）编写一个函数dept_fun，向函数传递一个部门号，如果dept表中不包含这个部门号，则返回”无此部门。

SQL> ed
已写入file afiedt.buf
1 CREATE OR REPLACE FUNCTION dept_fun(v_deptno NUMBER:=&deptno)
2 RETURN dept.dname%type
3 IS
4 v_dname dept.dname%type;
5 begin
6 -- Test statements here
7 select dname into v_dname
8 from dept where deptno=v_deptno;
9 return v_dname;
10 EXCEPTION
11 WHEN NO_DA TA_FOUND THEN
12 DBMS_OUTPUT.PUT_LINE('无此部门');
13* end;
SQL> /
输入deptno 的值: 10
原值1: CREATE OR REPLACE FUNCTION dept_fun(v_deptno NUMBER:=&deptno)
新值1: CREATE OR REPLACE FUNCTION dept_fun(v_deptno NUMBER:=10)
函数已创建。

2、使用EXECUTE、SELECT或PL/SQL程序块来调用包中的过程和函数。

SQL> EXECUTE emp_pro
PL/SQL 过程已成功完成。

SQL> SET SERVEROUTPUT ON
SQL> EXECUTE emp_pro
ACCOUNTING 3
RESEARCH 5
SALES 6
PL/SQL 过程已成功完成。

SQL> select dept_fun(10) from dept;
DEPT_FUN(10)
--------------------------------------------------------------------------------
ACCOUNTING
ACCOUNTING
ACCOUNTING
ACCOUNTING
SQL> select dept_fun(20) from dept;
DEPT_FUN(20)
--------------------------------------------------------------------------------
RESEARCH
RESEARCH
RESEARCH
RESEARCH
SQL> select dept_fun(90) from dept;
select dept_fun(90) from dept
*
第1 行出现错误:
ORA-06503: PL/SQL: 函数未返回值
ORA-06512: 在"SCOTT.DEPT_FUN", line 13
ORA-06512: 在line 1
无此部门
_______________________________________________________________________________ CREATE OR REPLACE PACKAGE BODY emp_pak
IS
PROCEDURE emp_pro1
(
p_name OUT emp.ename%type,
p_sal OUT emp.sal%type,
p_eno IN emp.empno%type
)
IS
begin
-- Test statements here
select ename,sal into p_name,p_sal
from emp where empno=p_eno;
END emp_pro1;
procedure emp_pro2
is
CURSOR dept_cur IS select dname,count(empno) from emp,dept
where emp.deptno=dept.deptno group by dname;
begin
for v_dept in dept_cur loop
dbms_output.put_line(v_dept.dname||' '||v_dept.count(empno) );
end loop;
end emp_pro2;
FUNCTION emp_fun(p_eno emp.empno%type)
RETURN emp.ename%type
IS
v_ename emp.ename%type;
begin
-- Test statements here
select ename into v_ename
from emp where empno=p_eno;
return v_ename;
END emp_fun;
FUNCTION dept_fun(v_deptno NUMBER:=&deptno)
RETURN dept.dname%type
IS
v_dname dept.dname%type;
begin
-- Test statements here
select dname into v_dname
from dept where deptno=v_deptno;
return v_dname;
EXCEPTION
WHEN NO_DATA_FOUND THEN
Return '无此部门';
end dept_fun;
END emp_pak;。