SQL Server vs Oracle 存储过程语法转换

一、SQLServervsOracle简单语法比较此为本人将ORACLE函数和存储过程转换为SQLSERVER遇到的一些语法问题的经验总结，肯定不能包括所有的语法不同点。注：简单的语法异同1、SQLSERVER变量必须以@开头。2、SQLSERVER语句后不需要写分号结束符。3、oracle变量类型number可以修改为sqlserver的decimal4、oracle变量类型varchar2可以修改为sqlserver的varchar5、SQLSERVER定义变量及传递参数，最好加上参数大小数值，例如：varchar(50)5、SQLSERVER不能用ROWID,ROWNUM(但可以用TOP代替)6、oracle里的nvl函数，在SQLSERVER里使用ISNULL函数取代7、SQLSERVER自定义函数不允许修改全局表数据（只允许修改自定义函数范围内表数据）,所以发生表修改的最好用存储过程实现而非函数。1create函数或存储过程异同点Oracle创建函数或存储过程一般是createorreplace……SQLSERVER则是在创建之前加一条语句，先判断是否已经存在，如果存在删除已有的函数或存储过程。函数语句ifexists(select*fromdbo.sysobjectswhereid=object_id(N'[dbo].[函数名]')andxtypein(N'FN',N'IF',N'TF'))dropfunction[dbo].[函数名]GO存储过程ifexists(select*fromdbo.sysobjectswhereid=object_id(N'[dbo].[存储过程名]')andOBJECTPROPERTY(id,N'IsProcedure')=1)dropprocedure[dbo].[存储过程名]GO2结构异同点ORACLECreate部分IS定义部分BEGIN…END;实现部分SQLSERVERCreate部分AS定义和实现部分(AS下面的代码一般用BEGIN…END包含)3调用参数ORACLE输入参数参数名In参数类型ORACLE输出参数参数名Out参数类型SQLSERVER输入参数参数名参数类型IN（IN可以不写，系统默认）SQLSERVER输出参数参数名参数类型OUTPUT4变量命名及赋值ORACLE1、变量名随便取2、定义格式为变量名变量类型;3、给变量赋值为变量名:=值；SQLSERVER1、变量名前面一般加@2、定义格式为declare变量名变量类型3、SET变量名=变量类型5IF语句ORACLEIF…THEN….ELSE...ENDIF;SQLSERVERIF...BEGIN……ENDELSEBEGIN……END或者IF...BEGIN……ENDELSEBEGIN……END6case语句ORACLEIF…THEN….ELSE...ENDIF;SQLSERVERIF...BEGIN……ENDELSEBEGIN……END或者IF...BEGIN……ENDELSEBEGIN……END7游标的定义及使用及循环操作ORACLE定义游标CURSORCurAISSELECTaFROMtabwhere…;SQLSERVER定义游标DECLARECurACURSORLOCALFORSELECTaFROMtabwhere…;ORACLE使用游标OpenCurA;--打开游标FetchCurAIntoISUserUnitPri;IFCurA%NOTFOUNDTHEN--注：如果为CurA%FOUND，看下面相同位置注释ISUserUnitPri:=1;ENDIF;CloseCurA;--关闭游标SQLSERVER使用游标OpenCurA--打开游标FetchnextfromCurAInto@ISUserUnitPriIF@@fetch_status<>0BEGIN--注：则@@fetch_status=0SET@ISUserUnitPri=1--没有选到记录给默认值1ENDCloseCurA--关闭游标DEALLOCATECurA--释放占用资源ORACLE循环操作游标(超级简洁)FORISUserUnitPriINCurALOOP…–-做操作ENDLOOP;注：想循环中间退出循环，用EXITSQLSERVER循环操作游标OpenCurA--打开游标FetchnextfromCurAInto@ISUserUnitPriWhile(@@fetch_status=0)BEGIN….–-做操作FetchnextfromCurAInto@ISUserUnitPriENDCloseCurA--关闭游标DEALLOCATECurA--释放占用资源注：想循环中间退出循环，用BREAK注意：SQLSERVER使用游标完后，需要删除游标引用(DEALLOCATEcursor_name)。8计算时间差ORACLEOracle两个时间相减得到一个以天为单位的带小数的值，需要根据自己的需要再换算成秒值。--这里为取START_QUEUE_TIME到当前时间的秒数（SYSDATE-START_QUEUE_TIME)*24*60*60SQLSERVERSQLSERVER两个时间相减得到还是时间（从1900-01-0100:00:00.000开始的时间）。所以想得到以秒的时间差，这么做就麻烦了。SQLSERVER取时间差，专门有一个DATEDIFF函数，具体看SQLSERVER帮助。--这里为取START_QUEUE_TIME到当前时间的秒数DATEDIFF(second,START_QUEUE_TIME,GETDATE())9topN问题在sqlserver中，topN问题很容易解决，如下例：从表stbdbdj中选取排序后的第一行数据进行赋值。在sql中解决方法很简单，在select后面加上：topn即可，其中n代表行数。select

