1.sysdate changed to Sysdate (), or Now ();
2.NVL (EXPR1,EXPR2) to Ifnull (EXPR1,EXPR2)
NVL2 (EXPR1,EXPR2,EXPR3) changed to IF (EXPR1,EXPR2,EXPR3);
3.to_date (' 19000101000000 ', ' Yyyymmddhh24miss ') changed to select Str_to_date (' 1900-01-01 00:00:00 ', '%y-%m-%d%H:%i:%s '), The wording is slightly different:
Date_format (date, '%y-%m-%d ')--------------To_char () in >oracle;
Str_to_date (date, '%y-%m-%d ')--------------to_date () in >oracle;
Description
%Y: Represents a 4-digit year
%y: Represents the year of 2
%m: Represents the month, in the form of (01 ... 12)
%c: Represents the month in the format of (1 ... 12)
%d: Represents the number of days in the month in the format (00 ...). 31)
%e: Represents the number of days in the month in the format (0 ...). 31)
%H: Represents the hour, in the form of (00 ... 23)
%k: Represents the hour, in the form of (0 ... 23)
%h: Represents the hour, in the form of (01 ... 12)
%I: Represents the hour, in the form of (01 ... 12)
%l: Represents the hour, in the form of (1 ... 12)
%i: Represents minutes, in the form of (00 ... 59)
%r: Represents the time, in the format of 12 hours (Hh:mm:ss [ap]m)
%T: Represents the time, in the format of 24 hours (HH:MM:SS)
%s: represents seconds in the format (00 ...). 59)
%s: represents seconds in the format (00 ...). 59)
4.Oracle the function to intercept the string is: substr (field name, start position, string length) starting position can start from 0, the interception results and starting from 1;
MySQL intercepts the string function as: substring (field name, start position, string length) starting position must start from 1, 0 can not get data;
5.MySql left join cannot be replaced by + sign;
6. Connection strings in Oracle | | , SQL Server uses +,mysql in Concat (' A ', ' B ', ' C ');
7.oracle:select INSTR (' sdsq ', ' s ', 2) value from dual (requires starting from position 2)
Mysql:select INSTR (' sdsq ', ' s ') value (starting from the default position of 1);
8.instr (' ABCDEFG ', ' ab ') changed to locate (' ab ', ' ABCDEFG '), name change, parameter position interchange;
9.length () changed to Char_length ();
10.to_number (' 123 ') changed to cast ("123" as signed integer), with signed integral type;
11.add_months (Sysdate, 2) changed to Date_add (Sysdate (), Interval 2 month);
12.MYSQL function: DATEDIFF (DATE1,DATE2) two date subtraction;
Oracle database compatible with MySQL differential notation