Comparison between MySQL and Oracle: Function 3: mysqloracle

Source: Internet
Author: User

Comparison between MySQL and Oracle: Function 3: mysqloracle

Function

No. Category ORACLE MYSQL Note
1 Numeric Functions Round (1.23456, 4) Round (1.23456, 4) Same:
ORACLE: select round (1.23456, 4) value from dual
MYSQL: select round (1.23456, 4) value
2 Abs (-1) Abs (-1) Function: obtains the absolute value of the current data.
Usage: the usage of oracle and mysql is the same.
Mysql: select abs (-1) value
Oracle: select abs (-1) value from dual
3 Ceil (-1.001 )) Ceiling (-1, 1.001) Function: returns the smallest integer not less than X.
Usage:
Mysqls: select ceiling (-1.001) value
Oracle: select ceil (-1.001) value from dual
4 Floor (-1, 1.001) Floor (-1, 1.001) Function: returns the maximum integer not greater than X.
Usage:
Mysql: select floor (-1.001) value
Oracle: select floor (-1.001) value from dual

5 Max (expr)/Min (expr) Max (expr)/Min (expr) Function: returns the minimum or maximum value of expr. MIN () and MAX () can accept a string parameter;
In this case, they will return the smallest or largest string to pass down.
Usage:
ROACLE: select max (user_int_key) from sd_usr;
MYSQL: select max (user_int_key) from sd_usr;
6 String Functions Ascii (str) Ascii (str) Function: returns the ASCII value of the leftmost character of the str string. If str is an empty string,
The return value is 0. If str is NULL, the return value is NULL.
Usage:
Mysql: select ascii ('A') value
Oracle: select ascii ('A') value from dual
7 CHAR (N ,...) CHAR (N ,...) Function: CHAR () is interpreted as an integer and returns the characters of the ASCII value represented by this integer.
String. The NULL value is ignored.
Usage:
Mysql: select char (97) value
Oracle: select chr (97) value from dual

8 REPLACE (str, from_str, to_str) REPLACE (str, from_str, to_str) Function: replace all the from_str strings in the str string with to_str, and return this string.
Usage:
Mysql: select replace ('abcdef', 'bcd', 'ijklmn ') value
Oracle: SELECT Replace ('abcdef', 'bcd', 'ijklmn ') value from dual


9 INSTR ('sdsq', 's', 2) INSTR ('sdsq','s ') Different number of parameters
ORACLE: select INSTR ('sdsq ','s', 2) value from dual (starting from location 2)
MYSQL: select INSTR ('sdsq', 's') value (starting from 1 by default)
10 SUBSTR ('abc', 2, 2) Substring ('abcd', 2, 2) Function names are different:
ORACLE: select substr ('abc', 2, 2) value from dual
MYSQL: select substring ('abcd',) value
11 Instr ('abcdefg', 'AB ') Locate ('AB', 'abcdefg ') Function names are different:
Instr-> locate (note: the positions of the sub-string and the total string of the locate must be exchanged)
ORACLE: SELECT instr ('abcdef', 'AB') VALUE FROM DUAL
MYSQL: SELECT locate ('AB', 'abcdefg') VALUE
12 Length (str) Char_length () Function names are different:
Else El: SELECT length ('aaaasdf ') VALUE FROM DUAL
MYSQL: SELECT char_length ('aaaasdf ') VALUE
13 REPLACE ('abcdef', 'bcd', 'ijklmn ') REPLACE ('abcdef', 'bcd', 'ijklmn ') Same:
ORACLE: select replace ('abcdef', 'bcd', 'ijklmn ') value from dual
MYSQL: select replace ('abcdef', 'bcd', 'ijklmn ') value
14 LPAD ('abc', 14, '0 ') LPAD ('abc', 14, '0 ') Same:
ORACLE: select LPAD ('abc', 14, '0') value from dual
MYSQL: select LPAD ('abc', 14, '0') value from dual
15 UPPER (iv_user_id) UPPER (iv_user_id) Same:
ORACLE: select UPPER (user_id) from sd_usr;
MYSQL: select UPPER (user_id) from sd_usr;
16 LOWER (iv_user_id) LOWER (iv_user_id) Same:
ORACLE: select LOWER (user_id) from sd_usr;
MYSQL: select LOWER (user_id) from sd_usr;
17 Control Flow Functions Nvl (u. email_address, 10) IFNULL (u. email_address, 10)
Or
ISNULL (u. email_address)
Function names are different (based on different functions ):
ORACLE: select u. email_address, nvl (u. email_address, 10) value from sd_usr u (if u. email_address = NULl, replace the value with 10 in DB)
MYSQL: select u. email_address, IFNULL (u. email_address, 10) value from sd_usr u (if u. email_address = NULl. The value in the result is 10 instead of 10 in the DB)
Select u. email_address, ISNULL (u. email_address) value from sd_usr u (if u. email_address is NULL, 1 <true> is displayed; otherwise, 0 <false> is displayed)
18 DECODE (iv_sr_status, g_sr_status_com, ld_sys_date, NULL) None. Use the IF or CASE statement instead.
IF statement format: (expr1, expr2, expr3)
Note:
1. decode (condition, value 1, translation value 1, value 2, translation value 2,... value n, translation value n, default value)
The function has the following meanings:
IF condition = value 1 THEN
RETURN (translation value 1)
ELSIF condition = value 2 THEN
RETURN (translation value 2)
......
ELSIF condition = value n THEN
RETURN (translation value n)
ELSE
RETURN (default)
END IF

