创建 MySQL 临时表，然后从中分配 CRUD [英] Creating MySQL Temporary Table then Assign CRUD From It

问题描述

我创建了一个存储过程，其逻辑如下:

i have created a stored procedure which have logic like this:

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

这是我的存储过程语法:

this is my stored procedure syntax:

  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行.

The stored procedure is successfully compiled, but when i call the stored procedure it always returns 0 rows.

调用存储过程的语法:

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

我的问题:

1. 可以再次使用临时表来创建进一步的 CRUD 语句吗?
2. 对失败的地方有什么想法吗?

谢谢

推荐答案

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

okay, i have discovered a mistake. The method to call the stored procedure is

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

没有引号.

这篇关于创建 MySQL 临时表，然后从中分配 CRUD的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！