신기한 연구소

안녕하세요. 신기한 연구소입니다.

SQL 작업을 하다 보면 NULL 처리가 필요한 경우가 많은데요.

SOL에서 NULL 처리를 하는 방법에 대해 알아봅니다.

오라클 기준으로 같이 살펴보겠습니다.

우선 NULL에 대해 알아볼게요.

NULL은 아직 정의가 안되었다, 또는 배정이 안 된 상태를 말합니다.

빈 값(공백)이나 0과는 다른 값이므로 주의해야 합니다. ASCII값도 다르거든요.

NULL의 특징 중 하나는 해당 값과 연산(+,-,*,/)을 해도 무조건 NULL이 나온다는 겁니다.

NULL에 대한 함수를 살펴보겠습니다.

우선 우리가 SELECT 문을 통해 데이터를 조회했는데 특정 컬럼의 값이 NULL이 나오는 경우가 있습니다.

이런 경우 대체할 값을 넣으면 좋은데요.

NVL이라는 함수를 사용하면 됩니다.

NVL(표현식_1, 표현식_2)

표현식_1의 값이 NULL이면 표현식 2의 값을 보여주고 NULL이 아니면 표현식_1의 값을 그대로 보여주는 함수입니다.

먼저 dept테이블의 loc 컬럼에 NULL값을 넣고 입력합니다.

조회하면 NULL로 표시됩니다.

아직 값이 할당 안되었다는 의미입니다.

사용자 화면에 NULL로 표시되면 일반 사용자들은 무슨 의미인지 모르겠지요?

Loc가 NULL인 경우는 ‘미설정’으로 변경해서 사용자에게 보여줄 수 있습니다.

5번째 라인에 NULL값이 '미설정'으로 변경된 것을 확인할 수 있습니다.

다음으로 NULLIF()에 대해 살펴봅니다.

NULLIF(표현식_1, 표현식_2)

표현식_1의 값과 표현식_2의 값이 같으면 NULL을 반환하는 함수입니다.

5번째 loc값과 NULL이 같기에 결과가 NULL로 나왔습니다.

3번째 dname값이 'SALES'면 NULL을 반환한다는 표현이기에 NULL로 변경된 것을 확인할 수 있습니다.

마지막으로 하나 더 살펴볼 함수는 COALESCE입니다.

COALESCE(표현식_1, 표현식_2, …)

표현식_1의 컬럼 중 NULL이 아닌 첫 번째 값을 보여줍니다.

표현식_2도 같은 원리이며 원하는 만큼 컬럼을 지정하면 됩니다.

coalesce(loc, dname)으로 표현식을 만들었는데 5번째 loc값이 NULL이어서 그다음 dname값인 IT가 나온 것을

확인할 수 있습니다.

AAA, BBB, CCC 컬럼값을 확인해 보면

ANO가 2인 경우 AAA는 NULL,

ANO가 3인 경우 AAA, BBB는 NULL입니다.

COALESCE를 사용하면 1행은 AAA가 값이 있으니 해당 값인 1

2행은 AAA가 NULL이기에 패스~ BBB는 값이 있으니 해당 값인 2

3행은 AAA, BBB가 NULL이기에 패스~ CCC는 값이 있으니 해당 값인 3

이 표출됩니다.

마지막으로 실전에서도 자주 사용하는 공집합에 대해 알아봅니다.

deptno가 60인 행은 없습니다.

그럼 데이터 자체가 존재하지 않기에 첫 번째 쿼리처럼 nvl함수를 사용해도 값이 나오지 않습니다.

그래서 max() 또는 min() 함수를 사용해서 NULL을 리턴 받은 다음 nvl을 사용하면

'no data'가 표출됨을 확인할 수 있습니다. 

이렇게 NULL에 대한 함수를 알아봤습니다.

수정이 필요하거나 잘못된 내용이 있으면 댓글 주세요.

아래 하트(공감) 버튼을 눌러서 더 다양한 글을 쓸 수 있게 응원 부탁드립니다. 감사합니다.

안녕하세요. 신기한 연구소입니다.

DML(Data Manipulation language)의 범주에 속한다는 TCL(Transaction Control Language)에 대해 알아봅니다.

DML의 범주에 속하지 않는다는 의견도 있지만 그보다 TCL에 대해 잘 정리하는 게 중요한 듯합니다.

오라클 기반에서 설명하겠습니다.

기업에서 내부 이체를 하는 경우를 생각해봅니다.

A라는 계좌의 돈을 B 계좌로 이체하는 경우입니다.

A에서 출금한 뒤 B로 입금이 완료되었습니다.

기업의 자금 시스템에서 해당 이체에 대한 결과를 수신했는데..

A 계좌에서 출금된 기록을 입력한 뒤 시스템 장애나 또는 B 계좌에 입금 처리하는 프로그램 오류로 인해서 B 계좌의 입금내역을 처리하지 못하게 된다면 큰 문제가 발생할 수 있습니다.

A 계좌에서는 돈이 나갔다고 하는데 B 계좌에서는 입금내역이 없는 것이죠.

물론 기업 내 자금 시스템 문제라면 실물 금액은 잘 이체되고 통장에는 잘 정리가 되어 있기에 나중에 대사 하는 과정에서 잘 정리하고 오류를 수정 및 개선하면 됩니다.

