CREATE TABLE dept (
  deptno NUMBER(2)    CONSTRAINT dept_pk PRIMARY KEY,
  dname  VARCHAR2(20) NOT NULL
);

CREATE TABLE emp (
  empno    NUMBER(4)    CONSTRAINT emp_pk PRIMARY KEY,
  ename    VARCHAR2(20) NOT NULL,
  deptno   NUMBER(2)    CONSTRAINT emp_dept_fk REFERENCES dept(deptno),
  sal      NUMBER(9,2)  CONSTRAINT emp_sal_ck CHECK (sal >= 0),
  hiredate DATE         DEFAULT SYSDATE,
  mgr      NUMBER(4)    REFERENCES emp(empno),
  email    VARCHAR2(50) UNIQUE
);

CREATE SEQUENCE emp_seq START WITH 8000 INCREMENT BY 1 NOCACHE;

INSERT INTO emp (empno, ename, deptno, sal)
VALUES (emp_seq.NEXTVAL, 'ALLEN', 30, 1600);

SELECT emp_seq.CURRVAL FROM dual;
-- → 8000, 같은 세션에서 방금 뽑은 값 재조회

-- 12c 이후: 시퀀스 없이 자동 증가
CREATE TABLE emp2 (
  empno NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  ename VARCHAR2(20)
);

CREATE INDEX idx_emp_deptno ON emp(deptno);
CREATE INDEX idx_emp_deptno_sal ON emp(deptno, sal);
CREATE INDEX idx_emp_upper_ename ON emp(UPPER(ename)); -- 함수 기반

ALTER TABLE emp ADD hp_no VARCHAR2(20);
ALTER TABLE emp MODIFY sal NUMBER(10,2);
ALTER TABLE emp DROP COLUMN hp_no;

COMMENT ON TABLE emp IS '직원 테이블';
COMMENT ON COLUMN emp.sal IS '월급, 원 단위';

-- 단건 삽입
INSERT INTO dept (deptno, dname) VALUES (40, 'IT');

-- INSERT ... SELECT: 조회 결과를 그대로 적재
INSERT INTO dept_backup SELECT * FROM dept;

-- INSERT ALL: 여러 테이블/행에 한 번에 삽입
INSERT ALL
  INTO dept VALUES (50, 'HR')
  INTO dept VALUES (60, 'QA')
SELECT * FROM dual;

-- 서브쿼리로 갱신: deptno=30 급여를 부서 평균으로
UPDATE emp e
SET sal = (SELECT AVG(sal) FROM emp WHERE deptno = e.deptno)
WHERE deptno = 30;

-- DELETE: 행 단위, ROLLBACK 가능, 트리거 발생
DELETE FROM emp WHERE empno = 7900;

-- TRUNCATE: 테이블 전체 비움, ROLLBACK 불가, HWM 초기화
TRUNCATE TABLE dept_backup;

MERGE INTO dept d
USING (SELECT 40 AS deptno, 'IT팀' AS dname FROM dual) s
ON (d.deptno = s.deptno)
WHEN MATCHED THEN
  UPDATE SET d.dname = s.dname
WHEN NOT MATCHED THEN
  INSERT (deptno, dname) VALUES (s.deptno, s.dname);
-- → deptno=40 있으면 dname 갱신, 없으면 새 행 삽입

-- WHERE 연산자
SELECT * FROM emp WHERE sal BETWEEN 1000 AND 3000;
SELECT * FROM emp WHERE deptno IN (10, 20);
SELECT * FROM emp WHERE ename LIKE 'S%';
SELECT * FROM emp WHERE ename LIKE 'S\_MITH' ESCAPE '\'; -- _를 문자로
SELECT * FROM emp WHERE mgr IS NULL; -- KING만 해당

-- ORDER BY: NULL 위치 지정
SELECT ename, mgr FROM emp ORDER BY mgr NULLS FIRST;

-- DISTINCT
SELECT DISTINCT deptno FROM emp;
-- → 10, 20, 30

-- 별칭과 큰따옴표: 공백·한글 별칭은 큰따옴표로 감싼다
SELECT sal AS "월 급여" FROM emp WHERE empno = 7839;

