ICT블로그

쿼리를 작성할 때 사실 잘 의식하지는 못하는 부분이지만, 오라클이 query를 수행하는 과정에서 형 변환은 자주, 끊임없이 일어나고 있다. 이채남 저 '오라클 실습'에서 소개한 예제를 여기에 정리하고자 한다.

형변환 사례 1) CONCAT 또는 합성연산자 ||의 사용 시

select emp_id || emp_name from temp;

CONCAT또는 합성연산자는 두개 이상의 문자열을 하나의 문자열로 묶어주는 함수이다.

emp_id가 number type이기 때문에, 이때 오라클은 자동으로 number --> 문자(char? varchar2?)로 형변환을 시켜준다. 즉 위의 쿼리는 다음과 같이 처리가 되게 된다.

(1) 형변환(숫자 to 문자) --> (2)문자열 합성

형변환 사례 2) number type에 문자열 함수를 사용할 경우

위의 사례보다 더 중요하게 생각되는 사례 인데, 예를 들어 다음 쿼리를 보자.

select emp_id, emp_name

from temp

where substr(emp_id,1,4) + 1 = 1998;

emp_id의 첫번째 4자리에 1을 더한 값이 1998인 사원의 사번과 이름을 출력하는 쿼리인데, 문제는 emp_id는 number type인데 substr은 문자열 함수라는 것이다.

따라서 오라클은 위의 쿼리를 수행하기 위해 다음과 같이 형변환을 수행한다.

(1)형변환(숫자 to 문자) -->            (2) substr함수 수행 -->  (3)형변환 (문자 to 숫자) --> (4) 연산

19970101(숫자) --> 19970101(문자)  -->   1997(문자)  -->  1997(숫자)     --> +1 연산 --> 1998(숫자)

두번의 형변환이 일어났는데, 두번째 형변환은 당연히 +1이라는 연산을 수행해주기 위해 일어난 것이다. 비교 대상인 값인 =1998은 숫자로 쓰인 것이므로 세번째 형변환은 일어나지 않는다. 만약 쿼리를 다음과 같이 썻다면, 세번째 형 변환도 일어날 것이다.

select emp_id, emp_name

from temp

where substr(emp_id,1,4) + 1 = '1998';

정리해보니 사실 사례 1과 2는 같은 사례였구나..

이 외에도 형변환의 사례를 찾는다면 이 포스팅을 계속 업데이트 하고 싶은 바람이다.

-- 첫번째 의문 : NOT NULL과 PRIMARY KEY

궁금증 1) NOT NULL 제약조건과 PRIMARY KEY 제약조건을 같이 쓸수 있을까? --> YES

아래 DDL문은 이채남 저 오라클 실습 1장에 나오는 예제 테이블 생성 쿼리이다. EMP_ID에 NOT NULL과 PRIMARY KEY 제약조건을 같이 줬는데, PK 자체가 NOT NULL에 Unique니까 이렇게 잡아주는게 의미가 있나? 또는 틀린 구문은 아닐까? 라는 의문이 들었다.

CREATE TABLE TEMP2 (

 EMP_ID      NUMBER NOT NULL PRIMARY KEY,

 EMP_NAME    VARCHAR2(10) NOT NULL,

 BIRTH_DATE  DATE,

 DEPT_CODE   VARCHAR2(06) NOT NULL,

 EMP_TYPE    VARCHAR2(04),

 USE_YN      VARCHAR2(01) NOT NULL,

 TEL         VARCHAR2(15),

 HOBBY       VARCHAR2(30),

 SALARY      NUMBER,

 LEV         VARCHAR2(04)

);

아래 쿼리를 수행시켜서 확인해봤다.

select a.owner, a.constraint_name, a.constraint_type, a.table_name, b.column_name, a.search_condition from user_constraints a, user_cons_columns b

where a.owner=b.owner

and a.constraint_name=b.constraint_name

and a.table_name=b.table_name

and a.table_name='TEMP2';

OWNER   CONS_NAME CONS_TYPE TAB_NAME COL_NAME SEARCH_CONDITION

PRAC01 SYS_C0011311 C TEMP2 EMP_ID "EMP_ID" IS NOT NULL

PRAC01 SYS_C0011312 C TEMP2 EMP_NAME "EMP_NAME" IS NOT NULL

PRAC01 SYS_C0011313 C TEMP2 DEPT_CODE "DEPT_CODE" IS NOT NULL

PRAC01 SYS_C0011314 C TEMP2 USE_YN "USE_YN" IS NOT NULL

PRAC01 SYS_C0011315 P TEMP2 EMP_ID

위의 결과에서 EMP_ID 컬럼에 PK constraint 와 NOT NULL Constraint 조건이 함께 생성된걸 확인 할 수 있다.

만약 첫번째 DDL문에서 not null 조건을 뺀 채 생성한다면 어떻게 될까?

OWNER   CONS_NAME CONS_TYPE TAB_NAME COL_NAME SEARCH_CONDITION

궁금증 2) PRIMARY KEY  컬럼에 Null을 명시해도 오류가 나지 않는가? 테이블이 생성되는가? --> YES

결과적으로 위와 같이 생성해도 아~무 문제도 없다. 언뜻 생각하면 헷갈릴 수 있지만 각 컬럼값에 대해 NULL이 default 값이란걸 생각해보면 당연한 결과이다. 즉 위의 쿼리는 아래의 쿼리와 동일하다.

