ICT블로그

뇌를 자극하는 오라클 프로그래밍(홍형경) 392p

<1. 서브쿼리 사용 시>

select emp.first_name||' '||emp.last_name as EMP_NAMES , emp.department_id,

          (select dep.department_name

              from departments dep

             where emp.department_id=dep.department_id) dep_name

             from employees emp;

<2. 단순 조인 사용 시>

select first_name||' '||last_name as EMP_NAMES , emp.department_id, dep.department_name

from employees emp, departments dep

where emp.department_id=dep.department_id(+);

서브쿼리를 사용한 1과 2는 같은 결과를 산출하지만, 성능면에서는 OUTER JOIN을 사용하지 않아도 되는 1번 문장이 더 나을 수있다. 만약 emp.department_id 값이 null 이 아닌 컬럼이 있을 경우, 아래 쿼리처럼 단순 EQUI JOIN을 사용해도 무방하다.

<2-1. 단순 조인 사용시 - EQUI JOIN>

select first_name||' '||last_name as EMP_NAMES , emp.department_id, dep.department_name

from employees emp JOIN departments dep

on emp.department_id = dep.department_id;

결국 설계 단계에서 테이블이 어떻게 정의될지와 사용될 쿼리에 대한 계획이 있어야 불필요한 성능 저하를 막을 수 있다.

-- OUTER JOIN (오라클)

select a.emp_id, b.emp_id, b.comm, b.work_year

from temp a, tcom b

where b.work_year(+) = '2001'

and b.emp_id(+) = a.emp_id;

-- OUTER JOIN (ANSI 표준)

select a.emp_id, b.emp_id, b.comm

from temp a LEFT JOIN tcom b

ON a.emp_id = b.emp_id

and b.work_year = '2001';

-- EQUI JOIN (오라클)

select a.emp_type 구분, a.lev 직급, a.emp_id 사번, a.emp_name 성명, substr(a.emp_name,2,3) 이름, a.salary 연봉, b.from_sal 연봉하한, b.to_sal 연봉상한

from temp a, emp_level b

where a.lev = b.lev;

-- EQUI JOIN (ANSI 표준)

select a.emp_type 구분, a.lev 직급, a.emp_id 사번, a.emp_name 성명, substr(a.emp_name,2,3) 이름, a.salary 연봉, b.from_sal 연봉하한, b.to_sal 연봉상한

from temp a JOIN emp_level b

ON a.lev = b.lev;

-- OUTER JOIN (오라클)

select a.emp_type 구분, a.lev 직급, a.emp_id 사번, a.emp_name 성명, substr(a.emp_name,2,3) 이름, a.salary 연봉, b.from_sal 연봉하한, b.to_sal 연봉상한

from temp a, emp_level b

where a.lev = b.lev(+);

-- LEFT OUTER JOIN (ANSI 표준)

select a.emp_type 구분, a.lev 직급, a.emp_id 사번, a.emp_name 성명, substr(a.emp_name,2,3) 이름, a.salary 연봉, b.from_sal 연봉하한, b.to_sal 연봉상한

from temp a LEFT OUTER JOIN emp_level b

ON a.lev = b.lev;

-- JOINING TABLE ITSELT (SELF JOIN)

select * from tdept;

select a.dept_code 부서코드, a.dept_name 부서명, a.parent_dept 상위부서코드, b.dept_name 상위부서명

from tdept a join tdept b

on a.parent_dept = b.dept_code;

select a.dept_code 부서코드, a.dept_name 부서명, a.parent_dept 상위부서코드, b.dept_name 상위부서명

from tdept a, tdept b

where a.parent_dept = b.dept_code;

select a.dept_code 부서코드, a.dept_name 부서명, a.parent_dept 상위부서코드, b.dept_name 상위부서명

from tdept a, tdept b

where a.parent_dept = b.dept_code

and b.dept_name = '영업';

select a.dept_code 부서코드, a.dept_name 부서명, a.parent_dept 상위부서코드, b.dept_name 상위부서명

from tdept a join tdept b

on a.parent_dept = b.dept_code

where b.dept_name = '영업';

select * from temp;

-- non equi join (Q/A : 03-8번)

select emp_id, emp_name, birth_date from temp;

-- 직원이 자신보다 생일이 빠른 사람과 매칭했을 때 나타나는 경우의 수 (SELF JOIN, NON-EQUI JOIN)

