Changes between Initial Version and Version 1 of v6/pgsummary

Timestamp:

04/11/2012 04:35:54 PM (14 years ago)

Author:

chenchongqi

Comment:

--

v6/pgsummary

@@ +1 @@
+= Oracle 转 PG 汇总 =
+
+== 语法 ==
+* rownum 转为 limit 
+{{{
+oracle:
+select title from sns_pclady.msg_message where rownum<=100;
+
+pg:
+select title from sns_pclady.msg_message limit 100;
+}}}
+* 别名要显式指定
+{{{
+oracle:
+select count(*) from ( select * from 
+(select * from sns_pclady.msg_message where receiver= 6499175  and  delbyreceiver=0  order by posttime desc) where rownum <=1000) where status=0;
+
+pg:
+select count(*) from ( select * from 
+(select * from sns_pclady.msg_message where receiver= 6499175  and  delbyreceiver=0  order by posttime desc) a limit 1000) b where b.status=0;
+}}}
+* 外连接写法调整
+{{{
+oracle:
+select a.field1, b.field2 from a, b where a.item_id = b.item_id(+)
+
+pg:
+select a.field1, b.field2 from a left outer join b on a.item_id = b.item_id
+}}}
+* sequence写法调整
+{{{
+oracle:
+sequence_name.nextval
+
+pg:
+nextval('sequence_name') 
+}}}
+* sysdate写法调整
+{{{
+oracle:
+select sysdate from dual
+
+pg:
+select [db_sysdate] from dual
+}}}
+* decode写法调整
+{{{
+oracle:
+decode(expr, search, expr[, search, expr...] [, default])
+
+pg:
+CASE WHEN expr THEN expr [...] ELSE expr END
+CASE WHEN c1 = 1 THEN 'match' ELSE 'no match' END
+}}}
+* nvl写法调整
+{{{
+oracle:
+NVL(hire_date, SYSDATE)
+
+pg:
+coalesce(expr1, expr2, expr3,....)  //这个是否可以代替decode？
+}}}
+* connect by 写法pg不支持
+{{{
+oracle:
+pg:
+WITH RECURSIVE source (part_no) AS (
+SELECT 2
+UNION ALL
+SELECT part.part_no
+FROM source JOIN part ON (source.part_no = part.parent_part_no)
+)
+SELECT * FROM source;
+}}}
+
+== 驱动 ==
+* pg 里没有rollback 语句，不会自动rollback ，需要应用里触发异常。 
+* 缺省返回的整型是Long
+{{{
+oracle:
+BigDecimal count = (java.math.BigDecimal?)em.createNativeQuery("select count(*) from table").getSingleResult();
+
+pg:
+Long count = (Long)em.createNativeQuery("select count(*) from table").getSingleResult();
+}}}