IT 개발입문 공부정리

CURSOR

[형식] - 단계
  1. CURSOR  cursor_name is query; : 하나 이상의 ROW를 담을 객체
  2. OPEN cursor_name; : 실제 실행 코드에서 커서를 시작한다
  3. LOOP, FETCH를 이용해서 값을 선언된 변수에 대입
  4. CLOSE cursor_name; : 객체를 닫는다. 

명시적 커서 확인(SQL%~)
IF SQL%FOUND THEN
    DBMS_OUTPUT.PUT_LINE('검색한 데이터가 존재한다  ' || V_SAL);
END IF;

CURSOR를 만들어서 SELECT 결과를 저장한 다음, 오픈하고 코드, 클로드한다.
1단계에서 그냥 query만 작성하면 SQL%FOUND, NOTFOUND 등 CURSOR의 속성으로 호출가능

함수는 RETURN에 있는 값을 리턴하지만 
프로시져는 OUT 매개변수에 있는 매개변수를 알아서 리턴

1. SQL%ROWCOUNT : 해당 SQL문에 영향을 받는 행의 수
2. SQL%FOUND : 해당 SQL문에 영향을 받는 행의 수가 1개 이상일 경우 TRUE를 리턴
3. SQL%NOTFOUND : 해당 SQL문에 영향을 받는 행의 수가 없으면 TRUE를 리턴
4. SQL%ISOPEN : 항상 FALSE, 암시적 커서가 열려있는지 유무

매개변수로 CURSOR를 받는 경우
[형식]
CREATE OR REPLACE PROCEDURE 프로시져명 ( 매개변수명 IN OUT SYS_REFCURSOR )
OPEN 매개변수명 FOR SELECT EMPNO, ENAME FROM EMP WHERE JOB = P_JOB;

============================================================================

Q1) 부서번호 20번인 사원의 부서번호 이름, 봉급을 구하는 프로시저를 만들어보자
SELECT EMPNO, ENAME, SAL FROM EMP WHERE DEPTNO = 20;

CREATE OR REPLACE PROCEDURE EMP_EX01 AS 

    -- SELECT에서 리턴 받을 변수 선언
    V_EMPNO EMP.EMPNO%TYPE;
    V_ENAME EMP.ENAME%TYPE;
    V_SAL NUMBER(7,2);
    
    -- 1. CURSOR  cursor_name is query; : 하나 이상의 ROW를 담을 객체
    CURSOR EMP_CURSOR IS 
        SELECT EMPNO, ENAME, SAL 
        FROM EMP 
        WHERE DEPTNO = 20;

BEGIN
    -- 2. OPEN cursor_name; : 실제 실행 코드에서 커서를 시작한다
    OPEN EMP_CURSOR;

    -- 3. LOOP, FETCH를 이용해서 값을 선언된 변수에 대입
        LOOP
            FETCH EMP_CURSOR INTO V_EMPNO, V_ENAME, V_SAL;
            EXIT WHEN EMP_CURSOR%NOTFOUND OR EMP_CURSOR%ROWCOUNT > 5;

            DBMS_OUTPUT.PUT_LINE(V_EMPNO);
            DBMS_OUTPUT.PUT_LINE(V_ENAME);
            DBMS_OUTPUT.PUT_LINE(V_SAL);

        END LOOP;

    -- 4. CLOSE cursor_name; : 객체를 닫는다. 
    CLOSE EMP_CURSOR;
END EMP_EX01;


Q2) 사원테이블에서 사원의 이름과 봉급을 출력해보자
SELECT ENAME, SAL FROM EMP;

CREATE OR REPLACE PROCEDURE EX_VIEW AS
    V_ENAME EMP.ENAME%TYPE;
    V_SAL EMP.SAL%TYPE;

    CURSOR C_VIEW IS SELECT ENAME, SAL FROM EMP;

BEGIN
    OPEN C_VIEW;
        LOOP
            FETCH C_VIEW INTO V_ENAME, V_SAL;
            EXIT WHEN C_VIEW%NOTFOUND;
            DBMS_OUTPUT.PUT_LINE(V_ENAME || V_SAL);

        END LOOP;
    CLOSE C_VIEW;
END EX_VIEW;


Q3) 사원테이블에서 커미션이 측정된 사원의 이름, 봉급, 커미션을 출력해보자 EX_VIEW01;
CREATE OR REPLACE PROCEDURE EX_VIEW01 AS 
    R_EMP EMP%ROWTYPE;

    CURSOR RES IS
        SELECT *    -- 1
        FROM EMP
        WHERE COMM IS NOT NULL;

BEGIN
    OPEN RES;
        LOOP
            FETCH RES INTO R_EMP;    -- 2  : 1이랑 2개 개수 같아야 함
            EXIT WHEN RES%NOTFOUND;
            
            dbms_output.put(R_EMP.ENAME || '        ');
            dbms_output.put(R_EMP.SAL || '        ');
            dbms_output.put_line(R_EMP.COMM);
        END LOOP;
    CLOSE RES;
END EX_VIEW01;


Q4) 부서번호를 커서로 전달해서 해당 사원의 번호, 이름, 봉급을 출력해보자
SELECT EMPNO, ENAME, SAL
FROM EMP
WHERE DEPTNO = 10;

CREATE OR REPLACE PROCEDURE EMP_EX04 AS 
    V_EMPNO EMP.EMPNO%TYPE;
    V_ENAME EMP.ENAME%TYPE;
    V_SAL NUMBER(7,2);

    CURSOR EMP_CURSOR (V_DEPTNO NUMBER) IS
        SELECT EMPNO, ENAME, SAL
        FROM EMP
        WHERE DEPTNO = V_DEPTNO;

BEGIN
    OPEN EMP_CURSOR(10);
        LOOP
            FETCH EMP_CURSOR INTO V_EMPNO, V_ENAME, V_SAL;
            EXIT WHEN EMP_CURSOR%NOTFOUND;
            
            DBMS_OUTPUT.PUT(V_EMPNO || '    ');
            DBMS_OUTPUT.PUT(V_ENAME || '    ');
            DBMS_OUTPUT.PUT_LINE(V_SAL || '    ');
        END LOOP;
    CLOSE EMP_CURSOR;
        
    OPEN EMP_CURSOR(10);
        LOOP
            FETCH EMP_CURSOR INTO V_EMPNO, V_ENAME, V_SAL;
            EXIT WHEN EMP_CURSOR%NOTFOUND;
            
            DBMS_OUTPUT.PUT(V_EMPNO || '    ');
            DBMS_OUTPUT.PUT(V_ENAME || '    ');
            DBMS_OUTPUT.PUT_LINE(V_SAL || '    ');
        END LOOP;
    CLOSE EMP_CURSOR;
END EMP_EX04;


Q5) 사원번호를 입력받아 데이터를 확인하고 급여를 1.1배 하는 프로시져를 만들자.
CREATE OR REPLACE PROCEDURE EMP_EX05 (P_EMPNO IN EMP.EMPNO%TYPE) AS 
    V_SAL EMP.SAL%TYPE;
    V_UPDATE_ROW NUMBER;

BEGIN
    SELECT SAL INTO V_SAL
    FROM EMP
    WHERE EMPNO = P_EMPNO;
    
    IF SQL%FOUND THEN
        DBMS_OUTPUT.PUT_LINE('검색한 데이터가 존재한다  ' || V_SAL);
        
        UPDATE EMP
        SET SAL = SAL * 1.1
        WHERE EMPNO = P_EMPNO;
        
        V_UPDATE_ROW := SQL%ROWCOUNT;
        DBMS_OUTPUT.PUT_LINE('급여가 인상된 사원의 수  ' || V_UPDATE_ROW);
        
    END IF;
END EMP_EX05;


Q6) 사원번호를 입력해서 무슨 부서인지를 리턴 받아보자. 프로시져
CREATE OR REPLACE PROCEDURE EMP_EX06(P_EMPNO IN EMP.EMPNO%TYPE,
                                                                    V_RES OUT VARCHAR2) 
AS
    V_DEPTNO EMP.DEPTNO%TYPE;

BEGIN
    SELECT DEPTNO INTO V_DEPTNO
    FROM EMP
    WHERE EMPNO = P_EMPNO;
    
    IF V_DEPTNO = 10 THEN
        V_RES := '10번 부서 사원이야';
    ELSIF V_DEPTNO = 20 THEN
        V_RES := '20번 부서 사원이야';
    ELSIF V_DEPTNO = 30 THEN
        V_RES := '30번 부서 사원이야';
    END IF;
        
END EMP_EX06;

VAR RES VARCHAR2(50);
EXEC EMP_EX06(7900, :RES);
PRINT RES;


Q7) 이름을 입력하면 직업을 리턴하는 코드를 작성하자
CREATE OR REPLACE PROCEDURE EMP_EX07 
(
  MY_RES IN OUT VARCHAR2 
) AS 
    V_RES EMP.JOB%TYPE;
BEGIN
    SELECT JOB INTO V_RES
    FROM EMP
    WHERE ENAME = MY_RES;
    
    MY_RES := V_RES;
    
END EMP_EX07;

VAR RES02 VARCHAR2(50);
EXECUTE :RES02 := 'SMITH';
EXECUTE EMP_EX07(:RES02);
PRINT RES02;

Q8) 직업을 입력하면 사번과 이름을 포함하는 객체를 리턴받는 프로시져를 생성
create or replace PROCEDURE EMP_EX08 (p_job varchar2,
  P_EMP_REFCUR IN OUT SYS_REFCURSOR)
AS
BEGIN
  OPEN p_emp_refcur FOR
                    SELECT empno, ename FROM emp WHERE job = p_job;
END EMP_EX08;

VAR EX_08 REFCURSOR;
EXECUTE EMP_EX08('SALESMAN', :EX_08);
PRINT EX_08;


Q9) Q8을 이용해서 사번과 이름을 출력하는 프로시져를 생성
create or replace PROCEDURE EMP_EX09 AS 
V_EMPNO EMP.EMPNO%TYPE;
V_ENAME EMP.ENAME%TYPE;
V_JOB EMP.JOB%TYPE := 'SALESMAN';
V_EMP_REFCUR SYS_REFCURSOR;

BEGIN
    DBMS_OUTPUT.PUT_LINE('EMPLYEES WITH JOB ' || V_JOB );
    DBMS_OUTPUT.PUT_LINE('EMPNO     ENAME');
    DBMS_OUTPUT.PUT_LINE('============================');

    EMP_EX08(V_JOB, V_EMP_REFCUR);
    LOOP
        FETCH V_EMP_REFCUR INTO V_EMPNO, V_ENAME;
        EXIT WHEN V_EMP_REFCUR%NOTFOUND;
        
        DBMS_OUTPUT.PUT_LINE(V_EMPNO || '        ' || V_ENAME);
    
    END LOOP;     
    CLOSE V_EMP_REFCUR;
END EMP_EX09;

EXECUTE EMP_EX09;

강의목표

1. PL/SQL의 문법을 이해하고 실행할 수 있다. - FOR LOOP

============================================================================

[형식]
FOR 변수 IN 초기값..최종값 LOOP
    명령문;
END LOOP;

[형식]
LOOP ~무한 반복 명령~ END LOOP;  -- EXIT로 탈출

ACCEPT 변수 PROMPT '출력문장' -> 사용자 입력라인 값을 읽어서 변수에 저장 %TYPE
===========================================================================
PROCEDURE 프로시져란?
  - 특정 작업을 수행할 수 있고 이름이 있는 PL/SQL 블록으로서 매개변수를 받을 수도 있고
    반복적으로 사용할 수도 있다.

  - 보통 연속실행 또는 구현이 복잡한 트랜잭션을 수행하는 PL/SQL 블록을
    DB에 저장하기 위해서 생성한다.

트랜잭션 : 작업의 단위, 일련의 작업 등을 하나의 프로세서(EXE)로 취하는 것

CREATE PROCEDURE p_name(
                        IN args    -- 실행환경에서 값을 전달
                     OUT args    -- 값을 리턴
                 IN OUT args    -- 전달, 리턴한다.
)
IS
    변수 선언
BEGIN
    구문
END p_name;

예시)
CREATE PROCEDURE update_sal(v_empno in number)
IS
BEGIN
    UPDATE EMP SET SAL = SAL + 100
    WHERE EMPNO = v_empno;
    COMMIT;
END update_sal;
/    -- 생성완료

EXECUTE UPDATE_SAL(7369);    -- 프로시져 실행
EXEC UPDATE_SAL(7369); 


만들어진 STORED PROCEDURE LIST 보기
SELECT * FROM USER_PROCEDURES;


프로시저의 내용까지 확인
SELECT * FROM USER_SOURCE;


SELECT OBJECT_NAME, STATUS
FROM USER_OBJECTS
WHERE OBJECT_TYPE = 'VIEW';    -- WHERE OBJECT_TYPE = 'PROCEDURE';


SELECT OBJECT_NAME, STATUS
FROM USER_OBJECTS
WHERE OBJECT_TYPE = 'PROCEDURE';

============================================================================
FUNCTION 함수는 매개변수로 IN만 가능하다. 그 대신 RETURN이라는 키워드가 존재
그것이 프로시져랑 차이점

var res_sal number;    --  변수 선언

EXECUTE :res_sal := fc_update_sal(7369);    -- 함수 호출 시 리턴하는 값을 대입 후 실행

PRINT RES_SAL;    -- 내용 출력

============================================================================
예제)
type MY_REC is record (v_empno number,
                               v_ename varchar2(30),
                               v_hiredate date);
empr MY_REC;

~~ INTO empr.v_empno, empr.v_ename, empr.v_hiredate
from emp;

============================================================================
Q1) 1에서 10까지 반복하여 TEST1 테이블에 저장하라 (for문 사용)
SET SERVEROUTPUT ON

CREATE TABLE TEST1(bunho NUMBER(3),
                            irum VARCHAR2(10));

BEGIN
  for i in 1..10 loop
    INSERT INTO TEST1 VALUES (i, sysdate);
  end loop;
END;

SELECT * FROM TEST1;


