在scott/tiger下测试

1、建立function

首先设置SQL> set serveroutput on;

create or replace function fgetEmp(depid in number, ret out varchar2)
return varchar2 is
type rec is table of emp%rowtype index by binary_integer;
emprec rec;
cursor cur1 is
select * from emp where deptno = depid;
j number := 1;
my_exception exception;
begin
ret := 'error';
open cur1; --下面的loop需要显示的open close 游标
loop
fetch cur1
into emprec(j);
exit when cur1%NOTFOUND;
if cur1%NOTFOUND then
raise my_exception;
end if;
dbms_output.put_line('fetch--' || emprec(j).empno || '--' || emprec(j)
.ename || '--' || emprec(j).deptno);
j := j + 1;
end loop;
close cur1;
for i in cur1 loop
--不需要显示的open close 游标
dbms_output.put_line(i.empno || '--' || i.ename || '--' || i.deptno);
end loop;
ret := 'success';
return ret;
exception
when my_exception then
dbms_output.put_line('no data');
return ret;
when others then
dbms_output.put_line('others error : sqlcode=' || sqlcode ||
' ;sqlerror:' || sqlerrm);
return ret;
end;

2、测试function

create or replace procedure test(dep number) as

ret varchar2(100) := 'ret';
begin

ret := fgetemp(dep, ret);
dbms_output.put_line(ret);
end;