뷰의 정의


물리적인 테이블을 근거한 논리적인 가상 테이블


뷰를 왜 쓰는가? 


특정 중요 데이터를 뽑으려고 할때 기존에 있는 테이블을 복사 하는게 아니라 가상테이블 뷰 VIEW를 이용하면 됨


테이블은 데이터가 있지만 뷰는 데이터가 없음(SQL만 저장)


사용자가 해당 VIEW에 접근하면, 그때 VIEW에 들어 있던 SQL이 수행되어 결과를 가져오는 것.


CREATE OR REPLACE VIEW 같은 이름의 VIEW가 있을 경우 삭제 후 다시 생성
FORCE 기본 테이블의 존재 여부에 상관없이 VIEW 생성
WITH CHECK OPTION 주어진 제약 조건에 맞는 데이터만 입력 및 수정
FOREIGN KEY SELECT만 가능한 읽기 전용 뷰를 생성


뷰의 장점


복잡하고 긴 쿼리문을 뷰로 정의하면 접근 단순화 가능


뷰는 물리적으로 데이터를 저장하지 않고 CREATE VIEW명령어로 뷰를 정의할때 AS절 다음에 기술한 쿼리 문장 자체를 저장함


단순 뷰 , 복합 뷰


 단순 뷰 

 복합 뷰 

 하나의 테이블로 생성

 여러개의 테이블로 생성

 그룹함수 사용이 불가능

 그룹 함수의 사용이 가능

 DISTINCT 사용 불가능

 DISTINCT 사용이 가능

 DML(INSERT,UPDATE,DELETE) 사용이 가능

 DML사용이 불가능



단순 뷰에서 DML명령어 사용이 불가능한 경우 : 뷰 정의에 포함되지 않은 컬럼 중에 기본 테이블의 칼럼이 NOT NULL 제약조건이 지정되어 있는 경우
 INSERT문 사용 불가능

-> 뷰에 대한 INSERT문은 기본 테이블에 NULL값을 입력하는 형태가 되기 때문

단순 뷰에서 DISTINCT를 포함한 경우에도 DML 명령 사용이 불가능, 그룹함수나 GROUP BY 절을 포함한 경우 DML명령 사용이 불가능


단순 뷰

예 1) 뷰를 생성할 수 있는 권한을 부여한다.


SQL> CONN system/[사용자 암호]

SQL> GRANT CREATE VIEW TO scott;

SQL> CONN scott/[사용자 암호] 

SQL> CREATE VIEW EMP_VIEW30 AS SELECT EMPNO, ENAME, DEPTNO FROM EMP_COPY WHERE DEPTNO=30; 


예 2) 뷰의 생성을 확인한다.


SQL> SELECT * FROM EMP_VIEW30; 


예 3) USER_VIEWS 데이터 딕셔너리를 확인한다.


SQL> COLUMN VIEW_NAME FORMAT A15

SQL> COLUMN TEXT FORMAT A50

SQL> SELECT VIEW_NAME, TEXT FROM USER_VIEWS; 


예 4) EMP_VIEW30 뷰에 데이터를 추가


SQL> INSERT INTO EMP_VIEW30 VALUES(8000, 'ANGEL', 30);

SQL> SELECT * FROM EMP_VIEW30; 


예 5) EMP_VIEW30 뷰의 기본 테이블인 EMP_COPY 테이블을 확인


SQL> SELECT * FROM EMP_COPY; 


복합 뷰


예 1) 사원 테이블과 부서 테이블을 조인하여 복합 뷰를 생성한다

CREATE VIEW EMP_VIEW_DEPT AS SELECT E.EMPNO, E.ENAME, E.SAL, E.DEPTNO, D.DNAME, D.LOC FROM EMP E, DEPT D WHERE E.DEPTNO=D.DEPTNO ORDER BY EMPNO DESC; 


예 2) 뷰를 생성한 후, 아래의 명령문을 활용하면, 복잡한 질의를 쉽게 처리 할 수 있다.

SELECT * FROM EMP_VIEW_DEPT; 



'전체 > Oracle' 카테고리의 다른 글

ORACLE 산술, 비교, 논리, 기타 연산자 정리 AND,OR,NOT,BETWEEN,IN,LIKE  (0) 2017.06.12
ORACLE INDEX  (0) 2017.06.12
ORACLE PIVOT 사용 달력 만들기, 분석함수 사용 달력 만들기  (0) 2017.06.11
ORACLE PIVOT과 UNPIVOT 정리  (2) 2017.06.09
ORACLE 정규표현식 SUBSTR, REPLACE, INSTR, DECODE, TRIM 사용하기  (2) 2017.06.05


1. oracle pivot 사용 행을 열로 바꿔 달력 만들기



1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 1. PIVOT 사용 행을 열로 바꾸기
SELECT SUN, MON, TUE, WED, THU, FRI, SAT
  FROM (
        SELECT TRUNC(DT + LEVEL-1, 'D') AS WEEK -- 현재 날짜 주의 시작 요일로 잘라버림
             , TO_CHAR(DT + LEVEL-1, 'D') AS DW -- 17-06-01부터 DAY값 일-토 1-7 값 매김
             , LPAD(LEVEL, 2, '0') AS DD -- LEVEL의 1-9까지 0 채워줌
          FROM (
--                SELECT TRUNC(SYSDATE,'MM') AS DT -- 현재 달의 첫번째 날 구함
                SELECT TO_DATE('20170601','YYYYMMDD') AS DT
                  FROM DUAL
               )
       CONNECT BY LEVEL <= TO_CHAR(LAST_DAY(SYSDATE),'DD') -- 현재 날짜의 마지막 날인 YYYY-MM-DD를 구해 DD만 뽑음 30
       )
 PIVOT (MAX(DD) FOR DW IN ('1' AS SUN, '2' AS MON, '3' AS TUE, '4' AS WED, '5' AS THU, '6' AS FRI, '7' AS SAT)) -- 그룹으로 관리할 컬럼 지정 그룹함수 사용가능 MAX(DD), 기준이 되는 컬럼 DW, 기준이 되는 컬럼 구성하기 위한 값 정의 IN()
