[Oracle DB] DDL (DATE DEFINITION LANGUAGE)

1. DDL (DATE DEFINITION LANGUAGE)

1. DDL (DATE DEFINITION LANGUAGE)

• DDL (DATE DEFINITION LANGUAGE) : 데이터 정의어
• 데이터베이스의 객체(테이블, 뷰, 시퀀스, 인덱스, 동의어, 사용자 등)을 관리하기 위한 SQL 명령

2. 식별자 작성 규칙

• 테이블명, 컬럼명, 별칭, 라벨명 등
• 영문자로 시작되며 1~30 범위의 문자들로만 구성
• A~Z, 0~9,_,$,# 문자들을 조합하여 작성 (대소문자 미구분, 스네이크표기법 권장)
• 영문자 외 다른 문자 사용 가능하나 미권장
• 키워드로 식별자를 선언할 경우 에러 발생 (" "안에 표현하면 가능하지만 비권장)

---

2. 테이블 (TABLE)

1. 테이블 생성

• 테이블 생성 : 테이블 속성(ATTRIBUTE)의 집합

CREATE TABLE 테이블명(칼럼명 자료형[(크기)] [DEFALUT 기본값] [컬럼제약조건],
	칼럼명 자료형[(크기)] [DEFALUT 기본값] [컬럼제약조건], ..., 
    	[테이블제약조건])

• 테이블 생성시 컬럼 기본값을 설정하지 않은 경우 자동으로 NULL이 기본값으로 자동 저장

2. 자료형 (DATATYPE)

• 자료형 (DATATYPE) : 컬럼에 저장 가능한 값의 형태를 표현하기 위한 키워드
• 숫자형 : NUMBER[(전체 자릿수, 소수점 자릿수)]
• 문자형 : CHAR(크기) (크기 : 1~2000 BYTE) >> 고정길이
              VARCHAR2(크기) (크기 : 1~4000 BYTE) >> 가변길이
              LONG (최대 2GBYTE) >> 가변길이 (테이블 하나의 컬럼에만             설정 가능하며 정렬 불가능)
              CLOB (최대 4GBYTE) >> 가변길이 (텍스트 파일을 저장하기             위한 자료형)
              BLOB (최대 4GBYTE) >> 가변길이 (이진 파일을 저장하기 위한 료형)
• 날짜형 : DATE (날짜와 시간)
             TIMESTAMP (MS 단위 시간)

3. DICTIONARY

• DICTIONARY : 시스템 정보를 제공하기 위한 가상의 테이블 (뷰)
• USER_OBJECTS : 현재 접속 사용자의 스키마로 전근 가능한 객체 정보를 제공하는 DICTIONARY
• USER_TABLES : 현재 접속 사용자의 스키마로 접근 가능한 테이블의 정보를 제공하는 DICTIONARY
  => 동의어로 TAPS 제공
• USER_TAP_COLUMNS : 테이블의 컬럼 정보를 제공하는 DICTIONARY
• USER_CONSTRAINTS : 테이블에 설정된 제약조건을 제공하는 DICTIONARY

4. 제약조건 (CONSTRAINT)

• 제약조건 (CONSTRAINT): 컬럼에 비정상적인 값이 저장되는 것을 방지하기 위한 기능
• 제약조건을 설정할 경우 제약조건을 효율적으로 관리하기 위해 제약조건의 이름을 지정하는 것을 권장

컬럼명 자료형(크기) CONSTRAINT 제약조건명 제약조건

• 컬럼 수준의 제약조건 : 테이블의 속성 선언시 컬럼에 제약조건 설정
• 테이블 수준의 제약조건 : 테이블 선언시 테이블의 특정 칼럼에 제약조건을 설정
• CONSTRAINT_NAME : 제약조건을 구분하기 위한 이름 (고유값), 제약조건의 이름을 지정하지 않으면 자동으로 SYS_XXXXXX으로 설정
• CONSTRAINT_TYPE : C (CHECK), U (UNIQUE), P (PRIMARY), R (REFERENCE)
• SEARCH_CONDITION : CHECK 제약조건으로 설정된 조건식

1. CHECK