Q2) 1~50까지 출력하자 [ 현재의 i값은 ? 00이다 ]  -> FOR LOOP
DECLARE
    i int := 0;
BEGIN
    FOR i IN 1..50 LOOP
        DBMS_OUTPUT.PUT_LINE('현재의 i 값은?  ' || i || ' 이다');
    END LOOP;
END;
/


Q3) 구구단에서 홀수 단만 출력해보자
DECLARE
    total number := 0;
BEGIN
    FOR i IN 2..9 LOOP
        IF MOD(i, 2) <> 0 THEN
            DBMS_OUTPUT.PUT_LINE(i || ' 단');

            FOR j IN 2..9 LOOP
                total := i*j;
                DBMS_OUTPUT.PUT_LINE(i || ' * ' || j || ' = ' || total);
            END LOOP;
            DBMS_OUTPUT.PUT_LINE('=========================');
        END IF;
    END LOOP;
END;
/


Q4) 1 ~ 50 출력해보자
DECLARE
    i int := 1;
BEGIN
    LOOP
        DBMS_OUTPUT.PUT_LINE('현재 i 값은  ' || i || '  입니다');
        i := i + 1;
        IF i > 50 THEN
            EXIT;
        END IF;
    END LOOP;
END;
/


Q5) EMP 테이블에서 부서번호 10인 사원 평균 급여를 출력하는 익명의 블록을 완성해보자.
SELECT AVG(SAL) FROM EMP WHERE DEPTNO=10 GROUP BY DEPTNO;

DECLARE
    avg01 NUMBER(7) := 0;
    deptno01 NUMBER(7) := 10;
BEGIN
    SELECT AVG(SAL) INTO avg01
    FROM EMP
    WHERE DEPTNO = deptno01
    GROUP BY DEPTNO;
    DBMS_OUTPUT.PUT_LINE(deptno01 || ' 번 부서의 평균 급여는 [ ' || avg01 || ' ] 원 입니다.');
END;
/

Q6) 20번 부서에 [5]명에 대한 평균 급여는 [2175]원이다. 익명으로 풀어보자.
     EMP 테이블에서 20번 부서의 개수, 평균 급여를 구하자.
SELECT COUNT(*), AVG(SAL) FROM EMP WHERE DEPTNO=20 GROUP BY DEPTNO;

DECLARE
    DNO NUMBER := 20;
    CNT NUMBER := 0;
    AVG_SAL NUMBER := 0;
BEGIN
    SELECT COUNT(*), AVG(SAL) INTO CNT, AVG_SAL
              FROM EMP WHERE DEPTNO=DNO GROUP BY DEPTNO;
    DBMS_OUTPUT.PUT_LINE('20번 부서 ' || CNT || ' 명에 대한 평균 급여는 ' || AVG_SAL || ' 원 입니다.');
END;
/


Q7) 사번을 입력해서 이름을 리턴받자.
ACCEPT INPUT_VAL
7902

DECLARE
    MY_NAME EMP.ENAME%TYPE;
BEGIN
    SELECT ENAME  INTO MY_NAME
    FROM EMP
    WHERE EMPNO = &INPUT_VAL;
    DBMS_OUTPUT.PUT_LINE('현재의 결과는 ' || MY_NAME);
END;
/


Q8) 30번 부서의 인원수는 [6]이고 평균급여 [777]이다. [D]등급이다.
조건 : 2500 이상이면 'A'
         2000 이상이면 'B'
         1500 이상이면 'C'
         1000 이상이면 'D'
                             'F'

ACCEPT DNO

DECLARE
    CNT NUMBER := 0;
    AVG_SAL NUMBER := 0;
    GRADE CHAR(1) := '';
BEGIN
    SELECT COUNT(*), AVG(SAL) INTO CNT, AVG_SAL
    FROM EMP 
    WHERE DEPTNO=&DNO 
    GROUP BY DEPTNO;

    IF AVG_SAL >= 2500 THEN GRADE := 'A';
        ELSIF AVG_SAL >= 2000 THEN GRADE := 'A';
        ELSIF AVG_SAL >= 1500 THEN GRADE := 'C';
        ELSIF AVG_SAL >= 1000 THEN GRADE := 'D';
        ELSE GRADE := 'F';
    END IF;

    DBMS_OUTPUT.PUT_LINE(&DNO || '부서의 인원수는 ' || CNT || '명 이다.');
    DBMS_OUTPUT.PUT_LINE('평균 급여는 ' || ROUND(AVG_SAL) || ' 원 이고, ' || GRADE || ' 등급이다.');
END;
/


Q9) 부서번호를 입력받아 사원의 번호, 이름 급여, 직업을 출력하는 프로시져 만들기
CREATE OR REPLACE PROCEDURE EX_PRO05( P_EMPNO IN EMP.EMPNO%TYPE)
AS
    V_EMP EMP%ROWTYPE;    -- 한 줄의 레코드 타입이 지정된다.
BEGIN
    SELECT EMPNO, ENAME, SAL, JOB INTO V_EMP.EMPNO, V_EMP.ENAME, V_EMP.SAL, V_EMP.JOB
    FROM EMP
    WHERE EMPNO = P_EMPNO;

    DBMS_OUTPUT.PUT_LINE(V_EMP.EMPNO);
    DBMS_OUTPUT.PUT_LINE(V_EMP.ENAME);
    DBMS_OUTPUT.PUT_LINE(V_EMP.SAL);
    DBMS_OUTPUT.PUT_LINE(V_EMP.JOB);
END EX_PRO05;


Q10) 사번을 입력받아서 월급에 100을 더하는 함수를 작성해보자
CREATE OR REPLACE FUNCTION FC_UPDATE_SAL(v_empno emp.empno%type)
RETURN NUMBER 
AS
    v_sal emp.sal%type;
BEGIN
    UPDATE EMP SET SAL = SAL + 100
    WHERE EMPNO = v_empno;
    COMMIT;

    SELECT SAL INTO v_sal
    FROM EMP
    WHERE EMPNO = v_empno;

    RETURN v_sal;
END FC_UPDATE_SAL;

var res_sal number;    --  변수 선언

EXECUTE :res_sal := fc_update_sal(7369);    -- 함수 호출 시 리턴하는 값을 대입 후 실행

PRINT RES_SAL;    -- 내용 출력


Q11) 사번을 입력받아 연봉을 계산(SAL*12 + COMM)해서 출력하는 함수를 작성하고 다음 문장을 실행
SELECT EMPNO, EX_GET_SAL(EMPNO) FROM EMP;

CREATE OR REPLACE FUNCTION EX_GET_SAL (V_ID EMP.EMPNO%TYPE)
RETURN NUMBER 
AS 
    GET_SAL NUMBER := 0;
BEGIN
    SELECT SAL*12+NVL(COMM,0) INTO GET_SAL
    FROM EMP
    WHERE EMPNO = V_ID;
    RETURN GET_SAL;
END EX_GET_SAL;


Q12) 사번을 입력받아 사원번호 사원이름 입사일을 출력하는 프로시져를 만들고 다음 문장을 실행
EXEC EX_PRO06(7369);

CREATE OR REPLACE PROCEDURE EX_PRO06 (ENO NUMBER)
AS 
    E_NAME VARCHAR2(20) := ' ';
    E_DATE DATE := SYSDATE;
BEGIN
    SELECT ENAME, HIREDATE INTO E_NAME, E_DATE
    FROM EMP
    WHERE EMPNO = ENO;
    
    DBMS_OUTPUT.PUT_LINE('사원번호 : ' || ENO);
    DBMS_OUTPUT.PUT_LINE('사원이름 : ' || E_NAME);
    DBMS_OUTPUT.PUT_LINE('입사일 : ' || E_DATE);
END EX_PRO06;

강의목표

1.VIEW의 개념을 이해하고 기술할 수 있다.
2. PL/SQL의 문법을 이해하고 실행할 수 있다.

============================================================================
뷰
  - 다른 테이블이나 뷰에 포함된 데이터의 맞춤 표현 Tailored Presentation

  - STORED QUERY VIRTUAL TABLE로 간주되는 DB객체

  - 하나 또는 하나 이상의 테이블 / 뷰에 포함된 데이터 부분 집합을 나타내는 논리적인 객체
        -> 선택적인 정보만 제공 가능

  - 자체적인 데이터를 포함하지 않는다.

  - 베이스테이블 : 뷰를 통해 보여지는 데이터를 포함하고 있는 실제 테이블

============================================================================
사용목적 및 장점
  1. Restricted data access
        뷰에 접근하는 사용자는 미리 정의된 결과만 볼 수 있음
        ==> 데이터 접근을 제한함으로써 중요한 데이터를 보호할 수 있다.

  2. Hide data complexity
        여러 테이블을 조인하는 등 복잡한 SQL 구문을 사용하는 경우
        자세한 SQL 구문의 내용을 숨길 수 있다.

  3. Simplify statement for the user
        복잡한 SQL 구문을 모르는 사용자라도 SELECCT 구문만으로 결과를 조회할 수 있다.

  4. Present the data in different perspective
        뷰에 포함된 컬럼은 참조 대상 테이블에 영향을 주지 않고 다른 이름으로 참조 가능하다.

  5. Isolate applications from changes in definitions of base tables
        베이스테이블에 포함된 여러 개의 컬럼 중 일부만 사용하도록 뷰를 생성한 경우, 
        뷰가 참조하지 않는 나머지 컬럼이 변경되어도 뷰를 사용하는 다른 프로그램들은 영향X

  6. Save complex queries
        복잡한 SQL 구문을 뷰 형태로 저장해서 반복적으로 사용이 가능하다.

============================================================================
생성구문
CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW view_name[ (alias ,,, ) ]
AS subquery
[WITH CHECH OPTION [CONSTRAINT constraint_name] ]
[WITH READ ONLY [CONSTRAINT constraint_name] ]

  - CREATE [OR REPLACE] : 지정한 뷰가 없으면 새로 생성, 동일 이름이 존재하면 수정
  - FORCE : 베이스테이블이 존재하지 않아도 뷰 생성 가능
  - NOFORCE : 베이스테이블이 존재해야만 뷰 생성 가능

  - alias : 뷰에서 사용되는 컬럼명, 생략하게 되면 subquery가 사용한 컬럼명을 사용한다.
             subquery에서 사용한 SELECT LIST 개수와 일치
  - 제약조건 : 제약조건으로 간주되므로 이름 지정 가능
      - WITH CHECH OPTION : 뷰를 통해서 접근 가능한 데이터에 대해서만 DML 작업 허용
                                   WHERE절에 안맞는 데이터가 베이스에 추가됐을 때 뷰에 반영할지 여부
      - WITH READ ONLY : 뷰를 통해 DML 작업을 허용 안함

============================================================================
VIEW - 데이터 조회 절차
VIEW를 사용한 SQL 구문해석
  - 데이터 딕셔너리 "USER_VIEWS"에서 뷰 정의 검색
  - SQL 구문을 실행한 계정이 관련된 베이스테이블에 접근하여 SELECT 할 수 있는 권한이 있는지 확인
  - 뷰 대신 베이스테이블을 기반으로 하는 동등한 작업으로 변환
  - 베이스테이블을 대상으로 데이터 조회

============================================================================
PL/SQL (Procedual Language / SQL / Oracle's Procedural Language extension to SQL)의 약어
  - 오라클 DB환경에서 실행되는 절차적인 데이터베이스 프로그래밍 언어

  - PL/SQL에서는 프로그램 단위를 BLOCK 이라고 부르며 애플리케이션 로직들을 작성

  - 변수정의, 조건처리, 반복문, Procedure Language 이다.

  - 모듈화 PL/SQL의 장점
<< label >> (optional)
DECLARE    -- Declarative part (optional)
  -- Declarations of local types, variables, & subprograms

BEGIN      -- Executable part (required)
  -- Statements (which can use items declared in declarative part)

[EXCEPTION -- Exception-handling part (optional)
  -- Exception handlers for exceptions (errors) raised in executable part]
END;

CMD에서 하려면 SET SERVEROUTPUT ON을 쳐줘야한다.
CMD에서 선언이후 실행은 / 이다.

=================================================================

예제
Q1) system 로그인해서 MYDB / admin1234 생성
create user MYDB identified by admin1234;


Q2) 권한은 도움말에서 그대로
grant CREATE SESSION, ALTER SESSION, CREATE DATABASE LINK, -
  CREATE MATERIALIZED VIEW, CREATE PROCEDURE, CREATE PUBLIC SYNONYM, -
  CREATE ROLE, CREATE SEQUENCE, CREATE SYNONYM, CREATE TABLE, - 
  CREATE TRIGGER, CREATE TYPE, CREATE VIEW, UNLIMITED TABLESPACE -
  to MYDB;


Q3) 로그인해서 VIEW 생성
CONN MYDB/admin1234

CREATE OR REPLACE VIEW V_EMP
AS 
SELECT ENAME, DEPTNO
FROM EMP
WHERE DEPTNO = 30;


Q4) VIEW 생성 정보를 확인해보자. USER_TAB_COLS
SELECT COLUMN_NAME, DATA_TYPE, NULLABLE
FROM USER_TAB_COLS
WHERE TABLE_NAME = 'V_EMP';


Q5) 조인으로 뷰를 만들어보자
CREATE OR REPLACE VIEW V_EMP_DEPT
AS 
SELECT ENAME, DNAME, JOB
FROM EMP LEFT JOIN DEPT USING (DEPTNO);

SELECT *
FROM V_EMP_DEPT;


Q6) ALIAS를 이용하여 테이블 컬럼에 해당하는 VIEW 객체를 생성하자
CREATE OR REPLACE VIEW V_EMP_DEPT02(M_NAME, M_DNAME, M_JOB)
AS 
SELECT ENAME, DNAME, JOB
FROM EMP LEFT JOIN DEPT USING (DEPTNO);

SELECT *
FROM V_EMP_DEPT02;


Q7) V_EMP 뷰의 내용 확인
SELECT *
FROM V_EMP;


Q8) 베이스테이블에 값을 추가하자
INSERT INTO EMP(EMPNO, ENAME, DEPTNO) 
VALUES(111, '111', 30);