select a.emp_id, a.emp_name, a.birth_date, b.emp_id, b.emp_name, b.birth_date

from temp a, temp b

where a.birth_date > b.birth_date(+);

-- 20 + ... + 1 = 210 개

-- 19 + ... + 1 = 190 개

-- 직원이 자신보다 생일이 빠른 사람과 매칭했을 때 나타나는 경우의 수2 

-- 단순히 숫자만 COUNT 시 (SELF JOIN, NON-EQUI JOIN)

-- 1. GROUP BY 를 쓴다

select a.emp_id, a.emp_name, a.birth_date, count(*)

from temp a, temp b

where a.birth_date >= b.birth_date

group by a.emp_id, a.emp_name, a.birth_date

order by count(*);

-- 2. 조금 더 조건을 명확히 나타내고 싶다면, >= 대신 OUTER JOIN을 함께 쓰고, null 이 나오는 컬럼을 count한다.

select a.emp_id, a.emp_name, a.birth_date, count(b.emp_id)

from temp a, temp b

where a.birth_date > b.birth_date(+)

group by a.emp_id, a.emp_name, a.birth_date

order by count(b.emp_id);

-- 3. 2번을 ANSI 표준 SQL로 바꾸기

select a.emp_id, a.emp_name, a.birth_date, count(b.emp_id)

from temp a LEFT OUTER JOIN temp b

  ON a.birth_date > b.birth_date

group by a.emp_id, a.emp_name, a.birth_date

order by count(b.emp_id);

-- Q/A : 03-9 #상위부서 찾기

select * from temp;

select * from tdept;

-- 1단계

select a.emp_id 사번, a.emp_name 성명, b.dept_code 부서코드, b.dept_name 부서명

from temp a, tdept b

where a.dept_code = b.dept_code;

select * from tdept;

select * from temp;

-- 2단계

select a.emp_id 사번, a.emp_name 성명, b.dept_code 부서코드, b.dept_name 부서명, c.dept_code 상위부서코드, c.dept_name 상위부서명, c.boss_id 상위부서장코드, d.emp_name 상위부서장명

from temp a, tdept b, tdept c, temp d

where b.parent_dept = 'CA0001'

  and a.dept_code = b.dept_code

  and b.parent_dept = c.dept_code

  and c.boss_id = d.emp_id(+);

-- 2단계 (ANSI 표준--> 다중 테이블 조인 연습)

select a.emp_id 사번, a.emp_name 성명, b.dept_code 부서코드, b.dept_name 부서명, c.dept_code 상위부서코드, c.dept_name 상위부서명, c.boss_id 상위부서장코드, d.emp_name 상위부서장명

from temp a JOIN tdept b

  ON (a.dept_code = b.dept_code and b.parent_dept = 'CA0001') 

     JOIN  tdept c

  ON (b.parent_dept = c.dept_code)

     LEFT OUTER JOIN temp d

  ON c.boss_id = d.emp_id;

     --WHERE b.parent_dept = 'CA0001';

     --> 참고: ANSI 표준 SQL에서 where 절로 필터링 하고 싶을땐, 맨 밑에 where 절을 쓰거나 혹은 JOIN 시 ON 구문안에 and 절로 표시한다. 단 ON 절에 넣는 것은 데이터 검증 조건을 추가하는 것이며, 단순 검색 조건 추가일 경우 WHERE절을 쓸 것을 권고한다. (SQL 전문가 가이드 316p 참조)

-- 참조 부서장을 맡고 있는 부서원 및 해당 부서의 상위부서코드/명/부서장코드/부서장명   

select a.emp_id 사번, a.emp_name 부서장명, b.dept_name 부서명, b.parent_dept 상위부서코드, c.dept_name 상위부서명, d.emp_id 상위부서장코드, d.emp_name 상위부서장명 

from temp a, tdept b, tdept c, temp d

where a.emp_id = b.boss_id

  and b.parent_dept=c.dept_code

  and c.boss_id = d.emp_id(+);

-- 참고 자료

select * from temp;

19970101 김길동 74/01/25 AA0001 정규 Y 등산 100000000 부장

19960101 홍길동 73/03/22 AB0001 정규 Y 낚시 72000000 과장

19970201 박문수 75/04/15 AC0001 정규 Y 바둑 50000000 과장

19930331 정도령 76/05/25 BA0001 정규 Y 노래 70000000 차장

19950303 이순신 73/06/15 BB0001 정규 Y 56000000 대리