• CHECK : 컬럼값으로 저장 가능한 조건을 제공하여 조건이 참인 경우에만 컬럼값으로 저장되도록 컬럼 수준의 제약조건 또는 테이블 수준 제약조건으로 설정가능

2. UNIQUE

• UNIQUE : 중복 컬럼값 저장을 방지하기 위한 제약조건
• 테이블의 컬럼에 다수 설정이 가능하며 NULL 허용

3. PRIMARY KEY (PK)

• PRIMARY KEY (PK) : 중복 컬럼값 저장을 방지하기 위한 제약조건
• 컬럼 수준의 제약조건 또는 테이블 수준의 제약조건 설정 가능
• 테이블에 한번만 설정 가능하며 NULL을 미허용
• 테이블에 한번만 설정 가능하므로 제약조건의 이름을 지정 생략 가능하지만 테이블의 관계를 구체화하기 위해 반드시 설정

4. FOREIGN KEY

• FOREIGN KEY : 부모 테이블에 저장된 행의 컬럼값을 참조하여 자식 테이블의 컬럼에 비정상적인 값이 저장되는 것을 방지하는 제약조건
• 테이블 수준의 제약조건만 설정 가능
• 부모 테이블의 PRIMARY KEY 제약조건이 설정된 컬럼을 참조하여 자식 테이블의 컬럼에 FOREIGN KEY 제약조건 설정
• 테이블의 관계를 구현하기 위한 제약조건
• FOREIGN KEY 제약조건 설정시 ON DELETE CASCADE 또는 ON DELETE SET NULL 기능 추가
• ON DELETE CASCADE : 부모 테이블의 행을 삭제할 경우 자식 테이블의 참조 컬럼값이 저장된 행 삭제 처리
• ON DELETE SET NULL : 부모 테이블의 행을 삭제할 경우 자식 테이블의 참조 컬럼값을 NULL로 변경하는 기능 제공

5. 테이블 삭제

• 테이블 삭제 : 테이블에 저장된 모든 행 삭제

DROP TABLE 테이블명

• TNAME 컬럼에 BIN으로 시작되는 테이블은 오라클 휴지통에 존재하는 삭제 테이블
• 오라클은 테이블을 삭제할 경우 테이블을 휴지통으로 이동하여 삭제 처리 (삭제 테이블 복구 가능)
• SHOW RECYCLEBIN : 오라클 휴지통에 존재하는 객체 목록 확인
• 오라클 휴지통에는 테이블뿐만 아니라 테이블과 종속관계에 있는 인덱스 객체도 같이 존재

• 오라클 휴지통에 존재하는 삭제 테이블 복구

FLASHBACK TABLE 테이블명 TO BEFORE DROP

• 오라클 휴지통의 테이블 삭제

PURGE TABLE 테이블명

• 오라클 휴지통의 모든테이블 삭제

PURGE RECYCLEBIN

• 오라클 휴지통을 사용하지 않고 물리적 삭제

DROP TABLE 테이블명 PURGE

6. TRUNCATE, RENAME, ALTER

• 테이블을 생성 직후의 상태로 초기화 처리하는 명령 (테이블에 저장된 모든 행 삭제 처리)

TRUNCATE TABLE 테이블명

• 테이블의 이름 변경

RENAME 기존테이블명 TO 변경테이블명

• 테이블의 속성 및 제약조건 변경 (변경옵션에 의해 테이블 속성에 대한 추가,삭제,변경 및 제약조건에 대한 추가,삭제 가능)

ALTER TABLE 테이블명 변경옵션

• 테이블의 속성 추가 (컬럼 기본값 및 컬럼 수준의 제약조건 설정 가능)

ALTER TABLE 테이블명 ADD(컬럼명 자료형[(크기)] [DEFAULT 기본값] [제약조건])

• 테이블 속성의 컬럼 자료형 또는 크기 변경 (컬럼 기본값 및 컬럼 수준의 제약조건 설정 가능)

ALTER TABLE 테이블명 MODIFY(컬럼명 자료형[(크기)] [DEFAULT 기본값] [제약조건])

• 테이블 속성의 컬럼명 변경

ALTER TABLE 테이블명 RENAME COLUMN 기존 컬럼명 TO 변경 컬럼명

• 테이블 속성 삭제 (테이블 속성에 저장된 컬럼값 삭제)