Q9) 베이스테이블의 값 변경이 뷰에 반영되었는지 객체를 확인해보자
SELECT *
FROM V_EMP;    --> 베이스테이블에 추가된 데이터를 확인할 수 있다.


Q10) VIEW에 값을 추가해보자    --> 실패
INSERT INTO V_EMP(ENAME, DEPTNO)
VALUES('222', 30);    --> EMP TABLE의 PK에 걸려서 추가되지 않는다.


Q11) VIEW의 값을 수정해보고 베이스테이블에 반영되는데 확인해보자
UPDATE V_EMP
SET DEPTNO=NULL;

SELECT *
FROM EMP;    --> VIEW에서 변경한 값이 베이스테이블에 반영된다.

ROLLBACK;    --> VIEW에도 롤백이 적용된다.


Q12) WITH READ ONLY를 이용한 제약조건을 포함하여 VIEW 생성
CREATE OR REPLACE VIEW V_EMP
AS
SELECT *
FROM EMP
WITH READ ONLY;

UPDATE V_EMP
SET DEPTNO=NULL;    --> READ ONLY라 에러 발생


Q13) 테스트용 베이스테이블 생성
CREATE TABLE EMP_TEST
AS
SELECT ENAME, SAL
FROM EMP
WHERE 1=0;

ALTER TABLE EMP_TEST
ADD MARRIAGE CHAR(2);

DESC EMP_TEST;

INSERT INTO EMP_TEST VALUES('111', 3000, 'Y');
INSERT INTO EMP_TEST VALUES('222', 4000, 'Y');
INSERT INTO EMP_TEST VALUES('333', 7000, 'N');
INSERT INTO EMP_TEST VALUES('444', 3000, 'Y');
INSERT INTO EMP_TEST VALUES('555', 5500, 'N');
INSERT INTO EMP_TEST VALUES('666', 10000, 'Y');


Q14) 옵션을 지정한 다음 DML을 확인하자
CREATE OR REPLACE VIEW V_EMP
AS
SELECT *
FROM EMP_TEST
WHERE MARRIAGE = 'N'
WITH CHECK OPTION;    --> WHERE 문의 조건에 맞는 값을 체크해서 DML을 수행

INSERT INTO EMP_TEST VALUES('777', 80000, 'Y');
SELECT * FROM EMP_TEST;    --> 베이스테이블은 데이터가 추가된다.
SELECT * FROM V_EMP;    --> 베이스테이블엔 안들어 간다.

INSERT INTO EMP_TEST VALUES('888', 800, 'N');
SELECT * FROM EMP_TEST;    --> 베이스테이블은 데이터가 추가된다.
SELECT * FROM V_EMP;    --> 베이스테이블에 들어간다.


Q15) USER_VIEWS
SELECT VIEW_NAME, TEXT
FROM USER_VIEWS
WHERE VIEW_NAME = 'V_EMP';


Q16) 별칭에 "" 구현하자
CREATE OR REPLACE VIEW V_EMP_DEPT02("M_NAME", "M_DNAME", "M_JOB")
AS 
SELECT ENAME, DNAME, JOB
FROM EMP LEFT JOIN DEPT USING (DEPTNO);

SELECT M_NAME, M_JOB
FROM V_EMP_DEPT02
WHERE "M_JOB" = 'CLERK';    --> 별칭에 ""를 줬으면 조건 검색할 때 줘야할 수도 있다.


Q18) 뷰를 삭제해보자
DROP VIEW V_EMP_DEPT02;


Q19) View를 이용해서 부서별 평균 월급보다 더 많은 월급을 받는 사원과 월급을 출력해보자.
CREATE OR REPLACE VIEW V_DEPT_SALAVG(D_ID, D_AVG)
AS
SELECT NVL(DEPTNO, 10), ROUND(AVG(SAL), -1)
FROM EMP
GROUP BY DEPTNO;

SELECT * FROM V_DEPT_SALAVG;

SELECT ENAME, SAL
FROM EMP
JOIN V_DEPT_SALAVG ON (NVL(DEPTNO, 10) = D_ID)
WHERE SAL > D_AVG
ORDER BY 2 DESC;


Q20) 인라인 뷰(Inline View)를 이용해서 부서별 평균 월급보다 더 많은 월급을 받는 
       사원과 월급을 출력해보자.
SELECT ENAME, SAL
FROM (SELECT NVL(DEPTNO, 10) AS D_ID, ROUND(AVG(SAL), -1) AS D_AVG
          FROM EMP
          GROUP BY DEPTNO) V_DEPT_SALAVG
JOIN EMP ON (NVL(DEPTNO, 10) = V_DEPT_SALAVG.D_ID)
WHERE SAL > D_AVG
ORDER BY 2 DESC;

1. 제약조건에 대해 살펴 보고 컬럼 레벨과 테이블 레벨을 통해 구현할 수 있다.
2. 제약조건을 수정하고 삭제할 수 있다.
3. 제약조건의 정보를 확인할 수 있다.

============================================================================

제약조건
  - NOT NULL : 해당 컬럼에 NULL을 포함하지 않도록 선언 (컬럼)
  - UNIQUE : 해당 컬럼 또는 컬럼 조합 값이 유일하도록 함 (컬럼, 테이블)
  - PRIMARY KEY : 식별 값, UNIQUE + NOT NULL (컬럼, 테이블)
  - REFERENCES TABLE(COLUMN) : 해당 컬럼이 참조하고 있는 부모 테이블의 특정 컬럼 값들과
                                              일치하거나 NULL이 되도록 보장한다. (컬럼, 테이블)
  - CHECK : 해당 컬럼에 특정 조건을 만족시키도록 한다. (컬럼, 테이블)

============================================================================
제약 조건 확인 : DESC USER_CONSTRAINTS;
  - CONSTRAINT_NAME : 제약조건 이름
  - CONSTRAINT_TYPE : 유형 
        - P : PRIMARY KEY
        - U : UNIQUE
        - R : REFERENCE
        - C : CHECK
  - TABLE_NAME : 테이블 이름
  - SEARCH_CONDITION : CHECK 제약조건 내용
  - R_CONSTRAINT_NAME : 참조 테이블의 PRIMARY KEY 이름
  - DELETE_RULE : 참조 테이블의 PRIMARY KEY 컬럼이 삭제될 때 사용되는 규칙
        - NO ACTION, SET NULL, CASCADE 등

============================================================================
삭제 룰
  - ON DELETE CASCADE : 대상 데이터를 삭제하고, 해당 데이터를 참조하는 데이터도 삭제
  - ON DELETE SET NULL : 대상 데이터를 삭제하고, 해당 데이터를 참조하는 데이터는 NULL로 바꿈
  - ON DELETE RESTRICTED : 삭제 대상 데이터를 참조하는 데이터가 존재하면 삭제 불가(DEFAULT)

수정 룰
  - ON UPDATE CASCADE : 대상 데이터를 삭제하고, 해당 데이터를 참조하는 데이터도 수정

============================================================================

[형식]
[테이블 생성]
CREATE TABLE 테이블명(
    컬럼명1 데이터타입 [제약조건] ,,,,
);

[테이블 수정]
ALTER TABLE 테이블명
ADD 컬럼명 데이터타입 [제약조건]
ADD CONSTRAINT 제약조건명 제약조건(컬럼명)

MODIFY 컬럼명 데이터타입
DROP COLUMN 컬럼명 [CASCADE CONSTAINTS]
DROP PRIMARY KEY [CASCADE] | UNION(컬럼명 ,,,, ) [CASCADE] | CONSTRAINT 제약조건명[CASCADE]

[이름 변경]
ALTER TABLE 기본 테이블명 RENAME TO 새 테이블명
RENAME 기존 테이블명 TO 새 테이블명

ALTER TABLE 테이블명 RENAME COLUMN 기본컬럼명 TO 새 컬럼명
ALTER TABLE 테이블명 RENAME CONSTRAINT 기존 제약조건명 TO 새 제약조건명

[테이블 복사]
  - 서브 쿼리를 이용한 테이블 생성 및 행 (레코드) 복사
  - 서브 쿼리를 이용해서 복사한 경우 NOT NULL을 제외한 제약조건은 복사 안됨
     (NOT NULL 제약조건도 SYS_****으로 복사된다.)
  - CREATE TABLE 테이블명[컬럼명 ,,,, ] AS 서브쿼리
  - CREATE TABLE 테이블명1 AS SELECT * FROM 테이블명 WHERE 1=0;

============================================================================
FOREIGN KEY : 참조 테이블의 컬럼값과 일치하거나 NULL 상태를 유지하도록 해야한다.

컬럼레벨 생성 : LID는 MY_DEPT의 PRIMARY KEY 컬럼을 참조한다.
REFERENCES 참조 테이블 [(참조 컬럼)] : 생략가능한 이유는 어차피 PRIMARY KEY를 참조하기 때문
CREATE TABLE TABLE_FK(
    ID CHAR(3),
    SNAME VARCHAR2(20),
    LID NUMBER(2) REFERENCES MY_DEPT (DEPTNO)  -- REFERENCES 참조 테이블 [(참조 컬럼)]
);

테이블레벨 생성
CREATE TABLE TABLE_FK(
    ID CHAR(3),
    SNAME VARCHAR2(20),
    LID NUMBER(2) 
CONSTRAINT FFFF FOREIGN KEY(LID) 
                  REFERENCES MY_DEPT (DEPTNO)  -- REFERENCES 참조 테이블 [(참조 컬럼)]
);

DELETE OPTION
제약조건을 생성할 때, 참조 컬럼 값이 삭제 되는 경우 FOREIGN KEY 컬럼 값을
어떻게 처리할 것인지 지정 가능하다.

============================================================================
SEQUENCE 시퀀스 : 순차적으로 자동으로 정수 값을 생성하는 객체

CREATE SEQUENCE sequence_name
[ INCREMENT BY N ] [ START WITH N ] 
[ {MAXVALUE N | NOMAXVALUE} ],[ MINVALUE | NOMINVALUE ]
[ {CYCLE | NOCYCLE} ] [ CACHE N | NOCACHE ]

NEXTVAL, CURRVAL

ex)
CREATE SEQUENCE MY_SEQ
START WITH 300
INCREMENT BY 5
MXVALUE 310
NOCYCLE
NOCACHE

SELECT MY_SEQ.NEXTVAL FROM DUAL;

CYCLE로 했을 때는 START WITH 값이랑 관계없이 1로 시작

============================================================================
예제 모음
Q1) EMP 테이블의 테이블 이름, 제약조건, 제약조건타입을 확인해보자

SELECT TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'EMP';


Q2) DEPT 테이블의 테이블 이름, 제약조건, 제약조건타입을 확인해보자

SELECT TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'DEPT';


Q3) 테이블 생성

CREATE TABLE TEST(
    ID NUMBER(5),
    NAME CHAR(10),
    ADDRESS VARCHAR2(50)
);

DESC TEST;


Q4) ID컬럼을 USR 컬럼으로 변경해보자

ALTER TABLE TEST
RENAME COLUMN ID TO USR;


Q5) TEST 테이블의 이름을 EXAM_TEST 테이블로 변경해보자

ALTER TABLE TEST
RENAME TO EXAM_TEST;

DESC EXAM_TEST;


Q6) EXAM_TEST 테이블을 삭제하고 휴지통 비우기

CREATE TABLE TEST
AS 
SELECT * FROM EXAM_TEST;  -- EXAM_TEST 복사해서 TEST 생성

CREATE TABLE TEST01
AS 
SELECT * FROM EXAM_TEST;  -- EXAM_TEST 복사해서 TEST01 생성

DROP TABLE EXAM_TEST;  -- EXAM_TEST 삭제

SELECT * FROM RECYCLEBIN;  -- 캐시메모리 버퍼, 휴지통에 EXAM_TEST 있는 것 확인
DESC RECYCLEBIN;

PURGE RECYCLEBIN;  -- 휴지통 비우기

DROP TABLE TEST01 PURGE;  -- 휴지통으로 가지않고 바로 삭제


Q7) 다양한 제약조건을 지정한 테이블을 만들어보자

CREATE TABLE USER1(
    IDX NUMBER PRIMARY KEY,
    ID VARCHAR2(10) UNIQUE,
    NAME VARCHAR2(10) NOT NULL,
    PHONE VARCHAR2(15),
    ADDRESS VARCHAR2(50),
    SCORE NUMBER(6, 2) CHECK (SCORE >= 0 AND SCORE <= 100),
    SUBJECT_CODE NUMBER(5),
    HIREDATE DATE DEFAULT SYSDATE,
    MARRIAGE CHAR(1) DEFAULT 'N' CHECK(MARRIAGE IN ('Y', 'N'))
);


Q8) 제약조건 확인

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, SEARCH_CONDITION
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'USER1';


Q9) 값을 입력해서 제약조건 위배를 생각해보자

INSERT INTO USER1 VALUES(1, 'AAA', 'KIM', '010-0000-0000', '서울', 75, 100, '2021-05-28', 'N');
INSERT INTO USER1 VALUES(2, 'BBB', 'KIM', '010-0000-0000', '서울', 75, 100, '2021-05-28', 'N');
INSERT INTO USER1 VALUES(3, 'CCC', 'KIM', '010-0000-0000', '서울', 100, 100, '2021-05-28', 'N');
INSERT INTO USER1 VALUES(4, 'DDD', 'KIM', '010-0000-0000', '서울', 100, 100, '2021-05-28', 'N');


Q10) TEST의 USR을 다시 ID로 변경하자

ALTER TABLE TEST
RENAME COLUMN USR TO ID;


Q11) TEST 테이블에 ID에 제약조건(TEST_ID_PK)을 추가하자

ALTER TABLE TEST
ADD CONSTRAINT TEST_ID_PK PRIMARY KEY(ID);

SELECT TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE, SEARCH_CONDITION
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'TEST';


Q12) TEST의 기본 키를 삭제해보자

ALTER TABLE TEST DROP PRIMARY KEY;


Q13) ID에다가 UNIQUE 제약을 걸자 UID : 제약조건 이름 안댐 예약어인듯?

ALTER TABLE TEST
ADD CONSTRAINT TEST_U_ID UNIQUE(ID);


