[Oracle] 03. CRUD 기본 (INSERT, SELECT, UPDATE, DELETE)

Oracle에서의 데이터 조작 기본인 CRUD 명령어를 정리합니다.
MySQL과의 차이점을 함께 설명합니다.

샘플 테이블 준비

CREATE TABLE employees (
    id         NUMBER(10) NOT NULL,
    name       VARCHAR2(50) NOT NULL,
    dept       VARCHAR2(30),
    salary     NUMBER(12,2) DEFAULT 0,
    hire_date  DATE,
    CONSTRAINT pk_emp PRIMARY KEY (id)
);

CREATE SEQUENCE seq_emp START WITH 1 INCREMENT BY 1;

INSERT (데이터 삽입)

기본 삽입

-- 시퀀스를 이용한 삽입
INSERT INTO employees (id, name, dept, salary, hire_date)
VALUES (seq_emp.NEXTVAL, '홍길동', '개발팀', 5000000, TO_DATE('2024-01-15', 'YYYY-MM-DD'));

-- 여러 행 삽입 (INSERT ALL)
INSERT ALL
    INTO employees (id, name, dept, salary, hire_date) VALUES (seq_emp.NEXTVAL, '김철수', '기획팀', 4500000, TO_DATE('2024-02-01', 'YYYY-MM-DD'))
    INTO employees (id, name, dept, salary, hire_date) VALUES (seq_emp.NEXTVAL, '이영희', '개발팀', 5500000, TO_DATE('2023-06-10', 'YYYY-MM-DD'))
    INTO employees (id, name, dept, salary, hire_date) VALUES (seq_emp.NEXTVAL, '박민수', '디자인팀', 4800000, TO_DATE('2024-03-20', 'YYYY-MM-DD'))
SELECT * FROM DUAL;

-- SYSDATE 사용
INSERT INTO employees (id, name, dept, salary, hire_date)
VALUES (seq_emp.NEXTVAL, '최지은', '개발팀', 6000000, SYSDATE);

MySQL의 INSERT INTO ... VALUES (...), (...), (...) 문법은 Oracle에서 지원하지 않습니다.
Oracle은 INSERT ALL ... SELECT * FROM DUAL 형태를 사용합니다.

INSERT 변형

-- 다른 테이블에서 복사
INSERT INTO employees_backup
SELECT * FROM employees WHERE dept = '개발팀';

-- MERGE (MySQL의 ON DUPLICATE KEY UPDATE에 해당)
MERGE INTO employees e
USING (SELECT 1 AS id, '홍길동' AS name, '인사팀' AS dept, 5500000 AS salary FROM DUAL) s
ON (e.id = s.id)
WHEN MATCHED THEN
    UPDATE SET e.dept = s.dept, e.salary = s.salary
WHEN NOT MATCHED THEN
    INSERT (id, name, dept, salary) VALUES (s.id, s.name, s.dept, s.salary);

SELECT (데이터 조회)

기본 조회

-- 전체 조회
SELECT * FROM employees;

-- 특정 컬럼 조회
SELECT name, dept, salary FROM employees;

-- 별칭(Alias) 사용
SELECT name AS "이름", dept AS "부서", salary AS "급여" FROM employees;

-- 중복 제거
SELECT DISTINCT dept FROM employees;

-- DUAL 테이블 (더미 테이블)
SELECT SYSDATE FROM DUAL;
SELECT 1 + 1 FROM DUAL;

Oracle에서 SELECT 1+1은 오류입니다. 반드시 FROM DUAL이 필요합니다.
(Oracle 23c부터는 FROM 절 생략 가능)

WHERE 조건절

-- 비교 연산자
SELECT * FROM employees WHERE salary >= 5000000;
SELECT * FROM employees WHERE dept = '개발팀';
SELECT * FROM employees WHERE dept != '기획팀';
SELECT * FROM employees WHERE dept <> '기획팀';  -- 동일

-- 논리 연산자
SELECT * FROM employees WHERE dept = '개발팀' AND salary >= 5000000;
SELECT * FROM employees WHERE dept = '개발팀' OR dept = '기획팀';
SELECT * FROM employees WHERE NOT dept = '디자인팀';

-- BETWEEN
SELECT * FROM employees WHERE salary BETWEEN 4500000 AND 5500000;

-- IN
SELECT * FROM employees WHERE dept IN ('개발팀', '기획팀');

