MyException - 我的异常网
当前位置:我的异常网» 操作系统 » Oracle向PostgreSQL移栽实例

Oracle向PostgreSQL移栽实例

www.MyException.Cn  网友分享于:2015-08-26  浏览:5次
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

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

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

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

文章评论

不懂技术不要对懂技术的人说这很容易实现
不懂技术不要对懂技术的人说这很容易实现
团队中“技术大拿”并非越多越好
团队中“技术大拿”并非越多越好
如何区分一个程序员是“老手“还是“新手“?
如何区分一个程序员是“老手“还是“新手“?
程序员周末都喜欢做什么?
程序员周末都喜欢做什么?
2013年美国开发者薪资调查报告
2013年美国开发者薪资调查报告
漫画:程序员的工作
漫画:程序员的工作
十大编程算法助程序员走上高手之路
十大编程算法助程序员走上高手之路
“肮脏的”IT工作排行榜
“肮脏的”IT工作排行榜
程序猿的崛起——Growth Hacker
程序猿的崛起——Growth Hacker
编程语言是女人
编程语言是女人
鲜为人知的编程真相
鲜为人知的编程真相
 程序员的样子
程序员的样子
初级 vs 高级开发者 哪个性价比更高?
初级 vs 高级开发者 哪个性价比更高?
10个调试和排错的小建议
10个调试和排错的小建议
每天工作4小时的程序员
每天工作4小时的程序员
2013年中国软件开发者薪资调查报告
2013年中国软件开发者薪资调查报告
旅行,写作,编程
旅行,写作,编程
老程序员的下场
老程序员的下场
Java程序员必看电影
Java程序员必看电影
如何成为一名黑客
如何成为一名黑客
Java 与 .NET 的平台发展之争
Java 与 .NET 的平台发展之争
为什么程序员都是夜猫子
为什么程序员都是夜猫子
程序员最害怕的5件事 你中招了吗?
程序员最害怕的5件事 你中招了吗?
什么才是优秀的用户界面设计
什么才是优秀的用户界面设计
做程序猿的老婆应该注意的一些事情
做程序猿的老婆应该注意的一些事情
我的丈夫是个程序员
我的丈夫是个程序员
总结2014中国互联网十大段子
总结2014中国互联网十大段子
我跳槽是因为他们的显示器更大
我跳槽是因为他们的显示器更大
为啥Android手机总会越用越慢?
为啥Android手机总会越用越慢?
程序员必看的十大电影
程序员必看的十大电影
程序员都该阅读的书
程序员都该阅读的书
程序员的鄙视链
程序员的鄙视链
程序员的一天:一寸光阴一寸金
程序员的一天:一寸光阴一寸金
那些争议最大的编程观点
那些争议最大的编程观点
我是如何打败拖延症的
我是如何打败拖延症的
Google伦敦新总部 犹如星级庄园
Google伦敦新总部 犹如星级庄园
亲爱的项目经理,我恨你
亲爱的项目经理,我恨你
一个程序员的时间管理
一个程序员的时间管理
程序员眼里IE浏览器是什么样的
程序员眼里IE浏览器是什么样的
5款最佳正则表达式编辑调试器
5款最佳正则表达式编辑调试器
代码女神横空出世
代码女神横空出世
看13位CEO、创始人和高管如何提高工作效率
看13位CEO、创始人和高管如何提高工作效率
聊聊HTTPS和SSL/TLS协议
聊聊HTTPS和SSL/TLS协议
10个帮程序员减压放松的网站
10个帮程序员减压放松的网站
科技史上最臭名昭著的13大罪犯
科技史上最臭名昭著的13大罪犯
60个开发者不容错过的免费资源库
60个开发者不容错过的免费资源库
程序员和编码员之间的区别
程序员和编码员之间的区别
“懒”出效率是程序员的美德
“懒”出效率是程序员的美德
当下全球最炙手可热的八位少年创业者
当下全球最炙手可热的八位少年创业者
软件开发程序错误异常ExceptionCopyright © 2009-2015 MyException 版权所有