oracle plsql例子,oracle plsql例子

在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;[@more@]