SQLServervsOracle存储过程语法转换1.2

1、此为本人将 ORACLE 函数和存储过程转换为 SQLSERVER 遇到的一些语法问题的经验总结，肯定不能包括所有的语法不同点注：简单的语法异同1、SQLSERVER 变量必须以开头。2、SQLSERVER 语句后不需要写分号结束符。3、oracle 变量类型 number 可以修改为 sqlserver 的 decimal4、oracle 变量类型 varchar2 可以修改为 sqlserver 的 varchar5、SQLSERVER 定义变量及传递参数，最好加上参数大小数值，例如：varchar（50）5、SQLSERVER 不能用 ROWID,ROWNUM（但可以用 TOP 代替）6

2、、oracle 里的 nvl 函数，在 SQLSERVER 里使用 ISNULL 函数取代7、SQLSERVER 自定义函数不允许修改全局表数据（只允许修改自定义函数范围内表数据），所以发生表修改的最好用存储过程实现而非函数。1create 函数或存储过程异同点Oracle 创建函数或存储过程一般是 createorreplaceSQLSERVER 则是在创建之前加一条语句，先判断是否已经存在，如果存在删除已有的函数或存储过程。函数语句ifexists(select*fromdbo.sysobjectswhereid=object_id(Ndbo.函数名)andxtypein(NFN,NIF,

3、NTF)dropfunctiondbo.函数名GO存储过程ifexists(select*fromdbo.sysobjectswhereid=object_id(Ndbo,存储过程名)andOBJECTPROPERTY(id,NIsProcedure)=1)dropproceduredbo.存储过程名SQLServervsOracle简单语法比较GO2 结构异同点ORACLECreate 部分IS 定义部分BEGINEND;实现部分SQLSERVERCreate 部分AS 定义和实现部分（AS 下面的代码一般用 BEGINEND 包含）3 调用参数ORACLE 输入参数参数名 In 参数类型O

4、RACLE 输出参数参数名 Out 参数类型SQLSERVER 输出参数参数名参数类型 OUTPUT4 变量命名及赋值ORACLE1、变量名随便取2、定义格式为变量名变量类型；3、给变量赋值为变量名：=值；SQLSERVER1、变量名前面一般加2、定义格式为 declare 变量名变量类型3、SET 变量名=变量类型SQLSERVER 输入参数参数名参数类型 IN（IN 可以不写，系统默认）5 IF 语句ORACLEIFTHEN.ELSE.ENDIF;SQLSERVERIF.BEGINENDELSEBEGINEND或者IF.BEGINENDELSEBEGINEND6 case 语句ORACLE

5、IFTHENELSEENDIF;SQLSERVERIF.BEGINENDELSEBEGINEND或者IF.BEGINENDELSEBEGINEND7 游标的定义及使用及循环操作ORACLE 定义游标CURSORCurAISSELECTaFROMtabwhere;SQLSERVER 定义游标DECLARECurACURSORLOCALFORSELECTaFROMtabwhere;ORACLE 使用游标OpenCurA;-打开游标FetchCurAIntoISUserUnitPri;IFCurA%NOTFOUNDTHEN-注：如果为CurA%FOUND,看下面相同位置注释ISUserUnitPri

6、:=1;ENDIF;CloseCurA;-关闭游标SQLSERVER 使用游标OpenCurA-打开游标FetchnextfromCurAIntoISUserUnitPriIFfetch_status0BEGIN-注：则6船卜_$12七5=0SETISUserUnitPri=1-没有选到记录给默认值1ENDCloseCurA-关闭游标DEALLOCATECurA-释放占用资源ORACLE 循环操作游标(超级简洁)FORISUserUnitPriINCurALOOP-做操作ENDLOOP;注：想循环中间退出循环，用EXITSQLSERVER 循环操作游标OpenCurA-打开游标Fetchnex

7、tfromCurAIntoISUserUnitPriWhile(fetch_status=0)BEGIN-做操作FetchnextfromCurAIntoISUserUnitPriENDCloseCurA-关闭游标DEALLOCATECurA-释放占用资源注：想循环中间退出循环，用BREAK注意：SQLSERVER 使用游标完后，需要删除游标引用(DEALLOCATEcursor_name)8 计算时间差ORACLEOracle 两个时间相减得到一个以天为单位的带小数的值，需要根据自己的需要再换算成秒值。-这里为取 START_QUEUE_TIME 到当前时间的秒数(SYSDATE-START

8、_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

9、 中解决方法很简单，在 select 后面加上：topn 即可，其中 n 代表行数。selecttop1entrust_date=entrust_date,entrust_no=entrust_nofromrun2k.stbdbdjwhereentrust_date=dateandentrust_noentrust_no_qandreport_status=1orderbyentrust_date,entrust_no；在 oracle 中，没有 topn 这个命令，我们采取把两层查询方式解决：首先，把需要查找的字段值直接进行排序，然后在外面进行第二次查询，并使用 rownum 决定行数。se

10、lectentrust_date,entrust_nointoentrust_date,entrust_nofrom(selectentrust_date,entrust_nofromstbdbdjwhereentrust_date=dateandentrust_noentrust_no_qandreport_status=1orderbyentrust_date,entrust_no)whererownumber=1;10 如何解决结果集返回时，*和变量同时存在的问题下面例子表示，在用游标返回结果集时，同时返回一个变量的值，在 sqlserver 中代码如下所示：selecta.*,b.or

11、gan_idfromrun2k.stbbpa,run2k.stkaccoargbwherea.date=entrust_dateanda.serial_no=serial_noanda.branch_no=b.branch_noanda.exchange_type=b.exchange_type;但在 oracle 中却没有这种用法，*后面必需跟 from。解决方法如下：1）我们可以把*变成所需要选择的字段，就是说采用表中需要显示的全部字段表示例如：openp_cursorforselectbranch_no,.,organ_idwhere.2）如果这个字段或者说变量是从另外一张表中取出来的，

12、同样可以采用下面的办法。openp_cursorforselecta.*,an_id;fromstkaccoentrusta,stkaccoargbwherea.branch_no=b.branch_noanda.exchange_type=b.exchange_typeanda.init_date=v_entrust_dateanda.serial_no=v_serial_no;11 外联接问题Sqlserveroraclea=*ba(+)=ba*bab(+)12 多条记录求和问题selectsum(A+B+C)intoDfrom.where.groupby.单条记录求和selec

13、tA+BintoCfrom.where.13 用 SQLSERVER 里 CASE 函数替换 DECODE 函数替换ORACLEdecode(client_status,0,正常，1,冻结,2,挂失,3,销户,未知)；SQLSERVER 没有 DECODE 函数caseclient_statuswhen0then正常when1then冻结when2then挂失when3then销户else未知end注：有趣的是 ORACLE 的 CASE 函数，在 SQLSERVER 里没有找到替代的，只好用 IFELSE 语句解决14 oracle 的 select(into 问题ORACLE 里直接取字段

14、值，用 selectinto 语法selectunit_idintounitidfromcall_user_tablewhereuser_id=1231312;SQLSERVER 直接取则直观的多，直接等于就可以了selectunitid=unit_idfromcall_user_tablewhereuser_id=1231312;15 update 语句中表别名问题因为有时候更新表时，需要从另一个表中更新数据，此处 Oracleupdate 语句可以给表起别名。但在 SQLSERVER 中 update 语句不允许用别名,但可以直接使用表明引用。如下：oralceUPDATEA 表 aSET

15、=(fromB 表 bwhereb.id=a.id)SqlserverUPDATEA 表 SETname=(selectB 表.namefromB 表 whereB 表.id=A 表.id)二、为兼容 oracle 添加的函数注意调用这些函数的时候，前面给加 dbo.,例如 sernuml=dbo.TO_NUMBER(123)TO_NUMBERCREATEFunctionTO_NUMBER(strVarchar(20)RETURNSdecimal/*说明：实现 ORACLETO_NUMBER 函数*参数说明：*输入：str返回：*/ASBEGINRETURN

16、CONVERT(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,expres

17、sion1 返回：*/ASBEGINRETURNCHARINDEX(expression2,expression1)ENDGOLENGTHCREATEFunctionLENGTH(expressionvarchar(1000)RETURNSINT/*说明：实现 ORACLELENGTH 函数*参数说明：*输入：expression返回：*/ASBEGINRETURNlen(expression)ENDGOSUBSTRCREATEFunctionSUBSTR(expressionvarchar(1000),startint,lengthint)RETURNSINT/*说明：实现 ORACLESU

18、BSTR 函数*参数说明：*输入：expression,start,length 返回：*/ASBEGINRETURNSUBSTRING(expression,start,length)ENDGONVL这个直接用 ISNULL 函数代替即可。三、大批量存储过程可以替换部分1、将 oracle 建立存储过程的代码 CREATEORREPLACEProcedure 存储过程名中的 ORREPLACE 替换为空2、将 oracle 的变量（和字段名不重名的）直接替换成变量名。例如：user_id 替换为user_id3、将 IS 替换为 AS。（注意：需要手工将 begin 提前到 AS 下面）。4

19、、下面 4 条为游标部分2.1、 将 oracle 游标 CURSORCurAIS 替换为DECLARECurACURSORLOCALFOR2.2、 将 oracle 游标 fetchCurAinto 替换为 FetchnextfromCurAInto2.3、 将 oracle 游标 IF（curA%NOTFOUND）THEN 替换为 IF（fetch_status0）BEGIN2.4、 将 oracle 游标 IF（curA%FOUND）THEN 替换为 IFfetch_status=0BEGIN5、将 oralce 中的；（分号）替换为空格6、将 oracle 的复制符号：=替换为=,当然

20、前面的 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

21、 连接字符串+下面演示下一个简单的替换例子：（注：因为每个存储过程代码太多，所以找了一个相对简单的做为例子。）OracleOracleCREATEORREPLACEFunctionA_Get_UnSELECTed_Reason（nSERVICENOInNUMBER,strDISposeMsgInOutVarchar2）RETURNNUMBERISv_nRetNUMBER;-返回值v_NumNUMBER;-所有台席v_BusyNumNUMBER;-忙台席-查询是否受理员全忙，是否因受理员暂停受理-适用于现在台席只能有一路话路的情况SELECTcount(*),sum(CUR_RECV_NUM)F

22、ROMAGENT_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|vnRetENDIF;ENDIF;CloseCurA;RETURNv_nRet;END;替换 sqlserv