꿈꾸는 개발자, DBA 커뮤니티 구루비

#문자 및 이진 문자열 타입

오라클 문자 데이터 타입 : CHAR, VARCHAR2, NCHAR, NVARCHAR2
데이터베이스 캐릭터셋 설정에 따라 문자를 표현 : US7ASCII(7비트, 128문자), WE8MSWIN1252(8비트, 256문자)

NLS 개요

NLS : National Language Support
저장된 원문 데이터의 인코딩, 캐릭터셋 변환, 데이터 정렬, 숫자 표현 등을 결정

Demo#1 (캐릭터셋 변환으로 인한 데이터 유실)
{code:sql	borderStyle=solid} SQL> SELECT * FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER = 'NLS_CHARACTERSET';

PARAMETER VALUE

---

---
NLS_CHARACTERSET KO16KSC5601

SQL> host echo $NLS_LANG
AMERICAN_AMERICA.KO16KSC5601

SQL> CREATE TABLE T ( DATA VARCHAR2(1) );

Table created.

SQL> INSERT INTO T VALUES ( CHR(224) );

1 row created.

SQL> INSERT INTO T VALUES ( CHR(225) );

1 row created.

SQL> INSERT INTO T VALUES ( CHR(226) );

1 row created.

SQL> SELECT DATA, DUMP(DATA) DUMP FROM T;

DATA DUMP

a Typ=1 Len=1: 224
a Typ=1 Len=1: 225
a Typ=1 Len=1: 226

-- 세 레코드의 DATA 컬럼 값이 모두 다름

SQL> COMMIT;

Commit complete.

– export NLS_LANG=AMERICAN_AMERICA.US7ASCII

SQL> SELECT DATA, DUMP(DATA) DUMP FROM T;

DATA DUMP

a Typ=1 Len=1: 224
a Typ=1 Len=1: 225
a Typ=1 Len=1: 226

-- 세 레코드의 DATA 컬럼 값이 모두 같음

SQL> VARIABLE D VARCHAR2(1)
SQL> VARIABLE R VARCHAR2(20)
SQL> BEGIN
2 SELECT DATA, ROWID INTO :D, :R FROM T WHERE ROWNUM = 1;
3 END;
4 /

PL/SQL procedure successfully completed.

SQL> UPDATE T SET DATA = :D WHERE ROWID = CHARTOROWID(:R);

1 row updated.

SQL> COMMIT;

Commit complete.

SQL> SELECT DATA, DUMP(DATA) DUMP FROM T;

DATA DUMP

NULL
a Typ=1 Len=1: 225
a Typ=1 Len=1: 226

-- 첫번째 레코드의 데이터가 손실됨
-- 위와 같은 케이스에서 EXP로 영구 백업을 받았는데 데이터 손실이 발생 했다면? (단, DATAPUMP 는 클라이언트 환경과 관계 없이 데이터베이스 캐릿터 셋으로 데이터를 EXPDP 한다)

|

h6. 문자열
{note}
 * 데이터 앞부분에 1~3 바이트의 길이 필드가 있고, 데이터가 NULL 이면 0xFF 로 표시됨 (단 뒤쪽 NULL 컬럼은 아무것도 저장하지 않음, 0 바이트)
 * 문자열 길이가 250(0x01 ~ 0xFA) 이하면 1 바이트, 초과 하는 경우 2 바이트 0xFE 플래그 사용
{note}

|{code:none|borderStyle=solid}VARCHAR2(80)

|

[11][H][e][l][l][o][ ][W][o][r][l][d]

|

{code:none

borderStyle=solid}CHAR(80){code}

{code:none

borderStyle=none}[80]H e l l o W o r l d ... {code}

CHAR/NCHAR 대신 VARCHAR2/NVARCHAR2 쓰자 (공간 절약, 데이터 비교 혼란)

Demo#2 (CHAR / VARCHAR2)
{code:sql	borderStyle=solid} SQL> CREATE TABLE T 2 ( CHAR_COLUMN CHAR(20), 3 VARCHAR2_COLUMN VARCHAR2(20) 4 ) 5 /

Table created.

SQL> INSERT INTO T VALUES ( 'Hello World', 'Hello World' );

1 row created.

SQL> SELECT * FROM T;

CHAR_COLUMN VARCHAR2_COLUMN

Hello World Hello World

SQL> SELECT * FROM T WHERE CHAR_COLUMN = 'Hello World'; -- 내부적 형변환

CHAR_COLUMN VARCHAR2_COLUMN

Hello World Hello World

SQL> SELECT * FROM T WHERE VARCHAR2_COLUMN = 'Hello World';

CHAR_COLUMN VARCHAR2_COLUMN

Hello World Hello World

SQL> SELECT * FROM T WHERE CHAR_COLUMN = VARCHAR2_COLUMN;

no rows selected

-- 같지만 다르다?

SQL> SELECT * FROM T WHERE TRIM(CHAR_COLUMN) = VARCHAR2_COLUMN;

CHAR_COLUMN VARCHAR2_COLUMN

Hello World Hello World