-- PIVOT (MAX(DD) FOR DW IN (1 SUN, 2 MON, 3 TUE, 4 WED, 5 THU, 6 FRI, 7 SAT)) 
 ORDER BY WEEK;
cs


출력 결과





2. oracle 분석 함수 사용(MAX, MIN...) 행을 열로 바꿔 달력 만들기



1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- 2. 분석함수 사용 행을 열로 바꾸기
SELECT MAX(DECODE(DW,'1', DD)) 일 
     , MAX(DECODE(DW,'2', DD)) 월
     , MAX(DECODE(DW,'3', DD)) 화
     , MAX(DECODE(DW,'4', DD)) 수
     , MAX(DECODE(DW,'5', DD)) 목
     , MAX(DECODE(DW,'6', DD)) 금
     , MAX(DECODE(DW,'7', DD)) 토
  FROM (
        SELECT TRUNC(DT + LEVEL-1, 'D') AS WEEK 
             , TO_CHAR(DT + LEVEL-1, 'D') AS DW 
             , LPAD(LEVEL, 2, '0') AS DD 
          FROM (
                SELECT TRUNC(SYSDATE,'MM') AS DT 
                  FROM DUAL
               )
       CONNECT BY LEVEL <= TO_CHAR(LAST_DAY(SYSDATE),'DD') 
       )
 GROUP BY WEEK -- 주 단위로 그룹 지음
 ORDER BY WEEK;
cs

출력 결과



다음과 같이 pivot(oracle 11g 사용가능), 분석함수를 이용해 행을 열로 바꿔 달력을 표현할 수 있다.




'전체 > Oracle' 카테고리의 다른 글

ORACLE INDEX  (0) 2017.06.12
ORACLE VIEW  (0) 2017.06.12
ORACLE PIVOT과 UNPIVOT 정리  (2) 2017.06.09
ORACLE 정규표현식 SUBSTR, REPLACE, INSTR, DECODE, TRIM 사용하기  (2) 2017.06.05
ORACLE 계층형 쿼리 만들기  (0) 2017.06.05


PIVOT


열을 행으로 ROW 형태의 데이터를 COLUMN 형태로 보여주는 쿼리이다.

컬럼의 값을 기준으로 새로운 컬럼을 도출하여 다양한 집계함수를 적용 가능하다.






UNPIVOT


행을 열으로 COLUMN 형태를 ROW 형태로 보여주는 쿼리이다.

Pivot 된 결과를 되돌리는 기능이 아닌 순수하게 Pivot 에 대응 되는 개념이다.

Pivot 된 결과를 Unpivot 을 쓴다고 해도 원래의 Pivot 전 테이블로 돌아갈 수 없다.

컬럼을 특정 컬럼의 값으로 대응하여 해당 컬럼의 값은 별도의 컬럼에 표시하여 나열한다.

기능적으로 Pivot 과 반대되는 개념이다.






1
2
3
4
5
6
7
8
9
10
11
12
-- PIVOT 사용 전
WITH T AS
(
 SELECT CHR(LEVEL + 64) C
      , LEVEL V
   FROM DUAL
connect by LEVEL <= 10
)
 
SELECT C
     , TO_CHAR(V) V
  FROM T;
cs


출력 결과





1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- PIVOT 사용 후
WITH T AS
(
 SELECT CHR(LEVEL + 64) C
      , LEVEL V
   FROM DUAL
CONNECT BY LEVEL <= 10
)
SELECT *
  FROM (
        SELECT C
             , V
          FROM T
       )
 PIVOT ( MIN(C) FOR V IN(1 V1, 2 V2, 3 V3, 4 V4, 5 V5, 6 V6, 7 V7, 8 V8, 9 V9, 10 V10))
cs

출력 결과




1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- UNPIVOT 사용 후
WITH T AS
(
 SELECT CHR(LEVEL + 64) C
      , LEVEL V
   FROM DUAL
connect by LEVEL <= 10
)
 SELECT *
   FROM (
         SELECT C
              , TO_CHAR(V) V
           FROM T    
        )
UNPIVOT ( V FOR GB2 IN (C AS 1, V AS 2) )
cs

출력 결과



1
2
3
4
5
6
7
8
9
10
11
12
-- PIVOT
WITH T AS(
    SELECT '1월' 월, '홍길동' 성명, 110 급여 FROM DUAL UNION ALL
    SELECT '1월' 월, '홍길동' 성명, 30 급여 FROM DUAL UNION ALL
    SELECT '1월' 월, '이상현' 성명, 210 급여 FROM DUAL UNION ALL
    SELECT '1월' 월, '김성현' 성명, 200 급여 FROM DUAL UNION ALL
    SELECT '2월' 월, '김성현' 성명, 120 급여 FROM DUAL UNION ALL
    SELECT '2월' 월, '이상현' 성명, 220 급여 FROM DUAL
)
SELECT * 
  FROM T
 PIVOT ( SUM(급여) FOR 월 IN ('1월','2월'))
cs


출력 결과




1
2
3
4
5
6
7
-- UNPIVOT
WITH T AS( 
    SELECT '홍길동' 성명, 140 일월, 120 이월 FROM DUAL UNION ALL
    SELECT '김철수' 성명, 210 일월, 220 이월 FROM DUAL
)
SELECT * FROM T
UNPIVOT(급여 FOR 월 IN(일월, 이월))
cs


출력 결과



'전체 > Oracle' 카테고리의 다른 글

