SQLServervsOracle存儲過程語法轉(zhuǎn)換1.2

1、此為本人將 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

2、、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ù)或存儲過程一般是 createorreplaceSQLSERVER 則是在創(chuàng)建之前加一條語句，先判斷是否已經(jīng)存在，如果存在刪除已有的函數(shù)或存儲過程。函數(shù)語句ifexists(select*fromdbo.sysobjectswhereid=object_id(Ndbo.函數(shù)名)andxtypein(NFN,NIF,

3、NTF)dropfunctiondbo.函數(shù)名GO存儲過程ifexists(select*fromdbo.sysobjectswhereid=object_id(Ndbo,存儲過程名)andOBJECTPROPERTY(id,NIsProcedure)=1)dropproceduredbo.存儲過程名SQLServervsOracle簡單語法比較GO2 結(jié)構(gòu)異同點ORACLECreate 部分IS 定義部分BEGINEND;實現(xiàn)部分SQLSERVERCreate 部分AS 定義和實現(xiàn)部分（AS 下面的代碼一般用 BEGINEND 包含）3 調(diào)用參數(shù)ORACLE 輸入?yún)?shù)參數(shù)名 In 參數(shù)類型O

4、RACLE 輸出參數(shù)參數(shù)名 Out 參數(shù)類型SQLSERVER 輸出參數(shù)參數(shù)名參數(shù)類型 OUTPUT4 變量命名及賦值ORACLE1、變量名隨便取2、定義格式為變量名變量類型；3、給變量賦值為變量名：=值；SQLSERVER1、變量名前面一般加2、定義格式為 declare 變量名變量類型3、SET 變量名=變量類型SQLSERVER 輸入?yún)?shù)參數(shù)名參數(shù)類型 IN（IN 可以不寫，系統(tǒng)默認）5 IF 語句ORACLEIFTHEN.ELSE.ENDIF;SQLSERVERIF.BEGINENDELSEBEGINEND或者IF.BEGINENDELSEBEGINEND6 case 語句ORACLE

5、IFTHENELSEENDIF;SQLSERVERIF.BEGINENDELSEBEGINEND或者IF.BEGINENDELSEBEGINEND7 游標的定義及使用及循環(huán)操作ORACLE 定義游標CURSORCurAISSELECTaFROMtabwhere;SQLSERVER 定義游標DECLARECurACURSORLOCALFORSELECTaFROMtabwhere;ORACLE 使用游標OpenCurA;-打開游標FetchCurAIntoISUserUnitPri;IFCurA%NOTFOUNDTHEN-注：如果為CurA%FOUND,看下面相同位置注釋ISUserUnitPri

6、:=1;ENDIF;CloseCurA;-關(guān)閉游標SQLSERVER 使用游標OpenCurA-打開游標FetchnextfromCurAIntoISUserUnitPriIFfetch_status0BEGIN-注：則6船卜_$12七5=0SETISUserUnitPri=1-沒有選到記錄給默認值1ENDCloseCurA-關(guān)閉游標DEALLOCATECurA-釋放占用資源ORACLE 循環(huán)操作游標(超級簡潔)FORISUserUnitPriINCurALOOP-做操作ENDLOOP;注：想循環(huán)中間退出循環(huán)，用EXITSQLSERVER 循環(huán)操作游標OpenCurA-打開游標Fetchnex

