23일차 - 테이블, 뷰

은채의 성장통·2025년 6월 30일

oracle sql

KCC정보통신

목록 보기

27/30

노션 링크

1. 테이블 생성

기본 테이블 생성

CREATE TABLE dept (
  deptno NUMBER(2),
  dname VARCHAR2(14),
  loc    VARCHAR2(13)
);

서브쿼리 기반 테이블 생성 (CTAS)

CREATE TABLE emp2 AS
SELECT * FROM employees;

→ employees 테이블의 구조와 데이터를 복사해서 emp2 생성.

2. 테이블 구조 변경 (ALTER TABLE)

열 추가

ALTER TABLE dept ADD (job VARCHAR2(20));

열 수정 (길이 변경 등)

ALTER TABLE dept MODIFY (dname VARCHAR2(30));

열 삭제

ALTER TABLE dept DROP COLUMN job;

열 이름 변경

ALTER TABLE dept RENAME COLUMN loc TO location;

열 비활성화 (SET UNUSED)

ALTER TABLE dept SET UNUSED (dname);

→ 논리적으로 열 제거. 복구 불가. 추후 ALTER TABLE dept DROP UNUSED COLUMNS;로 완전 제거 가능.

3. 테이블 삭제 및 데이터 초기화

테이블 완전 삭제

DROP TABLE dept CASCADE CONSTRAINTS;

테이블의 데이터만 삭제 (구조 유지)

TRUNCATE TABLE dept;

→ DELETE와는 다르게 ROLLBACK이 안 되며, 제약조건 검사도 없음. 속도 빠름.

1. 제약조건의 종류 및 정의 방식

제약조건 종류	설명	열 레벨 사용 가능	테이블 레벨 사용 가능
`NOT NULL`	NULL 허용 금지	O	X
`PRIMARY KEY`	중복과 NULL 불가, 테이블당 1개	O	O
`UNIQUE`	중복 불가, NULL은 허용	O	O
`FOREIGN KEY`	다른 테이블의 값을 참조	O	O
`CHECK`	지정한 조건을 만족해야 함	O	O
`DEFAULT`	입력값 없을 시 기본값 지정	O	X

2. 열 레벨 제약조건 예시

CREATE TABLE emp4 (
  empno NUMBER(4) CONSTRAINT emp4_empno_pk PRIMARY KEY,
  ename VARCHAR2(10) NOT NULL,
  sal   NUMBER(7,2) CONSTRAINT emp4_sal_ck CHECK(sal <= 10000),
  depno NUMBER(2) CONSTRAINT emp4_depno_fk REFERENCES departments(department_id)
);

각 열 정의와 동시에 제약조건을 설정
제약조건 이름 지정 가능

3. 테이블 레벨 제약조건 예시

CREATE TABLE emp5 (
  empno NUMBER(4),
  ename VARCHAR2(10) NOT NULL,
  sal   NUMBER(7,2),
  depno NUMBER(2),
  CONSTRAINT emp5_empno_pk PRIMARY KEY(empno),
  CONSTRAINT emp5_sal_ck CHECK(sal <= 10000),
  CONSTRAINT emp5_depno_fk FOREIGN KEY(depno) REFERENCES departments(department_id)
);

NOT NULL만 열 레벨에서 지정
그 외는 CONSTRAINT 제약명 제약조건(대상) 형식으로 정의

4. 제약조건 관리

제약조건 추가

ALTER TABLE emp5 ADD CONSTRAINT emp5_ename_uk UNIQUE (ename);

제약조건 삭제

ALTER TABLE emp5 DROP CONSTRAINT emp5_ename_uk;

NOT NULL 제약 추가

ALTER TABLE emp5 MODIFY (sal NOT NULL);

NOT NULL은 ALTER ... MODIFY로 추가해야 하며, ADD CONSTRAINT 방식은 사용할 수 없음.

5. 제약조건 비활성화 및 활성화

비활성화

ALTER TABLE emp5 DISABLE CONSTRAINT emp5_sal_ck;

종속 제약조건 포함 비활성화 (CASCADE)

ALTER TABLE departments DISABLE CONSTRAINT dept_pk CASCADE;

기본값이 NOVALIDATE 들어있는 값중에서 조건에 위배가 되는것이 없는지 확인하지 않는다.

활성화

ALTER TABLE emp5 ENABLE CONSTRAINT emp5_sal_ck;

