- 컬럼을 여러개의 로우로 표현하기


테이블의 데이터를 보면 한 컬럼안의 구분자를 사용하여 여러개의 데이터를 저장하여 사용하는 테이블이 있다.


1
SELECT '02,031,032' LIST_COL FROM DUAL
cs


1
2
3
4
│    LIST_COL   │
│───────────────│
│  02,031,032   │
└───────────────┘
cs


현재 LIST_COL 컬럼에는 한 컬럼안에 데이터가 ,(콤마)로 구분되어 코드값이 저장되어 있다.



1개의 로우로 표현된 컬럼 값을 ,(콤마)로 데이터를 분리하여 여러개의 ROW로 표현해보겠다.



1.  , (콤마)로 구분되어 코드값을 여러개의 ROW로 추출하기


1
2
3
4
     WITH LIST_TXT AS (SELECT '02,031,032' LIST_COL FROM DUAL)
   SELECT TRIM(REGEXP_SUBSTR(LIST_COL,'[^,]+',1,LEVEL)) AS  LIST_TO_ROW
     FROM LIST_TXT
CONNECT BY INSTR(LIST_COL, ',' , 1, LEVEL - 1 ) > 0
cs

 

1
2
3
    SELECT TRIM(REGEXP_SUBSTR('02,031,032','[^,]+',1,LEVEL)) AS  LIST_TO_ROW
      FROM DUAL
 CONNECT BY INSTR('02,031,032'','1, LEVEL-1 ) > 0
cs


[결과]


1
2
3
4
5
6
7
8
│  LIST_TO_ROW  │
│───────────────│
│     02    │
├───────────────┤
│     031    │
├───────────────┤
│     032    │
└───────────────┘
cs




이렇게 사용하면 여러개의 테이블에 필요한 데이터를 추출하고 싶을때 SUBQUERY를 사용하여 뽑아낼수 있다.


2. 응용하기 ( 데이터 리스트를 row로 표현하여 해당 데이터만 뽑아내기 )


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
    SELECT A.REGION
             , A.AREA_CODE
             , B.RESIDENCE_NAME
      FROM 
    (
        SELECT '000' AS P_CODE,'서울' AS REGION, '02' AREA_CODE FROM DUAL
        UNION ALL
        SELECT '000' AS P_CODE, '경기' AS REGION, '031' AREA_CODE FROM DUAL
        UNION ALL
        SELECT '000' AS P_CODE, '인천' AS REGION, '032' AREA_CODE FROM DUAL
        UNION ALL
        SELECT '000' AS P_CODE, '강원' AS REGION, '033' AREA_CODE FROM DUAL
        UNION ALL
        SELECT '000' AS P_CODE, '부산' AS REGION, '051' AREA_CODE FROM DUAL
    ) A
    INNER JOIN
    (
        SELECT '000' AS P_CODE, '조현영' AS RESIDENCE_NAME, '02,031,032' AS AREA_CODE_LIST FROM DUAL
    ) B
    ON A.P_CODE = B.P_CODE
    WHERE A.AREA_CODE IN(
        SELECT TRIM(REGEXP_SUBSTR(B.AREA_CODE_LIST,'[^,]+',1,LEVEL)) AS  LIST_TO_ROW
          FROM DUAL
     CONNECT BY INSTR(B.AREA_CODE_LIST, ','1, LEVEL-1 ) > 0
    )
cs


임시로 A, B 테이블 데이터를 정의하고 

A테이블에는 지역과 지역번호 데이터가 있고 

B테이블에는 지역번호 데이터가 ,(콤마)로 구분되어 여러개 들어가 있다.


[A 테이블]


1
2
3
4
5
6
P_CODE   REGION  AREA_CODE
 000   │ 서울   │   02    │
 000   │ 경기   │  031    │
 000   │ 인천   │  032    │
 000   │ 강원   │  033    │
 000   │ 부산   │  051    │
cs


[B 테이블]


1
2
P_CODE   RESIDENCE_NAME   AREA_CODE_LIST
 000   │    조현영        │   02,031,032  │
cs


A테이블과 B테이블을 조인하여

조현영의 지역번호에 해당하는 지역명을 추출하려면 REGEXP_SUBSTR 정규식을 사용해 

