Postgresql与Oracle语法区别-爱代码爱编程
一、Pg数据库相比较于Oracle数据库有什么优势?
1、PostgreSql是目前功能最强大的开源数据库
2、稳定可靠:PostgreSql在主备库方面非常完善,可以搭建同步备库、异步备库、延迟备库,在同步备库中可以同时配置数据同步到任意备库上;且在配置备库过程中比Oracle更加简单;此外PostgreSql是唯一能做到数据零丢失的开源数据库,目前有报道称国内外有部分银行使用PostgreSql数据库
3、开源省钱:免费的、开源的,而且使用的是类BSD协议,在使用和二次开发上基本没有限制
4、有更多支持互联网特征的功能,如数据类型支持网络地址类型、XML类型、JSON类型、UUID类型以及数组类型,且有强大的正则表达式函数;且支持大量主流的开发语言,包括C、C++、perl、Python、Java以及PHP等
5、轻量小巧:Oracle安装包一般几个GB以上级别,PostgreSql安装包只有几十MB大小;且PostgreSql可以在内存很小的机器上完美运行,Oracle基本要在数GB的云主机上才可运行
6、PostgreSql社区活跃:PostgreSql基本每三个月推出一个补丁版本,这意味着已知的BUG很快会被修复
二、事务
1、基本概念:Transaction是数据库管理系统DBMS执行过程中的一个逻辑单元,是一个sql命令组成的序列.其特点在于,当事务被提交DBMS后,DBMS需要确保所有的操作被完成;如果事务中有的操作未完成,那么所有操作都将回滚,回到事务提交之前的状态
2、区别:
Postgresql中的事务要手动开启,否则直接写update/insert/delete是默认自动提交的,手动开启方式如下
begin;-开启事务
update test set name = 'c' where id = 1;
end;关闭事务
只有执行了begin;开启事务,下面才可以接commit或rollback。后续end关闭事务;
Oracle数据库的事务无需手动开启,执行DML语句就会开启事务
三、子查询
Postgresql中子查询较为规范,子查询必须要取别名
select * from (select * from test) t; --正确写法
select * from (select * from test); --报错:subquery in from must have an alias
Oracle中子查询别名可写可不写。
四、数据类型
Oracle | PostgreSql |
---|---|
number | smallint/integer/bigint/decimal/numeric |
varchar2 | varchar |
date | date/time |
clob | text |
null | null |
五、分页
查询表中100条数据
PostgreSql中没有rownum伪列,有limit
--PostgreSql
select * from test t limit 100;
--Oracle
select * from test t where rownum <= 100;
六、部分函数差异
6.1 类型转换
--PostgreSql
select id::varchar from test;--使用两个冒号来进行类型转换
--Oracle
select cast(id as varchar2(10)) from test; --使用cast函数
6.2 空值转换
--PostgreSql
select coalesce(name,1) from test; --使用coalesce函数
--Oracle
select nvl(name,1) from test; --使用nvl函数
6.3 decode操作
PostgreSql不支持decode操作,但可以用case when转换
--PostgreSql
select (case name when 'a' then 1 else 2 end) name from test;
--Oracle
select decode(name,'a',1,2) name from test;
6.4 ||拼接符
有部分差异,主要体现在与Null的拼接上
--PostgreSql
select 'a'||null from dual; --返回null
--Oracle
select 'a'||null from dual; --返回'a'
6.5 动态执行
--PostgreSql
execute 'select count(1) from test' into v_count;
--Oracle
execute immediate 'select count(1) from test' into v_count;
七、游标
Oracle | PostgreSql |
---|---|
%found | found |
%notfound | not found |
%isopen | – |
%rowcount | – |
八、存储过程和函数
示例
create or replace function sp_test()
returns integer
language plpgsql
$functions$
declare
v_count integer;
begin
execute 'select count(1) from test' into v_count;
return v_count; --注意这里是return,不是returns
end;
$functions$
------------------------------------------------
create or replace function sp_test(out integer)
language plpgsql
$functions$
declare
v_count integer;
begin
execute 'select count(1) from test' into v_count;
end;
$functions$
----------------------------------------------------
create or replace function sp_test(out integer)
returns integer
language plpgsql
$functions$
declare
v_count integer;
begin
execute 'select count(1) from test' into v_count;
--return v_count注意这里不能出现return语句,有out参数的情况下出现return语句会报错
end;
$functions$
以上创建语法都是正确的
1、Pgsql中的函数也被称为存储过程,即函数和存储过程是一体的
2、Pgsql中存储过程既可以有out返回参数又可以有returns返回值。我们可以这样理解:里面带有return关键字的相当于Oracle中函数,有OUT返回参数的相当于Oracle中存储过程
3、调用存储过程
–Oracle,调用存储过程有三种方式
call sp_test();
begin
sp_test();
end;
exec sp_test();
–PostgreSql使用select查询语句来调用存储过程
select * from sp_test(入参1,入参2,入参3....);
有return关键字的pgsql存储过程可以通过上述方式调用来返回数据,如果是out参数是游标等类型呢?经过研究,可以通过以下方式调用
end;
begin;
select sp_test(入参1,入参2,入参3...);
fetch all in "<unnamed portal 1>"
九、declare/begin/end代码块
PostgreSql中也有代码块操作,语法和Oracle稍有不同
do
$do$
declare
x text := '';
v_sql varchar array := '{"li,shi,ming"}';
begin
foreach x in array v_sql
loop
raise notice '%',x;
end loop;
end
$do$
未完待续…欢迎各位大佬补充
关于Postgresql中一些特殊用法,可看下面这篇文章
PostgreSql学习