[sql server] 通过SQL Linked Server 执行Oracle 存储过程小结

通过SQL  Linked  Server 执行Oracle 存储过程小结

1 举例

我们可以通过下面的方法在SQL Server中通过Linked Server 来执行Oracle 存储过程。

(1)  Oracle Package

PACKAGE 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('''''[email protected]+''''',''''4'''',{resultset 3, p_MSG},{resultset 1, p_MSG1})}'

(3)执行结果

(a)

select @sql='SELECT  @StatusCode=p_msg  FROM OPENQUERY (HI4DB_MS,'''[email protected]+''')'

exec sp_executesql @sql,N'@StatusCode nvarchar(100) output',@StatusCode output

print @StatusCode

答案:@StatusCode=’a’

(b)

select @sql='SELECT top 3 @StatusCode=p_msg  FROM OPENQUERY (HI4DB_MS,'''[email protected]+''')'

exec sp_executesql @sql,N'@StatusCode nvarchar(100) output',@StatusCode output

print @StatusCode

答案:@StatusCode=’a’

(c)

select @sql='SELECT top 2 @StatusCode=p_msg  FROM OPENQUERY (HI4DB_MS,'''[email protected]+''')'

exec sp_executesql @sql,N'@StatusCode nvarchar(100) output',@StatusCode output

print @StatusCode

答案:@StatusCode=’b’

(d)

select @sql='SELECT top 1 @StatusCode=p_msg  FROM OPENQUERY (HI4DB_MS,'''[email protected]+''')'

exec sp_executesql @sql,N'@StatusCode nvarchar(100) output',@StatusCode output

print @StatusCode

答案:@StatusCode=’c’

(e)

SET @QueryStr='{CALL GSN. Test_PACKAGE.Test_procedure1('''''[email protected]+''''',''''4'''',{resultset 1, p_MSG1},{resultset 3, p_MSG})}'----(注意这里p_MSG1和p_MSG交换次序了)

EXEC('SELECT  p_msg1   FROM OPENQUERY (HI4DB_MS,'''[email protected]+''')')

select @sql='SELECT @StatusCode=p_msg1  FROM OPENQUERY (HI4DB_MS,'''[email protected]+''')'

exec sp_executesql @sql,N'@StatusCode nvarchar(100) output',@StatusCode output

print @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时，返回参数名字必须和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,'''[email protected]+''')' 

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)字段名，表名，数据库名之类作为变量时，必须用动态SQL

eg:  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 int

declare  @sqls nvarchar(1000)

declare  @strTableName nvarchar(55)

set  @strTableName='MCITY'

set  @sqls='select count(*) from ' [email protected]

exec (@sqls)

如何能将exec执行的结果存入变量@num中

declare  @num int

declare  @sqls nvarchar(1000)

declare  @strTableName nvarchar(55)

set  @strTableName='MCITY'

set  @sqls='select @a=count(*) from '[email protected]

exec  sp_executesql @sqls,N'@a int output',@num output

select  @num

（注：以上SQL在sv-02,Qservice下测试通过。）

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/bugchen888/archive/2006/02/24/608152.aspx