oracle sqlserver 存储过程迁移映射

1、存储过程移植约定为使移植过程更正确、迅速，建议将移植过程规范化处理，以便小组内人员移植时有据可循，使代码宜于相互交流、维护管理。初步设想分三大步：1、 自动预处理：删除多余的行、直接替换等，可编写宏完成；2、 半手工半自动处理：对各种语句模式进行转换，可编写程序完成；3、 手工处理。应尽量设法增加自动和半自动的处理，减少手工处理，以加快速度。以下分别说明之：一、 自动预处理：1、删除多余的行类型ms sql 存储过程备注tab转换将每个tab转换为两个空格方便后续处理截尾删除每条语句后的多余空格方便后续处理切换数据库语句use sql2kuse run2kuse his2kuse fil2ku

2、se crm2kuse oragan2kuse tmp2kuse master存储过程、表都在同一表空间，无需切换提示信息print sp_无对应语句判断存储过程存在性if exists(select name from sysobjects where name =因oracle创建时可同时替换已存在储存过程，无需此句删除存储过程drop procedure同上设置环境变量语句set nocount on/offset rowcount 0set transaction isolation level read uncommittedset transaction isolation lev

3、el read committed（默认就是committed）无对应语句set transaction先注释保留set rowcount 非0的要保留，使用rownum代替后注释。授权语句grant all onto handsome释放游标deallocate*cusr连续两行go，可删除一行因这些情况为删除其它语句形成，故在最后处理，注意此时go还未替换为/注释后有一行go，可删除一行go2、直接替换项类型ms sql oracle备注变量前缀开头替换为p_开头全局变量rowcount等在后面做特殊处理字符型变量类型char(varchar(varchar2(为简化空值、逻辑判断处理和提

4、高存储性能、查询效率go换行符/换行符如无换行符则可能将其它go替换掉数据库引用run2k.sql2k.空串创建存储过程语句create空格procedurecreate or replace function加密语句with空格encryptionreturn number ;无对应项变量声明语句declare空格空串注意sql server语句体内也可声明变量，需一起移到声明段插入语句insert空格insert into 需注意可能原来就是insert into的情况事务开始语句begin transaction/ tran空串或注释掉回滚语句rollback transaction/

5、tranrollback;事务提交语句commit transaction/ trancommit;回车换行符char(13chr(13字符串相加+，如： + 替换为|，如： | 注意不能把数值计算的+替换掉空字串 因在oracle中空字串为空值，原默认值空字串替换为空格。返回错误信息p_error_infopi_error_infop_error_info只用于返回错误信息常见函数替换else ifelsifend回车换行end if;回车换行因end一般为配合if使用，故作此处理，其它如while语句后的end特殊处理空值函数isnull(nvl(取子串substring(substr(o

6、racle中起始位0和1等效字串长度len(datalength(length(不要直接替换len，防止变量中出现len最小整数ceiling(ceil(.循环退出breakexit字段保留字空格date,空格online,空格date_,空格online_,level须大小写匹配，防止将自定义类型Date替换掉整除取余数空格%空格空格mod空格游标状态p_p_fetch_status（游标名）%found游标名需手工处理二、半手工半自动处理模式语句处理类型语句单元oracle备注入口参数单元处理范围为create or replace到as之间语句体工具处理1）入口参数名称变量1 类型1，变

7、量2 类型2 output，变量3 类型3 =缺省值，pi_变量1 类型1,pi_变量2 类型2 output,pi_变量3 类型3 :=缺省值,1）输入、输出参数改为pi_开头，输出游标p_cursor例外，仍可p_开头；2）输入输出参数改为in out，pi_error_info例外，可定义为out型3）若没有输出参数，增加p_cursor 参数：p_cursor out hstype.t_cursor；函数末尾仍需增加返回值04）需进行类型转换，新类型无长度声明，原长度保留在注释中备查5）参数表前后增加括号（）6）缺省值赋值由=改为:=7）in out类型参数不允许缺省值。2）参数表类型

8、转换integerintsmallinttinyintnumericnumber没有长度声明char（长度）char没有长度声明varchar（长度）varchar2没有长度声明自定义类型hstype. 自定义类型%type局部变量声明单元处理可由工具完成1）已有变量的类型转换integer、intnumber(12smallintnumber(8tinyintnumber(8numericnumbercharcharvarcharvarchar2自定义类型hstype. 自定义类型%type2）增加入口参数对应的局部变量声明p_变量n 类型n（长度）1）输入参数pi_在语句体内不可变更，需传

9、递一份给p_参数，为方便起见，在语句体开始时将所有pi_变量复制一份给p_变量，pi_error_info例外。在返回成功return 0前，将p_变量回传给pi_。2）输入参数p_向pi_转换时，不计类型长度，故需在输入参数向入口pi转换同时用原码转换。3）有exists语句时增加一个v_count变量3）局部变量类型、缺省值变量1 类型1，变量2 类型2 =缺省值，p_变量1 类型1;p_变量2 类型2 :=缺省值;1）以分号;结尾2）缺省值赋值为:=返回正常前在返回成功return 0前，将p_变量回传给pi_，pi_error_info例外游标初值如果有游标返回，则增加游标初值：ope

10、n p_cursor for select * from dual where 1=2;若无初值，提前返回时报ora03113错变量赋值单元语句处理select 变量1值1,变量2值2,变量1:值1;变量2值2;1）select替换为空串2）=替换为:=3）各子句的分隔符, 替换为分号;4）在结尾处加分号;存在型语句单元处理因存在型语句也是if型语句，故需先于if型语句处理if exists /not exists ( select . from . where .begindo sth.end(必须有begin、end，以便切分语句单元处理select count(* into v_coun

11、tfrom-existsif v_count>0 then do sth;end if;-not existsif v_count=0 then do sth;end if;注意：1改用记录数变通处理2用于大表时，在where语句中增加rownum=1的限定2增加exists及not exists注释，以便核对于测试if语句单元处理if beginendif thenend if;1）if行后加then2）语句后加分号；3）不采用块控制，去除begin、end行ifdo sth.if thendo sth.end if;if else if if thenelsif thenend if

12、;存储过程调用单元处理exec 返回码变量=存储过程名 p_变量1值1，p_变量2值2 output，返回码变量:=存储过程名pi_变量1> 值1，pi_变量2> 值2，;1）exec 替换为 空串同行的= 替换为 :=，2）按参数名传递时，前面的p_改为pi_，= 替换为 =>。按位置传递则无处理。3）有output 则去掉4）参数表需用括号括起来，结尾加;5）如为out参数，不可直接赋予null值插入insert异常处理insert if error != 0 or rowcount != 1begininsert into . ;exceptionwhen others

13、 then.end;1、插入字段如有保留字，需替换，如date改为date_2、如从其它表中取数据插入，即以insertselect形式时，注意没取到数据时，insert不触发异常！因此，如果不允许该情况,则自定异常p_error。更新update异常处理update if error != 0 or rowcount != 1beginupdate .where . ;if sql%rowcount>1 thenraise p_error; end if;exceptionwhen others then.end;修改记录为0条或多条时不触发异常。因此，如果不允许该情况,则自定异常p_

14、error。变量select赋值及异常处理select变量1字段名1,变量2字段名2,from where if error != 0 or rowcount != 1beginselect 字段1 into变量1,字段2 into 变量2,from . where .；exceptionwhen others thenend;必须有例外处理，否则无数据时会返回异常（at end of table）语句错误判断if p_p_error != 0或if p_p_error != 0 or p_p_rowcount != 1 thenexception回车换行when others then例外处

15、理定位函数charindex(substr,str替换为instr(str,substr参数位置互换三、手工处理类型ms sql oracle备注字符串相加convert(char(5, p_op_branch_no替换为p_op_branch_no，可以根据行中是否有|自动将多余字符去除oracle中字符串连接时不需转换为字符产生空格函数space(空格长度n左填充函数：lpad(字符串，总长度，填充符右填充函数：rpad (字符串，总长度，填充符需转换为总长度，填充符忽略时为空格替换函数stuff(字符串,起始位,替换长度,新字符串replace(字符串,搜索子串,新字符串注意并不等价，在

16、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,yyyymmd

17、d注意：date类型包括时间在内，如果不截取，两个日期之间的天数是带小数的。当前日期和时间getdate(替换为sysdate和datepart等结合使用时，需特殊处理日期到字符datepartto_char(p_date,yyyymmdd字符到日期to_date(date_str,yyyymmdd日期到整数datepartto_number (to_char(p_date,yyyymmdd类型转换函数数字到字符convert( 字符类型，数字变量to_char(数字变量字符到数字convert(数字类型，字符变量to_number(字符变量日期到字符convert(字符类型，日期变量to_c

18、har(日期变量字符到日期convert(日期类型，字符变量to_date(字符变量字符转换函数str( to_char( 部分存储过程缺乏局部变量1）有excists语句时，需新增v_count number(12；2）有例外处理时，需新增e_error exception变量声明后变量声明后加begin存储过程结束前如果是返回游标，则增加返回成功返回码存储过程结束时结束时，/换行符前加end 存储过程名;开关语句case 语句：如 case when s=a and b =b then aelse b end多重decode进行0和1的相乘、相加，完成case语句中的逻辑运算；如：deco

19、de( decode( s,a,1,0* decode(b,b,1,0 ,1 ,a ,b 。即and 为decode相乘=1，or为decode相加>1;动态过程调用if select proc = 'sp_1'elseselect proc = 'sp_2'exec procif select p_proc = 'sp_1'elseselect p_ proc = 'sp_2'end if;if p_proc = 'sp_1' thensp_1;elsesp_2;end if;记录条数判断if rowcou

20、nt = 0if sql%rowcount=0错误以结果集返回select ErrorNo = error_no, ErrorInfo = error_infoopen p_cursor for select p_error_no as ErrorNo, pi_error_info as ErrorInfo from dual;返回指定行数结果集set rowcount numselect where if p_ num = 0 thenp_ num := 99999999;end if;select where and rownum <= p_ num;结果集返回select open

21、 p_cursor forselect 四、注意事项：1、在pl/sql中空字符串为null，null值在插入到表中时要注意是否有非null限制，在逻辑判断时要特别注意；尤其在测试时，一些问题均由此引起。但是若将空字符串赋给char型变量时，与将null值赋给char型变量是不同的，前者赋值后不为null；如：若表hsa的一个字段c为char(10型且不允许为null，则不能：insert into hsa(c values (; 因为为null;但是若变量p_c char(10,且p_c:=; 则可以insert into hsa(c values (p_c;2、char型变量，空串不是nu

22、ll，而是相当于赋相应长度的空格！除非把null值的变量赋给它。所以在字符串逻辑判断处，若有is null，最好将其类型为varchar2，以便使空串为null；我们的存储过程的输入型字符串参数，一般定义一个相应的局部变量，类型与输入参数相同，但是如果此变量要做is null判断，则其类型应改为varchar2;如：p_s char8;若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_

23、s作为输入参数为null，则相当于p_s:=null;注意对于输入参数pi_s，不管其类型为varchar2,还是char,若为空则都为null;p_s varchar210;若p_s:=,则p_s为null;若p_c char(10; p_c:=; p_s:=p_c;则p_s不为null，长度为10;将某表char型字段值（为,而非null）赋给p_s，也同效。对于trim(函数，若去空后后为，则都为null (trim函数返回为varchar2;对于length(函数，若字符串为或null,则返回值都为null;但对于char型则为声明的长度。为简化空值处理和逻辑判断处理，并参照其它系统做法，本系