Q14) NAME CHAR(10)   --> VARCHAR2(10)로 변경해보자
ALTER TABLE TEST
MODIFY NAME VARCHAR2(10);


Q15) PKTABLE을 생성한 후 데이터를 입력해보자
CREATE TABLE PKTABLE(
    ID NUMBER CONSTRAINT PKTABLE_ID_PK PRIMARY KEY,
    NAME VARCHAR2(10)
);


Q16) PKTABLE의 제약조건을 삭제하자


Q17) PKTABLE의 제약조건을 추가해보자



Q18)

CREATE TABLE UTABLE(
    ID NUMBER CONSTRAINT U_ID_U UNIQUE,
    NAME VARCHAR2(10)
);

INSERT INTO UTABLE VALUES(1, '111');
INSERT INTO UTABLE VALUES(NULL, '111');
INSERT INTO UTABLE VALUES(NULL, '111');
INSERT INTO UTABLE VALUES(NULL, '111');

DELETE FROM UTABLE WHERE ID IS NULL;


Q19) UTABLE의 제약조건을 삭제한다.

ALTER TABLE UTABLE
DROP CONSTRAINTS U_ID_U;


Q20) 동일한 이름으로 (U_ID_U) UTABLE ID와 NAME으로 테이블 레벨로 복합키를 만들자

ALTER TABLE UTABLE ADD CONSTRAINT U_ID_U UNIQUE(ID,NAME);

INSERT INTO UTABLE VALUES(1, '111');
INSERT INTO UTABLE VALUES(NULL, '111');
INSERT INTO UTABLE VALUES(NULL, '111');


Q21) 테이블 생성 후 PK 연습해보자
  - 테이블 당 1개만 생성할 수 있다
  - UNIQUE + NOT NULL

CREATE TABLE TABLE_PK(
    ID CHAR(3) PRIMARY KEY,
    SNAME VARCHAR2(20)
);


Q22) 테이블 생성 후 PK 연습해보자 : 조합 컬럼 생성

CREATE TABLE TABLE_PK(
    ID CHAR(3) PRIMARY KEY,
    SNAME VARCHAR2(20)
);


Q23) MY_EMP, MY_DEPT의 두 테이블에 각각 PK를 추가한다.

CREATE TABLE MY_EMP
AS
SELECT * FROM EMP;

CREATE TABLE MY_DEPT
AS
SELECT * FROM DEPT;

ALTER TABLE MY_EMP
ADD CONSTRAINT ME_PK PRIMARY KEY(EMPNO);

ALTER TABLE MY_DEPT
ADD CONSTRAINT MD_PK PRIMARY KEY(DEPTNO);

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'MY_EMP' OR TABLE_NAME = 'MY_DEPT';

강의목표

1. commit, rollback, savepoint 를 이용한 트랜잭션 제어

2. 테이블 생성에 관한 구문을 이해하고 설정할 수 있다.
3. 제약 조건의 특징 및 동작을 이해하고, 적절한 제약 조건을 테이블에 설정할 수 있다.
4. VIEW, SEQUENCE, INDEX, SYNONYM을 생성, 변경 및 삭제할 수 있다.

============================================================================

Transaction이란 논리 작업 단위를 형성하는 DML문의 모음을 말한다.

트랜잭션이란?
  - 하나 혹은 두 개 이상의 SQL 문들로 이루어진 작업의 논리적인 단위

  - 하나의 트랜잭션 안의 모든 SQL은 동일한 효과로 성공하면 전부 성공하고 실패하면 전부 실패

  - 하나의 트랜잭션은 커밋(Commit)될 수도 있고 롤백(Rollback)될 수도 있다.

트랜잭션은 실행 가능한 첫 번째 SQL 문이 실행될 때 시작되어 다음 이벤트가 발생하면 종료된다.
  1. COMMIT 또는 ROLLBACK 문이 실행된 경우
  2. DCL (Data Control Language : GRANT, REVOKE) 문이 실행된 경우
  3. 사용자(Client)가 SQL*Plus를 종료하는 경우
  4. 시스템에 장애가 있거나 시스템이 고장 난 경우
  5. DDL문 (Data Definition Language : Create TABLE, ALTER, DROP, RENAME)이 실행된 경우

  - DDL 실패 시 ORACLE 은 DDL 문(데이터 정의어) 전후에도 암시적 COMMIT을 실행하므로 
    DDL문이 성공적으로 실행되지 않는 경우에도 서버가 실행한 커밋때문에 
    이전 명령문은 롤백 할 수 없다.

  - 트랜잭션이 종료되면 실행 가능한 다음 SQL 문이 다음 트랜잭션을 자동으로 시작한다.

============================================================================

명시적 Transaction 제어
  - COMMIT : 보류중인 모든 데이터의 변경 내용을 영구히 저장하고 현재의 트랜잭션을 종료.

  - SAVEPOINT NAME : 현재의 트랜잭션 내에 저장점을 표시

  - ROLLBACK : 보류중인 모든 데이터의 변경 내용을 버리고 현재의 트랜잭션을 종료.

  - ROLLBACK TO SAVEPOINT NAME : 현재의 트랜잭션을 지정된 저장점으로 ROLLBACK.


암시적 Transaction 제어
  - 자동 COMMIT : DDL, DCL 문이 수행되는 경우, iSQL*PLUS가 정상적으로 종료되는 경우

  - 자동 ROLLBACK : iSQL*PLUS가 비정상적으로 종료되는 경우,
                           시스템에 장애가 있을 경우

============================================================================

SAVEPOINT 사용
  - 하나의 트랜잭션 내에서 각각의 SQL 문이 실행되었던 시점을 구분하는데 사용된다.

  - 아주 긴 작업을 수행하다가 한 번의 실수로 모든 작업을 ROLLBACK 한다면 
    그 동안 작업 내용까지 모두 취소될 경우가 발생되어 예비책으로 사용된다.

  - 트랜잭션 중간 중간에 특정 시점을 표시해두면 실수한 트랜잭션만 취소할 수 있다.

============================================================================

LOCK : 동시에 수행되는 트랜잭션간에 파괴적인 상호작용을 방지한다.
          트랜잭션 수행기간 동안 LOCK은 유지된다.

>> 배타적 (exclusive lock) : 
    DML 문에서 수정하는 각행에 자동으로 배타적 잠금이 수행된다.
    트랜잭션이 종료될 때까지 다른 트랜잭션에서 해당 행을 변경하지 못하도록 한다.
    이 잠금은 다른 사용자가 동시에 동일한 행을 수정하지 못하도록 하고 
    아직 완료되지 않은 변경 내용을 다른 사용자가 덮어쓰지 못하도록 한다.

>> 공유 (share lock) :
    DML 작업도중 테이블 레벨에서 자동으로 수행된다.
    공유 잠금 모드에서는 여러 트랜잭션이 동일한 자원에 대해 공유 잠금을 획득할 수 있다.

==========> DML : 테이블 공유 잠금, 행은 배타적 잠금 수행된다.
                                잠금은 COMMIT, ROLLBACK 시 자동 해제 된다.

DEADLOCK : 둘 이상의 트랜잭션이 서로 상대방의 LOCK을 순환대기하여 
                 어떤 트랜잭션도 더 이상 진행할 수 없는 상태를 말한다.
                 오라클은 주기적으로 자동으로 DETECT하여 에러를 리턴한다.

  1. 데드락 상태는 두 명 이상의 USER가 서로 LOCK을 걸고 있는 객체에 대기하고 있을 때 발생한다.
  2. 현재 세션에 데드락이 발생한 상태
        > 다른 세션이 비호환 모드에서 자원(테이블)에 락을 실행할 경우
        > 현재 세션이 자원을 요구하는 경우
        > 현재 세션에 비해 비호환 모드에서 LOCK 처리된 자원을 다른 세션이 기다리는 경우
오라클 서버는 데드락 상태를 감지한 명령문을 롤백하여 데드락 상태를 자동으로 감지하고 해결한다.    

============================================================================

오라클의 오브젝트 종류 : TABLE(데이터저장), INDEX(질의의 효율성), 
                                 VIEW(하나이상의 테이블 데이터집합), SEQUENCE(기본 키 값을 생성)
                                 SYNONYM(객체에 다른 이름을 제공)

오브젝트 관련 DDL 명령어 : 
CREATE, ALTER, DROP, RENAME, COMMENT,
TRUNCATE( 테이블에 저장되어 있는 모든 행을 삭제할 때 사용 )

TABLE(데이터저장)
SYS 사용자는 모든 데이터 딕셔너리 테이블(사용자 이름, 권한, 객체이름, 테이블 제약조건 등)을 소유.

ex) 사용자가 소유한 테이블 SELECT COLUMN ,,, FROM TABLENAME;
     사용자가 소유한 오브젝트 이름과 종류를 확인 
        SELECT OBJECT_NAME, OBJECT_TYPE
        FROM USER_OBJECTS;

데이터 딕셔너리 뷰
  - USER_ : 사용자가 소유하는 객체에 관한 정보
  - ALL_ : 사용자가 액세스할 수 있는 모든 테이블에 관한 정보
  - DBA_ : DBA ROLE을 할당 받은 사용자만 액세스가 가능
  - V$ : 데이터베이스 서버의 성능, 메모리 및 잠금에 대한 동적 성능 뷰

============================================================================

[ 테이블 생성 형식 ]
CREATE TABLE [ schema.]TABLE_NAME (
    column datatype [DEFAULT...] ,,,,
)

schema 는 소유자의 이름?
테이블 명은 문자로 시작, 30자까지, 유일한 명칭
DEFAULT 뒤에는 오지 말아야 할 것 : NEXTVAL, CURRVAL, 다른 컬럼명

ex)
CREATE TABLE EMP_TEST(
    EMPID NUMBER(4),
    ENAME VARCHAR2(10),
    SAL NUMBER(7) DEFAULT 100,
    HIREDATE DATE DEFAULT SYSDATE
);

DATA TYPE
문자 : CHAR(고정길이 2000), VARCHAR2(가변길이 4000), NCHAR(고정길이 유니코드 2000)
        LONG(가변길이 2G)

숫자 : NUMBER[(PREC 전체38, SCALE 소수이하 자리수)] : 가변숫자 21BYTE 최대값
        BINARY_FLOAT : 32BIT 부동소수 = 4BYTE
        BINARY_DOUBLE : 64BIT 부동소수 = 8BYTE

날짜 : DATE = 7BYTE = BC 4712년 1월 1일부터 9999년도 12월 31일까지

LOB : CLOB : LONG 타입의 확장 형태 / 큰 문자 데이터 저장 / 최대 4G
        BLOB : 바이너리로 형태 저장 / 이미지, 영상, 음성 / 최대 4G
        NCLOB : NATIONAL CHARACTER SET로 저장 / 최대 4G
        BFILE : 외부 운영체제에서 파일시스템에 저장, 테이블에 접근 가능

============================================================================

테이블 변경
 - 새로운 컬럼 추가
ALTER TABLE table_name ADD(
    column datatype [ DEFAULT expr ] ,,,,
);

기존 컬럼의 수정 : 자리수, 컬럼에 데이터가 없을 경우 타입을 수정
ALTER TABLE table_name MODIFY(
    column datatype [ DEFAULT expr ] ,,,,
);


 - 컬럼 지정
SET UNUSED column : 컬럼을 삭제할 수 있도록 표시하고 실제 테이블에서 컬럼이 제거되지 않음
                                 SELECT *, DESC 해도 표시되지 않는다.

DROP UNUSED column : UNUSED로 표시된 모든 컬럼을 제거한다.
                                 테이블에서 UNUSED 열로부터 디스크 공간을 회수하려고 사용한다.

ALTER TABLE table_name
SET UNUSED COLUMN column_name;

ALTER TABLE table_name
DROP UNUSED COLUMNS;

ex)
ALTER TABLE BOOK SET UNUSED COLUMN ( AUTHOR );
ALTER TABLE BOOK DROP UNUSED COLUMN;


 - 컬럼삭제
기존 컬럼의 삭제 : 반드시 테이블에 하나 이상의 열은 존재해야 한다.
ALTER TABLE table_name DROP(column);
ALTER TABLE table_name DROP COLUMN(column);

ex) ALTER TABLE BOOK DROP (AUTHOR);

TRUNCATE TABLE 문 : 테이블의 모든 행을 제거한다. 저장공간 해제, 롤백할 수 없다.
TRUNCATE TABLE table_name;

============================================================================

ANSI 타입                                           : 오라클 변환 타입
1. CHARACTER(N) ---------------------------> CHAR(N)
2. CHARACTER VARYING(N) ----------------> VARCHAR(N)
3. NATIONAL CHARACTER(N) --------------> NCHAR(N)
4. NATIONAL CHARACTER VARYING(N) ---> NVARCHAR(N)
5. DECIMAL ----------------------------------> NUMBER
6. INTEGER, INT, SMALLINT ----------------> NUMBER
7. FLOAT, REAL, DOUBLE -------------------> NUMBER

강의목표

1. 테이블 데이터 조작을 구현하는 명령을 수행할 수 있다.
2. insert 문을 이용한 테이블 새로운 행 추가
3. update 문을 이용한 기존 데이터 수정
4. delete 문을 이용한 기존 데이터 삭제
5. merge 문을 이용한 테이블 병합

============================================================================

INSERT INTO TABLE명( 컬럼1, 컬럼2 ,,,, ) VALUES ( 값1, 값2 ,,,, )
INSERT INTO TABLE명 VALUES ( 값1, 값2 ,,,, ) : 컬럼을 생략하는 경우 TABLE의 모든 컬럼에 값을 넣음

  - 테이블에 열목록을 사용하지 않는 경우에는 테이블의 기존 열 순서에 따라 모두 나열해야 한다.

  - 열 목록은 테이블의 순서와 같을 필요는 없다. INSERT INTO EMP ( DEPTNO, ENAME ) 가능

  - VALUES 절에 나열된 열의 순서와 개수는 INSERT 절에 나열된 열의 순서 및 개수와 같아야 함

  - 문자 및 날짜는 작은 따옴표로 묶는다.

  - 숫자 값을 작은 따옴표로 묶으면 암시적 변환이 발생할 수 있다.

  - 입력할 데이터가 없을 경우 NULL로 대입, 제약조건에 DEFAULT 값이 있으면 DEFAULT 값 대입

