在 sys_refcursor 中使用 oracle 游标
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1095183/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
use oracle cursor within a sys_refcursor
提问by Lloyd
I've got a PL/SQL package that returns a sys_refcursor based on the id that you pass it. I'd like to iterate through some ids and create a new ref cursor with one column from the original result set repeated for each id. (Sort of a cross tab.) A very simplified version of the PL/SQL block looks like:
我有一个 PL/SQL 包，它根据您传递的 id 返回一个 sys_refcursor。我想遍历一些 id 并创建一个新的 ref 游标，其中每个 id 重复原始结果集中的一列。（有点像交叉表。）PL/SQL 块的一个非常简化的版本如下所示：
create or replace package body dashboard_package is
procedure visits(RC in out sys_refcursor, IdNumber varchar2) as
BEGIN
OPEN RC FOR
select *
from (
select cat, cat_order, subcat, label_text
, trim(to_char(sum(v.current_month),'9,999,999,999')) current_month
, trim(to_char(sum(v.ly_month),'9,999,999,999')) ly_month
, trim(to_char(sum(v.ytd_tot),'9,999,999,999')) ytd_tot
, trim(to_char(sum(v.lytd_tot),'9,999,999,999')) lytd_tot
, trim(to_char(sum(v.ly_tot),'9,999,999,999')) ly_tot
from dashboard v
where v.id_number = IdNumber
group by cat_order, subcat, cat, label_text
union all
...
) a
order by cat_order, subcat;
END;
END;
I think if I had something like this
我想如果我有这样的事情
create or replace procedure test_refcur is
refCursorValue SYS_REFCURSOR;
begin
dashboard_package.visits(refCursorValue,12345);
for cursrow in refCursorValue loop
dbms_output.put_line(cursrow.ytd_tot);
end loop;
end test_refcur;
working, I could take it from there... any thoughts? Or perhaps clarification on the question that I should be asking.
工作，我可以从那里拿走它......有什么想法吗？或者也许澄清我应该问的问题。
回答by Jeffrey Kemp
If you're coming in with a number of IDs, then first prize would be to run only one SQL query to fetch the lot in one go, using a bulk in-bind for the IDs. This would probably require a modification to dashboard_package.visits, or writing a new version of the visitsprocedure to accept a PL/SQL table of IDs instead of a single ID.
如果您使用多个 ID，那么一等奖将是仅运行一个 SQL 查询以一次性获取批次，并使用 ID 的批量绑定。这可能需要修改dashboard_package.visits或编写新版本的visits过程以接受 PL/SQL 表的 ID 而不是单个 ID。
If your hands are tied WRT modifying dashboard_package, then you could write a pipelined function to return the rows for a set of IDs:
如果您的手被绑定到 WRT 修改dashboard_package，那么您可以编写一个流水线函数来返回一组 ID 的行：
-- create some helper types for the pipelined function
create type visitobj as object
(id number
,cat dashboard.cat%type
,cat_order dashboard.cat_order%type
,subcat dashboard.subcat%type
,label_text dashboard.label_text%type
,current_month varchar2(13)
,ly_month varchar2(13)
,ytd_tot varchar2(13)
,lytd_tot varchar2(13)
,ly_tot varchar2(13));
create type visittable as table of visitobj;
create or replace function test_refcur
return visittable deterministic pipelined is
refCursorValue SYS_REFCURSOR;
cat dashboard.cat%type;
cat_order dashboard.cat_order%type;
subcat dashboard.subcat%type;
label_text dashboard.label_text%type;
current_month varchar2(13);
ly_month varchar2(13);
ytd_tot varchar2(13);
lytd_tot varchar2(13);
ly_tot varchar2(13);
begin
for id in (/*iterate through the IDs*/) loop
dashboard_package.visits(refCursorValue, id);
loop
fetch refCursorValue into cat, cat_order, subcat, label_text,
current_month, ly_month, ytd_tot,
lytd_tot, ly_tot;
exit when refCursorValue%NOTFOUND;
pipe row (visitobj (id, cat, cat_order, subcat, label_text,
current_month, ly_month, ytd_tot,
lytd_tot, ly_tot));
end loop;
end loop;
return;
end test_refcur;
-- now you can simply do this:
SELECT * FROM TABLE(test_refcur);
(Of course, "/*iterate through the IDs*/" would be whatever method you want to use to gather the IDs for which the function should be called - e.g. could be a PL/SQL table of IDs, or perhaps another query).
（当然，“ /*iterate through the IDs*/” 可以是您想用来收集应为其调用函数的 ID 的任何方法——例如，可以是 PL/SQL 的 ID 表，或者可能是另一个查询）。
Again I'd stress that "first prize" is to not do any of this extra work at all - just have a dashboard_package.visitsthat does it all in one SQL.
我再次强调，“一等奖”是根本不做任何这些额外的工作——只需dashboard_package.visits在一个 SQL 中完成所有工作即可。
On a side note, trim(to_char(sum(v.ly_tot),'9,999,999,999'))can be simplified to to_char(sum(v.ly_tot),'FM9,999,999,999'). Also, if you use the format 'FM9G999G999G999'instead, it will be non-locale-specific.
顺便说一句，trim(to_char(sum(v.ly_tot),'9,999,999,999'))可以简化为to_char(sum(v.ly_tot),'FM9,999,999,999'). 此外，如果您改用该格式'FM9G999G999G999'，它将是非语言环境特定的。
