背景

 最近参与数仓建设过程中需要进行oracle到postgresql的迁移，其中不可避免的涉及到了oracle存储过程、函数到postgresql的迁移。本次，我将针对Oracle用户遇到的一些函数语法与PostgreSQL不兼容的地方，谈一些我的体会。
 Oracle PL/SQL是非常强大的一门SQL编程语言，PostgreSQL也有一门非常高级的内置SQL编程语言——plpgsql。与Oracle PL/SQL语法极其类似，但是还是有一些不一样的地方。(PS：除了plpgsql，PostgreSQL还支持C，java，python，perl等流行的语言作为数据库的函数编程语言)
 本文主要涉及到函数、一些基本类型、提示······

函数

 oracle（PL/SQL）有存储过程和函数之分，存储过程定义关键字用procedure，函数定义用function；存储过程中不能用return返回值，但函数中可以，而且函数中必须有return子句。而postgresql没有存储过程和函数之分，postgresql只能使用function，但是其函数也被称为存储过程，可执行操作。

oracle--PL/SQL例子

①存储过程

create or replace procedure “p1 ”
--or replace代表创建该存储过程时，若存储名存在，则替换原存储过程，重新创建
--无参数列表时，不需要写()
is
  v_slno integer;
begin
  dbms_output.put_line('hello world');
end;

②函数

create or replace function f1()
--必须有返回值，且声明返回值类型时不需要加大小
return varchar
is
  msg varchar(50);
begin
   msg := 'hello world';
   return msg;
end;

Postgresql例子

CREATE OR REPLACE FUNCTION udf_f1()
--or replace代表创建该函数时，若函数名存在，则替换原存储过程，重新创建
--参数写在()中
  RETURNS integer AS
--无返回值时使用returns viod
$BODY$
DECLARE
    o_status integer;
begin
    o_status :=1;   
    return o_status;
end;
$BODY$
--必须指定编写语言
LANGUAGE plpgsql;

一些基本数据类型

①时间类型 date、timestamp

 oracle，date类型表示日期和时间，它可以存储月，年，日，世纪，时，分和秒， 可以使用TO_CHAR函数把DATE数据进行传统地包装，达到表示成多种格式的目的（TO_CHAR(sysdate,'MM/DD/YYYY HH24:MI:SS')），其实oracle 已经在DATE数据类型上扩展出来了TIMESTAMP数据类型，它包括了所有DATE数据类型的年月日时分秒的信息，而且包括了小数秒的信息。如果想把DATE类型转换成TIMESTAMP类型，就使用CAST函数（CAST(date1 AS TIMESTAMP)）。但是，普遍情况下大家还是习惯性用date类型，需要注意的是date两个日期型相减得到是两个时间的间隔，单位是“天”。
 postgresql，date类型只有四个字节，不能表示当天的时间，time类型只表示时间，无法确定日期（天），timestamp可具体表示到天、时、分、秒（注意without the zone无时区和with the zone时区，无特殊要求可不区分）。
 由上可见，虽然oracle和postgresql都包含date类型，但两者有所区别，通常状况下，在postgresql中用timestamp类型来表示oracle的date类型。

②数值类型 oracle--number、postgresql--numeric、oracle--float、postgresql--double

 oracle的number类型是oracle的内置类型之一，是oracle的最基础数值数据类型。在9iR2及其以前的版本中只支持一种适合存储数值数据的固有数据类型，在10g以后，出现了两种新的数值类型，即推出本地浮点数据类型(Native Floating-Point Data Types): BINARY_FLOAT(单精度32位)和BINARY_DOUBLE(双精度64位)。

number类型的语法很简单：number(p,s)：

p：精度位，precision，是总有效数据位数，取值范围是38，默认是38，可以用字符*表示38。
s：小数位，scale，是小数点右边的位数，取值范围是-84~127，默认值取决于p，如果没有指定p，那么s是最大范围，如果指定了p，那么s=0。

注意：number可进行小数四舍五入

 postgresql中没有number数据类型，但内置numeric数据类型，其职能与number类似，在postgresql中numeric称之为任意精度数值，可以存储小于等于1000位精度的数字，其语法与number相似（同样可以进行四舍五入）。注意，除了普通的数字值之外，numeric 类型允许特殊值NaN， 表示"不是一个数字"。任何在NaN上面的操作都生成另外一个NaN。 如果在 SQL 命令里把这些值当作一个常量写，你必须在其周围放上单引号，比如 UPDATE table SET x = 'NaN'。在输入时，字串 NaN 当作大小写无关看待。 类型 decimal 和 numeric 是等效的。

 oracle（PL/SQL类型）float数据类型（注意：oracle中的float类型是以二进制精度计算的），而postgresql中没有（注意：在实际编写函数或测试sql语句时，在postgresql中定义float不报错，可正常应用，不过最好用double precision代替）。
 postgresql中的浮点数类型是real和double precision。

 注意:
（以postgresql为例）：real和double precision是不准确的，即一些数值经过存储然后把数据再打印出来可能显示一些缺失（原因： 一些数值不能准确地转换成内部格式并且是以近似的形式存储的），如果需要准确的计算建议使用numeric

输出提示行

 输出 oracle--dbms_output.put_line（） postgresql--raise/raise notice

 其实，在PostgreSQL和Oracle（PL/SQL ） 的兼容过程中还有许多注意点，例如cursor、exectue、type、array、record等，后续继续分享。