기본값이 VALIDATE 들어있는 값중에서 조건에 위배가 되는것이 있는지 확인한다.

VIEW란?

하나 이상의 테이블 또는 다른 뷰를 기반으로 생성되는 논리적 가상 테이블
물리적인 데이터를 저장하지 않고, 쿼리 결과를 테이블처럼 보여줌
복잡한 쿼리를 단순화하거나 보안을 위해 특정 열만 노출할 때 사용
일반 테이블처럼 SELECT로 조회 가능하지만, 상황에 따라 INSERT, UPDATE, DELETE는 제한됨

1. 권한 확인

뷰를 생성하려면 CREATE VIEW 권한이 필요합니다. 현재 사용자에게 부여된 권한 및 역할 확인:

-- 사용자에게 부여된 역할 확인
SELECT * FROM user_role_privs;

-- 사용자에게 부여된 시스템 권한 확인
SELECT * FROM user_sys_privs;

2. 뷰 생성

단순 뷰

CREATE VIEW emp_view_dept60 AS
SELECT employee_id, first_name, last_name, job_id, salary
FROM employees
WHERE department_id = 60;

별칭 및 표현식 포함 뷰

CREATE VIEW emp_view_dept60_salary AS
SELECT
  employee_id,
  first_name || ' ' || last_name AS full_name,
  job_id,
  salary
FROM employees
WHERE department_id = 60;

3. 뷰 조회

뷰는 일반 테이블처럼 조회 가능:

SELECT * FROM emp_view_dept60_salary;

4. 뷰 수정

기존 뷰가 존재하더라도 CREATE OR REPLACE VIEW를 사용하면 수정 가능:

CREATE OR REPLACE VIEW emp_view_dept60_salary AS
SELECT
  employee_id AS empno,
  first_name || ' ' || last_name AS name,
  job_id AS job,
  salary
FROM employees
WHERE department_id = 60;

5. 복합 뷰 생성 (조인 포함)

CREATE VIEW emp_detail_view AS
SELECT
  e.employee_id AS id,
  e.first_name || ' ' || e.last_name AS name,
  d.department_name AS department,
  j.job_title AS job
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
JOIN jobs j ON e.job_id = j.job_id;

※ departmnet_id → department_id 오타 수정됨

6. 뷰 삭제

DROP VIEW emp_detail_view;

뷰를 삭제해도 기반 테이블의 데이터는 영향을 받지 않음

7. 뷰 관련 제약사항

INSERT, UPDATE, DELETE 가능 여부는 뷰의 정의에 따라 다름
- 단순 뷰는 가능
- 조인, 그룹 함수, 서브쿼리, 집계 등 포함 시 불가
필요시 WITH CHECK OPTION, WITH READ ONLY 옵션 사용 가능

예시: 읽기 전용 뷰

CREATE VIEW dept_salary_summary AS
SELECT
  department_id,
  COUNT(*) AS emp_count,
  AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
WITH READ ONLY;

뷰(View)를 이용한 DML 연산 (INSERT, UPDATE, DELETE)

1. 행(ROW) 제거 불가능한 경우 (DELETE 불가)

뷰 정의에 아래 요소가 포함되면 DELETE 연산이 불가능합니다:

GROUP BY 절
집계 함수 (SUM, AVG, COUNT 등)
DISTINCT 키워드
UNION / UNION ALL / INTERSECT / MINUS
CONNECT BY / START WITH
서브쿼리 포함

예시 (DELETE 불가)

-- DISTINCT 키워드 포함: DELETE 불가
CREATE OR REPLACE VIEW emp_dept60 AS
SELECT DISTINCT * FROM emps WHERE department_id = 60;

DELETE FROM emp_dept60 WHERE employee_id = 105; -- 오류 발생

2. 수정(UPDATE) 불가능한 경우

뷰 정의에 아래 요소가 포함되면 UPDATE 연산이 불가능합니다:

위의 DELETE 불가 조건 포함
표현식으로 정의된 열 (산술 연산, 문자열 결합 등)
ROWNUM 가상 열 사용
함수 결과로 정의된 열

예시 (UPDATE 불가)

-- 표현식 포함: 이름 결합, 연봉 계산
CREATE OR REPLACE VIEW emp_dept60 AS
SELECT
  employee_id,
  first_name || ' ' || last_name AS name,
  salary * 12 AS annual_salary
FROM emps
WHERE department_id = 60;

