17,086
社区成员
发帖
与我相关
我的任务
分享
create or replace function f_get_empname return sys_refcursor is
type_cur SYS_REFCURSOR;
begin
open type_cur for
select * from scott.emp;
return type_cur;
end;
declare
v_ref sys_refcursor;
rec scott.emp%rowtype;
begin
v_ref := f_get_empname;
loop
fetch v_ref into rec;
exit when v_ref%notfound;
dbms_output.put_line(rec.empno || rec.ename);
end loop;
end;
其实你这个函数只会返回最后一条记录,因为loop循环完到最后一条记录才return
create or replace function f_get_empname return scott.emp%rowtype is
v_name scott.emp%rowtype;
cursor c1 is
select * from scott.emp;
begin
open c1;
loop
fetch c1
into v_name;
exit when c1%notfound;
end loop;
return v_name;
exception
when others then
if c1%isopen then
close c1;
end if;
dbms_output.put_line('函数异常');
end;
declare
v_name scott.emp%rowtype;
begin
--把所有字段都这样赋值
v_name := f_get_empname;
dbms_output.put_line(v_name.empno || v_name.ename);
end;