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


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


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


저작자표시

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

공백 UPDATE, NULL UPDATE, 테이블 컬럼 데이터 수정방법  (0) 2019.11.15
ORA-28014, ORA-01940 DROP User,CREATE User Oracle 12c  (0) 2019.07.30
ORA-28000: the account is locked 활성화 방법  (0) 2019.07.08
oracle USER 생성, 권한부여, table space 생성, export, import 방법  (1) 2019.06.17
Oracle HINT  (0) 2019.04.11

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하고 해당 컬럼 데이터에 마우스 더블클릭으로 데이터를 조작하여 업데이트 할 수 있다.






저작자표시

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

오라클 column to row (컬럼의 데이터를 여러개의 row로 가져오기)  (0) 2020.02.26
ORA-28014, ORA-01940 DROP User,CREATE User Oracle 12c  (0) 2019.07.30
ORA-28000: the account is locked 활성화 방법  (0) 2019.07.08
oracle USER 생성, 권한부여, table space 생성, export, import 방법  (1) 2019.06.17
Oracle HINT  (0) 2019.04.11


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


저작자표시

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

오라클 column to row (컬럼의 데이터를 여러개의 row로 가져오기)  (0) 2020.02.26
공백 UPDATE, NULL UPDATE, 테이블 컬럼 데이터 수정방법  (0) 2019.11.15
ORA-28000: the account is locked 활성화 방법  (0) 2019.07.08
oracle USER 생성, 권한부여, table space 생성, export, import 방법  (1) 2019.06.17
Oracle HINT  (0) 2019.04.11


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


ORA-28000: the account is locked


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


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


1
ALTER USER scott ACCOUNT UNLOCK;
cs


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





저작자표시

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

공백 UPDATE, NULL UPDATE, 테이블 컬럼 데이터 수정방법  (0) 2019.11.15
ORA-28014, ORA-01940 DROP User,CREATE User Oracle 12c  (0) 2019.07.30
oracle USER 생성, 권한부여, table space 생성, export, import 방법  (1) 2019.06.17
Oracle HINT  (0) 2019.04.11
오라클 프로시저 만들기, 오라클 잡 등록하기  (0) 2019.01.24



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


저작자표시

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

ORA-28014, ORA-01940 DROP User,CREATE User Oracle 12c  (0) 2019.07.30
ORA-28000: the account is locked 활성화 방법  (0) 2019.07.08
Oracle HINT  (0) 2019.04.11
오라클 프로시저 만들기, 오라클 잡 등록하기  (0) 2019.01.24
UNION, UNION ALL 차이점  (1) 2017.11.23



다음과 같이 주문 테이블(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

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





저작자표시

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

ORA-28000: the account is locked 활성화 방법  (0) 2019.07.08
oracle USER 생성, 권한부여, table space 생성, export, import 방법  (1) 2019.06.17
오라클 프로시저 만들기, 오라클 잡 등록하기  (0) 2019.01.24
UNION, UNION ALL 차이점  (1) 2017.11.23
ORACLE 임시테이블 생성, 컬럼 데이터 유형 바꾸기( ADD, MODIFY, RENAME, DROP )  (2) 2017.11.22


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일로 설정된다.





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

oracle USER 생성, 권한부여, table space 생성, export, import 방법  (1) 2019.06.17
Oracle HINT  (0) 2019.04.11
UNION, UNION ALL 차이점  (1) 2017.11.23
ORACLE 임시테이블 생성, 컬럼 데이터 유형 바꾸기( ADD, MODIFY, RENAME, DROP )  (2) 2017.11.22
ORACLE 컬럼 중복값 제거(필터) 방법 정리  (0) 2017.11.22


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을 사용하여 집계함수를 넣어 조건비교를 하고 있다.





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

Oracle HINT  (0) 2019.04.11
오라클 프로시저 만들기, 오라클 잡 등록하기  (0) 2019.01.24
ORACLE 임시테이블 생성, 컬럼 데이터 유형 바꾸기( ADD, MODIFY, RENAME, DROP )  (2) 2017.11.22
ORACLE 컬럼 중복값 제거(필터) 방법 정리  (0) 2017.11.22
ORACLE WITH AS 사용방법  (0) 2017.07.25

+ Recent posts

티스토리툴바