Conversion of Cursor with Parameters from Oracle to MySQL

In Oracle cursor may have parameters that can appear in a query wherever a constant can appear. Parameters allow using one cursor with different values of input parameters in the query.

When converting an Oracle cursor with parameters to MySQL, SQLWays declares and uses local variables instead of the cursor parameters. Before opening the cursor, SQLWays assigns the values of the cursor parameters for these local variables.

TABLE 46. Cursor with Parameters Conversion from Oracle to MySQL
Oracle	MySQL
create procedure ora_sp_cur_with_param_mysql a var1 number := 0; CURSOR cur1 (val1 number) IS SELECT col1 FROM tab1 WHERE col5 = val1; BEGIN open cur1 (0); fetch cur1 into var1; close cur1; open cur1 (5); fetch cur1 into var1; close cur1; END;	Create procedure ora_sp_cur_with_param_mysql() BEGIN DECLARE var1 FLOAT DEFAULT 0; DECLARE val1 FLOAT; DECLARE cur1 CURSOR FOR SELECT col1 FROM tab1 WHERE col5 = val1; SET val1 = 0; open cur1; fetch cur1 into var1; close cur1; SET val1 = 5; open cur1; fetch cur1 into var1; close cur1; end;

TABLE 46. Cursor with Parameters Conversion from Oracle to MySQL

Oracle

MySQL

create procedure ora_sp_cur_with_param_mysql
a
var1 number := 0;
CURSOR cur1 (val1 number)  IS
SELECT col1 FROM tab1
WHERE col5 = val1;
BEGIN
open cur1 (0);
fetch cur1 into var1;
close cur1;
open cur1 (5);
fetch cur1 into var1;
close cur1;
END;

Create procedure ora_sp_cur_with_param_mysql()
BEGIN
DECLARE var1 FLOAT DEFAULT 0;
DECLARE val1 FLOAT;
DECLARE cur1 CURSOR FOR
SELECT col1 FROM tab1
WHERE col5 = val1;
SET val1 = 0;
open cur1;
fetch cur1 into var1;
close cur1;
SET val1 = 5; 
open cur1;
fetch cur1 into var1;
close cur1;
end;

Ispirer SQLWays Database Migration Software

Conversion of Cursor with Parameters from Oracle to MySQL