[ORACLE] CREATE() TABLE, VIEW, USER 생성 실무 예제 총정리

[Oracle] CREATE

 

1. Oracle CREATE란?

CREATE는 Oracle에서 테이블, 뷰, 사용자, 시퀀스, 인덱스, 프로시저 등 다양한 데이터베이스 객체를 새로 생성할 때 사용하는 기본 SQL 명령어입니다.

2. CREATE TABLE: 테이블 생성

CREATE TABLE employees (
  employee_id   NUMBER PRIMARY KEY,
  first_name    VARCHAR2(50),
  last_name     VARCHAR2(50),
  hire_date     DATE,
  salary        NUMBER(10, 2)
);

기본적인 테이블 생성 예제입니다. PRIMARY KEY 제약조건을 포함하고 있습니다.

3. CREATE VIEW: 뷰 생성

CREATE VIEW emp_view AS
SELECT employee_id, first_name, salary
FROM employees
WHERE salary > 3000;

SELECT 쿼리 결과를 가상 테이블처럼 정의하여 반복적인 쿼리 작성을 단순화할 수 있습니다.

4. CREATE INDEX: 인덱스 생성

CREATE INDEX idx_lastname ON employees(last_name);

조회 성능 향상을 위해 특정 칼럼에 인덱스를 생성합니다.

5. CREATE SEQUENCE: 시퀀스 생성

CREATE SEQUENCE emp_seq
START WITH 1
INCREMENT BY 1
NOCACHE
NOCYCLE;

기본 키 자동 증가를 위한 시퀀스 생성 예제입니다.

6. CREATE USER: 사용자 계정 생성

CREATE USER hr_user IDENTIFIED BY hrpass;
GRANT CONNECT, RESOURCE TO hr_user;
ALTER USER hr_user DEFAULT TABLESPACE users;

사용자 생성 후 CONNECT 및 RESOURCE 권한을 부여하고 기본 테이블스페이스를 지정합니다.

7. CREATE ROLE: 역할 생성

CREATE ROLE manager_role;
GRANT CREATE TABLE, CREATE VIEW TO manager_role;
GRANT manager_role TO hr_user;

권한 집합을 정의하고 사용자에게 역할을 부여합니다.

8. CREATE SYNONYM: 시노님 생성

CREATE SYNONYM emp_syn FOR hr.employees;

다른 스키마의 객체에 대해 짧은 별칭을 부여하여 접근을 단순화합니다.

9. CREATE PROCEDURE: 저장 프로시저 생성

CREATE OR REPLACE PROCEDURE raise_salary (
  p_id IN NUMBER, p_amount IN NUMBER)
IS
BEGIN
  UPDATE employees
  SET salary = salary + p_amount
  WHERE employee_id = p_id;
END;
/

업무 로직을 재사용 가능한 저장 프로시저로 구현하는 예입니다.

10. CREATE FUNCTION: 사용자 정의 함수

CREATE OR REPLACE FUNCTION get_annual_salary (p_id IN NUMBER)
RETURN NUMBER
IS
  v_salary NUMBER;
BEGIN
  SELECT salary * 12 INTO v_salary FROM employees WHERE employee_id = p_id;
  RETURN v_salary;
END;
/

간단한 계산이나 데이터 조회 기능을 함수로 만들어 재사용합니다.

11. CREATE TRIGGER: 트리거 생성

CREATE OR REPLACE TRIGGER trg_set_hire_date
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
  :NEW.hire_date := SYSDATE;
END;
/

INSERT 시점에 자동으로 값 설정을 수행하는 BEFORE TRIGGER 예제입니다.

12. CREATE TABLESPACE: 테이블스페이스 생성

CREATE TABLESPACE data_ts
DATAFILE '/u01/app/oracle/oradata/ORCL/data01.dbf'
SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 1G;

사용자 데이터 저장을 위한 논리적 저장소입니다.

13. CREATE TEMPORARY TABLE: 임시 테이블 생성

CREATE GLOBAL TEMPORARY TABLE temp_results (
  id NUMBER,
  value VARCHAR2(100)
) ON COMMIT DELETE ROWS;

세션 또는 트랜잭션 동안만 존재하는 테이블입니다.

14. CREATE MATERIALIZED VIEW: 물리적 뷰 생성

CREATE MATERIALIZED VIEW emp_mv
REFRESH COMPLETE ON DEMAND
AS
SELECT * FROM employees;

성능 개선을 위해 주기적으로 갱신되는 뷰입니다.

15. CREATE DIRECTORY: 외부 파일 디렉터리 객체 생성

CREATE DIRECTORY log_dir AS '/u01/logs';

외부 파일 입출력을 위해 Oracle이 접근 가능한 디렉터리를 정의합니다.

출처

• Oracle 공식 문서: https://docs.oracle.com/en/database/
• Oracle SQL Reference Manual
• 실무 중심 오라클 객체 설계와 튜닝 - 김종혁 저