欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

Oracle中定义package以及存储过程的使用

程序员文章站 2022-06-10 17:41:12
...

Oracle中定义package以及存储过程的使用 使用scott账户下的dept表; select * from dept order by deptno; 10 ACCOUNTING NEW YO

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;