Oracle 存储过程 package 使用 Object type

亦凉 2022-04-10 07:17 298阅读 0赞

# [Oracle中定义package以及存储过程的使用][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 table，cursor 和 value三种。

1.返回类型为pipelined table。

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。

![复制代码][20181228155228154]

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 /

![复制代码][20181228155228154]

最后，执行存储过程。

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

标签: [Oracle][]

好文要顶 关注我 收藏该文 ![20181228155228258][] ![20181228155228297][]

[![20181228155228349][]][20181228155228349 1]

[林本托][20181228155228349 1]  
[关注 - 9][- 9]  
[粉丝 - 289][- 289]

\+加关注

[« ][Link 1]上一篇：[不同系统平台下Java默认的安装路径][Link 1]  
[» ][Link 2]下一篇：[《编写高质量代码：改善Java程序的151个建议》笔记][Link 2]

posted @ 2015-02-20 00:39 [林本托][Link 3] 阅读(3298) 评论(0) [编辑][Link 4] [收藏][Link 5]

[https://www.cnblogs.com/IcanFixIt/p/4296379.html][Oracle_package]

[Oracle_package]: https://www.cnblogs.com/IcanFixIt/p/4296379.html
[20181228155228154]: /images/20220402/6e5dd86ef9414877ad0dba581a6d456e.png
[Oracle]: https://www.cnblogs.com/IcanFixIt/tag/Oracle/
[20181228155228258]: https://img-blog.csdnimg.cn/20181228155228258
[20181228155228297]: /images/20220402/e3b912dccefd45298aeb4254a5a15ecf.png
[20181228155228349]: /images/20220402/a8bf9bc848504c94b844fdfe4302bab7.png
[20181228155228349 1]: http://home.cnblogs.com/u/IcanFixIt/
[- 9]: http://home.cnblogs.com/u/IcanFixIt/followees
[- 289]: http://home.cnblogs.com/u/IcanFixIt/followers
[Link 1]: https://www.cnblogs.com/IcanFixIt/p/4294984.html
[Link 2]: https://www.cnblogs.com/IcanFixIt/p/4297749.html
[Link 3]: https://www.cnblogs.com/IcanFixIt/
[Link 4]: https://i.cnblogs.com/EditPosts.aspx?postid=4296379
[Link 5]: https://www.cnblogs.com/IcanFixIt/p/4296379.html#