한 컬럼에 저장된 리스트를 여러개의 row로 변환하여

in 조건절 안에 해당하는 데이터만 추출할 수 있는 것을 확인할 수 있다.


[결과]


1
2
3
4
REGION    AREA_CODE   RESIDENCE_NAME
 서울   │    02     │     조현영     │
 경기   │    031    │     조현영     │
 인천   │    032    │     조현영     │
cs


1. UPDATE ''(공백), NULL 이후 SELECT 하기


1
2
3
4
5
6
/* 테스트 테이블 생성 */
CREATE TABLE TEST_TABLE1 AS(
    SELECT 1 E_ID, '조현영' E_NAME FROM DUAL UNION ALL
    SELECT 2 E_ID, '이상현' E_NAME FROM DUAL UNION ALL
    SELECT 3 E_ID, '김지숙' E_NAME FROM DUAL 
);
cs


1
2
3
/* 조회하기 */
SELECT * 
  FROM TEST_TABLE1;
cs




1
2
3
4
/* E_NAME을 공백으로 업데이트 한다. */
UPDATE TEST_TABLE1
   SET E_NAME = ''
 WHERE E_ID = 1;
cs


1
2
3
4
/* E_NAME을 공백으로 업데이트 한 내용을 SELECT한다. */
SELECT * 
  FROM TEST_TABLE1
 WHERE E_NAME = '';
cs




결과가 나오지 않는다.


본인은 E_NAME을 공백으로 UPDATE를 완료하고 

E_NAME에는 공백의 데이터가 들어갈 것이라 예상했지만 

공백으로 조회했을때 데이터가 조회되지 않는다.


1
2
3
4
/*결과가 나온다. */
SELECT * 
  FROM TEST_TABLE1
 WHERE E_NAME IS NULL;
cs


하지만 IS NULL로 SELECT를 하면 데이터가 조회된다.



이 결과를 통해 NULL, 공백('') 모두 NULL로 업데이트 치는 것을 확인했다.


1
2
3
4
5
6
7
8
9
/* E_NAME을 공백으로 업데이트 한다. */
UPDATE TEST_TABLE1
   SET E_NAME = ''
 WHERE E_ID = 1;
 
/* E_NAME은 NULL로 업데이트 된다. */
UPDATE TEST_TABLE1
   SET E_NAME = NULL
 WHERE E_ID = 1;
cs


Mybatis를 사용한다면 param값의 유무에 따라 null과 공백을 같이 체크하는 경우를

다음과 같이 볼 수 있다. 


1
2
<if test='custNo != null and custNo != ""'>
</if>
cs


본인은 쿼리에서도 공백으로 데이터를 넣으면 공백으로 업데이트 되는 줄 알았던 것이다.




2. 테이블 컬럼 데이터 쉽게 UPDATE 하는 방법 


일반적인 UPDATE 문이다.

1
2
3
UPDATE TEST_TABLE1
   SET E_NAME = ''
 WHERE E_ID = 1;
cs


UPDATE 하기전 UPDATE할 데이터를 미리 SELECT 하고 데이터를 확인해

조건을 걸어 작업 하는 경우가 많은데 2단계의 과정 대신

SELECT 해서 바로 컬럼으로 접근해 고칠수 있는 방법이 있다.


1
2
3
4
5
6
7
 EDIT [테이블명] 
 WHERE [컬럼] = [조건]
 ;
 
 SELECT A.*, ROWID
   FROM [테이블명] alias A
 ;
cs


위 구문을 사용하면 SELECT하고 해당 컬럼 데이터에 마우스 더블클릭으로 데이터를 조작하여 업데이트 할 수 있다.







1. 유저 삭제하기


1
drop user [user명] cascade;
cs


[ORA-28014: cannot drop administrative users] 발생 시


1
alter session set "_oracle_script"=true;
cs



[ORA-01940: cannot drop a user that is currently connected] 발생 시


현재 session에 대해 select 하여 session을 KILL해줘야 한다.



- session 조회 하기

1
select sid,serial# from v$session where username = [user명 대문자로];
cs


- session KILL 하기

1
ALTER SYSTEM KILL SESSION '[SID], [SERIAL]';
cs


- use drop 하기