ORACLE VIEW  (0) 2017.06.12
ORACLE PIVOT 사용 달력 만들기, 분석함수 사용 달력 만들기  (0) 2017.06.11
ORACLE 정규표현식 SUBSTR, REPLACE, INSTR, DECODE, TRIM 사용하기  (2) 2017.06.05
ORACLE 계층형 쿼리 만들기  (0) 2017.06.05
정규표현식  (0) 2017.05.31


ORACLE 정규표현식 SUBSTR, REPLACE, INSTR, DECODE, TRIM 사용하기




1. 정규식을 사용해 전화번호 값을 자리수 마다 컬럼 나누기



1
2
3
4
5
6
7
-- 전화번호 컬럼으로 나누기
SELECT SUBSTR(PHONE,1,INSTR(PHONE,'-',1,1)-1) AS 첫번호 -- 1번째 자리부터 3개 숫자만 출력
     , SUBSTR(PHONE,INSTR(PHONE,'-',1,1)+1,((INSTR(PHONE,'-',1,2)-INSTR(PHONE,'-',1,1))-1)) AS 중간번호 -- 5번째 자리부터 9-4-1 = 4개 숫자만 출력
     , SUBSTR(PHONE, INSTR(PHONE,'-',1,2)+1, 4) AS 끝번호 -- 10번째 자리부터 4개 숫자만 출력
  FROM (
        SELECT '010-1234-5678' PHONE  FROM DUAL
       )
cs


INSTR은 찾을 문자열의 위치 값을 숫자로 반환함.

INSTR('기준이 되는 문자열','찾을 문자열', 기준이 되는 문자열에서 비교를 시작할 위치, 검색된 결과의 순번)


PHONE 문자열에 '-' 가 들어가있는 첫번째 자릿수의 값을 나타내는것.

EX)

SELECT '010-1234-5678' PHONE  FROM DUAL

INSTR(PHONE,'-',1,1) 의 출력 값은 4
INSTR(PHONE,'-',1,2) 의 출력 값은 9


SUBSTR은 찾을 문자열을 문자로 반환함.

SUBSTR('기준이 되는 문자열','시작할 자리수','몇번째까지 찾을 숫자')


SELECT '010-1234-5678' PHONE  FROM DUAL


SUBSTR(PHONE,1,3)

PHONE의 1(첫번째 문자부터) ~ 3(세번째 문자까지) 문자를 출력


010


SUBSTR(PHONE,5,4)

PHONE의 5(다섯번째 문자부터) ~ 4(네번째 문자까지) 문자를 출력

1234


SUBSTR(PHONE,10,4)

PHONE의 10(열번째 문자부터) ~ 4(네번째 문자까지) 문자를 출력

5678


2. 정규식을 사용해 주소를 시도구시읍면동리 주소 컬럼 나누기



1
2
3
4
5
6
7
SELECT REGEXP_SUBSTR(ADDR, '[^시도]+', 1, 1)||DECODE(REGEXP_INSTR(ADDR,'[도]'),'0','시','도') ADDR1 -- 서울 경기 || 도 인지 시 인지 구분
     , TRIM(REGEXP_SUBSTR(ADDR,' ([가-힝]*[구])|( ([가-힝]*[시]))',1,1)) ADDR2 -- 구인지 시인지 띄어쓰기 다음 문자열보고 판단 후 TRIM으로 공백 제거
     , TRIM(REGEXP_SUBSTR(ADDR,' ([가-힝]*[동리])',1,1)) ADDR3 -- 동인지 리인지 띄어쓰기 다음 문자열보고 판단 후 TRIM으로 공백 제거
     , TRIM(REGEXP_SUBSTR(ADDR, '([^동리]+)',1,2)) ADDR4
  FROM (
        SELECT REGEXP_REPLACE('서울시강남구역삼동698-8','([시도구읍면동리])','\1 ') ADDR  FROM DUAL  -- 띄어쓰기 된 상태로 서울시 강남구 역삼동 698-8 출력
       )
cs

DECODE는 컬럼명의 조건을 줘 참인지 거짓인지 판단해 출력함.

DECODE(컬럼명,'조건',참,거짓)

DECODE(SERIAL_NM,'10','삼성','LG')

DECODE(컬럼명,'조건1','값1','조건2','값2','조건3','값3')

DECODE(SERIAL_NM,'10','삼성','20','LG','30','APPLE')

DECODE 사용 ADDR의 도 값이 0과 같다면 시, ADDR의 도 값이 0과 다르다면 도
한마디로 경기. 강원, 제주, 경상, 전라 .. 도가 들어오면 도에 들어간 INSTR 값은 3이다. 3이면 도
김포, 수원, 안양, 의정부, 화성 시 일경우 도라는 값이 없어 0과 같으므로 시로 출력

TRIM은 공백제거로 사용함


3. 정규식을 사용해 숫자를 문자로  변환, 문자를 숫자로 변환



