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

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

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

 1 create or replace package SPTest
 2 is
 3 /*return a pipelined demo start*/
 4 type dept_data_rec_type is RECORD(
 5   DEPTNO NUMBER(2,0),
 6      DNAME VARCHAR2(14)
 7 );
 8 
 9 type dept_ref_type is REF CURSOR;
10 
11 function getDept(in_loc IN VARCHAR2) return dept_obj_type pipelined;
12 /*return a pipelined demo end*/
13 
14 /*return a cursor demo start*/
15 FUNCTION getDeptInfo(in_deptno IN dept.deptno%TYPE) RETURN dept_ref_type;
16 /*return a cursor demo end*/
17 
18 /* return a varchar value start */
19 function getName(in_deptno in number) RETURN VARCHAR2;
20 /* return a varchar value end */
21 end SPTest;
22 /
23 -----------------------------------------------------------------------------------------------
24 create or replace package body SPTest
25 is
26   /*return a pipelined demo start*/
27   function getDept(in_loc IN VARCHAR2) return dept_obj_type pipelined is
28     l_dept_obj dept_obj :=dept_obj(null, null);
29     dept_ref_type_cursor dept_ref_type;
30     dept_data_rec        dept_data_rec_type;
31   begin
32     open dept_ref_type_cursor
33     for select deptno, dname from dept where loc = in_loc;
34    
35     loop
36     fetch dept_ref_type_cursor into dept_data_rec;
37     exit when dept_ref_type_cursor%NOTFOUND;
38     l_dept_obj.DEPTNO := dept_data_rec.DEPTNO;
39     l_dept_obj.DNAME := dept_data_rec.DNAME;
40    
41     pipe row(l_dept_obj);
42     end loop;
43     close dept_ref_type_cursor;
44     RETURN ;
45   end getDept;
46   /*return a pipelined demo end*/
47   
48   /*return a cursor demo start*/
49   FUNCTION getDeptInfo(in_deptno IN dept.deptno%TYPE) RETURN dept_ref_type
50   AS
51           dept_ref_type_cursor dept_ref_type;        
52      BEGIN
53     
54      OPEN dept_ref_type_cursor FOR
55           SELECT deptno, dname, loc FROM dept where deptno = in_deptno;
56     
57      RETURN dept_ref_type_cursor;
58     
59      END getDeptInfo;
60   /*return a cursor demo end*/
61   
62   /* return a varchar value start */
63   function getName(in_deptno in number) RETURN VARCHAR2
64   as rtn_deptname VARCHAR2(100);
65   begin
66     select dname into rtn_deptname from dept where deptno = in_deptno;
67     RETURN rtn_deptname;
68   end getName;
69   /* return a varchar value start */
70   
71 end SPTest;
72 /

最後，執行存儲過程。

/*返回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;