Tips] 移植Oracle数据库到PostgreSQL的要点

来源：互联网发布：淘宝卖保健品需要什么编辑：程序博客网时间：2024/05/16 10:41

[Tips] 移植Oracle数据库到PostgreSQL的要点最近尝试把一个Oracle数据库，连同构建在这个数据库上的Java应用移植到PostgreSQL环境。在移植过程中，总结了一些要点，一方面作为笔记备忘，一方面也给有类似任务需要处理而又无从下手的朋友作为参考。1- 首先是准备PostgreSQL环境。有条件的话，最好是找一台空闲的PC机作为测试服务器，安装Linux或BSD，然后从源码编译最新的PostgreSQL 8.3.0。编译时，通过configure指定--with-perl和--with-python以支持PL/Perl和PL/Python。因为绝大多数Linux发行版都已自带Perl和Python，不必额外安装。2- 如果是Windows环境，又需要Perl和Python，则必须额外安装，Python的话，可以方便的找到2.5 for Windows的安装包，Perl的话，推荐ActivePerl，相对麻烦一点，为了后面用到的一些便利的功能顺利加载，Perl版本尽量选5.8.8。3- 创建数据库和用户。通过initdb初始化数据目录，配置postgresql.conf指定主机IP、端口等等信息，配置pg_hba.conf指定访问权限，通过pg_ctl -D <数据目录> -l <日志文件> start启动postmaster，然后createdb、createuser创建数据库和用户。数据库建好之后，就可以createlang -d <数据库名> [plperl|plperlu|plpython|plpythonu]开启PL/Perl和PL/Python。具体命令行参数可通过各命令加--help查看。4- 安装PostgreSQL客户端pgAdminIII，最新版是1.8.2，有条件的话，也可以下载源码自己编译。5- 安装Oracle客户端，需要在PostgreSQL同一台机器，以便Perl用于连接数据库的DBI和Oracle驱动DBD::Oracle模块顺利安装。如果是Windows上的ActivePerl，则可以通过ppm install DBD-Oracle，如果是Linux/BSD，则可以通过CPAN来安装，如perl -MCPAN -e shell进入CPAN Shell，通过install <模块名>或force install <模块名>安装DBI和DBD::Oracle。6- 数据库的移植，可以选择ora2pg来帮忙，目前的版本是4.7。ora2pg是一个用于读取Oracle数据库schema、数据，并生成PostgreSQL脚本或直接导入PostgreSQL数据库的Perl工具。用法很简单，就是通过.conf文件指定数据库连接信息包括NLS_LANG、需要导出导入的schema、table、view、data等等，然后执行一个pl脚本。这是目前相对比较成熟的一个方案，但是遇到schema复杂、约束较强的数据库，需要手工处理的地方还是不少。建议不要直接写入PostgreSQL，而是生成SQL脚本，验证无误后再执行。ora2pg默认会把Oracle中名称的大写转换成小写，因为PostgreSQL在解析SQL时，除非""括起来，默认都是转成小写。schema、table、view、sequence、data等等，基本用ora2pg，加上一些手工调整即可搞定。至于function、stored procedure等，还是手工移吧，偷不得懒。除了ora2pg，其实也可以配置DBI-Link，将Oracle数据库挂到PostgreSQL数据库作为一组独立的"schema"，然后用create table xxx as select ... from ...这样的语法来倒表和数据。PostgreSQL的contrib包也附带有一个dblink，不过是连接其他PostgreSQL数据库的，如果需要连接非PostgreSQL，还是考虑DBI-Link，任何可以通过Perl的DBI接口访问的数据库，都能link进PostgreSQL。7- 接下来就是Java应用本身了，我这次移的这个应用是Spring+iBatis架构的，很多SQL语句都是明文，好在DAO层的基础部分(CRUD)的SQLMap是工具自动生成，且都是符合ANSI SQL92标准的，不需要修改即可使用。其余的高级查询SQL，需要调整的地方不少，一些常见的修改列举如下：i. SELECT出来的column（包括子查询），如果有别名，必须加AS，比如 select null as some_column from some_table;ii. PostgreSQL没有dual表，类似select 0 from dual的语句，写成select 0即可;iii. DECODE函数需要重构成(case when some_column = 'some_value' then 'some_other_value' when ... then ... else 'some_default_value' end ) as some_column;iv. NVL()函数，PostgreSQL中相对应的是coalesce()，其实几乎所有主流DBMS都支持coalesce，包括Oracle，这才是标准写法;v. 比较日期，在PostgreSQL中，建议使用date_trunc('day', SOME_DATE) = date_trunc('day', #enteredDate#)这样的写法，其中'day'位置可选字段包括有year、month、week、hour、minute、second等等;vi. SYSDATE，对应到PostgreSQL是current_timestamp，可以根据需要使用current_date;vii. ROWNUM，通常我们用ROWNUM都是为了限制查询出来的记录数，PostgreSQL没有这个关键字，需要改成在SELECT语句最后添加 LIMIT语句，如LIMIT 100;viii. (+)这样的外连接写法需要调整为SQL标准的 table1 [LEFT|RIGHT|FULL] OUTER JOIN table2 ON (...);ix. CONNECT BY ... START WITH ... 递归查询可以参考 http: //www.postgresql.org/docs/8.3/static/tablefunc.html 的connectby()函数.最后再多提一点，PostgreSQL自带的过程语言是PL/pgSQL，在PostgreSQL上写function，除了用plpqsql，还支持sql、plperl(u)、plpython(u)等等。如果你对SQL天生过敏，看类似PL/pgSQL的代码都很吃力，别说是写了，你完全可以用你喜欢的语言来表达函数和存储过程的逻辑。有了PL/Python，你还怕什么呢？你几乎能做任何事。[更新 20080313] 把JDBC驱动的部分漏掉了，移植Java应用时，除了改SQL，还需要拿PostgreSQL的JDBC驱动放到classpath下面，如WEB-INF/lib，然后修改数据库连接URL，改成jdbc:postgresql://:/即可。[更新 20080323] 移植schema和数据时，比ora2pg更方便的一种方式是利用EnterpriseDB的Migration Tool，将Oracle的JDBC驱动ojdbc14.jar拷贝到EnterpriseDB安装路径下的jre/lib/ext下后，启动Developer Studio即可建立Oracle连接，选中schema后，可以通过右键Online Migration将schema、数据、函数包等等一次性通通导入EnterpriseDB。如果要继续往"纯"PostgreSQL移，从EDB做backup，然后到PostgreSQL下做restore，这样会丢掉函数包，因为毕竟EDB在PostgreSQL基础上做了相当改造以和Oracle兼容，不过函数包之类还是手工移比较稳妥。常用数据库函数对比(1)今天在处理postgreSQL数据库的函数的时候。遇到了一个问题我要在select语句中来判断某个字段的属性是否为空，或者是否为一个特定的值，我需要作判断，在oracle中我们会用nvl decode 等函数。同样 mysql 中会有ifnull,if 函数。有时候会搞混，忘记了那个哪个数据库支持这个函数了。于是就总结一下，不同数据库中的一些常用函数，这样方便以后使用。一，日期操作 1，操作当前日期和时间Microsoft SQL Server Select GETDATE() GOMySQL 返回日期不包括时间 Select CURDATE();MySQL 返回日期和时间 Select NOW();oracle Select SYSDATE FROM dual;PostgreSQL Select CURRENT_DATE; Select NOW();返回日期时间还包括时区2，操作时间的获取子域Microsoft SQL Server Select DATEPART(dw, GETDATE()) GOMySQL Select DAYNAME(CURDATE());oracle Select TO_CHAR(SYSDATE,'Day') FROM dual;PostgreSQL Select DATE_PART('dow',date 'now');//dow = day of week Select DATE_PART('hour', timestamp 'now')Microsoft SQL 中调用函数DATEPART语法为： DATEPART(datetype, date_expression).函数参数datetype 为month, day, week, day of week 等，而第二个参数为包含日期类型的字段或者一个真实的日期值，而mysql中的DAYNAME函数就是直接指定了当前日期为星期几，oracle中的TO_CHAR可以从日期中拿到所需要的子域，日期，小时，分钟等。3，时间间隔，在一些应用中需要知道两个时间间隔多远Microsoft SQL Server Select DATEDIFF(dd, '1/1/01', GETDATE()) GOMySQL Select FROM_DAYS(TO_DAYS(CURDATE()) - TO_DAYS('2001-11-25'));oracle Select TO_DATE('25-Nov-2000','dd-mon-yyyy') - TO_DATE('25-Aug-1969','dd-mon-yyyy') FROM dual;PostgreSQL Select AGE(CURRENT_DATE, '25-Aug-1969');测量不同时间的间隔，不同的数据库之间函数语法有很大的不同。4，日期时间格式化Microsoft SQL Server Select CONVERT(VARCHAR(11), GETDATE(), 102) GOMySQL Select DATE_FORMAT( /"2001-11-25/", /"%M %e, %Y/");oracle Select TO_CHAR(SYSDATE,'dd-Mon-yyyy hh:mi:ss PM') FROM dual;PostgreSQL Select TO_CHAR (timestamp(CURRENT_DATE),'dd-Mon-yyyy hh:mi:ss PM');二，字符串操作 1，字符串中包含字符Microsoft SQL Server Select CHARINDEX('eat', 'great') GOMySQL Select POSITION('eat' IN 'great');oracle Select INSTR('Great','eat') FROM dual;PostgreSQL Select POSITION('eat' IN 'great');通过上面的这些函数可以确定字符串在另一个字符串中的位置（及另一个字符串包含这个字符串的位置）。2，字符串去掉空格Microsoft SQL Server Select LTRIM(' sql_in_a_nutshell'), Select RTRIM('sql_in_a_nutshell '), Select LTRIM(RTRIM(' sql_in_a_nutshell ') GOMySQL Select LTRIM(' sql_in_a_nutshell'), Select RTRIM('sql_in_a_nutshell '), Select TRIM(' sql_in_a_nutshell '), Select TRIM(BOTH FROM ' sql_in_a_nutshell ');oracle Select LTRIM(' sql_in_a_nutshell'), Select RTRIM('sql_in_a_nutshell '), TRIM(' sql_in_a_nutshell ') FROM dual;PostgreSQL Select TRIM(LEADING FROM ' sql_in_a_nutshell'), TRIM(TRAILING FROM 'sql_in_a_nutshell '), TRIM(BOTH FROM ' sql_in_a_nutshell ');3，上面清除空格相反的操作，添加空格Microsoft SQL Server Not supportedMySQL Select LPAD('sql_in_a_nutshell', 20, ' '), RPAD('sql_in_a_nutshell', 20, ' ');oracle Select LPAD(('sql_in_a_nutshell', 20, ' '), RPAD(('sql_in_a_nutshell', 20, ' ') FROM dual;PostgreSQL Select LPAD('sql_in_a_nutshell', 20, ' '), RPAD('sql_in_a_nutshell', 20, ' ');上面支持该操作的数据库的函数都相同，并且都包括从左和右添加空格的方法。4，字符串替换Microsoft SQL Server [returns 'wabbit_hunting_season'] Select STUFF('wabbit_season', 7, 1, '_hunting_') GOMySQL [returns 'wabbit_hunting_season'] Select REPLACE('wabbit_season','it_','it_hunting_');oracle [returns 'wabbit_hunting_season'] Select REPLACE('wabbit_season','it_','it_hunting_') FROM dual; PostgreSQL Select TRANSLATE('wabbit_season','it_','it_hunting_'); Select replace('wabbit_season','it_','it_hunting_');5，字符串截取Microsoft SQL Server Select SUBSTRING('wabbit_duck_season', 7, 11) GOMySQL Select SUBSTRING('wabbit_duck_season', 7, 11);oracle Select SUBSTR('wabbit_duck_season', 7, 11) FROM dual;PostgreSQL Select SUBSTR('wabbit_duck_season', 7, 11);三，条件判断1，条件判断Microsoft SQL Server Select CASE WHEN foo = 'hi' THEN 'there' WHEN foo = 'good' THEN 'bye' ELSE 'default' END FROM t2 GOMySQL select if(('11'='11'),'1','2') select if(2>1,'1','2')oracle Select DECODE (payments_info,'CR','Credit','DB','Debit', null) FROM dual;PostgreSQL Select CASE WHEN foo = 'hi' THEN 'there' WHEN foo = 'good' THEN 'bye' ELSE 'default' END FROM t2;上面的函数我们就不多作解释了，很容易理解，我们来说一下mysql的if()函数，如果第一个参数为true那么返回地二个参数，否则返回第三个参数。2，判断空函数Microsoft SQL Server Select ISNULL(foo, 'Value is Null') GOMySQL select ifnull(122,'aaa')oracle Select NVL(foo,'Value is Null') FROM dual;PostgreSQL Select coalesce(foo,'Value is Null')3，下面与上面的函数不同Microsoft SQL Server [returns NULL when foo equates to 'Wabbits!'] Select NULLIF(foo, 'Wabbits!') GOMySQL N/Aoracle Select DECODE(foo,'Wabbits!',NULL) FROM dual;PostgreSQL Select NULLIF(foo, 'Wabbits!'); 函数语法:NULLIF(expression1, expression2)如果 expression1 等于 expression2则返回 NULL，如果expression1的值为null,也返回NULL