7、tfromCurAIntoISUserUnitPriWhile(fetch_status=0)BEGIN-做操作FetchnextfromCurAIntoISUserUnitPriENDCloseCurA-關(guān)閉游標DEALLOCATECurA-釋放占用資源注：想循環(huán)中間退出循環(huán)，用BREAK注意：SQLSERVER 使用游標完后，需要刪除游標引用(DEALLOCATEcursor_name)8 計算時間差ORACLEOracle 兩個時間相減得到一個以天為單位的帶小數(shù)的值，需要根據(jù)自己的需要再換算成秒值。-這里為取 START_QUEUE_TIME 到當前時間的秒數(shù)(SYSDATE-START

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

9、 中解決方法很簡單，在 select 后面加上：topn 即可，其中 n 代表行數(shù)。selecttop1entrust_date=entrust_date,entrust_no=entrust_nofromrun2k.stbdbdjwhereentrust_date=dateandentrust_noentrust_no_qandreport_status=1orderbyentrust_date,entrust_no；在 oracle 中，沒有 topn 這個命令，我們采取把兩層查詢方式解決：首先，把需要查找的字段值直接進行排序，然后在外面進行第二次查詢，并使用 rownum 決定行數(shù)。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 如何解決結(jié)果集返回時，*和變量同時存在的問題下面例子表示，在用游標返回結(jié)果集時，同時返回一個變量的值，在 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 外聯(lián)接問題Sqlserveroraclea=*ba(+)=ba*bab(+)12 多條記錄求和問題selectsum(A+B+C)intoDfrom.where.groupby.單條記錄求和selec

13、tA+BintoCfrom.where.13 用 SQLSERVER 里 CASE 函數(shù)替換 DECODE 函數(shù)替換ORACLEdecode(client_status,0,正常，1,凍結(jié),2,掛失,3,銷戶,未知)；SQLSERVER 沒有 DECODE 函數(shù)caseclient_statuswhen0then正常when1then凍結(jié)when2then掛失when3then銷戶else未知end注：有趣的是 ORACLE 的 CASE 函數(shù)，在 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 語句中表別名問題因為有時候更新表時，需要從另一個表中更新數(shù)據(jù)，此處 Oracleupdate 語句可以給表起別名。但在 SQLSERVER 中 update 語句不允許用別名,但可以直接使用表明引用。如下：oralceUPDATEA 表 aSET

15、=(fromB 表 bwhereb.id=a.id)SqlserverUPDATEA 表 SETname=(selectB 表.namefromB 表 whereB 表.id=A 表.id)二、為兼容 oracle 添加的函數(shù)注意調(diào)用這些函數(shù)的時候，前面給加 dbo.,例如 sernuml=dbo.TO_NUMBER(123)TO_NUMBERCREATEFunctionTO_NUMBER(strVarchar(20)RETURNSdecimal/*說明：實現(xiàn) ORACLETO_NUMBER 函數(shù)*參數(shù)說明：*輸入：str返回：*/ASBEGINRETURN

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

17、sion1 返回：*/ASBEGINRETURNCHARINDEX(expression2,expression1)ENDGOLENGTHCREATEFunctionLENGTH(expressionvarchar(1000)RETURNSINT/*說明：實現(xiàn) ORACLELENGTH 函數(shù)*參數(shù)說明：*輸入：expression返回：*/ASBEGINRETURNlen(expression)ENDGOSUBSTRCREATEFunctionSUBSTR(expressionvarchar(1000),startint,lengthint)RETURNSINT/*說明：實現(xiàn) ORACLESU

18、BSTR 函數(shù)*參數(shù)說明：*輸入：expression,start,length 返回：*/ASBEGINRETURNSUBSTRING(expression,start,length)ENDGONVL這個直接用 ISNULL 函數(shù)代替即可。三、大批量存儲過程可以替換部分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 參數(shù)里的空格 IN 空格替換空格（注意這里是空格 in 空格）9、將 oralceNVL 函數(shù)替換為 ISNULL10、將 oracle 里當前時間的函數(shù) SYSDATE 替換為 GETDATE11、將 Oracle 里的 varchar2 替換成 varchar（注意需要自己添加 varchar 的具體大?。?2、將 oracle 里的 ELSIF 替換成 ELSEIF13、將 oracle 連接字符串|替換為 SQLServer

21、 連接字符串+下面演示下一個簡單的替換例子：（注：因為每個存儲過程代碼太多，所以找了一個相對簡單的做為例子。）OracleOracleCREATEORREPLACEFunctionA_Get_UnSELECTed_Reason（nSERVICENOInNUMBER,strDISposeMsgInOutVarchar2）RETURNNUMBERISv_nRetNUMBER;-返回值v_NumNUMBER;-所有臺席v_BusyNumNUMBER;-忙臺席-查詢是否受理員全忙，是否因受理員暫停受理-適用于現(xiàn)在臺席只能有一路話路的情況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