oracle中定义package以及存储过程的使用
使用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;
本文永久更新链接地址：