UPDATE emp_dept60
SET name = 'Kim Minsoo'; -- 오류 발생 (표현식 열은 수정 불가)

3. 데이터 추가 불가능한 경우 (INSERT 불가)

다음 조건이 포함된 경우, INSERT 연산이 불가능합니다:

위의 DELETE, UPDATE 불가 조건 포함
뷰에 기본 테이블의 NOT NULL 열이 포함되지 않은 경우

예시 (INSERT 불가)

-- hire_date는 NOT NULL인데 뷰에서 제외됨 → INSERT 불가
CREATE OR REPLACE VIEW emp_dept60 AS
SELECT employee_id, first_name, last_name, email, salary
FROM emps
WHERE department_id = 60;

INSERT INTO emp_dept60 (employee_id, first_name, last_name, email, salary)
VALUES (300, 'Lee', 'Jisoo', 'leejs@example.com', 4000);
-- 오류 발생: hire_date 누락됨 (NOT NULL 제약 위반)

4. WITH CHECK OPTION

INSERT, UPDATE 시 뷰의 WHERE 조건을 만족하는 데이터만 허용
조건을 벗어나는 데이터 삽입/수정 시 오류 발생

예시 (조건 위배 시 오류)

CREATE OR REPLACE VIEW emp_dept60 AS
SELECT * FROM emps
WHERE department_id = 60
WITH CHECK OPTION;

-- 아래 쿼리는 부서번호가 10으로 바뀌므로 오류 발생
UPDATE emp_dept60
SET department_id = 10
WHERE employee_id = 105;

5. WITH READ ONLY

뷰에 대해 모든 DML 연산을 금지
INSERT, UPDATE, DELETE 모두 불가

예시 (모든 DML 금지)

CREATE OR REPLACE VIEW emp_dept60 AS
SELECT * FROM emps
WHERE department_id = 60
WITH READ ONLY;

DELETE FROM emp_dept60 WHERE employee_id = 105; -- 오류 발생
UPDATE emp_dept60 SET salary = 5000 WHERE employee_id = 105; -- 오류 발생

1. 시퀀스 (SEQUENCE)

개념 정리

항목	설명
목적	자동으로 유일한 숫자(주로 기본키)를 생성
특징	공유 객체, 메모리 캐시로 효율 향상 가능
사용 위치	보통 기본키 생성 시 사용
주요 속성	`START WITH`, `INCREMENT BY`, `MAXVALUE`, `CYCLE`, `CACHE`, `NOCACHE`

예시 코드

CREATE SEQUENCE depts_seq
    START WITH 91
    INCREMENT BY 1
    MAXVALUE 100
    NOCYCLE
    NOCACHE;

91부터 시작해 100까지 1씩 증가.
NOCYCLE: 최대값 도달 시 에러 발생 (순환 안 함).
NOCACHE: 미리 시퀀스를 메모리에 저장하지 않음.

시퀀스 사용

-- 시퀀스를 사용한 INSERT
INSERT INTO depts(deptno, dname, loc)
VALUES (depts_seq.NEXTVAL, 'MARKETING', 'SAN DIEGO');

-- 현재 시퀀스 값 확인 (NEXTVAL 호출 후에만 가능)
SELECT depts_seq.CURRVAL FROM dual;

시퀀스 수정 및 삭제

ALTER SEQUENCE depts_seq
    MAXVALUE 99999;

DROP SEQUENCE depts_seq;

2. 아이덴티티 컬럼 (Identity Column)

항목	설명
목적	자동 증가 숫자를 기본키에 부여
특징	Oracle 12c부터 지원, `GENERATED ALWAYS` 또는 `BY DEFAULT`
장점	시퀀스를 따로 관리하지 않아도 됨

예시 코드

CREATE TABLE depts (
    deptno NUMBER(2) GENERATED ALWAYS AS IDENTITY (START WITH 10 INCREMENT BY 10),
    dname VARCHAR2(14),
    loc VARCHAR2(13) UNIQUE,
    CONSTRAINT depts_deptno_pk PRIMARY KEY(deptno)
);

-- deptno는 자동 생성되므로 명시하지 않아도 됨
INSERT INTO depts(dname, loc)
VALUES ('MARKETING', 'SAN DIEGO');

ALWAYS: 사용자가 값을 직접 넣을 수 없음.
BY DEFAULT: 사용자가 직접 값 지정도 가능.

3. 인덱스 (INDEX)

개념 정리

