오라클, ROWNUM 및 NULL이란, , NLS Parameters, NLS_DATE_FORMAT, NLS_LANGUAGE, NLS_TERRITORY,

오라클, ROWNUM 및 NULL이란, , NLS Parameters, NLS_DATE_FORMAT, NLS_LANGUAGE, NLS_TERRITORY,

​

http://ojc.asia/bbs/board.php?bo_table=LecSQLnPlSql&wr_id=607

 

오라클, ROWNUM 및 NULL이란, , NLS Parameters, NLS_DATE_FORMAT, NLS_LANGUAGE, NLS_TERRITORY,

오라클, ROWNUM 및 NULL값, NLS_DATE_FORMAT3.3 ROWNUM 및 NULL값, NLS_DATE_FORMAT3.3.1 ROWNUMROWNUM은 의사 칼럼으로 참조만 될 뿐 데이터베이스에 저장되지 않는다. (DESC 명령으로 보이지 않는 의사 칼럼). SELECT절에 의해 추출되는 데이터(ROW)에 붙는 순번이다. 다시 말해 WHERE절까…

ojc.asia

3.3 ROWNUM 및 NULL값, NLS_DATE_FORMAT

3.3.1 ROWNUM

ROWNUM은 의사 칼럼으로 참조만 될 뿐 데이터베이스에 저장되지 않는다. (DESC 명령으로 보이지 않는 의사 칼럼). SELECT절에 의해 추출되는 데이터(ROW)에 붙는 순번이다. 다시 말해 WHERE절까지 만족 시킨 자료에 1부터 붙는 순번이다. WHERE절에 ROWNUM을 이용하여 조건을 주면 다른 조건을 만족시킨 결과에 대해 조건이 반영된다. SELECT 리스트에 ROWNUM을 이용하는 것도 물론 가능하다. ORDER BY를 사용한다면 WHERE절까지 만족 시킨 결과에 ROWNUM이 붙은 상태로 ORDER BY가 반영된다. 즉 ROWNUM은 ORDER BY전에 부여되며, ORDER BY는 맨 나중에 실행된다.

- ROWNUM을 변경하기 위해 DML을 사용할 수 없다.

- 주로 <, <= 사용하며 >, >= 인 경우 ROWNUM은 동작하지 않는다.

- ROWNUM = 1은 사용 가능 하지만 ROWNUM=2 인 경우는 데이터가 추출되지 않는다. (ROWNUM은 WHERE절을 만족하는 레코드에 붙이는 순번이므로 해석해 보면, 처음 한 건 추출해서 ROWNUM이 2인지 비교한다. 처음 레코드는 ROWNUM이 1, 조건에 맞지 않으므로 버리고 다른 다음 레코드 선택 후 또 ROWNUM이 2인지 비교하지만 역시 새로 추출되는 레코드는 ROWNUM이 1이므로 버리고 새로운 레코드를 추출한다, 이 과정을 반복해 보면 ROWNUM = 2는 도달할 수 없는 값이 됨을 알 수 있다.)

SQL> select rownum, empno, ename from emp; ROWNUM EMPNO ENAME ---------- ---------- -------------------- 1 7369 SMITH 2 7499 ALLEN 3 7521 WARD …… 14 개의 행이 선택되었습니다. -- 4건만 SEELCT SQL> select rownum, empno, ename from emp where rownum < 5; ROWNUM EMPNO ENAME ---------- ---------- -------------------- 1 7369 SMITH 2 7499 ALLEN 3 7521 WARD 4 7566 JONES -- 4건만 SELECT, ROWNUM을 먼저 부여 후 ename으로 정렬하니 ROWNUM이 깨진다. --ORDER BY는 ORDER BY를 제외한 쿼리를 먼저 실행 후 그 결과에 대해 정렬하므로 ROWNUM이 ENAME기준으로 정렬되게 되어 순차적으로 부여되지 않는다. SQL> select rownum, empno, ename from emp where rownum < 5 order by ename; ROWNUM EMPNO ENAME ---------- ---------- -------------------- 2 7499 ALLEN 4 7566 JONES 1 7369 SMITH 3 7521 WARD SQL> select rownum, empno, ename from emp; ROWNUM EMPNO ENAME ---------- ---------- -------------------- 1 7369 SMITH 2 7499 ALLEN 3 7521 WARD …… 14 개의 행이 선택되었습니다. SQL> select rownum, empno, ename from emp where rownum = 1; ROWNUM EMPNO ENAME ---------- ---------- -------------------- 1 7369 SMITH -- ROWNUM=2인 경우 선택되는 로우(행)가 없다. -- WHERE절에 ROWNUM 조건 외에는 없으므로 모든 데이터가 조건을 만족하게 되고, 최초 한건을 읽으면 ROWNUM이 1이되어 ROWNUM = 2 조건에 만족하지 않으므로 버리고, 다음 레코드를 또 읽어 ROWNUM을 부여하는데 이전에 읽은 데이터는 조건에 맞지 않아 버렸으므로 다시 ROWNUM이 1이되어 ROWNUM = 2 조건에 맞지않아 데이터를 버리고 다음 레코드를 읽게 된다. 이런식으로 하다보면 결국 ROWNUM = 2 조건을 만족하는 데이터는 없게된다. SQL> select rownum, empno, ename from emp where rownum = 2; 선택된 레코드가 없습니다.

