반응형

Oracle Replace 함수 생성(결과값 변환 출력)


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
69
70
71
CREATE OR REPLACE FUNCTION FN_REPLACE_CUST(
    IN_VALUES     IN      VARCHAR2,
    IN_TYPE         IN INT,
    IN_DELIMETER IN    VARCHAR2
)
RETURN VARCHAR2
    
/**************************************************************************
  # FN : FN_REPLACE_CUST
  # 옵션
  IN_VALUES     : 분리할 문자열 입력
  IN_TYPE        : 출력값 구분자(코드, 코드명)
  IN_DELIMITER : 분리할 구분자
**************************************************************************/
IS
    RETURN_VAL    VARCHAR2(2000); -- 결과 리턴 변수
    TMP_RET_VALUE VARCHAR2(2000);  -- 결과 임시 저장 변수
    
    SET_CODE  VARCHAR2(2000);        -- SELECT 결과 코드 저장 
    SET_CODE_NAME VARCHAR2(2000); -- SELECT 결과 코드명 저장
     
BEGIN
    
   -- IN_VALUES의 값이 없으면 빈 값을 리턴하고 종료
    IF LENGTH(LTRIM(RTRIM(IN_VALUES))) = 0 THEN
        RETURN '';
    END IF;
    
   FOR ForResult IN
    (
     WITH T AS
      (
        SELECT IN_VALUES AS SET_VALUES FROM DUAL -- 입력받은 문자열 임시 테이블 처리
       )
       /* 문자열을 분리하여 세로로 추출 */
       SELECT TRIM(REGEXP_SUBSTR(SET_VALUES, '[^'||IN_DELIMETER||']+', 1, LEVEL)) AS SET_VALUES
         FROM T
         CONNECT BY INSTR(SET_VALUES, ''||IN_DELIMETER||'', 1, LEVEL - 1) > 0
    )
    LOOP
            /* COMMON_CODE 의 코드 값과 비교후 결과값 대입 */
            SELECT CODE_M, CODE_M_NAME
                INTO SET_CODE, SET_CODE_NAME
                FROM COMMON_CODE
                WHERE CODE_L = '005'
                AND CODE_M = ForResult.SET_VALUES;
                  
            /* # IN_TYPE 구분값에 따라 코드번호, 코드명 추출 */
            IF IN_TYPE = 1 THEN
                TMP_RET_VALUE := SET_CODE;
            ELSIF IN_TYPE = 2 THEN
                TMP_RET_VALUE := SET_CODE_NAME;
            END IF;
                  
            /* # 결과값 저장 : 결과값 가로 배치 */
            IF RETURN_VAL IS NULL THEN
                    RETURN_VAL := TMP_RET_VALUE;
               ELSE
                    RETURN_VAL := RETURN_VAL||','||TMP_RET_VALUE;
            END IF;
    END LOOP;
    
    RETURN RETURN_VAL; -- 결과 리턴
    
    EXCEPTION
         WHEN OTHERS THEN
--              RETURN SQLERRM;
            RETURN IN_VALUES; -- 에러 또는 조인 결과값 없을시 기본 입력받은 값 리턴
    
END FN_REPLACE_CUST;
/



> 생성후 조회

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- # IN_TYPE : 1
SELECT ORG_TYPE, FN_REPLACE_CUST(ORG_TYPE, 1, '|') AS REPLACE_VALUES
    FROM USER_INFO
;       
 
ORG_TYPE         REPLACE_VALUES
--------------------------------------------
101|102|103|104    101,102,103,104
 
 
-- # IN_TYPE : 2
SELECT ORG_TYPE, FN_REPLACE_CUST(ORG_TYPE, 2, '|') AS REPLACE_VALUES
    FROM USER_INFO
;    
 
ORG_TYPE         REPLACE_VALUES
--------------------------------------------
101|102|103|104    가수,작곡가,작사가,기타

출처 :http://develop.sunshiny.co.kr/

반응형

'프로그램 관련 > oracle' 카테고리의 다른 글

Oracle - chr() 함수, Ascii 함수  (0) 2018.09.11
Oracle 중복 값 체크및 제거  (0) 2018.09.11
KO16KSC5601 에서 UTF8 or AL32UTF8 데이터 이관(import)  (0) 2018.09.11
컬럼 문자(char), 바이트(byte) 단위 변경  (0) 2018.09.11
ORACLE DECODE , 사용기~  (0) 2018.09.11
반응형

 KO16KSC5601 에서 UTF8 or AL32UTF8 데이터 이관(import)


KO...등으로 시작하는 캐릭터셋은 한글을 2Byte로 표현
UTF8 에서는 한글을 3Byte로 표현.

> KO16KSC5601에서 Export한 파일을 UTF8의 DB에 데이터 이관시, 
  한글 문자열이 있는 컬럼에서 아래와 같은 에러가 발생하며 import 되지 않음.

IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered
ORA-12899: value too large for column "USER_ID"."TABLE_NAME"."CONTENTS" (actual: 105, maximum: 100)