1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
SELECT TRANSLATE(
        REGEXP_REPLACE (
          REGEXP_REPLACE (
             REGEXP_REPLACE (LPAD (REGEXP_REPLACE(TXT,'(,)',''), 16, 0),'([0-9])([0-9])([0-9])([0-9])','\1천\2백\3십\4 ')
                           ,'(.*) (.*) (.*) (.*) ','\1조\2억\3만\4')
                        ,'0천0백0십0.|0[천백십]','')
                       ,'1234567890','일이삼사오육칠팔구') AS Num2String
  FROM (
        SELECT '100000000012000' AS TXT FROM DUAL 
       )
       
             
     SELECT TO_NUMBER (
                 DECODE (INSTR (JO, '조'),'', '0000',
                           DECODE (INSTR (JO, '천'),'0', '0',REGEXP_REPLACE (REGEXP_SUBSTR (JO,'(.[천])',1,1),'[가-힝]',''))
                        || DECODE (INSTR (JO, '백'),'0', '0',REGEXP_REPLACE (REGEXP_SUBSTR (JO,'(.[백])',1,1),'[가-힝]',''))
                        || DECODE (INSTR (JO, '십'),'0', '0',REGEXP_REPLACE (REGEXP_SUBSTR (JO,'(.[십])',1,1),'[가-힝]',''))
                        || DECODE (REGEXP_REPLACE (JO, '(.[천백십])', ''), '조', '0',REGEXP_REPLACE (REGEXP_REPLACE (JO, '(.[천백십])', ''),'[조]',''))
                        )
              || DECODE (INSTR (UK, '억'),'', '0000',
                           DECODE (INSTR (UK, '천'),'0', '0',REGEXP_REPLACE (REGEXP_SUBSTR (UK,'(.[천])',1,1),'[가-힝]',''))
                        || DECODE (INSTR (UK, '백'),'0', '0',REGEXP_REPLACE (REGEXP_SUBSTR (UK,'(.[백])',1,1),'[가-힝]',''))
                        || DECODE (INSTR (UK, '십'),'0', '0',REGEXP_REPLACE (REGEXP_SUBSTR (UK,'(.[십])',1,1),'[가-힝]',''))
                        || DECODE (REGEXP_REPLACE (UK, '(.[천백십])', ''),'억', '0',REGEXP_REPLACE (REGEXP_REPLACE (UK, '(.[천백십])', ''),'[억]',''))
                        )
              || DECODE (INSTR (MAN, '만'),'', '0000',
                           DECODE (INSTR (MAN, '천'),'0', '0',REGEXP_REPLACE (REGEXP_SUBSTR (MAN,'(.[천])',1,1),'[가-힝]',''))
                        || DECODE (INSTR (MAN, '백'),'0', '0',REGEXP_REPLACE (REGEXP_SUBSTR (MAN,'(.[백])',1,1),'[가-힝]',''))
                        || DECODE (INSTR (MAN, '십'),'0', '0',REGEXP_REPLACE (REGEXP_SUBSTR (MAN,'(.[십])',1,1),'[가-힝]',''))
                        || DECODE (REGEXP_REPLACE (MAN, '(.[천백십])', ''),'만', '0',REGEXP_REPLACE (REGEXP_REPLACE (MAN, '(.[천백십])', ''),'[만]',''))
                        )
              || DECODE (INSTR (ONE, '천'),'0', '0',REGEXP_REPLACE (REGEXP_SUBSTR (ONE,'(.[천])',1,1),'[가-힝]',''))
              || DECODE (INSTR (ONE, '백'),'0', '0',REGEXP_REPLACE (REGEXP_SUBSTR (ONE,'(.[백])',1,1),'[가-힝]',''))
              || DECODE (INSTR (ONE, '십'),'0', '0',REGEXP_REPLACE (REGEXP_SUBSTR (ONE,'(.[십])',1,1),'[가-힝]',''))
              || DECODE (REGEXP_REPLACE (ONE, '(.[천백십])', ''),'', '0',REGEXP_REPLACE (ONE, '(.[천백십])', ''))
              ) AS NUM
       FROM (      
               SELECT REGEXP_SUBSTR(TEXT,'(.*[조])', 1, 1) AS JO
                   , TRIM(REGEXP_REPLACE(REGEXP_SUBSTR(TEXT,'(.*[억])', 1, 1),'(.*[조])','')) AS UK
                   , TRIM(REGEXP_REPLACE(REGEXP_SUBSTR(TEXT,'(.*[만])', 1, 1),'(.*[조])|(.*[억])','')) AS MAN
                   , TRIM(REGEXP_REPLACE(REGEXP_REPLACE(TEXT,' (.*)','\1'),'(.*[조])|(.*[억])|(.*[만])','')) AS ONE
                FROM (
                      SELECT REGEXP_REPLACE(TRANSLATE('일백조일만이천','일이삼사오육칠팔구','123456789'),'([조억만])','\1 ') AS TEXT FROM DUAL
                     )
             )
cs


위와 같은 방식으로 SUBSTR, REPLACE, INSTR, DECODE, TRIM 을 적절히 활용하여 숫자를 문자로 변환, 문자를 숫자로 변환하는 쿼리를 작성할 수 있다.



'전체 > Oracle' 카테고리의 다른 글

ORACLE PIVOT 사용 달력 만들기, 분석함수 사용 달력 만들기  (0) 2017.06.11
ORACLE PIVOT과 UNPIVOT 정리  (2) 2017.06.09
ORACLE 계층형 쿼리 만들기  (0) 2017.06.05
정규표현식  (0) 2017.05.31
oracle 행을 열로 바꾸기, 가장 큰값, 평균 큰값 출력하기  (0) 2017.05.23

ORACLE 계층형 쿼리 만들기 - 1 




1
2
3
4
5
6
7
8
-- START WITH 부모로 지정될 ROOT 행 지정
-- CONNECT BY PRIOR 자식 컬럼 = 부모컬럼 부모에서 자식으로 트리구성 TOP-DOWN 구조
-- 왼쪽에 LEVEL만큼 특수문자 ┖를 추가하여 계층구조로 조회하는 예제
 
SELECT LEVEL, LPAD('┖', 2*(LEVEL-1)) || ENAME ENAME, EMPNO, MGR, JOB 
  FROM EMP
 START WITH JOB='PRESIDENT'
CONNECT BY PRIOR EMPNO=MGR; 
cs


출력결과



ORACLE 계층형 쿼리 만들기 - 2



1
2
3
4
5
SELECT LEVEL AS DEPTH, LPAD('┖', 2*(LEVEL-1)) || CODE_CD AS CODE_CD, UPCODE_CD, LPAD('┖', 2*(LEVEL-1)) || CODE_NM AS CODE_NM
  FROM 
  SYS_CODEINFO
 START WITH CODE_CD='00000'
