Shulou(Shulou.com)05/31 Report--

This article will explain in detail the example analysis of the function of difference comparison between MySQL and Oracle. The editor thinks it is very practical, so I share it with you for reference. I hope you can get something after reading this article.

Function

Numbering Category ORACLEMYSQL Note 1 numeric function round (1.23456pj4) round (1.23456pd4) is the same:

ORACLE:select round (1.23456 and 4) value from dual

MYSQL:select round (1.23456) value2abs (- 1) abs (- 1) function: take the current data as an absolute value

Usage: oracle is the same as mysql

Mysql: select abs (- 1) value

Oracle: select abs (- 1) value from dual

3ceil (- 1.001) ceiling (- 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

4floor (- 1.001) floor (- 1.001) function: returns the maximum integer value not greater than X

Usage:

Mysql: select floor (- 1.001) value

Oracle: select floor (- 1.001) value from dual

5Max (expr) / Min (expr) Max (expr) / Min (expr) function: returns the minimum or maximum value of expr. MIN () and MAX () can accept a string argument; here

In this case, they will return the smallest or largest string.

Usage:

ROACLE: select max (user_int_key) from sd_usr

MYSQL: select max (user_int_key) from sd_usr;6 string function ascii (str) ascii (str) function: returns the ASCII code value of the leftmost character of the string str. If str is an empty string

Then the return value is 0. If str is a NULL, the return value is also NULL.

Usage:

Mysql:select ascii ('a') value

Oracle:select ascii ('a') value from dual

7CHAR (NMagna...) CHAR (NMagol...) Function: CHAR () interprets the parameter as an integer and returns the character given by the ASCII code value represented by the integer

A string made up of. The NULL value will be ignored.

Usage:

Mysql:select char (97) value

Oracle:select chr (97) value from dual

8REPLACE (str,from_str,to_str) REPLACE (str,from_str,to_str) function: all string from_str that appears in the string str is replaced by to_str, and then returns this string.

Usage:

Mysql: SELECT REPLACE ('abcdef',' bcd', 'ijklmn') value

Oracle: SELECT Replace ('abcdef',' bcd', 'ijklmn') value from dual

The number of 9INSTR ('sdsq','s',2) INSTR (' sdsq','s') parameters is different

ORACLE: select INSTR ('sdsq','s',2) value from dual (required to start at location 2)

MYSQL: select INSTR ('sdsq','s') value (starting at default location 1) 10SUBSTR (' abcd',2,2) substring ('abcd',2,2) function name is different:

ORACLE: select substr ('abcd',2,2) value from dual

MYSQL: select substring ('abcd',2,2) value11instr (' abcdefg','ab') locate ('ab','abcdefg') function name is different:

Instr-> locate (Note: the positions of the substring and the total string of locate should be interchanged)

ORACLE: SELECT instr ('abcdefg',' ab') VALUE FROM DUAL

MYSQL: SELECT locate ('ab',' abcdefg') VALUE12length (str) char_length () function name is different:

ORACEL: SELECT length ('AAAASDF') VALUE FROM DUAL

MYSQL: SELECT char_length ('AAAASDF') VALUE13REPLACE (' abcdef', 'bcd',' ijklmn') REPLACE ('abcdef',' bcd', 'ijklmn'):

ORACLE: SELECT REPLACE ('abcdef',' bcd', 'ijklmn') value from dual

MYSQL: SELECT REPLACE ('abcdef',' bcd', 'ijklmn') value14LPAD (' abcd',14,'0') LPAD ('abcd',14,' 0'):

ORACLE: select LPAD ('abcd',14,' 0') value from dual

MYSQL: select LPAD ('abcd',14,' 0') value from dual15UPPER (iv_user_id) UPPER (iv_user_id):

ORACLE: select UPPER (user_id) from sd_usr

MYSQL: like select UPPER (user_id) from sd_usr;16LOWER (iv_user_id) LOWER (iv_user_id):

ORACLE: select LOWER (user_id) from sd_usr

MYSQL: select LOWER (user_id) from sd_usr;17 control stream function nvl (u.email_address, 10) IFNULL (u.email_address, 10)

Or

The name of the ISNULL (u.email_address) function is different (selected according to the role):

ORACLE: select u.email_address, nvl (u.email_address, 10) value from sd_usr u (if u.email_address=NULl, replace its 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, 10 is shown in the result instead of replacing its value with 10 in DB)

Select u.email_address, ISNULL (u.email_address) value from sd_usr u (1 if u.email_address is NULL, otherwise 0) 18DECODE (iv_sr_status,g_sr_status_com, ld_sys_date, NULL) none, please use IF or CASE statement instead.

IF statement format: (expr1,expr2,expr3) description:

1. Decode (condition, value 1, translation value 1, value 2, translation value 2. Value n, translation value n, default)

This function has the following meaning:

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 where it is located

Depends on the context.

Usage:

Mysql: SELECT IF (1 > 2, 2 and 3)

19 Type conversion function TO_CHAR (SQLCODE) date_format/ time_format function name is different

SQL > select to_char (sysdate,'yyyy-mm-dd') from dual

SQL > select to_char (sysdate,'hh34-mi-ss') from dual

Mysql > select date_format (now (),'% Ymuri% mme% d')

Mysql > select time_format (now (),'% Hmurf% iMel% S'); 20to_date (str,format) STR_TO_DATE (str,format) function name is different:

ORACLE:SELECT to_date ('2009-3-6) VAULE FROM DUAL

MYSQL: the name of the SELECT STR_TO_DATE ('2004-03-01 as SIGNED,'% Ymuri% MMI% d') VAULE21trunc (- 1.002) cast (- 1.002 as SIGNED) function is different:

The date value truncated 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 the same as SQL Server, except that the type parameters are slightly different: CAST (xxx AS type), CONVERT (xxx, type), the type must use the following types:

Available types

Binary, same effect with binary prefix: BINARY

Character type, with parameter: CHAR ()

Date: DATE

Time: TIME

Date and time type: DATETIME

Floating point numbers: DECIMAL

Integer: SIGNED

Unsigned integers: UNSIGNED22TO_NUMBER (str) CAST ("123" AS SIGNED INTEGER) function name is different

ORACLE:SELECT TO_NUMBER ('123') AS VALUE FROM DUAL

MYSQL: SELECT CAST ("123" AS SIGNED INTEGER) as value

SIGNED INTEGER: the signed 23-day shaping function SYSDATEnow () / SYSDATE () is written differently:

ORACLE:select SYSDATE value from dual

MYSQL:select now () value

Select sysdate () value24Next_day (sysdate,7) customizes a function: the F_COMMON_NEXT_DAY (date,int) function has a different name:

ORACLE: SELECT Next_day (sysdate,7) value FROM DUAL

MYSQL: SELECT F_COMMON_NEXT_DAY (SYSDATE (), 3) value from DUAL

(3: refers to the index value of the week) the name of the specified 25ADD_MONTHS (sysdate, 2) DATE_ADD (sysdate (), interval 2 month) function returned next week is different:

ORACLE: SELECT ADD_MONTHS (sysdate, 2) as value from DUAL

MYSQL: SELECT DATE_ADD (sysdate (), interval 2 month) as value from DUAL;262 date subtraction (D1-D2) DATEDIFF (date1,date2) function: returns the number of days between two dates.

Usage:

Mysql: SELECT DATEDIFF ('2008-12-30') AS DiffDate

Oracle: subtract two dates directly (for example, d1-d2=12.3)

There is no corresponding function in the 27SQL function SQLCODEMYSQL, but SQLException in JAVA. The getErrorCode () function can get the error number. The Oracle built-in functions SQLCODE and SQLERRM are specifically used in OTHERS processors to return the error code and error message of Oracle, respectively.

MYSQL: you can get the error code from JAVA. There is no corresponding function in the error status and error message 28SQLERRMMYSQL, but there is SQLException in JAVA. The getMessage () function can get error messages. The Oracle built-in functions SQLCODE and SQLERRM are specifically used in OTHERS processors to return error codes and error messages for Oracle, respectively.

MYSQL: you can get error codes, error status and error messages from JAVA. 29SEQ_BK_DTL_OPT_INT_KEY.NEXTVAL auto-grow columns are auto-grow columns in MYSQL. Get the latest ID by using the following method:

START TRANSACTION

INSERT INTO user (username,password)

VALUES (username,MD5 (password))

SELECT LAST_INSERT_ID () INTO id

COMMIT;30SUM (enable_flag) SUM (enable_flag) is the same:

ORCALE: SELECT SUM (enable_flag) FROM SD_USR

MYSQL: SELECT SUM (enable_flag) FROM SD_USR;31DBMS_OUTPUT.PUT_LINE (SQLCODE) has no corresponding method in MYSQL, its function is to print in the console for testing, and has no effect on migration. Dbms_output.put_line can only display 255characters per line, more than it will report an error on the "MySQL and Oracle difference comparison function example analysis" this article is shared here, I hope the above content can be of some help to you, so that you can learn more knowledge, if you think the article is good, please share it out for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.