DROP PROCEDURE
IF EXISTS PROC_NEW_CUS_BIND_PACKAGE;

CREATE PROCEDURE PROC_NEW_CUS_BIND_PACKAGE ()
BEGIN
	-- ???sql??????f 1???
  DECLARE t_error       INTEGER DEFAULT 0;
	-- ?????\?????????? 0???_???? 	 
  DECLARE FETCHSEQOK    INT DEFAULT 0;
  -- ????????
  DECLARE fMoney        VARCHAR(20);
  -- ?????
  DECLARE fCusCode      VARCHAR(50);
  -- ???????????????
  DECLARE cusAttrDate1  VARCHAR(50);
  -- ?????????4?f
  DECLARE phone     VARCHAR(20);
  -- ?????????
  DECLARE mainID        VARCHAR(50);
  -- 4?f?????
  DECLARE randNum       VARCHAR(50);
  -- ????
  DECLARE cardCode      VARCHAR(50);


  -- ??sql??????f???1  
	DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=1;
	--  ???????\?????????not fount ?????? ????????\?
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET FETCHSEQOK = 1;
  START TRANSACTION;

  BEGIN
    -- ??????????????????
    DECLARE CUS2 CURSOR FOR
      -- SELECT F_CUS_CODE, CUS_ATTR_DATE_1, f_phone, F_CUS_CARD_REL FROM T_CUS_INFO A
      --  LEFT JOIN T_CUS_CARD B ON A.F_CUS_CODE = B.F_CUS_CODE_REL AND B.F_CUS_CARD_TYPE = '0';
      SELECT F_CUS_CODE, f_phone FROM T_CUS_INFO A
        where not exists (select 1 from t_cus_package_info B where A.F_CUS_CODE = B.F_CUS_CODE);

    -- ????????????????????????????????????????
    OPEN CUS2;
    FETCH CUS2 INTO fCusCode, phone;
		REPEAT
				IF(FETCHSEQOK != 1) THEN

-- insert into t_test (col1) values(1);
 -- insert into t_test values (cusAttrDate1, fCusCode, phoneFour);
           -- ?????????????????
            SELECT CONCAT('282',DATE_FORMAT(NOW(), '%Y%m%d%h%i%s'), phone) INTO mainID;

           -- ????4?f?????????????????
            SELECT CONCAT(mainID, ROUND(ROUND(rand(),4)*10000)) INTO randNum;

           -- ????????????????????????????u??????
-- insert into t_test values(mainID, fCusCode, randNum, cardCode);
            INSERT INTO T_CUS_PACKAGE_INFO (F_MAIN_ID, F_PACKAGE_CODE, F_PACKAGE_NAME, F_PACKAGE_TYPE, F_USE_TYPE, F_CUS_CODE,  
             F_PACKAGE_RESOURCE, F_STATE, F_TIME, F_ORG_ID, F_SCAN_CODE, F_CARD_CODE, F_EFFECT_TIME) 
             VALUES (mainID, '1001201704100000', '???????????', '1', '8', fCusCode, '3', '1', DATE_FORMAT(NOW(), '%Y%m%d%h%i%s'), 'KZ', randNum, '', '');
        END IF;
    FETCH CUS2 INTO fCusCode, phone;
				UNTIL FETCHSEQOK = 1
		END REPEAT;
		CLOSE CUS2;
    SET FETCHSEQOK = 0;
-- select t_error;
    -- ??????
    IF t_error = 1 THEN
              ROLLBACK; 
             -- COMMIT;
          ELSE
             COMMIT;  
    END IF;
  END;
END;