Oracle中定義package以及儲存過程的使用

原文地址：http:///Linux/2015-02/113720.htm

使用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

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;

/

3、定義package body

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;