《SQL中调用ORACLE存储过程》由会员分享，可在线阅读，更多相关《SQL中调用ORACLE存储过程（6页珍藏版）》请在金锄头文库上搜索。
1、SQL Server 调用调用 Oracle 的存储过程的存储过程 收藏收藏 原文如下：通过 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
2、IN number,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 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_pr
3、ocedure1;END Test_PACKAGE;(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,results
4、et 1, p_MSG1)(3)执行结果(a)select sql=SELECT StatusCode=p_msg FROM OPENQUERY (HI4DB_MS,+QueryStr+)exec sp_executesql sql,NStatusCode nvarchar(100) output,StatusCodeoutputprint StatusCode答案:StatusCode=a(b)select sql=SELECT top 3 StatusCode=p_msg FROM OPENQUERY (HI4DB_MS,+QueryStr+)exec sp_executesql sql,
5、NStatusCode nvarchar(100) output,StatusCodeoutputprint 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,StatusCodeoutputprint StatusCode答案:StatusCode=b(d)select sql=SELECT top 1 StatusCode
6、=p_msg FROM OPENQUERY (HI4DB_MS,+QueryStr+)exec sp_executesql sql,NStatusCode nvarchar(100) output,StatusCodeoutputprint 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 F
7、ROM OPENQUERY (HI4DB_MS,+QueryStr+) select sql=SELECT StatusCode=p_msg1 FROM OPENQUERY (HI4DB_MS,+QueryStr+)exec sp_executesql sql,NStatusCode nvarchar(100) output,StatusCodeoutputprint StatusCode答案: StatusCode=abc2 上述使用方法的条件(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 时，返回参数名字必须和 Procedure 相同。3 上述方法的要点(1) 如果要实现“Oracle 和 SQL Server 数据库”之间的 Trans 处理，则 Oracle 的 Procedure 不要有 Commit,rollback 等语句，让 SQL Server 的 Store Procedure 去控制整个 Trans 何时 commit。(2
9、) 假如返回参数大于 1 个，返回参数的次序可以调换，调用时只返回第一个出现的返回参数，如上面的执行结果(e)。但是输入参数和返回参数的顺序不能调换。(3) 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,StatusCodeou
10、tput print 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 =
11、 CITYSelect 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(100
12、0) - 注意此处改为 nvarchar(1000)set s = select + 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
13、 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 在 sv-02,Qservice 下测试通过。） 删除重复数据： delete top(1) from table where name in( select name from table group by name having count(name)1 )SELECT *FROM table WHERE (id IN(SELECT MAX(id) FROM table GROUP BY name)
