오라클 SQL 개발 가이드 ® 조 성 복 기술본부 DB기술자문팀 한국 오라클

목차 최대값,최소값 구하기 일련번호 생성하기 IF...THEN...ELSE... 로직 처리 IF...NOT FOUND 로직 처리 조인 형태별 로직 튜닝 하나의 SQL로 통합 레포팅 데이터 컨버전

최대값, 최소값 구하기 SELECT MIN(COL2) FROM TAB WHERE COL1 = ‘VALUE1’; SELECT MAX(COL2) FROM TAB WHERE COL1 = ‘VALUE1’; TAB:IND:COL1+COL2 SELECT /*+ INDEX_DESC(TAB IND) */ COL2 FROM TAB WHERE COL1 = ‘VALUE1’ AND ROWNUM = 1; SELECT /*+ INDEX_ASC(TAB IND) */ COL2 FROM TAB WHERE COL1 = ‘VALUE1’ AND ROWNUM = 1; 인덱스는 미리 소팅되어 있다는 점을 활용하여 인덱스 1건,데이터 1건을 읽어서 최대값과 최소값을 구할 수 있다.

최대값, 최소값 구하기 < 계약 테이블 > 계약번호 NOT NULL VARCHAR2(24), 계약금액 NUMBER(15), ...... 1997년12월1일에 발생한 계약 중에 계약금액이 최대인 계약번호를 구하라. SELECT SUBSTR(MAX(LPAD(계약금액,'15','0')||계약번호),16,24) FROM 계약 WHERE 계약일자 = '19971201'; 인덱스가 없는 경우 SUBSTR과 MAX 함수를 이용하여 최대값을 구할 수 있다.

최대값, 최소값 구하기 1997년12월1일 발생한 계약 중 계약금액이 최대인 모든 계약번호를 구하라. SELECT 계약번호, 계약금액 FROM 계약 WHERE 계약금액 = (SELECT MAX(계약금액) WHERE 계약일자 = ‘19971201’) AND 계약일자 = ‘19971201’; 최대인 건이 여러 개일 경우에는 서브쿼리를 이용하여 모든 건을 구할 수 있다.

일련번호 채번하기 연속성은 보장하지 않고 유일성만 보장해도 되는 경우 CREATE SEQUENCE TAB_SEQ START WITH 1 INCREMENT BY 1; INSERT INTO TAB (SEQNO, COL1, COL2) VALUES (TAB_SEQ.NEXTVAL, :VALUE1, :VALUE2); 시퀀스를 이용하는 경우 유일성은 보장되나 연속성은 보장되지 않을 수도 있다.

일련번호 채번하기 연속성과 유일성을 동시에 보장해야 하는 경우 CREATE UNIQUE INDEX SEQNO_IDX ON TAB (SEQNO); INSERT INTO TAB (SEQNO, COL1, COL2) SELECT /*+ INDEX_DESC(A SEQNO_IDX) */ NVL(MAX(SEQNO),0)+1, :VALUE1, :VALUE2 FROM TAB A WHERE ROWNUM=1; 인덱스를 이용하는 경우 유일성과 연속성을 동시에 보장할 수 있다.

IF...THEN...ELSE... 로직 처리 < 사용자별권한 테이블 > 사용자ID NOT NULL VARCHAR2(13), 메뉴ID NOT NULL VARCHAR2(10), 사용권한 NOT NULL VARCHAR2(1), <- C/R/U/D 값 중 하나 ...... 사용자별 메뉴별 사용권한 4가지를 Y/N로 표시하라. SELECT USERID,MENUID, NVL(MIN(DECODE(사용권한,C,Y)),N) AS 생성권한, NVL(MIN(DECODE(사용권한,R,Y)),N) AS 읽기권한, NVL(MIN(DECODE(사용권한,U,Y)),N) AS 수정권한, NVL(MIN(DECODE(사용권한,D,Y)),N) AS 삭제권한 FROM 사용자별권한 GROUP BY USERID,MENUID; DECODE 함수를 이용하여 IF..THEN...ELSE... 로직을 처리할 수 있다.

