postgresql同oracle语法差异

由于这个项目后台程序量很大，存储过程+触发器大约有15万行代码。

用这个工具可以将一些oracle与pgsql的语法差异自动处理下，但不是全部，剩下的需要手工修改。

ORACLE语法 → PostgreSQL语法 1、VARCHAR2 → varchar 2、DATE → timestamp 3、SYSDATE → localtimestamp

4、Oracle中''和NULL是相同的，但pgsql是不同的，所以需要将''修改成NULL 5、字符串连接符 || Oracle： 'a'||null 结果是'a' pgsql: 'a'||null 结果是null 所以用concat()函数替代 6、trunc(时间) → date_trunc()

7、to_char, to_number, to_date pgsql都需要指定格式 8、DECODE → case 9、NVL → coalesce()

10、外连接(+) → left(right) join 11、GOTO语句 → pgsql不支持

12、pgsql不支持procedure和package，都需要改写成function 当package有全局变量的情况修改起来比较麻烦，我们是用临时表传递的。 13、cursor的属性 %FOUND → found %NOTFOUND → not found %ISOPEN → pgsql不支持 %ROWCOUNT → pgsql不支持

另外关于cursor的其他差异，参照这个帖子

http://bbs.pgsqldb.com/client/post_show.php?zt_auto_bh=56751 14、COMMIT,ROLLBACK;SAVEPOINT → pgsql不支持

15、Oracle的系统包，例如 DBMS_OUTPUT，DBMS_SQL，UTIL_FILE，UTIL_MAIL → pgsql不支持 16、异常处理方法不同 17、trigger的语法不同 18、日期的加减计算语法不同。

1、oracle没有继承和重载特性，pgsql支持继承和函数重载； 2、oracle中的空字符串等同于null，pgsql中是分开处理的； 3、oracle不支持boolean类型，可以用integer型代替 4、pgsql中 :: 为类型转换，oracle中不具有； 5、oracle中可以使用rownum分页，pgsql使用limit；

6、pgsql中，查询语句from子句中，表名后可以加 as 别名，oracle中表名后不允许出现as ； 7、pgsql子查询要求严格，必须具有别名才可以； 8、序列使用方式不一致。

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

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

B,SCHEMA.PREFIX_TABLE3

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 其重要特性就是客户端/服务器的自动转码

通过此方法移植的数据库，可以在程序方面不作任何调整而自动适应 至少目前没发现问题，汗一个！！