#25. SQL(VIEW), TCL, JDBC(JAVA에서 oracle연결)

   25-1. VIEW

 < VIEW 뷰 >
    SELECT(쿼리문)을 저장해둘 수 있는 객체
    임시테이블 같은 존재(실제 데이터가 담겨있는 것은 아님!!)

 

    1. VIEW 생성 방법
    
    [ 표현법 ]
    CREATE VIEW 뷰명
        AS 서브쿼리

CREATE OR REPLACE VIEW VW_EMPLOYEE
    AS SELECT EMP_ID, EMP_NAME, DEPT_TITLE, SALARY, NATIONAL_NAME, JOB_NAME, BONUS
         FROM EMPLOYEE E, DEPARTMENT, LOCATION L, NATIONAL N, JOB J
        WHERE DEPT_CODE = DEPT_ID
          AND LOCATION_ID = LOCAL_CODE
          AND L.NATIONAL_CODE = N.NATIONAL_CODE
          AND E.JOB_CODE = J.JOB_CODE;

*뷰 생성시 좋은점 : 서브쿼리를 이용해서 그때그때 엄청 길게쓰면 힘듦
그래서 딱 한번만 뷰로 생성을 해두면 그때부턴 뷰를 이용해서 간단하게 조회할 수 있음

 

 뷰는 논리적인 가상테이블 => 실질적으로 데이터를 저장하고 있지 않음(단순히 쿼리문이 TEXT문구로 저장되어 있음)
 참고) 해당 게정이 가지고 있는 VIEW들에 대한 내용을 조회하고자 한다면 USER_VIEWS 데이터 딕셔너리를 조회하면 됨

 

    CREATE OR REPLACE VIEW 뷰명
        AS 서브쿼리;
        뷰 생성 시 기존에 중복된 이름의 뷰가 없다면 새로운 뷰를 만들어주고
        기존에 중복된 이름의 뷰가 존재한다면 해당뷰를 갱신(변경)하는 옵션

 

 * 뷰 컬럼에 별칭 부여
   서브쿼리의 SELECT절에 함수나 산술연산식이 기술되어 있는 경우 반드시 별칭 지정

CREATE OR REPLACE VIEW VW_EMP_JOB
    AS SELECT EMP_ID, EMP_NAME, JOB_NAME, 
              DECODE(SUBSTR(EMP_NO, 8, 1), '1', '남', '2', '여') "성별", 
              EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM HIRE_DATE) "근무년수"
         FROM EMPLOYEE
         JOIN JOB USING (JOB_CODE);

모든 컬럼에 별칭 부여한다면,

CREATE OR REPLACE VIEW VW_EMP_JOB (사번, 사원명, 직급명, 성별, 근무년수)
    AS SELECT EMP_ID, EMP_NAME, JOB_NAME, 
              DECODE(SUBSTR(EMP_NO, 8, 1), '1', '남', '2', '여'), 
              EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM HIRE_DATE)
         FROM EMPLOYEE
         JOIN JOB USING (JOB_CODE);

    * 생성된 뷰를 이용해서 DML(INSERT, UPDATE, DELETE) 사용 가능
    뷰를 통해서 변경하더라도 실제 데이터가 담겨있는 테이블(베이스테이블)에 적용이 됨!

 

    * 하지만 뷰를 가지고 DML이 불가능한 경우가 더 많음!
    
    1) 뷰에 정의되어 있지 않은 컬럼을 조작하는 경우
    2) 뷰에 정의되어 있지 않은 컬럼 중 베이스테이블 상에 NOTNULL제약조건이 지정된 경우
    3) 산술연산식 또는 함수를 통해서 정의되어있는 경우
    4) 그룹함수나 GROUP BY절이 포함된 경우
    5) DISTINCT구문이 포함된 경우
    6) JOIN을 이용해서 여러 테이블을 매칭시켜놓았을 경우

 

 

    * VIEW 옵션
    [ 상세표현법 ]
    CREATE OR REPLACE FORCE/NOFORCE VIEW 뷰명
    AS 서브쿼리
    WITH CHECK OPTION
    WITH READ ONLY;
    
    1) OR REPLACE : 해당 뷰가 존재하지 않으면 새로 생성 / 해당 뷰가 존재한다면 갱신시켜주는 옵션
    2) FORCE / NOFORCE
        - FORCE : 서브쿼리에 기술된 테이블이 존재하지 않아도 뷰가 생성
        - NOFORCE(생략 시 기본값) : 서브쿼리에 기술된 테이블이 반드시 존재해야만 뷰가 생성
    3) WITH CHECK OPTION : 서브쿼리에 조건절에 기술된 내용에 만족하는 값으로만 DML을 수행
                            조건에 부합하지 않는 값으로 수정하는 경우 오류 발생
    4) WITH READ ONLY : 뷰에 대해 조회만 가능(DML수행 불가)

