Seven other differences between MySQL and Oracle: mysqloracle

Seven other differences between MySQL and Oracle: mysqloracle

Others

No.	Category	ORACLE	MYSQL	Note
1	Internal Connection changes	1, Select a. *, B. *, c. *, d .* From a, B, c, d Where a. id = B. id And a. name is not null And a. id = c. id (+) And a. id = d. id (+) The other side of "(+)" is the connection direction, Therefore, Example 1 is left join. Example 2 is a right join. 2, Select a. *, B. *, c. *, d .* From a, B, c, d Where a. id = B. id And a. name is not null And a. id (+) = c. id	Method 1 Select a. *, c. *, d .* From Left join (c, d) On (a. id = c. id and a. id = d. id), B Where a. id = B. id And a. name is not null Method 2 Select a. *, c. *, d .* From Left join c on a. id = c. id Left join d on a. id = d. id, B Where a. id = B. id And a. name is not null	There are some differences between oracle SQL statements and mysql SQL statements. 1. oracle left join, right join can be implemented using (+. Mysql can only use left join, right join, and other keywords.
2	The obtained or Number of affected items	SQL % ROWCOUNT	Run the select statement: FOUND_ROWS () Execute the update delete insert statement and use: ROW_COUNT ().	Oracle: SQL indicates the SQL Statement executed in the last Statement, and rowcount indicates the number of acquired or affected SQL statements. Mysql: After the select statement is executed, the number of affected items is queried using: FOUND_ROWS () Use ROW_COUNT () to query the number of affected items after executing the update delete insert statement ()
3	Query page	SELECT t1 .* FROM (SELECT MSG_INT_KEY, MSG_TY, MSG_CD, ROWNUM ROW_NUM FROM SD_SYS_MSG WHERE (ii_msg_int_key IS NULL OR msg_int_key = ii_msg_int_key) Order by MSG_CD ) T1 WHERE (in_page_no is null) OR (t1.ROW _ NUM> (In_page_no-1) * li_per_page_amt) AND t1.ROW _ NUM < (In_page_no * li_per_page_amt + 1) );	Method: replace ROWNUM in oracle with cyclic variables. Set @ mycnt = 0; SELECT (@ mycnt: = @ mycnt + 1) as ROW_NUM, t1 .* FROM (SELECT MSG_INT_KEY, MSG_TY, MSG_CD, ROWNUM ROW_NUM FROM SD_SYS_MSG WHERE (ii_msg_int_key is null or msg_int_key = ii_msg_int_key) Order by MSG_CD ) T1 WHERE (in_page_no is null) OR (t1.ROW _ NUM> (in_page_no-1) * li_per_page_amt) AND t1.ROW _ NUM <(in_page_no * li_per_page_amt + 1) );
4	Java null Value	"" Will be recognized as null in oracle after being passed in as a parameter	"" Mysql or "as a parameter ""	Now the java code needs to be modified: InPara. add (MSG_TY.equals ("")? Null: MSG_TY );
5	Execute dynamic SQL	Lv_ SQL: = 'select' | 'distinct '| iv_cd_field_name | 'field1' | 'From' | iv_table_name | 'Where' | NVL (iv_where_cause, '1 = 1 '); OPEN l_sys_cur FOR lv_ SQL;	Set @ a = iv_cd_field_name; Set @ B = iv_table_name; Set @ c = IFNULL (iv_where_cause, '1 = 1 '); SET @ s = concat ('select distinct ', @ a, 'field1 from', @ B, 'Where', IFNULL (@ c, '1 = 1 ')); PREPARE stmt3 FROM @ s; EXECUTE stmt3; Deallocate prepare stmt3;	1. oracle can place dynamic SQL statements in the cursor for execution. Mysql cursor Declaration has some limitations: mysql cursor must be declared before the handler is declared, and variables and conditions must be declared before the cursor or handler is declared. Mysql uses Prepared Statements to implement dynamic SQL. The example is as follows: INT Emp_id_var = 56 PREPARE SQLSA FROM "delete from employee WHERE emp_id =? "; Execute sqlsa using: Emp_id_var;
6	Transfer an array when the stored procedure calls each other	Steps for using arrays in oracle: 1. Convert the input string to an array using the P_UNPACK_LIST method. (lo_holiday_jan_upl is an array) P_UNPACK_LIST (iv_jan _ str, lv_delimiter, lo_holiday_jan_upl ); 2. upload an array to another stored procedure. P_MOD_MONTH (iv_year, 1, lo_holiday_jan_upl, iv_user_cd ); 3. Use an array in P_MOD_MONTH: (obtain the elements in the array and insert them to the SD_HOLIDAY table) FOR li_cnt IN 0 .. 9 LOOP IF iv_daystr (li_cnt) IS NOT NULL THEN Insert into SD_HOLIDAY (HOLIDAY_INT_KEY, YEAR, MONTH, DAY, ENABLE_FLAG, CREATE_BY, CREATE_DATE, LAST_UPD_BY, LAST_UPD_DATE) VALUES (SEQ_HOLIDAY_INT_KEY.NEXTVAL, Iv_year, Iv_month, Iv_daystr (li_cnt ), 1, Iv_user_cd, Ld_sys_date, Iv_user_cd, Ld_sys_date ); End if; End loop;	Steps for using arrays in mysql: 1. Submit the string to be processed to execute the business logic Stored Procedure processing. CALL SD_HOLIDAY_P_MOD_MONTH (iv_year, 1, iv_jan _ str, iv_user_cd ); 2. Process strings in SD_HOLIDAY_P_MOD_MONTH. (separate strings in custom format and process the business logic of each small string .) SET lv_inputstr = iv_inputstr; LoopLable: LOOP IF li_cnt> 9 THEN LEAVE looplable; ELSE SET li_pos = INSTR (lv_inputstr, iv_delimiter ); IF li_pos = 0 THEN Leave looplable; ELSE Set temp_str = SUBSTR (lv_inputstr, 1, li_pos-1 ); /* Insert temp_str to the SD_HOLIDAY table */ Insert into SD_HOLIDAY (...) SET lv_inputstr = SUBSTRING (lv_inputstr, li_pos + LENGTH (iv_delimiter )); End if; SET li_cnt = li_cnt + 1; End if; End loop loopLable;	How to pass an array when the stored procedure calls each other: Input 12 strings to the stored procedure in oracle, convert these 12 strings into 12 arrays, call other stored procedures, and pass these 12 arrays to the stored procedure respectively, facilitates the processing of business logic for each array. Mysql solution: remove the array in the stored procedure. when calling the two stored procedure, directly transfer the string, and then break down the string where the business logic is processed for business logic processing. See <2009002-OTMPPS-Difficult Questions-0001.doc> 2.4.2 layer-by-layer string Decomposition
7	Java cannot use String to get int	Select fac_unit_key FILED1 can be used in oracle	Select fac_unit_key FILED1 to be modified in mysql Select CAST (fac_unit_key as char) FILED1	CAST (intvalue as char)