오라클 function, decode설명

데이터가 아래와 같습니다.

업체명	계약일자	...	...
(주)고양건설	20000101	...	...
대영테크(주)	20100202	...	...
(합)건축사무소	20090202	...	...
PPC사무소	20090808	...	...
일반설비㈜	20090313	...	...

여기서 제가 하고 싶은건 (주), (합), ㈜ 와 같은 문자 기호등을 제외 시킨 순수한 업체명만을 가지고

다른 데이터와 비교 하고 싶은거죠.

그걸 위해 짠 쿼리는 아래와 같습니다.

decode ( sign(instr(CO_NM,'㈜')),'1', replace(CO_NM,'㈜',''),

decode(instr(CO_NM,'('),'1',substr(CO_NM, instr(CO_NM,'(') + 3,LENGTH(CO_NM))

, '0', CO_NM

, substr(CO_NM, 1, instr(CO_NM,'(')-1)

)

) as CO_NM

하나씩 차근차근 보면

decode( A , B, C, D, E, F ) = A 가 B 이면 C 를 하고 아니면 D이면 E를 하고 아니면 F를 디폴트로 해줘 라는 뜻이죠? ㅎㅎ

쉽게 자바로 본다면

if ( A = B ) { C }

else if ( A = D ) { E }

else F

가 되겠죠

sign(instr(CO_NM,'㈜')),'1', replace('㈜',''),

instr(CO_NM,'㈜') : ㈜의 위치를 알려주죠. 없을 경우는 0

sign() : 양수 일때 1 음수일때 -1 0 일때는 0을 리턴합니다.

즉 sign(instr(CO_NM,'㈜')) 은 특정 문자열이 있냐 없냐를 알수 있습니다.

replace (a,b) : a 를 b 로 바꿔줍니다. (trim으로도 할수 있지만 replace가 더 빠르더라구요..)

substr(A,m,n) : A 문자열을 m부터 n 까지 자릅니다.

LENGTH(CO_NM) : 문자열의 길이를 가져옵니다.

그럼 다시 차근히 소스를 보면

decode ( sign(instr(CO_NM,'㈜')),'1', replace(CO_NM,'㈜',''), // CO_NM 에 ㈜가 있으면 ㈜를 없애고

// CO_NM에 ㈜ 가 없으면

// (주),(합)을 없애기 위한 decode 입니다 .

decode(instr(CO_NM,'('),'1',substr(CO_NM, instr(CO_NM,'(') + 3,LENGTH(CO_NM))

// '(' 괄호가 맨 처음에 와있다면 (주), (합) 자른 나머지를 가져오고

, '0', CO_NM

// '(' 괄호가 없으면 그대로 가져오고

, substr(CO_NM, 1, instr(CO_NM,'(')-1)

// 그게 아니라면 처음부터 '(' 괄호 앞까지 잘라와라

) as CO_NM // 컬럼명은 CO_NM

이런거죠...

( 물론 더 간단한 방법은 많습니다.. 제 소스가 정답은 아니에요!! 저도 많이 서툴러서 부끄럽습니다 ㅠ.ㅠ )

+) 간단하게 아래와 같은 방법도 있네요 ㅎㅎ ㅠ.ㅠ;;

TRIM(SUBSTR(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(C.CO_NM,'(주)',''),'㈜',''),'(합)',''),'(유)',''),'(부도)',''),1,4))

그런데 문제는 계약에 따른 데이터이기 때문에

업체명이 계속 들어 올텐데 (부도), (멜랑꼴리),[어쩔시구] 뭐 이런식으로 잘라 내야 한다면

소스를 매번 거기에 맞춰 바꾸긴 힘들다는거죠... ㅠ.ㅠ

그래서 function 을 쓰는 방법으로 바꾸었습니다.

TB_CD001 이라는 테이블에 아래와 같이 데이터를 넣었어요

( 이 테이블은 공통적으로 쓰는 코드를 모아놓는 테이블입니다. )

코드명	내용	...	...
CO_EXCL_SYM	(주)	...	...
CO_EXCL_SYM	(합)	...	...
CO_EXCL_SYM	㈜	...	...
CO_EXCL_SYM	[어쩔시구]	...	...

CREATE OR REPLACE FUNCTION SSYADM.fc_pr_get_co_excl_sym(p_co_nm VARCHAR2)

// SSYADM의 권한으로 fc_pr_get_co_excl_sym 의 이름의 함수를 만듦니다. 이 함수는 VARCHAR2 타입의 변수 p_co_nm 을 받네요.

RETURN VARCHAR2 IS v_new_co_nm VARCHAR2(100);

// 나중에 v_new_co_nm VARCHAR2(100) 타입의 변수를 돌려 줄거에요.

BEGIN

// 시작합니다.

v_new_co_nm := p_co_nm;

// 들어온 변수를 새로운 변수에 넣어둡니다.

FOR p IN (SELECT 내용

FROM TB_CD001

WHERE 코드명 = 'CO_EXCL_SYM'

ORDER BY SORT_SRL

)

// 아까 테이블에 넣어놓은 내용들을 가지고 와 변수 p 에 넣어요. 여러개이니 FOR 를 사용합니다.

// 루프를 돌려서

LOOP

BEGIN

SELECT REPLACE(v_new_co_nm, p.내용, '') // v_new_co_nm 에 있는 내용들을 공백으로 바꿔가며 없애고

INTO v_new_co_nm // 바뀐 내용을 다시 v_new_co_nm 에 넣어요.

FROM DUAL;

END;

END LOOP;

// 루프가 끝나면서 바뀐 내용들은 v_new_co_nm 에 들어가있겠죠?

RETURN v_new_co_nm;

// 리턴시켜주고

END;

// 끝냅니다.

컴파일 시켜 준 다음

select fc_pr_get_co_excl_sym(co_nm), co_code from tb_pr001

이렇게 사용하면 됩니다.

오라클 function 은 PL/SQL 문법을 아셔야 해요. 공부하기 좋은 사이트 추천 합니다.

http://www.oracleclub.com/oraclelecture.action?lectureType=PLSQL

대충 한번 쭉 훑어보세요. 안보는것 보다는 대충이라도 보는게 훨신 도움됩니다.

어떤 종류가 있고 목차가 어떻다 정도만 기억해 놓으세요.

어짜피 직접 써볼 기회가 생겨야 디테일 하게 봐질거에요.. ㅎㅎ