mysql - 创建 MySQL 临时表然后从中分配 CRUD

Question

我创建了一个具有如下逻辑的存储过程:

1. 从 2 个表中获取数据结果，然后基于它创建临时表(使用两个参数开始日期和结束日期选择数据范围)。
2. 从临时表中选择几列然后分配给另一个表(例如 X)
3. 更新表 (X)。

这是我的存储过程语法:

  DELIMITER $$

  USE `sre`$$

  DROP PROCEDURE IF EXISTS `sp_checkPotentialProductf`$$

  CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_checkPotentialProductf`(IN sdate DATE, IN edate DATE)
BEGIN 
DECLARE sumtotal INT DEFAULT 0;
DECLARE first_margin DOUBLE;
DECLARE count_rows INT DEFAULT 0;
DECLARE startnum INT DEFAULT 0;

DECLARE start_mrg DOUBLE;
DECLARE adder DOUBLE;

/* assign table temporary and select the values */

CREATE TEMPORARY TABLE IF NOT EXISTS vShowTopSellingQuantitys AS
 SELECT
  `mt_pesanan`.`ms_langganan_idms_langganan`  AS `idms_langganan`,
  `mt_pesanan`.`ms_langganan_idms_kodebarang` AS `idms_kodebarang`,
  `md`.`nama`                         AS `nama`,
  `mt_pesanan`.`quantity`                     AS `quantity`,
  `mt_pesanan`.`harga`                        AS `harga`,
  `mt_pesanan`.`jumlah`                       AS `jumlah`,
  `mt_pesanan`.`discount`                     AS `discount`,
  `mt_pesanan`.`tgl_pesan`                    AS `tgl_pesan`,
  SUM(`mt_pesanan`.`quantity` )   AS `sum_product`
 FROM `mt_pesanan`  JOIN `ms_barang` `md`
 WHERE ((`mt_pesanan`.`tgl_pesan` <= edate) AND (`mt_pesanan`.`tgl_pesan` >= sdate)) 
       AND (md.`idms_kodebarang` = `mt_pesanan`.`ms_langganan_idms_kodebarang`)
     GROUP BY `mt_pesanan`.`ms_langganan_idms_kodebarang`
     ORDER BY SUM(`mt_pesanan`.`quantity` ) DESC;
/* end assign table temporary */

/* assign values to variables */
SELECT SUM(sum_product) INTO sumtotal FROM `vShowTopSellingQuantitys`;
SELECT COUNT(sum_product) INTO count_rows FROM `vShowTopSellingQuantitys`;
SELECT (sum_product/sumtotal)*100 INTO first_margin FROM `vShowTopSellingQuantitys` LIMIT 1; 

/* truncate database tmp*/ 
TRUNCATE tmp_produkpotensial;

/*creating algorithm to analyze the percentage quantity/total */
SET startnum = 1;

/*show result with percentage*/
SELECT `idms_kodebarang`, `nama`, sum_product , ((sum_product/sumtotal)*100) AS 'pecentage_margin' FROM `vShowTopSellingQuantitys`;

/*result execution from temporary table to analyze potential product */
INSERT INTO tmp_produkpotensial(idms_kodebarang,nama,percentage_margin)
SELECT `idms_kodebarang`, `nama`, ((sum_product/sumtotal)*100) AS 'percentage_margin' FROM `vShowTopSellingQuantitys`;

/* update the sum total of percentage */    
WHILE(count_rows >= startnum) DO
IF(startnum = 1) THEN
  SET start_mrg = 0;
  UPDATE tmp_produkpotensial SET tmp_produkpotensial.sum_margin = (percentage_margin + start_mrg) WHERE id_barang = startnum;
ELSE
  SELECT sum_margin INTO start_mrg FROM tmp_produkpotensial WHERE id_barang = (startnum - 1);
  UPDATE tmp_produkpotensial SET tmp_produkpotensial.sum_margin = (percentage_margin + start_mrg) WHERE id_barang = startnum;
END IF;

/* Assign Update to Update Sum Margin Column */
SET startnum = startnum + 1;
END WHILE;


 END$$

 DELIMITER ;

存储过程已成功编译，但当我调用存储过程时，它总是返回 0 行。

调用存储过程的语法:

  CALL `sp_checkPotentialProductf`('2012-12-31','2012-01-01');

我的问题:

1. 可以再次使用临时表来创建进一步的 CRUD 语句吗？
2. 是否知道失败的原因？

谢谢

Answer 1

好的，我发现了一个错误。调用存储过程的方法是

CALL `sp_checkPotentialProductf`(2012-12-31,2012-01-01);

没有引号。