쿼리로 시간차 계산시 날짜를 문자로 바꾼뒤 마이너스 연산한 값을 사용한 경우가 가끔 보이던데, 

의외로 이같은 실수를 저지르는 개발자들이 상당히 있는데다가 

오류가 쉽게 드러나지 않아 모르고 지나쳐버리는 경우도 꽤 많습니다.

하지만 이런 코드는 심각한 오류를 가지고 있으므로 반드시 수정되야합니다.



두 경우 모두 결과 값이 1로 나오길 기대하겠지만 두번째 경우는 41이라는 결과가 나오게 됩니다.

문자간 마이너스 연산시 오라클이 문자를 숫자로 취급하는데, 

이때 숫자를 10진수로 처리하기 때문에 의도와는 달리 완전히 다른 계산 결과가 나와버린 것 입니다.


오라클에서 시간차를 구하려면 날짜간 마이너스 연산 후 단위를 변환해야 올바른 결과를 얻을 수 있습니다.



(날짜차이, 달수 차이등은 이전 포스팅 참조하세요.)




오라클에서 길이가 없는 문자열(aka empty string, white space, blank)은 null 로 취급된다.
즉, 필드에 들어있는 값이나 '' 문자 또는 함수가 반환하는 값이 길이가 없는 문자열이라면 null 로 취급된다는 것이다.
이러한 특징은 여타 DBMS 와 다른 특징이므로 매우 주의해야 할 점이다.

몇가지 예를 통해 오라클에서 길이가 없는 문자열을 null 로 취급하고 있음을 확인해보자.

1. NVL
NVL(expr1, expr2) 함수는 expr1이 null 이 아니면 expr1을 그대로 반환하고, null 이면 expr2을 반환한다.
NVL 을 이용해 null 이면 길이가 없는 문자열을 반환하도록 다음과 같이 쿼리를 작성하고 테스트해보면

SELECT NVL(null, '') FROM DUAL


의도와는 다르게 아래와 같이 null 이 반환되버린다.

 NVL(NULL,'')    
 --------------- 
 (null)          

2. INSERT
not null 필드에 길이가 없는 문자열을 삽입하기위해 임시테이블을 만들고, 레코드를 추가해보자.

CREATE TABLE TestTable (field1 varchar(50) not null)
GO
INSERT INTO TestTable VALUES ('')


그러면 다음과 같이 에러가 발생한다.

>[에러] 스크립트 라인: 1-1 ----------------------------------
 ORA-01400: NULL을 ("USER1"."TestTable"."FIELD1") 안에 삽입할 수 없습니다
 스크립트 라인 1. 문장 라인 1, 컬럼 26

3. 비교
'' 은 무엇과 비교하더라도 true 가 될 수 없다. 따라서 다음은 모두 true 가 아니다.

* '' = ''
* '' = NULL
* AnyExpression = ''


그리고 다음은 null 을 반환한다.

* trim(' ')
* '' 을 저장한 필드


단, 다음은 참이다.

* '' IS NULL


 
왜 오라클은 empty string 을 null 로 취급할까?
http://stackoverflow.com/questions/203493/why-does-oracle-9i-treat-an-empty-string-as-null 
SQL standard 가 만들어지기 전 empty string 으로 null 을 식별하도록 디자인 했었는데, SQL Standard 에서 empty string 과 null 을 구별하기로 정해진 이후에도 하위 호환성을 위해 열어둔 것 같다고 함.

오래전부터 데이터베이스 스키마 변경(버전)을 관리하기 위해 간단한 툴을 만들어 보고 싶었지만,
만들 시간이 별로 없기에.. 오늘은 관련 툴을 찾아봤다.

역시 시간이 없는 관계로 공부는 나중에..ㅎ

테이블에 랜덤한 dummy 데이터를 여러개 insert 하고 싶을때

SELECT a.cnt, trunc(a.cnt / 10), DBMS_RANDOM.value()
FROM (SELECT LEVEL cnt FROM DUAL CONNECT BY LEVEL < 10000) a

1. 날짜 더하기 빼기

오라클은 SQLServer의 dateAdd 함수가 없다. 대신 + / – 연산자를 이용해 일자를 더하고 뺀다.

select to_date('2009-10-10') - 60 from dual
---------------------------
2009. 8. 11 오전 12:00:00 

또는 add_months(date, n) 함수를 이용해 달을 더하거나 뺀다.

select add_months('2009-10-10', -1) from dual
------------------------------
2009. 9. 10 오전 12:00:00

시간은 ‘날짜 + 숫자/24’ 와 같은 방식으로 더하고 뺀다.

select to_date('2010-03-01') + 1/24 from dual
-----------------------------
2010. 3. 1 오전 1:00:00        

 

2. 날짜간 차이 구하기

오라클은 SQLServer의 dateDiff 함수도 없다. 대신 + / – 연산자를 이용해 일수의 차를 구할 수 있다.

select to_date('2010-03-02') - to_date('2010-03-01') from dual
----------------------------------------------
1                                             

달수 차는 months_between(date, date) 함수를 통해 구한다.

select months_between('2010-03-02', '2009-10-01') from dual
--------------------------------------------
5.03225806451612903225806451612903225806    

정수부분이 달 수의 차이. 비정수부분은 달수 계산하고 남은 나머지 부분이다.

 

3. 기타

LAST_DAY('2010-02-01')   
-------------------------
2010. 2. 28 오전 12:00:00  

ROUND(TO_DATE('2010-02-0111:32:40','YYYY-MM-DDHH24:MI:SS'),'DAY')   
--------------------------------------------------------------------
2010. 1. 31 오전 12:00:00                                

ROUND(TO_DATE('2010-02-0111:32:40','YYYY-MM-DDHH24:MI:SS'),'Q')   
------------------------------------------------------------------
2010. 1. 1 오전 12:00:00

TRUNC(SYSDATE,'MI')    
-----------------------
2010. 3. 18 오후 12:38:00

+ Recent posts