原文:
通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_PACKAGETest_procedure1('''''+@BatchID+'''''''''4''''{resultset 3 p_MSG}{resultset 1 p_MSG1})}'
(3)执行结果
(a)
select @sql'SELECT @StatusCodep_msg FROM OPENQUERY (HI4DB_MS'''+@QueryStr+''')'
exec sp_executesql @sqlN'@StatusCode nvarchar(100) output'@StatusCode output
print @StatusCode
答案@StatusCode’a’
(b)
select @sql'SELECT top 3 @StatusCodep_msg FROM OPENQUERY (HI4DB_MS'''+@QueryStr+''')'
exec sp_executesql @sqlN'@StatusCode nvarchar(100) output'@StatusCode output
print @StatusCode
答案@StatusCode’a’
(c)
select @sql'SELECT top 2 @StatusCodep_msg FROM OPENQUERY (HI4DB_MS'''+@QueryStr+''')'
exec sp_executesql @sqlN'@StatusCode nvarchar(100) output'@StatusCode output
print @StatusCode
答案@StatusCode’b’
(d)
select @sql'SELECT top 1 @StatusCodep_msg FROM OPENQUERY (HI4DB_MS'''+@QueryStr+''')'
exec sp_executesql @sqlN'@StatusCode nvarchar(100) output'@StatusCode output
print @StatusCode
答案@StatusCode’c’
(e)
SET @QueryStr'{CALL GSN Test_PACKAGETest_procedure1('''''+@BatchID+'''''''''4''''{resultset 1 p_MSG1}{resultset 3 p_MSG})}'(注意里p_MSG1p_MSG交换次序)
EXEC('SELECT p_msg1 FROM OPENQUERY (HI4DB_MS'''+@QueryStr+''')')
select @sql'SELECT @StatusCodep_msg1 FROM OPENQUERY (HI4DB_MS'''+@QueryStr+''')'
exec sp_executesql @sqlN'@StatusCode nvarchar(100) output'@StatusCode output
print @StatusCode
答案 @StatusCode’abc’
2 述方法条件
(1) Link ServerMicrosoftDriver〔Microsoft OLE DB Provider for Oracle〕
(2) Oracle Package中Procedure返回参数Table类型目前table
试成功栏位
(3) SQL ServerStore Procedure调Oracle Procedure时返回参数名字必须Procedure相
3 述方法点
(1) 果实现OracleSQL Server数库〞间Trans处理OracleProcedureCommitrollback等语句SQL ServerStore Procedure控制整Trans时commit
(2) 假设返回参数1返回参数次序调换调时返回第出现返回参数面执行结果(e)输入参数返回参数序调换
(3) { resultset n p_MSG1}里n表示返回表行数N等实际行数实际行数会报错
(4) 假设返回表行记录执行
select @sql'SELECT @StatusCodep_msg FROM OPENQUERY (HI4DB_MS'''+@QueryStr+''')'
exec sp_executesql @sqlN'@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 @acount(*) from '+@strTableName
exec sp_executesql @sqlsN'@a int output'@num output
select @num
〔注:SQLsv02Qservice测试通〕
删重复数:
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))
文档香网(httpswwwxiangdangnet)户传
《香当网》用户分享的内容,不代表《香当网》观点或立场,请自行判断内容的真实性和可靠性!
该内容是文档的文本内容,更好的格式请下载文档