记录一次存储过程的使用（2）

/*存储过程start--插入选项的code*/
DELIMITER //
DROP PROCEDURE
IF EXISTS INSERT_OPTION_VALUE//

CREATE PROCEDURE INSERT_OPTION_VALUE ()
BEGIN
  DECLARE done  INT DEFAULT 0;/*游标循环标志*/
  DECLARE v_field_id varchar(20) ;
  DECLARE v_field_column varchar(60) ;
  DECLARE v_business_type varchar(20) ;

    DECLARE fields CURSOR FOR
        select cf.id, cf.field_column, cf.business_type from t_custom_field cf where cf.field_input_type = 8 and cf.field_column is not null and cf.field_group in (1, 2);

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1 ;

    OPEN fields ;
    fieldloop : LOOP
    FETCH fields INTO v_field_id, v_field_column, v_business_type;
      IF done = 1 THEN
        LEAVE fieldloop ;
      END IF ;
          BEGIN
                IF v_field_column is not null and v_field_column <> ‘‘
                    and v_business_type is not null and v_business_type <> ‘‘
                    and v_field_id is not null and v_field_id <> ‘‘ THEN
                    CASE WHEN v_business_type = ‘01‘ THEN
                            SET @v_sql=‘INSERT INTO `t_option_value_rel_resource` (`id`, `field_id`, `data_id`, `business_type`, `code`)(‘;
                            SET @v_sql= CONCAT(@v_sql,‘select UUID_SHORT(), ‘,v_field_id,‘, c.ID, \‘01\‘, c.‘,v_field_column,‘ from t_customer_potential c where c.validity = 1 and c.‘,v_field_column,‘ is not null);‘);
                            /*预编译*/
                            PREPARE stmt FROM @v_sql;
                            EXECUTE stmt;
                            DEALLOCATE PREPARE stmt;
                        WHEN v_business_type = ‘02‘ THEN
                            SET @v_sql=‘INSERT INTO `t_option_value_rel_resource` (`id`, `field_id`, `data_id`, `business_type`, `code`)(‘;
                            SET @v_sql= CONCAT(@v_sql,‘select UUID_SHORT(), ‘,v_field_id,‘, c.ID, \‘02\‘, c.‘,v_field_column,‘ from t_customer c where c.validity = 1 and c.‘,v_field_column,‘ is not null);‘);
                            PREPARE stmt FROM @v_sql;
                            EXECUTE stmt;
                            DEALLOCATE PREPARE stmt;
                        WHEN v_business_type = ‘03‘ THEN
                            SET @v_sql=‘INSERT INTO `t_option_value_rel_resource` (`id`, `field_id`, `data_id`, `business_type`, `code`)(‘;
                            SET @v_sql= CONCAT(@v_sql,‘select UUID_SHORT(), ‘,v_field_id,‘, c.ID, \‘03\‘, c.‘,v_field_column,‘ from t_linkman c where c.validity = 1 and c.‘,v_field_column,‘ is not null);‘);
                            PREPARE stmt FROM @v_sql;
                            EXECUTE stmt;
                            DEALLOCATE PREPARE stmt;
                        WHEN v_business_type = ‘04‘ THEN
                            SET @v_sql=‘INSERT INTO `t_option_value_rel_resource` (`id`, `field_id`, `data_id`, `business_type`, `code`)(‘;
                            SET @v_sql= CONCAT(@v_sql,‘select UUID_SHORT(), ‘,v_field_id,‘, c.ID, \‘04\‘, c.‘,v_field_column,‘ from t_opportunity c where c.validity = 1 and c.‘,v_field_column,‘ is not null);‘);
                            PREPARE stmt FROM @v_sql;
                            EXECUTE stmt;
                            DEALLOCATE PREPARE stmt;
                        WHEN v_business_type = ‘05‘ THEN
                            SET @v_sql=‘INSERT INTO `t_option_value_rel_resource` (`id`, `field_id`, `data_id`, `business_type`, `code`)(‘;
                            SET @v_sql= CONCAT(@v_sql,‘select UUID_SHORT(), ‘,v_field_id,‘, c.ID, \‘05\‘, c.‘,v_field_column,‘ from t_contract c where c.validity = 1 and c.‘,v_field_column,‘ is not null);‘);
                            PREPARE stmt FROM @v_sql;
                            EXECUTE stmt;
                            DEALLOCATE PREPARE stmt;
                        WHEN v_business_type = ‘15‘ THEN
                            SET @v_sql=‘INSERT INTO `t_option_value_rel_resource` (`id`, `field_id`, `data_id`, `business_type`, `code`)(‘;
                            SET @v_sql= CONCAT(@v_sql,‘select UUID_SHORT(), ‘,v_field_id,‘, c.ID, \‘15\‘, c.‘,v_field_column,‘ from t_product c where c.validity = 1 and c.‘,v_field_column,‘ is not null);‘);
                            PREPARE stmt FROM @v_sql;
                            EXECUTE stmt;
                            DEALLOCATE PREPARE stmt;
                        WHEN v_business_type = ‘23‘ THEN
                            SET @v_sql=‘INSERT INTO `t_option_value_rel_resource` (`id`, `field_id`, `data_id`, `business_type`, `code`)(‘;
                            SET @v_sql= CONCAT(@v_sql,‘select UUID_SHORT(), ‘,v_field_id,‘, c.ID, \‘23\‘, c.‘,v_field_column,‘ from t_payment_record c where c.validity = 1 and c.‘,v_field_column,‘ is not null);‘);
                            PREPARE stmt FROM @v_sql;
                            EXECUTE stmt;
                            DEALLOCATE PREPARE stmt;
                        WHEN v_business_type = ‘25‘ THEN
                            SET @v_sql=‘INSERT INTO `t_option_value_rel_resource` (`id`, `field_id`, `data_id`, `business_type`, `code`)(‘;
                            SET @v_sql= CONCAT(@v_sql,‘select UUID_SHORT(), ‘,v_field_id,‘, c.ID, \‘25\‘, c.‘,v_field_column,‘ from t_sale_contract c where c.validity = 1 and c.‘,v_field_column,‘ is not null);‘);
                            PREPARE stmt FROM @v_sql;
                            EXECUTE stmt;
                            DEALLOCATE PREPARE stmt;
                        WHEN v_business_type = ‘30‘ THEN
                            SET @v_sql=‘INSERT INTO `t_option_value_rel_resource` (`id`, `field_id`, `data_id`, `business_type`, `code`)(‘;
                            SET @v_sql= CONCAT(@v_sql,‘select UUID_SHORT(), ‘,v_field_id,‘, c.ID, \‘30\‘, c.‘,v_field_column,‘ from t_bill_record c where c.validity = 1 and c.‘,v_field_column,‘ is not null);‘);
                            PREPARE stmt FROM @v_sql;
                            EXECUTE stmt;
                            DEALLOCATE PREPARE stmt;
                    ELSE
                        SET done = 1;
                    END CASE;

                    SET done = 0;
                END IF ;
            END;
    END LOOP fieldloop ;
    CLOSE fields ;
END ;

call INSERT_OPTION_VALUE();

DROP PROCEDURE IF EXISTS INSERT_OPTION_VALUE;
/*存储过程end*/