top

1

@entrust_date

=

entrust_date,

@entrust_no

=

entrust_no

from

run2k..stbdbdj

where

entrust_date

=

@date

and

entrust_no

>

@entrust_no_q

and

report_status

=

'1'

order

by

entrust_date,entrust_no；

在oracle中，没有topn这个命令，我们采取把两层查询方式解决：首先，把需要查找的字段值直接进行排序，然后在外面进行第二次查询，并使用rownum决定行数。select

entrust_date,entrust_no

into

@entrust_date,

@entrust_no

from

(

select

entrust_date,entrust_no

from

stbdbdj

where

entrust_date

=

@date

and

entrust_no

>

@entrust_no_q

and

report_status

=

'1'

order

by

entrust_date,entrust_no

)

where

rownumber

<=1

;

10如何解决结果集返回时，*和变量同时存在的问题下面例子表示，在用游标返回结果集时，同时返回一个变量的值，在sqlserver中代码如下所示：select

a.*,an_id

from

run2k..stbbp

a,run2k..stkaccoarg

b

where

a.date

=

@entrust_date

and

a.serial_no

=

@serial_no

and

a.branch_no

=

b.branch_no

and

a.exchange_type

=

b.exchange_type;

但在oracle中却没有这种用法，’*’后面必需跟from。解决方法如下：

1）我们可以把'*'变成所需要选择的字段，就是说采用表中需要显示的全部字段表示*。

例如：

open

p_cursor

for

select

branch_no,...,organ_id

where...

2）如果这个字段或者说变量是从另外一张表中取出来的，同样可以采用下面的办法。open

p_cursor

for

select

a.*,an_id;

from

stkaccoentrust

a,

stkaccoarg

b

where

a.branch_no

=

b.branch_no

and

a.exchange_type

=

b.exchange_type

and

a.init_date

=

v_entrust_date

and

a.serial_no

=

v_serial_no;11外联接问题Sqlserver<--->oracle

a=*b<--->a(+)=b

a*=b<--->a=b(+)12多条记录求和问题selectsum(A+B+C)

intoD

from...

where...

groupby...

单条记录求和

selectA+B

intoC

from...

