游标带参数与不带参数

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;

效率方面，你懂的