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

Presentation on theme: "오라클 SQL 개발 가이드 ® 조 성 복 기술본부 DB기술자문팀 한국 오라클."— Presentation transcript:

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

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

3 최대값, 최소값 구하기 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건을 읽어서 최대값과 최소값을 구할 수 있다.

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

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

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

7 일련번호 채번하기 연속성과 유일성을 동시에 보장해야 하는 경우
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; 인덱스를 이용하는 경우 유일성과 연속성을 동시에 보장할 수 있다.

8 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... 로직을 처리할 수 있다.

9 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%’;

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

11 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"; }

12 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 로직을 처리할 수 있다.

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

14 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 ‘ ’ AND ‘ ’ AND A.CUST_NO = B.CUST_NO GROUP BY A.CUST_NAME;

15 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 ‘ ’ AND ‘ ’ GROUP BY CUST_NO) V WHERE A.CUST_NO = V.CUST_NO; GROUP BY를 먼저 수행한 후에 조인하면 조인 연결회수를 줄일 수 있다.

16 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 ‘ ’ AND ‘ ’ AND A.UNIT_PRICE > 10,000 GROUP BY A.SALEDATE SELECT SALEDATE, COUNT(*) FROM SALE A WHERE SALEDATE BETWEEN ‘ ’ AND ‘ ’ AND EXISTS (SELECT ‘X’ FROM PROD B WHERE B.PROD_NO = A.PROD_NO AND B.UNIT_PRICE > 10,000) GROUP BY SALEDATE; 단지 체크 기능을 하기 위한 조인이라면 EXISTS 서브쿼리로 변환한다.

17 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.사원명;

18 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 조인에서는 답이 틀리지 않게 주의할 필요가 있다.

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

20 변경내역 및 버전 관리 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; }

21 변경내역 및 버전 관리 ==> 잘못된 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, 서브쿼리를 사용하여 조건을 구성할 수 없다.

22 변경내역 및 버전 관리 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) );

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

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

25 배타적 관계 ==> 잘못된 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을 활용한다.

26 순환 관계 계정정보 계정금액 # * 계정번호 (FK) # * 계정번호 # * 지점번호 (FK) 계정명 # * 회계일자
# * 계정번호 계정명 상위계정번호 (FK) 계정금액 # * 계정번호 (FK) # * 지점번호 (FK) # * 회계일자 지점정보 # * 지점번호 지점명 1000 (최상위계정) ......

27 순환 관계 계정정보 테이블 SELECT 계정번호 FROM 계정정보 START WITH 계정번호 = ‘1000’
( INDEX 필요 ) 계정번호 계정명 상위계정번호 계정과목 NULL 계정과목 계정과목 계정과목 계정과목 계정과목 계정과목 계정과목 계정과목 계정과목 계정과목 계정과목 계정과목 ① ② ④ ③ SELECT 계정번호 FROM 계정정보 START WITH 계정번호 = ‘1000’ CONNECT BY PRIOR 계정번호 = 상위계정번호 ④ ⑥ ⑤ ⑥ ⑤ ⑦ ⑦ ②,④,⑥ : CONNECT BY PRIOR 계정번호 = 상위계정번호 ③,⑤,⑦ : 상위계정번호 = 상수값 (INDEX 필요)

28 순환 관계 계정번호별로 한달 범위의 값을 미리 합계하여 처리 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.계정번호 ;

29 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

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

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