-- LIKE
SELECT * FROM employees WHERE name LIKE '김%';
SELECT * FROM employees WHERE name LIKE '%수';
SELECT * FROM employees WHERE name LIKE '_길_';  -- 1글자 + '길' + 1글자

-- NULL 체크
SELECT * FROM employees WHERE dept IS NULL;
SELECT * FROM employees WHERE dept IS NOT NULL;

정렬 (ORDER BY)

SELECT * FROM employees ORDER BY salary ASC;
SELECT * FROM employees ORDER BY salary DESC;
SELECT * FROM employees ORDER BY dept ASC, salary DESC;

-- NULLS FIRST / NULLS LAST (Oracle 전용)
SELECT * FROM employees ORDER BY dept NULLS LAST;
SELECT * FROM employees ORDER BY dept DESC NULLS FIRST;

행 수 제한

Oracle에는 MySQL의 LIMIT가 없습니다.

-- Oracle 12c+ : FETCH FIRST (표준 SQL)
SELECT * FROM employees ORDER BY salary DESC
FETCH FIRST 3 ROWS ONLY;

-- 페이징
SELECT * FROM employees ORDER BY id
OFFSET 2 ROWS FETCH NEXT 3 ROWS ONLY;

-- Oracle 11g 이하: ROWNUM
SELECT * FROM (
    SELECT e.*, ROWNUM AS rn
    FROM (SELECT * FROM employees ORDER BY salary DESC) e
    WHERE ROWNUM <= 5
)
WHERE rn >= 3;

-- ROW_NUMBER 활용 (범용)
SELECT * FROM (
    SELECT e.*, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
    FROM employees e
)
WHERE rn BETWEEN 3 AND 5;

UPDATE (데이터 수정)

-- 단일 컬럼 수정
UPDATE employees SET salary = 5200000 WHERE id = 1;

-- 다중 컬럼 수정
UPDATE employees SET dept = '인사팀', salary = 5300000 WHERE id = 1;

-- 조건부 수정
UPDATE employees SET salary = salary * 1.1 WHERE dept = '개발팀';

-- 서브쿼리를 이용한 수정
UPDATE employees e
SET salary = (SELECT AVG(salary) FROM employees WHERE dept = e.dept)
WHERE dept = '개발팀';

⚠️ Oracle은 기본적으로 AUTO COMMIT이 아닙니다.
UPDATE/DELETE 후 반드시 COMMIT 또는 ROLLBACK을 실행해야 합니다.

UPDATE employees SET salary = 5500000 WHERE id = 1;
COMMIT;  -- 변경 확정
-- 또는
ROLLBACK;  -- 변경 취소

DELETE (데이터 삭제)

-- 조건부 삭제
DELETE FROM employees WHERE id = 5;

-- 여러 조건
DELETE FROM employees WHERE dept = '기획팀' AND salary < 4000000;

-- 전체 삭제
DELETE FROM employees;

-- TRUNCATE (더 빠름, ROLLBACK 불가)
TRUNCATE TABLE employees;

DELETE vs TRUNCATE

항목	DELETE	TRUNCATE
WHERE 조건	사용 가능	사용 불가
속도	느림 (행 단위)	빠름 (세그먼트 해제)
ROLLBACK	가능	불가
트리거	실행됨	실행 안됨
공간 반환	반환 안됨	반환됨
HWM 리셋	안됨	리셋됨

집계 함수

SELECT COUNT(*) FROM employees;
SELECT COUNT(DISTINCT dept) FROM employees;

SELECT SUM(salary) AS 총급여 FROM employees;
SELECT AVG(salary) AS 평균급여 FROM employees;
SELECT MAX(salary) AS 최고급여 FROM employees;
SELECT MIN(salary) AS 최저급여 FROM employees;

-- 부서별 집계
SELECT dept, COUNT(*) AS 인원, AVG(salary) AS 평균급여
FROM employees
GROUP BY dept;

-- HAVING
SELECT dept, AVG(salary) AS avg_sal
FROM employees
GROUP BY dept
HAVING AVG(salary) >= 5000000;

SELECT 실행 순서

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → FETCH/OFFSET

MySQL과 동일한 순서이지만, Oracle에서는 HAVING 절에서 별칭을 사용할 수 없습니다.
HAVING avg_sal >= 5000000 (❌) → HAVING AVG(salary) >= 5000000 (✅)