OPENQUERY

※ 주의사항

• OPENQUERY로 전달하는 쿼리는 연결된 서버의 쿼리 문법이어야 합니다. (ex. MS의 T-SQL을 Oracle DB에서 사용할 수 없음)

• MSSQL 에서의 빈 문자열('')은 오라클에서 NULL과 같습니다.

FROM OPENQUERY

SELECT

SELECT [컬럼명] FROM OPENQUERY([연결된 서버별칭], 'SELECT [컬럼명] FROM [테이블]')

--ex 1)
SELECT * FROM OPENQUERY(ORCL, 'SELECT * FROM BA030T')
--ex 2)
SELECT * FROM OPENQUERY(ORCL, 'SELECT itemcd, itemnm FROM BA030T WHERE useyn = ''Y''')

SELECT : 외부 변수 처리 예시

DECLARE @v_useyn varchar(1) = 'Y'

DECLARE @v_query nvarchar(1000) = '
    SELECT * FROM OPENQUERY(ORCL, ''SELECT itemcd, itemnm FROM BA030T WHERE useyn = ''''' + @v_useyn + ''''''')'

PRINT @v_query
EXEC (@v_query)

DELETE

DELETE FROM OPENQUERY([연결된 서버별칭], 'SELECT [컬럼명] FROM [테이블]')

--ex)
DELETE FROM OPENQUERY(ORCL, 'SELECT * FROM BA030T WHERE itemcd = ''100-17-011''')

INSERT INTO (연결된 서버 → MSSQL)

INSERT INTO [테이블명]
SELECT [컬럼명] FROM OPENQUERY ([연결된 서버별칭], 'SELECT [컬럼명] FROM [테이블명]')

--ex)
INSERT INTO BA030T
		(	itemcd, itemnm, itemacnt, insiz, spec	)
	SELECT * FROM OPENQUERY(ORCL, '
	SELECT	itemcd, itemnm, itemacnt, insiz, spec 
	FROM BA030T 
	WHERE itemcd = ''#361-UC-106-4-01''')

INSERT INTO OPENQUERY

INSERT INTO (MSSQL → 연결된 서버)

INSERT INTO OPENQUERY ([연결된 서버별칭], 'SELECT [컬럼명] FROM [테이블명]')
SELECT [컬럼명] FROM [테이블명];

--ex 1)
INSERT INTO OPENQUERY (ORCL, 'SELECT * FROM BA030T')
SELECT * FROM BA030T WHERE itemcd <> ''

--ex 2)
INSERT INTO OPENQUERY (ORCL, 'SELECT itemcd, itemnm FROM BA030T')
SELECT itemcd, itemnm FROM BA030T WHERE itemcd = '100-17-011'

UPDATE OPENQUERY

UPDATE

UPDATE OPENQUERY([연결된 서버별칭], 'SELECT [컬럼명] FROM [테이블]') SET [컬럼명] = [값]

--ex)
UPDATE OPENQUERY(ORCL, 'SELECT * 
						FROM BA030T 
						WHERE itemcd = ''100-17-011''') SET
		BNATUR = 'A10',
		SETBNATUR = 'B20'

Execute Procedure

MS-SQL

linked server to oracle

DECLARE @p_itemcd varchar(100) = '#361-UC-106-01',
		@p_itemnm varchar(100) = 'A',
		@p_result_code varchar(100) = '',
		@p_result_msg varchar(100) = '';

EXECUTE ('BEGIN P_ORACLE_TEST(?, ?, ?, ?); END;', @p_itemcd, @p_itemnm, @p_result_code OUTPUT, @p_result_msg OUTPUT) AT ORCL;
SELECT @p_result_code, @p_result_msg

Oracle

DECLARE P_CODE VARCHAR2(100);
        P_MSG VARCHAR2(100);
BEGIN
    P_ORACLE_TEST ('#361-UC-106-01a', 'A', P_CODE, P_MSG);
    
    DBMS_OUTPUT.PUT_LINE('R1 :  ' || P_CODE);
    DBMS_OUTPUT.PUT_LINE('R2 :  ' || P_MSG);
END;a

Difference

ORACLE ↔ MS SQL

Oracle	MS SQL
||	+
FROM DUAL	FROM절 삭제
LENGTH	LEN
MOD	%
SYSDATE	GETDATE()
NVL(a, b)	ISNULL(a, b)
TO_CHAR(날짜, 'YYYY-MM-DD')	CONVERT(VARCHAR(10), 날짜, 120)
TO_DATE(문자열)	CONVERT(DATETIME, 문자열)

ETC

Oracle SQL Developer 다운로드 (필요 시)