IF...THEN...ELSE... 로직 처리 < 계약 테이블 > 계약번호 NOT NULL VARCHAR2(24), 계약금액 NUMBER(15), 계약차수 VARCHAR2(2), ...... 1997년도에 발생한 계약의 월별 계약금액 합계를 구하라. SELECT NVL(SUM(DECODE(SUBSTR(CONTDTC,5,2),'01',CONTAMT)),0) AS "1월 계약액", NVL(SUM(DECODE(SUBSTR(CONTDTC,5,2),'02',CONTAMT)),0) AS "2월 계약액", NVL(SUM(DECODE(SUBSTR(CONTDTC,5,2),'03',CONTAMT)),0) AS "3월 계약액", ...... NVL(SUM(DECODE(SUBSTR(CONTDTC,5,2),'12',CONTAMT)),0) AS "12월 계약액" FROM 계약 WHERE 계약일자 LIKE ‘1997%’;

IF...NOT FOUND 로직 처리 CUSTOMER_TMP 확인대상 잠정고객 정식등록고객 ID_NO HNAME ENAME GRADE CUSTOMER ID_NO HNAME ENAME GRADE

IF...NOT FOUND 로직 처리 잠정고객 중에 정식고객으로 존재하면 E00 아니면 E01, E00인 경우, GRADE 값이 같으면 G00, 다르면 G01로 출력하라. DECLARE C CURSOR FOR SELECT ID_NO, GRADE FROM CUSTOMER_TMP ; FOR ( ; ; ) { FETCH C INTO :ID_NO, :GRADE1 ; SELECT COUNT(*) INTO :CNT_ID FROM CUSTOMER WHERE ID_NO = :ID_NO; IF ( CNT_ID == 0 ) THEN CHECK_EXIST = "E01"; ELSE { CHECK_EXIST = "E00" ; SELECT GRADE INTO :GRADE2 FROM CUSTOMER WHERE ID_NO = :ID_NO; IF ( GRADE1 == GRADE2 ) THEN CHECK_GRADE = "G00"; ELSE CHECK_GRADE = "G01"; }

IF...NOT FOUND 로직 처리 SELECT A.ID_NO, DECODE( B.ID_NO, NULL, 'E01', 'E00' ), DECODE( B.GRADE, NULL, NULL, A.GRADE, 'G00', 'G01' ) FROM CUSTOMER_TMP A, CUSTOMER B WHERE A.ID_NO = B.ID_NO(+) ; OUTER-JOIN으로 IF..NOT FOUND 로직을 처리할 수 있다.

조인 형태별 로직 튜닝 1 : M 조인 M : 1 : M 조인 배타적 관계 순환 관계 Super-type / Sub-type 열을 행으로 행을 열로

1 : M 조인 PRODUCT CUSTOMER 1995년 1/4분기 매출 데이터를 고객명 별로 매출액의 합계를 구하라. 상품 고객 SALE SALEDATE CUST_NO PROD_NO SALE_CLASS SALE_AMT PRODUCT PROD_NO PROD_NAME PROD_CLASS UNIT_PRICE CUSTOMER CUST_NO CUST_NAME CUST_PHONE BR_NO 1995년 1/4분기 매출 데이터를 고객명 별로 매출액의 합계를 구하라. SELECT A.CUST_NAME, SUM(B.SALE_AMT) FROM CUSTOMER A, SALE B WHERE B.SALEDATE BETWEEN ‘19950101’ AND ‘19950331’ AND A.CUST_NO = B.CUST_NO GROUP BY A.CUST_NAME;

1 : M 조인 SELECT A.CUST_NAME, V.SUM_SALE_AMT FROM CUSTOMER A, (SELECT CUST_NO, SUM(SALE_AMT) AS SUM_SALE_AMT FROM SALE WHERE SALEDATE BETWEEN ‘19950101’ AND ‘19950331’ GROUP BY CUST_NO) V WHERE A.CUST_NO = V.CUST_NO; GROUP BY를 먼저 수행한 후에 조인하면 조인 연결회수를 줄일 수 있다.

1 : M 조인 1995년 1/4분기 매출 중 단가가 10,000원 이상의 일자별 매출 건수를 구하라. SELECT B.SALEDATE, COUNT(*) FROM PRODUCT A, SALE B WHERE A.PROD_NO = B.PROD_NO AND B.SALEDATE BETWEEN ‘19950101’ AND ‘19950331’ AND A.UNIT_PRICE > 10,000 GROUP BY A.SALEDATE SELECT SALEDATE, COUNT(*) FROM SALE A WHERE SALEDATE BETWEEN ‘19980101’ AND ‘19980331’ AND EXISTS (SELECT ‘X’ FROM PROD B WHERE B.PROD_NO = A.PROD_NO AND B.UNIT_PRICE > 10,000) GROUP BY SALEDATE; 단지 체크 기능을 하기 위한 조인이라면 EXISTS 서브쿼리로 변환한다.

M : 1 : M 조인 연가 병가 총무부 직원들의 연가 및 병가의 사원별 일자 합을 구하라. 사원번호 [PK1] 시작일 [PK2] 일수 사원 사원번호 [PK1] 사원명 부서명 연봉 o o o 병가 사원번호 [PK1] 시작일 [PK2] 일수 사유 총무부 직원들의 연가 및 병가의 사원별 일자 합을 구하라. SELECT A.사원명, SUM(B.일수) AS 연가일, SUM(C.일수) AS 병가일 FROM 사원 A, 연가 B, 병가 C WHERE A.사원번호 = B.사원번호 AND A.사원번호 = C.사원번호 AND A.부서명 = ‘총무부’ GROUP BY A.사원명;

M : 1 : M 조인 SELECT A.사원명, V1.연가일, V2.병가일 FROM 사원 A, (SELECT 사원번호,SUM(일수) AS 연가일 FROM 연가 GROUP BY 사원번호) V1, (SELECT 사원번호,SUM(일수) AS 병가일 FROM 병가 GROUP BY 사원번호) V2 WHERE A.사원번호 = V1.사원번호 AND A.사원번호 = V2.사원번호 AND A.부서명 = ‘총무부’; SELECT V1.사원명, V1.연가일, SUM(C.일수) AS 병가일 FROM (SELECT A.사원번호, A.사원명, SUM(B.일수) AS 연가일 FROM 사원 A, 연가 B WHERE A.사원번호 = B.사원번호 AND A.부서명 = ‘총무부’ GROUP BY A.사원번호, A.사원명) V1, 병가 C WHERE V1.사원번호 = C.사원번호 GROUP BY V1.사원명, V1.연가일; M:1:M 조인에서는 답이 틀리지 않게 주의할 필요가 있다.

변경내역 및 버전 관리 고객 발송주소변경 영업점 고객번호 [PK1] 고객번호 [PK1] 영업점코드 [PK1] 고객명 영업점명 주민등록번호 발송주소 영업점코드 (FK) 변경사원번호 고객등급 변경구분 등록일자

변경내역 및 버전 관리 EXEC SQL DECLARE CUST_CUR CURSOR FOR SELECT B.영업점명, A.고객번호, A.고객명, A.주민등록번호, A.등급, A.등록일자 FROM 고객 A, 영업점 B WHERE B.영업점코드 = 'A15' AND A.영업점코드 = B.영업점코드; FOR ( ; ; ) { EXEC SQL FETCH CUST_CUR INTO :CUST_LIS ; EXEC SQL SELECT 발송주소 FROM 발송주소변경 A WHERE 고객번호 = :CUST_LIS.고객번호 AND 적용일자 = (SELECT MAX(적용일자) FROM 발송주소변경 B WHERE A.고객번호 = B.고객번호); IF (SQLCA.SQLCODE == 1403 ) /* 주소가 등록되지 않았음 */ CONTINUE; }

변경내역 및 버전 관리 ==> 잘못된 SQL outer-join되는 컬럼에 대해서는 SELECT /*+ INDEX(C 발송주소변경_PK) */ B.영업점명, A.고객번호, A.고객명, A.주민등록번호, A.등급, A.등록일자, C.발송주소, C.적용일자 FROM 고객 A, 영업점 B, 발송주소변경 C WHERE B.영업점코드 = 'A15' AND A.영업점코드 = B.영업점코드 AND A.고객번호 = C.고객번호(+) AND C.적용일자(+) = (SELECT /*+ INDEX_DESC(D 발송주소변경_PK) */ 적용일자 FROM 발송주소변경 D WHERE A.고객번호 = D.고객번호 AND ROWNUM = 1) ); ==> 잘못된 SQL outer-join되는 컬럼에 대해서는 IN,BETWEEN,LIKE,OR, 서브쿼리를 사용하여 조건을 구성할 수 없다.