1
drop user dev_emart cascade;
cs



2. 유저 생성하기


1
2
3
4
5
6
7
8
9
alter session set "_ORACLE_SCRIPT"=true;
 
create user [user명] identified by [패스워드];
 
grant create session to [user명];
 
grant connect, resource to [user명];
 
alter user [user명] default tablespace users quota unlimited on users;
cs



SQLPlus를 이용해 scott/tiger 로 로그인을 시도했는데 계정이 잠겼다고 오류가 발생하였다.


ORA-28000: the account is locked


scott tiger 계정 활성화 방법으로는


시스템 계정으로(system / 비밀번호) 로그인해서  


1
ALTER USER scott ACCOUNT UNLOCK;
cs


다음 쿼리를 통해 scott의 계정잠김을 해제할 수 있다.







1. USER 생성


1
2
3
4
-- user 생성
CREATE USER SANGHYUN IDENTIFIED BY mypassword
DEFAULT tablespace USERS
TEMPORARY tablespace TEMP;
cs


2. USER 권한부여하기


1
2
3
4
5
-- 권한부여하기
GRANT RESOURCE, CONNECT TO SANGHYUN;
 
-- 데이터베이스 import 시 권한 부여하기
GRANT IMP_FULL_DATABASE TO SANGHYUN;
cs


3. TABLE SPACE 생성


1
2
3
4
-- table space 생성
CREATE TABLESPACE MY_TABLE_TAB DATAFILE 'MY_TABLE_TAB' SIZE 2048M 
AUTOEXTEND ON NEXT 4M MAXSIZE UNLIMITED LOGGING PERMANENT 
EXTENT MANAGEMENT LOCAL AUTOALLOCATE BLOCKSIZE 8K SEGMENT SPACE MANAGEMENT MANUAL FLASHBACK ON
cs


4. export 방법


1
2
-- export 방법
exp userid=sanghyun/mypw  file='D:\myfile.dmp'
cs


5. import 방법


1
2
-- import 방법
imp username/password file=D:\myfile.dmp commit=y ignore=y full=y;
cs




다음과 같이 주문 테이블(ORDER_MST), 주문 아이템 테이블 (ORDER_ITEM) 이 있다. 

1개의 주문에는 N개의 주문 아이템이 들어간다.(1:N 관계)


주문 테이블(ORDER_MST)은 상품번호(GOOD_ID)가 없으며 주문번호(ORD_NO)가 중복될수 없다.



주문번호 

 주문상태

 주문자

 1

 A1

 조현영

 2

 A1

 조현영

 3

 A2

 조현영




주문 아이템 테이블(ORDER_ITEM)에는 상품번호(GOOD_ID)가 기준이어 주문번호(ORD_NO)가 중복될수 있다.


주문번호 

 상품번호

 상품이름

 구매갯수

 주문날짜

1

 100

 우유

 1

190405 

1

 100

 사과

 3

190405

2

 100

 키위

 2

190405

3

 100

 우유

 3

190405




Q.    19년 4월 5일에 우유를 구매한 주문번호와 각 주문당 몇개의 우유를 구매하였는지 알아보려고 한다.


그러면 다음과 같이 쿼리를 작성할 수 있다.


1
2
3
4
5
6
7
8
9
10
SELECT A.ORD_NO     -- 주문번호
     , B.ORDER_DATE -- 주문날짜
     , B.GOOD_ID    -- 상품번호
     , B.ITEM_QTY   -- 갯수
  FROM ORDER_MST A
 INNER JOIN ORDER_ITEM B
    ON A.ORD_NO = B.ORD_NO
   AND A.ORDER_DATE >= '20190405'
   AND A.ORDER_DATE <= '20190405'   
   AND B.GOOD_ID = '10000'  -- 우유
cs



하지만 주문갯수가 1000개 이상 주문아이템도 10000개 이상으로 많다면 쿼리실행속도가 느려질수 있다.

이런경우 HINT를 사용하여 쿼리 실행속도를 개선시킬수 있다.



