SQL中調用ORACLE存儲過程

1、SQLServer調用Oracle的存儲過程收藏原文如下：通過SQLLinkedServer執(zhí)行Oracle存儲過程小結1舉例我們可以通過下面的方法在SQLServer中通過LinkedServer來執(zhí)行Oracle存儲過程。(1)OraclePackagePACKAGETest_PACKAGEASTYPEt_tisTABLEofVARCHAR2(30)INDEXBYBINARY_INTEGER;PROCEDURETest_procedure1(p_BATCH_IDINVARCHAR2,p_NumberINnumber,p_MSGOUTt_t,p_MSG1OUTt_t);ENDTest_PAC

2、KAGE;PACKAGEBODYTest_PACKAGEASPROCEDURETest_procedure1(p_BATCH_IDINVARCHAR2,p_NumberINnumber,p_MSGOUTt_t,p_MSG1OUTt_t)ASBEGINp_MSG(1):='c'p_MSG(2):='b'p_MSG(3):='a'p_MSG1(1):='abc'RETURN;COMMIT;EXCEPTIONWHENOTHERSTHENROLLBACK;ENDTest_procedure1;ENDTest_PACKAGE;(2)在SQL

3、Server中通過LinkedServer來執(zhí)行Oracle存儲過程declareBatchIDnvarchar(40)declareQueryStrnvarchar(1024)declareStatusCodenvarchar(100)declaresqlnvarchar(1024)setBatchID='AAA'SETQueryStr='CALLGSN.Test_PACKAGE.Test_procedure1(""'+BatchID+'''',""4"",result

4、set3,p_MSG,resultset1,p_MSG1)'(3)執(zhí)行結果(a)selectsql='SELECTStatusCode=p_msgFROMOPENQUERY(HI4DB_MS,”'+QueryStr+'")'execsp_executesqlsql,N'StatusCodenvarchar(100)output',StatusCodeoutputprintStatusCode答案:StatusCode='a'(b)selectsql='SELECTtop3StatusCode=p_msgF

5、ROMOPENQUERY(HI4DB_MS,”'+QueryStr+”')'execsp_executesqlsql,N'StatusCodenvarchar(100)output',StatusCodeoutputprintStatusCode答案:StatusCode='a'(c)selectsql='SELECTtop2StatusCode=p_msgFROMOPENQUERY(HI4DB_MS,'"+QueryStr+'")'execsp_executesqlsql,N'

6、StatusCodenvarchar(100)output',StatusCodeoutputprintStatusCode答案:StatusCode='b'(d)selectsql='SELECTtop1StatusCode=p_msgFROMOPENQUERY(HI4DB_MS,"'+QueryStr+"')'execsp_executesqlsql,N'StatusCodenvarchar(100)output',StatusCodeoutputprintStatusCode答案:StatusCo

7、de='c'(e)SETQueryStr='CALLGSN.Test_PACKAGE.Test_procedure1(""'+BatchID+“”',""4"",resultset1,p_MSG1,resultset3,p_MSG)'-(注意這里p_MSG1和p_MSG交換次序了)EXEC('SELECTp_msg1FROMOPENQUERY(HI4DB_MS,”'+QueryStr+”')')selectsql='SELECTStatusCode

8、=p_msg1FROMOPENQUERY(HI4DB_MS,'"+QueryStr+"')'execsp_executesqlsql,N'StatusCodenvarchar(100)output',StatusCodeoutputprintStatusCode答案：StatusCode=abc'2上述使用方法的條件(1) LinkServer要使用Microsoft的Driver(MicrosoftOLEDBProviderforOracle)(2) OraclePackage中的Procedure的返回參數(shù)是Table類型

9、，目前table只試成功一個欄位。(3) SQLServer的StoreProcedure調用OracleProcedure時，返回參數(shù)名字必須和Procedure相同。3上述方法的要點如果要實現(xiàn)"Oracle和SQLServer數(shù)據(jù)庫”之間的Trans處理，則Oracle的Procedure不要有Commit,rollback等語句，讓SQLServer的StoreProcedure去控制整個Trans何日ccommit。(2)假如返回參數(shù)大于1個，返回參數(shù)的次序可以調換，調用時只返回第一個出現(xiàn)的返回參數(shù)，如上面的執(zhí)行結果(e)。但是輸入?yún)?shù)和返回參數(shù)的順序不能調換。results

10、etn,p_MSG1,這里的n,表示返回表的行數(shù)。N可以大于等于實際的行數(shù)，但不能小于實際的行數(shù)，會報錯。(4)假如返回表有多行記錄，執(zhí)行selectsql='SELECTStatusCode=p_msgFROMOPENQUERY(HI4DB_MS,'"+QueryStr+"')'execsp_executesqlsql,N'StatusCodenvarchar(100)output',StatusCodeoutputprintStatusCode,StatusCode中的值為最后一行記錄的值，如執(zhí)行結果(a)。4動態(tài)SQL語

11、句普通SQL語句可以用Exec執(zhí)行eg:Select*fromMCITYExec('select*fromMCITY)sp_executesqlN'select*fromtableName'-請注意字符串前一定要加N(2)字段名，表名，數(shù)據(jù)庫名之類作為變量時，必須用動態(tài)SQLeg:declareFielsNamevarchar(20)declaresqlsnvarchar(1000)setFielsName='CITY'SelectFielsNamefromMCITY-專昔誤Exec('select'+FielsName+'fro

12、mMCITY')-請注意加號前后的單引號的邊上要加空格setsqls='select'+FielsName+'fromMCITY'execsp_executesqlsqls當然將字符串改成變量的形式也可declaresvarchar(1000)sets='select'+FielsName+'fromMCITY'Exec(s)-成功execsp_executesqls-此句會報錯declaresNvarchar(1000)-注意此處改為nvarchar(1000)sets='select'+fname+&#

13、39;fromfromMCITY'Exec(s)-成功execsp_executesqls-止匕句正確(3)輸出參數(shù)eg:declarenumintdeclaresqlsnvarchar(1000)declarestrTableNamenvarchar(55)setstrTableName='MCITY'setsqls='selectcount(*)from'+strTableNameexec(sqls)如何能將exec執(zhí)行的結果存入變量num中declarenumintdeclaresqlsnvarchar(1000)declarestrTableNamenvarchar(55)setstrTableName='MCITY'setsqls='selecta=count(*)from'+strTableNameexecsp_executesqlsqls,N'aintou