Привет, мне сложно с этой хранимой процедурой. im получение ошибки: Результат состоял из нескольких строк.
вот моя хранимая процедура:
DELIMITER $$
DROP PROCEDURE IF EXISTS `dss`.`COSTRET` $$
CREATE DEFINER=`dwadmin`@`192.168.%.%` PROCEDURE `COSTRET`( TDATE DATE)
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE ls_id VARCHAR(8);
DECLARE ld_cost DECIMAL(10,4);
DECLARE ld_retail DECIMAL(10,4);
DECLARE cur1 CURSOR FOR SELECT DISTINCT `id` FROM `prod_performance` WHERE `psc_week` = TDATE;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
-- Get the Cost
CREATE TEMPORARY TABLE IF NOT EXISTS `prod_itemcost`
SELECT DISTINCTROW `itemcode` ID, `mlist` COST
FROM (SELECT `itemcode`, `pceffdate`, `mlist`
FROM `purchcost` a
where `pceffdate` = (SELECT MAX(z.`pceffdate`) FROM `purchcost` z WHERE z.`itemcode` = a.`itemcode`
AND z.`pceffdate` <= TDATE)) tb
ORDER BY `itemcode`;
OPEN cur1;
REPEAT
FETCH cur1 INTO ls_id;
IF NOT done THEN
SELECT DISTINCTROW `cost` INTO ld_cost FROM `prod_itemcost` WHERE id = ls_id;
UPDATE LOW_PRIORITY `prod_performance` SET `current_cost` = ld_cost WHERE `psc_week` = TDATE and `id` = ls_id;
END IF;
UNTIL done END REPEAT;
CLOSE cur1;
-- Destroy Temporary Tables
DROP TEMPORARY TABLES IF EXISTS `prod_itemcost`;
END $$
DELIMITER ;
Любые решения и рекомендации очень ценятся!