1
2
3
4
5
6
7
8
9
10
11
SELECT /*+ LEADING ( A ) USE_NL ( A B) */ 
       A.ORD_NO     -- 주문번호
     , B.ORDER_DATE -- 주문날짜
     , B.GOOD_ID    -- 상품번호
     , B.ITEM_QTY   -- 갯수
  FROM ORDER_MST A
 INNER JOIN ORDER_ITEM B
    ON A.ORD_NO = B.ORD_NO
   AND A.ORDER_DATE >= '20190405'
   AND A.ORDER_DATE <= '20190405'
   AND B.GOOD_ID = '10000'  -- 우유
cs



아래는 위 쿼리에 사용된 HINT 문장이다. 


1
/*+ LEADING ( A ) USE_NL ( A B) */ 
cs


LEADING은 join시에 가장 먼저 읽을 테이블을 선택하는 hint이다.

A 테이블을 먼저 읽어들이고 USE_NL(Use Nested Loop Join) 중첩 루프 조인이 일어나도록 하는 힌트 문장이다.  


주문 아이템(ORDER_ITEM) N의 데이터가 많아 

주문 마스터(ORDER_MST) 1을 먼저 읽어 

주문마스터와 주문아이템 테이블을 조인하겠다고 알려주는 것이다.



조인 순서를 알려주는 hint 문장 정리


- ORDERED

FROM절에 기술된 테이블 순서대로 조인을 수행하도록 유도한다.


- LEADING

FROM절에 기술한 테이블 순서와 상관없이 조인순서를 제어하는 힌트이다.


- USE_NL

중첩 루프 조인이 발생하게 하는 힌트이다.






1. PL/SQL이란?


- PL/SQL 은 Oracle’s Procedural Language extension to SQL 의 약자 이다.

- SQL문장에서 변수정의, 조건처리(IF), 반복처리(LOOP, WHILE, FOR)등을 지원하며,

오라클 자체에 내장되어 있는 Procedure Language 이다.

- DECLARE문을 이용하여 정의되며, 선언문의 사용은 선택 사항 이다.

- PL/SQL 문은 블록 구조로 되어 있고 PL/SQL자신이 컴파일 엔진을 가지고 있다.


2. PL/SQL의 장점


- PL/SQL 문은 BLOCK 구조로 다수의 SQL 문을 한번에 ORACLE DB로 보내서 처리하므로 수행속도를 향상 시킬수 있다.

- PL/SQL 의 모든 요소는 하나 또는 두개이상의 블록으로 구성하여 모듈화가 가능하다.

- 보다 강력한 프로그램을 작성하기 위해서 큰 블록안에 소블럭을 위치시킬 수 있다.

- VARIABLE, CONSTANT, CURSOR, EXCEPTION을 정의하고, SQL문장과 Procedural 문장에서 사용 한다.

- 단순, 복잡한 데이터 형태의 변수를 선언 한다.

- 테이블의 데이터 구조와 컬럼명에 준하여 동적으로 변수를 선언 할 수 있다.

- EXCEPTION 처리 루틴을 이용하여 Oracle Server Error를 처리 한다.

- 사용자 정의 에러를 선언하고 EXCEPTION 처리 루틴으로 처리 가능 하다.



3. 오라클 프로시저 샘플로 만들기


다음은 새직원의 대한 연차를 +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
CREATE OR REPLACE PROCEDURE INTRANET.JOB_AUTO_VAC_NEW_EMPLOYEE
IS
    --FOR문을 위한 커서 생성
    CURSOR EMP_VAC_CUR IS
     SELECT * FROM(
        SELECT EMP_NO
                 , EMP_NM
                 , VAC_TOTAL_CNT
                 , JOIN_DT
                 , TO_CHAR(TRUNC(A.JOIN_DT, 'DD'),'YYYY') AS JOIN_YEAR
          FROM EMP_EMPLOYEE A
       ) A1
      WHERE A1.JOIN_YEAR = TO_CHAR(TRUNC(SYSDATE, 'DD'),'YYYY');
      
    total_cnt VARCHAR2(10);
BEGIN
    FOR EMP_VAC IN EMP_VAC_CUR LOOP
        
        -- 자신의 연차+1 을 변수에 저장
        total_cnt := EMP_VAC.VAC_TOTAL_CNT + 1;
        
        UPDATE EMP_EMPLOYEE A
              SET VAC_TOTAL_CNT =  total_cnt
         WHERE EMP_NO = EMP_VAC.EMP_NO;
 
    END LOOP;
