[oracle DB] DDL (Data Definition Language)

2. DDL (Data Definition Language : 데이터 정의어)

• scalar type / constraint / sequence

 

 

DDL은 database 내의 객체(table, sequence, etc...) 등을 생성하고 변경하고 삭제하기 위해 사용되는 sql문이다. 종류는 다음의 세 가지가 있다.

create table table명
(column1 data type, column2 data type, ...);

create table todo
(task varchar2(20), success varchar2(3));

create sequence sequence명;

create sequence test_seq;

select test_seq from dual;

1. create : 필요한 객체를 만들고 싶을 때 사용한다. table을 만들고싶다면 create table, sequence를 만들고싶다면 create sequence로 시작한다.
   (table은 표이고, sequence는 글자 그대로 사전적 의미의 순서이다. 숫자를 카운트해준다. 하지만 이것 그대로는 숫자를 출력하지 못하고, select를 통해서 출력해야 한다. 이때 dual이라는 것도 사용하는데, dual은 연습용 durmy table이라고 생각하면 될 것 같다. 행렬을 하나씩 뽑을 때 사용한다.)
   

   desc table명;
   
   desc todo;

   

   
   만든 table의 구조를 보고 싶다면, desc를 사용한다.
   
   

   alter table table명 add(column명 varchar2(10));
   
   alter table table명 modify(column명 varchar2(20));
   
   alter table table명 rename column명 to 바꿀 column명;
   
   alter table table명 drop(column명);

2. alter : 객체를 변경할 때 사용한다.
   add : column을 추가한다.
   modify : column을 수정한다.
   rename : column의 이름을 바꾼다.
   drop : column을 삭제한다.
   

   drop table table명;
   
   drop table todo;​

3. drop : 객체를 삭제할 때 사용한다.

 

scalar type (oracle data type)

1. char(  ) : 고정 길이의 문자. 최대 2000 byte.
2. varchar2(  ) : 가변 길이의 문자. 최대 4000 byte. 괄호 (  ) 안에 들어간 숫자는 들어갈 수 있는 문자의 최대 길이를 의미한다. 만약 20이라면 영문 기준 20자, 한글 기준 10자이다. 사실 powershell 한글 처리가 글자 당 3인데, 실제로 app을 만들어서 넣으면 2만 차지한다.
3. number : 숫자 값을 소수점 아래 38자리부터 위 38자리수를 저장할 수 있다.
   (ex - number(10) : 정수 10자리. / number(10, 2) : 전체 자리수 10자리, 소수점 이하 2자리.
          / number(p, s) : p는 유효숫자 자리수. 1~38 default 38, s는 소수점 이하 자리수. -84~127 default 0
            s에 음수가 들어간다면 소수점 이상으로 넘어감. 소수점 이하 입력한 값보다 더 많이 쓰면 반올림.)
4. date : 날짜(시간) 저장. '199x/xx/xx' 로 구분하며, 날짜 객체는 to_date('199xxxxx', 'YYYYMMDD') 와 같이 작성해서 table에 입력할 수 있다. JDBC에서 getDate() 로 불러올 수도 있지만, 이때 to_char 함수를 이용하여 문자열로 바꾼 다음에 읽어와야 한다. 문자열로 바꾼 다음에 getString()으로 읽어온다.
5. clob : 문자 data를 최대 4GB까지 저장 가능. JDBC에서 읽어올 때, getString() 으로 읽어올 수 없고, getClob() 으로 읽어와야 한다.(10g 버전 이상부터)
6. blob : binary data(2진 data)를 저장할 때 이용한다.

 

constraint (제약 조건)

제약 조건은 table의 column에 원하지 않는 data에 대한 입력, 수정, 삭제를 방지하기 위해 table의 생성 혹은 변경 시 설정하는 조건이다. 이는 저장된 data의 신뢰도를 높이기 위함이라고 한다. 종류는 다음의 5가지이다.

