Oracle转PostgreSQL

不念不忘少年蓝@ 2022-11-01 00:56 256阅读 0赞

最近在做一些Oracle SQL转PostgreSQL的工作，顺便记录这些改变，以便以后再转换有个参考。

<table> 
 <thead> 
  <tr> 
   <th>描述</th> 
   <th>Oracle</th> 
   <th>PostgreSQL</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>分页</td> 
   <td>select * from ( select z.*,rownum as num from( " + sql + " ) z ) where num&gt;=" + startRow + " and num&lt;=" + endRow</td> 
   <td>select z.* from ( " + sql + " ) z LIMIT " + pageSize + " OFFSET " + startRow</td> 
  </tr> 
  <tr> 
   <td>日期</td> 
   <td>① select sysdate from dual <br>② select t.* from test_user t where <code>sysdate &lt; t.end_date +1</code> <br> ③ select t.* from test_user t where <code>now() &lt; date_trunc('day',t.end_date) + interval '1d'</code><br>④ select * from test_user where <code>round(pwd_eff_date+pwd_eff_days-now())&gt;0</code></td> 
   <td>① select now(); <br>② select t.* from test_user t where <code>now() &lt; date_trunc('day',t.end_date) + interval '1d'</code> <br>③ select trunc(date_trunc(‘day’,now()) - interval ‘60d’) <br>④ select * from test_user where <code>(pwd_eff_date + (interval '1d' * pwd_eff_days))::date - now()::date &gt;0</code></td> 
  </tr> 
  <tr> 
   <td>DB</td> 
   <td>&lt;driverClassName&gt;oracle.jdbc.OracleDriver&lt;/driverClassName&gt; &lt;url&gt;jdbc:oracle:thin:@192.168.1.1:1521:testdb&lt;/url&gt;</td> 
   <td>&lt;driverClassName&gt;org.postgresql.Driver&lt;/driverClassName&gt; &lt;url&gt;jdbc:postgresql://192.168.1.1:5432/testdb&lt;/url&gt;</td> 
  </tr> 
  <tr> 
   <td>序列</td> 
   <td>test_seq_log.nextval</td> 
   <td>nextval(‘test_seq_log’)</td> 
  </tr> 
  <tr> 
   <td>别名</td> 
   <td>① update <code>test_user t</code> set t.test_username=‘123456’ <br>② select * from (select * from TEST_STAFF where staff_id = ? or employee_id = ?) where status=‘1’</td> 
   <td>① update <code>test_user</code> set test_username='123456’<br>② select * from (select * from TEST_STAFF where staff_id = ? or employee_id = ?) <code>result</code> where status=‘1’</td> 
  </tr> 
  <tr> 
   <td>Row</td> 
   <td>select t.*, t.<code>rowid</code> from test_user t</td> 
   <td>select t.*, t.<code>ctid</code> from test_user t</td> 
  </tr> 
  <tr> 
   <td>日期</td> 
   <td></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>nvl</td> 
   <td>select DISTINCT * from test_feature t where <code>nvl</code>(t.parent_id,0)=0</td> 
   <td>select DISTINCT <code>t.*</code> from test_feature t where <code>coalesce</code>(t.parent_id,0)=0</td> 
  </tr> 
  <tr> 
   <td>分组</td> 
   <td>SELECT COUNT(*) FROM ET_CHOICEITEM WHERE QUE_ID = 60421 ORDER BY ITEM_ID</td> 
   <td>SELECT COUNT(*) FROM ET_CHOICEITEM WHERE QUE_ID = 60421 ORDER BY ITEM_ID</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>select sc.*,s.*,c.*,r.* from test_staffcourse sc,test_staff s,test_course c,test_result r where sc.training_id=s.training_id and sc.course_id=c.course_id and <code>r.training_id(+)=sc.training_id and r.COURSE_ID(+)=sc.course_id</code></td> 
   <td>select sc.*,s.*,c.*,r.* from test_staffcourse sc join test_staff s on sc.training_id=s.training_id join test_course c on sc.course_id=c.course_id <code>left join test_result</code> r on r.training_id=sc.training_id and r.COURSE_ID=sc.course_id</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>connect by prior</td> 
   <td>select distinct * from TEST_FEATURE connect by prior PARENT_ID=FEATURE_ID start with FEATURE_ID in (11, 12) order by FEATURE_ID</td> 
   <td>select a.* from TEST_FEATURE a where a.FEATURE_ID in (11, 12) union all select distinct * from TEST_FEATURE b where b.FEATURE_ID in (select distinct c.parent_id from TEST_FEATURE c where c.FEATURE_ID in (11, 12)) order by FEATURE_ID</td> 
  </tr> 
  <tr> 
   <td>Decode</td> 
   <td>decode(sign(decode(sc.last_result,null,90000,0,9000,-1,90000,sc.last_result)-c.pass_mark),-1,0,1)=1)</td> 
   <td>case when sign(case when sc.last_result=null then 90000 when sc.last_result=0 then 9000 when sc.last_result=-1 then 90000 else sc.last_result end - c.pass_mark) = -1 then 0 else 1 end = 1)</td> 
  </tr> 
  <tr> 
   <td>Rownum</td> 
   <td>SELECT <code>ROWNUM</code>, empno, ename, job FROM emp WHERE ROWNUM &lt; 5 ORDER BY ename;</td> 
   <td>select <code>row_number()</code> over () as rownum, * from emp limit 10 offset 5;</td> 
  </tr> 
 </tbody> 
</table>

复制表结构：

create table test_user_backup_20210223 as
    ( select * from test_user)