3.3.2 NULL이란?

NULL은 공백이나 0과는 다른 값이 비어있다는 것을 의미한다. EMP 테이블에서 COMM 칼럼이 NULL이라는 것은 아직 수당이 정해지지 않거나 해서 값이 없음을 나타내는 것이지 수당이 0임을 나타내는 것은 아니다. 즉 아직 값이 적용되지 않았음을 의미한다. 오라클에서 칼럼에 아무런값이 없는 경우에 “칼럼이 널 값을 가졌다” 라고 하며 널 값은 칼럼이 NOT NULL로 설계되지 않았다면 어떤 데이터 타입에도 나타날 수 있고 길이가 0 으로 DATA를 위한 물리적 공간을 차지하지 않는다.

모든 오라클 스칼라 함수들은 NULL을 입력 받으면 널값을 리턴하며(NVL, REPLACE, CONCAT함수는 제외), 집합/집계 함수의 경우 NULL은 연산 대상에서 빠진다. 널 값하고 하는 모든 사칙연산은 모두 NULL이다. 예를 들자면 세 명의 학생이 있는데 국어 점수가 NULL, 100, 200 이라고 하자. 오라클 집계함수 AVG를 이용하게 되면 NULL인 데이터는 연산 대상에서 빠져 평균은 150 이된다. 흔히 세명 이니 평균이 100이라고 볼 수 있지만 널 값을 가진 칼럼 이기에 연산 대상에서 제외 된 것이다.

NULL값을 가진 칼럼에 대해 WHERE절에 NULL인지 확인 하기 위해서는 반드시 IS NULL, IS NOT NULL을 사용해야 한다. (= 또는 != 사용하지 말라!)

NULL값을 처리하는 함수는 NVL, NVL2, NULLIF, COALESC 등이 있으니 SQL함수에서 확인하자.

-- 연봉을 계산하는 문장이다. COMM 값이 NULL인 경우 연봉은 얼마인가? SQL> SELECT sal, comm, (sal+comm)*12 FROM emp; SAL COMM (SAL+COMM)*12 ---------- ---------- ------------- 800 1600 300 22800 1250 500 21000 2975 1250 1400 31800 2850 2450 3000 5000 1500 0 18000 1100 950 3000 1300 SQL> -- COMM이 널인 경우 SAL+COMM값도 NULL 이된다. SQL> -- 수당이 없는 사원 수는? SQL> select count(*) from emp 2 where comm is null; COUNT(*) ---------- 10 SQL> -- 아래 쿼리는 잘못 사용한 예이다. NULL연산은 IS NULL, IS NOT NULL을 사용하자. SQL> select count(*) from emp 2 where comm = null; COUNT(*) ---------- 0

3.3.3 NLS Parameters

NLS(National Language Support)는 언어 지원과 관련된 파라미터를 지칭하며

SQL*PLUS등에서 확인 하려면 다음과 같이 조회한다.

SQL> show parameter nls NAME TYPE VALUE ----------------------- ------ -------- nls_calendar string //sysdate등에서 보이는 캘린더 데이터 포맷 nls_comp string BINARY //where절, PL/SQL안의 NLS데이터 비교 방법 nls_currency string nls_date_format string nls_date_language string nls_dual_currency string nls_iso_currency string nls_language string AMERICAN nls_length_semantics string BYTE nls_nchar_conv_excp string FALSE nls_numeric_characters string nls_sort string nls_territory string AMERICA nls_time_format string nls_time_tz_format string nls_timestamp_format string nls_timestamp_tz_format string

NLS_DATE_FORMAT이란?