2. mysql If syntax description
Function: IF expr1 is TRUE (expr1 <> 0 and expr1 <> NULL), the return value of IF () is expr2;
Otherwise, the return value is expr3. The return value of IF () is a numeric value or a string value, depending on its location
Context.
Usage:
Mysql: select if (1> 2, 2, 3 );
19 Type conversion functions TO_CHAR (SQLCODE) Date_format/time_format Different function names
SQL> select to_char (sysdate, 'yyyy-mm-dd') from dual;
SQL> select to_char (sysdate, 'hh24-mi-ss') from dual;
Mysql> select date_format (now (), '% Y-% m-% D ');
Mysql> select time_format (now (), '% H-% I-% s ');
20 To_date (str, format) STR_TO_DATE (str, format) Function names are different:
ORACLE: SELECT to_date ('2017-3-6 ', 'yyyy-mm-dd') VAULE FROM DUAL
MYSQL: SELECT STR_TO_DATE ('2017-03-01 ',' % Y-% m-% D') VAULE
21 Trunc (-1, 1.002) Cast (-1.002 as SIGNED) Function names are different:
The date value intercepted by the TRUNC function for the specified element.
ORACLE: select trunc (-1.002) value from dual
MYSQL: select cast (-1.002 as SIGNED) value
MYSQL:
Character Set conversion: CONVERT (xxx USING gb2312)
Type conversion is similar to SQL Server, that is, the type parameters are a little different: CAST (xxx AS type), CONVERT (xxx, type), The type must use the following types:

Available types
Binary, with a BINARY Prefix: binary
Character type, with parameters: CHAR ()
DATE: DATE
TIME: TIME
DATETIME type
Floating Point: DECIMAL
Integer: SIGNED
UNSIGNED integer
22 TO_NUMBER (str) CAST ("123" as signed integer) Different function names
ORACLE: SELECT TO_NUMBER ('123') as value from dual;
MYSQL: select cast ("123" as signed integer) as value;
Signed integer: SIGNED INTEGER
23 Date Functions SYSDATE Now ()/SYSDATE () Different writing methods:
ORACLE: select SYSDATE value from dual
MYSQL: select now () value
Select sysdate () value
24 Next_day (sysdate, 7) Customize a function: F_COMMON_NEXT_DAY (date, int) Function names are different:
ORACLE: SELECT Next_day (sysdate, 7) value FROM DUAL
MYSQL: SELECT F_COMMON_NEXT_DAY (SYSDATE (), 3) value from DUAL;
(3: index value of the week) the specified return date following the date of the next week
25 ADD_MONTHS (sysdate, 2) DATE_ADD (sysdate (), interval 2 month) Function names are different:
ORACLE: SELECT ADD_MONTHS (sysdate, 2) as value from DUAL;
MYSQL: SELECT DATE_ADD (sysdate (), interval 2 month) as value from DUAL;
26 2 Date subtraction (D1-D2) DATEDIFF (date1, date2) Function: returns the number of days between two dates.
Usage:
Mysql: select datediff ('2017-12-30 ', '2017-12-29') AS DiffDate
Oracle: Subtract directly with two dates (for example, d1-d2 = 12.3)
27 SQL Functions SQLCODE MYSQL does not have the corresponding function, but SQLException in JAVA. The getErrorCode () function can obtain error numbers. The built-in Oracle functions SQLCODE and SQLERRM are especially used in the OTHERS processor to return the Oracle error code and error messages respectively.
MYSQL: You can get error codes, error statuses, and error messages from JAVA.
28 SQLERRM MYSQL does not have the corresponding function, but SQLException in JAVA. The getMessage () function can obtain error messages. The built-in Oracle functions SQLCODE and SQLERRM are especially used in the OTHERS processor to return the Oracle error code and error messages respectively.
MYSQL: You can get error codes, error statuses, and error messages from JAVA.
29 SEQ_BK_DTL_OPT_INT_KEY.NEXTVAL Auto-increment Column Auto-increment columns in MYSQL. obtain the latest ID using the following method:
Start transaction;
Insert into user (username, password)
VALUES (username, MD5 (password ));
SELECT LAST_INSERT_ID () INTO id;
COMMIT;
30 SUM (enable_flag) SUM (enable_flag) Same:
ORCALE: select sum (enable_flag) FROM SD_USR;
MYSQL: select sum (enable_flag) FROM SD_USR;
31 DBMS_OUTPUT.PUT_LINE (SQLCODE) There is no corresponding method in MYSQL. It is printed on the console for testing and has no impact on migration. Dbms_output.put_line can only display 255 characters per line. If it exceeds the limit, an error is returned.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.