通过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下测试通过。)
本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/bugchen888/archive/2006/02/24/608152.aspx
分享到:
相关推荐
Windows Server 2008 64位操作系统内SQL Server建立Linked Server连接Oracle
在为SQL SERVER创建ORACLE的连接服务器时,没有ORACLE provider for OLE DB 的连接服务驱动。经网上搜索,把PDF安装说明和驱动程序一起收藏,请各位下载;
from text files such as CSV files, from XML, from other database brands such as Oracle and MySQL, and even from other SQL Server databases. You'll learn techniques for managing metadata, transforming...
3. 配置Oracle 客户端tnsnames.ora 文件,其中需要注意文件中的数据库别名“ORADB”,该名称在下文创建Linked Server 时将会使用到。 ORADB = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = HOSTIP)(PORT = ...
/oracle92/app/oracle/product/9.2.0.1/bin/oracle: ELF 64-bit MSB executable SPARCV9 Version 1, dynamically linked, not stripped 2)SQL> select * from v$version; BANNER -------------------------------...
that allows an application developer to use a third-generation language's native procedure or function calls to access the Oracle database server and control all phases of SQL statement execution....
that allows an application developer to use a third-generation language's native procedure or function calls to access the Oracle database server and control all phases of SQL statement execution....
Support for SQL Server Compact Edition 4.0 is added SQLite data provider User-defined function for SQLite provider is supported Default UniNoCase collation for SQLite provider is added (the ...
that allows an application developer to use a third-generation language‘s native procedure or function calls to access the Oracle database server and control all phases of SQL statement execution....
Oracle in Processes ........................................................................................................... 1 Oracle in Action ........................................................
matlab导入excel代码utl_identify_linked_pairs_in_a_table_across_rows 跨表识别表中的链接对。 关键字:sas sql join合并大数据分析宏oracle teradata mysql sas社区stackoverflow statistics人工智慧AI ...
matlab导入excel代码utl_linked_and_unlinked_clusters_of_servers_that_have_one_or_more_linkages_in_common 具有一个或多个链接的服务器的链接的和未链接的群集。 关键字:sas sql join合并大数据分析宏oracle ...
OCI (Oracle) ........................... no ODBC ................................... no PostgreSQL ............................. no SQLite2 ................................ no SQLite ...............