实践Oracle与DB2区别及问题解决
项目进入开发阶段的时候,为了方便,一直使用Oracle数据库进行开发, 所以很多sql语句都是在oracle能正常创建的,后期由于项目中嵌入了IBM的产品及其他因素,所以不得不使用db2数据库,切换数据库过程中的区别还是有点大,如:创建表、视图、存储过程、Ibatis支持等等、、、,下面就总结一下我从Oracle数据库切换到DB2数据库碰到的一些问题及如何解决。
1. 创建表字段类型的区别<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />
|
创建表区别基本上就这些了,创建表时根据区别修改一下字段类型基本上不会大的问题。
1. 创建视图区别
创建视图的时候不能使用 OR REPLACE ,不能这样使用排序语句
CREATE
VIEW VIEW_SSC AS
SELECT DATE_TIME FROM JBPM4_TASK TB ORDER BY TB. DATE DESC
2. 创建存储过程区别
a)
参数类型和参数名称的位置不同
db2: CREATE PROCEDURE PRO1 (IN OrgID int)
oracle:CREATE PROCEDURE PRO1 (OrgID IN int)
b)
同时作为输入输出参数的写法不同
db2: CREATE PROCEDURE PRO1 (INOUT OrgID int) INOUT连着写
oracle:CREATE PROCEDURE PRO1 (OrgID IN OUT int) IN OUT中间空格隔开,而且必须IN在OUT之前
c)
没有输入或输出参数时
db2: CREATE PROCEDURE PRO1 ()
oracle:CREATE PROCEDURE PRO1 不能有空的括号
d)
赋值语句写法不一样
db2: set var =..
oracle: var := ..
e) 异常处理不一样
f) CREATE PROCEDURE
db2:
CREATE PROCEDURE SSCDBUSER.INDI_DEL_PRO
(IN IDPARA VARCHAR(32), OUT RETURNPARA INTEGER)
LANGUAGE SQL
DYNAMIC RESULT SETS 1
DETERMINISTIC
BEGIN
DECLARE COUNTID INTEGER;
DECLARE APPLYNOD VARCHAR(20);
DECLARE CATEGORYD VARCHAR(4);
SELECT COUNT(ID) INTO COUNTID FROM VIEW_SSC_INDIVIDUALFORMLIST WHERE ID=IDPARA;
SET RETURNPARA = COUNTID;
SELECT APPLY_NO, CATEGORY INTO APPLYNOD, CATEGORYD FROM VIEW_SSC_INDIVIDUALFORMLIST WHERE ID=IDPARA;
IF CATEGORYD= '1001' THEN
DELETE FROM SSC_EVECT_LOAN_INFO WHERE ID=IDPARA;
ELSEIF CATEGORYD= '1002' THEN
DELETE FROM SSC_DAILY_LOAN_INFO WHERE ID=IDPARA;
DELETE FROM SSC_DAILY_LOAN_DETAIL_INFO WHERE ID=APPLYNOD;
ELSEIF CATEGORYD= '1003' THEN
DELETE FROM SSC_EVECT_EXPENSE_INFO WHERE ID=IDPARA;
DELETE FROM SSC_EVECT_EXPENSE_DETAIL_INFO WHERE ID=APPLYNOD;
ELSEIF CATEGORYD= '1004' THEN
DELETE FROM SSC_DAILY_EXPENSE_INFO WHERE ID=IDPARA;
DELETE FROM SSC_DAILY_EXPENSE_DETAIL_INFO WHERE ID=APPLYNOD;
END IF;
END
oracle:
CREATE OR REPLACE PROCEDURE "INDI_DEL_PRO" (IDPARA IN VIEW_SSC_INDIVIDUALFORMLIST.ID%TYPE, RETURNPARA OUT NUMBER)
AS
APPLYNO VIEW_SSC_INDIVIDUALFORMLIST.APPLY_NO%TYPE;
CATEGORY VIEW_SSC_INDIVIDUALFORMLIST.CATEGORY%TYPE;
BEGIN
SELECT COUNT(*) INTO RETURNPARA FROM VIEW_SSC_INDIVIDUALFORMLIST WHERE ID=IDPARA;
SELECT APPLY_NO, CATEGORY INTO APPLYNO,CATEGORY FROM VIEW_SSC_INDIVIDUALFORMLIST WHERE ID=IDPARA;
IF CATEGORY= '1001'THEN
DELETE FROM SSC_EVECT_LOAN_INFO WHERE ID=IDPARA;
ELSIF CATEGORY= '1002'THEN
DELETE FROM SSC_DAILY_LOAN_INFO WHERE ID=IDPARA;
DELETE FROM SSC_DAILY_LOAN_DETAIL_INFO WHERE ID=APPLYNO;
ELSIF CATEGORY= '1003'THEN
DELETE FROM SSC_EVECT_EXPENSE_INFO WHERE ID=IDPARA;
DELETE FROM SSC_EVECT_EXPENSE_DETAIL_INFO WHERE ID=APPLYNO;
ELSIF CATEGORY= '1004'THEN
DELETE FROM SSC_DAILY_EXPENSE_INFO WHERE ID=IDPARA;
DELETE FROM SSC_DAILY_EXPENSE_DETAIL_INFO WHERE ID=APPLYNO;
END IF;
END;
3. 如果工程是有用到Ibatis, 那么Ibatis中sql语句写法也有些区别
a) Ibatis支持oracle数据库sql中可以带*号查询,db2不能使用select * from tablename;这是最主要的。
b) Ibatis支持oracle空值插入、更新,但是如果是db2 映射值传空的话要必须要加上对应的字段类型
如:# remark:VARCHAR#
remark是属性值,VARCHAR是数据库中对应的字段类型。
总结了一下,方便以后查阅,同时也希望对看到这篇博客的朋友一些帮助,因为最近在解决这些问题时也花了不少时间。
-
本文附件下载:
- 实践Oracle与DB2区别及问题解决.rar (44.5 KB)
已有 0 人发表留言,猛击->> 这里<<-参与讨论
ITeye推荐