테이블의 날짜형 칼럼에 값을 입력 시 DATE형으로 만들어 입력을 하거나 NLS_DATE_FORMAT에 맞는 문자열이라면 문자열로 입력이 가능하다. 가끔 날짜형에 문자를 넣을 때 FORMAT이 맞지 않아 애로사항이 있을 수 있으니 NLS_DATE_FORMAT 값을 알고 그 형식대로 문자열을 입력하면 된다.

실제 날짜 표기(TO_CHAR, TO_DATE 실행시) “월요일”, “MON” 으로 표시를 좌우하는 NLS_DATE_FORMAT 값은 NLS_LANGUAGE 을 따른다.

NLS_DATE_FORMAT은 현재 시스템의 기본 날짜 입출력 형태를 지정하는 파라미터 이며 TO_CHAR, TO_DATE 함수의 기본 DATE FORMAT이다.

SQL> select * from nls_session_parameters 2 where parameter = 'NLS_DATE_FORMAT'; PARAMETER VALUE ------------------- ----------------------------------------- NLS_DATE_FORMAT RR/MM/DD SQL> select sysdate from dual; SYSDATE -------- 16/09/07 SQL> create table datetest ( 2 mydate date 3 ); 테이블이 생성되었습니다. SQL> insert into datetest values ('16/10/01'); 1 개의 행이 만들어졌습니다. SQL> insert into datetest values ('16-10-02'); 1 개의 행이 만들어졌습니다. SQL> insert into datetest values ('20161003'); 1 개의 행이 만들어졌습니다. SQL> insert into datetest values ('161004'); 1 개의 행이 만들어졌습니다. SQL> insert into datetest values ('10-06-2016'); insert into datetest values ('10-06-2016') * 1행에 오류: ORA-01830: 날짜 형식의 지정에 불필요한 데이터가 포함되어 있습니다 SQL> select * from datetest; MYDATE -------- 16/10/01 …… -- NLS_DATE_FORMAT에 맞지 않는 문자열이라면 DATE형으로 형변환해서 입력하면 된다. SQL> insert into datetest values (to_date('10-06-2016','dd-mm-yyyy')); 1 개의 행이 만들어졌습니다. SQL> select * from datetest; MYDATE -------- 16/10/01 …… 16/10/06 -- NLS_DATE_FORMAT을 변경하여 현재 시스템의 날짜 입출력 포맷을 바꾸자. SQL> alter session set nls_date_format='yyyy.mm.dd'; 세션이 변경되었습니다. SQL> select * from datetest; MYDATE ---------- 16.10.01 …… 16.10.06 -- NLS_LANGUAGE값을 한국어로 바꾸자. NLS_DATE_FORMAT은 NLS_LANGUAGE값을 따른다. SQL> alter session set nls_language='KOREAN'; 세션이 변경되었습니다. -- 요일을 단축형과 전체표기 방식으로 표시 SQL> SELECT TO_CHAR(sysdate,'DAY'), TO_CHAR(sysdate,'DY') from DUAL; TO_CHAR(S TO_C --------- ---- 화요일 화 SQL> alter session set nls_language='AMERICAN'; Session altered. SQL> SELECT TO_CHAR(sysdate,'DAY'), TO_CHAR(sysdate,'DY') from DUAL; TO_CHAR(SYSDA TO_CHA ------------- ------ TUESDAY TUE -- 월을 단축/축약형태로 표시(한글로 월표시는 단축형과 전체표기방식으로 표시하는 것이 같다.) SQL> alter session set nls_date_format = 'YYYY-MON-DD'; 세션이 변경되었습니다. SQL> select hiredate from emp where rownum < 3; HIREDATE ---------------- 1980-12월-17 1981-2월 -20 -- 월을 전체표기 방식으로 표시하자. SQL> alter session set nls_date_format = 'YYYY-MONTH-DD'; 세션이 변경되었습니다. SQL> select hiredate from emp where rownum < 3; HIREDATE -------------- 1980-12월-17 1981-2월 -20 SQL> alter session set nls_language='AMERICAN'; Session altered. -- 요일을 단축형과 전체표기 방식으로 표시 SQL> SELECT TO_CHAR(sysdate,'DAY'), TO_CHAR(sysdate,'DY') from DUAL; TO_CHAR(SYSDA TO_CHA ------------- ------ SUNDAY SUN -- 영문의 경우 단축형과 전체표기 방식이 다르다. 단축형은 3글자만 표시한다. SQL> alter session set nls_date_format = 'YYYY-MON-DD'; Session altered. SQL> select hiredate from emp where rownum < 3; HIREDATE -------------- 1980-DEC-17 1981-FEB-20 SQL> alter session set nls_date_format = 'YYYY-MONTH-DD'; Session altered. SQL> select hiredate from emp where rownum < 3; HIREDATE --------------------- 1980-DECEMBER -17 1981-FEBRUARY -20

NLS_LANG, NLS_LANGUAGE, NLS_TERRITORY

NLS_LANG : 오라클 데이터베이스의 환경변수 값이 아니라 사용자 환경을 Oracle DB 알려주는 역할을 하는 환경변수.

NLS_LANG = [언어]_[영역].[문자셋]

<language>_<Territory>.<client characterset>

예) AMERICAN_AMERICA.KO16KSC5601, AMERICAN_AMERICA.UTF8, KOREAN_KOREA.KO16KSC5601

언어 :현재 사용자가 사용하는 언어적 특성을 결정짓는 값

문자셋, 정렬방식, 날짜 표기에 사용되는 기호(년/월/일, YYYY/MM/DD)

Default 값은 AMERICAN 이다.

실제 날짜 표기(TO_CHAR,TO_DATE 실행시 - 월요일, MON 으로 표시를 좌우하는 NLS_DATE_FORMAT 값은 NLS_LANGUAGE 을 따른다.

영역 :현재 사용자 언어의 영역의 특성을 결정짓는 값

Language 값만 설정하면, Language 값을 따른다. ( KOREAN ==> KOREA )

문자셋 :현재 사용자의 시스템이 인식할 수 있는 문자셋의 값

만약에 Windows Client에서 한국어 환경을 사용하는 경우 NLS_LANG 값을′KOREAN_KOREA.KO16MSWIN949′로

유닉스 Client에서 한국어를 입출력한다면 다음과 같이 NLS_LANG을 ′KOREAN_KOREA.KO16KSC5601′로 설정 할 수 있다.

[NLS관련 주요 변수]

NLS_TERRITORY : 영역 설정 - NLS_LANG 변수값에 의해 자동 설정

설정 방법예 : ALTER SESSION SET NLS_TERRITORY = 'KOREA'

NLS_LANGUAGE : 언어 설정- NLS_LANG 변수값에 의해 자동 설정 초기화변수

설정 방법예 : ALTER SESSION SET NLS_LANGUAGE = 'KOREAN'

NLS_LANG : 언어,영역, 캐릭터셋 설정 - 기본값은 ′AMERICAN_AMERICA.US7ASCII′

설정 방법예 : OS 환경변수로 설정

NLS_COMP : SQL에서의 비교 방식(<,>,=) 설정 - BINARY값으로 비교

설정 방법예 : ALTER SESSION SET NLS_COMP =''

NLS_SORT : 문자열의 정렬방법 설정 - NLS_LANGUAGE값에 따라 결정

설정 방법예 : ALTER SESSION SET NLS_SORT = 'KOREAN_M'

[NLS_LANG 지정 방법]

1. SESSION LEVEL

- select * from nls_session_parameters 로 확인 가능

- 변경 방법1.

SQL> select * from nls_session_parameters where PARAMETER in ('NLS_LANGUAGE','NLS_TERRITORY','NLS_DATE_FORMAT','NLS_DATE_LANGUAGE','NLS_SORT'); PARAMETER VALUE ------------------------------ ---------------------------------------- NLS_LANGUAGE AMERICAN NLS_TERRITORY AMERICA NLS_DATE_FORMAT DD-MON-RR NLS_DATE_LANGUAGE AMERICAN NLS_SORT BINARY SQL> SELECT SYS_CONTEXT('USERENV','LANGUAGE') "NLS SET" from dual; NLS SET -------------------------------------------------------------------------------- AMERICAN_AMERICA.US7ASCII

-- 변경

SQL> ALTER SESSION SET NLS_LANGUAGE='KOREAN';

- 변경 방법2.

UNIX, WINDOWS 환병변수에서 NLS_LANG 값을 변경한다.

UNIX - export NLS_LANG=American_America.US7ASCII 설정

Windows - SET NLS_LANG=American_America.US7ASCII 설정

2. INSTANCE LEVEL

- SELECT * FROM NLS_INSTANCE_PARAMETERS ; 를 통해서 확인 가능

- SELECT NAME, VALUE FROM V$PARAMETER WHERE NAME LIKE '%NLS%';

3. DB LEVEL

- SELECT * FROM NLS_DATABASE_PARAMETERS ;

- SELECT NAME, VALUE$ FROM SYS.PROPS$ WHERE NAME LIKE '%NLS%' ;

- SELECT * FROM V$NLS_PARAMETERS 에서 확인 가능