SQL中调用RCLE存储过程

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 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_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='AAA'SET QueryStr='CALL GSN. Test_PACKAGE.Test_procedure1( ""'+BatchID+""',''' '4"”,resultset 3, p_MSG,resultset 1, p_MSG1)'(3)执行结果(a)select sql='SELECT StatusCode=p_msg FROM OPENQUERY (HI4DB_MS,”'+ QueryStr+'")'exec sp_executesql sql,N'StatusCode 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,N'StatusCode nvarchar(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,N'StatusCode nvarchar(100) output',StatusCode outputprint StatusCode答案：StatusCode= ' b'(d)select sql='SELECT top 1 StatusCode=p_msg FROM OPENQUERY (HI4DB_MS,'"+QueryStr+"')'exec sp_executesql sql,N'StatusCode 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 OPENQUERY (HI4DB_MS,”'+QueryStr+"')') select sql='SELECT StatusCode=p_msg1 FROM OPENQUERY (HI4DB_MS,”'+ QueryStr+”')'exec sp_executesql sql,N'StatusCode nvarchar(100) output',StatusCode outputprint StatusCode答案：StatusCode=' abc '2上述使用方法的条件(1) Link Server要使用 Microsoft 的 Driver (Microsoft OLE DB Provider for Oracle )(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个，返回参数的次序可以调换，调用时只返回第一个出现的返回参数，如上面的执行结果(e)o但是输入参数和返回参数的顺序不能调换。resultset n, p_MSG1 ,这里的n ,表示返回表的行数。N可以大于等于实际的行数，但不能小于实际的行数，会报错。(4)假如返回表有多行记录，执行select sql='SELECT StatusCode=p_msg FROM OPENQUERY (HI4DB_MS,”'+ QueryStr+'")'exec sp_executesql sql,N'StatusCode nvarchar(100) output',StatusCode outp utprint StatusCode, StatusCode中的值为最后一行记录的值，如执行结果（a）。4动态SQL语句(1)普通SQL语句可以用Exec执行eg: Select * from MCITYExec('select * from MCITY)sp_executesql N'select * from tableName'-请注意字符串前一定要加N(2)字段名，表名，数据库名之类作为变量时，必须用动态 SQLeg: declare FielsName varchar(20)declare sqls nvarchar(1000)set FielsName = 'CITY'Select 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 MCITY'Exec(s)- 成功exec sp_executesql s -此句会报错declare s Nvarchar(1000)-注意此处改为 nvarchar(1000)set s = 'select ' + fname + ' from from MCITY'Exec(s)- 成功exec sp_executesql s -止匕句正确(3)输出参数 eg: declare num intdeclare sqls nvarchar(1000) declare strTableName nvarchar(55)set strTableName='MCITY'set sqls='select count(*) from ' +strTableNameexec (sqls)如何能将exec执行的结果存入变量num中declare num intdeclare sqls nvarchar(1000)declare strTableName nvarchar(55)set strTableName='MCITY'set sqls='select a=count(*) from '+strTableNameexec sp_executesql sqls,N'a int output',num outputselect num(注：以上 SQL在sv-02,Qservice 下测试通过。)删除重复数据：delete top(1) from table where name in( select name from table group byname having count(name)>1 )SELECT *FROM table WHERE (id IN(SELECT MAX(id) FROM table GROUPBY name)