資源描述:
《sql中調(diào)用oracle存儲過程》由會員上傳分享,免費(fèi)在線閱讀,更多相關(guān)內(nèi)容在工程資料-天天文庫。
1、SQLServer調(diào)用Oracle的存儲過程收藏原文如下:通過SQL??Linked??Server?執(zhí)行Oracle?存儲過程小結(jié)1舉例我們可以通過下面的方法在SQL?Server中通過Linked?Server?來執(zhí)行Oracle?存儲過程。(1)??Oracle?PackagePACKAGE?Test_PACKAGE?AS???????TYPE?t_t?is?TABLE?of?VARCHAR2(30)???????INDEX?BY?BINARY_INTEGER;???????PROCEDURE?Test_p
2、rocedure1??????(?p_BATCH_ID?IN?VARCHAR2,????????p_Number?IN?number,????????p_MSG?OUT?t_t,?SQLServer調(diào)用Oracle的存儲過程收藏原文如下:通過SQL??Linked??Server?執(zhí)行Oracle?存儲過程小結(jié)1舉例我們可以通過下面的方法在SQL?Server中通過Linked?Server?來執(zhí)行Oracle?存儲過程。(1)??Oracle?PackagePACKAGE?Test_PACKAGE?AS????
3、???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,?SQLServer調(diào)用Oracle的存儲過程收藏原文如下:通過SQL??Linked??Server?執(zhí)行Oracle?存儲過程小結(jié)1舉例我們可以通過下面的方
4、法在SQL?Server中通過Linked?Server?來執(zhí)行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_MS
5、G?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????????BE
6、GIN????????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中通過Link
7、ed?Server?來執(zhí)行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'''',{results
8、et?3,?p_MSG},{resultset?1,?p_MSG1})}'(3)執(zhí)行結(jié)果(a)select?@sql='SELECT??@StatusCode=p_msg??FROM?OPENQUERY?(HI4DB_MS,'''+@QueryStr+''')'exec?sp_executesql?@sql,N'@StatusCode?nvarchar(100)?outp