변경내역 및 버전 관리 SELECT /*+ INDEX(C 발송주소변경_PK) */ B.영업점명, A.고객번호, A.고객명, A.주민등록번호, A.등급, A.등록일자, C.발송주소, C.적용일자 FROM 고객 A, 영업점 B, 발송주소변경 C WHERE B.영업점코드 = 'A15' AND A.영업점코드 = B.영업점코드 AND A.고객번호 = C.고객번호(+) AND (C.적용일자 IS NULL OR C.적용일자 = (SELECT /*+ INDEX_DESC(D 발송주소변경_PK) */ 적용일자 FROM 발송주소변경 D WHERE A.고객번호 = D.고객번호 AND ROWNUM = 1) );

배타적 관계 개인고객정보 계좌정보 ● 법인고객정보 ● 배타적 관계 (EXCLUSIVE) # * 고객번호 # * 계좌번호 성명 개설일 개설지점 비밀번호 실명확인여부 해지상태여부 상품종류 적용금리 * 고객번호 NOT NULL * 고객종류 NOT NULL # * 고객번호 성명 주소 주민등록번호 # * 계좌번호 개설일 개설지점 비밀번호 실명확인여부 해지상태여부 상품종류 적용금리 ● 법인고객정보 # * 고객번호 법인명 주소 사업자등록번호 재무상태 매출액 ● 배타적 관계 (EXCLUSIVE)

