gpt4 book ai didi

神谕。在两个过程中重用游标作为参数

转载 作者:行者123 更新时间:2023-12-02 01:10:36 26 4
gpt4 key购买 nike

让我们创建两个测试程序:

CREATE OR REPLACE PROCEDURE Aaaa_Test1(
pDog SYS_REFCURSOR
) IS
TYPE tDogRec is record (objid varchar2(7), lim number, debt number);
TYPE tDog IS TABLE OF tDogRec;
vDog tDog;
BEGIN
IF pDog%ISOPEN THEN
FETCH pDog BULK COLLECT INTO vDog;

IF vDog.count >= 1 THEN
FOR i IN vDog.First..vDog.Last LOOP
Dbms_Output.Put_Line('Aaaa_Test1 = '||vDog(i).Objid);
END LOOP;
END IF;

END IF;
END; -- Aaaa_Test1 Procedure
/
CREATE OR REPLACE PROCEDURE Aaaa_Test2(
pDog SYS_REFCURSOR
) IS
TYPE tDogRec is record (objid varchar2(7), lim number, debt number);
TYPE tDog IS TABLE OF tDogRec;
vDog tDog;
BEGIN
IF pDog%ISOPEN THEN
FETCH pDog BULK COLLECT INTO vDog;

IF vDog.count >= 1 THEN
FOR i IN vDog.First..vDog.Last LOOP
Dbms_Output.Put_Line('Aaaa_Test2 = '||vDog(i).Objid);
END LOOP;
END IF;

END IF;
END; -- Aaaa_Test2 Procedure

然后让我们尝试打开游标并将其按顺序传递给这些过程:

DECLARE
Vcdogcur SYS_REFCURSOR;
BEGIN
OPEN Vcdogcur FOR
select '6518535' objid, 10000 lim,0 debt
from dual
union all
select '6518536', 0,500
from dual
union all
select '5656058', 0,899
from dual
union all
select '2180965', 5000,0
from dual
union all
select '2462902', 0,100
from dual;

Aaaa_Test1(Vcdogcur);
Aaaa_Test2(Vcdogcur);
CLOSE Vcdogcur;
END;

如您所见,我无法在第二个过程中使用已提取的游标,因为 ORACLE 游标是只进式和只读的。有什么方法可以帮助解决这个任务?

我不能简单地将这些程序合二为一。需要将它们的逻辑彼此分开。

最佳答案

您需要打开游标两次。使用字符串变量来保存查询将防止您编写查询两次。

DECLARE
Vcdogcur SYS_REFCURSOR;
dyn_query varchar2(500);
BEGIN
dyn_query := 'select ''6518535'' objid, 10000 lim,0 debt
from dual
union all
select ''6518536'', 0,500
from dual
union all
select ''5656058'', 0,899
from dual
union all
select ''2180965'', 5000,0
from dual
union all
select ''2462902'', 0,100
from dual' ;


open Vcdogcur for dyn_query ;
Aaaa_Test1(Vcdogcur);
CLOSE Vcdogcur;
open Vcdogcur for dyn_query ;
Aaaa_Test2(Vcdogcur);
close Vcdogcur;
END;
/

关于神谕。在两个过程中重用游标作为参数,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/17919771/

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