Oracle管理SQL中调用ORACLE存储过程

1、SQL Server 调用 Oracle 的存储过程收藏G 纠原文如下：通过 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 nu mber

2、,p_MSG OUT 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 nu mber,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;

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

4、, p_MSG1)(3) 执行结果select sql=SELECT StatusCode=p_msg FROM OPENQUERY (HI4DB_MS,”+QueryStr+)exec sp_executesql sql,NStatusCode nvarchar(100) output,StatusCode outputprint StatusCode答案：StatusCode= a(b)select sql=SELECT top 3 StatusCode=p_msg FROM OPENQUERY (HI4DB_MS,”+QueryStr+”)exec sp_executesql sql,N

5、StatusCode nvarchar(IOO) 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 StatusC

6、ode=p_msg FROM 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

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

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

9、 commit。假如返回参数大于 1 个，返回参数的次序可以调换，调用时只返回第一个出现的返回参数，如上面的执行结果(e)。但是输入参数和返回参数的顺序不能调换。 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,StatusCo

10、de outputprint StatusCode, 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 = CI

11、TYSelect FielsName from MCITY-错误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)-注意此处改为

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