《卸甲笔记》-PostgreSQL和Oracle的SQL差异分析之五：函数的差异(二)

PostgreSQL是世界上功能最强大的开源数据库，在国内得到了越来越多机构和开发者的青睐和应用。随着PostgreSQL的应用越来越广泛，Oracle向PostgreSQL数据库的数据迁移需求也越来越多。数据库之间数据迁移的时候，首先是迁移数据，然后就是SQL、存储过程、序列等程序中不同的数据库中数据的使用方式的转换。下面根据自己的理解和测试，写了一些SQL以及数据库对象转换方面的文章，不足之处，尚请多多指教。

1、to_number函数

to_number用于将字符类型转换成数字。主要使用在显示格式的控制以及排序等地方。特别是排序的时候，因为按照字符排序和按照数字排序，结果是不同的。

Oracle的to_number函数接受两个参数。第一个参数是需要转换的字符串，第二个参数是要转换的格式。实际使用的时候，除非采用特殊的格式显示，否则一个参数就已经足够了。

PostgreSQL的to_number函数也接受两个参数。使用的时候，不能只使用一个参数。排序的时候， 格式字符串中需要设置转换的位数为该字段的最大位数。否则，只使用格式提供的转换位数来排序。比如格式提供了两位，那么就转换最前面的两位字符为数字，然后使用它来排序。

Oracle to_number函数

SQL> desc o_test;
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 NAME                                               VARCHAR2(10)
 AGE                                                VARCHAR2(10)

SQL> select * from o_test;

        ID NAME       AGE
---------- ---------- ----------
           赵大       20
           钱二       9
           孙三       30
           李四       110

SQL> select * from o_test order by age;

        ID NAME       AGE
---------- ---------- ----------
           李四       110
           赵大       20
           孙三       30
           钱二       9

SQL> select * from o_test order by to_number(age);

        ID NAME       AGE
---------- ---------- ----------
           钱二       9
           赵大       20
           孙三       30
           李四       110

SQL> select id, name, to_number(age) age from o_test order by to_number(age);

        ID NAME              AGE
---------- ---------- ----------
           钱二                9
           赵大               20
           孙三               30
           李四              110

PostgreSQL to_number函数

postgres=# \d p_test;
                           数据表 "public.p_test"
 栏位 |         类型          |                    修饰词
------+-----------------------+----------------------------------------------
 id   | integer               | 非空 默认 nextval('p_test_id_seq'::regclass)
 name | character varying(10) |
 age  | character varying(10) |

postgres=# select * from p_test;
 id | name | age
----+------+-----
  1 | 赵大 | 20
  2 | 钱二 | 9
  3 | 孙三 | 30
  4 | 李四 | 110
(4 行记录)

postgres=# select * from p_test order by age;
 id | name | age
----+------+-----
  4 | 李四 | 110
  1 | 赵大 | 20
  3 | 孙三 | 30
  2 | 钱二 | 9
(4 行记录)

postgres=# select * from p_test order by to_number(age);
错误:  函数 to_number(character varying) 不存在
第1行select * from p_test order by to_number(age);
                                   ^
提示:  没有匹配指定名称和参数类型的函数. 您也许需要增加明确的类型转换.
postgres=# select * from p_test order by to_number(age,'9999999999');
 id | name | age
----+------+-----
  2 | 钱二 | 9
  1 | 赵大 | 20
  3 | 孙三 | 30
  4 | 李四 | 110
(4 行记录)

postgres=# select * from p_test order by to_number(age,'99');
 id | name | age
----+------+-----
  2 | 钱二 | 9
  4 | 李四 | 110
  1 | 赵大 | 20
  3 | 孙三 | 30
(4 行记录)

postgres=# select id,name, to_number(age,'9999999999') from p_test order by to_number(age,'9999999999');
 id | name | to_number
----+------+-----------
  2 | 钱二 |         9
  1 | 赵大 |        20
  3 | 孙三 |        30
  4 | 李四 |       110
(4 行记录)

2、decode函数

decode是Oracle固有的一个函数，用于条件判断。其格式为
decode(条件, 值1, 返回值1, 值2, 返回值2,... 值n, 返回值n, 缺省值) 。当条件等于值1的时候返回返回值1，······等于值n的时候返回返回值n。都不等于的时候返回缺省值。

PostgreSQL中，decode函数使用来解码的，和encode函数相对。对于Oracle的decode函数，可以把它转换成case......when....的SQL语句，得到一样的效果。
Oracle也支持case....when。用法和PostgreSQL中类似。

Oracle

SQL> select * from o_test;

        ID NAME       AGE
---------- ---------- ----------
           赵大       20
           钱二       9
           孙三       30
           李四       110

SQL> select decode(age, '20', '赵大', '9', '钱二', '张三') testname from o_test;

TEST
----
赵大
钱二
张三
张三

SQL> select case age when '20' then '赵大' when '9' then '钱二' else '张三' end testname from o_test;

TEST
----
赵大
钱二
张三
张三

