MySQL与Oracle 差异比较之七 其它
<p>其它</p>
<p>
</p>
<table border="0" cellpadding="0" cellspacing="0"><tbody>
<tr>
<td>
编号</td>
<td>
类别</td>
<td>
ORACLE</td>
<td>
MYSQL</td>
<td>
注释</td>
</tr>
<tr>
<td>
1</td>
<td>
内连接的更改</td>
<td>
<p>
1、<br>
select a.*, b.*, c.*, d.*<br>
from a, b, c, d<br>
where a.id = b.id<br>
and a.name is not null<br>
and a.id = c.id(+)<br>
and a.id = d.id(+)<br>
"(+)"所在位置的另一侧为连接的方向，<br>
所以上面的例子1是左连接。<br>
以下的例子2既是右连接。</p>
<p>
2、<br>
select a.*, b.*, c.*, d.*<br>
from a, b, c, d<br>
where a.id = b.id<br>
and a.name is not null<br>
and a.id(+) = c.id</p>
</td>
<td>
方法一<br>
select a.*, c.*, d.*<br>
from a<br>
left join(c, d) <br>
on (a.id = c.id and a.id = d.id), b<br>
where a.id = b.id<br>
and a.name is not null<br>
方法二<br>
select a.*, c.*, d.*<br>
from a<br>
left join c on a.id = c.id<br>
left join d on a.id = d.id, b<br>
where a.id = b.id<br>
and a.name is not null</td>
<td>
oracle sql语句和mysql sql语句有一定的区别. <br>
1. oracle左连接,右连接可以使用(+)来实现. <br>
Mysql只能使用left join ,right join等关键字. </td>
</tr>
<tr>
<td>
2</td>
<td>
最后一句执行的sql statement所取得或<br>
影响的条数</td>
<td>
SQL%ROWCOUNT</td>
<td>
执行select语句后用: FOUND_ROWS()<br>
执行update delete insert语句后用: <br>
ROW_COUNT(). </td>
<td>
oracle中: <br>
sql 表示最后一句执行的 SQL Statement, rowcount表示該 SQL 所取得或影响的条数.<br>
Mysql中: <br>
执行select语句后查询所影响的条数用: FOUND_ROWS()<br>
执行update delete insert语句后查询所影响的条数用: ROW_COUNT()</td>
</tr>
<tr>
<td>
3</td>
<td>
查询分页</td>
<td>
SELECT t1.*<br>
FROM <br>
(SELECT MSG_INT_KEY, <br>
MSG_TY, <br>
MSG_CD, <br>
ROWNUM ROW_NUM<br>
FROM SD_SYS_MSG<br>
WHERE (ii_msg_int_key IS NULL <br>
OR msg_int_key = ii_msg_int_key)<br>
ORDER BY MSG_CD<br>
) t1<br>
WHERE (in_page_no IS NULL)<br>
OR (t1.ROW_NUM ><br>
((in_page_no -1)*li_per_page_amt) <br>
AND t1.ROW_NUM <<br>
(in_page_no*li_per_page_amt + 1)<br>
);</td>
<td>
方法:使用循环变量替换oracle中ROWNUM<br>
set @mycnt = 0;<br>
SELECT (@mycnt := @mycnt + 1) as ROW_NUM,t1.*<br>
FROM<br>
(SELECT MSG_INT_KEY, MSG_TY, MSG_CD, ROWNUM ROW_NUM<br>
FROM SD_SYS_MSG<br>
WHERE (ii_msg_int_key IS NULL OR msg_int_key = ii_msg_int_key )<br>
ORDER BY MSG_CD<br>
) t1<br>
WHERE (in_page_no IS NULL)<br>
OR (t1.ROW_NUM> ((in_page_no - 1) * li_per_page_amt)<br>
AND t1.ROW_NUM < (in_page_no * li_per_page_amt + 1)<br>
);</td>
<td>
</td>
</tr>
<tr>
<td>
4</td>
<td>
java null值</td>
<td>
""作为参数传入后,在oracle中将识别为null</td>
<td>
""作为参数据传mysql还是""</td>
<td>
现在java代码需要修改:<br>
inPara.add(MSG_TY.equals("") ? null : MSG_TY);</td>
</tr>
<tr>
<td>
5</td>
<td>
执行动态sql</td>
<td>
lv_sql := 'SELECT ' ||' distinct ' || iv_cd_field_name || ' FIELD1 '||<br>
' FROM ' || iv_table_name ||<br>
' WHERE ' || NVL(iv_where_cause,' 1=1 ');<br><br>
OPEN l_sys_cur FOR lv_sql;</td>
<td>
set @a = iv_cd_field_name;<br>
set @b = iv_table_name;<br>
set @c = IFNULL(iv_where_cause,' 1=1 ');<br><br>
SET @s = concat('SELECT distinct ', @a , ' FIELD1 FROM ' , @b ,<br>
' WHERE ' , IFNULL(@c,' 1=1 '));<br><br>
PREPARE stmt3 FROM @s;<br>
EXECUTE stmt3;<br>
<br>
DEALLOCATE PREPARE stmt3;</td>
<td>
1. oracle可以将动态sql放在游标中执行. <br>
mysql游标声明有一定的局限性: mysql游标必须在声明处理程序之前被声明，并且变量和条件必须在声明光标或处理程序之前被声明。Mysql采用Prepared Statements实现动态sql. 例子如下:<br>
INT Emp_id_var = 56<br>
PREPARE SQLSA<br>
FROM "DELETE FROM employee WHERE emp_id=?" ;<br>
EXECUTE SQLSA USING :Emp_id_var ;<br>
</td>
</tr>
<tr>
<td>
6</td>
<td>
存储过程相互调用时传递数组</td>
<td>
<p>
oracle使用数组步骤: <br>
1. 将传入的字符串通过P_UNPACK_LIST方法转换为数组.(lo_holiday_jan_upl即为数组)<br>
P_UNPACK_LIST(iv_jan__str, lv_delimiter, lo_holiday_jan_upl);<br>
2. 传数组到另一个存储过程.<br>
P_MOD_MONTH(iv_year, 1, lo_holiday_jan_upl, iv_user_cd);<br>
3. P_MOD_MONTH中使用数组: (将数组中的各个元素取出来插入到SD_HOLIDAY表)<br>
FOR li_cnt IN 0 .. 9 LOOP<br>
IF iv_daystr(li_cnt) IS NOT NULL THEN<br>
INSERT INTO SD_HOLIDAY<br>
(HOLIDAY_INT_KEY,<br>
YEAR,<br>
MONTH,<br>
DAY,<br>
ENABLE_FLAG,<br>
CREATE_BY,<br>
CREATE_DATE,<br>
LAST_UPD_BY,<br>
LAST_UPD_DATE)<br>
VALUES<br>
(SEQ_HOLIDAY_INT_KEY.NEXTVAL,<br>
iv_year,<br>
iv_month,<br>
iv_daystr(li_cnt),<br>
1,<br>
iv_user_cd,<br>
ld_sys_date,<br>
iv_user_cd,<br>
ld_sys_date);<br>
END IF;</p>
<p>
END LOOP;</p>
</td>
<td>
mysql中数用数组步骤: <br>
1. 将需要处理的字符串交给执行业务逻辑<br>
的存储过程处理. <br>
CALL SD_HOLIDAY_P_MOD_MONTH(iv_year, 1, iv_jan__str, iv_user_cd);<br>
2. SD_HOLIDAY_P_MOD_MONTH中处理字符串. (将字符串按自定格式分隔出来,在对每个小字符串进行业务逻辑处理.)<br>
SET lv_inputstr = iv_inputstr;<br>
loopLable:LOOP<br>
IF li_cnt > 9 THEN<br>
LEAVE looplable;<br>
ELSE<br>
SET li_pos = INSTR(lv_inputstr, iv_delimiter);<br>
IF li_pos = 0 THEN<br>
leave looplable;<br>
ELSE<br>
set temp_str = SUBSTR(lv_inputstr, 1, li_pos - 1);<br>
/*插入temp_str到SD_HOLIDAY表*/<br>
INSERT INTO SD_HOLIDAY(...)<br>
SET lv_inputstr = SUBSTRING(lv_inputstr, li_pos + LENGTH(iv_delimiter)); <br>
END IF; <br>
SET li_cnt = li_cnt+1;<br>
END IF;<br>
END LOOP loopLable;</td>
<td>
存储过程相互调用时传递数组解决方法: <br>
oracle中传入12个字符串到存储过程,然后将这12个字符串转换为12个数组,再调用其他存储过程并将这12个数组分别传给存储过程,便利每个数组进行业务逻辑处理. <br>
mysql解决方法: 将存储过程中的数组去掉,两个存储过程调用时直接传递字符串,然后再需要处理业务逻辑的地方将字符串分解,进行业务逻辑处理. <br>
<br>
可以参考<<2009002-OTMPPS-Difficult Questions-0001.doc>> 中 2.4.2 逐层分解字符串</td>
</tr>
<tr>
<td>
7</td>
<td>
Java无法以String来接取int</td>
<td>
select fac_unit_key FILED1在oracle可以</td>
<td>
select fac_unit_key FILED1在mysql中要改<br>
select CAST(fac_unit_key AS CHAR) FILED1</td>
<td>
CAST(intvalue AS CHAR)</td>
</tr>
</tbody></table>
頁:
[1]