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

This article mainly shows you "what are the differences between MySQL and Oracle". The content is simple and clear. I hope it can help you solve your doubts. Let the editor lead you to study and learn the article "what are the differences between MySQL and Oracle".

Other

Changes to connections in numbered category ORACLEMYSQL comment 1

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 the position of "(+)" is the direction of the connection.

So example 1 above is a left connection.

Example 2 below is both a right connection.

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 one

Select a. Floor, c. Stories, d.*

From a

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 two

Select a. Floor, c. Stories, d.*

From a

Left join c on a.id = c.id

Left join d on a.id = d.id, b

Where a.id = b.id

There are some differences between and a.name is not nulloracle sql statement and mysql sql statement.

1. Oracle left connection, right connection can be achieved using (+).

Mysql can only use the keywords left join, right join, etc. 2 obtained by the sql statement executed in the last sentence or

The number of affected SQL%ROWCOUNT is followed by: FOUND_ROWS () after executing the select statement

After executing the update delete insert statement, use:

In ROW_COUNT (). Oracle:

Sql indicates the SQL Statement executed in the last sentence, and rowcount indicates the number of entries obtained or affected by the SQL.

In Mysql:

The number of entries affected by the query after the execution of the select statement uses: FOUND_ROWS ()

The number of entries affected by the query after the execution of the update delete insert statement: ROW_COUNT () 3 query paging 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) );方法:使用循环变量替换oracle中ROWNUM 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) );　4java null值""作为参数传入后,在oracle中将识别为null""作为参数据传mysql还是""现在java代码需要修改: inPara.add(MSG_TY.equals("") ? null : MSG_TY);5执行动态sqllv_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可以将动态sql放在游标中执行. mysql游标声明有一定的局限性: mysql游标必须在声明处理程序之前被声明，并且变量和条件必须在声明光标或处理程序之前被声明。Mysql采用Prepared Statements实现动态sql. 例子如下: INT Emp_id_var = 56 PREPARE SQLSA FROM "DELETE FROM employee WHERE emp_id=?" ; EXECUTE SQLSA USING :Emp_id_var ; 6存储过程相互调用时传递数组 oracle使用数组步骤: 1. 将传入的字符串通过P_UNPACK_LIST方法转换为数组.(lo_holiday_jan_upl即为数组) P_UNPACK_LIST(iv_jan__str, lv_delimiter, lo_holiday_jan_upl); 2. 传数组到另一个存储过程. P_MOD_MONTH(iv_year, 1, lo_holiday_jan_upl, iv_user_cd); 3. P_MOD_MONTH中使用数组: (将数组中的各个元素取出来插入到SD_HOLIDAY表) 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; mysql中数用数组步骤: 1. 将需要处理的字符串交给执行业务逻辑 的存储过程处理. CALL SD_HOLIDAY_P_MOD_MONTH(iv_year, 1, iv_jan__str, iv_user_cd); 2. SD_HOLIDAY_P_MOD_MONTH中处理字符串. (将字符串按自定格式分隔出来,在对每个小字符串进行业务逻辑处理.) 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 into 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

Pass an array solution when END LOOP loopLable; stored procedures call each other:

12 strings are passed into the stored procedure in oracle, then the 12 strings are converted into 12 arrays, and then other stored procedures are called and the 12 arrays are passed to the stored procedure respectively, which facilitates the business logic processing of each array.

Mysql solution: remove the array in the stored procedure, transfer the string directly when the two stored procedures are called, and then decompose the string where you need to deal with the business logic for business logic processing.

You can refer to 2.4.2 in which the string is decomposed layer by layer. 7Java cannot be used to pick up intselect fac_unit_key FILED1 in String. In oracle, select fac_unit_key FILED1 can be changed in mysql.

Select CAST (fac_unit_key AS CHAR) FILED1CAST (intvalue AS CHAR) above are all the contents of this article entitled "what are the differences between MySQL and Oracle". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.