Home » SQL & PL/SQL » SQL & PL/SQL » CAN I use two cursors like show below
CAN I use two cursors like show below [message #329022] Mon, 23 June 2008 14:06 Go to next message
victory_nag
Messages: 36
Registered: June 2008
Location: CA
Member
declare
CURSOR C1 IS 
	SELECT TRACK_NUM, MIN(SUB_ACCOUNT) AS START_KEY, MAX(SUB_ACCOUNT) AS END_KEY
	FROM (
              SELECT TO_NUMBER(ACCOUNT_NUMBER) AS S_ACCOUNT,
              RANK() OVER (PARTITION BY ACCOUNT_NUMBER
              ORDER BY ROWNUM) AS ROW_INSTANCE,
              NTILE(  20  ) OVER (ORDER BY ACCOUNT_NUMBER) AS TRACK_NUM
              FROM  FSTORE.FS_HO_ADJUSTMENT@FDM_NGPROD)
	 WHERE ROW_INSTANCE = 1
         GROUP BY TRACK_NUM ;
CURSOR C2 IS 
	SELECT TRACK_NUM, MIN(SUB_ACCOUNT) AS START_KEY, MAX(SUB_ACCOUNT) AS END_KEY
	FROM (
              SELECT TO_NUMBER(PH_ACCOUNT) AS S_ACCOUNT,
              RANK() OVER (PARTITION BY PH_ACCOUNT
              ORDER BY ROWNUM) AS ROW_INSTANCE,
              NTILE(  20  ) OVER (ORDER BY PH_ACCOUNT) AS TRACK_NUM
              FROM  FSTORE.PUB_PHN_MASTER_MONTH_HISTORY@FDM_NGPROD)
	 WHERE ROW_INSTANCE = 1
         GROUP BY TRACK_NUM ;

C1_TRK_NUM NUMBER;
C1_STR_KEY NUMBER;
C1_END_KEY NUMBER;

C2_TRK_NUM NUMBER;
C2_STR_KEY NUMBER;
C2_END_KEY NUMBER;

begin
OPEN C1;
LOOP
	FETCH C1 INTO C1_TRK_NUM,
		      C1_STR_KEY,
		      C1_END_KEY ;
	EXIT WHEN C1%NOTFOUND;

	STR_TIME_STG_HO_ADJ := DBMS_UTILITY.GET_TIME;
	INSERT INTO /*+ PARALLEL (A,8) APPEND */ STG_HO_ADJUSTMENT
		SELECT * FROM FSTORE.FS_HO_ADJUSTMENT@FDM_NGPROD  
		WHERE MONTH_ID = DT_STG_ADJ
		AND TO_NUMBER(ACCOUNT_NUMBER) BETWEEN C1_STR_KEY AND C1_END_KEY;
	COMMIT;

	INSERT INTO /*+ PARALLEL (A,8) APPEND */ STG_HO_ADJUSTMENT
		SELECT * FROM FSTORE.FS_HO_ADJUSTMENT@FDM_NGPROD  
		WHERE MONTH_ID = DATE_INST
		AND TO_NUMBER(ACCOUNT_NUMBER) BETWEEN C1_STR_KEY AND C1_END_KEY;
	COMMIT;
	END_TIME_STG_HO_ADJ := DBMS_UTILITY.GET_TIME;
	DBMS_OUTPUT.PUT_LINE (END_TIME_STG_HO_ADJ - STR_TIME_STG_HO_ADJ||' '|| 'SEC');
END LOOP;
CLOSE C1;

OPEN C2;
LOOP
	FETCH C2 INTO C2_TRK_NUM,
		      C2_STR_KEY,
		      C2_END_KEY ;
	EXIT WHEN C2%NOTFOUND ;
	INSERT INTO /*+ PARALLEL (A,8) APPEND */ STG_PHN_MASTER_MONTH_HISTORY
		SELECT * FROM FSTORE.PUB_PHN_MASTER_MONTH_HISTORY@FDM_NGPROD 
		WHERE MONTH_ID = DATE_INST 
		AND TO_NUMBER(PH_ACCOUNT) BETWEEN C2_STR_KEY AND C2_END_KEY;
	COMMIT;
ENDLOOP;
CLOSE C2;

end;
Re: CAN I use two cursors like show below [message #329030 is a reply to message #329022] Mon, 23 June 2008 14:44 Go to previous message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
CAN I use two cursors like show below

./fa/3314/0/ Well, can you? Did you try, at least? What happened?
Previous Topic: DBMS_JOB Scheduler Issue
Next Topic: How to pass the value to four different columns in a view
Goto Forum:
  


Current Time: Sat Dec 03 18:14:05 CST 2016

Total time taken to generate the page: 0.07801 seconds