SQL中调用ORACLE存储过程.doc
SQL Server 调用Oracle的存储过程 收藏 原文如下：通过SQL Linked Server 执行Oracle 存储过程小结1 举例我们可以通过下面的方法在SQL Server中通过Linked Server 来执行Oracle 存储过程。(1) Oracle PackagePACKAGE Test_PACKAGE AS TYPE 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 AS PROCEDURE Test_procedure1 ( p_BATCH_ID IN VARCHAR2, p_Number IN number, p_MSG OUT t_t, p_MSG1 OUT t_t ) AS BEGIN p_MSG(1):='c' p_MSG(2):='b' p_MSG(3):='a'p_MSG1(1):='abc' RETURN; COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK; 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 outputprint 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=abc2 上述使用方法的条件(1) Link Server要使用Microsoft的Driver（Microsoft OLE DB Provider for Oracle）(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) 假如返回参数大于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,N'StatusCode nvarchar(100) output',StatusCode output print StatusCode, StatusCode中的值为最后一行记录的值, 如执行结果(a)。4 动态SQL语句(1)普通SQL语句可以用Exec执行eg: Select * from MCITY Exec('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 by name having count(name)>1 )SELECT *FROM table WHERE (id IN(SELECT MAX(id) FROM table GROUP BY name)
