🎯 Oracle SQL 고급 정리: 동적 테이블 생성, 제약조건, 조인, 집합 연산자

 

1️⃣ EXECUTE IMMEDIATE를 활용한 테이블 일괄 생성

BEGIN
   FOR i IN 1991 .. 1995 LOOP 
      EXECUTE IMMEDIATE 'CREATE TABLE order_' || i || ' AS SELECT * FROM order_1990';
   END LOOP;
END;
/

• 위 구문은 order_1990 테이블을 기반으로 order_1991 ~ order_1995까지 복사 테이블을 생성.
• 생성 확인: SELECT * FROM TABS;

---

2️⃣ 🎯 제약조건 (Constraints)

제약조건은 데이터 무결성(integrity) 유지를 위해 꼭 필요함.

제약조건	설명	예시 오류 상황
NOT NULL	NULL 허용 안됨	job_id에 값 누락
UNIQUE	중복 불가	같은 이메일 입력
CHECK	조건 만족해야 함	salary < 0 입력
PRIMARY KEY	NOT NULL + UNIQUE	중복 또는 NULL ID
FOREIGN KEY	다른 테이블 참조	없는 부서 ID 참조

• 복사 테이블(CREATE TABLE A AS SELECT * FROM B)은 제약조건이 복사되지 않음 ⚠️

💡 제약조건 확인 및 관리

• 조회:
• DESC USER_CONSTRAINTS; SELECT * FROM ALL_CONSTRAINTS WHERE table_name='EMPLOYEES';
• 제약조건 추가 예시:
• ALTER TABLE employees2 ADD CONSTRAINT email_with_at CHECK (REGEXP_LIKE(email, '@')) NOVALIDATE;
• 삭제:
• ALTER TABLE employees2 DROP CONSTRAINT new_emp_email_uk;

---

3️⃣ 🎯 JOIN (조인)

📌 JOIN 순서 정리

1. 어떤 정보가 필요한가?
2. 그 정보는 어떤 테이블에 있는가?
3. 공통 컬럼(FK/PK)을 찾는다.
4. 적절한 조인 사용 (INNER, OUTER, SELF)

📋 공통 컬럼 예시

테이블	공통 컬럼
employees, departments	department_id
employees, jobs	job_id

✅ INNER JOIN (일치하는 값만 조인)

SELECT e.last_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE e.last_name = 'King';

• Oracle Join 방식:
• SELECT e.last_name, j.job_title, d.department_name FROM employees e, jobs j, departments d WHERE e.job_id = j.job_id AND e.department_id = d.department_id AND e.last_name = 'King';

---

✅ OUTER JOIN (데이터 누락 방지)

부서 없는 직원도 포함하고 싶을 때 사용

• ANSI 방식:
• SELECT e.employee_id, d.department_name FROM departments d RIGHT OUTER JOIN employees e ON d.department_id = e.department_id;
• Oracle 방식:
• SELECT e.employee_id, d.department_name FROM employees e, departments d WHERE e.department_id = d.department_id(+);

※ + 기호는 데이터가 없는 테이블에 붙임.

---

✅ SELF JOIN (자기 자신과 조인)

SELECT A.last_name AS EMP_NAME, B.last_name AS MGR_NAME
FROM employees A
JOIN employees B ON A.manager_id = B.employee_id
JOIN departments D ON A.department_id = D.department_id
WHERE D.department_name = 'Executive';

---

4️⃣ 🎯 SET 연산자 (집합 연산자)

연산자 의미 중복 포함 여부

연산자	의미	중복 포함 여부
UNION	합집합	❌
UNION ALL	합집합	✅
MINUS	차집합	❌
INTERSECT	교집합	❌

✅ 예시

SELECT employee_id FROM employees WHERE salary > 10000
UNION
SELECT employee_id FROM employees WHERE department_id = 100;

※ 컬럼 수, 순서, 데이터 타입이 동일해야 함! 다르면 ORA-01789 에러 발생.

---

5️⃣ 🎯 DEFAULT 제약조건

• 별도 입력 없을 시 자동으로 값 설정

CREATE TABLE employees4 (
  emp_id NUMBER(4),
  ename VARCHAR2(20),
  sal NUMBER(6,2) DEFAULT 0,
  gender CHAR(1) DEFAULT 'M',
  hiredate DATE DEFAULT SYSDATE
);

---