SQL SERVER 数据库通过连接服务器 调用ORACLE 数据库中的存储过程

时间:2022-12-17 21:48:13

通过SQL  Linked  Server 执行Oracle 存储过程小结
1 举例
我们可以通过下面的方法在SQL Server中通过Linked Server 来执行Oracle 存储过程。
(1)  Oracle Package
PACKAGE Test_PACKAGE AS
       TYPE t_t is TABLE of
通过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('''''+@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 output
print @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 output
print @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 output
print @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 output
print @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 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,'''+@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)字段名,表名,数据库名之类作为变量时,必须用动态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 ' +@strTableName
exec (@sqls)
如何能将exec执行的结果存入变量@num中
declare  @num int
declare  @sqls nvarchar(1000)
declare  @strTableName nvarchar(55)
set  @strTableName='MCITY'
set  @sqls='select @a=count(*) from '+@strTableName
exec  sp_executesql @sqls,N'@a int output',@num output
select  @num

(注:以上SQL在sv-02,Qservice下测试通过。)
 

 

 

方法二:

 

 

Oracle (Oracle Provider for OLE DB) (8.1.7.3.0) 允许对 Oracle 数据库中的数据进行分布式查询。对 Oracle (Oracle Provider for OLE DB) 的支持如下:

Oracle 32 位
 仅支持进程外访问接口
 
Oracle 64 位
 无
 
创建链接服务器以访问 Oracle 数据库实例
确保运行 Microsoft SQL Server 的服务器上的 Oracle 客户端软件处于访问接口所要求的级别。Oracle (Oracle Provider for OLE DB) (8.1.7.3.0) 要求安装 Oracle Client 8.1.7.0。

在运行 SQL Server 的服务器上创建指向 Oracle 数据库实例的别名。有关详细信息,请参阅 Oracle 文档。

执行 sp_addlinkedserver 创建链接服务器,同时指定 OraOLEDB.Oracle 作为 provider_name,并指定 Oracle 数据库的别名作为 data_source。

以下示例假定已将别名定义为 DQORA8:

 复制代码
exec sp_addlinkedserver @server='OrclDB',
    @srvproduct='Oracle',
    @provider='OraOLEDB.Oracle',
    @datasrc='DQORA8'
 

使用 sp_addlinkedsrvlogin 创建从 SQL Server 登录名到 Oracle 登录名的登录名映射。

以下示例通过使用 Oracle 登录名 OrclUsr 和密码 OrclPwd,将 SQL Server 登录名 Joe 映射到在步骤 3 中定义的链接服务器:

 复制代码
sp_addlinkedsrvlogin @rmtsrvname = 'OrclDB',
   @useself = 'false',
   @locallogin = 'Joe',
   @rmtuser = 'OrclUsr',
   @rmtpassword = 'OrclPwd'
 

在 Oracle 链接服务器中引用表时,请遵循以下规则:

如果在 Oracle 中创建的表名和列名没有使用带引号的标识符,请使用字母全部大写的名称。


如果在 Oracle 中创建的表名和列名使用了带引号的标识符,请使用与在 Oracle 中创建名称时所用字母完全相同的字母大小写形式。


INSERT 语句应为表中的所有列提供值,即使表中的某些列可能为 NULL 或具有默认值。


此访问接口不支持将 datetime 文字作为参数按以下格式直接传递:

 复制代码
EXEC 'string' '<datetime-literal>' AT <oraclelinkedserver>
 
例如,不支持按以下方式使用 datetime 文字 '14-sep-94':

 复制代码
EXEC('INSERT INTO DYNORCL.SALES(ID, ORD_NO, ORD_DATE, QTY)
   VALUES (?, ?, ?, ?)', '6380', '6871', '14-sep-94', 5) AT OrclDB
 
而是应创建一个 datetime 变量,为其赋文字值,然后调用将变量作为参数的 EXEC:

 复制代码
DECLARE @v1 datetime
SET @v1 = CONVERT(datetime,'14-sep-94')
EXEC('INSERT INTO DYNORCL.SALES(ID, ORD_NO, ORD_DATE, QTY)
   VALUES (?, ?, ?, ?)', '6380', '6871', @v1, 5) AT OrclDB
 

限制
SQL Server 不支持对 Oracle 表使用 AVG 聚合函数,因为 Oracle 访问接口返回 OLE DB 类型 DBTYPE_VARNUMERIC,而 SQL Server 不支持 DBTYPE_VARNUMERIC。