배타적 관계 CURSOR FOR SELECT 계좌번호, 개설일, 고객종류, 고객번호 FROM 계좌정보 WHERE 개설일 LIKE '199603%' ORDER BY 개설일; LOOP FETCH INTO :계좌번호, :개설일, :고객종류, :고객번호 IF (:고객종류 = ‘1’) SELECT 성명 INTO :고객명 FROM 개인고객정보 WHERE 고객번호 = :고객번호; ELSE SELECT 법인명 INTO :고객명 FROM 법인고객정보 WHERE 고객번호 = :고객번호;

배타적 관계 ==> 잘못된 SQL 배타적 관계에서는 OUTER-JOIN을 활용한다. SELECT A.계좌번호, A.개설일, B.개인고객명, C.법인고객명 FROM 계좌정보 A, 개인고객 B, 법인고객 C WHERE A.개설일 LIKE '199503%' AND A.고객번호 = B.고객번호 AND A.고객번호 = C.고객번호 ORDER BY A.개설일 ; ==> 잘못된 SQL SELECT A.계좌번호, A.개설일, B.개인고객명, C.법인고객명 FROM 계좌정보 A, 개인고객 B, 법인고객 C WHERE A.개설일 LIKE '199503%' AND A.고객번호 = B.고객번호(+) AND A.고객번호 = C.고객번호(+) ORDER BY A.개설일 ; 배타적 관계에서는 OUTER-JOIN을 활용한다.