INSERT 구문 SUBQUERY : 
  - INSERT 절의 열의 수와 서브쿼리의 열의 수가 일치해야 한다

  - 한 번에 여러 개의 행을 입력할 수 있다.

INSERT INTO TABLE SUBQUERY : VALUES도 없고 괄호도 없다

============================================================================

UPDATE 문을 이용한 기존 데이터 수정
  - UPDATE 구문을 이용해서 특정 열의 값을 변경할 수 있다.

  - WHERE 절을 생략하면 테이블의 모든 행이 수정된다.

UPDATE 테이블명
SET column = values [ ,,,, ]
[ WHERE 조건 ]

============================================================================

DELETE 문
DELETE [FROM] 테이블명
[WHERE Condition];

============================================================================

MERGE 문
  - 테이블의 조건에 따라 데이터를 갱신하거나 삽입하는 기능을 제공한다.

  - 해당 행이 존재하는 경우 UPDATE를 수행하고 새로운 행일 경우 INSERT를 수행한다.

[형식]
MERGE INTO 테이블명
USING  -> 변경 또는 추가할 데이터의 원본을 지정하는 테이블 또는 VIEW, 서브쿼리
ON -> MERGE 연산이 변경 또는 추가를 수행하는 조건
WHEN MATCHED THEN
UPDATE SET
WHEN NOT MATCHED THEN
INSERT
VALUES

============================================================================

다중 테이블 입력
  - 무조건 INSERT : ALL 뒤에 여러 개의 INTO 절을 지정해 무조건 다중 테이블에 데이터 삽입
                         서브쿼리에서는 반환된 각 행에 대해 INTO 절을 한 번씩 수행한다.

  - 조건 ALL INSERT : 조건지정, 하나의 다중 INSERT는 WHEN문을 127개까지 사용할 수 있다.
                             (ALL이 없을 경우)

  - 조건 FIRST INSERT : 나열된 순서대로 WHEN문을 평가 실행한다.

  - 피벗 INSERT : 비 관계형 테이블의 레코드를 관계형으로 구현할 때 사용한다.

무조건 INSERT
INSERT ALL
    INTO 테이블명 [ ( 컬럼 ) ] VALUES()
    INTO
SELECT ... FROM ... WHERE

조건 ALL INSERT
INSERT ALL
    WHEN 조건 THEN
        INTO 테이블명 [ ( 컬럼 ) ] VALUES()
    WHEN 조건 THEN
        INTO
SELECT ... FROM ... WHERE

조건 FIRST INSERT : ALL의 경우 WHEN 조건만 맞으면 모든 테이블에 들어가지만
                            FIRST의 경우 한 번 WHEN을 만족하면 다른 테이들에는 들어가지 않는다.

============================================================================

예제 문제

테스트 테이블 생성
CREATE TABLE TEST
AS
SELECT * FROM EMP;

Q1 ) 특정행을 삽입해보자

INSERT INTO TEST( EMPNO, ENAME, HIREDATE )
VALUES ( 7233, 'PAUL', SYSDATE );


Q2) 사원의 번호, 사용자 이름, 봉급을 입력해보자

INSERT INTO TEST( EMPNO, ENAME, SAL )
VALUES ( 7234, USER, 3400 );


Q3) 날짜 값을 입력해보자 TO_DATE()

INSERT INTO TEST ( EMPNO, ENAME, HIREDATE )
VALUES ( 7533, 'ELVIS', TO_DATE('1997-02-05', 'YYYY-MM-DD'));

INSERT INTO TEST ( EMPNO, ENAME, HIREDATE )
VALUES ( 7535, 'ELVIS02', '1997/02/07');

INSERT INTO TEST ( EMPNO, ENAME, HIREDATE )
VALUES ( 7536, 'ELVIS03', '1997-02-28');


Q4) EMP 테이블 사원 중에 A가 들어있는 사원의 정보만 추출해서 NEW_EMP에 입력

CREATE TABLE NEW_EMP(
ID NUMBER(4),
NAME VARCHAR2(10)
);

INSERT INTO NEW_EMP
SELECT EMPNO, ENAME
             FROM EMP
             WHERE ENAME LIKE '%A%';


Q5) TEST 테이블의 커미션을 0으로 변경하자

UPDATE TEST
SET COMM = 0;


Q6) UPDATE한 구문을 취소하자

ROLLBACK;


Q7) TEST 테이블에서 FORD의 커미션을 500으로 변경하자

UPDATE TEST
SET COMM = 500
WHERE ENAME = 'FORD';


Q8) TEST 테이블에서 PAUL의 직업은 CLERK로 봉급은 5000 커미션은 봉급의 20%로 변경해보자

UPDATE TEST
SET JOB = 'CLERK', SAL = 5000, COMM = SAL*0.2
WHERE ENAME = 'PAUL';  --> 현재 SAL은 5000이 아니라 NULL이라 COMM이 변경되지 않는다.


Q9) TEST테이블에서 서브쿼리를 사용해서 사원의 번호가 7902인 사원의 부서번호를
      KING과 동일하게 변경하는 UPDATE를 해보자

UPDATE TEST
SET DEPTNO = ( SELECT DEPTNO
                      FROM TEST
                      WHERE ENAME = 'KING' )
WHERE EMPNO = 7902;


Q10) 7698사원의 직업과 부서번호를 7499사원의 직업과 부서번호와 같도록 수정하자

UPDATE TEST
SET ( JOB, DEPTNO ) = ( SELECT JOB, DEPTNO
                                FROM EMP
                                WHERE EMPNO = 7499)
WHERE EMPNO = 7698;

Q11) TEST 전체 레코드를 삭제하자

DELETE FROM TEST;
DELETE TEST;


Q12) FORD 사원을 삭제

DELETE TEST
WHERE ENAME ='FORD';
SELECT * FROM TEST;


Q13) 부서번호 20번인 사원 삭제

DELETE TEST
WHERE DEPTNO = 20;
SELECT * FROM TEST;


Q14) 사원의 봉급 중에 2000 이상을 받는 사원을 삭제

DELETE TEST
WHERE SAL >= 2000;
SELECT * FROM TEST;


Q15) EMP 테이블에서 TEST 테이블과 비교해서 동일한 사원번호의 데이터가 있으면
       TEST 테이블의 봉급, 커미션을 EMP 테이블의 값으로 수정하고
       해당 사원번호를 가진 사원이 없으면 TEST에 EMP 테이블의 데이터를 이용해서 입력해보자

MERGE INTO TEST T
USING EMP E
ON (T.EMPNO = E.EMPNO)
WHEN MATCHED THEN
    UPDATE SET T.SAL = E.SAL, T.COMM=E.COMM
WHEN NOT MATCHED THEN
    INSERT (EMPNO, ENAME, SAL, COMM)
    VALUES (E.EMPNO, E.ENAME, E.SAL, E.COMM);


Q16) EMP테이블에서 부서번호가 20 이상의 데이터를 TEST01 테이블에 사원번호, 입사일, 봉급을 입력하고
       TEST02 테이블에는 사원번호, 매니저, 봉급을 입력해보자

CREATE TABLE TEST01
AS
SELECT * FROM EMP
WHERE  1 = 0;  -- 구조만 생성되고 데이터는 삽입되지 않는다.

CREATE TABLE TEST02
AS
SELECT * FROM EMP
WHERE  1 = 0;

INSERT ALL
    INTO TEST01 ( EMPNO, HIREDATE, SAL ) VALUES ( EMPNO, HIREDATE, SAL )
    INTO TEST02 ( EMPNO, MGR, SAL ) VALUES ( EMPNO, MGR, SAL )
SELECT EMPNO, HIREDATE, SAL, MGR
FROM EMP
WHERE DEPTNO >= 20;


Q17) EMP테이블에서 부서번호가 20 이상의 데이터를 TEST01 테이블에 사원번호, 입사일, 봉급을 입력하고
       TEST02 테이블에는 사원번호, 매니저, 봉급을 입력해보자
       단 봉급이 2000이상이면 TEST01에 나머지는 TEST02 입력하자

INSERT ALL
    WHEN SAL >= 2000 THEN
        INTO TEST01 (EMPNO, HIREDATE, SAL) VALUES ( EMPNO, HIREDATE, SAL )
    WHEN SAL < 2000 THEN
        INTO TEST02 (EMPNO, MGR, SAL) VALUES ( EMPNO, MGR, SAL )
SELECT EMPNO, HIREDATE, SAL, MGR
FROM EMP
WHERE DEPTNO >= 20;


Q18) 

INSERT FIRST
    WHEN SAL >= 2000 THEN
        INTO TEST01 (EMPNO, HIREDATE, SAL) VALUES ( EMPNO, HIREDATE, SAL )
    WHEN EMPNO >= 0 THEN
        INTO TEST02 (EMPNO, MGR, SAL) VALUES ( EMPNO, MGR, SAL )
SELECT EMPNO, HIREDATE, SAL, MGR
FROM EMP;

Q19)

INSERT ALL
    INTO TEST01 ( EMPNO, ENAME ) VALUES ( EMPNO, ENAME )
    INTO TEST01 ( EMPNO, ENAME, SAL ) VALUES ( EMPNO, ENAME, SAL )
    INTO TEST01 ( EMPNO, ENAME, MGR ) VALUES ( EMPNO, ENAME, MGR )
SELECT EMPNO, ENAME, HIREDATE, SAL, MGR
FROM EMP;

1. set 연산자를 이해하고 기술할 수 있다.
2. subquery를 기술할 수 있다.

============================================================================
SET 연산자는 두 개 이상의 질의 결과를 하나의 결과로 통합하며 SET 연산자를 포함하는 질의를
복합질의 라고 한다.  --> 질의는 쿼리를 말함

종류는 다음과 같다.
  - UNION : 두 질의 중 어느 것 하나에 의해서라도 선택된 모든 구분(DISTINCT)행을 결과로 한다.
                양쪽 질의의 의해서 선택된 행을 모두 포함한다. 
                중복 값은 한 번만 첫 번째 칼럼을 기준으로 ASC정렬

  - UNION ALL : 중복 행을 포함하여 두 질의 중 어느 것 하나에 의해서 선택된 모든 행을 결과로

  - INTERSECT : 두 질의에 공통으로 선택된 모든 구분(DISTINCT)행을 결과로 한다.
                     첫 번째 선택 문장에서 두 번째 선택 문장과 일치하는 최소한 하나의 행을 리턴

  - MINUS : 첫 번째 SELECT 문에 선택되고 두 번째 SELECT 문에서 선택되지 않은 
                모든 구분(DISTINCT)행을 결과로 한다.
                두 번째 선택 문장에서는 선택되지 않은 첫 번째 선택 문장에 의해 선택된 모든 유일 행

특징
  - 모든 SET 연산자는 같은 우선순위를 가진다. SQL문장이 여러 개의 SET 연산자를 포함한다면
    DB는 명시적으로 다른 순서를 명시하는 괄호가 없을 경우 왼쪽(위)에서 오른쪽(아래)로 실행

  - 사용하는 질의의 평가 순서를 명시적으로 표현하기 위해서는 괄호를 사용한다.

[형식]
SELECT 
FROM
WHERE
GROUP BY
HAVING

    SET 연산자

SELECT 
FROM
WHERE
GROUP BY
HAVING
ORDER BY

============================================================================
UNION
  - 두 개의 SELECT 문장에서 컬럼의 개수가 맞아야 한다. 컬럼의 순서도 맞아야 하는 듯?

  - 컬럼 이름은 같지 않아도 된다.

  - 중복 값은 하나만 리턴된다.

  - WHERE 절에서 UNION을 사용하는 질의는 
    SELECT 목록에 있는 것과 똑같은 수와 데이터 타입의 열을 가져야 한다.

============================================================================
Subquery
정의, 유형
  - Single row Subquery, Multi row Subquery
  - Multi column Subquery
  - Scalar Subquery
  - Correlated Subquery
  - With 절과 Subquery

Scalar -> 프로그램에서 자료형을 의마한다. 단일 값을 의미한다. 정수, 문자, 문자열 등

[형식]
SELECT
FROM
WHERE ( SELECT
            FROM
            WHERE )

    밖에가 주 쿼리, OUTER 쿼리, 기본 질의, 외부 질의
    괄호 안이 서브 쿼리, INNER 쿼리, 부 쿼리

  1. 서브쿼리는 일반적으로 기본 질의 실행 전에 한 번 실행된다.
  2. 서브쿼리의 결과는 기본 질의에 사용된다.
  3. 서브쿼리는 ( ) 묶여 있어야 한다.
  4. 비교 조건의 오른쪽에 서브쿼르릴 명시한다.
  5. 일반적인 서브쿼리에서는 (Top-N 분석 제외) ORDER BY 절을 사용하지 않는다.
  6. 단일행 연산자, 복수행 연산자(IN, ANY, ALL)
  7. SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, UPDATE, INSERT, DELETE 문에 사용


ex) 'JONES'보다 더 많은 월급을 받는 사원의 이름과 월급을 출력해보자
SELECT SAL
FROM EMP
WHERE ENAME = 'JONES';  --> 2975

SELECT ENAME, SAL
FROM EMP
WHERE SAL > 2975;  --> 2명 

이 작업을 한번에 한다.
SELECT ENAME, SAL
FROM EMP
WHERE SAL > ( SELECT SAL
                     FROM EMP
                     WHERE ENAME = 'JONES' );

============================================================================

다중 행 서브쿼리 : Multi row Subquery
ORA-01427: single-row subquery returns more than one row

  - IN : 목록에 있는 임의의 값과 동일하다. DATA OR, DATA OR,,,

  - ANY : 값을 반환하는 각각의 값과 개별 비교한다.
        < ANY : 최대값보다 적음
        > ANY : 최소값보다 큼
        = ANY : IN 연산자와 동일

  - ALL : 서브쿼리에 의해 반환되는 모든 값과 비교한다.
        < ALL : 최소값보다 작음
        > ALL : 최대값보다 큼

  - NOT : IN, ANY, ALL 연산자와 함께 사용된다. 

