oracle 存储过程返回结果集 table形式 (使用sys_refcursor 及程序包package 两种方式)

待我称王封你为后i 2022-04-08 11:46 1570阅读 0赞

# oracle 存储过程返回 结果集 table形式 (使用sys\_refcursor 及程序包package 两种方式) #

2015年11月27日 12:01:30 [cherish1forever][] 阅读数：9231 标签： [oracle][][存储过程][Link 1][sys\_refcursor][sys_refcursor][返回结果集][Link 2][返回table][table] 更多

个人分类： [oracle][oracle 1][SQL][]

1.创建一个表Test用来测试.

1.  `CREATE TABLE "TEST"`
2.  `( "AAA" NUMBER(*,0),`
3.  `"BBB" VARCHAR2(10 BYTE)`
4.  `)`

2.向Test表中插入测试数据

1.  `insert into Test values(1,'a');`
2.  `insert into Test values(2,'b');`
3.  `insert into Test values(3,'c');`
4.  `insert into Test values(1,'aaaa');`
5.  `insert into Test values(2,'bbbbb');`
6.  `insert into Test values(3,'cccccc');`

3.进入主题，创建存储过程

1.  `create or replace procedure pro1(`
2.  `param nvarchar2,`
3.  `mycur OUT sys_refcursor --游标，用于存储过程中返回结果集`
4.  `)`
5.  `as`
6.  `begin`
7.  `open mycur for select * from test where AAA=param ;`
8.  `end;`

4.执行存储过程

1.  `var type_cur refcursor`
2.  `var para nvarchar2(20)`
3.  `execute :para :='1';`
4.  `exec pro1(:para,:type_cur);`
5.  `print type_cur;`

5.使用程序包package创建存储过程 返回table

1.  `create or replace package pkg_HelloWorld as`
2.  `type myrctype is ref cursor; --定义游标类型`
3.  `procedure getHelloWorld (param nvarchar2,rst out myrctype); --创建存储过程声明，注：必须与程序包体中的存储过程头相同`
4.  `end pkg_HelloWorld;`

6.创建package body包体

1.  `create or replace package body pkg_HelloWorld as`
2.  `procedure getHelloWorld(param nvarchar2,rst out myrctype) --注：必须与包声明中相同`
3.  `as`
4.  `begin`
5.  `open rst for`
6.  `select * from test where AAA=param;`
7.  `end getHelloWorld;`
8.  `end pkg_HelloWorld;`

6.调用 使用包，返回table

1.  `var type_cur refcursor`
2.  `var para nvarchar2(20)`
3.  `execute :para :='1';`
4.  `exec PKG_HELLOWORLD.getHelloWorld(:para,:type_cur);`
5.  `print type_cur;`

[https://blog.csdn.net/cherish1forever/article/details/50068351][https_blog.csdn.net_cherish1forever_article_details_50068351]

# sys\_refcursor 和 cursor 优缺点比较 #

2016年12月13日 15:32:58 [ocean42234111][] 阅读数：2487 标签： [数据库应用][Link 3] 更多

个人分类： [ORACLE][]

本文转自

http://blog.sina.com.cn/s/blog\_638978a40100qjzc.html\#commonComment.

无奈无法评论，只好转到本人博客下添加评论

sys\_refcursor 和 cursor 优缺点比较

优点比较

优点一：sys\_refcursor，可以在存储过程中作为参数返回一个table格式的结构集（我把他认为是table类型，容易理解，其实是一个游标集）， cursor 只能用在存储过程，函数，包等的实现体中，不能做参数使用。

优点二：sys\_refcursor 这东西可以使用在包中做参数，进行数据库面向对象开放。哈哈。我喜欢。cursor 就不能。

缺点比较：

缺点：sys\_refcursor 不能用open，close ，fetch 进行操作。不好学，难理解。

cursor  可以用 open，close ，fetch  操作，容易学，易懂

其他就目前不知道，至于游标的的基础概念，去google，百度一大堆的。这里就不累赘了。看例子：

建立一个存储过程

create or replace procedure up\_test(o out sys\_refcursor) is  
begin  
   open o for select \* from lq\_test;  
end;

返回的类型是sys\_refcursor；

建立第二个存储过程

create or replace procedure up\_getData(aMsg out varchar2) is  
  type p\_table\_type is table of lq\_test%rowtype;  
  p\_table p\_table\_type;  
  v sys\_refcursor;  
begin  
     up\_test(v);  
     fetch v bulk collect into p\_table;  
     for i in 1..p\_table.count loop  
        dbms\_output.put\_line('字段1：'||p\_table(i).v1 || '   字段2：' || p\_table(i).v2);  
     end loop;  
end;

这里要注意fetch 带参数的用法，bulk collect ，这是第集合的操作，必须先定义一个结合类。见上面的例子，还不懂就google了。用法就简单，没啥好介绍的。

取集合的值应该这样p\_table(i).v1，其中i标识几行，带上字段，即可了。呵呵，容易理解。

评论:

调用方法:  
declare  l\_cc varchar2(20):='CC';  
begin  
    up\_getData(l\_cc);  
end;

[https://blog.csdn.net/ocean42234111/article/details/53610253][https_blog.csdn.net_ocean42234111_article_details_53610253]

[cherish1forever]: https://me.csdn.net/cherish1forever
[oracle]: http://so.csdn.net/so/search/s.do?q=oracle&t=blog
[Link 1]: http://so.csdn.net/so/search/s.do?q=%E5%AD%98%E5%82%A8%E8%BF%87%E7%A8%8B&t=blog
[sys_refcursor]: http://so.csdn.net/so/search/s.do?q=sys_refcursor&t=blog
[Link 2]: http://so.csdn.net/so/search/s.do?q=%E8%BF%94%E5%9B%9E%E7%BB%93%E6%9E%9C%E9%9B%86&t=blog
[table]: http://so.csdn.net/so/search/s.do?q=%E8%BF%94%E5%9B%9Etable&t=blog
[oracle 1]: https://blog.csdn.net/cherish1forever/article/category/1623483
[SQL]: https://blog.csdn.net/cherish1forever/article/category/5866535
[https_blog.csdn.net_cherish1forever_article_details_50068351]: https://blog.csdn.net/cherish1forever/article/details/50068351
[ocean42234111]: https://me.csdn.net/ocean42234111
[Link 3]: http://so.csdn.net/so/search/s.do?q=%E6%95%B0%E6%8D%AE%E5%BA%93%E5%BA%94%E7%94%A8&t=blog
[ORACLE]: https://blog.csdn.net/ocean42234111/article/category/6576828
[https_blog.csdn.net_ocean42234111_article_details_53610253]: https://blog.csdn.net/ocean42234111/article/details/53610253