动态游标使用笔记

-- START ---

	-- 动态游标声明 --
	TYPE MY_TYPE_CURSOR IS REF CURSOR;
	CUR_SQL        MY_TYPE_CURSOR;
	STR_SQL_CURSOR VARCHAR2(4000);

	-- 数组声明 用于存放游标中的ROWID字段 --
	TYPE MY_TYPE_TABLE_ROWID IS TABLE OF ROWID;
	TABLE_ROWID MY_TYPE_TABLE_ROWID;

	-- 数组声明 用于存放游标中的"姓名"字段 --
	TYPE MY_TYPE_NAME IS TABLE OF VARCHAR2(200);
	TABLE_NAME     MY_TYPE_NAME;
	TABLE_NEW_NAME MY_TYPE_NAME;

	-- 本地变量声明 --
	STR_FULL_WIDTH VARCHAR2(200);
	STR_HALF_WITHD VARCHAR2(200);
	NUM_LIMIT_ROWS NUMBER;
	STR_SQL_UPDATE VARCHAR2(4000);

BEGIN

	STR_FULL_WIDTH := F_GET_HF(0);
	STR_HALF_WITHD := F_GET_HF(1);
	NUM_LIMIT_ROWS := 1000;

	STR_SQL_CURSOR := 'SELECT ' || COLNAME || ',' || 'f_trans_name(' ||
					  COLNAME || ',''' || STR_FULL_WIDTH || ''',''' ||
					  replace(STR_HALF_WITHD,'''','''''') || '''),ROWID RID FROM ' || OWNER || '.' ||
					  TABNAME;

	STR_SQL_UPDATE := 'UPDATE ' || OWNER || '.' || TABNAME || ' SET ' ||
					  COLNAME || ' = :1,' || COLSTATS ||
					  ' = decode(length(:2),2,' || COLSTATS || ',3,' ||
					  COLSTATS || ',4,' || COLSTATS || ',''28'')' ||
					  ' WHERE ROWID = :3';

	OPEN CUR_SQL FOR STR_SQL_CURSOR;
	LOOP
		FETCH CUR_SQL BULK COLLECT
			INTO TABLE_NAME, TABLE_NEW_NAME, TABLE_ROWID LIMIT NUM_LIMIT_ROWS;
		FORALL I IN 1 .. TABLE_NAME.COUNT EXECUTE IMMEDIATE STR_SQL_UPDATE
						 USING TABLE_NEW_NAME(I), TABLE_NEW_NAME(I),
						 TABLE_ROWID(I)
			;
		COMMIT;
		EXIT WHEN CUR_SQL%NOTFOUND;
	END LOOP;

	CLOSE CUR_SQL;

-- END --