============================================================================

다중 열 서브쿼리 : Multi column Subquery
서브쿼리의 결과값이 두 개 이상의 컬럼을 반환하는 서브쿼리로 혼합WHERE를 작성한다.
중복된 WHERE 조건을 하나의 WHERE 절로 결합시킨다.
  - NON-PAIRWISE 비교 : 

  - PAIRWISE 비교 : 

============================================================================

Correlated Subquery
[형식]
SELECT
FROM TABLE1 MAIN
WHERE COLUMN1 OPERATOR ( SELECT
                                          FROM TABLE2
                                          WHERE expr1 = main.expr2 )

  - 메인 쿼리의 한 row에 대해서 서브쿼리가 한 번씩 실행된다

  - 테이블에서 행을 먼저 읽어서 각 행의 값을 관련된 데이터와 비교한다.

  - 기본 질의에서 각 후보행에 대해 서브쿼리가 다른 결과를 반환해야 하는 경우 사용된다.

  - 서브쿼리에서 메인쿼리의 컬럼명을 사용할 수 있으나, 
    메인쿼리에서는 서브쿼리의 컬렴명을 사용할 수 없다.

실행
  1. 후보행을 메인쿼리에서 가져온다
  2. 후보행의 값을 사용해서 서브쿼리를 실행한다
  3. 서브쿼리의 결과 값을 사용하여 후보행의 조건을 확인한다.
  4. 후보행이 남지 않을 때까지 반복한다.

FROM 절에 서브쿼리 = INLINE VIEW
[형식]
SELECT
FROM ( SELECT ,,, )
WHERE 조건식;

============================================================================
TOP-N 쿼리
ex)
SELECT ROWNUM, ENAME, SAL
FROM EMP
WHERE ROWNUM < 4
ORDER BY SAL DESC;

SELECT ROWNUM, ENAME, SAL
FROM ( SELECT *
           FROM EMP
           ORDER BY SAL DESC )
WHERE ROWNUM < 4;

============================================================================
스칼라 서브쿼리 : Scalar Subquery
  - 하나의 행에서 하나의 열 값만 반환하는 서브쿼리를 스칼라 서브쿼리라고 한다.

  - SELECT, INSERT 문의 VALUES 목록, DECODE 및 CASE의 조건 및 표현식 부분,
    GROUP BY 를 제외한 SELECT 의 모든 절, UPDATE 문의 SET 절 및 WHERE 절에서 연산자 목록

  - 서브쿼리가 0개 행을 반환하면 스칼라 서브쿼리의 값은 NULL이다.

  - 서브쿼리가 2개 이상일 때는 행을 반환하는 오류가 발생한다.

============================================================================
Exists 연산자 : 서브쿼리의 결과 집합에 행이 있는지 유무를 검사

WITH절
  - 질의를 쉽게 만든다.

  - 반복 실행 시에 한 번만 실행된다.

============================================================================
계층형 쿼리
  - START WITH 절을 이용한 시작 지점

  - CONNECT BY 절을 이용한 트리 진행 방향 설정 / 조건절을 이용해서 데이터 제거

  - LEVEL 의사열의 활용

  - PRIOR 키워드

  - WHERE 조건절 활용해서 데이터 제거

[형식]
SELECT [LEVEL] column1, column2,,,,,, 
FROM TABLE1 MAIN
WHERE
[START WITH CONDITION]  --> 시작할 조건 명시, column1 = VALUE
[CONNECT BY PRIOR CONDITION] ;
    CONNECT BY PRIOR PARENT KEY = CHILD KEY
    CONNECT BY PRIOR CHILD KEY = PARENT KEY

============================================================================
Q1) 데이터 생성을 해보자

CREATE TABLE EMP_HISTORY( NAME, TITLE, DEPTID )
AS
SELECT ENAME, JOB, DEPTNO
FROM EMP;

DESC EMP_HISTORY;


Q2) EMP 테이블과 EMP_HISTORY의 테이블의 내용을 UNION 연산을 실행

SELECT ENAME, JOB, DEPTNO 
FROM EMP
UNION
SELECT *
FROM EMP_HISTORY;

같은결과 = FULL JOIN으로 변경

SELECT ENAME, JOB, DEPTNO 
FROM EMP E FULL JOIN EMP_HISTORY A
ON E.ENAME = A.NAME;

어제 만든 TEST_EMP로도 해봄

SELECT ENAME, JOB, DEPTNO 
FROM EMP
UNION
SELECT ENAME, JOB, DEPTNO
FROM TEST_EMP;


Q3) EMP 테이블과 EMP_HISTORY의 테이블의 내용을 UNION ALL 연산을 실행

SELECT ENAME, JOB, DEPTNO 
FROM EMP
UNION ALL
SELECT *
FROM EMP_HISTORY;


Q4) TEST_EMP, EMP를 이용해서 INTERSECT 해보자

SELECT *
FROM TEST_EMP
INTERSECT
SELECT *
FROM EMP;


Q5) EMP 테이블과 EMP_HISTORY의 테이블의 내용을 MINUS 연산을 실행

SELECT ENAME, JOB, DEPTNO 
FROM EMP
MINUS
SELECT *
FROM EMP_HISTORY;

SELECT *
FROM TEST_EMP
MINUS
SELECT *
FROM EMP;


Q6) EMP, DEPT에서 부서번호, 부서이름, 입사일을 UNION 연산자를 이용해서 출력해보자

SELECT DEPTNO, TO_CHAR(NULL), HIREDATE
FROM EMP
UNION
SELECT DEPTNO, DNAME, TO_DATE(NULL)
FROM DEPT
ORDER BY 1;

같은 표현이지만 다른데서 타입을 확인하기 위해 명시적으로 표현하려고 TO CHAR TO DATE를 씀

SELECT DEPTNO, NULL, HIREDATE
FROM EMP
UNION
SELECT DEPTNO, DNAME, NULL
FROM DEPT
ORDER BY 1;


Q7) 7566 사원보다 더 많은 월급을 받는 사원의 이름과 월급을 출력해보자

SELECT ENAME , SAL
FROM EMP
WHERE SAL > ( SELECT SAL
                     FROM EMP
                     WHERE EMPNO = 7566 );


Q8) 급여의 평균보다 적은 사원의 사원번호, 이름, 직업, 급여, 부서번호를 출력하자

SELECT EMPNO, ENAME, JOB, SAL, DEPTNO
FROM EMP
WHERE SAL < ( SELECT AVG(SAL)
                     FROM EMP );


Q9) 사원의 번호가 7521인 사원의 직업과 같고 급여가 7934 사원보다 많은 사원의
      사원번호, 이름, 직업, 입사일자, 급여를 조회하자

SELECT EMPNO, ENAME, JOB, HIREDATE, SAL
FROM EMP
WHERE JOB = ( SELECT JOB
                     FROM EMP
                     WHERE EMPNO = 7521 )
AND SAL > ( SELECT SAL
                  FROM EMP
                  WHERE EMPNO = 7934 );


Q10) 사원테이블에서 사원의 급여가 20번 부서의 최소 급여보다 많은 부서를 리턴해보자

SELECT DEPTNO, MIN(SAL)
FROM EMP
GROUP BY DEPTNO
HAVING MIN(SAL) > ( SELECT MIN(SAL)
                             FROM EMP
                             WHERE DEPTNO = 20 );


Q11) 사원테이블에서 직업 중에서 가장 작은 평균 급여를 받는 직업을 조회하자

SELECT JOB, AVG(SAL)
FROM EMP
GROUP BY JOB
HAVING AVG(SAL) = ( SELECT MIN(AVG(SAL))
                              FROM EMP
                              GROUP BY JOB);


Q12) 직업이 SALESMAN인 최소 한 명 이상의 사원보다 급여를 많이 받는 사원의 
       이름, 급여, 직업을 출력하자

SELECT ENAME, SAL, JOB
FROM EMP
WHERE JOB != 'SALESMAN'
AND SAL > ANY ( SELECT SAL
                        FROM EMP
                        WHERE JOB = 'SALESMAN' );


Q13) 직업이 SALESMAN인 모든 사원보다 급여를 많이 받는 사원의
       이름, 봉급, 직업, 입사일, 부서번호를 출력하자

SELECT ENAME, SAL, JOB, HIREDATE, DEPTNO
FROM EMP
WHERE SAL > ALL ( SELECT SAL
                           FROM EMP
                           WHERE JOB = 'SALESMAN' );

결과 같음

SELECT ENAME, SAL, JOB, HIREDATE, DEPTNO
FROM EMP
WHERE SAL > ( SELECT MAX(SAL)
                     FROM EMP
                     WHERE JOB = 'SALESMAN' );


Q14) FORD, BLAKE와 관리자 및 부서가 같은 사원의 정보를 출력해보자

SELECT *
FROM EMP
WHERE MGR IN ( SELECT MGR
                       FROM EMP
                       WHERE ENAME IN('FORD', 'BLAKE') )
AND DEPTNO IN ( SELECT DEPTNO
                        FROM EMP
                        WHERE ENAME IN('FORD', 'BLAKE') )
AND ENAME NOT IN ('FORD', 'BLAKE');


Q15) FORD, BLAKE와 관리자 및 부서가 같은 사원의 정보를 출력해보자
       PAIRWISE 비교

SELECT *
FROM EMP
WHERE (MGR, DEPTNO) IN ( SELECT MGR, DEPTNO
                                     FROM EMP
                                     WHERE ENAME IN ('FORD', 'BLAKE') )
AND ENAME NOT IN ('FORD', 'BLAKE');    --> 안되는게 정상


Q16) 사원이 속한 부서의 평균 급여보다 많은 급여를 받는 사원의
       이름, 급여, 부서번호, 입사일, 직업을 출력하자

SELECT ENAME, SAL, DEPTNO, HIREDATE, JOB
FROM EMP E
WHERE SAL > ( SELECT AVG(SAL)
                     FROM EMP
                     WHERE DEPTNO = E.DEPTNO );


Q17) 사원이 속한 부서의 평균 급여보다 많은 급여를 받는 사원의
       이름, 급여, 부서번호, 입사일, 직업을 출력하자

SELECT E.ENAME, E.SAL, E.DEPTNO, E.HIREDATE, E.JOB, D.AVGSAL
FROM EMP E, ( SELECT DEPTNO, AVG(SAL) AVGSAL
                    FROM EMP E
                    GROUP BY DEPTNO ) D
WHERE E.DEPTNO = D.DEPTNO
AND E.SAL > D.AVGSAL;


Q18) 사원테이블에서 사원 중 급여를 많이 받는 상위 4등에서 7등까지 이름, 급여를 조회하자

SELECT ROWNUM, ENAME, SAL
FROM ( SELECT *
           FROM EMP
           ORDER BY SAL DESC )
WHERE ROWNUM < 8
MINUS
SELECT ROWNUM, ENAME, SAL
FROM ( SELECT *
           FROM EMP
           ORDER BY SAL DESC )
WHERE ROWNUM < 4;

다른 풀이
SELECT ENAME, SAL
FROM ( SELECT ROWNUM, ENAME, SAL
           FROM ( SELECT *
                      FROM EMP
                      ORDER BY SAL DESC )
                      WHERE ROWNUM < 8
                      ORDER BY 1 DESC )
WHERE ROWNUM < 5;


Q19) EMP 테이블에서 사원번호, 이름, 부서번호, 본인 부서의 평균 급여를 조회하자
SELECT EMPNO, ENAME, SAL, DEPTNO, ( SELECT AVG(SAL)
                                                     FROM EMP
                                                     WHERE DEPTNO = E.DEPTNO) AS ASAL
FROM EMP E;

SELECT EMPNO, ENAME, SAL, DEPTNO, AVG(SAL)
FROM EMP JOIN DEPT USING (DEPTNO)
GROUP BY DEPTNO;




Q20) 사원번호, 이름, 해당부서의 지역이 DALLAS이면 TOP으로 BOSTON이면 BRENCH로 표시하자

SELECT EMPNO, ENAME, ( CASE WHEN DEPTNO = ( SELECT DEPTNO
                                                                     FROM DEPT
                                                                     WHERE LOC = 'DALLAS' ) THEN 'TOP'
                                          WHEN DEPTNO = ( SELECT DEPTNO
                                                                     FROM DEPT
                                                                     WHERE LOC = 'BOSTON' ) THEN 'BRENCH' 
                                          ELSE '없음' END ) AS LOCATION
FROM EMP;


Q21) 사원테이블에서 사원번호 이름 부서번호 입사일을 출력하되 
       본인 부서의 부서명이 큰 값이 나오도록 정렬
SELECT EMPNO, ENAME, DEPTNO, HIREDATE
FROM EMP E
ORDER BY ( SELECT DNAME
                FROM DEPT
                WHERE DEPTNO = E.DEPTNO) DESC;


Q22) 사원테이블에서 부하직원을 가지고 있는 사원의 사원번호, 이름, 직업, 입사일자를 조회하자.

SELECT EMPNO, ENAME, JOB, HIREDATE
FROM EMP E
WHERE EXISTS ( SELECT 1 
                      FROM EMP
                      WHERE E.EMPNO = MGR )
ORDER BY EMPNO;


Q23) WITH 절을 이용해서 전체 부서의 총 급여보다 총 급여가 많은 부서의 
      부서이름, 총 급여를 조회하자
      WITH 절을 이용해서 부서별 총 급여의 평균보다 총 급여가 많은 부서의 
      부서이름, 총 급여를 조회하자

WITH
DEPT_COSTS AS ( SELECT DNAME, SUM(SAL) AS DEPT_TOTAL
                        FROM EMP E, DEPT D
                        WHERE E.DEPTNO = D.DEPTNO
                        GROUP BY D.DNAME ),
AVG_COST AS ( SELECT SUM(DEPT_TOTAL) / COUNT(*) AS DEPT_AVG
                     FROM DEPT_COSTS )
