Oracle如何向PostgresQL移植

共计 4733 个字符，预计需要花费 12 分钟才能阅读完成。

这篇文章主要为大家展示了“Oracle 如何向 PostgresQL 移植”，内容简而易懂，条理清晰，希望能够帮助大家解决疑惑，下面让丸趣 TV 小编带领大家一起研究并学习一下“Oracle 如何向 PostgresQL 移植”这篇文章吧。

Oracle 向 PostgresQL 移植实例

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

以上是“Oracle 如何向 PostgresQL 移植”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注丸趣 TV 行业资讯频道！