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

문자 데이타 타입 : CHAR, VARCHAR2, NCHAR, NVARCHAR2가 있고 2000, 4000바이트 문자를 표현
US7ASCII 캐릭터 셋은 128문자 ASCII 표준. 7비트 사용하여 128문자를 표현가능
WE8MSWIN1252는 서유럽의 캐릭터 셋으로 128 ASCII 문자 표현. 8비트를 사용하여 128문자를 추가로 표현가능

NLS 개요

NLS는 국가별 언어를 지원한다
디스크에 영구적으로 저장된 원문 데이터의 인코딩
캐릭터 셋에서 다른 캐릭터 셋으로 데이터의 명확한 변환


처음 작업시 발생된 에러 

SELECT DATA, DUMP(DATA) DUMP
FROM T;

SQL> SELECT DATA, DUMP(DATA) DUMP
  2  FROM T;
SP2-0784: 0xE0(으)로 시작하는 부적합하거나 불완전한 문자가 반환됨
SP2-0784: 0xE1(으)로 시작하는 부적합하거나 불완전한 문자가 반환됨
SP2-0784: 0xE2(으)로 시작하는 부적합하거나 불완전한 문자가 반환됨

KO16MSWIN949작업시 생김


<에러 해결>
(캐릭터 셋 SERVER AL32UTF8, Client : AL32UTF8
해도 에러는 일어나지않지만 한글이 깨지는 현상 발생 조회시...
터미널 에서 set nls_lang=.AL32UTF8 으로 해결)



SQL> set linesize 50
SQL> SELECT *
  2  FROM NLS_DATABASE_PARAMETERS
  3  WHERE PARAMETER ='NLS_CHARACTERSET';

PARAMETER     		VALUE
------------  		--------
NLS_CHARACTERSET 	 AL32UTF8


host echo $NLS_LANG
$NLS_LANG

windown 환경에서는 레지스터리환경으로 변경가능

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.


D     DUMP
-    ------------------
Typ=1 Len=1: 224
Typ=1 Len=1: 225
Typ=1 Len=1: 226

SQL> commit;

Commit complete.

export NLS_LANG=AMERICAN_AMERICA.US7ASCII

SQL> variable d varchar2(1)
SQL> variable r varchar2(20)
SQL>
SQL> begin
  2  select data, rowid into :d, :r
  3  from  t
  4  where rownum =1;
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> update t
  2     set data = :d
  3  where rowid = chartorowid(:r);

1 row updated.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select data,dump(data) dump
  2  from t;

D  DUMP
-  ------
    Typ=1 Len=1: 224
	Typ=1 Len=1: 225
	Typ=1 Len=1: 226

캐릭터셋 변경관련 정리사이트
http://blog.naver.com/966138/60019247059

문자열

CHAR, VARCHAR2, NCHAR, NVARCHAR2 기본 문자열 존재
데이터가 NULL 이면 0xFF(255) 싱글바이트로 표현
문자열 길이가 250 이하라면 , 길이를 표현하기 위해서 1바이트 사용
문자열 길이가 250바이트 초과한다면, 2바이트의 0xFE(254) 플래그를 사용하여 표현

<VARCHAR2(80)에 저장된 hello World>

<CHAR(80)에 저장된 Hello Wolrd>

CHAR 보다 VARCHAR2보다 나은 게 하나도 없슴 그러므로 VARCHAR2를 쓰자!!!
저장공간 낭비, 정보조회시 애플리케이션 혼란 등...


SQL> drop table t;

Table dropped.

SQL>
SQL> create table t
  2  (
  3     char_column char(20),
  4     varchar2_column varchar2(20)
  5  );

Table created.

SQL>
SQL> insert into t values('Hello World','Hello World');

1 row created.

SQL> select *
  2  from t;

CHAR_COLUMN          VARCHAR2_COLUMN
-------------------- --------------------
Hello World          Hello World

SQL> select *
  2  from t
  3  where char_column ='Hello World';

CHAR_COLUMN          VARCHAR2_COLUMN
-------------------- --------------------
Hello World          Hello World

SQL>
SQL> select *
  2  from t
  3  where varchar2_column ='Hello World';

CHAR_COLUMN          VARCHAR2_COLUMN
-------------------- --------------------
Hello World          Hello World

SQL> select *
  2  from t
  3  where char_column = varchar2_column;

no rows selected
-- 같은 데이터지만 조회되지않는다.
-- CHAR는 빈공간 으로 채우기 때문에 다르다고 판단한다.

SQL> select *
  2  from t
  3  where trim(char_column) = varchar2_column;

CHAR_COLUMN          VARCHAR2_COLUMN
-------------------- --------------------
Hello World          Hello World


SQL> select *
  2  from t
  3  where char_column = rpad(varchar2_column,20);

CHAR_COLUMN          VARCHAR2_COLUMN
-------------------- --------------------
Hello World          Hello World

-- 바인드변수사용시
SQL> variable varchar2_bv varchar2(20)
SQL> exec :varchar2_bv := 'Hello World';

PL/SQL procedure successfully completed.

SQL>
SQL> select *
  2  from t
  3  where char_column = :varchar2_bv;

no rows selected


SQL> select *
  2  from t
  3  where varchar2_column = :varchar2_bv;

CHAR_COLUMN          VARCHAR2_COLUMN
-------------------- --------------------
Hello World          Hello World

-- varchar2 바인드 변수는 char로 확장되지 않음으로 나오지 않는다.
-- 정상적인 사용을 하기 위해서는 char 타입 바인드 변수를 사용한다.

SQL> variable char_bv char(20)
SQL> exec :char_bv := 'Hello World';

PL/SQL procedure successfully completed.

SQL>
SQL> select *
  2  from t
  3  where char_column = :char_bv;

