甲骨文.在两个过程中将游标重新用作参数 [英] Oracle. Reuse cursor as parameter in two procedures
本文介绍了甲骨文.在两个过程中将游标重新用作参数的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
让我们创建两个测试过程:
Let's create two test procedures:
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
然后,我们尝试按顺序打开游标并将其传递给以下过程:
Then let's try to open cursor and pass it to these procedures in order:
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游标是正向和只读的.有什么方法可以帮助解决这一任务?
As you can see, I can't use already fetched cursor in second procedure, because ORACLE cursors are forward-and-read-only. What ways can help to solve this task?
我不能简单地将这些过程合而为一.需要保持逻辑分离.
I can't simply bring these procedures into one. Need to keep their logic separate from each other.
推荐答案
您需要两次打开光标.使用字符串变量保存查询将阻止您编写两次查询.
You need to open the cursor twice. Using a string variable to hold the query will prevent you from writing the query twice.
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;
/
这篇关于甲骨文.在两个过程中将游标重新用作参数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文