순환 관계 계정정보 계정금액 # * 계정번호 (FK) # * 계정번호 # * 지점번호 (FK) 계정명 # * 회계일자 # * 계정번호 계정명 상위계정번호 (FK) 계정금액 # * 계정번호 (FK) # * 지점번호 (FK) # * 회계일자 지점정보 # * 지점번호 지점명 1000 (최상위계정) 1100 1200 ..... 1110 1120 ...... 1111 1112 ...... ......

순환 관계 계정정보 테이블 SELECT 계정번호 FROM 계정정보 START WITH 계정번호 = ‘1000’ ( INDEX 필요 ) 계정번호 계정명 상위계정번호 1000 계정과목-1000 NULL 1100 계정과목-1100 1000 1200 계정과목-1200 1000 1110 계정과목-1110 1100 1120 계정과목-1120 1100 1210 계정과목-1210 1200 1220 계정과목-1240 1200 1111 계정과목-1111 1110 1112 계정과목-1112 1110 1121 계정과목-1121 1120 1122 계정과목-1122 1120 1211 계정과목-1211 1210 1212 계정과목-1212 1210 ... ......... ... ① ② ④ ③ SELECT 계정번호 FROM 계정정보 START WITH 계정번호 = ‘1000’ CONNECT BY PRIOR 계정번호 = 상위계정번호 ④ ⑥ ⑤ ⑥ ⑤ ⑦ ⑦ ②,④,⑥ : CONNECT BY PRIOR 계정번호 = 상위계정번호 ③,⑤,⑦ : 상위계정번호 = 상수값 (INDEX 필요)

순환 관계 계정번호별로 한달 범위의 값을 미리 합계하여 처리 SELECT A.계정번호, SUM (B.계정금액) FROM 계정정보 A, ( SELECT 계정번호, SUM(계정금액) AS 계정금액 FROM 계정금액 WHERE 회계일자 LIKE '199610%' GROUP BY 계정번호 ) B WHERE B.계정번호 IN ( SELECT D.계정번호 FROM 계정정보 D START WITH D.계정번호 = A.계정번호 CONNECT BY PRIOR D.계정번호 = D.상위계정번호 ) GROUP BY A.계정번호 ;

Super-type/Sub-type 종목 정보 일자별 종목 거래내역 SELECT A.STK_CODE, B.ABBR MHA01M00 SELECT A.STK_CODE, B.ABBR FROM MHA01M00 B, MHB03T00 A WHERE A.DT = :B1 AND A.STK_CODE = B.STK_CODE AND B.STK_BD_TP = :B2 UNION SELECT C.STK_CODE, B.ABBR FROM MHA01M00 B, MHB04T00 C WHERE C.DT = :B3 AND C.STK_CODE = B.STK_CODE AND C.KEEP_GOOD_TP = :B4 AND B.STK_BD_TP = :B2; MHB03T00 # * DT # * STK_CODE # * STK_CODE * ABBR DESC STK_BD_TP MHB04T00 # * DT # * STK_CODE KEEP_GOOD_TP

Super-type/Sub-type SELECT B.STK_CODE, B.ABBR FROM MHA01M00 B WHERE EXISTS (SELECT 'X' FROM MHB03T00 A WHERE A.DT = :B1 AND A.STK_CODE = B.STK_CODE ) AND B.STK_BD_TP = :B2 <-- 처리 범위를 결정하기 위해 먼저 풀림 UNION FROM MHB04T00 C WHERE C.DT = :B3 AND C.KEEP_GOOD_TP = :B4 AND C.STK_CODE = B.STK_CODE ) AND B.STK_BD_TP = :B2 ;

Super-type/Sub-type SELECT B.STK_CODE, B.ABBR FROM MHA01M00 B WHERE ( EXISTS ( SELECT 'X' FROM MHB03T00 A WHERE A.DT = :B1 AND A.STK_CODE = B.STK_CODE ) OR EXISTS ( SELECT 'X' FROM MHB04T00 C WHERE C.DT = :B3 AND C.KEEP_GOOD_TP = :B4 AND C.STK_CODE = B.STK_CODE ) AND B.STK_BD_TP = :B2 ;