SQL中調用ORACLE存儲過程.doc

SQL Server 調用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_PACKAGE;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)在SQLServer中通過LinkedServer來執(zhí)行Oracle存儲過程declareBatchIDnvarchar(40)declareQueryStrnvarchar(1024)declareStatusCodenvarchar(100)declaresqlnvarchar(1024)setBatchID=AAASETQueryStr=CALLGSN.Test_PACKAGE.Test_procedure1(+BatchID+,4,resultset3,p_MSG,resultset1,p_MSG1)(3)執(zhí)行結果(a)selectsql=SELECTStatusCode=p_msgFROMOPENQUERY(HI4DB_MS,+QueryStr+)execsp_executesqlsql,NStatusCodenvarchar(100)output,StatusCodeoutputprintStatusCode答案:StatusCode=a(b)selectsql=SELECTtop3StatusCode=p_msgFROMOPENQUERY(HI4DB_MS,+QueryStr+)execsp_executesqlsql,NStatusCodenvarchar(100)output,StatusCodeoutputprintStatusCode答案:StatusCode=a(c)selectsql=SELECTtop2StatusCode=p_msgFROMOPENQUERY(HI4DB_MS,+QueryStr+)execsp_executesqlsql,NStatusCodenvarchar(100)output,StatusCodeoutputprintStatusCode答案:StatusCode=b(d)selectsql=SELECTtop1StatusCode=p_msgFROMOPENQUERY(HI4DB_MS,+QueryStr+)execsp_executesqlsql,NStatusCodenvarchar(100)output,StatusCodeoutputprintStatusCode答案:StatusCode=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=p_msg1FROMOPENQUERY(HI4DB_MS,+QueryStr+)execsp_executesqlsql,NStatusCodenvarchar(100)output,StatusCodeoutputprintStatusCode答案:StatusCode=abc2 上述使用方法的條件(1)LinkServer要使用Microsoft的Driver（MicrosoftOLEDBProviderforOracle）(2)OraclePackage中的Procedure的返回參數(shù)是Table類型，目前table只試成功一個欄位。(3)SQLServer的StoreProcedure調用OracleProcedure時，返回參數(shù)名字必須和Procedure相同。3 上述方法的要點(1)如果要實現(xiàn)“Oracle和SQLServer數(shù)據(jù)庫”之間的Trans處理，則Oracle的Procedure不要有Commit,rollback等語句，讓SQLServer的StoreProcedure去控制整個Trans何時commit。(2)假如返回參數(shù)大于1個，返回參數(shù)的次序可以調換，調用時只返回第一個出現(xiàn)的返回參數(shù)，如上面的執(zhí)行結果(e)。但是輸入?yún)?shù)和返回參數(shù)的順序不能調換。(3)resultsetn,p_MSG1，這里的n，表示返回表的行數(shù)。N可以大于等于實際的行數(shù)，但不能小于實際的行數(shù)，會報錯。(4)假如返回表有多行記錄,執(zhí)行 selectsql=SELECTStatusCode=p_msgFROMOPENQUERY(HI4DB_MS,+QueryStr+)execsp_executesqlsql,NStatusCodenvarchar(100)output,StatusCodeoutputprintStatusCode,StatusCode中的值為最后一行記錄的值,如執(zhí)行結果(a)。4 動態(tài)SQL語句(1)普通SQL語句可以用Exec執(zhí)行eg:Select*fromMCITYExec(select*fromMCITY)sp_executesqlNselect*fromtableName-請注意字符串前一定要加N(2)字段名，表名，數(shù)據(jù)庫名之類作為變量時，必須用動態(tài)SQLeg:declareFielsNamevarchar(20)declaresqlsnvarchar(1000)setFielsName=CITYSelectFielsNamefromMCITY-錯誤Exec(select+FielsName+fromMCITY)-請注意加號前后的單引號的邊上要加空格setsqls=select+FielsName+fromMCITYexecsp_executesqlsqls當然將字符串改成變量的形式也可declaresvarchar(1000)sets=select+FielsName+fromMCITYExec(s)-成功execsp_executesqls-此句會報錯declaresNvarchar(1000)-注意此處改為nvarchar(1000)sets=select+fname+fromfromMCITYExec(s)-成功execsp_executesqls-此句正確(3)輸出參數(shù)eg:declarenumintdeclaresqlsnvarchar(1000)declarestrTableNamenvarchar(55)setstrTableName=MCITYsetsqls=selectcount(*)from+strTableNameexec(sqls)如何能將exec執(zhí)行的結果存入變量num中declarenumintdeclaresqlsnvarchar(1000)declarestrTableNamenvarchar(55)setstrTableName=MCITYsetsqls=selecta=count(*)from+strTableNameexecsp_executesqlsqls,Naintoutput,numoutputselectnum（注：以上SQL在sv-02,Qservice下測試通