|
|
利用 DB2 9.7 新特性迁移 Oracle PL/SQL 程序到 DB2 中
史明春, 软件工程师, IBM
简介： IBM DB2 for Linux, UNIX, and Windows (LUW) 9.7 提供了许多激动人心的新特性，其中有一个就是对 Oracle SQL 和 PL/SQL 的支持，它支持 PL/SQL 许多语法，使得从 Oracle 到 DB2 的迁移变的更加容易。
本文的标签: [url=]sql_(structured_query_language)[/url], [url=]事务[/url], [url=]关于产品[/url], [url=]数据访问[/url], [url=]管理[/url], [url=]迁移[/url], [url=]配置[/url]
标记本文！
发布日期： 2011 年 3 月 14 日
级别： 初级
访问情况 421 次浏览
建议: 0 (添加评论)
[url=]![]() [/url][url=]![]() [/url][url=]![]() [/url][url=]![]() [/url][url=]![]() [/url] 平均分 （共 4 个评分 ）
由于 DB2 在价格上的优势以及其他各种原因，许多用户想要把他们原来在 Oracle 数据库中运行的 PL/SQL 程序迁移到 DB2 数据库中，例如最近有个专业保险软件开发的公司，他们原来所有的系统使用的数据库都是基于 Oracle 的，并在此基础上面开发了大量的 PL/SQL 代码，他们一直想把他们的软件销售到欧洲的保险公司中去，但是欧洲的许多保险公司现在正在使用的系统的数据库是 DB2，出于对现状的考虑，这些欧洲保险公司要求此家保险软件公司所开发的系统也要运行在他们熟悉的 DB2 上面。但是在 DB2 推出对 PL/SQL 的支持以前，由于缺乏对 Oracle 内建函数等特性的支持，这项工作是非常困难甚至难以完成的，他们进行半年时间的尝试最后放弃了。好消息是 DB2 9.7 中新特性增加了对 OraclePL/SQL 支持，这项工作对他们来说现在变得很容易了，那么这个新特性具体是什么样呢？
PL/SQL 支持新特性详解
在 DB2 9.7 中，PL/SQL 语句和脚本可以使用 DB2 的界面来编译和执行了 , 你无需要进行任何转换就可以在 DB2 中执行下面 PL/SQL 语句：
- 匿名块； 例如，DECLARE … BEGIN … END
- CREATE OR REPLACE FUNCTION 语句
- CREATE OR REPLACE PACKAGE 语句
- CREATE OR REPLACE PACKAGE BODY 语句
- CREATE OR REPLACE PROCEDURE 语句
- CREATE OR REPLACE TRIGGER 语句
- DROP PACKAGE 语句
- DROP PACKAGE BODY 语句；
PL/SQL 过程（procedure）和函数（function）能够被 PL/SQL 或者 DB2 过程和函数调用，下列语句和语言的组成部分可以在 PL/SQL 上下文中使用：
- 类型声明：
- Assciative arrays
- Record 类型
- VARRAY 类型
- 变量声明：
- 基本语句， 从句 ， 语句属性 ：
- Assignment 语句
- NULL 语句
- RETURNING INT 从句
- 语句属性，包括 SQL%FOUND、SQL%NTFOUND 和 SQL%ROWCOUNT
- 控制语句和结构：
- CASE 语句 ：
- 异常处理
- EXIT 语句
- FOR 语句
- GOTO 语句
- IF 语句
- LOOP 语句
- WHILE 语句
- 静态游标 ：
- CLOSE 语句
- Cursor FOR loop 语句
- FETCH 语句 ( 包括 FETCH INTO 一个 %ROWTYPE 变量 )
- OPEN 语句
- 参数化游标
- 游标属性
- REF 游标 ：
- REF 游标作为变量和参数
- 强类型 REF 游标
- OPEN FOR 语句
- 返回 REF 游标给 JDBC 应用
- 错误处理：
- RAISE_APPLICATIN_ERRR 过程
- RAISE 语句
- SQLCODE 函数
- SQLERRM 函数
从上面的支持清单我们可以看到 DB 9.7 对 Oracle PL/SQL 的支持已经非常强大了，下面我们通过具体的例子来验证一下支持的程度。
回页首
准备工作
因为 DB2 9.7 中和 Oracle 的默认兼容性设置是不兼容，为了使用 DB2 9.7 中的兼容性特性，我们首先必须用 db2set 这个命令来设置 DB2 的兼容性参数，也就是设置 DB2_COMPATIBILITY_VECTOR 为 ORA，这样我们就可以使用 DB2 和 Oracle 所有的兼容性特性。命令示例如下：
| db2set DB2_COMPATIBILITY_VECTOR=ORA db2stop db2start |
需要注意的是这个命令如果在数据库已经创建以后执行的话，NUMBER，DATE，VARCHAR2 这三种数据类型在 DB2 中是不能使用的，要想使用这三种数据的兼容性，必须在创建数据库前执行这个命令。如果不想使用 DB2 的 Oracle 兼容性特性的话，只需要设置 DB2_COMPATIBILITY_VECTOR 为空，重启一下数据库就可以了。
在迁移进行之前，我们创建一个名字叫 sample 的数据库来做示例，创建这个数据库很简单，打开 DB2 的命令行处理器，发出以下命令就可以了：
但是随着后面迁移活动的进行，你会发现由于默认创建的数据库的 page size 是 4k，不能满足 Oracle 数据库特性的需要，你需要带上 pagesize 16k 这个属性，也就是用下面这个命令来创建数据库：
| create database sample pagesize 16 K |
在 DB2 9.7 中还提供了一个名字为 CLP Plus 的命令行工具，这个工具和 Oracle 自带的 SQL Plus 使用方法基本相同，一般 pl/sql 程序的执行都是使用这个工具的。
回页首
一个简单的示例
我们先来看一个简单的示例，这个例子选自 Oracle 来演示 PL/SQL 中 CASE 语句的使用的，例子如下：
| set serveroutput on Drop function Get_Grade3; create or replace function Get_Grade3(score IN NUMBER) RETURN VARCHAR2 is begin -- use a Searched CASE Statement to find the Grade for the -- score passed in as a parameter CASE WHEN score BETWEEN 80 AND 100 THEN return 'A'; WHEN score BETWEEN 65 AND 79 THEN return 'B'; WHEN score BETWEEN 50 AND 64 THEN return 'C'; WHEN score BETWEEN 40 AND 49 THEN return 'D'; WHEN score BETWEEN 0 AND 39 THEN return 'F'; --Comment the ELSE leg to generate the exception --ELSE return 'Invalid score'; END CASE; exception when CASE_NOT_FOUND then return 'Exception - Case Not Found for score - '|| score; end Get_Grade3; / select Get_Grade3(-1) from dual / |
把这段代码保存在本地计算机的 D 盘下面为 casetest.sql，在 DB2 的 CLP plus 中运行一下，哦，这段程序竟然一点都不需要修改就运行成功了，运行的结果如下：
图 1.一个简单示例的迁移运行结果
![]()
这个例子给了我们很大的信心，我们可以运行一个稍微复杂的例子看看结果怎么样。
回页首
一个稍微复杂的示例
这是选自《 Oracle PL/SQL 实例精解（原书第 4 版）》的第 24 章的一个例子，这个例子中包括了变量的定义，一个 procedue，两个 function，以及 cursor 使用方法的演示，exception 使用方法的演示，以及 DBMS_OUTPUT 这个 oracle 内建函数的使用的演示。示例程序如下：
| -- ch24_2a.sql CREATE OR REPLACE PACKAGE school_api as v_current_date DATE; PROCEDURE Discount_Cost; FUNCTION new_instructor_id RETURN instructor.instructor_id%TYPE; FUNCTION total_cost_for_student (i_student_id IN student.student_id%TYPE) RETURN course.cost%TYPE; END school_api; / -- ch24_2b.sql CREATE OR REPLACE PACKAGE BODY school_api AS PROCEDURE discount_cost IS CURSOR c_group_discount IS SELECT distinct s.course_no, c.description FROM section s, enrollment e, course c WHERE s.section_id = e.section_id GROUP BY s.course_no, c.description, e.section_id, s.section_id HAVING COUNT(*) >=8; BEGIN FOR r_group_discount IN c_group_discount LOOP UPDATE course SET cost = cost * .95 WHERE course_no = r_group_discount.course_no; DBMS_OUTPUT.PUT_LINE ('A 5% discount has been given to' ||r_group_discount.course_no||' '||r_group_discount.description); END LOOP; END discount_cost; FUNCTION new_instructor_id RETURN instructor.instructor_id%TYPE IS v_new_instid instructor.instructor_id%TYPE; BEGIN SELECT INSTRUCTOR_ID_SEQ.NEXTVAL INTO v_new_instid FROM dual; RETURN v_new_instid; EXCEPTION WHEN OTHERS THEN DECLARE v_sqlerrm VARCHAR2(250) := SUBSTR(SQLERRM,1,250); BEGIN RAISE_APPLICATION_ERROR(-20003, 'Error in instructor_id: '||v_sqlerrm); END; END new_instructor_id; FUNCTION total_cost_for_student (i_student_id IN student.student_id%TYPE) RETURN course.cost%TYPE IS v_cost course.cost%TYPE; BEGIN SELECT sum(cost) INTO v_cost FROM course c, section s, enrollment e WHERE c.course_no = s.course_no AND e.section_id = s.section_id AND e.student_id = i_student_id; RETURN v_cost; EXCEPTION WHEN OTHERS THEN RETURN NULL; END total_cost_for_student; BEGIN SELECT trunc(sysdate, 'DD') INTO v_current_date FROM dual; END school_api; / |
导入需要的的数据库表，编译一下这个 package，令人惊讶的是这个比较复杂的的 PL/SQL 程序，竟然也不需要一点修改就编译通过了，那能不能正常运行呢？我们试一下调用 discount_cost 试试看，在 CLP Plus 中发出 call school_api.discount_cost() 命令，结果如下：
图 2.复杂的示例迁移运行结果
![]()
真是太令人兴奋了，这说明 DB2 对 oracle 的兼容性的支持已经达到了一个很好的程度了。但是别忙，是不是所有程序都可以这么幸运的无需要修改就能正确的运行呢？当然不是，我们来看一个需要的修改的例子。
回页首
一个需要修改的示例
下面这个示例是选自《 Oracle PL/SQL 实例精解（原书第 4 版）》第 23 章，它演示了创建一个 record 类型，然后通过从数据库里面选择一条记录来填充这个记录，示例如下：
| CREATE OR REPLACE TYPE zipcode_obj_type AS OBJECT (zip VARCHAR2(5), city VARCHAR2(25), state VARCHAR2(2), created_by VARCHAR2(30), created_date DATE, modified_by VARCHAR2(30), modified_date DATE); / DECLARE v_zip_obj zipcode_obj_type; BEGIN SELECT zipcode_obj_type(zip, city, state, null, null, null, null) INTO v_zip_obj FROM zipcode WHERE zip = '06883'; DBMS_OUTPUT.PUT_LINE ('Zip: '||v_zip_obj.zip); DBMS_OUTPUT.PUT_LINE ('City: '||v_zip_obj.city); DBMS_OUTPUT.PUT_LINE ('State: '||v_zip_obj.state); END; / |
我们运行一下这段代码试试看，这次没有那么幸运了，运行出错了，错误如下：
图 3.需要修改的迁移运行结果
![]()
查一下 DB2 9.7 的参考文档我们可以发现这样一段描述 :“record 类型只支持在 create package 或者 create package body 语句中定义”，所以我们要使得这一段程序可以运行的话，必须把它们包装在 package 中，我们现在把它包装在一个名字叫做的 pkg_type 的 package 中，示例程序如下：
| CREATE OR REPLACE PACKAGE pkg_type IS PROCEDURE test_type(zip_code varchar2(5)); end; / CREATE OR REPLACE PACKAGE BODY pkg_type IS TYPE zipcode_obj_type is record (zip VARCHAR2(5), city VARCHAR2(25), state VARCHAR2(2), created_by VARCHAR2(30), created_date DATE, modified_by VARCHAR2(30), modified_date DATE); v_zip_obj zipcode_obj_type; PROCEDURE test_type(zip_code varchar2(5)) as BEGIN SELECT zipcode_obj_type(zip, city, state, null, null, null, null) INTO v_zip_obj FROM zipcode WHERE zip = zip_code; DBMS_OUTPUT.PUT_LINE ('Zip: '||v_zip_obj.zip); DBMS_OUTPUT.PUT_LINE ('City: '||v_zip_obj.city); DBMS_OUTPUT.PUT_LINE ('State: '||v_zip_obj.state); END; END; / call pkg_type.test_type('06883'); / |
运行一下，看看结果怎么样：
图 4.修改后仍然报错的迁移示例运行结果
![]()
还是有错误！！！ DB2 不能识别 zipcode_obj_type 作为构造函数的语法，没有办法，我们只有构造一个 cursor 来重写程序，示例如下：
| CREATE OR REPLACE PACKAGE pkg_type IS PROCEDURE test_type(zip_code varchar2(5)); end; / CREATE OR REPLACE PACKAGE BODY pkg_type IS t_zip_max INTEGER := 1; TYPE zipcode_obj_type IS TABLE of zipcode%ROWTYPE INDEX BY BINARY_INTEGER; v_zip_obj zipcode_obj_type; PROCEDURE test_type(zip_code varchar2(5)) as CURSOR zip_cur IS SELECT * FROM zipcode WHERE zip = zip_code; BEGIN OPEN zip_cur; LOOP FETCH zip_cur INTO v_zip_obj(t_zip_max); EXIT WHEN zip_cur%NOTFOUND; t_zip_max := t_zip_max + 1; END LOOP; CLOSE zip_cur; t_zip_max := t_zip_max - 1; FOR i IN 1..t_zip_max LOOP DBMS_OUTPUT.PUT_LINE ('Zip: '||v_zip_obj(i).zip); DBMS_OUTPUT.PUT_LINE ('City: '||v_zip_obj(i).city); DBMS_OUTPUT.PUT_LINE ('State: '||v_zip_obj(i).state); END LOOP; END; END; / call pkg_type.test_type('06883'); / |
运行一下看看，这会没有错误了，结果如下：
图 4.再次修改后的迁移示例运行结果
![]()
回页首
DB2 9.7 支持的 Oracle PL/SQL 特性的范围
从上面的例子可以看到，DB2 9.7 版本并不是支持 Oracle 所有的特性的，那么这个版本究竟有哪些限制呢？从 DB2 参考文档中我们可以看到现在有以下限制：
- 下列产品版本不支持 PL/ SQL 语句的编译和执行：
- PL/SQL 函数和触发器不能创建在分区数据库环境。
- 当数据库没有定义为一个 Unicode 数据库的时候，NCLOB 数据类型不支持用在 PL/SQL 语句中。在定义为 Unicode 的数据库中，NCLOB 数据类型映射到 DB2 DBCLOB 数据类型。
- XMLType 数据类型不支持。
- 类型声明中不支持函数，过程，触发器或匿名块。
- FOR EACH 语句不支持的 PL/SQL 触发器。
从列表中我们可以看到，基本上 Oracle 数据库中经常要使用的特性这个版本都支持了，甚至一些比较新的特性也支持了，唯一让人感到失望的是不支持在 package 之外定义类型变量，但是这在企业应用中也不是什么大问题，因为我们程序基本上都会定义在 package 当中的。
回页首
结束语
通过本文的介绍 , 我们可以看到 DB2 9.7 对 Oracle PL/SQL 特性的支持已经达到一个很高的程度，这对有许多 PL/SQL 遗留程序，现在又需要迁移到 DB2 数据库中来的企业是个福音，因为许多程序不需要修改就可以直接在 DB2 中运行，即使需要修改，工作量也比较小了，这使得我们的迁移的工作量降到了最低，但是我们在迁移之前还是需要注意现在版本还不支持的特性，提早做出规划，以便于我们更好，更快的进行迁移工作。
参考资料
学习
获得产品和技术
- 使用可直接从 developerWorks 下载的 IBM 产品评估试用软件 构建您的下一个开发项目。
- 现在可以免费使用 DB2。下载 DB2 Express-C，这是为社区提供的 DB2 Express Edition 的免费版本，它提供了与 DB2 Express Edition 相同的核心数据特性，为构建和部署应用程序奠定了坚实的基础。
讨论
关于作者
史明春，IBM CDL 软件工程师，有内容管理解决方案的经验，熟悉内容管理的基本技术，IBM Content Manager 相关产品基本理论。 |
|
IP卡
狗仔卡
发表于 2011-3-25 16:02
收藏
提升卡
置顶卡
变色卡
显身卡
发表于 2011-3-31 00:41
楼主