CONNECT BY PRIOR CODE_CD=UPCODE_CD; 
cs

출력결과



LEVEL, START WITH, CONNECT BY PRIOR 문장을 통해


계층형 쿼리를 작성할 수 있고 현재 코드값과 상위코드값, 코드네임을 계층적으로 출력할 수 있다.



'전체 > Oracle' 카테고리의 다른 글

ORACLE PIVOT과 UNPIVOT 정리  (2) 2017.06.09
ORACLE 정규표현식 SUBSTR, REPLACE, INSTR, DECODE, TRIM 사용하기  (2) 2017.06.05
정규표현식  (0) 2017.05.31
oracle 행을 열로 바꾸기, 가장 큰값, 평균 큰값 출력하기  (0) 2017.05.23
DUAL 테이블에서 00~23 시 까지 입력된 컬럼에 현재 시간 찍기  (0) 2017.05.18

1. 정규표현식이란


문자열 데이터의 간단한 패턴 및 복잡한 패턴을 검색 할 수 있는 방법으로 기존의 LIKE 연산의 한계를 뛰어넘는 막강한 검색 도구이다. 다양한 Meta Character 를 이용하여 복잡한 프로그래밍을 간단하게 해결 할 수 있으며 Data Validation, ETL(Extract, Transform, Load), Data Cleansing, Data Mining 등의 작업에서 유용하게 사용 될 수 있다. 제약 조건으로 테이블의 Data 유효성을 검증 할때도 사용 가능하다.



2. 정규표현식 방법


 REGEXP_LIKE

 Like 연산과  유사하며 정규식 패턴을 검색

 REGEXP_REPLACE

 정규식 패턴을 검색하여 대체 문자열로 변경

 REGEXP_INSTR

 정규식 패턴을 검색하여 위치 반환

 REGEXP_SUBSTR

 정규식 패턴을 검색하여 부분 문자 추출 

 REGEXP_COUNT

 정규식 패턴을 검색하여 발견된 횟수 반환 



3. Meta Character


.

 지원되는 Character set에서 NULL 을 제외한 임의의 문자와 일치

 +

 한 번 이상 발생 수 일치 

 ?

 0 또는 1번 발생 수 일치 

 *

 선행 하위식의 0번 이상 발생 수 일치 

 {m}

 선행 표현식의 정확히 m번 이상 발생 수 일치 

 {m , }

 선행 하위식과 최소 m번 이상 발생 수 일치 

 {m, n}

 선행 하위식의 최소 m번 이상, 최대 n번 이하 발생 수 일치 

 [ ... ]

 괄호 안의 리스트에 있는 임의의 단일 문자와 일치 

 |

 여러 대안 중 하나와 일치 (OR) 

 ( . . . )

 괄호로 묶인 표현식을 한 단위로 취급함. 하위식은 리터럴의 문자열이나 연산자를 포함한 복잡한 표현식 가능 

 ^

 문자열 시작 부분과 일치 

 $

 문자열 끝 부분과 일치 

 \

 표현식에서 후속 메타 문자를 리터럴로 처리 (ESCAPE) 

 \n

 괄호 안의 그룹화된 n번쨰(1~9) 선행 하위식과 일치. 괄호는 표현식이 기억되도록 만들고 backreference에서 표현식 참조 

 /d

 숫자 문자 

 [ :class: ]

 지정된 POSIX 문자 클래스에 속한 임의의 문자와 일치

 [:alpha:] 알파벳 문자 [:digit:] 숫자 [:lower:] 소문자 알파벳 문자 [:upper:] 대문자 알파벳 문자 [:alnum:] 알파벳/숫자

 [:space:] 공백 문자 [:punct:] 구두점 기호 [:cntrl:] 컨트롤 문자 [:print:] 출력 가능한 문자 

 [^:class:]

 괄호 안의 리스트에 없는 임의의 단일 문자와 일치 


Meta Character를 이용하여 정규식 사용이 가능하다.



4. 정규표현식 활용 예제



REGEXP_REPLACE

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions130.htm



1
2
3
4
5
SELECT
  REGEXP_REPLACE(phone_number,
                 '([[:digit:]]{3})\.([[:digit:]]{3})\.([[:digit:]]{4})',
                 '(\1) \2-\3') "REGEXP_REPLACE"
  FROM employees;
cs



1
2
3
4
5
6
7
REGEXP_REPLACE
--------------------------------------------------------------------------------
(515) 123-4567
(515) 123-4568
(515) 123-4569
(590) 423-4567
. . .
cs




REGEXP_INSTR

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions129.htm#SQLRF06300



1
2
3
4
5
SELECT
SELECT
  REGEXP_INSTR('500 Oracle Parkway, Redwood Shores, CA',
               '[^ ]+', 1, 6) "REGEXP_INSTR"
  FROM DUAL;
cs


1
2
3
REGEXP_INSTR
------------
          37
cs



1
2
3
4
SELECT
  REGEXP_INSTR('500 Oracle Parkway, Redwood Shores, CA',
               '[s|r|p][[:alpha:]]{6}', 3, 2, 1, 'i') "REGEXP_INSTR"
  FROM DUAL;
cs


1
2
3
REGEXP_INSTR
------------
          28
cs




REGEXP_SUBSTR

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions131.htm



1
2
3
4
SELECT
  REGEXP_SUBSTR('500 Oracle Parkway, Redwood Shores, CA',
                ',[^,]+,') "REGEXPR_SUBSTR"
  FROM DUAL;
cs


1
2
3
REGEXPR_SUBSTR
-----------------
, Redwood Shores,
cs



