orcle 与pg数据库的差别

pg数据库

1. 基本语法差异

1.1. 基本数据类型差异

 

2. 高级语法差异

2.1. 事务差异

Oracle 中，通过 commit/rollback来实现事务提交或回滚。

结构类似于： begin select ... update ... delete ... commit; exception when others then rollback; end;

PostgreSQL 实际上把每个 SQL 语句当做在一个事务中执行来看待。 如果你没有发出BEGIN命令，那么每个独立的语句都被一个隐含的BEGIN 和(如果成功的话)COMMIT包围。一组包围在BEGIN和COMMIT 之间的语句有时候被称做事务块。 例如：

BEGIN;

UPDATE accounts SET balance = balance - 100.00

WHERE name = 'Alice'; SAVEPOINT my_savepoint;

UPDATE accounts SET balance = balance + 100.00 WHERE name = 'Bob';

ROLLBACK TO my_savepoint;

UPDATE accounts SET balance = balance + 100.00

WHERE name = 'Wally';

COMMIT;

2.2. 函数继承与重载

Oracle不支持 继承和重载特性，pgsql支持继承和函数重载；

2.3. 类型转换

pgsql 中的类型转换 “::” 符，Oracle 不支持。

2.4. 子查询

子查询，pgsql要求更严格，必须具有别名才可以；

4. 兼容性设置

4.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;

4.2. 虚表 dual 问题

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;

4.3. 数据分页问题

-- Oracle

select * from ( select * from ( select * from t1 order by col1, col2 )

where rownum <=50 order by col3, col4 ) where rowmun <=20 order by col5, col6;

-- postgresql

select * from (select * from (select * from t1 order by col1, col2) ta order by col3,

col4 limit 50) tb order by col5, col6 limit 20;

树形查询

oracle：

select t.dept_id, t.dept_name, t.dept_code, t.par_dept_id,

level from SYS_DEPT t start with t.dept_id = '40288ac45a3c1e8b015a3c28b4ae01d6'

connect by prior t.dept_id = t.par_dept_id order by level, t.dept_code

pg：

WITH RECURSIVE T(ID,NAME,PARENT_ID) AS

( SELECT ID,NAME,PARENT_ID FROM TREE_TEST WHERE ID =13

UNION ALL SELECT T1.ID,T1.NAME,T1.PARENT_ID FROM TREE_TEST T1

JOIN T ON T1.PARENT_ID=T.ID ) SELECT ID,NAME,PARENT_ID FROM T

【运行缓慢】

执行如下命令，查询单次调用最耗时 SQL TOP 10。

select userid::regrole, dbid, query from pg_stat_statements order by mean_time desc limit 10;

执行如下命令，查询总最耗时 SQL TOP 10。

select userid::regrole, dbid, query from pg_stat_statements order by total_time desc limit 10;

【IO缓慢】

执行如下命令，查询单次调用最耗 IO SQL TOP 10。

select userid::regrole, dbid, query from pg_stat_statements order by (blk_read_time+blk_write_time)/calls desc limit 10;

执行如下命令，查询总最耗 IO SQL TOP 10。

select userid::regrole, dbid, query from pg_stat_statements order by (blk_read_time+blk_write_time) desc limit 10;

【响应时间抖动的SQL】

执行如下命令，查询响应时间抖动最严重 SQL。

select userid::regrole, dbid, query from pg_stat_statements order by stddev_time desc limit 10;

【最耗共享内存的SQL】

执行如下命令，查询最耗共享内存 SQL。

select userid::regrole, dbid, query from pg_stat_statements order by (shared_blks_hit+shared_blks_dirtied) desc limit 10;

【最耗临时空间的SQL】

select userid::regrole, dbid, query from pg_stat_statements order by temp_blks_written desc limit 10;