mysql - 案例不适用于我的存储过程

Question

我创建了此过程来在表product_universal_description 中插入upc_id 和相关值。

 CREATE PROCEDURE veealpha
    ( 
      IN s_po_id INT(11),
      IN s_supplier_id INT(11),
      IN s_location_id VARCHAR(32),
      IN s_warehouse_id INT(11),
      IN s_user_id INT(11),
      OUT message VARCHAR(64),
      OUT error_code INT(4)
    )

    BEGIN 

      DECLARE temp_upc VARCHAR(32);
      DECLARE i INT;
      DECLARE finished INTEGER DEFAULT 0;
      DECLARE loop_count int(4);
      DECLARE upc varchar(32);
      DECLARE p_product_id int(11);
      DECLARE p_model varchar(64);


      DECLARE counter_cursor CURSOR FOR 
        SELECT product_id,model,quantity FROM product 
          WHERE model in('CFB0040','CFB0042','CFB0043','CFB0044') 
            AND quantity > 0;

      DECLARE CONTINUE HANDLER FOR 1062 
        SET message = 'Duplicate Keys Found';

      DECLARE CONTINUE HANDLER FOR NOT FOUND 
        SET finished = 1;

      OPEN counter_cursor;

      add_data : LOOP
        FETCH counter_cursor INTO p_product_id, p_model, loop_count;
          SET i = 1;
          WHILE loop_count > 0 DO 
            CASE i
              WHEN i < 10 THEN
                SET temp_upc = CONCAT(s_po_id,'-','CFC','-','30','-','APR14','-',p_model,'-000',i);
              WHEN (i >= 10 AND i < 100) THEN  
                SET temp_upc = CONCAT(s_po_id,'-','CFC','-','30','-','APR14','-',p_model,'-00',i);
              WHEN (i >= 100 AND i < 1000) THEN  
                SET temp_upc = CONCAT(s_po_id,'-','CFC','-','30','-','APR14','-',p_model,'-0',i);
              ELSE
                SET temp_upc = CONCAT(s_po_id,'-','CFC','-','30','-','APR14','-',p_model,'-',i);
            END CASE;
            INSERT INTO product_universal_description
            (
              `upc_id`,
              `po_id`,
              `supplier_id`,
              `location_id`,
              `warehouse_id`,
              `product_id`,
              `model_no`,
              `added_by`,
              `updated_by`,
              `date_added`,
              `date_modified`
              ) VALUES (
              temp_upc,
              s_po_id,
              s_supplier_id,
              s_location_id,
              s_warehouse_id,
              p_product_id,
              p_model,
              s_user_id,
              s_user_id,
              NOW(),
              NOW()
            );
            SET i=i+1;
            SET loop_count = loop_count - 1;
          END WHILE;
        IF finished = 1 THEN 
          LEAVE add_data;
        END IF;
      END LOOP add_data;

      CLOSE counter_cursor;
    END

    CALL veealpha(123,45,'UP',1,56,@msg,@err);

ON 执行我得到这样的结果。

我如何为 UPC_ID 给出条件，即应根据具体情况妥善处理。但是如果 i = 1 FOR all 则在 CASE 处需要 ELSE 条件。谁能告诉我..发生了什么问题以及我怎样才能得到想要的结果。

Answer 1

尝试:

...
-- CASE i
CASE
    WHEN i < 10 THEN
        SET temp_upc = CONCAT(s_po_id,'-','CFC','-','30','-','APR14','-',p_model,'-000',i);
    WHEN (i >= 10 AND i < 100) THEN  
        SET temp_upc = CONCAT(s_po_id,'-','CFC','-','30','-','APR14','-',p_model,'-00',i);
    WHEN (i >= 100 AND i < 1000) THEN  
        SET temp_upc = CONCAT(s_po_id,'-','CFC','-','30','-','APR14','-',p_model,'-0',i);
    ELSE
        SET temp_upc = CONCAT(s_po_id,'-','CFC','-','30','-','APR14','-',p_model,'-',i);
END CASE;
...