CREATE OR REPLACE VIEW VW_EMPBONUS
    AS SELECT EMP_ID, EMP_NAME, BONUS
         FROM EMPLOYEE
        WHERE BONUS IS NOT NULL
  WITH READ ONLY;

 

25-2. SEQUENCE

    < 시퀀스 SEQUENCE >
    자동으로 번호를 발생시켜주는 역할을 하는 객체
    정수값을 자동으로 순차적으로 생성해줌
    
    예) 회원번호, 사번, 게시글 번호 등등 채번할 때 주로 사용할 예정
    
    1. 시퀀스 객체 생성 구문
    
    [ 표현법 ]
    CREATE SEQUENCE 시퀀스명
    START WITH 시작숫자         => 생략가능, 처음 발생시킨 시작값 지정
    INCREMENT BY 증가값         => 생략가능, 몇 씩 증가시킬건지 결정
    MAXVALUE 최대값            => 생략가능, 최대값 지정
    MINVALUE 최소값            => 생략가능, 최소값 지정
    CYCLE/NOCYCLE               => 생략가능, 값 순환 여부 지정
    CACHE 바이트크기 / NOCACHE   => 생략가능, 캐시메모리 여부 지정, CACHE_SIZE기본값은 20BYTE
    
    * 캐시메모리 : 미리 발생될 값들을 생성해서 저장해두는 공간
                 매 번 호출할 때마다 새롭게 번호를 생성하는 것보다
                 캐시공간에 미리 생성된 값들을 가져다 쓰게되면 훨씬 속도가 빠름
                 단, 접속이 끊기고 나서 재접속 후 기존에 생성된 값들은 날아가고 없음

 

CREATE SEQUENCE SEQ_EMPNO
 START WITH 300
 INCREMENT BY 5
 MAXVALUE 310
 NOCYCLE
 NOCACHE;

    2. 시퀀스 사용구문
    
    시퀀스명.CURRVAL : 현재 시퀀스의 값(마지막으로 성공적으로 발생된 NEXTVAL 값)
    시퀀스명.NEXTVAL : 시퀀스값을 증가시키고 증가된 시퀀스의 값
                    기존의 시퀀스 값에서 INCREMENT BY 값만큼 증가된 값
                    (시퀀스명.CURVAL + INCREMENT BY 값)
                    
                    시퀀스 생성 후 첫 NEXTVAL은 START WITH로 지정된 시작값으로 발생

SELECT SEQ_EMPNO.CURRVAL FROM DUAL;
-- ORA-08002: sequence SEQ_EMPNO.CURRVAL is not yet defined in this session
-- NEXTVAL을 한번이라도 수행하지 않으면 CURVAL을 사용할 수 없음
-- CURRVAL은 마지막에 성공적으로 수행된 NEXTVAL의 값을 저장해서 보여주는 임시값!!

SELECT SEQ_EMPNO.NEXTVAL FROM DUAL; -- 300
SELECT SEQ_EMPNO.CURRVAL FROM DUAL; -- 300

SELECT SEQ_EMPNO.NEXTVAL FROM DUAL; -- 305

SELECT * FROM USER_SEQUENCES;
-- LAST_NUMBER : 현재 상황에서 NEXTVAL을 실행할 경우 예정 값 -- 310
SELECT SEQ_EMPNO.NEXTVAL FROM DUAL; -- 310

    3. 시퀀스 병경
    
    [ 표현법 ]
    ALTER SEQUENCE 시퀀스명
    START WITH 시작숫자         => 생략가능, 처음 발생시킨 시작값 지정
    INCREMENT BY 증가값         => 생략가능, 몇 씩 증가시킬건지 결정
    MAXVALUE 최대값            => 생략가능, 최대값 지정
    MINVALUE 최소값            => 생략가능, 최소값 지정
    CYCLE/NOCYCLE               => 생략가능, 값 순환 여부 지정
    CACHE 바이트크기 / NOCACHE   => 생략가능, 캐시메모리 여부 지정, CACHE_SIZE기본값은 20BYTE
   
    * START WITH는 변경 불가 => 정 바꾸고 싶다면 해당 시퀀스를 삭제했다가 다시 생성

ALTER SEQUENCE SEQ_EMPNO
      INCREMENT BY 10
      MAXVALUE 400;

 

 

---------------------------------------------------------------------------------------------------------------------------------------------------------

JDBC에 oracle 연결 접근 (ojdbc6.jar)

경로 :  C:\oraclexe\app\oracle\product\11.2.0\server\jdbc\lib