SQL Server Linked Server 쿼리 활용
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;----------------------------------------------------------------
관련 link : http://dialup.egloos.com/927560
: PostgreSQL Procedure
SELECT * FROM OPENQUERY(<Linked Server Name>, 'SELECT * FROM <Procedure Name>');