Use of multiple cursors in Mysql stored procedure
As a part of an upgrade project, we are converting existing Oracle Stored Procedures to equivalent MySQL stored procedure.
Fairly new to MySQL, we do not have expertise in scripting stored procedures in MySQL.
Need to understand how the following Oracle procedure can be converted to MySQL Stored procedure:
EXEC SQL EXECUTE
DECLARE
CURSOR cur_1
IS
SELECT a.col1, a.col2
FROM table a
ORDER BY a.col1 ASC, a.col2;
cur_1_item cur_1%rowtype;
CURSOR cur_2
IS
SELECT b.col1, b.col2
FROM table b
ORDER BY b.col1 DESC, b.col2;
cur_2_item cur_2%rowtype;
last_fetch VARCHAR2(7) := 'neither';
threshold_price NUMBER;
threshold_volume NUMBER;
FUNCTION end_of_arbitrage(
cur_2_item_price NUMBER,
cur_1_item_price NUMBER)
RETURN BOOLEAN
IS
BEGIN
IF cur_2_item_price < cur_1_item_price THEN
RETURN true;
ELSE
RETURN false;
END IF;
END;
BEGIN
debug.put_line ('Arbitrage tagging');
OPEN cur_1;
FETCH cur_1 INTO cur_1_item;
OPEN cur_2;
FETCH cur_2 INTO cur_2_item;
LOOP
IF cur_2%notfound OR cur_1%notfound OR end_of_arbitrage (cur_2_item.price, cur_1_item.price) THEN
EXIT;
END IF;
debug.put ('Sell ' || TO_CHAR (ROUND (cur_2_item.volume, 3)) || ' MWh @ ' || TO_CHAR (ROUND (cur_2_item.price, 3)));
debug.put (', Buy ' || TO_CHAR (ROUND (cur_1_item.volume, 3)) || ' MWh @ ' || TO_CHAR (ROUND (cur_1_item.price, 3)));
IF cur_2_item.volume = cur_1_item.volume THEN
debug.put_line (' Fetch Both');
cur_1_item.volume := 0;
cur_2_item.volume := 0;
last_fetch := 'sell';
FETCH cur_2 INTO cur_2_item;
IF cur_2%notfound OR end_of_arbitrage (cur_2_item.price, cur_1_item.price) THEN
debug.put_line (' -- end with Sell ' || TO_CHAR (ROUND (cur_2_item.volume, 3)) || ' MWh @ ' || TO_CHAR (ROUND (cur_2_item.price, 3)));
EXIT;
END IF;
last_fetch := 'buy';
FETCH cur_1 INTO cur_1_item;
IF cur_1%notfound OR end_of_arbitrage (cur_2_item.price, cur_1_item.price) THEN
debug.put_line (' -- end with Buy ' || TO_CHAR (ROUND (cur_1_item.volume, 3)) || ' MWh @ ' || TO_CHAR (ROUND (cur_1_item.price, 3)));
EXIT;
END IF;
last_fetch := 'both';
ELSE
IF cur_2_item.volume < cur_1_item.volume THEN
cur_1_item.volume := cur_1_item.volume - cur_2_item.volume;
debug.put (' -- reduce Buy to ' || TO_CHAR (ROUND (cur_1_item.volume, 3)) || ' MWh @ ' || TO_CHAR (ROUND (cur_1_item.price, 3)));
debug.put_line (' Fetch Sell');
last_fetch := 'sell';
FETCH cur_2 INTO cur_2_item;
elsif cur_1_item.volume < cur_2_item.volume THEN
cur_2_item.volume := cur_2_item.volume - cur_1_item.volume;
debug.put (' -- reduce Sell to ' || TO_CHAR (ROUND (cur_2_item.volume, 3)) || ' MWh @ ' || TO_CHAR (ROUND (cur_2_item.price, 3)));
debug.put_line (' Fetch Buy');
last_fetch := 'buy';
FETCH cur_1 INTO cur_1_item;
END IF;
END IF;
END LOOP;
IF last_fetch = 'neither' THEN
debug.put_line ('No Arbitrage data found');
RETURN;
elsif last_fetch = 'both' -- Can't happen
THEN
dbms_standard.raise_application_error ( -20796, 'Program error in arbitrage, last fetch was ''both''', true);
END IF;
IF cur_2%notfound THEN
debug.put_line ('Sell arbitrage all');
UPDATE temp_table
SET vol = NULL
WHERE ..
;
debug.put_line (TO_CHAR (sql%rowcount) || ' rows updated');
ELSE
debug.put_line ('Sell arbitrage > ' || TO_CHAR (cur_2_item.price));
UPDATE temp_table
SET vol = NULL
WHERE ..
;
debug.put_line (TO_CHAR (sql%rowcount) || ' rows updated');
END IF;
IF cur_1%notfound THEN
debug.put_line ('Buy arbitrage all');
UPDATE temp_table
SET vol = NULL
WHERE ..
;
debug.put_line (TO_CHAR (sql%rowcount) || ' rows updated');
ELSE
debug.put_line ('Buy arbitrage < ' || TO_CHAR (cur_1_item.price));
UPDATE temp_table
SET vol = NULL
WHERE ..
;
debug.put_line (TO_CHAR (sql%rowcount) || ' rows updated');
END IF;
IF last_fetch = 'sell' THEN
threshold_price := cur_1_item.price;
threshold_volume := 0;
WHILE cur_1_item.price = threshold_price AND NOT cur_1%notfound
LOOP
threshold_volume := threshold_volume + cur_1_item.volume;
FETCH cur_1 INTO cur_1_item;
END LOOP;
debug.put_line ('Un-tagged buy volume ' || TO_CHAR (threshold_volume) || ' MWh @ ' || TO_CHAR (threshold_price));
UPDATE temp_table bss
SET vol =
(SELECT DECODE (threshold_volume, 0, NULL, bss.volume_after_dmat * (threshold_volume / SUM (volume_after_dmat)))
FROM temp_table
WHERE price = threshold_price
)
WHERE price = threshold_price
;
debug.put_line (TO_CHAR (sql%rowcount) || ' rows updated');
elsif last_fetch = 'buy' THEN
threshold_price := cur_2_item.price;
threshold_volume := 0;
WHILE cur_2_item.price = threshold_price AND NOT cur_2%notfound
LOOP
threshold_volume := threshold_volume - cur_2_item.volume;
FETCH cur_2 INTO cur_2_item;
END LOOP;
debug.put_line ('Un-tagged sell volume ' || TO_CHAR (threshold_volume) || ' MWh @ ' || TO_CHAR (threshold_price));
UPDATE temp_table bss
SET vol =
(SELECT DECODE (threshold_volume, 0, NULL, bss.volume_after_dmat * (threshold_volume / SUM (volume_after_dmat)))
FROM temp_table
WHERE price = threshold_price
)
WHERE price = threshold_price
;
debug.put_line (TO_CHAR (sql%rowcount) || ' rows updated');
END IF;
CLOSE cur_1;
CLOSE cur_2;
END;
END-EXEC;