Comparison between MySQL and Oracle: Function 3: mysqloracle

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.