FOREIGN KEY (외래키)
: 하나의 테이블에서 다른 테이블을 연결하는 속성(ATTRIBUTE, COLUMN)
: 외래키를 잡지 않아도 조인에는 문제 X
→ INSERT, UPDATE, DELETE 사용에 제한을 주기 위해 사용
→ 연관된 COLUMN에 존재하지 않는 값을 입력하는 경우, 시스템 오류는 아니지만 논리적 오류 발생
→ 이런 논리적 오류 발생을 막는 것이 외래키의 의미
SUB QUERY
: SQL 내부에 SQL이 있는 형태
: ()와 함께 사용
▶ 형태
▷ WHERE 절에 SUB QUERY가 있는 경우
SELECT * FROM EMP WHERE DEPTNO = (SELECT DEPTNO FROM DEPT WHERE DNAME = 'SALES');출력 결과
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- -------- ---------- ---------- ---------- 7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30 7521 WARD SALESMAN 7698 81/02/22 1250 500 30 7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30 7698 BLAKE MANAGER 7839 81/05/01 2850 30 7844 TURNER SALESMAN 7698 81/09/08 1500 30 7900 JAMES CLERK 7698 81/12/03 950 30
▷ FROM 절에 SUB QUERY가 있는 경우
SELECT * FROM EMP, (SELECT EMPNO BOSS_NO, ENAME BOSS_NAME FROM EMP) BOSS
WHERE EMP.MGR = BOSS.BOSS_NO;출력 결과
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO BOSS_NO BOSS_NAME ---------- ---------- --------- ---------- -------- ---------- ---------- ---------- ---------- ---------- 7788 SCOTT ANALYST 7566 82/12/09 3000 20 7566 JONES 7902 FORD ANALYST 7566 81/12/03 3000 20 7566 JONES 7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30 7698 BLAKE 7521 WARD SALESMAN 7698 81/02/22 1250 500 30 7698 BLAKE 7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30 7698 BLAKE 7844 TURNER SALESMAN 7698 81/09/08 1500 30 7698 BLAKE 7900 JAMES CLERK 7698 81/12/03 950 30 7698 BLAKE 7934 MILLER CLERK 7782 82/01/23 1300 10 7782 CLARK 7876 ADAMS CLERK 7788 83/01/12 1100 20 7788 SCOTT 7566 JONES MANAGER 7839 81/04/02 2975 20 7839 KING 7698 BLAKE MANAGER 7839 81/05/01 2850 30 7839 KING 7782 CLARK MANAGER 7839 81/06/09 2450 10 7839 KING 7369 SMITH CLERK 7902 80/12/17 800 20 7902 FORD
▷ SELECT 절에 SUB QUERY가 있는 경우
SELECT ENAME, JOB, DEPTNO,
(SELECT DNAME FROM DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO)DNAME FROM EMP;출력 결과
ENAME JOB DEPTNO DNAME ---------- --------- ---------- -------------- SMITH CLERK 20 RESEARCH ALLEN SALESMAN 30 SALES WARD SALESMAN 30 SALES JONES MANAGER 20 RESEARCH MARTIN SALESMAN 30 SALES BLAKE MANAGER 30 SALES CLARK MANAGER 10 ACCOUNTING SCOTT ANALYST 20 RESEARCH KING PRESIDENT 10 ACCOUNTING TURNER SALESMAN 30 SALES ADAMS CLERK 20 RESEARCH JAMES CLERK 30 SALES FORD ANALYST 20 RESEARCH MILLER CLERK 10 ACCOUNTING
▷ 테이블 복제
: () 안에 SUB QUERY 사용 X
: PRIMARY KEY 등의 CONSTRAINTS까지는 복제 X
→ 테이블의 내용까지 복제
CREATE TABLE EMP2 AS SELECT * FROM EMP;→ 테이블의 구조만 복제
: WHERE 절을 불만족시킬 시 가능
CREATE TABLE EMP3 AS SELECT * FROM EMP WHERE 1 = 2;▷ UPDATE
UPDATE EMP2 SET JOB2 = JOB || ' ' || (SELECT DNAME FROM DEPT WHERE EMP2.DEPTNO = DEPTNO);실행 결과
ENAME JOB JOB2 ---------- --------- ---------------------- SMITH CLERK CLERK RESEARCH ALLEN SALESMAN SALESMAN SALES WARD SALESMAN SALESMAN SALES JONES MANAGER MANAGER RESEARCH MARTIN SALESMAN SALESMAN SALES BLAKE MANAGER MANAGER SALES CLARK MANAGER MANAGER ACCOUNTING SCOTT ANALYST ANALYST RESEARCH KING PRESIDENT PRESIDENT ACCOUNTING TURNER SALESMAN SALESMAN SALES ADAMS CLERK CLERK RESEARCH JAMES CLERK CLERK SALES FORD ANALYST ANALYST RESEARCH MILLER CLERK CLERK ACCOUNTING
▷ DELETE
DELETE FROM EMP2 WHERE DEPTNO = (SELECT DEPTNO FROM DEPT WHERE DNAME = 'RESEARCH');실행 결과
ENAME JOB2 DEPTNO ---------- ---------------------- ---------- ALLEN SALESMAN SALES 30 WARD SALESMAN SALES 30 MARTIN SALESMAN SALES 30 BLAKE MANAGER SALES 30 CLARK MANAGER ACCOUNTING 10 KING PRESIDENT ACCOUNTING 10 TURNER SALESMAN SALES 30 JAMES CLERK SALES 30 MILLER CLERK ACCOUNTING 10
▷ HAVING
SELECT JOB, SUM(SAL) FROM EMP GROUP BY JOB
HAVING SUM(SAL) >= (SELECT SAL FROM EMP WHERE JOB = 'PRESIDENT');출력 결과
JOB SUM(SAL) --------- ---------- SALESMAN 5600 MANAGER 8275 ANALYST 6000 PRESIDENT 5000
▶ 단일행 SUB QUERY
: SELECT 결과가 하나의 행
▷ 단일행 조회의 예
SELECT * FROM EMP WHERE ENAME = 'SMITH';출력 결과
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- -------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 80/12/17 800 20
▶ 다중행 SUB QUERY
: SELECT 결과가 여러 행
▷ 다중행 조회의 예
SELECT * FROM EMP WHERE SAL IN (800, 1250, 3000);출력 결과
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- -------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 80/12/17 800 20 7521 WARD SALESMAN 7698 81/02/22 1250 500 30 7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30 7788 SCOTT ANALYST 7566 82/12/09 3000 20 7902 FORD ANALYST 7566 81/12/03 3000 20
▷ 단일행, 다중행 여부에 따라 조회 방법이 다름
VIEW
: 가상의 테이블 생성 방법
: 실제 테이블에 접근 제한 방법
→ 테이블을 공유하면서도 일부 COLUMN에 대해 접근을 제한할 때 사용
▶ 용법
CREATE VIEW VIEW_NAME AS [SUB_QUERY];
DROP VIEW VIEW_NAME;
CREATE OR REPLACE VIEW VIEW_NAME AS [SUB_QUERY];▷ CREATE OR REPLACE
: 없으면 새로 만들고, 있으면 수정하라는 명령어
▶ INSERT, UPDATE, DELETE
: VIEW를 생성할 때, 선택한 실제 테이블도 변경
▷ INSERT
: 원래 테이블에서 가져오지 않은 COLUMN이 NOT NULL이 아닐 것
→ NOT NULL일 경우, INSERT 사용 X
▷ UPDATE
: VIEW를 통해 조회할 수 있는 COLUMN은 UPDATE 가능
▷ DELETE
▶ JOIN VIEW
: 여러 테이블을 JOIN하여 만들어진 VIEW
: INSERT, UPDATE, DELETE 가능
→ INSERT, DELETE는 하나의 테이블만 가능
→ UPDATE는 VIEW를 만들 때 SELECT 된 COLUMN만 가능
→ VIEW 생성시 SELECT 한 테이블도 변경
▶ WITH READ ONLY
: 읽기 전용 VIEW
: VIEW를 통한 INSERT, UPDATE, DELETE를 제한
CREATE OR REPLACE VIEW VIEW_DELIVERY3 AS
SELECT 주문번호, 배송지, 고객이름, 주문일자, 제품명, 수량 FROM 주문, 고객, 제품
WHERE 주문.주문고객 = 고객.고객아이디 AND 주문.주문제품 = 제품.제품번호
WITH READ ONLY;▶ WITH CHECK OPTION
: VIEW를 만든 SELECT문의 WHERE 조건절에서 사용된 속성의 값은 UPDATE 불가
CREATE OR REPLACE VIEW EMP4VIEW4 AS
SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO FROM EMP4
WHERE DEPTNO = 30 OR JOB = 'MANAGER' WITH CHECK OPTION;
-- EMP4VIEW4에서 DEPTNO가 30인 데이터의 DEPTNO의 변경, JOB이 MANAGER인 데이터의 JOB 변경 불가PL/SQL
▶ 용법
▷ 1단계 : 기본 형태
BEGIN
DBMS_OUTPUT.PUT_LINE('HELLO');
END;
/출력 결과
HELLO PL/SQL 프로시저가 성공적으로 완료되었습니다.
▷ 2단계 : 변수 사용
: DECLARE와 BEGIN 사이에 변수 선언
: 대입 연산자는 :=
DECLARE
TEST_EMPNO NUMBER(5);
TEST_ENAME VARCHAR2(20);
BEGIN
TEST_EMPNO := 9999;
TEST_ENAME := '철수';
DBMS_OUTPUT.PUT_LINE(TEST_EMPNO || ' ' || TEST_ENAME);
END;
/출력 결과
9999 철수 PL/SQL 프로시저가 성공적으로 완료되었습니다.
▷ 3단계 : USER DEFINED DATA TYPE
: TABLE_NAME.COLUMN_NAME%TYPE
→ EX) EMP.ENAME%TYPE : EMP 테이블의 ENAME을 정의한 DATA TYPE 사용
DECLARE
TEST_EMPNO EMP.EMPNO%TYPE; --NUMBER(4)
TEST_ENAME EMP.ENAME%TYPE; --VARCHAR2(10)
BEGIN
TEST_EMPNO := 9999;
TEST_ENAME := '철수';
DBMS_OUTPUT.PUT_LINE(TEST_EMPNO || ' ' || TEST_ENAME);
END;
/출력 결과
9999 철수 PL/SQL 프로시저가 성공적으로 완료되었습니다.
