我有一個遊標
CURSOR cur_sap IS
SELECT A.MATNR as MATNR ,A.WERKS as WERKS, A.LGORT as LGORT
FROM MARD A,MARA B
WHERE A.MANDT = p_mandt
AND A.WERKS IN (.....)
AND ....其中括號中要輸入的條件是下面這段SQL返回的KXK02
SELECT KXK02 FROM KXK_FILE
WHERE KXK01='...'
AND KXKACTI='...'請大家幫忙看看,我應該怎樣調用啊?
CURSOR cur_sap IS
SELECT A.MATNR as MATNR ,A.WERKS as WERKS, A.LGORT as LGORT
FROM MARD A,MARA B
WHERE A.MANDT = p_mandt
AND A.WERKS IN (.....)
AND ....其中括號中要輸入的條件是下面這段SQL返回的KXK02
SELECT KXK02 FROM KXK_FILE
WHERE KXK01='...'
AND KXKACTI='...'請大家幫忙看看,我應該怎樣調用啊?
is
type cur is ref cursor;
procedure getRst( rst out cur );
end aaa;
create or replace package body aaa
is
procedure getRst( rst out cur )
is
begin
open rst for SELECT A.MATNR as MATNR ,A.WERKS as WERKS, A.LGORT as LGORT
FROM MARD A,MARA B
WHERE A.MANDT = p_mandt
AND A.WERKS IN (.....);
end;
end aaa;
用字符串變量將sql語句串起來再賦給cursor
tmpSQL varchar2(1000);
plant_all varchar2(100);
TYPE CUR IS REF CURSOR;
cur1 CUR;CURSOR cur_plant IS
SELECT KXK02 FROM KXK_FILE
WHERE KXK01='6'
AND KXKACTI='Y'
AND KXK03=p_depart
ORDER BY KXK02 DESC;BEGIN plant_all:='';
FOR sr IN cur_plant LOOP
plant_all:=''''||sr.KXK02||''''||','||plant_all;
END LOOP;
plant_all:='('||SUBSTR(plant_all,1,LENGTH(plant_all)-1)||')'; tmpSQL:='SELECT A.MATNR as MATNR ,A.WERKS as WERKS, A.LGORT as LGORT, ';
tmpSQL:=tmpSQL||' A.PSTAT as PSTAT ,B.MEINS as MEINS ' ;
tmpSQL:=tmpSQL||' FROM MARD A,MARA B ';
tmpSQL:=tmpSQL||' WHERE A.MANDT = '''||p_mandt||''' ';
tmpSQL:=tmpSQL||' AND A.WERKS IN '||plant_all||' ';
tmpSQL:=tmpSQL||' AND A.MANDT = B.MANDT ';
...
OPEN cur1 FOR tmpSQL;
WHILE TRUE LOOP
FETCH cur1 INTO tmpMATNR,tmpLGORT,tmpWERKS,tmpPSTAT,tmpMEINS,tmpLABST;
EXIT WHEN cur1 %NOTFOUND;