SQL中调用ORACLE存储过程45.docVIP

SQL Server 调用Oracle的存储过程 收藏 原文如下：通过SQL??Linked??Server?执行Oracle?存储过程小结1 举例我们可以通过下面的方法在SQL?Server中通过Linked?Server?来执行Oracle?存储过程。(1)??Oracle?PackagePACKAGE?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=AAASET?@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?outputprint?@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?outputprint?@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?outputprint?@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?outputprint?@StatusCode答案:@StatusCode=’c’(e)SET?@QueryStr={C