SQL中调用ORACLE存储过程

1、SQL Server调用Oracle的存储过程收藏原文如下：通过SQL Linked Server 执行0rac 1 e存储过程小结1举例我们可以通过下面的方法在SQL Server中通过Linked Server来执行Oracle存储过 程。(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 来执行Oracle 存储过程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) 执行结果(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的返回参数是Table类型，目前table只试成功一个栏位。(3) SQL Server 的 Store Procedure 调用 Oracle Procedure 时，返回参数名字必须 和 Procedur

9、e 相同。3上述方法的要点(1) 如果要实现"Oracle和SQL Server数据库”之间的Trans处理，则Oracle的Proc edure 不要有 mit, rollback 等语句，让 SQL Server 的 Store Procedure 去控制整个 Tra ns何时mito(2) 假如返回参数大于1个，返回参数的次序可以调换，调用时只返回第一个出现的返回 参数，如上面的执行结果(e)。但是输入参数和返回参数的顺序不能调换。(3) resultset n, p. MSGl,这里的n,表示返回表的行数。N可以大于等于实际的 行数，但不能小于实际的行数，会报错。(4) 假如

10、返回表有多行记录，执行select sql=rSELECT StatusCode=pmsg FROM OPENQUERY (HI4DB ''+Qu eryStr+，")'exec sp executesql sql,N*StatusCode nvarchar(100) output*,StatusCode outpu tprint StatusCode, StatusCode中的值为最后一行记录的值，如执行结果(a)。4动态SQL语句(1) 普通SQL语句可以用Exec执行egSelect * from MCITYExec (* select * from M

11、CITY)sp_executesql N'select * from tableName串前一定要加N一请注意字符(2) 字段名，表名，数摇库名之类作为变量时，必须用动态SQLeg： declare FielsNamevarchar (20)declare sqls nvarchar (1000)set F i e1sName'CITY'Select FielsName from MCITYExec (rselect '+ FielsName +1 from MCITY *)请注意加号前后的单引号的边上要加空格set sqls=，select+ FielsNam

12、e +' from MCITYexec sp_executesql sqls当然将字符串改成变量的形式也可declare s varchar (1000)1 select *+ FielsName +' from MCITY1Exec(s)成功exec sp executesql s此句会报错declare s Nvarchar (1000)注意此处改为nvarchar (1000)set* select '+ fname+1 from from MCITYrExec(s)成功exec sp_executesql s此句正确(3) 输出参数 eg： declare nu

13、m int declare sqls nvarchar (1000)declarestrTableName nvarchar(55) set strTableName=，MCITY*+strTableNameset sqls=，select count() from 'exec (sqls)如何能将exec执行的结果存入变量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(注：以上