# CHAR, VARCHAR 타입의 컬럼에서 기본단위(Byte)를 문자단위(Char)로 변경하여 import 가능
  > 이 방법도 KO16KSC5601 캐릭터셋의 VARCHAR 타입에서 한글 글자수가 1330(4000/3)여개 이상일경우 문제발생.


> 진행
1) 캐릭터셋 KO16KSC5601의 데이타 Export
2) Import 할 DB의 NLS_LENGTH_SEMANTICS 파라메터 변경
   ALTER SYSTEM SET NLS_LENGTH_SEMANTICS=CHAR SCOPE=BOTH ;
   또는
   CREATE TABLE TABLE_NAME(VARCHAR2(100 CHAR))와 같이 
   DDL 스크립트의 문자 데이터 타입의 단위설정에 CHAR를 지정(기본 Byte 단위)
3) 목표 DB의 Oracle 계정 imp 명령에서 SHOW=Y 옵션을 이용하여 DDL 스크립트 추출
   imp dbuser/1234 file='/DMP/database.dmp' log='/DMP/database_sql.log' fromuser=dbuser SHOW=Y
   또는
   토드등의 툴에서 DDL 스크립트 추출
4) 목표 DB에 DDL 스크립트를 이용하여 테이블 생성(스크립트의 문자 타입에 CHAR 단위 지정)
5) 목표 DB에 imp 명령에서 IGNORE=Y 옵션을 이용하여 데이터 import 진행
   imp dbuser/1234 file='/DMP/database.dmp' log='/DMP/database.log' fromuser=dbuser IGNORE=Y


출처 : http://develop.sunshiny.co.kr/category/10

반응형

'프로그램 관련 > oracle' 카테고리의 다른 글

Oracle 중복 값 체크및 제거  (0) 2018.09.11
Oracle Replace 함수 생성(결과값 변환 출력)  (0) 2018.09.11
컬럼 문자(char), 바이트(byte) 단위 변경  (0) 2018.09.11
ORACLE DECODE , 사용기~  (0) 2018.09.11
UNION과 UNION ALL 의 차이 및 주의 사항  (0) 2018.08.22
반응형

# 컬럼 문자(char), 바이트(byte) 단위 변경



NLS_LENGTH_SEMANTICS는 바이트(Byte) 또는 문자(Char)길이를 사용하여 char, varchar2 타입의 컬럼을 만들수 있습니다. 
이때 기존의 컬럼은 영향을 받지 않습니다.

NLS_LENGTH_SEMANTICS는 SYS와 SYSTEM의 테이블에 적용되지 않고, 데이터 사전(Data Dictionary)은 항상 바이트 의미 체계를 사용합니다.
그래서 NLS_LENGTH_SEMANTICS를 이용하여 CHAR 단위의 컬럼을 생성해도 데이터 사전에는 실제 바이트로 보여줌

# Oracle 9i부터 컬럼의 데이터타입에 Byte 또는 Char를 붙여 단위별 지정 가능
   문자(Char)단위로 변경 하면 설정한 값에 따라 자동으로 Byte 공간을 할당해줍니다.   

> KO16KSC5601
Byte 단위 : 영문/숫자/기호(1Byte), 한글/한자(2Byte)
Char 단위 설정 예) VARCHAR2(100 CHAR) -> VARCHAR2(200)(데이터사전)

> UTF8
Byte 단위 : 영문/숫자/기호(1Byte), 한글/한자(3Byte)
Char 단위 설정 예) VARCHAR2(100 CHAR) -> VARCHAR2(400)(데이터사전)


# NLS_LENGTH_SEMANTICS 설정 방법

1) NLS_LENGTH_SEMANTICS 전역 파라메터 변경 : DBMS 재시작 필요
  > ALTER [SYSTEM|SESSION] SET NLS_LENGTH_SEMANTICS=[CHAR|BYTE]
  
2) 테이블 생성시 지정
  > VARCHAR2(100 CHAR)


> 캐릭터셋 확인

1
2
3
4
5
6
7
8
9
10
SELECT PARAMETER, VALUE
    FROM NLS_DATABASE_PARAMETERS
    WHERE PARAMETER LIKE '%CHAR%';
     
      PARAMETER                      VALUE
------------------------------ --------------------------
NLS_NUMERIC_CHARACTERS         .,
NLS_CHARACTERSET               AL32UTF8
NLS_NCHAR_CONV_EXCP            FALSE
NLS_NCHAR_CHARACTERSET         AL16UTF16


> NLS_LENGTH_SEMANTICS 전역 파라메터 조회

1
2
3
4
5
6
7
8
9
10
11
SELECT *
    FROM NLS_SESSION_PARAMETERS
    WHERE PARAMETER = 'NLS_LENGTH_SEMANTICS'
;
 
-- SQL Plus 에서 파라메터 조회
SQL> SHOW PARAMETER NLS_LENGTH_SEMANTICS
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_length_semantics                 string      BYTE