END;
/
 
cs


1. CREATE OR REPLACE PROCEDURE 사용해서 프로시저를 만들거나 교체한다.

2. CURSOR를 만들고 만든 커서를 VAC_CUR을 가지고 BEGIN 과 END사이 FOR반복을 돌린다.

3. FOR반복을 돌며 EMP(직원)의 현재연차를 가져와 현재연차+1을 하여 변수에 저장한다.

4. UPDATE를 하나씩 쳐주면서 반복한다.

5. 반복이 끝나면 종료한다.



4. 프로시저 실행하기


1
EXECUTE INTRANET.JOB_AUTO_VAC_NEW_EMPLOYEE
cs



5. 오라클 잡 등록하기


오라클 잡(ORACLE JOB)을 등록하여 원하는날짜와 시간대에 프로시저가 실행될수 있게 스케쥴링 할 수 있다.


toad를 사용한다면 create job을 통해 실행될 날짜를 설정할수 있고 어떤 프로시저를 실행할지 명시할 수 있다.



오라클 잡을 생성하게 되면 다음과 같이 PL/SQL 문이 만들어지고 job에 대한 상세한 내용을 다시 확인할 수 있다. 




내용을 풀이하면 job의 간격은 매월 1일에 실행할 것이고 다음실행 날짜는 2019년 2월 1일에

INTRANET 프로시저를 실행하겠다는 것이다. 

이 job은 2019년 2월 1일에 실행되고 다음 실행날짜는 3월 1일로 설정된다.






1. UNION, UNION ALL 차이점



1.1 UNION


1
2
3
4
5
6
7
8
9
-- UNION
SELECT 1 ID, '수학' SUBJECT, 90 SCORE FROM DUAL UNION
SELECT 1 ID, '수학' SUBJECT, 90 SCORE FROM DUAL UNION
SELECT 1 ID, '수학' SUBJECT, 90 SCORE FROM DUAL
 
-- Result 1
-- ID SUBJECT SCORE
-- 1     수학        90
;
cs


1.2 UNION ALL 



1
2
3
4
5
6
7
8
9
10
11
-- UNION ALL
SELECT 1 ID, '수학' SUBJECT, 90 SCORE FROM DUAL UNION ALL
SELECT 1 ID, '수학' SUBJECT, 90 SCORE FROM DUAL UNION ALL
SELECT 1 ID, '수학' SUBJECT, 90 SCORE FROM DUAL
 
-- Result 2
-- ID SUBJECT SCORE
-- 1     수학        90
-- 1     수학        90
-- 1     수학        90
;
cs


UNION 을 사용하면 컬럼의 값이 모두 같은 값이며 중복되면 중복되는 컬럼 row를 제외하고 출력되지만

UNION ALL 을 사용하면 컬럼의 값이 모두 같은 값이며 중복되더라도 모든 컬럼 row가 출력된다.




2. GROUP BY와 HAVING 이해하기



1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- GROUP BY와 HAVING 이해하기
 
SELECT A1.ID, A1.NAME, SUM(A1.SCORE) AS SCORE
  FROM (
            SELECT 1 ID, '김상현' NAME, '영어' SUBJECT, 80 SCORE FROM DUAL UNION ALL
            SELECT 1 ID, '김상현' NAME, '수학' SUBJECT, 80 SCORE FROM DUAL UNION ALL
            SELECT 2 ID, '이상현' NAME, '영어' SUBJECT, 100 SCORE FROM DUAL UNION ALL
            SELECT 2 ID, '이상현' NAME, '수학' SUBJECT, 100 SCORE FROM DUAL
           ) A1 
WHERE A1.NAME LIKE '%상현%'
GROUP BY A1.ID, A1.NAME, SCORE
HAVING SUM(SCORE) >= 170
 
-- RESULT 
 
-- ID NAME SCORE
-- 2  이상현   200
;
cs


해당 쿼리를 통해

GROUP BY는 데이터들을 그룹별로 나눌수 있고.

WHERE 절에서 집계함수를 사용 할 수 없으므로

HAVING을 사용하여 집계함수를 넣어 조건비교를 하고 있다.





+ Recent posts