MySQL Forums
Forum List  »  Stored Procedures

Use of multiple cursors in Mysql stored procedure
Posted by: Richard Premkumar
Date: June 23, 2015 09:59AM

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;

Options: ReplyQuote


Subject
Views
Written By
Posted
Use of multiple cursors in Mysql stored procedure
11466
June 23, 2015 09:59AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.