1
2
3
4
SELECT
  REGEXP_SUBSTR('http://www.oracle.com/products',
                'http://([[:alnum:]]+\.?){3,4}/?') "REGEXP_SUBSTR"
  FROM DUAL;
cs


1
2
3
REGEXP_SUBSTR
----------------------
http://www.oracle.com/
cs





'전체 > Oracle' 카테고리의 다른 글

ORACLE PIVOT과 UNPIVOT 정리  (2) 2017.06.09
ORACLE 정규표현식 SUBSTR, REPLACE, INSTR, DECODE, TRIM 사용하기  (2) 2017.06.05
ORACLE 계층형 쿼리 만들기  (0) 2017.06.05
oracle 행을 열로 바꾸기, 가장 큰값, 평균 큰값 출력하기  (0) 2017.05.23
DUAL 테이블에서 00~23 시 까지 입력된 컬럼에 현재 시간 찍기  (0) 2017.05.18

1. JSTL 이란


1. JSP 표준라이브러리(JSP Standard Tag Library) 이다.


2. JSP에서 사주 사용하는 기능(반복과 조건, 데이타 관리 포맷, XML 조작, 데이타베이스 액세스)을 구현하는 커스텀 태그 라이브러리 모음이다.


3. 시간, 날짜, 숫자의 포맷이나 문자열 가공등의 처리에서 비즈니스로직과 프리젠테이션 로직을 분리할 수 있게 해준다.


4. JSTL은 EL(Expression Language)를 사용하여 표현한다.



2. JSTL 라이브러리


 라이브러리

기능 

 접두어

 코어

 일반 프로그램 언어에서 제공하는 변수선언, 조건/제어/반복문등의 기능을 제공한다.

 c

 포맷팅 

 숫자,날짜,시간을 포맷팅 하는 기능과 국제화, 다국어 지원 기능을 제공한다.

 fmt

 함수

 문자열을 처리하는 함수를 제공한다. 

 fn

 데이터베이스

 데이터베이스의 데이터를 입력/수정/삭제/조회하는 기능을 제공한다.

 sql

 XML처리

 XML 문서를 처리할 때 필요한 기능을 제공한다.

 x




3. JSTL 문법


1. Core


 명령어

내용 

 예제 

c:url

url 호출 

  
1
2
<c:url value="test.jsp"/>
<c:url value="/test.jsp" context="/other"/>
cs

c:out

객체를 화면에 출력 

  
1
2
<c:out value="${data}"/>
<c:out value="${data}" default="값없음"/>
cs

c:set 

저장영역에 객체를 저장 

  
1
2
<!-- scope : page, request, session, application -->
<c:set scope="request" var="data" value="my_testValue" />
cs

c:forEach 

반복문 제어 

  
1
2
3
4
5
6
7
8
9
10
11
12
13
<c:forEach var="NOTICE" items="${NOTICE_LIS}" varStatus="status">
<c:out value="${NOTICE.COLUMN}"/>
</c:forEach>
 
<!-- 0123456 -->
<c:forEach var="S" begin="0" end="6">
<c:out value="${S}"/>
</c:forEach>
 
<!-- 036 -->
<c:forEach var="S" begin="0" end="6" step="3">
<c:out value="${S}"/>
</c:forEach>
cs

c:remove 

저장영역에서 객체를 삭제 

  
1
<c:remove scope="request" var="data" />
cs

c:if 

조건문 제어 

  
1
2
3
<c:if test="${empty data}">
data 값이 비어있으면 실행
</c:if>
cs

c:choose

c:when

c:otherwise 

복합조건문 제어 

  
1
2
3
4
5
6
<c:choose>
<c:when test="${data == 'A'}">data 값이 A이면 실행</c:when>
<c:when test="${data == 'B'}">data 값이 B이면 실행</c:when>
<c:when test="${data == 'C'}">data 값이 C이면 실행</c:when>
<c:otherwise>data 값이 A, B, C값이 아닐 경우 실행</c:otherwise>
</c:choose>
cs

c:import! 

다른 jsp화면을 현재 화면에 출력 

  
1
<c:import! url="test.jsp"/>
cs

c:redirect 

경로 이동 

  
1
<c:redirect url="주소"/>
cs



2. 연산자


연산자

예제 

결과내용 

==

eq 

${1 == 1}

${1 eq 1}

true 

!=

ne 

${1 != 1}

${1 ne 1} 

false 

<

lt

${1 < 10}

${1 lt 10} 

true 

>

gt

${1 > 10}

${1 gt 10} 

false 

<=

le

${1 <= 1}

${1 le 1} 

true 

>=

ge

${1>=0}

${1 ge 0} 

false 

&&

and

${true && false}

${true and false}

false

||

or 

${true || false}

${true or false} 

true 

!

not

${!true}

${not true} 

false 

empty

${empty name} 

name이 null이거나 빈 문자열이면 true 

not empty 

${not empty name} 

name이 null도 아니고 빈 문자열도 아니면 true 



4. status 상태 속성 제어



1
2
3
4
5
6
7
8
9
10
11
12
13
< c:foreach items=”${RESULT}” var=”RESULT” varStatus=”status”>
 
    ${status.current}<br/> <!– 현재 아이템 –>
    ${status.index}<br/>        <!– 0부터의 순서 –>
    ${status.count}<br/>        <!– 1부터의 순서 –>
    ${status.first}<br/>           <!– 현재 루프가 처음인지 반환 –>
    ${status.last}<br/>           <!– 현재 루프가 마지막인지 반환 –>
    ${status.begin}<br/>        <!– 시작값 –>
    ${status.end}<br/>         <!– 끝값 –>
    ${status.step}<br/>         <!– 증가값 –>
 
< /c:forEach>
 
cs


5. JSTL for문


1. 인덱스로 접근