SELECT *
FROM DEPT_COSTS
WHERE DEPT_TOTAL > ( SELECT DEPT_AVG
                                 FROM AVG_COST )
ORDER BY DNAME;


Q24) 

SELECT ENAME || '  reports to  ' || PRIOR ename "walk"
FROM EMP
START WITH ENAME = 'KING'
CONNECT BY PRIOR EMPNO = MGR; 


Q25) SMITH 기준으로 BOTTOM UP 방향으로 매니저 정보를 출력해보자

SELECT ENAME, JOB, EMPNO, MGR
FROM EMP
START WITH ENAME = 'SMITH'
CONNECT BY PRIOR MGR = EMPNO;


Q26) 

SELECT LPAD(' ', 4 * LEVEL-4) || ENAME ORG_CHART, LEVEL, EMPNO, MGR, DEPTNO
FROM EMP
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR; 


Q27) 

SELECT DEPTNO, EMPNO, ENAME, JOB, SAL
FROM EMP
WHERE ENAME != 'SMITH'
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR; 


Q28) Branch 삭제 하기

SELECT DEPTNO, EMPNO, ENAME, JOB, SAL
FROM EMP
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR AND ENAME != 'JONES';


Q29) 

SELECT LPAD(' ', 4 * LEVEL-4) || ENAME ORG_CHART, LEVEL, EMPNO, MGR, DEPTNO
FROM EMP
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR
ORDER BY ENAME;

SELECT LPAD(' ', 4 * LEVEL-4) || ENAME ORG_CHART, LEVEL, EMPNO, MGR, DEPTNO
FROM EMP
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR
ORDER SIBLINGS BY ENAME;


Q30) 누구 - 누구 -

SELECT LPAD(' ', 4 * LEVEL-4) || ENAME ORG_CHART, JOB,
SYS_CONNECT_BY_PATH(ENAME, '--') "11"
FROM EMP
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR;


Q31) 자식이 있다 없다

SELECT LPAD(' ', 4 * LEVEL-4) || ENAME ORG_CHART, JOB,
CONNECT_BY_ISLEAF
FROM EMP
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR;


Q32) LEAF만 보여주기

SELECT LPAD(' ', 4 * LEVEL-4) || ENAME ORG_CHART, JOB
FROM EMP
WHERE CONNECT_BY_ISLEAF = 1
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR;

1. 분석 함수 종류를 살펴보고 활용하자
2. join 또는 subquery를 살펴보고 구현해보자

============================================================================
JOIN
  - 데이터베이스에서 여러 테이블의 데이터가 필요한 경우 사용된다.

  - 어떤 테이블을 기준으로 다른 테이블에 있는 ROW를 찾아오는 것

  - 서로 독립적인 데이터들 간의 조인을 이용해서 필요한 정보를 참조하게 된다.

  - 해당열에 존재하는 공통 값, 일반적으로 기본키 및 외래키 열을 JOIN 조건으로 사용해서
    한 테이블의 행을 다른 테이블의 행에 조인할 수 있다.

  - N개의 테이블을 조인하려면 최소 N-1개의 조인 조건이 필요하다.
    만일 3개의 테이블을 조인하려면 최소 2개의 조인 조건이 필요
    

[형식]
SELECT TABLE1.COLUMN, TABLE2.COLUMN ,,,,, 
FROM TABLE1, TABLE2
WHERE TABLE1.COLUMN = TABLE2.COLUMN;

============================================================================
JOIN = INNER JOIN = EQUI JOIN : 
  - 두 개의 테이블 간의 컬럼 값들이 정확하게 일치하는 경우 데이터를 리턴
  - FALSE, NULL을 제외한 결과만 나온다.

OUTER JOIN : 
  - 주종관계를 만들어서 주테이블은 전체 출력, 종 테이블은 TRUE만 출력
  - FOREIGN KEY 관계에서 참조되는 테이블이 주 테이블, 끌어다 쓰는 테이블이 종 테이블
  - 오라클은 종쪽에 (+), ANSI는 주쪽에 방향을 적어준다. EMP RIGHT OUTER JOIN DEPT

SELF JOIN : 
  - 같은 테이블에 있는 행들을 JOIN하는데 사용

NON-EQUI JOIN : 
  - 두 개의 테이블 간의 컬럼 값들이 정확하게 일치하지 않는 경우에 사용
  - 두 개의 테이블에 동일한 이름을 가진 모든 컬럼을 기준으로 JOIN

============================================================================
Q1) CARTESIAN : 첫 번째 테이블의 모든 행이 두 번째 모든 행에 조인
    ex) A = ROW의 수가 n개, B = ROW의 수가 m개 : n * m개

-ANSI
SELECT E.ENAME, D.DNAME
FROM EMP E CROSS JOIN DEPT D;

 - ORACLE
SELECT *
FROM EMP, DEPT;  --> 뒤에 있는 테이블이 앞에있는 테이블을 순서대로 조회하고 넘어감


Q2) INNER JOIN을 이용하여 사원테이블의 사원번호, 이름, 부서번호, 부서명을 조회해보자

 - ANSI
SELECT EMPNO, ENAME, DEPTNO, DNAME
FROM EMP JOIN DEPT USING(DEPTNO);

SELECT EMPNO, ENAME, DEPTNO, DNAME
FROM TEST_EMP JOIN DEPT USING(DEPTNO);  --> USING은 COLUMN명이 같을 때 쓴다.


 - ORACLE
SELECT E.EMPNO, E.ENAME, E.DEPTNO, D.DNAME
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO;


Q3) TEST_EMP 테이블에 부서배치 받지 않은 사원을 추가해서 INNER JOIN을 해보자

INSERT INTO TEST_EMP(ENAME, DEPTNO) VALUES (777, NULL);

 - ANSI
SELECT EMPNO, ENAME, DEPTNO, DNAME
FROM TEST_EMP INNER JOIN DEPT USING(DEPTNO);  --> INNER는 기본이기 때문에 JOIN만 써도 됨

 - ORACLE
SELECT T.EMPNO, T.ENAME, T.DEPTNO, D.DNAME
FROM TEST_EMP T, DEPT D
WHERE T.DEPTNO = D.DEPTNO;


Q4) SALESMAN의 사원번호, 이름, 급여, 부서명, 근무지를 리턴한다

 - ANSI
SELECT EMPNO, ENAME, SAL, DNAME, LOC
FROM EMP JOIN DEPT USING(DEPTNO)
WHERE JOB = 'SALESMAN';

 - ORACLE
SELECT E.EMPNO, E.ENAME, E.SAL, D.DNAME, D.LOC
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO AND E.JOB = 'SALESMAN';


Q5) EMP 테이블과 DEPT 테이블에서 DEPT 테이블에 있는 모든 자료를
     사원번호, 이름, 직업, DEPT 테이블의 부서번호 부서명을 조회하자

 - ANSI
SELECT EMPNO, ENAME, JOB, DEPTNO, DNAME
FROM EMP RIGHT OUTER JOIN DEPT USING(DEPTNO);

 - ORACLE
SELECT E.EMPNO, E.ENAME, E.JOB, D.DEPTNO, D.DNAME
FROM DEPT D, EMP E
WHERE D.DEPTNO = E.DEPTNO(+);


Q6) 00사원의 관리자는 000이다. 

 - ANSI
SELECT WORKER.ENAME || ' 사원의 관리자는 ' || MANAGER.ENAME || ' 이다.' "관리자 정보"
FROM EMP WORKER JOIN EMP MANAGER 
ON WORKER.MGR = MANAGER.EMPNO;  --> INNER JOIN

SELECT WORKER.ENAME || ' 사원의 관리자는 ' || MANAGER.ENAME || ' 이다.' "관리자 정보"
FROM EMP WORKER LEFT JOIN EMP MANAGER 
ON WORKER.MGR = MANAGER.EMPNO;  --> OUTER JOIN

 - ORACLE
SELECT WORKER.ENAME || ' 사원의 관리자는 ' || MANAGER.ENAME || ' 이다.' "관리자 정보"
FROM EMP WORKER, EMP MANAGER
WHERE WORKER.MGR = MANAGER.EMPNO;  --> INNER JOIN이 된다. 결과 11 row

SELECT WORKER.ENAME || ' 사원의 관리자는 ' || MANAGER.ENAME || ' 이다.' "관리자 정보"
FROM EMP WORKER, EMP MANAGER
WHERE WORKER.MGR = MANAGER.EMPNO(+);  --> OUTER JOIN이 된다. 결과 12 row


Q7) 사원이름, 부서번호, 부서명을 조회하자
SELECT ENAME, DEPTNO, DNAME
FROM EMP NATURAL JOIN DEPT USING DEPTNO;  
    --> ORA-00933: SQL command not properly ended

SELECT E.ENAME, DEPTNO, D.DNAME
FROM EMP E NATURAL JOIN DEPT D;

SELECT ENAME, DEPTNO, DNAME
FROM EMP NATURAL JOIN DEPT;


Q8) 사원번호, 이름, 직업, 봉급 봉급의 등급을 사용하자

 - ANSI
SELECT ENAME, SAL, GRADE
FROM EMP JOIN SALGRADE
ON SAL BETWEEN LOSAL AND HISAL;

 - ORACLE
SELECT EMPNO, ENAME, JOB, SAL, GRADE
FROM SALGRADE, EMP
WHERE SAL BETWEEN LOSAL AND HISAL;

============================================================================
JOIN 연습용 테이블 생성 AND 값 넣기
CREATE TABLE X(
S1 VARCHAR2(2),
X2 VARCHAR2(2)
);

CREATE TABLE Y(
S1 VARCHAR2(2),
Y2 VARCHAR2(2)
);

INSERT INTO X VALUES('YA', NULL);
INSERT INTO X VALUES('YB', 1);
INSERT INTO X VALUES('C', NULL);

INSERT INTO Y VALUES('YA', NULL);
INSERT INTO Y VALUES('YB', 1);

1. 분석 함수 종류를 살펴보고 활용하자
2. join을 살펴보고 구현해보자

============================================================================

분석함수를 사용하게 된 이유
  - RDBS 상에서 컬럼과 컬럼 연산, 비교, 연결은 쉬운 반면 
    행과 행간의 관계를 정의하거나 비교, 연산하는 것을 하나의 SQL로 처리하기 힘든점을 해결할려고

  - 그 전에는 프로그램을 작성, 인라인 뷰를 이용해서 복잡한 SQL문 작성

  - 분석함수를 도입해서 행간의 연산을 원할하게 연동한다. 단, 중첩사용은 불가능
    서브쿼리에는 사용 가능

[형식]
    SELECT Analytic_Function(arguments) OVER
                               ([PARTITION By 절] [ORDER BY 절] [WINDOWING 절])
    FROM 테이블명;

  - Analytic_Function : AVG, COUNT, LAG, LEAD, MAX, MIN, RANK, RATIO_TO_REPORT
                             ROW_NUMBER, SUM 등
                             arguments 0 ~ 3개 까지만 올 수 있다. 
                             *는 COUNT(*)만 허용. DISTINCT는 해당 집계 함수가 허용할 때만 가능

  - OVER : 해당 함수가 쿼리 결과 집합에 따라서 적용되는 지시어이다.
              FROM, WHERE, GROUP BY, HAVING 이후에 계산된다.
              SELECT, ORDER BY 구문 뒤에 사용할 수 있다.

  - PARTITION By : 쿼리 결과를 < expr_list > 별로 그룹핑한다. 생략시에는 하나의 그룹으로 리턴

  - ORDER BY : ORDER BY < expr_list > [ (ASC | DESC), (NULL FIRST | LAST) ]
                    < expr_list >에서는 별칭이나 숫자를 사용할 수 없다.

  - WINDOWING : ROWS, RANGE가 일단 핵심
                        ROWS는 물리적인 단위(ROW위치)고 RANGE는 논리적인 단위(ROW값)이다.
                        ROWS[RANGE] BETWEEN start_point AND end_point

  - start_point : 그룹별 시작점을 의미 UNBOUNDED PRECEDING, CURRENT ROW,
                     value_expr PRECEDING or value_expr FOLLOWING 이 올 수 있다.

  - end_point : 그룹별 시작점을 의미 UNBOUNDED FOLLOWING, CURRENT ROW,
                     value_expr PRECEDING or value_expr FOLLOWING 이 올 수 있다.


분석함수의 장점
  - JOIN이나 프로그램의 OVERHEAD를 줄임 
    (QUERY SPEED의 향상된 SELF-JOIN, 절차적 로직으로 표현하는 것을 NATIVE SQL에서 
      바로 적용할 수 있도록 JOIN이나 프로그램의 OVERHEAD를 줄임)

  - 간결한 SQL로 복잡한 분석 작업을 수행가능 (유지보수가 간편하고 생산성 향상)

  - 이해 및 활용이 용이 (기존 SQL - syntax를 그대로 쓰기 때문에 ANSI SQL로 채택된다)

============================================================================

Ranking Family 특징
  - 대상집합에 대해서 특정 컬럼들을 기준으로 순위나 등급을 부여한다.
  - 오름차순, 내림차순 가능
  - NULL 은 순위의 가장 처음 또는 마지막으로 강제 처리된다.
  - Rank function은 각 PARTITOIN마다 초기화 된다.
  - 순위 또는 등급은 GROUP BY, CUBE, ROLLUP절 마다 초기화가 된다.

RANK() : 각 로우마다 순위를 매긴다.
            P -> O -> 1부터 시작하여 동일한 값은 동일한 순위를 가지고,
            동일한 순위의 수만큼 다음 순위는 건너 뛴다.

DENSE_RANK() : RANK와 다르게 동률있는 수만큼 순위를 미루지 않는다 ex) 1, 1, 2등

CUME_DIST() : CUMULATIVE DISTRIBUTION FUNCTION
  - PARTITION 나누어진 블럭별로 각 ROW를 ORDER BY에 명시된 순서대로 정렬한 후
    그룹별 상대적인 위치(누적된 분산정보)를 구한다

  - 상대적인 위치는 구하고자 하는 값보다 작거나 같은 값을 가진 ROW수를
    그룹 내의 전체 ROW수로 나눈 것을 의미한다.

  - 결과값의 범위는 0< X <=1 이다.

