创建 MySQL 临时表,然后从中分配 CRUD [英] Creating MySQL Temporary Table then Assign CRUD From It
本文介绍了创建 MySQL 临时表,然后从中分配 CRUD的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我创建了一个存储过程,其逻辑如下:
i have created a stored procedure which have logic like this:
- 从 2 个表中获取数据结果,然后基于它创建临时表(使用两个参数开始日期和结束日期选择数据范围).
- 从临时表中选择几列,然后分配给另一个表(例如 X)
- 更新表 (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');
我的问题:
- 可以再次使用临时表来创建进一步的 CRUD 语句吗?
- 对失败的地方有什么想法吗?
谢谢
推荐答案
好的,我发现了一个错误.调用存储过程的方法是
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屋!
查看全文