oracle移植pg,postgresql和oracle互相移植

2.1、环境处理

2.1.1、注意事项

PostgresQL中的||用法与其他数据库不同：

select a||b from table1;

当a或b其中一个为null时，该查询返回null，切记切记！！！！！

2.2、兼容性设置

2.2.1、PostgresQL中没有concat函数，且由于||用法的问题，无法使用||替换，解决方法为

在public schema中创建函数concat

create or replace function concat(text,

text)

returns text as

$body$select coalesce($1,'') || coalesce($2,'')$body$

language 'sql' volatile;

alter function concat(text, text) owner to postgres;

--注意coalesce()的作用

--无需特殊授权即可在其他schema中使用

2.2.2、PostgresQL中没有dual虚拟表，为保证程序兼容性，可创建伪视图(view)替代：

CREATE OR REPLACE VIEW dual AS

SELECT NULL::"unknown"

WHERE 1 = 1;

ALTER TABLE dual OWNER TO postgres;

GRANT ALL ON TABLE dual TO postgres;

GRANT SELECT ON TABLE dual TO public;

--必须授权public以select权限

2.2.3、空字符串''插入数字列的问题

PostgresQL7.3版之后，不再允许空字符串''作为空值null直接插入数字类型的列中，所以插入数据时经常会出现

ERROR: invalid input syntax for integer: ""

之类的错误，而PostgresQL本身并未提供直接将空字符串自动转换为0或null的函数，这使得写应用程序时非常棘手。

比较标准的解决方法是：

a、在应用程序中控制(废话)

b、在要插入的表上做触发器，自动将''转换为0或null

c、自定义数据类型，在输入输出函数中自动将''转换为0或null

d、自定义函数，在应用程序中套在可能会产生''的字段外面

函数举例：

esin(enpty string is null)

CREATE OR REPLACE FUNCTION esin(text)

RETURNS int4 AS

$BODY$

SELECT CASE

WHEN $1='' THEN null

ELSE $1::integer

END;

$BODY$

LANGUAGE 'sql' VOLATILE;

ALTER FUNCTION esin(text) OWNER TO postgres;

e、最后一种方法是hack源码，重新编译PgSQL(代码可参见7.2版)

2.3、程序移植

2.3.1、concat函数：见2.1.2.1，程序中无需修改

2.3.2、外连接

移植方法：

ORACLE:

简单外连接：

SELECT COUNT(DISTINCT(A.COL1)) AS RCOUNT

FROM

SCHEMA.PREFIX_TABLE1 A,SCHEMA.PREFIX_TABLE2 B

WHERE 1 = 1

AND A.COL2 = B.COL2(+)

AND A.COL3 > 0

AND A.COL4 = '1'

超级变态外连接：

SELECT COUNT(DISTINCT(A.COL1)) AS RCOUNT

FROM

SCHEMA.PREFIX_TABLE1 A,SCHEMA.PREFIX_TABLE2 B,SCHEMA.PREFIX_TABLE3

C,SCHEMA.PREFIX_TABLE4 D

WHERE 1 = 1

AND A.COL2 = B.COL2

AND A.COL3 = C.COL3(+)

AND A.COL4 = D.COL4(+)

AND A.COL5 > 0

AND A.COL6 = '1'

POSTGRESQL:

简单外连接：

select count(distinct(a.col1)) as rcount

from

schema.prefix_table1 a left outer join schema.prefix_table2 b on

(a.col2 = b.col2)

where 1 = 1

and a.col3 > 0

and a.col4 = '1'

超级变态外连接：

select count(distinct(a.col1)) as rcount

from

schema.prefix_table1 a inner join schema.prefix_table2 b on (a.col2

= b.col2)

left outer join schema.prefix_table3 c on (a.col3 = c.col3)

left outer join schema.prefix_table4 d on (a.col4 = d.col4)

where 1 = 1

and a.col5 > 0

and a.col6 = '1'

2.3.3、子查询：

PostgresQL中子查询较为规范，子查询结果集必须拥有alias

移植方法：

ORACLE:

SELECT * FROM (

SELECT * FROM (

SELECT * FROM SCHEMA.PREFIX_TABLE ORDER BY COL1

) WHERE X=1 ORDER BY COL2

) WHERE Y=2 ORDER BY COL3

POSTGRESQL:

SELECT * FROM (

SELECT * FROM (

SELECT * FROM SCHEMA.PREFIX_TABLE ORDER BY COL1 ALIAS1

) WHERE X=1 ORDER BY COL2 ALIAS2

) WHERE Y=2 ORDER BY COL3

2.3.4、数据分页

PostgresQL中没有rownum，无法使用where rownum < =

X的方法进行分页，取而代之的是limit X，offset Y方法

而ORACLE中不允许使用LIMIT X的方法

移植方法：

×此移植无法做到两种数据库兼容

ORACLE:

SELECT * FROM ( SELECT * FROM (SELECT *

FROM SCHEMA.PREFIX_TABLE1 ORDER BY COL1 DESC,COL2 ASC) where ROWNUM

<= 50 ORDER BY

COL3 ASC,COL4 DESC)

WHERE ROWNUM <= 20 ORDER BY COL5 DESC,COL6 ASC;

POSTGRES:

select * from ( select * from (SELECT *

FROM SCHEMA.PREFIX_TABLE1 ORDER BY COL1 DESC,COL2 ASC) selb order

by col3 asc,col4

desc limit 50 ) sela

order by col5 desc,col6 asc limit 20;

--注意！！limit必须用于order

by之后！！！！！

--另，此方法仅为移植oracle分页示例，实际生产过程中没有必要使用oracle特有的三层嵌套方法

2.3.4、序列使用：

移植方法：

×此移植无法做到两种数据库兼容

ORACLE:SELECT SCHEMA.PREFIX_TABLE1_SEQUENCE.NEXTVAL AS nCode

FROM DUAL

POSTGRES:SELECT NEXTVAL('SCHEMA.PREFIX_TABLE1_SEQUENCE') AS nCode

FROM DUAL

--注意，此方法前提是dual视图已建立，如没有，可省略FROM

DUAL

2.3.5、JDBC调整

使用postgresql-8.0-312.jdbc3.jar

ORACLE:db.url=jdbc:oracle:thin:@192.168.0.1:1521:ORCL

POSTGRESQL:db.url=jdbc:postgresql://192.168.0.1:5432/database

3、中文问题