Oracle Migrating MySQL Database note

Oracle to MySQL modification:

1.substr ()
substr (String, 0, 10) Here the test must be obtained from the first bit both substr (string, 1, 10)
2.To_char ()
Can only be used as a function of Oracle, compatible with Oracle and MySQL and changed to concat (..., ");
Two are used here, one is to convert a similar int to a string
3. Select * FROM (SELECT * from table2) to add aliases here
4. NVL function
was changed to case if Length (str) >0 then ' processing 1 ' else ' processing 2 ' end
5. Use Oracle to get the current date and format it as YYYY-MM-DD and the day before
Resolved, the acquisition time is changed to a value, Java gets the current time and the day before the date passed to the database (MySQL has its own cache, if the function is used, he will not cache, in addition to compatible with MySQL and Ora cle)
6. Oracle RowNum
Select @rownum: [Email protected]+1 rownum
From (select @rownum: =0 from table)
7. To_char (to_date (t1.create_date, ' yyyy-mm-dd hh24:mi:ss '), ' DateFormat ')
 modified in order to? Str_to_date (t1.create_date, '%y-%m-%d%h:%i:%s ')? Date_format (now (), '%y-%m-%d%h:%i:%s '
8. the meaning of the date's direct subtraction is different.
For example, Oracle Sysdate + 1 becomes sysdate () + Interval 1 day (note that if written sysdate () + 1 syntax is correct, but the meaning is wrong)
Query Select Sysdate () + 1 from dual in MySQL for example 20080223153234 (= 20080223153233 + 1) number
9. Select ' ABC ' | | ' d ' from dual two data execution results are different (grammar can pass), MySQL is written in the form of select Concat (' abc ', ' d ')
10.autocommitNote that the MySQL
11.Remove Aliases
Delete from table1 t where substr (t.trade_date,1,4) =?
Such SQL can be in Oracle but in MySQL you need to remove the alias
Delete from table1 where substr (trade_date,1,4) =?
12.Stored Procedures
Oracle with parameter declaration
Create or Replace procedure Thinkxdt.proc_update_seq (V_typename in Varchar2,v_num out varchar2)
    MySQL with parameter declaration
DROP PROCEDURE IF EXISTS ' proc_update_seq ';
CREATE definer= ' root ' @ '% ' PROCEDURE ' proc_update_seq ' (in V_typename varchar ($), out V_num varchar (200))
    
The Oracle parameter is (name In/out type)
The MySQL parameter for the (in/out name type) Oracle declaration variable in the stored procedure is as follows: name type;  (before begin, create PROCEDURE () as a declaration of MySQL in the stored procedure declaration variable as follows: Declare name type; (declared in begin and end)

    Oracle has parameters with Sys_refcursor cursors in the stored procedure, so that MySQL cannot be used
    Oracle uses   This declaration parameter to refer to the field type of the other table, and MySQL is best to change it directly to that type.

---------------------------------------------------------------------------
    %rowtype This declaration of a result set of MySQL is not supported, resolved (two parts)
         1.  Create a temporary table
         2. is that it? The result set fields are individually declared as a separate variable.

Oracle is as follows:
Var_name Tablename%rowtype; --Declaration parameters
                   --Assigned value
Select O.* to TableName from Oldtablename as O where rownum=1

                   --Using
Tablename.a is directly the value of field a.

MySQL is as follows:
                  --Oralce declaration parameter changed to create temp table
CREATE temporary TABLE IF not EXISTS tablename (
SELECT *
From Oldtablename t
WHERE Tag_type=p_tag_type
ORDER by create_date desc, id desc
Limit 1
);
                  --The fields in the table have to create a variable
declare a int default-1;
declare b int;
                  --Assign value 
Select T.a to a from TableName as t where limit 1;
Select t.b to B from TableName as t where limit 1;
                 --Using
--Direct use of variable a B
--Need after use
                  --Clear temporary table
TRUNCATE TABLE tmptable;

---------------------------------------------------------------------------    

   Determines whether the character is empty can be directly length (character), <= 0 is empty

    stored procedures in Oracle can return
MySQL needs to get a tag .
As follows:
Begin changed to Label:begin when a label name is added
Return from Oracle to leave label; Can
    
    Assigning values to variables in Oracle stored procedures can be directly assigned var_name: = 10;
MySQL needs this modification set var_name: = 10; (MySQL must add set)
  
    The Oracle stored procedure return value needs to be added to the parameter status_rc out Sys_refcursor
And then
Open STATUS_RC for
Select ' -10000 ' as rscode, ' xxx cannot be null ' as rsmsg from dual;
Return
   MySQL Direct
Select ' -10000 ' as Rscode,
' The operation direction is not properly formatted (only ' + ' or '-') ' as Rsmsg
from dual;
Leave label;

   the exception handling in the MySQL stored procedure is as follows:

--When declaring variables after the exception handling begin, add the following

declare _err int default 0;

Declare continue handler for SqlException, sqlwarning, not found set _err=1;  

--end of code plus

If _err=1 Then
Rollback
Select ' -10000 ' as Rscode, ' XXXX exception! ' as rsmsg from dual;
Leave label;
End If;

Note values, some of which are not empty by default, but are null
To_char (sysdate, ' yyyy-mm-dd ')
    Instead: Date_format (now (), '%y-%m-%d ')
15. If the field type is varchar but this field value is a number, want to sort by this field, MySQL needs to find out +0 will be automatically converted to int type

Oracle Migrating MySQL Database note