oracle sqlserver 存储过程迁移映射.docx
《oracle sqlserver 存储过程迁移映射.docx》由会员分享，可在线阅读，更多相关《oracle sqlserver 存储过程迁移映射.docx（17页珍藏版）》请在冰豆网上搜索。
oraclesqlserver存储过程迁移映射
存储过程移植约定
为使移植过程更正确、迅速，建议将移植过程规范化处理，以便小组内人员移植时有据可循，使代码宜于相互交流、维护管理。
初步设想分三大步：
1、自动预处理：
删除多余的行、直接替换等，可编写宏完成；
2、半手工半自动处理：
对各种语句模式进行转换，可编写程序完成；
3、手工处理。
应尽量设法增加自动和半自动的处理，减少手工处理，以加快速度。
以下分别说明之：
一、自动预处理：
1、删除多余的行
类型
mssql存储过程
备注
tab转换
将每个tab转换为两个空格
方便后续处理
截尾
删除每条语句后的多余空格
方便后续处理
切换数据库语句
usesql2k
userun2k
usehis2k
usefil2k
usecrm2k
useoragan2k
usetmp2k
usemaster
…
存储过程、表都在同一表空间，无需切换
提示信息
print‘sp_…’
无对应语句
判断存储过程存在性
ifexists（selectnamefromsysobjectswherename=…
因oracle创建时可同时替换已存在储存过程，无需此句
删除存储过程
dropprocedure…
同上
设置环境变量语句
setnocounton/off
setrowcount0
settransactionisolationlevelreaduncommitted
settransactionisolationlevelreadcommitted（默认就是committed）
无对应语句
settransaction先注释保留
setrowcount非0的要保留，使用rownum代替后注释。
授权语句
grantallon…tohandsome
释放游标
deallocate*cusr
连续两行go，可删除一行
因这些情况为删除其它语句形成，故在最后处理，注意此时go还未替换为/
注释后有一行go，可删除一行go
2、直接替换项
类型
mssql
oracle
备注
变量前缀
@开头
替换为p_开头
全局变量@@rowcount等在后面做特殊处理
字符型变量类型
char（
varchar（
varchar2（
为简化空值、逻辑判断处理和提高存储性能、查询效率
go换行符
/换行符
如无换行符则可能将其它go替换掉
数据库引用
run2k..
sql2k..
空串
创建存储过程语句
create空格procedure
createorreplacefunction
加密语句
with空格encryption
returnnumber;
无对应项
变量声明语句
declare空格
空串
注意sqlserver语句体内也可声明变量，需一起移到声明段
插入语句
insert空格…
insertinto…
需注意可能原来就是insertinto的情况
事务开始语句
begintransaction/tran
空串或注释掉
回滚语句
rollbacktransaction/tran
rollback;
事务提交语句
committransaction/tran
commit;
回车换行符
char（13）
chr（13）
字符串相加
+，如：
’+
+‘
替换为||，如：
‘||
||‘
注意不能把数值计算的+替换掉
空字串
‘’
‘‘
因在oracle中空字串为空值，原默认值空字串替换为空格。
返回错误信息
p_error_info
pi_error_info
p_error_info只用于返回错误信息
常见函数替换
elseif
elsif
end回车换行
endif;回车换行
因end一般为配合if使用，故作此处理，其它如while语句后的end特殊处理
空值函数
isnull（…）
nvl（…）
取子串
substring（…）
substr（…）
oracle中起始位0和1等效
字串长度
len（…）
datalength（…）
length（…）
不要直接替换len，防止变量中出现len
最小整数
ceiling（…）
ceil（...）
循环退出
break
exit
字段保留字
空格date,
空格online,
空格date_,
空格online_,level
须大小写匹配，防止将自定义类型Date替换掉
整除取余数
空格%空格
空格mod空格
游标状态
p_p_fetch_status
（游标名）%found
游标名需手工处理
二、半手工半自动处理
模式语句处理
类型
语句单元
oracle
备注
入口参数单元处理
范围为createorreplace到as之间语句体
工具处理
1）入口参数名称
@变量1类型1，
@变量2类型2output，
@变量3类型3=缺省值，
…
pi_变量1类型1,
pi_变量2类型2output,
pi_变量3类型3:
=缺省值,
…
1）输入、输出参数改为pi_开头，输出游标p_cursor例外，仍可p_开头；
2）输入输出参数改为inout，pi_error_info例外，可定义为out型
3）若没有输出参数，增加p_cursor参数：
p_cursorouthstype.t_cursor；函数末尾仍需增加返回值0
4）需进行类型转换，新类型无长度声明，原长度保留在注释中备查
5）参数表前后增加括号（）
6）缺省值赋值由=改为:
=
7）inout类型参数不允许缺省值。
2）参数表类型转换
integer
int
smallint
tinyint
numeric
number
没有长度声明
char（长度）
char
没有长度声明
varchar（长度）
varchar2
没有长度声明
自定义类型
hstype.自定义类型%type
局部变量声明单元处理
可由工具完成
1）已有变量的类型转换
integer、int
number（12）
smallint
number（8）
tinyint
number（8）
numeric
number
char
char
varchar
varchar2
自定义类型
hstype.自定义类型%type
2）增加入口参数对应的局部变量声明
p_变量n类型n（长度）
1）输入参数pi_在语句体内不可变更，需传递一份给p_参数，为方便起见，在语句体开始时将所有pi_变量复制一份给p_变量，pi_error_info例外。
在返回成功return0前，将p_变量回传给pi_。
2）输入参数p_向pi_转换时，不计类型长度，故需在输入参数向入口pi转换同时用原码转换。
3）有exists语句时增加一个v_count变量
3）局部变量类型、缺省值
@变量1类型1，
@变量2类型2=缺省值，
p_变量1类型1;
p_变量2类型2:
=缺省值;
1）以分号;结尾
2）缺省值赋值为:
=
返回正常前
在返回成功return0前，将p_变量回传给pi_，pi_error_info例外
游标初值
如果有游标返回，则增加游标初值：
openp_cursorforselect*fromdualwhere1=2;
若无初值，提前返回时报ora03113错
变量赋值单元语句处理
select变量1＝值1,
变量2＝值2,
…
变量1:
＝值1;
变量2＝值2;
…;
1）select替换为空串
2）=替换为:
=
3）各子句的分隔符,替换为分号;
4）在结尾处加分号;
存在型语句单元处理
因存在型语句也是if型语句，故需先于if型语句处理
ifexists/notexists（select..from..where..）
begin
dosth.
end
（必须有begin、end，以便切分语句单元处理）
selectcount（*）
intov_count
from…
--exists
ifv_count>0then
dosth;
endif;
--notexists
ifv_count=0then
dosth;
endif;
注意：
1改用记录数变通处理
2用于大表时，在where语句中增加rownum=1的限定
2增加exists及notexists注释，以便核对于测试
if语句单元处理
if…
begin
end
if…then
…
endif;
1）if行后加then
2）语句后加分号；
3）不采用块控制，去除begin、end行
if…
dosth.
if…then
dosth.
endif;
if…
elseif…
if…then
elsif…then
endif;
存储过程调用单元处理
exec返回码变量=存储过程名
p_变量1＝值1，
p_变量2＝值2output，
…
返回码变量:
=存储过程名
pi_变量1＝>值1，
pi_变量2＝>值2，…;
1）exec替换为空串
同行的=替换为:
=，
2）按参数名传递时，前面的p_改为pi_，=替换为=>。
按位置传递则无处理。
3）有output则去掉
4）参数表需用括号括起来，结尾加;
5）如为out参数，不可直接赋予null值
插入insert异常处理
insert…
if@@error!
=0or@@rowcount!
=1
…
begin
insertinto...;
exception
whenothersthen
.....
end;
1、插入字段如有保留字，需替换，如date改为date_
2、如从其它表中取数据插入，即以insert…select形式时，注意没取到数据时，insert不触发异常！
因此，如果不允许该情况,则自定异常p_error。
更新update异常处理
update…
if@@error!
=0or@@rowcount!
=1
…
begin
update...where....;
ifsql%rowcount>1then
raisep_error;
endif;
exception
whenothersthen
.....
end;
修改记录为0条或多条时不触发异常。
因此，如果不允许该情况,则自定异常p_error。
变量select赋值及异常处理
select变量1＝字段名1,
变量2＝字段名2,
…
from…
where…
if@@error!
=0or@@rowcount!
=1
begin
select字段1into变量1,
字段2into变量2,
…
from...
where...；
exception
whenothersthen
…
end;
必须有例外处理，否则无数据时会返回异常（atendoftable）
语句错误判断
ifp_p_error!
=0或
ifp_p_error!
=0orp_p_rowcount!
=1then
exception回车换行
whenothersthen
例外处理
定位函数
charindex（substr,str）
替换为instr（str,substr）
参数位置互换
三、手工处理
类型
mssql
oracle
备注
字符串相加
convert（char（5）,p_op_branch_no）
替换为p_op_branch_no，可以根据行中是否有||自动将多余字符去除
oracle中字符串连接时不需转换为字符
产生空格函数
space（空格长度n）
左填充函数：
lpad（字符串，总长度，填充符）
右填充函数：
rpad（字符串，总长度，填充符）
需转换为总长度，填充符忽略时为空格
替换函数
stuff（字符串,起始位,替换长度,新字符串）
replace（字符串,搜索子串,新字符串）
注意并不等价，在oracle中如不指定新字符串，则为删除搜索子串，即替换为空
左子串
left（字符串，长度）
substr（字符串，开始位，长度）
oracle中的长度>0
右子串
rigth（字符串，长度）
substr（字符串，开始位，长度）
oracle中的长度<0
日期函数
日期加
dateadd
（日期列+/-值）或add_months
日期间的间隔
datediff，如：
datediff（dd,convert（datetime,convert（char（8）,p_integral_update））,getdate（））;
直接用日期相减，如：
trunc（sysdate）-to_date（p_date,’yyyymmdd’）
注意：
date类型包括时间在内，如果不截取，两个日期之间的天数是带小数的。
当前日期和时间
getdate（）
替换为sysdate
和datepart等结合使用时，需特殊处理
日期到字符
datepart
to_char（p_date,’yyyymmdd’）
字符到日期
to_date（date_str,’yyyymmdd’）
日期到整数
datepart
to_number（to_char（p_date,’yyyymmdd’））
类型转换函数
数字到字符
convert（字符类型，数字变量）
to_char（数字变量）
字符到数字
convert（数字类型，字符变量）
to_number（字符变量）
日期到字符
convert（字符类型，日期变量）
to_char（日期变量）
字符到日期
convert（日期类型，字符变量）
to_date（字符变量）
字符转换函数
str（）
to_char（）
部分存储过程缺乏局部变量
1）有excists语句时，需新增v_countnumber（12）；
2）有例外处理时，需新增e_errorexception
变量声明后
变量声明后加begin
存储过程结束前
如果是返回游标，则增加返回成功返回码
存储过程结束时
结束时，/换行符前加
end存储过程名;
开关语句
case语句：
如case
whens=’a’andb=’b’then‘a’
else‘b’end
多重decode进行0和1的相乘、相加，完成case语句中的逻辑运算；如：
decode（decode（s,’a’,1,0）*decode（b,’b’,1,0）,1,’a’,’b’）。
即and为decode相乘=1，or为decode相加>1;
动态过程调用
if…
select@proc='sp_1'
else
select@proc='sp_2'
exec@proc
if…
selectp_proc='sp_1'
else
selectp_proc='sp_2'
endif;
ifp_proc='sp_1'then
sp_1;
else
sp_2;
endif;
记录条数判断
if@@rowcount=0
ifsql%rowcount=0
错误以结果集返回
selectErrorNo=@error_no,ErrorInfo=@error_info
openp_cursorforselectp_error_noasErrorNo,pi_error_infoasErrorInfofromdual;
返回指定行数结果集
setrowcount@num
select…
where…
ifp_num=0then
p_num:
=99999999;
endif;
select…
where…
andrownum<=p_num;
结果集返回
select…
openp_cursorfor
select…
四、注意事项：
1、在pl/sql中空字符串’’为null，null值在插入到表中时要注意是否有非null限制，在逻辑判断时要特别注意；尤其在测试时，一些问题均由此引起。
但是若将空字符串赋给char型变量时，与将null值赋给char型变量是不同的，前者赋值后不为null；如：
若表hsa的一个字段c为char（10）型且不允许为null，则不能：
insertintohsa（c）values（‘’）;因为’’为null;
但是若变量p_cchar（10）,且p_c:
=’’;则可以
insertintohsa（c）values（p_c）;
2、char型变量，空串‘’不是null，而是相当于赋相应长度的空格！
！
除非把null值的变量赋给它。
所以在字符串逻辑判断处，若有isnull，最好将其类型为varchar2，以便使空串’’为null；我们的存储过程的输入型字符串参数，一般定义一个相应的局部变量，类型与输入参数相同，但是如果此变量要做isnull判断，则其类型应改为varchar2;如：
p_schar[8];
若p_s:
=’’,则p_s不为null;且length（p_s）=8;但是不能作p_s=’’的逻辑判断而应是作p_s=’‘的逻辑判断（8个空格）。
若p_s:
=null，则p_s为null，length（p_s）也为null（注意不为0）。
若p_s:
=pi_s,而pi_s作为输入参数为null，则相当于p_s:
=null;
注意对于输入参数pi_s，不管其类型为varchar2,还是char,若为空则都为null;
p_svarchar2[10];
若p_s:
=’’,则p_s为null;
若p_cchar（10）;p_c:
=’’;p_s:
=p_c;则p_s不为null，长度为10;将某表char型字段值（为’’,而非null）赋给p_s，也同效。
对于trim（）函数，若去空后后为’’，则都为null（trim函数返回为varchar2）;
对于length（）函数，若字符串为’’或null,则返回值都为null;但对于char型则为声明的长度。
为简化空值处理和逻辑判断处理，并参照其它系统做法，本系统的字符型数据全部采用varchar2型。
3、oracle的insertinto,update操作,必须显式commit，否则会引起死锁或回滚;
4、备份事务功能的代码段可注释保留，由硬件来实现。
5、插入语句如以分号结尾，无需再加/，否则会再执行上一次语句，这在hsdata.sql中需注意。
6、处理以下功能号的存储过程，需对返回的date_字段作特殊处理
303、1572、2023、2024、2098、2271、2272、3007、3008、3427、3440、3441、3482、3617、3711、3712、4032
五、格式约定：
1、缩排格式
·两空格缩排
·方法的括号单独成行，并且与名称行一样缩排，例如：
createorreplacefunctiontestfuntion
（
param1...
param2...
）
returnnumber
as
2、大小写
为便于阅读和代码维护、管理，并根据以前的实践经验，一般都采用小写。
以下情况例外：
1）自定义类型首字母大写。
2）在对象插入hsobjects时，存储过程名称用大写，避免与ORACLE的数据字典中不一致？
（暂不使用该条）
3、注释
1）单行注释采用—
2）多行注释采用/**/
3）注释不许嵌套
4．路径及文件名
存储过程根目录为hsplatora/sqlsrc
该目录下按原企业版结构建立子目录。
源文件名：
hsplatform/zjzqsql/子目录/xxx.sql
宏处理后文件名：
hsplatora/sqlsrc/子目录/xxx_mac.sql
工具软件处理后文件名：
hsplatora/sqlsrc/子目录/xxx0_pre.sql，可分割为xxx0_pre.sql、xxx1_pre.sql…
处理结果文件名：
：
hsplatora/sqlsrc/子目录/xxx0_ora.sql
升级会员 