CHAR_COLUMN          VARCHAR2_COLUMN
-------------------- --------------------
Hello World          Hello World

SQL>
SQL> select *
  2  from t
  3  where varchar2_column = :char_bv;

no rows selected

결론은 char는 쓰지말자!!(불필요한 함수를 사용하여 혼란을 야기 및 속도적인문제)
varchar2를 사용하자!!

문자열 문법

문자열타입	연산
VARCHAR (<SIZE> <BYTElCHAR>)	최대 4000바이트 저장. 1에서 4000까지 숫자로 지정
CHAR (<SIZE> <BYTElCHAR>)	최대 2000바이트 저장. 1에서 2000까지 숫자로 지정
NVARCHAR (<SIZE>)	내셔널 캐릭터 셋이 지원하는 0보다 큰 최대 범위의 숫자를 지정 가능
NCHAR (<SIZE>)	내셔널 캐릭터 셋이 지원하는 0보다 큰 최대 범위의 숫자를 지정 가능

바이트(BYTE) 또는 문자(CHAR) 옵션

BYTE : VARCHAR2(10 BYTE) 형식 데이터의 10바이트까지 지원. 멀티바이트 캐릭터 셋에서 BYTE 옵션은 CHAR 옵션과 같지 않다.
CHAR : VARCHAR2(10 CHAR) 형식. 데이터의 10문자까지 지원. VARCHAR2(4000 CHAR) 이론적으로 4000자 까지 가능


-- 바이트 또는 문자 테스트
SQL> SELECT *
  2  FROM NLS_DATABASE_PARAMETERS
  3  WHERE PARAMETER ='NLS_CHARACTERSET';

PARAMETER     		VALUE
------------  		--------
NLS_CHARACTERSET 	 AL32UTF8



SQL> drop table t;

Table dropped.

SQL>
SQL> create table t
  2  (
  3     a varchar2(1),
  4     b varchar2(1 char),
  5     c varchar2(4000 char)
  6  );

Table created.

SQL> desc t;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 A                                                  VARCHAR2(1)
 B                                                  VARCHAR2(1 CHAR)
 C                                                  VARCHAR2(4000 CHAR)

SQL> insert into t(a) values(unistr('\00d6'));
insert into t(a) values(unistr('\00d6'))
                        *
ERROR at line 1:
ORA-12899: value too large for column "BSHMAN"."T"."A" (actual: 2, maximum: 1)

-- vharchar2(1)은 한 문자를 의미하는게 아니라 1바이트를 의미한다.
-- 유니코드 한 문자는 1바이트로 지정된 컬럼에 입력X
-- 싱글바이트 캐릭터 셋에서 문자 20자는 20바이트를 차지 = varchar2(20)이면 가능
-- 멀티바이트 캐릭터 셋에서는 20문자 필드는 80바이트를 사용(문자당 4바이트 일 경우)
-- 이런경우 alter table t modify (a char(1 char))  의 DLL이나
-- 테이블 생성전 NLS_LENGTH_SEMANTICS (DEFAULT : BYTE) -> CHAR로 변경
-- alter session set NLS_LENGTH_SEMANTICS  ='CHAR';

SQL> insert into t(b) values(unistr('\00d6'));

1 row created.

SQL> select length(b), lengthb(b), dump(b) dump
  2  from t;

 LENGTH(B) LENGTHB(B)  DUMP
---------- ----------  -----------------
 1          2          Typ=1 Len=2: 195,150



-- 영문,한글 입력 테스트
SQL> insert into t(a) values('a');

1 row created.

SQL> insert into t(a) values('가');
insert into t(a) values('가')
                        *
ERROR at line 1:
ORA-12899: value too large for column "BSHMAN"."T"."A" (actual: 2, maximum: 1)

SQL>
SQL> insert into t(b) values('a');

1 row created.

SQL> insert into t(b) values('가');
insert into t(b) values('가')
                        *
ERROR at line 1:
ORA-12899: value too large for column "BSHMAN"."T"."B" (actual: 2, maximum: 1)

SQL> declare
  2     l_data varchar2(4000 char);
  3     l_ch   varchar2(1 char) := unistr('\00d6');
  4  begin
  5     l_data := rpad(l_ch,4000,l_ch);
  6     insert into t (c) values(l_data);
  7  end;
  8  /
declare
*
ERROR at line 1:
ORA-01461: can bind a LONG value only for insert into a LONG column
ORA-06512: at line 6

-- 4000문자 -> 실제 8000바이트 입력으로 에러

SQL> declare
  2     l_data varchar2(4000 char);
  3     l_ch   varchar2(1 char) := unistr('\00d6');
  4  begin
  5     l_data := rpad(l_ch,2000,l_ch);
  6     insert into t (c) values(l_data);
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL> select length(c), lengthb(c)
  2  from t
  3  where c is not null;

 LENGTH(C) LENGTHB(C)
---------- ----------
      2000       4000

-- 2000문자 -> 실제 4000바이트 입력으로 성공

h4.'N'타입

멀티플 캐릭터 셋을 관리하거나 저장할 필요가 있는 시스템에서 사용
새로운 애플리케이션에서 멀티바이트 데이터를 지원할 필요가 있는 시스템에서 유용

CHAR/VARCHAR의 차이

텍스트 기본 캐릭터 셋이 아닌 데이터베이싀 내셔널 캐릭터 셋의 의해 저장
길이는 char,byte 옵션이 가능하지만 n타입은 char옵션 길이만 표현

14S_문자 및 이진 문자열 타입 (by bshman) [2014.11.11]

NLS 개요

문자열

문자열 문법

바이트(BYTE) 또는 문자(CHAR) 옵션

CHAR/VARCHAR의 차이