SQL中调用RCLE存储过程

1、SQL Server 调用Oracle的存储过程收藏aLa原文如下：通过SQL Linked Server 执行Oracle 存储过程小结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_BATCH_ID IN VARCHAR2,p_Number IN number,p_MSG OUT

2、 t_t,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_MSG(1):=c;p_MSG(2):=b;p_MSG(3):=a;p_MSG1(1):=abc;RETURN;COMMIT;EXCEPTIONWHEN OTHERS THENROLLBACK;END Test_procedure1;END Test_PA

3、CKAGE;(2)在SQL Server中通过Linked Server 来执行 Oracle 存储过程declare BatchID nvarchar (40)declare QueryStr nvarchar (1024)declare StatusCode nvarchar(100)declare sql nvarchar(1024)set BatchID=AAASET QueryStr=CALL GSN. Test_PACKAGE.Test_procedure1( +BatchID+, 4”,resultset 3, p_MSG,resultset 1, p_MSG1)(3)执行结果(

4、a)select sql=SELECT StatusCode=p_msg FROM OPENQUERY (HI4DB_MS,”+ QueryStr+)exec sp_executesql sql,NStatusCode nvarchar(100) output,StatusCode outp utprint StatusCode答案：StatusCode= a(b)select sql=SELECT top 3 StatusCode=p_msg FROM OPENQUERY (HI4DB_MS,”+QueryStr+”)exec sp_executesql sql,NStatusCode nv

5、archar(100) output,StatusCode outputprint StatusCode答案：StatusCode= a(c)select sql=SELECT top 2 StatusCode=p_msg FROM OPENQUERY (HI4DB_MS,+QueryStr+)exec sp_executesql sql,NStatusCode nvarchar(100) output,StatusCode outputprint StatusCode答案：StatusCode= b(d)select sql=SELECT top 1 StatusCode=p_msg FRO

6、M OPENQUERY (HI4DB_MS,+QueryStr+)exec sp_executesql sql,NStatusCode nvarchar(100) output,StatusCode outputprint StatusCode答案：StatusCode= c(e)SET QueryStr=CALL GSN. Test_PACKAGE.Test_procedure1( +BatchID+,4”,resultset 1, p_MSG1,resultset 3, p_MSG)-(注意这里 p_MSG1 和 p_MSG 交换次序了)EXEC(SELECT p_msg1 FROM OP

7、ENQUERY (HI4DB_MS,”+QueryStr+) select sql=SELECT StatusCode=p_msg1 FROM OPENQUERY (HI4DB_MS,”+ QueryStr+”)exec sp_executesql sql,NStatusCode nvarchar(100) output,StatusCode outputprint StatusCode答案：StatusCode= abc 2上述使用方法的条件(1) Link Server要使用 Microsoft 的 Driver (Microsoft OLE DB Provider for Oracle

8、)(2) Oracle Package 中的Procedure 的返回参数是 Table类型，目前table 只试成功一个栏位。(3) SQL Server 的 Store Procedure 调用 Oracle Procedure 时，返回参数名字必须和 Pr ocedure 相同。3上述方法的要点如果要实现 Oracle SQL Server数据库”之间的 Trans处理，则 Oracle的Proce dure 不要有 Commit,rollback等语句，让 SQL Server 的 Store Procedure去控制整个 Trans 何日c commit 。(2)假如返回参数大于 1

9、个，返回参数的次序可以调换，调用时只返回第一个出现的返回参数，如上面的执行结果(e)o但是输入参数和返回参数的顺序不能调换。resultset n, p_MSG1 ,这里的n ,表示返回表的行数。N可以大于等于实际的行数，但不能小于实际的行数，会报错。(4)假如返回表有多行记录，执行select sql=SELECT StatusCode=p_msg FROM OPENQUERY (HI4DB_MS,”+ QueryStr+)exec sp_executesql sql,NStatusCode nvarchar(100) output,StatusCode outp utprint Statu

10、sCode, StatusCode中的值为最后一行记录的值，如执行结果（a）。4动态SQL语句(1)普通SQL语句可以用Exec执行eg: Select * from MCITYExec(select * from MCITY)sp_executesql Nselect * from tableName-请注意字符串前一定要加N(2)字段名，表名，数据库名之类作为变量时，必须用动态 SQLeg: declare FielsName varchar(20)declare sqls nvarchar(1000)set FielsName = CITYSelect FielsName from MC

11、ITY-错误Exec(select + FielsName + from MCITY )-请注意加号前后的单引号的边上要加空格set sqls=select + FielsName + from MCITY exec sp_executesql sqls当然将字符串改成变量的形式也可declare s varchar(1000)set s = select + FielsName + from MCITYExec(s)- 成功exec sp_executesql s -此句会报错declare s Nvarchar(1000)-注意此处改为 nvarchar(1000)set s = sele

12、ct + fname + from from MCITYExec(s)- 成功exec sp_executesql s -止匕句正确(3)输出参数 eg: declare num intdeclare sqls nvarchar(1000) declare strTableName nvarchar(55)set strTableName=MCITYset sqls=select count(*) from +strTableNameexec (sqls)如何能将exec执行的结果存入变量num中declare num intdeclare sqls nvarchar(1000)declare strTableName nvarchar(55)set strTableName=MCITYset sqls=select a=count(*) from +strTableNameexec sp_executesql sqls,Na int output,num outputselect num(注：以上 SQL在