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>

관련 link : http://social.msdn.microsoft.com/Forums/en-US/sqlsmoanddmo/thread/12e9c126-496b-49ad-9bf7-4320f7d09af8

: 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;

----------------------------------------------------------------

관련 link : http://dialup.egloos.com/927560

: PostgreSQL Procedure

SELECT * FROM OPENQUERY(<Linked Server Name>, 'SELECT * FROM <Procedure Name>');