> 데이터베이스 전역 NLS_LENGTH_SEMANTICS 변경, char 타입

1
2
3
4
5
6
7
8
SQL> ALTER SYSTEM SET NLS_LENGTH_SEMANTICS=CHAR SCOPE=BOTH ;
 
-- SQL Plus 에서 파라메터 조회
SQL> SHOW PARAMETER NLS_LENGTH_SEMANTICS
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_length_semantics                 string      CHAR


> DATA_LENGTH, CHAR_USED 타입 조회

1
2
3
4
5
SELECT  COLUMN_ID, OWNER, TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_LENGTH, CHAR_USED
    FROM DBA_TAB_COLUMNS
    WHERE TABLE_NAME ='[테이블명]'
    ORDER BY COLUMN_ID
;


> 테이블 생성및 테스트

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
/*
 # CHAR 타입 테이블 생성
*/
CREATE TABLE CHAR_TEST(
    SEQ NUMBER,
    TEXT_SMALL VARCHAR2(100 CHAR),
    TEXT_BIG VARCHAR2(4000 CHAR)
)
 
/*
 # CHAR_TEST 테이블 구조 조회
 > 테이블 구조상에서는 CREATE TABLE시에 설정한 사이즈로 Char 타입 정보 출력
*/
DESC CHAR_TEST;
 
Column Name    ID    Pk    Null?    Data Type   
--------------------------------------------------------
     SEQ         1        Y    NUMBER
   TEXT_SMALL   2        Y    VARCHAR2 (100 Char)
   TEXT_BIG     3        Y    VARCHAR2 (4000 Char)
 
 
/*
 # Data Dictionary 테이블 컬럼별 타입 조회(실제 바이트)
 > 데이터사전에서는 Char 타입에 맞춰서 자동으로 변경된 데이타 길이가 보여짐
   VARCHAR2 타입 최고 길이 4000바이트 안에서 자동 변경
*/
SELECT  COLUMN_ID, OWNER, TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_LENGTH, CHAR_USED
    FROM DBA_TAB_COLUMNS
    WHERE TABLE_NAME ='CHAR_TEST'
    ORDER BY COLUMN_ID
;
 
COLUMN_ID   OWNER   TABLE_NAME   COLUMN_NAME   DATA_TYPE   DATA_LENGTH   CHAR_USED
--------------------------------------------------------------------------------
    1    SUNSHINY    CHAR_TEST    SEQ    NUMBER    22   
    2    SUNSHINY    CHAR_TEST    TEXT_SMALL    VARCHAR2    400    C -- CHAR 타입
    3    SUNSHINY    CHAR_TEST    TEXT_BIG    VARCHAR2    4000    C -- CHAR 타입


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
/*
 # BYTE 타입 테이블 생성
 > 전역 NLS_LENGTH_SEMANTICS 가 BYTE 일경우 기본으로 BYTE 타입 생성
*/
CREATE TABLE BYTE_TEST(
    SEQ NUMBER,
    TEXT_SMALL VARCHAR2(100),
    TEXT_BIG VARCHAR2(4000)
)
 
-- BYTE_TEST 테이블 구조 조회
DESC BYTE_TEST;
 
Column Name    ID    Pk    Null?    Data Type    Default    Histogram    Encryption Alg
------------------------------------------------------------------------------------
    SEQ         1        Y    NUMBER        No       
TEXT_SMALL    2        Y    VARCHAR2 (100 Byte)        No       
TEXT_BIG      3        Y    VARCHAR2 (4000 Byte)        No   
 
-- Dictionary 테이블 컬럼별 타입 조회
SELECT  COLUMN_ID, OWNER, TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_LENGTH, CHAR_USED
    FROM DBA_TAB_COLUMNS
    WHERE TABLE_NAME ='BYTE_TEST'
    ORDER BY COLUMN_ID
;
 
COLUMN_ID    OWNER    TABLE_NAME    COLUMN_NAME    DATA_TYPE    DATA_LENGTH    CHAR_USED
--------------------------------------------------------------------------------
     1    SUNSHINY    BYTE_TEST    SEQ    NUMBER    22   
     2    SUNSHINY    BYTE_TEST    TEXT_SMALL    VARCHAR2    100    B -- BYTE 타입
     3    SUNSHINY    BYTE_TEST    TEXT_BIG    VARCHAR2    4000    B -- BYTE 타입

출처:http://develop.sunshiny.co.kr/969

반응형

'프로그램 관련 > oracle' 카테고리의 다른 글

Oracle Replace 함수 생성(결과값 변환 출력)  (0) 2018.09.11
KO16KSC5601 에서 UTF8 or AL32UTF8 데이터 이관(import)  (0) 2018.09.11
ORACLE DECODE , 사용기~  (0) 2018.09.11
UNION과 UNION ALL 의 차이 및 주의 사항  (0) 2018.08.22
oracle INTERSECT,MINUS 사용기  (0) 2018.08.22

+ Recent posts

티스토리툴바