Friday, August 16, 2019

bulk collect delete and insert


DECLARE
   CURSOR c
   IS
      SELECT   xps.ROWID ROW_ID
             , xps.PHX_ORDER_NUMBER
             , xps.PHX_SHIPPING_GROUP_ID
             , xps.ITEM_ID
             , xps.NCMS_ORDER_NUMBER
             , xps.STATUS_FLAG
             , xps.TRACKING_NUMBER
             , xps.PROCESSED_FLAG
        FROM   xxont_phx_status_t xps, oe_order_headers_all ooh
       WHERE   1 = 1
         AND xps.ncms_order_number = ooh.order_number
         AND ooh.creation_Date >= TO_DATE ('26-JUL-2019 09:00:00', 'DD-MON-YYYY, HH24:MI:SS');

   TYPE phx_array_type IS TABLE OF c%ROWTYPE;

   phx_array   phx_array_type;
BEGIN
   OPEN c;

   FETCH c BULK COLLECT INTO   phx_array;

   CLOSE c;

   -- Delete

   FORALL x IN phx_array.FIRST .. phx_array.LAST
      DELETE FROM   xxont_phx_status_t pst
            WHERE   pst.ROWID = phx_array (x).ROW_ID;

   -- Insert into base table
   FORALL x IN phx_array.FIRST .. phx_array.LAST
      INSERT INTO xxont_phx_status_t (PHX_ORDER_NUMBER
                                    , PHX_SHIPPING_GROUP_ID
                                    , ITEM_ID
                                    , NCMS_ORDER_NUMBER
                                    , STATUS_FLAG
                                    , TRACKING_NUMBER
                                    , PROCESSED_FLAG)
        VALUES   (phx_array (x).PHX_ORDER_NUMBER
                , phx_array (x).PHX_SHIPPING_GROUP_ID
                , phx_array (x).ITEM_ID
                , phx_array (x).NCMS_ORDER_NUMBER
                , phx_array (x).STATUS_FLAG
                , phx_array (x).TRACKING_NUMBER
                , phx_array (x).PROCESSED_FLAG);

   COMMIT;
END;
/

1 comment: