How to Return Result-Set via PLSQL Table or REF Cursor
如何通过PLSQL 表 或者 REF Cursor 返回结果集
create or replace package test_pack is
type mycur is ref cursor;
type myrectype is record (loc varchar2(100));
type mytabtype is table of myrectype index by binary_integer;
end;
/
/* Procedure to Return resultset using REF Cursor */
create or replace procedure return_many_rows_ref(rset in out test_pack.mycur)
is
begin
open rset for select * from dept;
end;
/
/* Procedure to Return resultset using PL/SQL table of Records */
create or replace procedure return_many_rows_tabrec(rset in out test_pack.mytabtype)
is
cursor dept_cur is select loc from dept;
ctr number := 0;
begin
for x in dept_cur loop
rset(ctr).loc := x.loc;
ctr := ctr + 1;
end loop;
end;
/
/* PL/SQL block to test procedure that returns many rows using REF cursor */
set serveroutput on;
declare
result1 test_pack.mycur;
dep_rec dept%rowtype;
begin
return_many_rows_ref(result1);
loop
fetch result1 into dept_rec;
exit when result1%notfound;
dbms_output.put_line(dep_rec.deptno);
end loop;
close result1;
end;
/
/* PL/SQL block to test procedure that returns many rows using PL/SQL table of records*/
declare
result2 test_pack.mytabtype;
begin
return_many_rows_tabrec(result2);
for x in result2.first..result2.last loop
dbms_output.put_line(result2(x).loc);
end loop;
end;
/
分享到:
相关推荐
how-to-write-injection-proof-plsql
introduction to -sql-plsql1
plsql-web.rar---plsql-web.rar--plsql-web.rar
51CTO下载-Oracle+PLSQL开发基础
数据库-简单-plsql.ppt该文档详细且完整,值得借鉴下载使用,欢迎下载使用,有问题可以第一时间联系作者~
大型数据库技术-实验四 PLSQL编程
win7-64位-plsql连接oracle64插件及教程
下面简单介绍下PLSQL developer工具的SVN版本控制 1, 下载pl/sql-svn插件。 2, 关闭你打开的所有pl/sql窗口。 3,将插件里的SVN_Plugin.dll文件复制到PLSQL DeveloperPlugIns目录下。 4,重新打开pl/sql,...
KingbaseESV8R6产品手册-SQL和PLSQL
oracle笔记二--plsql 编程oracle笔记二--plsql 编程oracle笔记二--plsql 编程oracle笔记二--plsql 编程
plsql-day1 plsql-day1 plsql-day1 plsql-day1 plsql-day1 plsql-day1 plsql-day1
visual foxpro 数据库驱动,dbf数据库驱动
introduction to oracle - sql plsql (vol2).pdf
JSON-PLSQL JSON-PLSQL JSON-PLSQL JSON-PLSQL
not support HTML, MS DOC, MY Oracle PLSQL development journal
64位windows系统上安装了Oracle 11g r2后,安装PLSQL Developer客户端出现如下图情况,连接数据库时,无法选择用户角色,强制输入用户信息及连接信息后会弹出错误信息,根据信息可知PLSQL Developer并不支持64位系统...