-- DUAL: 테이블 없이 값 하나를 조회할 때 쓰는 가상 테이블
SELECT SYSDATE, 1 + 1 AS calc FROM dual;

-- INNER JOIN: 양쪽 다 있는 행만
SELECT e.ename, d.dname
FROM emp e
JOIN dept d ON e.deptno = d.deptno;

-- LEFT JOIN: emp 기준, 매칭 없으면 dname은 NULL
SELECT e.ename, d.dname
FROM emp e
LEFT JOIN dept d ON e.deptno = d.deptno;

-- FULL JOIN: 양쪽 어느 한쪽에만 있어도 표시
SELECT e.ename, d.dname
FROM emp e
FULL JOIN dept d ON e.deptno = d.deptno;

-- Oracle 구식 (+) 표기: LEFT JOIN e.deptno = d.deptno(+) 와 동일
SELECT e.ename, d.dname
FROM emp e, dept d
WHERE e.deptno = d.deptno(+);

-- 셀프 조인: 직원과 그 상급자
SELECT e.ename AS 직원, m.ename AS 상급자
FROM emp e
LEFT JOIN emp m ON e.mgr = m.empno;

-- CROSS JOIN: 모든 조합, 카티전 곱
SELECT e.ename, d.dname FROM emp e CROSS JOIN dept d;

-- ON에 조건: deptno=10이 아닌 emp도 남고, 조건 안 맞으면 dname만 NULL
SELECT e.ename, d.dname
FROM emp e
LEFT JOIN dept d ON e.deptno = d.deptno AND d.deptno = 10;

-- WHERE에 조건: LEFT JOIN 결과에서 dname=NULL인 행까지 걸러짐
SELECT e.ename, d.dname
FROM emp e
LEFT JOIN dept d ON e.deptno = d.deptno
WHERE d.deptno = 10;
-- → 사실상 INNER JOIN과 같은 결과

-- COUNT(*): 전체 행 수, COUNT(col): NULL 제외한 개수
SELECT COUNT(*), COUNT(mgr) FROM emp;
-- → COUNT(*)=7, COUNT(mgr)=6 (KING의 mgr가 NULL)

SELECT deptno, SUM(sal), AVG(sal), MAX(sal), MIN(sal)
FROM emp
GROUP BY deptno;

-- HAVING: 그룹 결과에 대한 조건, WHERE보다 뒤에 평가
SELECT deptno, COUNT(*) cnt
FROM emp
GROUP BY deptno
HAVING COUNT(*) >= 3;
-- → deptno=20, 30만 남음

-- ROLLUP: 부서별 합계 + 전체 합계 소계 행 추가
SELECT deptno, SUM(sal)
FROM emp
GROUP BY ROLLUP(deptno);

-- LISTAGG: 그룹의 값을 구분자로 이어붙인 문자열로
SELECT deptno, LISTAGG(ename, ', ') WITHIN GROUP (ORDER BY ename) AS 명단
FROM emp
GROUP BY deptno;
-- → 20: JONES, SCOTT, SMITH

-- GROUPING: ROLLUP 소계 행인지 구분 (1이면 소계)
SELECT deptno, SUM(sal), GROUPING(deptno) AS is_total
FROM emp
GROUP BY ROLLUP(deptno);

-- 순위: RANK/DENSE_RANK는 동점 처리 방식이 다르다
SELECT ename, sal,
  ROW_NUMBER() OVER (ORDER BY sal DESC) AS rn,
  RANK()       OVER (ORDER BY sal DESC) AS rk,
  DENSE_RANK() OVER (ORDER BY sal DESC) AS drk
FROM emp;

-- PARTITION BY: 부서별로 나눠서 순위 계산
SELECT ename, deptno, sal,
  RANK() OVER (PARTITION BY deptno ORDER BY sal DESC) AS dept_rank
FROM emp;

-- LAG/LEAD: 이전/다음 행 값 참조
SELECT ename, hiredate,
  LAG(hiredate)  OVER (ORDER BY hiredate) AS 이전입사일,
  LEAD(hiredate) OVER (ORDER BY hiredate) AS 다음입사일
