Oracle向PostgreSQL移植实例

http://www.cnblogs.com/top5/archive/2009/11/06/1597142.html


1、数据移植
　　1.1、数据库初始化

　　以WINDOWS平台为例

　　标准版本PostgresQL 8.0.3

　　安装时LOCALE必须选择C，否则会产生大量编码问题!!!!

　　初始化数据库选择SQL_ASCII编码，因为8.0.3安装时不允许直接使用UNICODE初始化数据库

　　管理工具使用pgAdmin III，因为此工具会自动侦测数据库编码，默认使用UNICODE，只有在数据库使用SQL_ASCII时，才使用SQL_ASCII编码

　　1.2、建库

　　1.2.1、建立生产数据库时用UNICODE编码，建库时表空间置空(与ORACLE表空间概念不同)，所有者选择Postgres

　　1.2.2、建好库之后请建立schema，此用法与ORACLE相同

　　1.2.3、PostgresQL的权限管理较为严格，建好库后创建的所有对象如不特殊声明，都将建立在public schema下，请注意!!!!

　　1.2.4、在库中创建的所有对象所有者均为库的所有者(默认为Postgres)，如需变更，请手动处理

　　1.2.5、public schema下创建的视图、表、函数等，默认不允许其他用户访问，如有需要请在授权向导中添加public组的权限

　　1.3、建表

　　1.3.1、数据类型

　　Oracle数据类型||| PostgresQL数据类型

　　VARCHAR2||| VARCHAR

　　CLOB||| TEXT

　　DATE||| DATE/TIME/TIMESTAMP(DATE仅包含日期、TIME仅包含时间、TIMESTAMP均包含,通常使用DATE)

　　NUMBER||| SMALLINT/INTEGER/BIGINT/NUMERIC/REAL/DOUBLE PRECISION(通常可用NUMERIC)

　　BLOB||| BYTEA

　　×上表只包括常见数据类型中两者不同的部分

　　1.3.2、建表脚本

　　ORACLE脚本

　　CREATE TABLE SCHEMA.PREFIX_INFO
　　(
　　INFO_ID VARCHAR2(25 BYTE),
　　INFO_TITLE VARCHAR2(500 BYTE),
　　INFO_CONTENT CLOB,
　　INFO_DATE DATE DEFAULT sysdate,
　　INFO_STATUS VARCHAR2(1 BYTE) DEFAULT '1',
　　LANG_ID NUMBER DEFAULT 1,
　　INFO_CLICKNUM NUMBER DEFAULT 0,
　　IS_POP VARCHAR2(1 BYTE) DEFAULT '0',
　　INFO_VALIDDAYS NUMBER DEFAULT 1
　　)
　　LOGGING
　　NOCACHE
　　NOPARALLEL;
　　ALTER TABLE SCHEMA.PREFIX_INFO ADD (
　　CONSTRAINT PK_PREFIX_INFO PRIMARY KEY (INFO_ID));
　　CREATE SEQUENCE PREFIX_INFO_SEQUENCE
　　INCREMENT BY 1
　　START WITH 582
　　MINVALUE 1
　　MAXVALUE 9999999999999999999999999999
　　NOCYCLE
　　CACHE 20
　　NOORDER;

　　PostgresQL脚本

　　create table schema.prefix_info
　　(
　　info_id varchar(25),
　　info_title varchar(500),
　　info_content text,
　　info_date date default now(),
　　info_status varchar(1) default '1',
　　lang_id numeric default 1,
　　info_clicknum numeric default 0,
　　is_pop varchar2(1) default '0',
　　info_validdays numeric default 1
　　);
　　--PostgresQL中字段名称区分大小写，为保证兼容性，强烈建议脚本中的字符均用小写，这样在SQL语句中将忽略大小写
　　--PostgresQL中字段类型的括号中只能出现数字
　　--PostgresQL中sysdate应写为now()，用来取系统当前时间
　　alter table schema.prefix_info add constraint prefix_info_pkey primary key(info_id);
　　--注意增加约束时的写法，和ORACLE略有不同
　　CREATE SEQUENCE schema.prefix_info_sequence
　　increment 1
　　minvalue 1
　　maxvalue 9223372036854775807
　　start 582
　　cache 20;
　　--系统默认的最大值与ORACLE不同

　　1.3.3、数据移植

　　强烈建议使用TOAD中的SAVE AS工具，不要使用export table工具(时间格式有问题)，建好表后可无缝移植。

　　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

　　3、中文问题

　　不得不说PostgresQL非常牛X

　　其重要特性就是客户端/服务器的自动转码

　　通过此方法移植的数据库，可以在程序方面不作任何调整而自动适应

　　至少目前没发现问题，汗一个!!