2、程序移植
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.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之后!!!!!
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