1. not null : data가 null이면 안되는 column에 부여하는 조건으로, column level에서만 부여할 수 있다. 일반적인 제약 조건과는 다르다. 제약 조건 자체도 추가하려고 할 때, 추가의 방식이 아닌 수정의 방식으로 추가할 수 있다.(column level이라는 것은 table level도 있다는 것이다.)
2. unique key (유일 키) : 저장된 data가 중복되지 않고 유일하게 유지되어야 할 때 사용하는 조건이다. null은 허용된다.
3. primary key (대표 키) : not null과 unique key의 조건을 합친 조건으로 null이 아닌 유일한 data이어야 한다. 만약 같은 값을 넣으려고 한다면 '무결정 제약조건 위배' 오류가 뜬다고 했다.
4. check : 조건에 맞는 data만 입력되도록 하는 '조건' 을 부여한다.
5. foreign key (외래 키) : 부모 table의 primary key를 참조하는 column에 붙이는 조건이다.

(ex)

create table dept2
(deptno number(2) constraint dept2_deptno_pk primary key,
dname varchar2(15) default '영업부',
loc char(1) constraint dept2_loc_ck check(loc in('1', '2')));

• column명 datatype constraint constraint명 제약 조건 : 의 순서로 작성한다.
• default : 해당 column에 default로 들어갈 값을 정해준다.
  

  create table dept2
  (deptno number(2) primary key,
  dname varchar2(15) default '영업부',
  loc char(1) check(loc in('1', '2')));

• constraint와 제약 조건명은 생략할 수 있다. 다만 제약 조건명을 적지 않으면 임의로 이름이 붙게 되는데, 혹시라도 이것을 참고하려고 할 때 알아보기 힘들기 때문에 번거로움을 감수한다면 이름을 적어주는 것이 좋을 수 있다. 제약 조건명은 보통 'table명_column명_조건' 의 방식으로 만든다.
  

  create table dept3(
  deptno number(2) primary key,
  dname varchar2(15) not null);
  
  create table dept3(
  deptno number(2),
  dname varchar2(15) not null,
  constraint dept3_deptno_pk primary key(deptno));
   
  create table emp3(
  empno number(4) primary key,
  ename varchar2(15) not null,
  deptno number(2) references dept3(deptno));
  	-> deptno number(2)
  	   constraint emp3_deptno_fk foreign key(deptno) references dept3(deptno)

• table level로 제약 조건을 작성한다면 두 번째 예시처럼 작성한다. 어떤 column에 primary key를 걸어줄 것인지를 소괄호 (  )에 명시해야하며, not null은 table level에서 제약 조건을 줄 수 없다.
• 외래 키를 이용해서 table을 만들기 위해서는 먼저 부모 table이 존재해야 한다. 부모 table의 primary key를 'references' 를 이용해서 가져올 수 있다. foreign key는 생략이 아니라 문법상 column level에 작성하지 않는 것이며, 내용을 풀어서 table level에 작성할 수도 있다. 이렇게 만든 table의 경우 부모 table의 내용을 참조하고 있기 때문에 부모 table을 먼저 삭제하는 것이 불가능하다,
  

  select constraint_name from user_constraint;
  desc user_constraint;
  
  select * from user_constraint where table_name='dept2';
  
  alter table dept2 drop constraint 제약조건명;
  
  alter table dept2 add(constraint 제약조건명 primary key(deptno));
  
  alter table dept3 modify dname constraint dept3_dname_nn not null;

• select문이나 desc로 제약 조건을 검색할 수 있다.
• 제약 조건의 이름을 설정하지 않아서 임의의 이름으로 된 조건을 찾기 위해서는 해당 table 이름을 사용해서 select를 통해 추정할 수 있다.
• 제약 조건은 수정이 불가능하고, 추가 및 삭제만 가능하다.
  

  alter table dept2
  disable constraint ~​

• disable을 통해 제약 조건을 무력화시킬 수도 있다. 이것은 이런저런 충돌 때문에 필요할 수도 있으니 적어둔다. enable과 반대의 동작을 한다고 했다.

 

sequence (시퀀스)

