Oracle向PostgresQL移植例項

先參考參考！[@more@]

初來乍到，發個本人的移植例項請大家拍磚
Oracle向PostgresQL移植

--By 大河馬,
--2005-08,
--Ver. 1.1

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
其重要特性就是客戶端/伺服器的自動轉碼
透過此方法移植的資料庫，可以在程式方面不作任何調整而自動適應
至少目前沒發現問題，汗一個！！