Re: How to convert this Oracle PL/SQL Function to MySQL

Re: How to convert this Oracle PL/SQL Function to MySQL

Posted by: Nausher Cholavaram
 Date: November 30, 2007 01:53PM

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 - 17072 54877

 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 54877 17072
 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 54877 17072 DEFAULT

 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 54877="
 And for assignment expressions
 Find RegEx - "([a-zA-Z]+) := ([a-zA-Z0-9\'\-]+)"
Replacment Expr - "SET 54877 = 17072"
 Preserving white space,
 Find RegEx - "^([ ]+)([a-zA-Z]+):= ([a-zA-Z0-9\'\-]+)"
Replacment Expr - "54877SET 17072 :=

경축! 아무것도 안하여 에스천사게임즈가 새로운 모습으로 재오픈 하였습니다.
어린이용이며, 설치가 필요없는 브라우저 게임입니다.
https://s1004games.com

 Similiary for CONSTANT DECLARATIONS use
 Find RegEx - ([a-zA-Z_]+) constant ([a-zA-Z0-9)(]+) := ([0-9]+);
 Replace Expr - DECLARE 54877 17072 DEFAULT

 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(54877, 17072)

 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(54877)"

 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.