SQL中調(diào)用ORACLE存儲(chǔ)過程

1、SQL Server調(diào)用Oracle的存儲(chǔ)過程收藏原文如下：通過SQL Linked Server 執(zhí)行0rac 1 e存儲(chǔ)過程小結(jié)1舉例我們可以通過下面的方法在SQL Server中通過Linked Server來執(zhí)行Oracle存儲(chǔ)過 程。(1) Oracle PackagePACKAGE Test PACKAGE ASTYPE t_t is TABLE of VARCHAR2(30)INDEX BY BINARY,INTEGER；PROCEDURE Test procedure1(p BATCHD IN VARCHAR2,p_Number IN number,P.MSG OUT t_t.

2、p MSG1 OUT t_t);END Test PACKAGE；PACKAGE BODY Test PACKAGE ASPROCEDURE Test procedure1(p BATCH一ID IN VARCHAR2,p Number IN number,P.MSG OUT t_t,p MSG1 OUT t_t)ASBEGINp. MSGp. MSG(2)： = ,b,；p. MSG(3)=a；p MSGl(l)： = Qbc；RETURN；MIT；EXCEPTIONWHEN OTHERS THENROLLBACK；END Test procedure1；END Test PACKAGE；(

3、2) 在SQL Server中通過Linked Server 來執(zhí)行Oracle 存儲(chǔ)過程declare BatchID nvarchar (40)declare QueryStr nvarchar (1024)declare StatusCode nvarchar(100)declare sq1 nvarchar(1024)set BatchID=，AM*SET QueryStr=, CALL GSN. Test_PACKAGE. Test_procedurel(* * *1，+BatchID+,1'".八'4'''.resultset 3.

4、 p_MSG.resultset 1, p_ MSG1)1(3) 執(zhí)行結(jié)果(a)select sql=rSELECT StatusCode=p. msg FROM OPENQUERY (HI4DB_MS,r11-QueryStr+''')'exec sp executesql sql,NfStatusCode nvarchar(100) output*,StatusCode outpu tprint StatusCode答案:StatusCode=, a'(b) select sql=fSELECT top 3 StatusCode=p_msg FROM

5、 OPENQUERY (HI4DB MS,-QueryStr+,F)rexec sp_executesql sql,N1StatusCode nvarchar(100) output *.StatusCode outpu print StatusCode答案:StatusCode=, a(c)select sql=fSELECT top 2 StatusCode=p_msg FROM OPENQUERY (HI4DB MS,1r r -QueryStr+，f)rexec sp_executesql sql.N1StatusCode nvarchar(100) outputr.StatusCod

6、e outpu tprint StatusCode答案:StatusCode=， b'(d)select sql=rSELECT top 1 StatusCode=p_msg FROM OPENQUERY (HI4DB MS,1'r-QueryStr+，f)rexec sp executesql sqlStatusCode nvarchar(100) output1,StatusCode outpu print StatusCode答案:StatusCode二'c(e)SET QueryStr=，CALL GSN. Test.PACKAGE. Test procedur

7、e1C11f，+BatchID+，11 *r / 1''4'' '' resultset 1, p. MSG1. resultset 3. p_MSG)'(注意這里 p_MSG1 和P MSG交換次序了)EXEC(rSELECT pmsgl FROM OPENQUERY (HI4DB MS/r,-QueryStr+,r1)r) select sql=rSELECT StatusCode=p_msgl FROM OPENQUERY (HI4DB MS/r,-QuerySexec sp executesql sql,N*StatusCode

8、nvarchar(100) output*,StatusCode outpuprint StatusCode答案：StatusCode=" abc*2上述使用方法的條件(1) Link Server 要使用 Microsoft 的 Driver (Microsoft OLE DB Provider fo r Oracle)(2) Oracle Package中的Procedure的返回參數(shù)是Table類型，目前table只試成功一個(gè)欄位。(3) SQL Server 的 Store Procedure 調(diào)用 Oracle Procedure 時(shí)，返回參數(shù)名字必須 和 Procedur

9、e 相同。3上述方法的要點(diǎn)(1) 如果要實(shí)現(xiàn)"Oracle和SQL Server數(shù)據(jù)庫”之間的Trans處理，則Oracle的Proc edure 不要有 mit, rollback 等語句，讓 SQL Server 的 Store Procedure 去控制整個(gè) Tra ns何時(shí)mito(2) 假如返回參數(shù)大于1個(gè)，返回參數(shù)的次序可以調(diào)換，調(diào)用時(shí)只返回第一個(gè)出現(xiàn)的返回 參數(shù)，如上面的執(zhí)行結(jié)果(e)。但是輸入?yún)?shù)和返回參數(shù)的順序不能調(diào)換。(3) resultset n, p. MSGl,這里的n,表示返回表的行數(shù)。N可以大于等于實(shí)際的 行數(shù)，但不能小于實(shí)際的行數(shù)，會(huì)報(bào)錯(cuò)。(4) 假如

10、返回表有多行記錄，執(zhí)行select sql=rSELECT StatusCode=pmsg FROM OPENQUERY (HI4DB ''+Qu eryStr+，")'exec sp executesql sql,N*StatusCode nvarchar(100) output*,StatusCode outpu tprint StatusCode, StatusCode中的值為最后一行記錄的值，如執(zhí)行結(jié)果(a)。4動(dòng)態(tài)SQL語句(1) 普通SQL語句可以用Exec執(zhí)行egSelect * from MCITYExec (* select * from M

11、CITY)sp_executesql N'select * from tableName串前一定要加N一請注意字符(2) 字段名，表名，數(shù)搖庫名之類作為變量時(shí)，必須用動(dòng)態(tài)SQLeg： declare FielsNamevarchar (20)declare sqls nvarchar (1000)set F i e1sName'CITY'Select FielsName from MCITYExec (rselect '+ FielsName +1 from MCITY *)請注意加號(hào)前后的單引號(hào)的邊上要加空格set sqls=，select+ FielsNam

12、e +' from MCITYexec sp_executesql sqls當(dāng)然將字符串改成變量的形式也可declare s varchar (1000)1 select *+ FielsName +' from MCITY1Exec(s)成功exec sp executesql s此句會(huì)報(bào)錯(cuò)declare s Nvarchar (1000)注意此處改為nvarchar (1000)set* select '+ fname+1 from from MCITYrExec(s)成功exec sp_executesql s此句正確(3) 輸出參數(shù) eg： declare nu

13、m int declare sqls nvarchar (1000)declarestrTableName nvarchar(55) set strTableName=，MCITY*+strTableNameset sqls=，select count() from 'exec (sqls)如何能將exec執(zhí)行的結(jié)果存入變量num中declarenum intdeclaresqls nvarchar(1000)declarestrTableName nvarchar (55)setstrTableName=，MCITY，setsqls=，select a=count (*) fi'om r +strTab 1 eNameexecsp executesql sqls,Nfa int output1,num outputselectnum(注：以上