SQL Server vs Oracle 存儲過程語法轉(zhuǎn)換

一、SQLServervsOracle簡單語法比較此為本人將ORACLE函數(shù)和存儲過程轉(zhuǎn)換為SQLSERVER遇到的一些語法問題的經(jīng)驗總結(jié)，肯定不能包括所有的語法不同點。注：簡單的語法異同1、SQLSERVER變量必須以@開頭。2、SQLSERVER語句后不需要寫分號結(jié)束符。3、oracle變量類型number可以修改為sqlserver的decimal4、oracle變量類型varchar2可以修改為sqlserver的varchar5、SQLSERVER定義變量及傳遞參數(shù)，最好加上參數(shù)大小數(shù)值，例如：varchar(50)5、SQLSERVER不能用ROWID,ROWNUM(但可以用TOP代替)6、oracle里的nvl函數(shù)，在SQLSERVER里使用ISNULL函數(shù)取代7、SQLSERVER自定義函數(shù)不允許修改全局表數(shù)據(jù)（只允許修改自定義函數(shù)范圍內(nèi)表數(shù)據(jù)）,所以發(fā)生表修改的最好用存儲過程實現(xiàn)而非函數(shù)。1create函數(shù)或存儲過程異同點Oracle創(chuàng)建函數(shù)或存儲過程一般是createorreplace……SQLSERVER則是在創(chuàng)建之前加一條語句，先判斷是否已經(jīng)存在，如果存在刪除已有的函數(shù)或存儲過程。函數(shù)語句ifexists(select*fromdbo.sysobjectswhereid=object_id(N'[dbo].[函數(shù)名]')andxtypein(N'FN',N'IF',N'TF'))dropfunction[dbo].[函數(shù)名]GO存儲過程ifexists(select*fromdbo.sysobjectswhereid=object_id(N'[dbo].[存儲過程名]')andOBJECTPROPERTY(id,N'IsProcedure')=1)dropprocedure[dbo].[存儲過程名]GO2結(jié)構(gòu)異同點ORACLECreate部分IS定義部分BEGIN…END;實現(xiàn)部分SQLSERVERCreate部分AS定義和實現(xiàn)部分(AS下面的代碼一般用BEGIN…END包含)3調(diào)用參數(shù)ORACLE輸入?yún)?shù)參數(shù)名In參數(shù)類型ORACLE輸出參數(shù)參數(shù)名Out參數(shù)類型SQLSERVER輸入?yún)?shù)參數(shù)名參數(shù)類型IN（IN可以不寫，系統(tǒng)默認）SQLSERVER輸出參數(shù)參數(shù)名參數(shù)類型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游標的定義及使用及循環(huán)操作ORACLE定義游標CURSORCurAISSELECTaFROMtabwhere…;SQLSERVER定義游標DECLARECurACURSORLOCALFORSELECTaFROMtabwhere…;ORACLE使用游標OpenCurA;--打開游標FetchCurAIntoISUserUnitPri;IFCurA%NOTFOUNDTHEN--注：如果為CurA%FOUND，看下面相同位置注釋ISUserUnitPri:=1;ENDIF;CloseCurA;--關(guān)閉游標SQLSERVER使用游標OpenCurA--打開游標FetchnextfromCurAInto@ISUserUnitPriIF@@fetch_status<>0BEGIN--注：則@@fetch_status=0SET@ISUserUnitPri=1--沒有選到記錄給默認值1ENDCloseCurA--關(guān)閉游標DEALLOCATECurA--釋放占用資源ORACLE循環(huán)操作游標(超級簡潔)FORISUserUnitPriINCurALOOP…–-做操作ENDLOOP;注：想循環(huán)中間退出循環(huán)，用EXITSQLSERVER循環(huán)操作游標OpenCurA--打開游標FetchnextfromCurAInto@ISUserUnitPriWhile(@@fetch_status=0)BEGIN….–-做操作FetchnextfromCurAInto@ISUserUnitPriENDCloseCurA--關(guān)閉游標DEALLOCATECurA--釋放占用資源注：想循環(huán)中間退出循環(huán)，用BREAK注意：SQLSERVER使用游標完后，需要刪除游標引用(DEALLOCATEcursor_name)。8計算時間差ORACLEOracle兩個時間相減得到一個以天為單位的帶小數(shù)的值，需要根據(jù)自己的需要再換算成秒值。--這里為取START_QUEUE_TIME到當前時間的秒數(shù)（SYSDATE-START_QUEUE_TIME)*24*60*60SQLSERVERSQLSERVER兩個時間相減得到還是時間（從1900-01-0100:00:00.000開始的時間）。所以想得到以秒的時間差，這么做就麻煩了。SQLSERVER取時間差，專門有一個DATEDIFF函數(shù)，具體看SQLSERVER幫助。--這里為取START_QUEUE_TIME到當前時間的秒數(shù)DATEDIFF(second,START_QUEUE_TIME,GETDATE())9topN問題在sqlserver中，topN問題很容易解決，如下例：從表stbdbdj中選取排序后的第一行數(shù)據(jù)進行賦值。在sql中解決方法很簡單，在select后面加上：topn即可，其中n代表行數(shù)。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決定行數(shù)。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如何解決結(jié)果集返回時，*和變量同時存在的問題下面例子表示，在用游標返回結(jié)果集時，同時返回一個變量的值，在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外聯(lián)接問題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函數(shù)替換DECODE函數(shù)替換ORACLEdecode(client_status,'0','正常，'1','凍結(jié)','2','掛失','3','銷戶','未知');SQLSERVER沒有DECODE函數(shù)caseclient_status

when'0'then'正常'

when'1'then'凍結(jié)'

when'2'then'掛失'

when'3'then'銷戶'

else'未知'

end

注：有趣的是ORACLE的CASE函數(shù)，在SQLSERVER里沒有找到替代的，只好用IFELSE語句解決。14oracle的select…into問題ORACLE里直接取字段值，用select…into語法selectunit_idintounitidfromcall_user_tablewhereuser_id=‘1231312’SQLSERVER直接取則直觀的多，直接等于就可以了select@unitid=unit_idfromcall_user_tablewhereuser_id=‘1231312’15update語句中表別名問題因為有時候更新表時，需要從另一個表中更新數(shù)據(jù)，此處Oracleupdate語句可以給表起別名。但在SQLSERVER中update語句不允許用別名，但可以直接使用表明引用。如下：oralceUPDATEA表aSET=(selectfromB表bwhereb.id=a.id)SqlserverUPDATEA表SETname=(selectB表.namefromB表whereB表.id=A表.id)二、為兼容oracle添加的函數(shù)注意調(diào)用這些函數(shù)的時候，前面給加dbo.,例如sernum1=dbo.TO_NUMBER(‘123’)TO_NUMBERCREATEFunctionTO_NUMBER(@strVarchar(20))RETURNSdecimal/**說明:實現(xiàn)ORACLETO_NUMBER函數(shù)*參數(shù)說明:* 輸入：@str返回：*/ASBEGINRETURNCONVERT(decimal(18,2),@str)ENDGOTO_CHARCREATEFunctionTO_CHAR(@numdecimal(20))RETURNSVarchar/**說明:實現(xiàn)ORACLETO_CHAR函數(shù)*參數(shù)說明:* 輸入：@num返回：*/ASBEGINRETURNCONVERT(varchar(20),@num)ENDGOINSTRCREATEFunctionINSTR(@expression1varchar(1000),@expression2varchar(1000))RETURNSINT/**說明:實現(xiàn)INSTR函數(shù)*參數(shù)說明:* 輸入：@expression2,@expression1返回：*/ASBEGINRETURNCHARINDEX(@expression2,@expression1)ENDGOLENGTHCREATEFunction[LENGTH](@expressionvarchar(1000))RETURNSINT/**說明:實現(xiàn)ORACLELENGTH函數(shù)*參數(shù)說明:* 輸入：@expression返回：*/ASBEGINRETURNlen(@expression)ENDGOSUBSTRCREATEFunction[SUBSTR](@expressionvarchar(1000),@startint,@lengthint)RETURNSINT/**說明:實現(xiàn)ORACLESUBSTR函數(shù)*參數(shù)說明:* 輸入：@expression,@start,@length返回：*/ASBEGINRETURNSUBSTRING(@expression,@start,@length)ENDGONVL這個直接用ISNULL函數(shù)代替即可。三、大批量存儲過程可以替換部分將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參數(shù)里的空格IN空格替換空格（注意這里是空格in空格）9、將oralceNVL函數(shù)替換為ISNULL10、將oracle里當前時間的函數(shù)SYSDATE替換為GETDATE11、將Oracle里的varchar2替換成varchar（注意需要自己添加varchar的具體大?。?2、將oracle里的ELSIF替換成ELSEIF13、將oracle連接字符串||替換為SQLServer連接字符串+下面演示下一個簡單的替換例子：（注：因為每個存儲過程代碼太多，所以找了一個相對簡單的做為例子。）OracleCREATEORREPLACEFunctionA_Get_UnSELECTed_Reason(nSERVICENOInNUMBER,strDISposeMsgInOutVarchar2)RETURNNUMBERISv_nRetNUMBER;--返回值v_NumNUMBER;--所有臺席v_BusyNumNUMBER;--忙臺席--查詢是否受理員全忙,是否因受理員暫停受理CURSORCurAIS--適用于現(xiàn)在臺席只能有一路話路的情況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后經(jīng)過修改后的代碼，(注：黑色部分為代碼原有部分，淺藍色部分為代碼替換部分，紅色部分是替換后手工修改的地方)CREATEprocedureA_Get_UnSELECTed_Reason(@nSERVICENOdecimal(20),