FROM emp;

-- 누적 합계
SELECT ename, hiredate, sal,
  SUM(sal) OVER (ORDER BY hiredate) AS 누적급여
FROM emp;

-- FIRST_VALUE, NTILE: 부서 내 최고 급여자, 4분위 그룹
SELECT ename, deptno, sal,
  FIRST_VALUE(ename) OVER (PARTITION BY deptno ORDER BY sal DESC) AS top,
  NTILE(4) OVER (ORDER BY sal DESC) AS quartile
FROM emp;

-- 그룹별 상위 N: 부서별 급여 1위만 뽑기
SELECT * FROM (
  SELECT e.*, RANK() OVER (PARTITION BY deptno ORDER BY sal DESC) rk
  FROM emp e
) WHERE rk = 1;

-- ROWNUM 함정: 이 쿼리는 "정렬 안 된" 상태에서 3건을 자른다
SELECT * FROM emp ORDER BY sal DESC; -- 여기에 WHERE ROWNUM<=3을 붙이면 안 됨

-- 올바른 3중 서브쿼리 패턴: 정렬 → ROWNUM 부여 → 범위 자르기
SELECT * FROM (
  SELECT a.*, ROWNUM rn FROM (
    SELECT * FROM emp ORDER BY sal DESC
  ) a WHERE ROWNUM <= 10
) WHERE rn > 5;
-- → 급여 6~10위

-- 12c 이후: OFFSET FETCH로 훨씬 간결
SELECT * FROM emp
ORDER BY sal DESC
OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY;

-- ROW_NUMBER 방식: 총 건수와 함께 페이지 계산이 필요할 때
SELECT * FROM (
  SELECT e.*, ROW_NUMBER() OVER (ORDER BY sal DESC) rn FROM emp e
) WHERE rn BETWEEN 6 AND 10;

-- 키셋 페이징: 마지막으로 본 id 이후만 조회, 대용량에서 빠름
SELECT * FROM emp
WHERE empno > 7654
ORDER BY empno
FETCH NEXT 10 ROWS ONLY;

<!-- lastEmpno=null이면 처음부터, 아니면 그 이후부터 -->
SELECT * FROM emp
WHERE 1=1
  <if test="lastEmpno != null">AND empno &gt; #{lastEmpno}</if>
ORDER BY empno
FETCH NEXT #{pageSize} ROWS ONLY

SELECT SUBSTR('HELLO', 1, 3) FROM dual;      -- → HEL
SELECT SUBSTR('HELLO', -3) FROM dual;        -- → LLO, 음수는 뒤에서부터
SELECT INSTR('HELLO', 'L') FROM dual;        -- → 3, 첫 위치

SELECT LENGTH('안녕'), LENGTHB('안녕') FROM dual;
-- → LENGTH=2(문자 수), LENGTHB=6(UTF-8 바이트 수)

SELECT UPPER('abc'), LOWER('ABC'), INITCAP('abc def') FROM dual;
-- → ABC, abc, Abc Def

SELECT LPAD('7', 4, '0'), RPAD('AB', 5, '*') FROM dual;
-- → 0007, AB***

SELECT TRIM('  hi  '), LTRIM('  hi'), RTRIM('hi  ') FROM dual;
SELECT REPLACE('2024-01-01', '-', '/') FROM dual; -- → 2024/01/01
SELECT TRANSLATE('abc', 'ab', 'AB') FROM dual;     -- → ABc, 문자 단위 치환

SELECT ename || '(' || deptno || ')' FROM emp WHERE empno = 7839;
SELECT CONCAT('A', 'B') FROM dual; -- → AB, 두 인자만 받음

-- 정규식: LIKE보다 세밀한 패턴 매칭
SELECT * FROM emp WHERE REGEXP_LIKE(ename, '^[JS]');
SELECT REGEXP_SUBSTR('a-01', '[0-9]+') FROM dual;      -- → 01
SELECT REGEXP_REPLACE('010-1234-5678', '-', '') FROM dual; -- → 01012345678