19966102 지문덕 72/07/05 BC0001 정규 Y 45000000 과장

19930402 강감찬 72/08/15 CA0001 정규 Y 64000000 차장

19960303 설까치 71/09/25 CB0001 정규 Y 35000000 사원

19970112 연흥부 76/11/05 CC0001 정규 Y 45000000 대리

19960212 배뱅이 72/12/15 CD0001 정규 Y 39000000 과장

*빈 값은 모두 NULL

-- 그룹함수          

-- (1)COUNT           

select count(*), count(8) from temp; -- 10, 10 

select count(*), count(8) from dual; -- 1,1 

--> *나 의미없는 값을 넣으면 row수만큼 읽어온다

select * from temp order by lev, hobby;

select count(emp_id), count(tel), count(hobby) from temp; -- 10, 0, 4

--> count() 괄호 안에 컬럼을 넣으면, 해당 컬럼이 null이 아닌 레코드의 개수를 읽어온다

select lev, 

count(*),  

count(hobby)

from temp

group by lev

order by lev;

-- 과장 4 2

-- 대리 2 0

-- 부장 1 1

-- 사원 1 0

-- 차장 2 1

--참고. GROUP 함수의 특징

select count(*) from temp

where hobby = '공부';

-- 또는

select count(hobby) from temp

where hobby = '공부';

-- count(*)

----------

-- 0

-- 1 row selected. --> 0 rows selected 가 아님. 해당 1개의 그룹(*group by 절이 없으므로 레코드 전체가 하나의 그룹)에 대해 1건의 결과를 출력하며, 그 그룹 중 '공부'가 취미인 레코드가 0개라는 1건의 결과를 출력함

select count(*)

from temp

where lev = '과장'

and salary >= 40000000;

select count(distinct lev) from temp;

-- min과 max

select min(emp_id) from temp;

select lev, min(emp_id)

from temp

group by lev;

-- 연습문제 그룹함수 연습

TEMP에서 직급별로 최소연봉을 가진 직원의 사번과 연봉을 읽어오자. (단,SUBQUERY를 사용하면 안 된다.)

select to_char(salary) from temp;

select lpad(to_char(salary),10,'0') from temp;

select min(lpad(to_char(salary),10,'0')) from temp;

select substr(min(lpad(to_char(salary),10,'0')||emp_id),11,8) EMP_ID from temp;

select lev, substr(min(lpad(to_char(salary),10,'0')||emp_id),11,8) EMP_ID, min(salary) SAL

from temp

group by lev;

-- SUM, AVG

-- 주의 : number type 컬럼만 인수로 받을 수 있다

-- 따라하기

select lev, count(*), sum(salary), avg(salary), sum(salary)/count(*) AVG2

from temp

group by lev;

-- 주의! 평균을 구할때는 NULL 값을 유의해야 한다.

--실험

select avg(salary) from temp; --57600000

update temp set salary = 0 where emp_name = '홍길동';

select count(salary), avg(salary), sum(salary)/count(*) AVG2 from temp; -- 10, 50400000, 50400000

update temp set salary = null where emp_name = '홍길동';

select count(*), count(salary), avg(salary), sum(salary), sum(salary)/count(*) AVG2 from temp; -- 10, 9, 56000000, 504000000, 50400000

--> 결국 avg(salary)= sum(salary)/count(salary) 로 구해진다는 것 (홍길동을 제외한 나머지 9명의 연봉 평균이 구해졌다.)

--> 전체 10명의 평균을 구하기 위해서는 nvl 함수를 사용해줘야 한다.

select avg(nvl(salary,0)) from temp; --50400000

rollback;

-- 데이터형 변환함수

-- 기본 사항              

--          날짜 ---> 문자 ---> 숫자

--             TO_CHAR   TO_NUMBER

--          날짜 <--- 문자 <--- 숫자

--             TO_DATE   TO_CHAR

--          날짜  --/--> 숫자 (불가능)

--          날짜 <--/--  숫자 (불가능)

-- TO_CHAR(1)

-- 날짜를 문자로

-- 기본예제

select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') from dual; --2016-01-28 21:37:07

select to_char(sysdate,'YyyY') from dual; --2016

select to_char(sysdate,'YYY') from dual; --016

select to_char(sysdate,'YY') from dual; --16

select to_char(sysdate,'Y') from dual; --6