create sequence 시퀀스명
increment by 한번에 증감할 양 (default: +1)
start with 시작값 (default: 0 )
maxvalue (default: 해당 oracle version에서의 최댓값)
minvalue (default: 1)
cycle / nocycle(default) : 최댓값 도달시 반복할 것인지 여부
cache 숫자 / nocache (default: cache 20)

select sequence명.nextval from dual;

select sequence명.currval from dual;

sequence는 글자 그대로 사전적 의미의 순서이다. 숫자를 카운트해준다.

1. increment by : 한 번에 얼마나 증감할 지 결정한다.
2. start with : 어떤 숫자부터 시작할 지 결정한다.
3. maxvalue : sequence의 최댓값을 결정한다.
4. minvalue : sequence의 최솟값을 결정한다.
5. cycle / nocycle : sequence가 최댓값에 도달했을 때, 반복할 것인지 아닌지를 결정한다.
6. cache / nocache : 숫자를 매번 하나하나 만드는 것도 약간 부담되는 작업이기 떄문에 한 번에 미리 20개씩 만들어두고 꺼내서 사용하는 방식을 채택한다. 때문에 만들어둔 sequence가 끝나기 전에 재부팅을 하게 되면 미리 만들어둔 cache는 사라지고 다음 cache를 만들어 그 시작 숫자부터 이어지기 때문에 너무 빈 숫자에 집착하지 않도록 한다. 일반적으로는 미리 만드는 것이 좋지만, 경우에 따라서 nocache를 채택하는 경우도 있다고 한다.

하지만 이것 그대로는 숫자를 출력하지 못하고, select를 통해서 출력해야 한다. 이때 dual이라는 것도 사용하는데, dual은 연습용 durmy table이라고 생각하면 될 것 같다. 행렬을 하나씩 뽑을 때 사용한다. 사용할 수 있는 함수는 다음의 두 종류가 있다.

1. nextval : sequence의 다음 값을 return한다. 이때, sequence의 숫자가 실제로 변동하는 것이니 주의한다.
2. currval : sequence의 현재 값을 return한다.

drop sequence sequence명;

select sequence_name from user_sequences;​

drop으로 sequence를 삭제할 수도 있고, select로 sequence를 조회할 수도 있다.

create sequence 시퀀스명
increment by 한번에 증감할 양 (default: +1)
maxvalue (default: 해당 oracle version에서의 최댓값)
minvalue (default: 1)
cycle / nocycle(default) : 최댓값 도달시 반복할 것인지 여부
cache 숫자 / nocache (default: cache 20)

수업 시간에는 sequence는 한 번 만들면 수정할 수 없기때문에 그대로 써야한다고 했지만, 검색해보니 수정이 가능했다.^[각주:1] create와 다르게 start with 에 해당하는 것은 수정이 불가능했다. 그래서 sequence를 초기화 하는 방법도 가져왔다.^[각주:2]

 

select sequence명.currval from dual;

alter sequence명
increment by -현재 값;

select sequence명.nextval from dual;
select sequence명.currval from dual;

alter sequence명
increment by 1;

방법은 간단하다. sequence의 현재 값을 알아낸 후, 다음 sequence에 현재 값만큼을 뺄 수 있도록 sequence를 수정하고, nextval을 통해 값을 sequence의 값을 0으로 만든 다음 현재 값이 0인지 확인한다. 확인했으면 다시 sequence를 1씩 더하도록 수정하면 된다.

 

사실 필자도 이렇게 번거로울거면 sequence를 삭제하고 다시 만들면 간단한 일이라고 생각했다. 하지만 DB의 object를 함부로 삭제하면 혼선을 줄 수 있다고 한다. sequence 자체에 권한을 줄 수도 있다고 하는데, 이런 상황까지 고려하면 삭제하고 다시 만드는 것은 지양하는 것이 좋겠다.

 

1. [ORACLE]오라클_시퀀스(Sequence) 생성 / 수정 / 삭제 :: 나만의 기록들 (tistory.com) [본문으로]
2. [ORACLE] SEQUENCE 생성, CURRVAL/NEXTVAL, 초기화 (tistory.com) [본문으로]