SQL> SELECT * FROM T WHERE CHAR_COLUMN = RPAD(VARCHAR2_COLUMN, 20);

CHAR_COLUMN VARCHAR2_COLUMN

Hello World Hello World

-- 바인드 변수 타입 VARCHAR2

SQL> VARIABLE VARCHAR2_BV VARCHAR2(20)
SQL> EXEC :VARCHAR2_BV := 'Hello World';

PL/SQL procedure successfully completed.

SQL> SELECT * FROM T WHERE CHAR_COLUMN = :VARCHAR2_BV;

no rows selected

SQL> SELECT * FROM T WHERE VARCHAR2_COLUMN = :VARCHAR2_BV;

CHAR_COLUMN VARCHAR2_COLUMN

Hello World Hello World

-- 바인드 변수 타입 CHAR

SQL> VARIABLE CHAR_BV CHAR(20)
SQL> EXEC :CHAR_BV := 'Hello World';

PL/SQL procedure successfully completed.

SQL> SELECT * FROM T WHERE CHAR_COLUMN = :CHAR_BV;

CHAR_COLUMN VARCHAR2_COLUMN

Hello World Hello World

SQL> SELECT * FROM T WHERE VARCHAR2_COLUMN = :CHAR_BV;

no rows selected

SQL>

|

h6. 문자열 문법

 * VARCHAR2 ( <SIZE> <BYTE | CHAR> ) : 최대 4000 바이트
 * CHAR ( <SIZE> <BYTE | CHAR> ) : 최대 2000 바이트
 * NVARCHAR2 ( <SIZE> )
 * NCHAR ( <SIZE> )

||길이 옵션||예제||설명||
|{code:none|borderStyle=solid}BYTE 옵션

|

VARCHAR2(10 BYTE)

|

데이터의 10바이트까지 지원, 멀티바이트 캐릭터 셋에서는 2문자만 저장할 수도 있음

|

{code:none

borderStyle=solid}CHAR 옵션{code}

{code:none

borderStyle=solid}VARCHAR2(10 CHAR){code}

{code:none

borderStyle=none}데이터의 10문자까지 지원, 멀티바이트 캐릭터 셋에서는 40바이트 까지 사용될 수 있음{code}

Demo#3 (BYTE / CHAR)
{code:sql	borderStyle=solid}

SELECT * FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER = 'NLS_CHARACTERSET';

CREATE TABLE T
( A VARCHAR2(1),
B VARCHAR2(1 CHAR),
C VARCHAR2(4000 CHAR)
)
/

INSERT INTO T (A) VALUES (UNISTR('\00d6'));
– 1 BYTE 로 설정된 A 컬럼에 멀티바이트 캐릭터 1개가 입력이 안됨
-- 싱글바이트 캐릭터 셋 : 20 문자 = 20 바이트
-- 멀티바이트 캐릭터 셋 : 20 문자 = 80 바이트 (문자 당 4바이트 일 경우)
-- 캐릭터 셋 변경시 기존 VARCHAR2(20) 은 VARCHAR2(20 CHAR) 로 수정 하거나 NLS_LENGTH_SEMANTICS (DEFAULT: BYTE) 를 CHAR 로 설정

INSERT INTO T (B) VALUES (UNISTR('\00d6'));
SELECT LENGTH(B), LENGTHB(B) DUMP(B) DUMP FROM T;
– 1글자(LENGTH), 2바이트(LENGTHB)

|

||Demo#4 (VARCHAR2(4000) / CHAR(2000))||
|{code:sql|borderStyle=solid}

DECLARE
  L_DATA VARCHAR2(4000 CHAR);
  L_CH   VARCHAR2(1 CHAR) := UNISTR('\00D6');
BEGIN
  L_DATA := RPAD(L_CH, 4000, L_CH);
  INSERT INTO T ( C ) VALUES ( L_DATA );
END;
/
-- 4000 문자는 실제 8000 바이트, 따라서 입력 실패

DECLARE
  L_DATA VARCHAR2(4000 CHAR);
  L_CH   VARCHAR2(1 CHAR) := UNISTR('\00D6');
BEGIN
  L_DATA := RPAD(L_CH, 2000, L_CH);
  INSERT INTO T ( C ) VALUES ( L_DATA );
END;
/

SELECT LENGTH(C), LENGTHB(C) FROM T WHERE C IS NOT NULL;

-- 2000 문자는 실제 4000 바이트, 따라서 입력 성공

|

'N' 타입 (NVARCHAR2, NCHAR)
- 멀티플 캐릭터 셋을 다루는 시스템에서 사용
- 대부분은 싱글바이트 고정 길이지만, 멀티바이트 데이터를 관리하거나 저장할 필요가 있을때
- 기존 데이터를 사용하면서 새로운 애플리케이션에서 멀티바이트 데이터를 지원할 필요가 있을때
- VARCHAR2, CHAR 와 차이점
  - 데이터베이스의 네셔널 캐릭터 셋에 의해 관리 됨
  - 길이는 CHAR 로 지정 가능

문자 및 이진 문자열 타입 (by openlsw) [2012.11.17]

#문자 및 이진 문자열 타입

NLS 개요