SELECT ROUND(1234.567, 1), ROUND(1234.567, -2) FROM dual;
-- → 1234.6, 1200 (음수는 정수 자릿수 반올림)

SELECT TRUNC(1234.567, 1), TRUNC(1234.567, -2) FROM dual;
-- → 1234.5, 1200 (버림, 반올림 없음)

SELECT MOD(10, 3), CEIL(1.1), FLOOR(1.9), ABS(-5), POWER(2, 10) FROM dual;
-- → 1, 2, 1, 5, 1024

SELECT TO_NUMBER('1,234', '9,999') FROM dual; -- → 1234

-- 0으로 나누기 방지: NULLIF로 분모가 0이면 NULL 처리
SELECT sal / NULLIF(0, 0) FROM dual; -- → NULL, ORA-01476 대신 안전
SELECT 100 / NULLIF(deptno - 10, 0) FROM emp WHERE empno = 7839;

-- NUMBER(p,s): p는 전체 자릿수, s는 소수점 이하 자릿수
-- NUMBER(9,2)는 정수부 7자리 + 소수부 2자리까지 저장

SELECT SYSDATE, SYSTIMESTAMP, CURRENT_DATE FROM dual;
-- SYSDATE/SYSTIMESTAMP: DB 서버 시각, CURRENT_DATE: 세션 시간대 시각

SELECT TO_CHAR(hiredate, 'YYYY-MM-DD DY') FROM emp WHERE empno = 7839;
-- → 1981-11-17 화
SELECT TO_DATE('20240315', 'YYYYMMDD') FROM dual;

-- 날짜 산술: 정수는 일 단위, 1/24는 1시간
SELECT hiredate + 1 AS 다음날, SYSDATE + 1/24 AS 한시간후 FROM emp WHERE empno=7839;

SELECT ADD_MONTHS(hiredate, 3) FROM emp WHERE empno = 7839;   -- 3개월 후
SELECT MONTHS_BETWEEN(SYSDATE, hiredate) FROM emp WHERE empno = 7839;
SELECT LAST_DAY(hiredate) FROM emp WHERE empno = 7839;         -- 그 달 말일
SELECT NEXT_DAY(hiredate, '월요일') FROM emp WHERE empno = 7839;
SELECT TRUNC(hiredate, 'MM') FROM emp WHERE empno = 7839;      -- 그 달 1일
SELECT EXTRACT(YEAR FROM hiredate) FROM emp WHERE empno = 7839;
SELECT hiredate + INTERVAL '1' MONTH FROM emp WHERE empno = 7839;

-- 월별 집계 패턴
SELECT TRUNC(hiredate, 'MM') AS 입사월, COUNT(*)
FROM emp
GROUP BY TRUNC(hiredate, 'MM');

-- 나쁜 예: hiredate에 TRUNC를 씌우면 인덱스를 못 탄다
SELECT * FROM emp WHERE TRUNC(hiredate) = TO_DATE('1981-11-17', 'YYYY-MM-DD');

-- 좋은 예: 컬럼은 그대로, 범위로 비교
SELECT * FROM emp
WHERE hiredate >= TO_DATE('1981-11-17', 'YYYY-MM-DD')
  AND hiredate <  TO_DATE('1981-11-18', 'YYYY-MM-DD');

SELECT NVL(mgr, 0) FROM emp WHERE empno = 7839;         -- → 0
SELECT NVL2(mgr, '있음', '없음') FROM emp WHERE empno = 7839; -- → 없음
SELECT COALESCE(mgr, deptno, 0) FROM emp;               -- 첫 NOT NULL 값
SELECT NULLIF(sal, 0) FROM emp;                         -- 같으면 NULL

-- DECODE: 동등 비교만, CASE: 범위·조건식 가능(검색 CASE)
SELECT DECODE(deptno, 10, '회계', 20, '연구', '기타') FROM emp;
SELECT CASE WHEN sal >= 3000 THEN '고'
            WHEN sal >= 1500 THEN '중'
            ELSE '저' END AS 급여등급
FROM emp;

