SQL Server Linked Server 쿼리 활용

2012. 4. 9. 15:56

SQL Server Linked Server 쿼리 활용 Work/SQL Server2012. 4. 9. 15:56

SQL Server로 Linked Query를 사용하여 작업할 경우가 많다.

Linked Query 자체를 생성하는 것이 힘겨운 경우도 있고... 우여곡절이 많다. ㅠㅠ

이번 기록은 Linked Query를 일단 생성한 후의 활용에 대해서 정리한다.

Query

: SELECT * FROM <Linked Server Name>..<Owner Name>.<Table Name> WHERE ~~;

-- 성능 않좋고... 어딘가에 써먹을 데가 별로 없다.

-- 쿼리 작성이 간단하다.

-- 4-Part 라고 한다.

-- INSERT, UPDATE, DELETE 문장을 보통 쿼리문과 비슷하게 사용한다.

: SELECT * FROM OPENQUERY(<Linked Server Name>, 'SELECT * FROM <Table Name> WHERE ~~');

-- 성능이 나쁘지 않고, 자주 사용.

-- 쿼리를 문자열로 조합하기 귀찮음.

-- OPENQUERY 라고 한다.

-- INSERT의 경우 OPENQUERY 내의 SELECT 문에 WHERE 1<>1 로 UPDATE, DELETE 문은 검색 제한자를 정확히 써서 인덱스를 잘 사용하면 성능이 봐줄만 하다.

-- OPENQUERY 내의 문자열은 완성된 형태의 문자열만 가능하며, 변수는 받을 수 없다. <-- 이게 치명적인 귀찮음...

Procedure 호출

: Oracle Procedure

execute ('call <OracleSchema>.<OracleProcedureName>()') at <Linked Server Name>

: Oracle Package

SELECT *

FROM OPENQUERY(ls_name , '{CALL user1.upk_test.up_test_inpkg(1111,''222'', {resultSet 1, po_RecordSet }}');

"sqlServer에 linked server로 oracle에 연결하고, oracle의 sp를 call한 후, 응답코드에 따라
이후 작업 진행"
위와 같은 방법보다는 application에서 control하는 것이 좋을 듯 하지만, 어쩔 수 없이 이렇게
해야만 하는 경우가 있어, 코드 부스러기 주워담아 아래와 같이 해결했다.
return값을 table형식이 아닌 number형식으로 받는 방법을 몰라서 응답코드를 table형식으로 return
하도록 처리하였다.(이에 대해서 좀 더 효율적인 방법에 대해서 댓글 부탁합니다.)
CASE1) oracle에서 package를 작성
1. oracle에서 패키지 작성
----------------------------------------------------------------
create or replace package upk_test
as
    type tbltype is table of number(10)
    index by pls_integer;
    procedure up_test_inpkg(
    pi_nParam1        IN NUMBER,
    pi_szParam1       IN VARCHAR2,
    po_RecordSet      OUT TblType
    );
end upk_test;
/
create or replace package body upk_test
as
    procedure up_test_inpkg(
    pi_nParam1        IN NUMBER,
    pi_szParam1       IN VARCHAR2,
    po_RecordSet      OUT TblType
    ) as
    begin
    /**
    business logic 구현
    ......
    **/

    --넘겨줄 처리결과 코드 세팅
    po_RecordSet(1) := v_nResult;
    end up_test_inpkg;
end upk_test;
----------------------------------------------------------------
2. sqlserver에서 openquery를 통해 query
----------------------------------------------------------------
SELECT *
FROM OPENQUERY(ls_name , '{CALL user1.upk_test.up_test_inpkg(1111,''222'', {resultSet 1, po_RecordSet }}');
----------------------------------------------------------------

CASE2) oracle에서 package에 type선언만 하고 해당 type을 sp에서 참조
----------------------------------------------------------------
create or replace package upk_types
as
    type numtbltype is table of number(10)
    index by pls_integer;
end upk_types;
/

create or replace procedure up_test(
    pi_nParam1        IN NUMBER,
    pi_szParam1       IN VARCHAR2,
    po_RecordSet      OUT upk_type.numtbltype
)
as

begin
    /**
    business logic 구현
    ......
    **/

    --넘겨줄 처리결과 코드 세팅
    po_RecordSet(1) := v_nResult;
end;
----------------------------------------------------------------