SQL> select case  when age = '20' then '赵大' when age= '9' then '钱二' else '张三' end testname from o_test;

TEST
----
赵大
钱二
张三
张三

PostgreSQL

postgres=# select * from p_test;
 id | name | age
----+------+-----
  1 | 赵大 | 20
  2 | 钱二 | 9
  3 | 孙三 | 30
  4 | 李四 | 110
(4 行记录)

postgres=#  select case age when '20' then '赵大' when '9' then '钱二' else '张三' end testname from p_test;
 testname
----------
 赵大
 钱二
 张三
 张三
(4 行记录)


postgres=#  select case when age='20' then '赵大' when age= '9' then '钱二' else '张三' end testname from p_test;
 testname
----------
 赵大
 钱二
 张三
 张三
(4 行记录)

postgres=# select encode('abcdefghijklmn', 'base64');
        encode
----------------------
 YWJjZGVmZ2hpamtsbW4=
(1 行记录)

postgres=# select decode('YWJjZGVmZ2hpamtsbW4=', 'base64');
             decode
--------------------------------
 \x6162636465666768696a6b6c6d6e
(1 行记录)

3、instr函数

Oracle的instr函数是查找一个字符串中，另一个字符串所在的位置。如果找不到则返回0。instr一共有四个参数。前两个分别表示源字符串和查找字符串，第三个表示开始位置(<0的时候表示从右望左找)。第四个表示第几次出现的值。
PostgreSQL中，可以使用position(substring in string) 函数来对应它。position函数没有Oracle的那么复杂，有些复杂的功能只能使用自定义函数来实现它。

Oracle instr

SQL> select instr('helloworld', 'l') from dual;

INSTR('HELLOWORLD','L')
-----------------------
                      3

SQL> select instr('helloworld', 'l', 5) from dual;

INSTR('HELLOWORLD','L',5)
-------------------------
                        9

SQL> select instr('helloworld', 'l', -5) from dual;

INSTR('HELLOWORLD','L',-5)
--------------------------
                         4

SQL> select instr('helloworld', 'l', 4, 2) from dual;

INSTR('HELLOWORLD','L',4,2)
---------------------------
                          9

PostgreSQL position

postgres=# select position('l' in 'helloworld');
 position
----------
        3
(1 行记录)

postgres=# select length(substring('helloworld', 1, 4)) + position('l' in substring('helloworld',5));
 ?column?
----------
        9
(1 行记录)

postgres=#  select instr('helloworld', 'l', -5, 1) ;
错误:  函数 instr(unknown, unknown, integer, integer) 不存在
第1行select instr('helloworld', 'l', -5, 1) ;
            ^
提示:  没有匹配指定名称和参数类型的函数. 您也许需要增加明确的类型转换.
postgres=# CREATE FUNCTION instr(string varchar, string_to_search varchar,
postgres(# beg_index integer, occur_index integer)
postgres-# RETURNS integer AS 
$$

postgres$# DECLARE
postgres$# pos integer NOT NULL DEFAULT 0;
postgres$# occur_number integer NOT NULL DEFAULT 0;
postgres$# temp_str varchar;
postgres$# beg integer;
postgres$# i integer;
postgres$# length integer;
postgres$# ss_length integer;
postgres$# BEGIN
postgres$# IF beg_index > 0 THEN
postgres$# beg := beg_index;
postgres$# temp_str := substring(string FROM beg_index);
postgres$#   FOR i IN 1..occur_index LOOP
postgres$# pos := position(string_to_search IN temp_str);
postgres$#             IF i = 1 THEN
postgres$# beg := beg + pos - 1;
postgres$# ELSE
postgres$# beg := beg + pos;
postgres$# END IF;
postgres$#             temp_str := substring(string FROM beg + 1);
postgres$# END LOOP;
postgres$#         IF pos = 0 THEN
postgres$# RETURN 0;
postgres$# ELSE
postgres$# RETURN beg;
postgres$# END IF;
postgres$# ELSE
postgres$# ss_length := char_length(string_to_search);
postgres$# length := char_length(string);
postgres$# beg := length + beg_index - ss_length + 2;
postgres$#         WHILE beg > 0 LOOP
postgres$# temp_str := substring(string FROM beg FOR ss_length);
postgres$# pos := position(string_to_search IN temp_str);
postgres$#             IF pos > 0 THEN
postgres$# occur_number := occur_number + 1;
postgres$#                 IF occur_number = occur_index THEN
postgres$# RETURN beg;
postgres$# END IF;
postgres$# END IF;
postgres$#             beg := beg - 1;
postgres$# END LOOP;
postgres$#         RETURN 0;
postgres$# END IF;
postgres$#
postgres$# END;
postgres$# 
$$
 LANGUAGE plpgsql;
CREATE FUNCTION
postgres=#  select instr('helloworld', 'l', -5, 1) ;
 instr
-------
     4
(1 行记录)

postgres=# select instr('helloworld', 'l', 4, 2);
 instr
-------
     9
(1 行记录)