◀ Previous Next ▶ Up ▲


witpoko.com


FETCH CURSOR vs. FOR LOOP CURSOR ต่างกันตรงไหนครับ

รบกวน ชี้แนะหน่อยครับ
โดยปกติเวลาผมเขียนจะใช้
DECLARE
CURSOR MAIN_CUR IS
SELECT
ban,
quote_soln_id,
asset_num,
update_name_flag
FROM T_ORDER_DISCONNECT
WHERE substr(asset_num,13,1)= 'V'
AND order_type = 'Disconnect'
AND order_sts_code = 'X';
vBAN t_order_disconnect.ban%TYPE;
vQUOTE_SOLN_ID t_order_disconnect.quote_soln_id%TYPE;
vASSET_NUM t_order_disconnect.asset_num%TYPE;
vFLAG t_order_disconnect.update_name_flag%TYPE;

CURSOR NAME_V_CUR IS
select spi.name
from s_quote_item sqi,nbsrps.s_prod_int spi
where sqi.quote_soln_id = vQUOTE_SOLN_ID
and sqi.stat_cd in ('Active','Inactive')
and sqi.prod_id = spi.row_id
and substr(spi.name,2,1) between '0' and '9';

BEGIN
OPEN MAIN_CUR;
LOOP
FETCH MAIN_CUR INTO vBAN,vQUOTE_SOLN_ID,vASSET_NUM,vFLAG
IF MAIN_CUR%FOUND THEN
OPEN NAME_V_CUR;
FETCH NAME_V_CUR INTO vNAME;
IF NAME_V_CUR%FOUND THEN
IF vFLAG = 'N' then
UPDATE T_ORDER_DISCONNECT
SET ELEMENT_NAME = 'ABCD',
UPDATE_NAME_FLAG = 'Y'
WHERE BAN = vBAN
AND QUOTE_SOLN_ID = vQUOTE_SOLN_ID
AND ASSET_NUM = vASSET_NUM;
END IF;
END IF;
CLOSE NAME_V_CUR;
ELSE
EXIT;
END IF;
END LOOP;
CLOSE MAIN_CUR;
END;


จากคุณ : Dio -[ 31 ม.ค. 50 - 13:53:38 A:172.20.11.120 X:203.144.130.176 ]





กระทู้ยอดนิยม