where...13用SQLSERVER里CASE函数替换DECODE函数替换ORACLEdecode(client_status,'0','正常，'1','冻结','2','挂失','3','销户','未知');SQLSERVER没有DECODE函数caseclient_status

when'0'then'正常'

when'1'then'冻结'

when'2'then'挂失'

when'3'then'销户'

else'未知'

end

注：有趣的是ORACLE的CASE函数，在SQLSERVER里没有找到替代的，只好用IFELSE语句解决。14oracle的select…into问题ORACLE里直接取字段值，用select…into语法selectunit_idintounitidfromcall_user_tablewhereuser_id=‘1231312’SQLSERVER直接取则直观的多，直接等于就可以了select@unitid=unit_idfromcall_user_tablewhereuser_id=‘1231312’15update语句中表别名问题因为有时候更新表时，需要从另一个表中更新数据，此处Oracleupdate语句可以给表起别名。但在SQLSERVER中update语句不允许用别名，但可以直接使用表明引用。如下：oralceUPDATEA表aSET=(selectfromB表bwhereb.id=a.id)SqlserverUPDATEA表SETname=(selectB表.namefromB表whereB表.id=A表.id)二、为兼容oracle添加的函数注意调用这些函数的时候，前面给加dbo.,例如sernum1=dbo.TO_NUMBER(‘123’)TO_NUMBERCREATEFunctionTO_NUMBER(@strVarchar(20))RETURNSdecimal/**说明:实现ORACLETO_NUMBER函数*参数说明:* 输入：@str返回：*/ASBEGINRETURNCONVERT(decimal(18,2),@str)ENDGOTO_CHARCREATEFunctionTO_CHAR(@numdecimal(20))RETURNSVarchar/**说明:实现ORACLETO_CHAR函数*参数说明:* 输入：@num返回：*/ASBEGINRETURNCONVERT(varchar(20),@num)ENDGOINSTRCREATEFunctionINSTR(@expression1varchar(1000),@expression2varchar(1000))RETURNSINT/**说明:实现INSTR函数*参数说明:* 输入：@expression2,@expression1返回：*/ASBEGINRETURNCHARINDEX(@expression2,@expression1)ENDGOLENGTHCREATEFunction[LENGTH](@expressionvarchar(1000))RETURNSINT/**说明:实现ORACLELENGTH函数*参数说明:* 输入：@expression返回：*/ASBEGINRETURNlen(@expression)ENDGOSUBSTRCREATEFunction[SUBSTR](@expressionvarchar(1000),@startint,@lengthint)RETURNSINT/**说明:实现ORACLESUBSTR函数*参数说明:* 输入：@expression,@start,@length返回：*/ASBEGINRETURNSUBSTRING(@expression,@start,@length)ENDGONVL这个直接用ISNULL函数代替即可。三、大批量存储过程可以替换部分将oracle建立存储过程的代码CREATEORREPLACEProcedure存储过程名中的ORREPLACE替换为空将oracle的变量（和字段名不重名的）直接替换成@变量名。例如：user_id替换为@user_id3、将IS替换为AS。(注意：需要手工将begin提前到AS下面)。4、下面4条为游标部分4.1、将oracle游标CURSORCurAIS替换为DECLARECurACURSORLOCALFOR4.2、将oracle游标fetchCurAinto替换为FetchnextfromCurAInto4.3、将oracle游标IF(curA%NOTFOUND)THEN替换为IF(@@fetch_status<>0)BEGIN4.4、将oracle游标IF(curA%FOUND)THEN替换为IF@@fetch_status=0BEGIN5、将oralce中的;(分号)替换为空格6、将oracle的复制符号:=替换为=,当然前面的SET符号必须自己手工一个一个添加。7、将oracle所有的then替换为begin,将所有的endif替换为end8、将oracle参数里的空格IN空格替换空格（注意这里是空格in空格）9、将oralceNVL函数替换为ISNULL10、将oracle里当前时间的函数SYSDATE替换为GETDATE11、将Oracle里的varchar2替换成varchar（注意需要自己添加varchar的具体大小）12、将oracle里的ELSIF替换成ELSEIF13、将oracle连接字符串||替换为SQLServer连接字符串+下面演示下一个简单的替换例子：（注：因为每个存储过程代码太多，所以找了一个相对简单的做为例子。）OracleCREATEORREPLACEFunctionA_Get_UnSELECTed_Reason(nSERVICENOInNUMBER,strDISposeMsgInOutVarchar2)RETURNNUMBERISv_nRetNUMBER;--返回值v_NumNUMBER;--所有台席v_BusyNumNUMBER;--忙台席--查询是否受理员全忙,是否因受理员暂停受理CURSORCurAIS--适用于现在台席只能有一路话路的情况SELECTcount(*),sum(CUR_RECV_NUM)FROMAGENT_TABLEa,T_CUR_AGENT_SERVICE_ABILITYbWHEREa.UNIT_ID!=-1anda.AGENT_ID=b.AGENT_IDandb.SERVICENO=nSERVICENO;BEGINOpenCurA;FetchCurAIntov_Num,v_BusyNum;IFCurA%FOUNDTHENIF(v_Num=0)THENstrDISposeMsg:=strDISposeMsg||'无受理员';v_nRet:=-3;ELSIF(v_Num=v_BusyNum)THENstrDISposeMsg:=strDISposeMsg||'受理员全忙';v_nRet:=-1;ELSEstrDISposeMsg:=strDISposeMsg||'受理员暂停受理';v_nRet:=-2;ENDIF;ENDIF;CloseCurA;RETURNv_nRet;END;替换sqlserver后经过修改后的代码，(注：黑色部分为代码原有部分，浅蓝色部分为代码替换部分，红色部分是替换后手工修改的地方)CREATEprocedureA_Get_UnSELECTed_Reason(@nSERVICENOdecimal(20),