분류	설명
목적	검색 성능 향상 (데이터 접근 속도 개선)
자동 생성	`PRIMARY KEY`, `UNIQUE` 제약 조건 시 자동 생성
수동 생성	특정 열 검색 성능 향상을 위해 직접 생성 가능
사용 시점	WHERE 조건절, 조인 조건, 정렬 대상 등에 자주 사용되는 열

인덱스 종류 및 예시

종류	설명	예시
일반 인덱스	단순 열 기반 인덱스	`CREATE INDEX idx_name ON table(col);`
유일 인덱스	중복 허용 안 함	`CREATE UNIQUE INDEX idx_name ON table(col);`
비트맵 인덱스	중복 값 많은 경우 유리, DW에 적합	`CREATE BITMAP INDEX idx_name ON table(col);`
함수 기반 인덱스	가공된 값에 대해 인덱싱	`CREATE INDEX idx_upper_name ON table(UPPER(name));`
복합 인덱스	두 개 이상의 컬럼으로 구성	`CREATE INDEX idx_multi ON table(col1, col2);`

예시 코드

-- 일반 인덱스
CREATE INDEX emps_first_name_idx ON emps(first_name);

-- 유일 인덱스
CREATE UNIQUE INDEX emps_email_idx ON emps(email);

-- 인덱스 삭제
DROP INDEX emps_first_name_idx;

인덱스 생성 시 고려 사항

조건	설명
조건문에 자주 사용되는 열	WHERE 절에 자주 등장하는 열
조인 조건에 사용되는 열	테이블 간 연결 시 빠른 조회 가능
정렬 또는 그룹핑 열	ORDER BY, GROUP BY 성능 향상
중복이 적고 넓은 분포를 가진 열	선택도(선택율)가 높아야 효과적

요약

구분	목적	특징	예시 사용처
시퀀스	고유 번호 생성	명시적 호출 필요, 공유 객체	기본키 수동 생성
아이덴티티	고유 번호 생성	자동 적용, 시퀀스 대체	테이블 정의에 직접 사용
인덱스	검색 성능 향상	다양한 종류 존재	WHERE, JOIN, 정렬 조건

은채의 성장통

인생 별거 없어

이전 포스트

22일차 - 서브쿼리

다음 포스트

23일차 - 테이블, 뷰

KCC정보통신

1. 테이블 생성

기본 테이블 생성

서브쿼리 기반 테이블 생성 (CTAS)

2. 테이블 구조 변경 (ALTER TABLE)

열 추가

열 수정 (길이 변경 등)

열 삭제

열 이름 변경

열 비활성화 (SET UNUSED)

3. 테이블 삭제 및 데이터 초기화

테이블 완전 삭제

테이블의 데이터만 삭제 (구조 유지)

1. 제약조건의 종류 및 정의 방식

2. 열 레벨 제약조건 예시

3. 테이블 레벨 제약조건 예시

4. 제약조건 관리

제약조건 추가

제약조건 삭제

NOT NULL 제약 추가

5. 제약조건 비활성화 및 활성화

비활성화

종속 제약조건 포함 비활성화 (CASCADE)

활성화

VIEW란?

1. 권한 확인

2. 뷰 생성

단순 뷰

별칭 및 표현식 포함 뷰

3. 뷰 조회

4. 뷰 수정

5. 복합 뷰 생성 (조인 포함)

6. 뷰 삭제

7. 뷰 관련 제약사항

예시: 읽기 전용 뷰

뷰(View)를 이용한 DML 연산 (INSERT, UPDATE, DELETE)

1. 행(ROW) 제거 불가능한 경우 (DELETE 불가)

예시 (DELETE 불가)

2. 수정(UPDATE) 불가능한 경우

예시 (UPDATE 불가)

3. 데이터 추가 불가능한 경우 (INSERT 불가)

예시 (INSERT 불가)

4. WITH CHECK OPTION

예시 (조건 위배 시 오류)

5. WITH READ ONLY

예시 (모든 DML 금지)

1. 시퀀스 (SEQUENCE)

개념 정리

예시 코드

시퀀스 사용

시퀀스 수정 및 삭제

2. 아이덴티티 컬럼 (Identity Column)

예시 코드

3. 인덱스 (INDEX)

개념 정리

인덱스 종류 및 예시

예시 코드

인덱스 생성 시 고려 사항

요약

22일차 - 서브쿼리

24일차 - JDBC

0개의 댓글