NTILE() : () 안의 숫자만큼 나눠라
  ex) PARTITION 내에 100개의 ROW가 있는데 4개의 BUCKET값을 나누고 싶다. NTILE(4)
                 =>  1개의 BUCKET 당 25씩 ROW가 배정된다.
  ex) 103개의 ROW에 대해서 NTILE(5)를 적용
      - 근사치로 배분한 후 남는 값에 대해서 최초 PARTITION부터 한 개씩 배분

ROW_NUMBER()
  - PARTITION 내에 ORDER BY 절에 의해 정렬된 순서로 유일한 값을 리턴한다.
  - ROWNUM 과는 전혀 상관없다.

============================================================================

WINDOWING : 윈도우 집계함수는 ROW에 대한 집계함수

REPORTING : 한 집합 레벨에 대한 집계 값과 다른 집합 레벨에 대한 집계 값의 비교를 통해
                 분석하고자 하는 경우
    ex) 사원의 급여와 해당 부서의 평균 급여를 비교할 때
         사원의 급여를 제외한 부서의 평균 급여를 알고 싶을 때

RATIO_TO_REPORT() : 해당 구간에서 차지하는 비율을 리턴하는 함수

Lead / Lag Family : 
  - 특정 ROW가 속한 파티션 내에서 상대적 상하 위치에 있는 특정 ROW의 COLUMN 값을
    참조하거나 상호 비교할 때 사용하는 함수

============================================================================

예제모음

Q1) 사원테이블에서 사원의 이름, 부서번호, 급여, 급여가 많은 사원으로부터 순위를 조회하자

SELECT ENAME, DEPTNO, SAL, RANK() OVER(ORDER BY SAL DESC) "RANK"
FROM EMP;

SELECT ENAME, DEPTNO, SAL, RANK() OVER(ORDER BY SAL DESC) "RANK"
FROM EMP
ORDER BY 3;

SELECT ENAME, DEPTNO, SAL, 
                      RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) "RANK"
FROM EMP;


Q2) 샘플 테이블 생성, 값 넣기

CREATE TABLE TEST_EMP
AS
SELECT * FROM EMP;

INSERT INTO TEST_EMP (EMPNO, ENAME, SAL, DEPTNO) VALUES ( 111, 111, 3000, 30);
INSERT INTO TEST_EMP (EMPNO, ENAME, SAL, DEPTNO) VALUES ( 222, 222, 3000, 30);
INSERT INTO TEST_EMP (EMPNO, ENAME, SAL, DEPTNO) VALUES ( 333, 333, 3000, 20);
INSERT INTO TEST_EMP (EMPNO, ENAME, SAL, DEPTNO) VALUES ( 444, 444, 3000, 20);


Q3) 

SELECT ENAME, DEPTNO, SAL, 
                      RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) "RANK"
FROM TEST_EMP;


Q4) TEST_EMP 테이블에서 DENSE_RANK() PARTITON, ORDER BY로 구해보자

SELECT ENAME, DEPTNO, SAL, 
                      DENSE_RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) "RANK"
FROM TEST_EMP;


Q5) 20번 부서 사원의 이름, 봉급, 누적분산 정보를 조회하자

SELECT ENAME, SAL, CUME_DIST() OVER ( ORDER BY SAL DESC) "상위 몇%"
FROM TEST_EMP
WHERE DEPTNO = 20;


Q6) 사원의 봉급을 기준으로 4등급으로 분류하자

SELECT ENAME, SAL, NTILE(3) OVER(ORDER BY SAL DESC)
FROM TEST_EMP;


Q7) 사원번호, 이름, 봉급, 입사일을 조회하는데 순번을 매기자 ( ROW_NUMBER() )

SELECT EMPNO, ENAME, SAL, HIREDATE, 
ROW_NUMBER() OVER( ORDER BY SAL DESC, HIREDATE ASC) AS "순번"
FROM TEST_EMP;


SELECT EMPNO, ENAME, SAL, HIREDATE, 
ROW_NUMBER() OVER( ORDER BY SAL DESC, HIREDATE ASC) AS "순번"
FROM TEST_EMP
ORDER BY 1;


Q8) 사원의 이름, 부서번호, 급여, 전체급여 합계, 부서별 합계를 리턴
SELECT ENAME, DEPTNO, SAL, SUM(SAL)
FROM EMP;  --> 안됨

SELECT ENAME, DEPTNO, SAL, 
SUM(SAL) OVER(PARTITION BY DEPTNO) "DEPT SUM",
SUM(SAL) OVER() "TOTAL SUM"
FROM EMP; 


Q9) 사원의 이름, 부서번호, 급여, 업무별 급여평균, 해당 업무의 최대 급여를 조회
SELECT ENAME, DEPTNO, JOB, SAL, 
AVG(SAL) OVER(PARTITION BY JOB) "AVG JOB",
MAX(SAL) OVER(PARTITION BY JOB) "MAX JOB"
FROM EMP;


Q10) 사원이름, 부서번호, 봉급 합계를 3줄씩 더한 결과, 누적 합계를 구해보자
SELECT ENAME, DEPTNO, SAL,
SUM(SAL) OVER(ORDER BY SAL ROWS BETWEEN 1 preceding AND 1 following) "SUM 1",
SUM(SAL) OVER(ORDER BY SAL ROWS UNBOUNDED PRECEDING) "SUM 2"
FROM TEST_EMP;

2번 줄은 ROWS를 기준으로 위 ROW와 아래 ROW의 SAL을 합친 값
3번 줄은 누적 값


Q11) 사원의 총 월급을 50000으로 증가했을 때 기존 월급 비율로 적용했을 경우
       각 사원은 얼마씩 받게되는지 확인해보자

SELECT ENAME, SAL, 
RATIO_TO_REPORT(SAL) OVER() AS "비율",
TRUNC( RATIO_TO_REPORT(SAL) OVER() * 50000) AS "받을 급여"
FROM TEST_EMP;


Q12) 사원이름, 부서번호, 봉급, 본인 이전의 봉급 값을 조회 LAG

SELECT ENAME, DEPTNO, SAL, 
LAG(SAL, 1, 0) OVER (ORDER BY SAL) AS NET_SAL,
LAG(SAL, 1, SAL) OVER (ORDER BY SAL) AS SAL2
FROM TEST_EMP;

SELECT ENAME, DEPTNO, SAL, 
LAG(SAL, 1, 0) OVER (PARTITION BY DEPTNO ORDER BY SAL) AS SAL3
FROM TEST_EMP;


Q13) 사원이름, 부서번호, 봉급, 본인 이전의 봉급 값을 조회 LEAD

SELECT ENAME, DEPTNO, SAL, 
LEAD(SAL, 1, 0) OVER (ORDER BY SAL) AS NET_SAL,
LEAD(SAL, 1, SAL) OVER (ORDER BY SAL) AS SAL2
FROM TEST_EMP;

SELECT ENAME, DEPTNO, SAL, 
LEAD(SAL, 1, 0) OVER (PARTITION BY DEPTNO ORDER BY SAL) AS SAL3
FROM TEST_EMP;

SELECT ENAME, DEPTNO, SAL, 
LAG(SAL, 1, 0) OVER (ORDER BY SAL) AS NET_SAL,
LEAD(SAL, 1, 0) OVER (ORDER BY SAL DESC) AS NET_SAL
FROM TEST_EMP;  --> 이게 결과가 같음

※ TO_DATE의 'RR'형식과 'YY'형식

    YY 형식은 날짜의 년도부분을 현재의 세기로 해석
    RR 형식은 현재 년도와 지정된 년도의 두자리를 비교하여 50년을 기준으로 해석을 달리함
    현재연도가 00~49 사이라면 'RR' 
    01~49까지는 00년대로 해석하고
    50~99까지는 1900년대로 해석한다.

※ 문자는 왼쪽정렬, 숫자는 우측정렬

※ 추가 변환함수

    TO_TIMESTAMP : CHAR, VARCHAR2 데이터 타입을 TIMESTAMP로 변환
    TO_TIMESTAMP_TZ : CHAR, VARCHAR2 데이터 타입을 TIMESTAMP WITH TIME ZONE 타입으로 변환
    TO_YMINTERVAL : CHAR, VARCHAR2 데이터 타입을 INTERVAL YEAR TO MONTH 타입으로 변환
    TO_DSINTERVAL : CHAR, VARCHAR2 데이터 타입을 INTERVAL DAY TO SECOND 타입으로 변환

※ 기타함수

    NVL(column, exp1) : NULL 값을 exp1 값으로 변환
    NVL2(column, exp1, exp2) : 해당 컬럼이 NULL일 경우 exp2 값을, NULL이 아니면 exp1 값을 리턴
    NULLIF(exp1, exp2) : exp1과 exp2 값을 비교해 같으면 NULL, 다르면 exp1 값을 리턴
    COALESCE(exp1, exp2, ..... , expN) : exp1, exp2, ..... , expN 중 null이 아닌 첫 번째 값을 이용
    DECODE : if ~ then ~ else 문의 역할 수행 ( 조건부 조회를 쉽게)

    [형식]

    DECODE(VALUE, IF1, THEN1, IF2, THEN2.....) : VALUE의 값이 IF1일 경우 THEN1값으로 리턴 .... 
    CASE WHEN 조건식 THEN
        EXP1 END                   : if ~ then ~ else 문의 역할 수행

============================================================================

1. 그룹함수 : GROUP BY 절, GROUP 함수를 선언하고 활용할 수 있다.
2. HAVING절을 사용해서 GROUP 제한을 줄 수 있다.
3. ROLLUP 연산을 사용해서 하위 총계값을 계산할 수 있다.
4. CUBE 연산을 사용해서 모든 열 조합에 대한 그룹함수를 적용할 수 있다.
5. GROUPING 함수를 사용해서 ROLLUP, CUBE를 통해 만들어진 행 값을 식별할 수 있다.
6. GROUPING SETS 절을 사용해서 원하는 조합을 설정할 수 있다.

그룹함수(다중 행 함수 = Multi Row) : 여러개의 ROW데이터를 묶어서 처리한다.
  - 여러 행들의 그룹이 모여서 그룹당 하나의 결과를 리턴하는 함수
  - SELECT, HAVING, ORDER BY절에 사용될 수 있다.
  - SELECT 문장 내의 GROUP BY절은 행들을 그룹화 한다.
  - HAVING절은 그룹을 제한하기 위해 사용한다.
  - 집합함수, 집계함수, 그룹함수라고도 한다.

종류
    COUNT(*) : NULL값을 포함한 행의 수
    COUNT(표현식) : 표현식의 값이 NULL값을 제외한 행의 수
    SUM ([DISTINCT | ALL] 표현식) : 표현식의 값이 NULL값을 제외한 합
    AVG ([DISTINCT | ALL] 표현식) : 표현식의 값이 NULL값을 제외한 평균

    MIN ([DISTINCT | ALL] 표현식) : 표현식의 값이 NULL값을 제외한 최소값
    MAX ([DISTINCT | ALL] 표현식) : 표현식의 값이 NULL값을 제외한 최대값
      - 문자, 날짜데이터 사용가능, 나머지는 숫자만

    STDDEV ([DISTINCT | ALL] 표현식) : 표현식의 값이 NULL값을 제외한 표준편차
    VARIAN ([DISTINCT | ALL] 표현식) : 표현식의 값이 NULL값을 제외한 분산

그룹 함수 구문
SELECT 그룹함수 
FROM 테이블명
WHERE 조건             ----------- > 그룹의 조건문 사용 불가
GROUP BY 컬럼1, 컬럼2    -------- > 별칭 사용 불가
HAVING 그룹에 대한 조건
ORDER BY 컬럼1, 컬럼2

============================================================================

GROUP BY에 ROLLUP 및 CUBE 연산자 사용
  - ROLLUP 및 CUBE 연산자를 질의의 GROUP BY절에 지정할 수 있다.
  - ROLLUP 그룹화는 정규 그룹화 행과 하위 총계값을 포함하는 결과 집합을 산출한다.
  - CUBE 연산자를 GROUP BY절에 사용하면 지정된 표현식에서 가능한 모든 조합 값에 따라
    선택된 행이 그룹화되고 각 그룹에 대한 요약 정보를 나타내는 행이 반환된다.
  - CUBE : ROLLUP의 결과 행 및 교차 도표화 행을 포함하는 결과 집합을 산출한다.

ROLLUP 연산자
  - 보고서를 작성할 때 집합에서 통계 및 요약정보를 추출하는데 사용할 수 있다.
  - GROUP BY 절에 지정된 열 목록을 따라 오른쪽에서 왼쪽 방향으로 하나씩 그룹을 만든다.
    그런 다음 그룹함수를 생성한 그룹에 적용한다.
  - ROLLUP 연산자 없이 N(GROUP BY 열 수)차원의 하위 총계를 산출하려면 
    N+1개의 SELECT 문을 UNION ALL로 연결해야 한다. 그러면 모든 SELECET 문이
    각각 테이블에 엑세스하므로 질의가 비효율적으로 실행된다.
  - 단 한 번 테이블에 액세스하여 해당 결과를 취합한다.
  - 하위 총계를 산출하는데 필요한 열이 많은 경우 유용하다.

CUBE : GROUP BY 확장형이다.
  - ROLLUP은 하위 총계 조합 중에 일부만 산출하지만 CUBE는 GROUP BY절에 지정된
    모든 그룹의 조합에 대해 하위 총계와 최상위 총계를 산출한다.
  - 집계함수에 CUBE 연산자를 사용하면 결과 집합에 추가 행이 만들어진다.
  - GROUP BY절에 N개의 열이 있을 경우 상위 집계 조합수는 2^N개 이다.

GROUPING

  - GROUPING 함수는 ROLLUP, CUBE 연산자와 함께 사용한다.
  - 행에서 하위 총계를 형성한 그룹을 찾을 수 있다.
  - ROLLUP, CUBE를 통해 만들어진 NULL 값과 저장된 NULL 값을 구별할 수 있다.
  - 0 (해당열) 또는 1(집계 연산결과)를 리턴한다.