I've been reading & lurking on the MySQL Forums for a week now, in order to learn about converting a couple of Oracle Stored Procedures I had to MySQL.
Here are my notes from this exercise, currently it is unedited, I will
soon have this edited and cleaned up.
Till then I present
Nausher's (Unedited) Guide to converting Oracle Stored Procedures to MySQL
==========================================================================
1) FUNCTIONS in MySQL cannot take OUT/INOUT parameters, change any such Oracle functions
to PROCEDURE.
1) Make use of the DELIMITER construct, or your MySQL Stored Procedures
just wont work.
2) Change "AND ROWNUM < x" to " LIMIT x"
Note: There is no "AND" before the LIMIT clause.
3) Change SQL%ROWCOUNT with the following constructs
For select queries -> FOUND_ROWS()
For update/insert/delete -> ROW_COUNT()
4) Change the CREATE PROCEDURE construct
'CREATE PROCEDURE xyz
AS declarations
BEGIN '
somefield CONSTANT NUMBER(1) := 3;
to
'CREATE PROCEDURE xyz()
BEGIN
DECLARE declarations'
4a)
Change the parameters, as MySQL has the parameter direction IN|OUT|INOUT first then name and type.
Find RegEx - (([a-zA-Z]+) (IN) ([a-zA-Z0-9\(]+) and. ([a-zA-Z]+) (OUT) ([a-zA-Z0-9\(]+)
Replacment Expr - \2 \1 \3
4b) Declarations follow the following order i) Variables ii) Cursors iii) Handlers
Replace all declare statements to use the DECLARE statement
Find RegEx - (db[a-zA-Z]+) ([a-zA-Z0-9\(\)]+)
Replace Expr -DECLARE \1 \2
For Ora expressions with the CONSTANT keyword, (MySQL doesn't support)
Find RegEx - ([a-zA-Z0-9_]+) constant ([a-zA-Z0-9\(\)]+) := ([a-zA-Z0-9\'\-]+)
Replace Expr - DECLARE \1 \2 DEFAULT \3
5) Change CONSTANT types to DEFAULT
Oracle
------
PROCEDURE abc
IS
somefield CONSTANT NUMBER(1) := 3;
BEGIN
MySQL
-----
DECLARE somefield INT(1) DEFAULT 3;
6) Replace 'EXIT WHEN condition' statements with the 'LEAVE label:' construct IF meets condition
Oracle
------
LOOP
EXIT WHEN SQL%ROWCOUNT = 0;
END LOOP;
MySQL
-----
msgloop: LOOP
IF exit condition met THEN
LEAVE msgloop;
END LOOP msgloop;
7) Convert Data types using the MySQL Migration Toolkit i.e. OracleDump.pl (http://forums.mysql.com/read.php?61,43890,43890)
or refer the table 2.4. at
http://download.oracle.com/docs/cd/E10405_01/doc/appdev.120/e10380/oracle_mysql_compared.htm#g1034154
8) Convert Oracle variable definitions to MySQL definitions
i.e. "xyz:=123" => "SET xyz=123" you can use RegEx to do this.
Example expressions using notepad++ are
Find RegEx - "(db[a-zA-Z]+) :="
Replacment Expr - "SET \1="
And for assignment expressions
Find RegEx - "([a-zA-Z]+) := ([a-zA-Z0-9\'\-]+)"
Replacment Expr - "SET \1 = \2"
Preserving white space,
Find RegEx - "^([ ]+)([a-zA-Z]+):= ([a-zA-Z0-9\'\-]+)"
Replacment Expr - "\1SET \2 := \3"
Similiary for CONSTANT DECLARATIONS use
Find RegEx - ([a-zA-Z_]+) constant ([a-zA-Z0-9)(]+) := ([0-9]+);
Replace Expr - DECLARE \1 \2 DEFAULT \3;
9) Similiarly change FUNCTIONS like this
Oracle -
FUNCTION getFunction ( dbId IN NUMBER) RETURN NUMBER
IS
<Function Body>
MySQL -
CREATE FUNCTION getFunction ( dbId IN INT) RETURNS INT
RETURN
<Function Body>
10) Replace functions as follows
Oracle => MySQL
-----------------
NVL() => IFNULL()
Find RegEx - NVL\(([a-zA-Z_\.]+)\, ([a-zA-Z0-9\'\-]+)\)
Replace Expr - IFNULL(\1, \2)
11) CURSOR Declaration change like the following
DECLARE CURSOR C_cur(type declr) IS <SELECT STATEMENT> (Ora)
To
C_cur CURSOR FOR <SELECT STATEMENT> (MySQL)
(Note: MySQL uses, curvariable CURSOR, this is different from Oracle.
12) Change labels (possibly for GOTO) from <<END_FUNC>> => :END_FUNC
13) Change Exception Handling as per the following -
Oracle -
EXCEPTION
WHEN NO_DATA_FOUND THEN stmts..;
MySQL -
DECLARE CONTINUE HANDLER FOR NOT FOUND stmts..; -- Set a variable here like SET not_found =1
Check for the variable, to decide the error condition, when running the query.
REPEAT
stmts..;
UNTIL not_found END REPEAT;
14) If you have Oracle cursor with the FOR loop
You can change it thus
i) First declare a Boolean - DECLARE done BOOL DEFAULT FALSE;
ii) And a continue handler, which will set this boolean -
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET done := TRUE;
iii) And then re-write the FOR loop as a loop with an IF check
i.e.
Change -
-- cursor definition for MYCURSOR
CURSOR MYCURSOR(someid number) IS
FOR I IN MYCURSOR
LOOP
-- stmts;
END LOOP;
To -
-- cursor definition for MYCURSOR
DECLARE CURSOR MYCURSOR FOR -- select query
OPEN MYCURSOR;
loop_mycursor: LOOP
FETCH MYCURSOR INTO variables --(a,b,c where a b c have been declared before);
IF done THEN
CLOSE MYCURSOR;
LEAVE loop_mycursor;
END IF;
-- stmts;
END LOOP;
15) Ensure your Oracle VARCHAR & VARCHAR2 references which have no default values are provided with some
e.g. VARCHAR(15). Note: there is no VARCHAR2 type in MySQL as of 5.0.1
16) MySQL (5.1) does not support the GOTO statement, you can change a block using teh following construct
to access the GOTO statement.
lbl: BEGIN
LEAVE lbl;
END lbl;
17) Convert function calls from oracle to mysql
E.g.
If converting INT to VARCHAR or vice versa, don't have to use an equiv functions to TO_CHAR() as MySQL
will automatically cast types, if that doesn't work try using a CONCAT() to force INT to VARCHAR and CAST/CONVERT().
TO_CHAR() to FORMAT() -- (use LPAD() as well, if you are looking to pad the data)
TO_CHAR(,'xxx') to HEX() -- For creating Hex Strings
Use the following RegEx to simplify conversion :-
Find RegEx - "ltrim\(to_char\(([a-zA-Z]+), \'[x]+\'\)\)"
Replacment Ex - "HEX(\1)"
For Dates -
Convert
TO_CHAR(SYSDATE, 'YYYYMMDDHH24MI') , to
DATE_FORMAT(CURRENT_TIMESTAMP, '%Y%m%d%H%i') ,
Note: Replace SYSDATE with CURRENT_TIMESTAMP
Refer -
http://www.adp-gmbh.ch/ora/sql/to_char.html for Date conversion formats.
18) Change any join qeueries to use MySQL Syntax.
Shortcuts like (+) need to be replaced with LEFT OUTER JOIN (complete ANSI syntax)
Oracle uses (+) in the predicate to indicate if to return the row even if this column is NULL.
E.g.
select count(*) from v$session s, v$process p where p.addr=s.paddr (+) and s.sid (+) = -1;
Here even if s.paddr , s.sid have NULL values rows will be returned.
Whereas, in ANSI syntax
select count(*) from v$process p left join v$session s on p.addr=s.paddr where s.sid = -1;
The left join is done first, then the rows are filtered, thus any rows with s.sid = NULL will be filtered.
Way out is to, add the predicate to the JOIN ON clause,
select count(*) from v$process p left join v$session s on p.addr=s.paddr and s.sid = -1;
E.g.
SELECT A.STATUS, A.VISIBLE, NVL(A.STATUS_STRING_CODE, -1), NVL(A.STATUS_STRING, ''), NVL(A.LOCALE_STRING, ''),
NVL(A.DEVICE_GROUP, 0), NVL(A.CLIENT_IP_ADDR, ''), NVL(A.CLIENT_PORT_NO, 0), NVL(C.DEVICE_GROUP, 0),
NVL(A.MSISDN, '')
INTO dbStatus, dbVisible, dbStatusStrCode, dbStatusStr, dbLocalStr, dbDeviceGrp, dbIpAddr, dbPortNo, dbOffDevGrp,
dbMsisdn
FROM USERS A, USER_DEVICES B, DEVICES C
WHERE A.USER_ID = dbPalId
AND A.USER_ID = B.USER_ID (+)
AND B.DEVICE_ID = C.DEVICE_ID (+);
Changed to (MySQL) -
SELECT A.STATUS, A.VISIBLE, IFNULL(A.STATUS_STRING_CODE, -1), IFNULL(A.STATUS_STRING, ''), IFNULL(A.LOCALE_STRING, ''),
IFNULL(A.DEVICE_GROUP, 0), IFNULL(A.CLIENT_IP_ADDR, ''), IFNULL(A.CLIENT_PORT_NO, 0), IFNULL(C.DEVICE_GROUP, 0), IFNULL(A.MSISDN, '')
FROM USERS A LEFT JOIN USER_DEVICES B ON B.USER_ID = A.USER_ID LEFT JOIN DEVICES C ON B.DEVICE_ID = C.DEVICE_ID
WHERE A.USER_ID = 1;
In case you have multiple tables in the Join Query chain the ANSI JOIN statements i.e. tableA LEFT JOIN tableB ON condn1 LEFT JOIN tableC ON condn2;
Thus
SELECT COUNT(*) as oracount
FROM USER_PALS A, USER_PALS B, USERS C
WHERE A.PAL_USER_ID = B.USER_ID (+)
AND B.PAL_USER_ID = C.USER_ID (+)
AND A.USER_ID = 1
Will become
SELECT COUNT(*) as myscount
FROM USER_PALS A LEFT JOIN USER_PALS B ON A.PAL_USER_ID = B.USER_ID LEFT JOIN USERS C ON B.PAL_USER_ID = C.USER_ID
WHERE A.USER_ID = 1
19) Since there are no sequences in MySQL, and the MySQL LAST_INSERT_ID() function
only returns values on the last insert statement, change
Oracle stmts like - SELECT SOME_SEQ.NEXTVAL FROM DUAL;
To this in MySQL - SELECT MAX(SOME_COL) + 1 FROM SOME_TABLE;
20) IF ELSE conditions,
The MySQL construct is ELSEIF and not ELSIF (as per Oracle).
Additionally if you are incrementing a variable
eg. SET A := A + 2; make sure to add brackets, as the MySQL parser wont accept otherwise
i.e. SET A := (A + 2);
21) Change procedure invocations to CALL getProcedure() *-check
22) MySQL does not like the Oracle No-op stmt NULL;(E.g. in IF THEN ELSE, SWITCH ... CASE)
replace with empty "BEGIN END;" block, as MySQL tolerates
empty BEGIN END blocks.