SQL*Plus, 다중처리(Array Processing)

SQL*Plus, 다중처리(Array Processing)

#define ARRAY_SIZE 100

EXEC SQL BEGIN DECLARE SECTION;
	int	v_num1[ARRAY_SIZE];
	VARCHAR	v_fld1[ARRAY_SIZE][10];
     	VARCHAR	v_rowid[ARRAY_SIZE][20];
	short	i_rowid[ARRAY_SIZE];
	int	loop;
EXEC SQL END DECLARE SECTION;

EXEC SQL DECLARE C1 CURSOR FOR
	SELECT 
		a.num1
		, a.fld1
		, b.rowid
	FROM TABLE1 a, TABLE2 b
	WHERE a.fld1 = b.fld2(+)
	ORDER BY B.ROWID;

EXEC SQL OPEN c1;
indt = 1;

while (indt) {
	EXEC SQL FETCH c1 INTO :v_num1,:v_fld1,:v_rowid:i_rowid;

	if (sqlca.sqlcode == 1403) indt = 0;
	loop = sqlca.sqlerrd[2] - num_ret;	/* num_ret: number of rows returned */
	num_ret = sqlca.sqlerrd[2];	/* Reset the number. */
	
	/* rowid is null at the fist row fetched */
	if (i_rowid[0] == -1) {
		process_up_ins(0);	/* INSERT ONLY */
	} else {
		/* rowid is null at the last row fetched */
		if ( i_rowid[loop - 1] == -1 ) { 
			process_up_ins(1);      /* UPDATE AND INSERT BOTH */
		} else {
			process_up_ins(2); 	/* UPDATE ONLY */    
		}
	}
	EXEC SQL COMMIT WORK;
}
EXEC SQL CLOSE c1;
EXEC SQL COMMIT WORK RELEASE;

process_up_ins(proc_flag)
int	proc_flag;

{
	if (proc_flag == 2) {
		EXEC SQL FOR :loop UPDATE TABLE2 SET num2 = num2 + :v_num1
		WHERE ROWID = :v_rowid;
	} else if (proc_flag == 1) {
		EXEC SQL FOR :loop INSERT INTO TABLE2
		SELECT :v_fld1, :v_num1 FROM dual
		WHERE :v_rowid IS NULL;

		EXEC SQL FOR :loop UPDATE TABLE2 SET num2 = num2 + :v_num1
		WHERE :v_rowid IS NOT NULL
		AND ROWID = :v_rowid;
	} else {
		EXEC SQL FOR :loop INSERT INTO TABLE2
		VALUES ( :v_fld1, :v_num1 );
	}
}

원문 출처
Encore – 대용량 데이터베이스

Leave a Reply