select to_char(sysdate,'SYEAR') from dual; -- TWENTY SIXTEEN

select to_char(sysdate,'YEAR') from dual; --TWENTY SIXTEEN

select to_char(sysdate,'Q') from dual; --1

select to_char(sysdate,'mm') from dual; --01

select to_char(sysdate,'month') from dual; --1월(January)

select to_char(sysdate,'mon') from dual; --1월(JAN)

select to_char(sysdate,'D') from dual; --5

select to_char(sysdate,'DD') from dual; --28

select to_char(sysdate,'DDD') from dual; --028

select to_char(sysdate,'DAY') from dual; --목요일 (Thursday)

select to_char(sysdate,'DY') from dual; --목 (THU)

select to_char(sysdate,'AM') from dual; --오후

select to_char(sysdate,'PM') from dual; --오후

select to_char(sysdate,'A.M.') from dual; --오후

select to_char(sysdate,'P.M.') from dual; --오후

select to_char(sysdate,'HH') from dual; --09

select to_char(sysdate,'HH24') from dual; --21

select to_char(sysdate,'MI') from dual; --37

select to_char(sysdate,'SS') from dual; --07

--응용

select to_char(sysdate,'DD') from dual; --28 --> 원본

select to_char(sysdate,'DDTH') from dual; --28TH (서수)

select to_char(sysdate,'DDSP') from dual; --TWENTY-EIGHT (숫자를 철자로)

select to_char(sysdate,'DDSPTH') from dual; --TWENTY-EIGHTH (서수를 철자로)

select to_char(sysdate,'DDTHSP') from dual; --TWENTY-EIGHTH (서수를 철자로2)

--따라하기

select to_char(sysdate,'yy/mm/dd') date1, --16/01/28

to_char(sysdate,'yyyy.mm.dd') date2, --2016.01.28

to_char(sysdate,'month dd.yyyy') date3, --1월 28.2016

to_char(sysdate,'DY DD MON YY') date4, --목 28 1월 16

to_char(sysdate,'Day Mon DD') date5 --목요일 1월 28

from dual;

-- 연습문제

select emp_name, emp_id, birth_date from temp; --> 생일과 요일 출력변환

select emp_name, emp_id, to_char(birth_date,'yyyy "년" month dd"일" Day') from temp; --> 정답

-- TO_CHAR(2)

-- 숫자를 문자로

select to_char(192939,'999999') from dual; --192939

select to_char(192939,'00000000') from dual; --00192939

select to_char(192939,'999,999') from dual; --192,939

select to_char(192.939,'999.99') from dual; --192.94

select to_char(192939,'$999999') from dual; --$192939

select to_char(192939,'L999,999') from dual; --         ￦192,939

select to_char(-192939,'999999MI') from dual; --192939- (마이너스표시)

select to_char(192939,'999999EEEE') from dual;   2E+05 (과학적표기)

--따라하기

select to_char(1234,'09,999') number1, --01,234

       to_char(1234.56,'99,999.99') number2, --1,234.56

       to_char(1234,'$99,999') number3, --$1,234

       to_char(1234,'L99,999') number4, --         ￦1,234

       to_char(-1234,'99,999MI') number5 --1,234-

       -- to_char(-1234,'99,999EEEE')

       from dual;

--연습문제

select salary,

       to_char(salary, '099,999,999') SAL1,

       to_char(salary, '999,999,999.00') SAL2, 

       to_char(salary, '$999,999,999') SAL3,

       trim(to_char(salary, 'L999,999,999')) SAL4,

       to_char(salary, '999,999,999') SAL5 ,

       to_char(salary, '99EEEE') SAL6

       from temp;

--TO_NUMBER

--문자를 숫자로(O)

--날짜를 숫자로(X)

--따라하기

select to_number('123456') TONUM1, --123456

       to_number('123,456','999999') TONUM2 --123456

       from dual;

--TO_DATE

--문자를 날짜로(O)

--숫자를 날짜로(X)

select to_date('1997-12-31 13:33:44','yyyy-mm-dd hh24:mi:ss')

from dual; -- 97/12/31

select round(sysdate - to_date('19700101','yyyy-mm-dd')) "며칠"

from dual; --16829

--1970년 1월 1일 00시00분00초에서 946075441초가 지나면??

select to_date('19700101000000','yyyymmddhh24miss') +

           946075441 / (24*60*60)

           from dual; -- 99/12/24