그렇지만 은행 시스템이라면 정말 심각한 문제이지요.

그래서 이렇게 하나 이상의 프로세스가 서로 밀접하게 연결되어서 일괄 처리할 수 있도록 논리적 그룹 또는 집합을 구성하는데 이를 트랜잭션(Transaction)이라고 합니다.

위의 경우를 보면 A 계좌의 출금 내역 갱신과 B 계좌의 입금내역 갱신은 서로 밀접하게 연결된 프로세스로 하나만 잘 되면 안 되고 두 개의 프로세스가 같이 실행되거나 취소돼야 데이터의 일관성을 유지할 수 있기에 하나의 논리적 그룹으로 묶어서 처리해야 합니다.

트랜젝션은 데이터의 변화에 대한 처리를 지원하기에 Insert, Update, Delete가 대상이 됩니다.

트랜젝션(Transaction)은 commit, rollback, savepoint 세 가지로 구성됩니다.

세가지 구성에 대해 알아봅니다.

COMMIT;

먼저 위 예제를 확인했을 경우 

A 계좌에 출금 내역 입력, B계좌의 입금내역 입력으로 

두 개의 insert문이 일괄 실행됩니다.

commit문이 실행되기 전은 두 개의 insert문 실행으로 메모리에 임시로 데이터가 입력된 상태입니다.

모든 사용자가 볼 수 있는 실제 입력이 완료된 상황이 아니고 작업자만 확인할 수 있는 상태입니다.

또한 commit 전이기에 해당 행은 락이 걸린 상태라 다른 사용자가 접근해서 변경할 수 없는 상태입니다.

오류가 없거나 데이터 입력이 확신이 되면 commit;문을 실행해서 실제로 데이터베이스에 입력됩니다.

[예시]

UPDATE ACCOUNT

SET REMAIN = 50000

WHERE ACCOUNT_NO = '100-100-100000';

1건 수정완료...

COMMIT; 

커밋완료...

ROLLBACK;

두 개의 입력문을 실행했는데..

입력문을 보니 오류가 있습니다.

그래서 다시 작업을 해서 입력문을 실행해야 한다면 

현재 메모리에 임시 반영된 입력 데이터를 지우고 락을 제거해야 합니다.

그때 Rollback;을 사용합니다.

Rollback;를 실행하면 두 개의 insert을 실행하기 전 상태로 돌아갑니다.

단, commit;을 하고 rollback;을 하는 건 의미가 없습니다.

commit; 전에 사용할 수 있습니다.

commit; 전에 실행한 모든 insert, update, delete문에 대해 원복 한다는 점을 기억하세요.

[예시]

DELETE FROM ACCOUNT

WHERE ACCOUNT_NO = '100-100-100000';

1건 삭제완료...

ROLLBACK; 

롤백완료...

SAVEPOINT;

실전에서 사용한 적은 없습니다만..

한 번에 처리해야 할 프로세스가 많은 상황에서 중간 부분까지 취소를 하고 오류 수정 후 다시 실행할 필요가 있는 경우에 사용하면 편리하겠더군요.

어떤 경우가 있을지 생각해봤는데요.

하나의 테이블로 관리되던 재고 자료를 기본정보와 부가정보로 나눠서 입력하는 경우를 생각해봅니다.

데이터양이 많기에 한번 실행하면 

기본정보가 6시간 부가정보가 4시간 정도로 

총 10시간 정도 걸린다고 가정해봅니다.

일괄처리라 sql문을 쭉 실행했는데 부가정보 중간 부분에 오류가 발생합니다.

헉~

rollback 하고 다시 수정 후 진행하려면 엄청난 시간이 소요되겠지요.

그때 중간에 savepoint를 활용해서 rollback를 해당 savepoint까지만 진행하고 오류 수정 후 다시 진행할 수 있기에

효율적으로 업무를 진행할 수 있게 됩니다.

[예시]

--기본정보 입력

INSERT INTO EMP (EMP_ID, EMP_NM)

VALUES ("00001", "홍순이");

INSERT INTO EMP (EMP_ID, EMP_NM)

VALUES ("00002", "강돌이");

...

INSERT INTO EMP (EMP_ID, EMP_NM)

VALUES ("99985", "공이돌");  --99985건 입력

SAVEPOINT SV01;

--부가정보 입력

INSERT INTO EMP_SUB (EMP_ID, EMP_SEQ, BIRTH)

VALUES ("00001", 1, "20030202");

...

INSERT INTO EMP_SUB (EMP_ID, EMP_SEQ, BIRTH)

VALUES ("99985", 99985, "20100309"); --99985건 입력

COMMIT;를 하면 전체 입력 완료~

ROLLBACK;을 하면 전체 입력 초기화~

ROLLBACK TO SV01;을 하면 제일 마지막 INSERT문에서 위로 올라가 SAVEPOINT SV01; 바로 전까지 ROLLBACK를 실행합니다.

SAVEPOINT는 중간중간 필요한 부분에 추가해서 넣을 수 있으며 하단부터 위로 ROLLBACK TO SAVEPOINT를 진행하면서 데이터를 확인 및 처리할 수 있습니다.

잘못된 정보나 수정이 필요하면 댓글 주세요.

감사합니다.