カーソルバンドパラメータとパラメータなし
CREATE OR REPLACE PROCEDURE x_ne_change
AS
CURSOR cur_new
IS
SELECT int_id, omc_id || ':' || msc_id || ':' || bsc_id AS related_id,
omc_id, msc_id, bsc_id, ne_name, cell_id, lac, freq_band,
trx_count, tch, sdcch, gprs_enabled, gprs_trx, dedicated_pdch,
max_pdch, device_type, software_version, dumpfre_type, site_no,
cell_no, rac, ncc, bcc, sms_cb_used, bsc_omc_int_id, omc_int_id,
TIMESTAMP
FROM appuser.K_C_CELL
WHERE TIMESTAMP = '2004-04-23 8' AND cell_id < 2000;
CURSOR cur_old (c_no NUMBER)
IS
SELECT int_id, omc_id || ':' || msc_id || ':' || bsc_id AS related_id,
omc_id, msc_id, bsc_id, ne_name, cell_id, lac, freq_band,
trx_count, tch, sdcch, gprs_enabled, gprs_trx, dedicated_pdch,
max_pdch, device_type, software_version, dumpfre_type, site_no,
cell_no, rac, ncc, bcc, sms_cb_used, bsc_omc_int_id, omc_int_id,
TIMESTAMP
FROM appuser.K_C_CELL
WHERE TIMESTAMP = '2004-04-21 6' AND cell_id = c_no;
BEGIN
FOR v_new IN cur_new
LOOP
FOR v_old IN cur_old (v_new.cell_id)
LOOP
BEGIN
IF v_new.related_id <>; v_old.related_id
THEN
INSERT INTO TEST_NE_CHANGE
(omc_id, omc_int_id, ne_id,
old_value, now_value,
modify_item, modify_time
)
VALUES (v_new.omc_id, v_new.omc_int_id, v_new.cell_id,
v_old.related_id, v_new.related_id,
'related_id', v_new.TIMESTAMP
);
COMMIT;
END IF;
IF v_new.tch <>; v_old.tch
THEN
INSERT INTO TEST_NE_CHANGE
(omc_id, omc_int_id, ne_id,
old_value, now_value, modify_item, modify_time
)
VALUES (v_new.omc_id, v_new.omc_int_id, v_new.cell_id,
v_old.tch, v_new.tch, 'TCH', v_new.TIMESTAMP
);
COMMIT;
END IF;
END;
END LOOP;
END LOOP;
END;
CREATE OR REPLACE PROCEDURE w_ne_change
AS
CURSOR cur_new
IS
SELECT int_id, omc_id || ':' || msc_id || ':' || bsc_id AS related_id,
omc_id, msc_id, bsc_id, ne_name, cell_id, lac, freq_band,
trx_count, tch, sdcch, gprs_enabled, gprs_trx, dedicated_pdch,
max_pdch, device_type, software_version, dumpfre_type, site_no,
cell_no, rac, ncc, bcc, sms_cb_used, bsc_omc_int_id, omc_int_id,
TIMESTAMP
FROM appuser.W_C_CELL
WHERE TIMESTAMP = '2004-04-23 8' AND cell_id < 200;
CURSOR cur_old
IS
SELECT int_id, omc_id || ':' || msc_id || ':' || bsc_id AS related_id,
omc_id, msc_id, bsc_id, ne_name, cell_id, lac, freq_band,
trx_count, tch, sdcch, gprs_enabled, gprs_trx, dedicated_pdch,
max_pdch, device_type, software_version, dumpfre_type, site_no,
cell_no, rac, ncc, bcc, sms_cb_used, bsc_omc_int_id, omc_int_id,
TIMESTAMP
FROM appuser.W_C_CELL
WHERE TIMESTAMP = '2004-04-23 6' AND cell_id < 200;
BEGIN
FOR v_new IN cur_new
LOOP
FOR v_old IN cur_old
LOOP
IF v_old.cell_id = v_new.cell_id
THEN
BEGIN
IF v_new.related_id <>; v_old.related_id
THEN
INSERT INTO TEST_NE_CHANGE
(omc_id, omc_int_id,
ne_id, old_value,
now_value, modify_item,
modify_time
)
VALUES (v_new.omc_id, v_new.omc_int_id,
v_new.cell_id, v_old.related_id,
v_new.related_id, 'related_id',
v_new.TIMESTAMP
);
COMMIT;
END IF;
END;
END IF;
END LOOP;
END LOOP;
END;
効率の面では