使用scott账户下的dept表；
select * from dept order by deptno;
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
为了演示方便，插入一条数据：
insert into dept(deptno, dname, loc) values(50,'SYSTEM', 'NEW YORK');
新插入的记录为：50 SYSTEM NEW YORK
?
我们主要演示在package中存储过程的返回类型为pipelined，cursor 和 value三种。
1.返回类型为pipelined。
create or REPLACE type dept_obj is OBJECT( DEPTNO NUMBER(2,0), DNAME VARCHAR2(14 BYTE) );
create or REPLACE type dept_obj_type AS table of dept_obj;
2.定义package 和package body。
------------------------------------------------------
create or replace package SPTest
is
/*return a pipelined demo start*/
type dept_data_rec_type is RECORD(
? DEPTNO NUMBER(2,0),
? ? DNAME VARCHAR2(14)
);
type dept_ref_type is REF CURSOR;
function getDept(in_loc IN VARCHAR2) return dept_obj_type pipelined;
/*return a pipelined demo end*/
/*return a cursor demo start*/
FUNCTION getDeptInfo(in_deptno IN dept.deptno%TYPE) RETURN dept_ref_type;
/*return a cursor demo end*/
/* return a varchar value start */
function getName(in_deptno in number) RETURN VARCHAR2;
/* return a varchar value end */
end SPTest;
/
-----------------------------------------------------------------------------------------------
create or replace package body SPTest
is
? /*return a pipelined demo start*/
? function getDept(in_loc IN VARCHAR2) return dept_obj_type pipelined is
? ? l_dept_obj dept_obj :=dept_obj(null, null);
? ? dept_ref_type_cursor dept_ref_type;
? ? dept_data_rec? ? ? ? dept_data_rec_type;
? begin
? ? open dept_ref_type_cursor
? ? for select deptno, dname from dept where loc = in_loc;
?
? ? loop
? ? fetch dept_ref_type_cursor into dept_data_rec;
? ? exit when dept_ref_type_cursor%NOTFOUND;
? ? l_dept_obj.DEPTNO := dept_data_rec.DEPTNO;
? ? l_dept_obj.DNAME := dept_data_rec.DNAME;
?
? ? pipe row(l_dept_obj);
? ? end loop;
? ? close dept_ref_type_cursor;
? ? RETURN ;
? end getDept;
? /*return a pipelined demo end*/
?
? /*return a cursor demo start*/
? FUNCTION getDeptInfo(in_deptno IN dept.deptno%TYPE) RETURN dept_ref_type
? AS
? ? ? ? ? dept_ref_type_cursor dept_ref_type;? ? ? ?
? ? BEGIN
? ?
? ? OPEN dept_ref_type_cursor FOR
? ? ? ? ? SELECT deptno, dname, loc FROM dept where deptno = in_deptno;
? ?
? ? RETURN dept_ref_type_cursor;
? ?
? ? END getDeptInfo;
? /*return a cursor demo end*/
?
? /* return a varchar value start */
? function getName(in_deptno in number) RETURN VARCHAR2
? as rtn_deptname VARCHAR2(100);
? begin
? ? select dname into rtn_deptname from dept where deptno = in_deptno;
? ? RETURN rtn_deptname;
? end getName;
? /* return a varchar value start */
?
end SPTest;
/
------------------------------------------------------
最后，执行存储过程。
/*返回pipelined table */
select deptno, dname from table(SPTest.getDept('NEW YORK')) order by deptno;
/*返回cursor*/
select SPTest.getDeptInfo(10) from dual;
/*返回具体值*/?
select SPTest.getName(50) from dual;