-- 1. date type + date type 의 가산은 불가능

select sysdate + birth_date

from temp

where emp_name = '홍길동';

-- 2. date type - date type 의 감산은 가능

select emp_name, sysdate-birth_date from temp

where emp_name = '홍길동';

-- 3. date type에 number type을 더하거나 빼서 날짜 계산이 가능

select emp_name, birth_date, birth_date + 1.5, birth_date - 1.5

from temp

where emp_name = '홍길동';

-- * sysdate는 초단위까지 계산되지만 기본적으로 년/월/일만 출력됨

select sysdate from dual;

-- 4. date type 출력 형식 지정 및 시, 분, 초 계산

select to_char(sysdate,'HH24:MI:SS') AS TIME,

       to_char(sysdate + 14/24, 'HH24:MI:SS') AS "14시간 후",

       to_char(sysdate + 30 / (24*60) , 'HH24:MI:SS') AS "30분후",

       to_char(sysdate + 50 / (24*60*60), 'HH24:MI:SS') AS SEC from dual;

-- 5. 날짜 함수의 활용 (1) ADD_MONTHS

select sysdate,

       add_months(sysdate,1), -- 한달 더하기

       add_months(sysdate,-13) -- 13개월 빼기

from   dual;

-- 6. 날짜 함수의 활용 (1) MONTHS_BETWEEN

select months_between(sysdate,birth_date) MON_TERM

from  temp

where emp_name = '홍길동';

-- 7. 연습문제 : ADD_MONTH 함수의 사용

select emp_name, round(months_between(birth_date,sysdate)) MONTHS,

       to_char(birth_date,'yyyy-mm-dd') BIRTH,

       to_char(add_months(sysdate,months_between(birth_date,sysdate)),'yyyy-mm-dd') TO_YE1,

       to_char(add_months(sysdate,trunc(months_between(birth_date,sysdate))),'yyyy-mm-dd') TO_YE2,

       to_char(add_months(sysdate,round(months_between(birth_date,sysdate))),'yyyy-mm-dd') TO_YE3 from temp;

-- 8. LAST_DAY --> 해당 월의 말일 산출

select emp_name, birth_date, last_day(birth_date)

from temp;

쿼리를 작성할 때 사실 잘 의식하지는 못하는 부분이지만, 오라클이 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;

30분 동안 끙끙거린 문제...

왜 다음 문장이 오류가 발생하는가?

create table scott.emp_list_part (

EMPNO NUMBER(22),

ENAME VARCHAR2(10),

JOB VARCHAR2(9),

MGR NUMBER(22),

HIREDATE DATE(7),

SAL NUMBER(22),

COMM NUMBER(22),

DEPTNO NUMBER(22),

constraint pk_list_empno primary key(empno)

using index tablespace indx

)

partition by list (job)

(

partition job_entry values ('CLERK','ANALYST') tablespace t1,

partition job_middle values ('MANAGER','SALESMAN') tablespace t2,

partition job_high values ('PRESIDENT') tablespace t3);

--> 답은 DATE 때문이다. DATE TYPE 옆에는 LENGTH를 기재하지 않는다.

create table scott.emp_list_part (

EMPNO NUMBER(22),

ENAME VARCHAR2(10),

JOB VARCHAR2(9),

MGR NUMBER(22),

HIREDATE DATE,

SAL NUMBER(22),

COMM NUMBER(22),

DEPTNO NUMBER(22),

constraint pk_list_empno primary key(empno)

using index tablespace indx

)

partition by list (job)

(

partition job_entry values ('CLERK','ANALYST') tablespace t1,

partition job_middle values ('MANAGER','SALESMAN') tablespace t2,

partition job_high values ('PRESIDENT') tablespace t3);

수정완료.

(1)Oracle Database Administrator's Guide

10/11(일) 47P ~ 51P

이렇게 수행 시 에러 발생함.

select a.employee_id, a.first_name||' '||a.last_name names, (select b.department_name

from employees a, departments b

where a.department_id = b.department_id) dep_names

from employees a

where a.department_id = 100;

ORA-01427: single-row subquery returns more than one row

01427. 00000 -  "single-row subquery returns more than one row"

*Cause:    

*Action:

--> 요렇게 고쳐서 수행해야 함

select a.employee_id, a.first_name||' '||a.last_name names, (select b.department_name

from departments b

where a.department_id = b.department_id) dep_names

from employees a

where a.department_id = 100;