Oracle to DB2 v9.7 Migration (2)
验证结果。
1、 视图
db2 select * from viewname
db2 select * from viewname
2、 函数
db2 values funcname (…)
db2 values funcname (…)
3、 存储过程
db2 call procname (…)
3.1 含有%ROWTYPE类型的变量
调用脚本:
Declare
AA …%TYPE;
Begin
Funcschema.funcname(AA);
End;
/
调用脚本:
Declare
AA …%TYPE;
Begin
Funcschema.funcname(AA);
End;
/
4、 package
分别验证包中的func和proc。
分别验证包中的func和proc。
4.1 function中不允许有out参数。将function改为procedure;func中的return变量值写入procedurename(..)的变量中,定义成out变量参数。另外,其他原来调用该func的地方都要改成调用proc。
4.2 含有%ROWTYPE参数类型
调用脚本:函数需要有return的变量值。
Declare
P_RECORD TM_BILLING_SUBT_TAX_FEE_TEMP%ROWTYPE;
Output varchar2(100);
Begin
Output := PKG_SYNC_SUB_TAXT_FEE.FNT_GET_STATE(P_RECORD);
end;
/
调用脚本:函数需要有return的变量值。
Declare
P_RECORD TM_BILLING_SUBT_TAX_FEE_TEMP%ROWTYPE;
Output varchar2(100);
Begin
Output := PKG_SYNC_SUB_TAXT_FEE.FNT_GET_STATE(P_RECORD);
end;
/
4.3 FOR R1 IN (SELECT *
FROM TM_BILLING_COMP_WAYBILL_TEMP WT
WHERE WT.PROCESS_NO = L_PROCESS_NO) LOOP
改为:
cursor c1 is select * from TM_BILLING_SUBT_TAX_FEE_TEMP WT WHERE WT.PROCESS_NO = L_PROCESS_NO;
R1 TM_BILLING_COMP_WAYBILL_TEMP%ROWTYPE;
FROM TM_BILLING_COMP_WAYBILL_TEMP WT
WHERE WT.PROCESS_NO = L_PROCESS_NO) LOOP
改为:
cursor c1 is select * from TM_BILLING_SUBT_TAX_FEE_TEMP WT WHERE WT.PROCESS_NO = L_PROCESS_NO;
R1 TM_BILLING_COMP_WAYBILL_TEMP%ROWTYPE;
cursor c1 is select * from TM_BILLING_SUBT_TAX_FEE_TEMP WT WHERE WT.PROCESS_NO = L_PROCESS_NO;
R1 TM_BILLING_COMP_WAYBILL_TEMP%ROWTYPE;
R1 TM_BILLING_COMP_WAYBILL_TEMP%ROWTYPE;
4.4 时间戳记冲突
Proc中调用proc1,则proc1应在porc之前定义。
Proc中调用proc1,则proc1应在porc之前定义。
4.5 对于p_waybill_records BAR_RECORD_SET;这种类型的变量无法在脚本中调用,因为该类型找不到。该类型是在package头中定义的。所以直接在用到该变量的package_body中的procedure里再写一个测试用的procedure来验证该package_body中的procedure是调用成功的。例如:
----------------test
procedure IRENETEST(
p_statement IN OUT NOCOPY TL_EXCEPTION_LOG.LINE_NO%TYPE,
p_bar_record IN TT_BAR_EXPORT_WAYBILL%ROWTYPE,
p_waybill_records IN OUT NOCOPY BAR_RECORD_SET
)is
BEGIN
STP_BAR_WAYBILL_LOAD_BILL(p_statement,p_bar_record,p_waybill_records);
STP_BAR_WAYBILL_LOAD_CAR(p_statement, p_bar_record, p_waybill_records);
STP_BAR_WAYBILL_LOAD_PACKAGE(p_statement,p_bar_record, p_waybill_records);
END IRENETEST;
-----------------endtest
----------------test
procedure IRENETEST(
p_statement IN OUT NOCOPY TL_EXCEPTION_LOG.LINE_NO%TYPE,
p_bar_record IN TT_BAR_EXPORT_WAYBILL%ROWTYPE,
p_waybill_records IN OUT NOCOPY BAR_RECORD_SET
)is
BEGIN
STP_BAR_WAYBILL_LOAD_BILL(p_statement,p_bar_record,p_waybill_records);
STP_BAR_WAYBILL_LOAD_CAR(p_statement, p_bar_record, p_waybill_records);
STP_BAR_WAYBILL_LOAD_PACKAGE(p_statement,p_bar_record, p_waybill_records);
END IRENETEST;
-----------------endtest
5、针对@ dblink的修改
创建联邦的脚本:
create server: 在=>提示符下用db2inst1建
create server sf1 type DB2/UDB version '9.7' wrapper "DB2" authid "db2int1" password “db2inst1" options (add dbname 'sf1', password 'Y')
create server: 在=>提示符下用db2inst1建
create server sf1 type DB2/UDB version '9.7' wrapper "DB2" authid "db2int1" password “db2inst1" options (add dbname 'sf1', password 'Y')
create user_mapping; 在=>提示符下用db2inst1建
create user mapping for "exp5" server sf1 options (add remote_authid 'exp5', add remote_password 'password');
create nickname: 用exp5建
db2 "create or replace nickname exp5_2.customer for sername.exp5.customer";
from:http://mydatacool.spaces.live.com/blog/cns!E07D9F640D30893D!135.entry
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/665930/viewspace-682793/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/665930/viewspace-682793/