论坛徽章:: 0

电梯直达

1楼 [收藏(0)] [报告]

发表于 2004-04-23 17:40 |只看该作者 |倒序浏览

比较表中 cell_id相同的记录在两个不同时间段发生的更新，如某项发生变化则记录到另一个表 test_ne_change 中
开始写的，效率极低

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;

复制代码

效率太慢了，如果有一万条数据比较的话就超慢。高手们帮我看看吧

文库|博客

voi-jia

白手起家

论坛徽章:: 0

2楼 [报告]

发表于 2004-04-23 18:40 |只看该作者

【讨论】带参数的游标的写法

如此改了一下，效率高了不少：）

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;
/

复制代码

实战分享：从技术角度谈机器学习入门| 【大话IT】RadonDB低门槛向MySQL集群下战书 | ChinaUnix打赏功能已上线！ | 新一代分布式关系型数据库RadonDB知多少？

返回列表

Chinaunix › 论坛 › 数据库技术 › Oracle › 【讨论】带参数的游标的写法

【讨论】带参数的游标的写法 [复制链接]

【讨论】带参数的游标的写法

浏览过的版块