SQL中调用ORACLE存储过程

SQLServer调用Oracle的存储过程收藏原文如下：通过SQLLinkedServer执行Oracle存储过程小结1举例我们可以通过下面的方法在SQLServer中通过LinkedServer来执行Oracle存储过程。(1)OraclePackagePACKAGETest_PACKAGEASTYPEt_tisTABLEofVARCHAR2(30)INDEXBYBINARY_INTEGER;PROCEDURETest_procedure1(p_BATCH_IDINVARCHAR2,p_NumberINnumber,p_MSGOUTt_t,p_MSG1OUTt_t);ENDTest_PACKAGE;PACKAGEBODYTest_PACKAGEASPROCEDURETest_procedure1(p_BATCH_IDINVARCHAR2,p_NumberINnumber,p_MSGOUTt_t,p_MSG1OUTt_t)ASBEGINp_MSG(1):='c';p_MSG(2):='b';p_MSG(3):='a';p_MSG1(1):='abc';RETURN;COMMIT;EXCEPTIONWHENOTHERSTHENROLLBACK;ENDTest_procedure1;ENDTest_PACKAGE;(2)在SQLServer中通过LinkedServer来执行Oracle存储过程declare@BatchIDnvarchar(40)declare@QueryStrnvarchar(1024)declare@StatusCodenvarchar(100)declare@sqlnvarchar(1024)set@BatchID='AAA'SET@QueryStr='{CALLGSN.Test_PACKAGE.Test_procedure1('''''+@BatchID+''''',''''4'''',{resultset3,p_MSG},{resultset1,p_MSG1})}'(3)执行结果(a)select@sql='SELECT@StatusCode=p_msgFROMOPENQUERY(HI4DB_MS,'''+@QueryStr+''')'execsp_executesql@sql,N'@StatusCodenvarchar(100)output',@StatusCodeoutputprint@StatusCode答案:@StatusCode=’a’(b)select@sql='SELECTtop3@StatusCode=p_msgFROMOPENQUERY(HI4DB_MS,'''+@QueryStr+''')'execsp_executesql@sql,N'@StatusCodenvarchar(100)output',@StatusCodeoutputprint@StatusCode答案:@StatusCode=’a’(c)select@sql='SELECTtop2@StatusCode=p_msgFROMOPENQUERY(HI4DB_MS,'''+@QueryStr+''')'execsp_executesql@sql,N'@StatusCodenvarchar(100)output',@StatusCodeoutputprint@StatusCode答案:@StatusCode=’b’(d)select@sql='SELECTtop1@StatusCode=p_msgFROMOPENQUERY(HI4DB_MS,'''+@QueryStr+''')'execsp_executesql@sql,N'@StatusCodenvarchar(100)output',@StatusCodeoutputprint@StatusCode答案:@StatusCode=’c’(e)SET@QueryStr='{CALLGSN.Test_PACKAGE.Test_procedure1('''''+@BatchID+''''',''''4'''',{resultset1,p_MSG1},{resultset3,p_MSG})}'----(注意这里p_MSG1和p_MSG交换次序了)EXEC('SELECTp_msg1FROMOPENQUERY(HI4DB_MS,'''+@QueryStr+''')')select@sql='SELECT@StatusCode=p_msg1FROMOPENQUERY(HI4DB_MS,'''+@QueryStr+''')'execsp_executesql@sql,N'@StatusCodenvarchar(100)output',@StatusCodeoutputprint@StatusCode答案:@StatusCode=’abc’2上述使用方法的条件(1)LinkServer要使用Microsoft的Driver（MicrosoftOLEDBProviderforOracle）(2)OraclePackage中的Procedure的返回参数是Table类型，目前table只试成功一个栏位。(3)SQLServer的StoreProcedure调用OracleProcedure时，返回参数名字必须和Procedure相同。3上述方法的要点(1)如果要实现“Oracle和SQLServer数据库”之间的Trans处理，则Oracle的Procedure不要有Commit,rollback等语句，让SQLServer的StoreProcedure去控制整个Trans何时commit。(2)假如返回参数大于1个，返回参数的次序可以调换，调用时只返回第一个出现的返回参数，如上面的执行结果(e)。但是输入参数和返回参数的顺序不能调换。(3){resultsetn,p_MSG1}，这里的n，表示返回表的行数。N可以大于等于实际的行数，但不能小于实际的行数，会报错。(4)假如返回表有多行记录,执行select@sql='SELECT@StatusCode=p_msgFROMOPENQUERY(HI4DB_MS,'''+@QueryStr+''')'execsp_executesql@sql,N'@StatusCodenvarchar(100)output',@StatusCodeoutputprint@StatusCode,@StatusCode中的值为最后一行记录的值,如执行结果(a)。4动态SQL语句(1)普通SQL语句可以用Exec执行eg:Select*fromMCITYExec('select*fromMCITY)sp_executesqlN'select*fromtableName'--请注意字符串前一定要加N(2)字段名，表名，数据库名之类作为变量时，必须用动态SQLeg:declare@FielsNamevarchar(20)declare@sqlsnvarchar(1000)set@FielsName='CITY'Select@FielsNamefromMCITY--错误Exec('select'+@FielsName+'fromMCITY')--请注意加号前后的单引号的边上要加空格set@sqls='select'+@FielsName+'fromMCITY'execsp_executesql@sqls当然将字符串改成变量的形式也可declare@svarchar(1000)set@s='select'+@FielsName+'fromMCITY'Exec(@s)--成功execsp_executesql@s--此句会报错declare@sNvarchar(1000)--注意此处改为nvarchar(1000)set@s='select'+@fname+'fromfromMCITY'Exec(@s)--成功execsp_executesql@s--此句正确(3)输出参数eg:declare@numintdeclare@sqlsnvarchar(1000)declare@strTableNamenvarchar(55)set@strTableName='MCITY'set@sqls='selectcount(*)from'+@strTableNameexec(@sqls)如何能将exec执行的结果存入变量@num中declare@numintdeclare@sqlsnvarchar(1000)declare@strTableNamenvarchar(55)set@strTableName='MCITY'set@sqls='select@a=count(*)from'+@strTableNameexecsp_executesql@sqls,N'@aintoutput',@numoutputselect@num（注：以上SQL在sv-02,Qservice下测试通过。）删除重复数据：deletetop(1)fromtablewherenamein(selectnamefromtablegroupbynamehavingcount(name)1)SELECT*FROMtableWHERE(idIN(SELECTMAX(id)FROMtableGROUPBYname))