gpt4 book ai didi

sql - 在存储过程中声明 SYS_REFCURSOR 和 ROWTYPE

转载 作者:行者123 更新时间:2023-12-02 07:29:21 25 4
gpt4 key购买 nike

所以我有一个存储过程ONE

create or replace PROCEDURE ONE
(
A in number
B in number
...
ZZ out SYS_REFCURSOR
) IS
SOME_OTHER_STUFF
BEGIN
...
END

这会返回一个 sys_refcursor 以及我需要的一些数据。然后我有一个存储过程两个

create or replace PROCEDURE TWO
(
A in number
B in number
...
ZZ out SYS_REFCURSOR
) IS
Count_Records Sys_Refcursor;
l_rec Count_Records%rowtype;
BEGIN
/* get some data from the ONE stored procedure to use on this procedure*/
ONE(A,B,...Count_Records)

Loop
fetch count_records into l_rec;
Exit When count_records%Notfound;
If l_rec.TT_RAW > MAX_RECORDS Then
Raise To_Many_Results;
End If;
End Loop;

END

当我尝试声明行类型时出现错误 l_rec Count_Records%rowtype;

如何声明这个变量?问题是 TT_RAW 是我试图获取的列,它是一个 SUM,并且游标未映射到任何包含大量计算的表。

谢谢

最佳答案

sys_refcursor 的全部意义在于它是动态定义的。另一方面,rowtype 声明必须是静态的(如果不是,编译器无法判断从中引用的字段是否有效)。这是一个用例的定义,您应该定义自己的强类型引用游标,而不是使用 sys_refcursor

下面是一个非常简单的例子:

CREATE OR REPLACE PACKAGE BODY rc_example IS
PROCEDURE two (a IN NUMBER, b IN NUMBER);
END rc_example;
/

CREATE OR REPLACE PACKAGE BODY rc_example IS
TYPE one_record IS RECORD (FIRST_VALUE VARCHAR2 (10));

TYPE one_cursor IS REF CURSOR
RETURN one_record;

--Could alternately be declared using a table's rowtype:
--TYPE one_cursor is ref cursor return some_table%rowtype;

PROCEDURE one (a IN NUMBER, b IN NUMBER, zz OUT one_cursor) IS
BEGIN
OPEN zz FOR SELECT 'test' FROM DUAL;
END one;

PROCEDURE two (a IN NUMBER, b IN NUMBER) IS
count_records SYS_REFCURSOR;
l_rec count_records%ROWTYPE;
BEGIN
one (a, b, count_records);

LOOP
FETCH count_records INTO l_rec;

DBMS_OUTPUT.put_line (l_rec.FIRST_VALUE);
EXIT WHEN count_records%NOTFOUND;
END LOOP;
END two;
END rc_example;
/

关于sql - 在存储过程中声明 SYS_REFCURSOR 和 ROWTYPE,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/23762522/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com