1
2
3
4
5
6
<c:forEach var="i" begin="1" end="5" step="1" varStatus="status">
    번호 : ${status.count}
    이름 : ${item[i].name}
    직업 : ${item[i].job}
    급여 : ${item[i].salary}
</c:forEach>
cs


2. begin과 end로 접근


 

1
2
3
4
5
6
<c:forEach var="item" items="${list}" begin=0 end=5 step=1 varStatus="status">
    번호 : ${status.count}
    이름 : ${item.name}
    직업 : ${item.job}
    급여 : ${item.salary}
</c:forEach>
cs


3. 일반적인 접근



1
2
3
4
5
6
<c:forEach var="item" items="${list}" varStatus="status">
    번호 : ${status.count}
    이름 : ${item.name}
    직업 : ${item.job}
    급여 : ${item.salary}
</c:forEach>
cs


4. JSTL break문 구현



1
2
3
4
5
6
7
8
9
10
11
12
<c:set var="doneLoop" value="false" /> 
<c:forEach var="entity" items="${_STORE}" varStatus="status">
    <c:if test="${status.count % 6 eq 1}">
        <c:set var="doneLoop" value="false" />
    </c:if>
    <c:if test="${not doneLoop}"> 
        <span><input type="checkbox" id="store_id" name="store_id" value="<c:out value="${entity.STORE_ID}"/>" <c:out value="${entity.USE_YN}"/>><c:out value="${entity.STORE_NM}"/></span>
        <c:if test="${status.count % 6 eq 0}"> 
            <c:set var="doneLoop" value="true"/> 
        </c:if>
    </c:if>
</c:forEach>
cs


'전체 > Thymeleaf, JSTL' 카테고리의 다른 글

Thymeleaf 문법 정리  (3) 2018.10.08

테이블에 존재하는 과목, 점수, 이름 항목의 oracle 행을 열로 바꾸고


과목 점수가 가장 큰 사람과 평균 과목 점수가 가장 큰사람의 리스트를 출력하겠다.



1. 테이블 생성



1
2
3
4
5
6
CREATE TABLE SCOTT.TEST_TABLE
(
  NM     VARCHAR2(100 BYTE),
  OBJ    VARCHAR2(100 BYTE),
  SCORE  NUMBER(10)
)
cs



2.1 함수 사용 하지 않고 출력하기



1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
SELECT 
    
    (SELECT 
         NM 
       FROM 
       TEST_TABLE 
      WHERE 
      SCORE=(SELECT MAX(SCORE) 
       FROM TEST_TABLE
      WHERE OBJ='JAVA') 
        AND OBJ='JAVA') AS JAVA_TOP
    ,
    (SELECT 
         NM 
       FROM 
       TEST_TABLE 
      WHERE 
      SCORE=(SELECT MAX(SCORE) 
       FROM TEST_TABLE
      WHERE OBJ='JSP') 
        AND OBJ='JSP') AS JSP_TOP 
    ,
    (SELECT 
         NM 
       FROM 
       TEST_TABLE 
      WHERE 
      SCORE=(SELECT MAX(SCORE) 
       FROM TEST_TABLE
      WHERE OBJ='ORACLE') 
        AND OBJ='ORACLE') AS ORACLE_TOP 
    ,
    (SELECT 
         NM 
       FROM 
       TEST_TABLE 
      WHERE 
      SCORE=(SELECT MAX(SCORE) 
       FROM TEST_TABLE
      WHERE OBJ='JQUERY') 
        AND OBJ='JQUERY') AS JQUERY_TOP 
    ,
    (SELECT 
         NM 
       FROM 
       TEST_TABLE 
      WHERE 
      SCORE=(SELECT MAX(SCORE) 
       FROM TEST_TABLE
      WHERE OBJ='SPRING') 
        AND OBJ='SPRING') AS SPRING_TOP 
    ,
    (
     SELECT 
         NM
       FROM
       (
        SELECT NM
              ,AVG(SCORE) AS AVG_SCORE
          FROM TEST_TABLE
          GROUP BY NM
          ORDER BY AVG_SCORE DESC
       )
       WHERE ROWNUM=1
    ) AS AVG_TOP
       
    FROM TEST_TABLE
    WHERE ROWNUM <= 1
cs


2.2 MAX(DECODE)와 RANK OVER() 사용하기 - 1


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
 SELECT MAX(DECODE(OBJ, 'JAVA'  , NM, NULL)) AS JAVA_TOP
       ,MAX(DECODE(OBJ, 'JSP'  , NM, NULL)) AS JSP_TOP
       ,MAX(DECODE(OBJ, 'ORACLE'  , NM, NULL)) AS ORACLE_TOP
       ,MAX(DECODE(OBJ, 'JQUERY'  , NM, NULL)) AS JQUERY_TOP  
       ,MAX(DECODE(OBJ, 'SPRING'  , NM, NULL)) AS SPRING_TOP
       ,MAX(DECODE(AVG_RANK1, 1 , NM, NULL)) AS AVG_TOP1
       ,MAX(DECODE(AVG_RANK2, 1 , NM, NULL)) AS AVG_TOP2  
   FROM
     (SELECT NM
          , OBJ
          , RANK() OVER(PARTITION BY OBJ ORDER BY SCORE DESC) AS SINGLE_RANK
          , RANK() OVER(PARTITION BY OBJ ORDER BY AVG_SCORE DESC) AS AVG_RANK1
          , DENSE_RANK() OVER(ORDER BY AVG_SCORE DESC) AS AVG_RANK2
       FROM 
       (
        SELECT NM
             , OBJ
             , SCORE
             , AVG(SCORE) OVER(PARTITION BY NM) AS AVG_SCORE
          FROM TEST_TABLE
       )
     ) 
   WHERE SINGLE_RANK = 1
 
 
 
 
 
     (SELECT NM
          , OBJ
          , RANK() OVER(PARTITION BY OBJ ORDER BY SCORE DESC) AS SINGLE_RANK
          , RANK() OVER(PARTITION BY OBJ ORDER BY AVG_SCORE DESC) AS AVG_RANK1
          , DENSE_RANK() OVER(ORDER BY AVG_SCORE DESC) AS AVG_RANK2
       FROM 
       (
        SELECT NM
             , OBJ
             , SCORE
             , AVG(SCORE) OVER(PARTITION BY NM) AS AVG_SCORE
          FROM TEST_TABLE
       )
     ) 