CREATE TABLE TEMP2 (

 EMP_ID      NUMBER PRIMARY KEY,

 EMP_NAME    VARCHAR2(10) NOT NULL,

 BIRTH_DATE  DATE,

 DEPT_CODE   VARCHAR2(06) NOT NULL,

 EMP_TYPE    VARCHAR2(04),

 USE_YN      VARCHAR2(01) NOT NULL,

 TEL         VARCHAR2(15),

 HOBBY       VARCHAR2(30),

 SALARY      NUMBER,

 LEV         VARCHAR2(04)

);

그리고 당연하게도 PK가 해당 컬럼에 있기 때문에, 테이블 생성 시 NULL을 명시했다고 해서 해당 컬럼이 NULL인 레코드가 테이블에 insert되는 경우는 발생하지 않기 때문에 안심하시라.

궁금증 3)  NOT NULL(NULL), PRIMARY KEY 구문의 순서가 바뀌어도 되는가? --> YES

실험으로 확인했으나, 간단한 부분이니 따로 결과를 올리지는 않았다.

-- 두번째 의문 : DROP TABLE 구문

DROP TABLE구문은 다음과 같다.

DROP TABLE schema_name.table_name [cascade constraints] [purge];

궁금증 4) cascade constraints 구문

일단 헷갈리는 부분인 Foreign Key에 대해 정리하고 넘어가자. Foreign Key 생성 예제는 다음과 같다.

ALTER TABLE employees

ADD CONSTRAINTS fk_deptno FOREIGN KEY (department_id)

REFERENCES departments (department_id);

여기서 자식 테이블은 employees, 부모 테이블은 departments가 된다. 즉, Foreign Key constraint(이하 Foreign Key)는 자식테이블에 생성되는 것이다. 또한 Foreign Key는 referential integrity constraint(참조 무결성 제약조건)과 같은 말이다.

도표로 표현하면 다음과 같다.

자식테이블                                     부모테이블

--------                                     ---------

Foreign Key  --> refers to (참조)  -->   Primary Key or Unique Key

                                           *referential integrity relationship (참조 무결성 관계) 

다시 DROP TABLE 구문으로 돌아가서, cascade constraints구는 만약 drop될 테이블이 부모테이블로서 참조되고 있다면, 자식테이블의 참조하는 Foreign Key 제약조건을 함께 삭제하라는 명령이다. 왜냐하면 자식 테이블의 외래키에 의해 참조되고 있는 테이블은 Drop 될 수 없기 때문이다.

결국 drop할 테이블이 부모 테이블이 아니라면 cascade constraints 구문은 불필요하다.

사족) Foreign Key 제약조건과 관련하여 끊임없이 헷갈리는 이유는 짧은 경험 때문이기도 하지만, 자식테이블에 FK를 생성할 때 부모테이블에 대응되는 무언가(?)가 없기 때문이지 않나... 라는 생각이 든다. 따라서 "자식테이블에 FK를 생성하면, 부모 테이블엔 referential integrity relationship(참조 무결성 관계)가 생긴다." 정도로 정리를 해두려고 한다. 물론 엄밀히 따지면 부-자 테이블 간에 해당 관계가 성립되는 것이겠지만...

궁금증 5) Purge 구문과 Constraints

아시다시피 purge 구문은 oracle 10g의 신 기능인 recycle bin과 관련된 구문이다. 즉 테이블을 drop 했을 때 purge하기 전까지는 해당 테이블은 recycle bin에 place되어 복구가 가능하다. 그런데 constraints와 관련하여..

일단 테이블 드랍

drop table temp2;

그리고 다음 구문으로 조회를 한다.

select a.owner, a.constraint_name, a.constraint_type, a.table_name, b.column_name, a.search_condition from user_constraints a, user_cons_columns b

where a.owner=b.owner

and a.constraint_name=b.constraint_name

and a.table_name=b.table_name

and a.table_name like 'BIN%';

OWNER   CONS_NAME CONS_TYPE TAB_NAME COL_NAME SEARCH_CONDITION

PRAC01 BIN$IFoPPCQFRgiZ37xFqNreYA==$0 C BIN$rcwgjLNxTnyUVLV7CgSwgw==$0 EMP_ID "EMP_ID" IS NOT NULL

PRAC01 BIN$VQtSPYN1SoWRJruYXqAABg==$0 C BIN$rcwgjLNxTnyUVLV7CgSwgw==$0 EMP_NAME "EMP_NAME" IS NOT NULL

PRAC01 BIN$za5xbIlVT9u3ju49LJEoDA==$0 C BIN$rcwgjLNxTnyUVLV7CgSwgw==$0 DEPT_CODE "DEPT_CODE" IS NOT NULL

PRAC01 BIN$JRARQl33RA2Yup/70nI8eQ==$0 C BIN$rcwgjLNxTnyUVLV7CgSwgw==$0 USE_YN "USE_YN" IS NOT NULL

PRAC01 BIN$33sqjnJjRJqWelc3yo2tkg==$0 P BIN$rcwgjLNxTnyUVLV7CgSwgw==$0 EMP_ID

다음과 같이 constraints도 완전히 삭제 되지 않고 replace 되었음을 확인할 수 있다.

그리고 recycle bin을 purge 한다.

purge recyclebin;

다시 조회하면 recycle bin에 replaced되었던 constraints도 삭제 되었음을 확인할 수 있다.

만약 해당 테이블을 복원할 필요가 없거나, 백업이 잘 되어 있어서 recycle bin 보관이 필요치 않은 경우라면, 다음 구문으로 깔끔하게 constraints 까지 정리하는 편이 좋겠다.

drop table temp2 purge;