ALTER TABLE 테이블명 DROP COLUMN 컬럼명

• 테이블 수준의 제약조건은 ADD 옵션을 사용하여 추가 가능

ALTER TABLE 테이블명 ADD [CONSTRAINT 제약조건명] 제약조건

• 제약조건 삭제

ALTER TABLE 테이블명 DROP [PRIMARY KEY | CONSTRAINT 제약조건명]

---

3. 뷰 (VIEW)

1. 뷰 (VIEW)

• 뷰 (VIEW) : 테이블을 기반으로 만들어지는 가상의 테이블
• 단순뷰와 복합뷰로 구분
• 뷰는 테이블의 행검색 또는 테이블의 권한 설정을 간편하게 제공하기 위해 생성

2. 단순뷰

• 단순뷰 : 하나의 테이블을 기반으로 생성되는 뷰
• 뷰를 이용한 테이블의 검색뿐만 아니라 테이블의 행 삽입, 삭제, 변경 가능
• 단순뷰 생성시 그룹함수 또는 DISTINCT 키워드를 사용한 경우 검색만 가능
• 행에 대한 삽입, 삭제, 변경 가능

3. 복합뷰

• 복합뷰 : 여러개의 테이블을 기반으로 생성된 뷰
• 테이블의 행를 결합하여 생성된 뷰
• 검색만 가능

4. 뷰 생성

• 서브쿼리 사용

CREATE [ON REPLACE] VIEW [(FORCE|NOFORCE)] VIEW 뷰이름 [(컬럼명, 컬럼명, ...)]
	AS SELECT 검색대상, 검색대상, ... FROM 테이블명 [WHERE 조건식]
    	[WITH CHECK OPTION] [WITH READ ONLY]

• CREATE ON REPLACE : 동일한 이름의 뷰가 있는 경우 기존뷰를 삭제하고 새로운 뷰 생성
• FORCE : 서브쿼리의 검색결과가 없어도 강제로 뷰를 생성하기 위한 기능 제공
• WITH CHECK OPTION : 뷰를 생성한 서브쿼리의 조건식에서 사용된 컬럼값을 변경하지 못하도록 설정하는 기능 제공
• WITH READ ONLY : 검색만 가능하도록 설정하는 기능 제공
• USER_VIEW : 뷰 정보를 제공하는 딕셔너리

5. 뷰 삭제

DROP VIEW 뷰이름

---

3. 시퀀스 (SEQUENCE)

1. 시퀀스 (SEQUENCE)

• 시퀀스 (SEQUENCE) : 숫자값(정수값)을 저장하여 자동 증가되는 값을 제공하는 객체

CREATE SEQUENCE 시퀀스명 [START WITH 초기값] [INCREMENT BY 증가값]
	[MAXVALUE 최대값] [MINVALUE 최소값] [CYCLE] [CACHE 개수]

• START WITH 초기값 : 시퀀스에 저장된 초기값 설정 (생략 : NULL)
• INCREMENT BY 증가값 : 자동 증가되는 숫자값 설정 (생략 : 1)
• MAXVALUE 최대값 : 시퀀스에 저장된 저장 가능한 최대값 설정 (생략 : 오라클에서 숫자값으로 표현 가능한 최대값)
• MINVALUE 최소값 : 시퀀스에 저장 가능한 최소값 설정 (생략 : 1)
• CYCLE : 시퀀스에 저장된 값이 최대값을 초과할 경우 최소값부터 다시 제공되도록 반복하는 기능을 제공
• CACHE 개수 : 임의의 저장공간에 자동 증가값을 미리 생성하여 제공할 수 있는 개수 설정 (생략 : 20)
• USER_SEQUENCE : 시퀀스 정보를 제공하는 딕셔너리

2. 시퀀스 사용

• 시퀀스에 저장된 숫자값 확인

시퀀스명.CURRVAL

• 시퀀스에 저장된 숫자값을 이용하여 증가된 값을 제공하는 방법

시퀀스명.NEXTVAL

• 시퀀스 변경

ALTER SEQUENCE 시퀀스명 [MAXVALUE | MINVALUE | INCREMENT BY] 변경값

• 시퀀스 삭제

DROP SEQUENCE 시퀀스명