cs


레코드를 특정 column의 값을 기준으로 정렬하여 순서를 매길 수 있다. 

RANK() OVER 또는 ROW_NUMBER() OVER를 사용하면 된다. 



2.3 MAX(DECODE)와 RANK OVER() 사용하기 - 2



1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
WITH TEST_TABLE AS (
   SELECT '상곤' NM, 'JAVA' OBJ, 90 SCORE FROM DUAL UNION ALL
   SELECT '상곤' NM, 'ORACLE' OBJ, 50 SCORE FROM DUAL UNION ALL
   SELECT '상곤' NM, 'JSP' OBJ, 60 SCORE FROM DUAL UNION ALL
   SELECT '상곤' NM, 'SPRING' OBJ, 40 SCORE FROM DUAL UNION ALL
   SELECT '상곤' NM, 'JQUERY' OBJ, 80 SCORE FROM DUAL UNION ALL
   SELECT '건희' NM, 'JAVA' OBJ, 80 SCORE FROM DUAL UNION ALL
   SELECT '건희' NM, 'ORACLE' OBJ, 90 SCORE FROM DUAL UNION ALL
   SELECT '건희' NM, 'JSP' OBJ, 60 SCORE FROM DUAL UNION ALL
   SELECT '건희' NM, 'SPRING' OBJ, 50 SCORE FROM DUAL UNION ALL
   SELECT '건희' NM, 'JQUERY' OBJ, 40 SCORE FROM DUAL UNION ALL
   SELECT '성민' NM, 'JAVA' OBJ, 40 SCORE FROM DUAL UNION ALL
   SELECT '성민' NM, 'ORACLE' OBJ, 80 SCORE FROM DUAL UNION ALL
   SELECT '성민' NM, 'JSP' OBJ, 90 SCORE FROM DUAL UNION ALL
   SELECT '성민' NM, 'SPRING' OBJ, 70 SCORE FROM DUAL UNION ALL
   SELECT '성민' NM, 'JQUERY' OBJ, 50 SCORE FROM DUAL UNION ALL
   SELECT '승구' NM, 'JAVA' OBJ, 50 SCORE FROM DUAL UNION ALL
   SELECT '승구' NM, 'ORACLE' OBJ, 89 SCORE FROM DUAL UNION ALL
   SELECT '승구' NM, 'JSP' OBJ, 85 SCORE FROM DUAL UNION ALL
   SELECT '승구' NM, 'SPRING' OBJ, 90 SCORE FROM DUAL UNION ALL
   SELECT '승구' NM, 'JQUERY' OBJ, 70 SCORE FROM DUAL UNION ALL
   SELECT '상현' NM, 'JAVA' OBJ, 80 SCORE FROM DUAL UNION ALL
   SELECT '상현' NM, 'ORACLE' OBJ, 70 SCORE FROM DUAL UNION ALL
   SELECT '상현' NM, 'JSP' OBJ, 50 SCORE FROM DUAL UNION ALL
   SELECT '상현' NM, 'SPRING' OBJ, 40 SCORE FROM DUAL UNION ALL
   SELECT '상현' NM, 'JQUERY' OBJ, 90 SCORE FROM DUAL
)
SELECT  MAX(DECODE(OBJ, 'JAVA'  , NM, NULL)) AS JAVA_TOP
       ,MAX(DECODE(OBJ, 'JSP'  , NM, NULL)) AS JSP_TOP
       ,MAX(DECODE(OBJ, 'ORACLE'  , NM, NULL)) AS ORACLE_TOP
       ,MAX(DECODE(OBJ, 'JQUERY'  , NM, NULL)) AS JQUERY_TOP  
       ,MAX(DECODE(OBJ, 'SPRING'  , NM, NULL)) AS SPRING_TOP 
       ,MAX(DECODE(AVG_RANK1, 1 , NM, NULL)) AS RANK_OVER 
       ,MAX(DECODE(AVG_RANK2, 1 , NM, NULL)) AS DENSE_RANK 
 FROM(
       SELECT NM
          , OBJ
          , SCORE
          , RANK
          , RANK() OVER (PARTITION BY OBJ ORDER BY AVG_SCORE DESC) AS AVG_RANK1
          , DENSE_RANK() OVER(ORDER BY AVG_SCORE DESC) AS AVG_RANK2
       FROM 
       (
        SELECT NM
             , OBJ
             , SCORE
             , RANK() OVER(PARTITION BY OBJ ORDER BY SCORE DESC) AS RANK
             , AVG(SCORE) OVER(PARTITION BY NM) AS AVG_SCORE
          FROM TEST_TABLE
       )
      )
    WHERE RANK = 1
 
cs


'전체 > Oracle' 카테고리의 다른 글

ORACLE PIVOT과 UNPIVOT 정리  (2) 2017.06.09
ORACLE 정규표현식 SUBSTR, REPLACE, INSTR, DECODE, TRIM 사용하기  (2) 2017.06.05
ORACLE 계층형 쿼리 만들기  (0) 2017.06.05
정규표현식  (0) 2017.05.31
DUAL 테이블에서 00~23 시 까지 입력된 컬럼에 현재 시간 찍기  (0) 2017.05.18

+ Recent posts

티스토리툴바