-- NULL 비교 규칙: = NULL은 항상 거짓, IS NULL만 참
SELECT * FROM emp WHERE mgr = NULL;    -- → 0행, 항상 거짓
SELECT * FROM emp WHERE mgr IS NULL;   -- → KING 1행

-- NOT IN과 NULL 함정: 목록에 NULL이 있으면 전체가 알 수 없음(0행)
SELECT * FROM emp WHERE deptno NOT IN (SELECT deptno FROM dept WHERE dname = 'X');
-- 서브쿼리 결과에 NULL이 하나라도 있으면 결과가 통째로 사라진다

-- 스칼라 서브쿼리: SELECT 절에서 값 하나
SELECT ename, (SELECT dname FROM dept d WHERE d.deptno = e.deptno) AS dname
FROM emp e;

-- 인라인 뷰: FROM 절의 서브쿼리
SELECT * FROM (SELECT deptno, AVG(sal) avg_sal FROM emp GROUP BY deptno) WHERE avg_sal > 1500;

-- EXISTS vs IN: EXISTS는 존재 여부만 확인, 대용량 상관 서브쿼리에 유리
SELECT * FROM emp e WHERE EXISTS (SELECT 1 FROM dept d WHERE d.deptno = e.deptno);
SELECT * FROM emp e WHERE e.deptno IN (SELECT deptno FROM dept);

-- WITH(CTE): 복잡한 쿼리를 이름 붙여 재사용
WITH dept_avg AS (
  SELECT deptno, AVG(sal) avg_sal FROM emp GROUP BY deptno
)
SELECT e.ename, d.avg_sal FROM emp e JOIN dept_avg d ON e.deptno = d.deptno;

-- 재귀 WITH: 조직도처럼 스스로를 참조하는 계층 조회
WITH org (empno, ename, mgr, lvl) AS (
  SELECT empno, ename, mgr, 1 FROM emp WHERE mgr IS NULL
  UNION ALL
  SELECT e.empno, e.ename, e.mgr, o.lvl + 1
  FROM emp e JOIN org o ON e.mgr = o.empno
)
SELECT * FROM org ORDER SIBLINGS BY ename;

-- CONNECT BY: Oracle 전통 계층 조회 문법
SELECT LEVEL, ename, SYS_CONNECT_BY_PATH(ename, '/') AS 경로
FROM emp
START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr
ORDER SIBLINGS BY ename;

UPDATE emp SET sal = sal + 100 WHERE empno = 7900;
SAVEPOINT sp1;
UPDATE emp SET sal = sal + 100 WHERE empno = 7654;
ROLLBACK TO sp1; -- sp1 이후 변경만 취소
COMMIT;

-- 다른 트랜잭션이 끝날 때까지 대기 없이 즉시 실패/건너뛰기
SELECT * FROM emp WHERE empno = 7900 FOR UPDATE NOWAIT;
SELECT * FROM emp WHERE deptno = 30 FOR UPDATE SKIP LOCKED;
SELECT * FROM emp WHERE empno = 7900 FOR UPDATE WAIT 3;

-- 낙관적 잠금: version 컬럼으로 동시 수정 감지
UPDATE emp SET sal = sal + 100, version = version + 1
WHERE empno = 7900 AND version = 3;
-- → 영향받은 행 수가 0이면 다른 사용자가 먼저 수정한 것

EXPLAIN PLAN FOR
SELECT * FROM emp WHERE deptno = 10;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
-- → PLAN_TABLE에 저장된 실행 계획을 트리 형태로 출력

SELECT table_name FROM user_tables;
SELECT column_name, data_type, nullable FROM user_tab_columns WHERE table_name = 'EMP';
SELECT index_name, uniqueness FROM user_indexes WHERE table_name = 'EMP';
SELECT index_name, column_name FROM user_ind_columns WHERE table_name = 'EMP';
SELECT constraint_name, constraint_type FROM user_constraints WHERE table_name = 'EMP';
SELECT sequence_name, last_number FROM user_sequences;
SELECT text FROM user_source WHERE name = 'MY_PROC' ORDER BY line;