how to use bulk collect in db2 cursor

Question

Here is my procedure, I don't know how to use bulk collecton in cursor, that we can batch process the cursor data. Please help me, thanks!

CREATE PROCEDURE PROC_AUTOACTIVE
BEGIN ATOMIC
DECLARE v_sql VARCHAR(800);
DECLARE v_customer_id BIGINT;
DECLARE v_cardnum varchar(500);
DECLARE v_cardtype varchar(20);
DECLARE v_status varchar(10);
DECLARE v_lastname varchar(200);
DECLARE v_email varchar(150);
DECLARE v_mobile varchar(30);
DECLARE v_phone varchar(30);
DECLARE v_zipcode varchar(20);
DECLARE v_crm_mobile varchar(30);
DECLARE v_address varchar(500);
DECLARE v_order_count BIGINT;
DECLARE v_order_no varchar(500);
DECLARE not_found CONDITION FOR SQLSTATE '02000';
DECLARE at_end INT DEFAULT 0; 
DECLARE c_customers CURSOR  FOR s_cardsinfo;
DECLARE CONTINUE HANDLER FOR not_found SET at_end = 1;

SET v_sql = 'select t.customer_id, v.CUSTOMER_ID, v.CARD_TYPE, v.STATUS
               from customer_tempcard t,
                    vip_fields v 
               where t.tempcard_num=v.CUSTOMER_ID 
                 and t.status=1 
                 and v.STATUS=1 
                 and exists (select id 
                               from orders o 
                               where o.FK_CUSTOMER=t.CUSTOMER_ID
                                 and o.FK_ORDER_STATUS in (3,4,6)) ';
  PREPARE s_cardsinfo FROM v_sql;
  OPEN c_customers;
  --fetch card info
      LOOP_CUSTOMER_INFO:
      LOOP
        FETCH c_customers INTO v_customer_id,v_cardnum,v_cardtype,v_status;
        IF at_end <> 0 THEN
          SET at_end = 0;
          LEAVE LOOP_CUSTOMER_INFO;
        END IF;

          select c.LOGON_ID, o.DEV_CUSTOMER_NAME, 
                 o.DEV_MOBILE, o.DEV_PHONE, o.DEV_ZIP, o.DEV_ADDRESS, o.ORDER_NO
            into v_email, v_lastname,
                 v_mobile, v_phone, v_zipcode, v_address, v_order_no
            from orders o,customer c
            where o.FK_CUSTOMER=c.ID
              and o.FK_CUSTOMER=v_customer_id
              and o.FK_ORDER_STATUS in (3,4,6)
            order by o.ID desc
            fetch first 1 rows only;
          IF v_mobile <> null THEN
            SET v_crm_mobile = v_mobile;
          ELSE
            SET v_crm_mobile = v_phone;
          END IF;
          update customer_tempcard ct
            set ct.STATUS='0',
                ct.UPDATE_TIME=current_timestamp
              where ct.CUSTOMER_ID=v_customer_id;

          update card_store cs 
            set cs.STATUS='0',
                cs.UPDATE_TIME=current_timestamp
            where cs.CARD_NUM=v_cardnum;

          update vip_fields v
            set v.LAST_NAME=v_lastname,
                v.EMAIL=v_email, v.MOBILE=v_crm_mobile,
                v.CUSTOMER_UPDATE_TIME=current_timestamp,
                v.UPDATE_TIME=current_timestamp,
                v.OPERATION_TYPE='2',
                v.CREATE_SOURCE='2',
                v.STATUS='0',
                v.ZIP_CODE=v_zipcode,
                v.ADDRESS=v_address
            where customer_id = v_cardnum;

          update customer c
            set c.VIP_CARD_NUMBER=v_cardnum,
                c.VIP_CARD_NAME=v_lastname,
                c.VIP_EMAIL=v_email,
                c.VIP_CARD_TYPE=v_cardtype,
                c.LEVEL=v_cardtype,
                c.VIP_ZIP=v_zipcode,
                c.VIP_MOBILE=v_crm_mobile,
                c.VIP_ADDRESS=v_address,
                c.FK_CUSTOMER_GRADE='1' 
            where c.id=v_customer_id;

          insert into beactiveinfo
            values (default,v_cardnum,v_order_no,current_timestamp);
      END LOOP;
      CLOSE c_customers;
END

Answer 1

BULK COLLECT is part of the Oracle compatibility feature in DB2, so, firstly, you cannot use it in the DB2 SQL PL native context, which you are using in your procedure. Secondly, you don't use BULK COLLECT in a cursor. You use SELECT ... BULK COLLECT INTO an_array_variable ... to populate a PL/SQL array. If you intend then to loop over that array, you won't get any performance benefit over the cursor, while incurring the memory overhead for storing the entire result set in the application memory.