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 수행불가)

SEQUENCE

< 시퀀스 SEQUENCE >
자동으로 번호 발생시켜주는 역할을 하는 객체
정수값을 자동으로 순차적으로 생성해줌

EX) 회원번호, 사번, 게시글 번호 등등 채번할 때 주로 사용할 예정

1. 시퀀스객체 생성구문[표현법]
   CREATE SEQUENCE 시퀀스명
   [START WITH 시작숫자] --> 처음 발생시킬 시작값 지정
   [INCREMENT BY 증가값] --> 몇 씩 증가시킬건지 지정
   [MAXVALUE 최대값] --> 최대값 지정
   [CYCLE | NOCYCLE] --> 값 순환 여부 지정
   [CACHE 바이트 크기 | NOCACHE] --> 캐시메모리 여부 지정
   

• 캐시 메모리 : 미리 발생될 값들을 생성해서 저장해두는 공간
  매번 호출할 때 마다 새로이 번호를 생성하는 것보다
  캐시 메모리 공간에 미리 생성된 값들을 가져다 쓰게 되면 훨씬 속도가 빠름
  단, 접속이 끊기고 나서 재접속 후 기존에 생성되었던 값들은 날라가고 없음

테이블명 : TB
뷰명 : VW
시퀀스명 : SEQ
트리거명 : TRG

CREATE SEQUENCE SEQ_TEST ;

SELECT * FROM USER_SEQUENCE; -- 이 계정이 소유하고 있는 시퀀스들에 대한 정보 조회용
-- USER_TABLES, USER_VIEWS

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

2. 시퀀스 사용 구문 시퀀스명.CURRVAL : 현재 시퀀스의 값
   시퀀스명.NEXTVAL : 시퀀스값을 증가시키고 증가된 시퀀스의 값
   기존시퀀스 값에서 INCREMENT BY 값 만큼 증가된 값
   == 시퀀스명.CURRVAL + INCREMENT BY 값
   => 단, 시퀀스 생성 후 첫 NEXTVAL는 STRAT WITH 로 지정된 시작값으로 발생
   

SELECT SEQ_EMPNO.CURRVAL FROM DUAL;
--> NEXTVAL를 한번이라도 수행하지 않는 이상 CURRVAL를 수행할 수 없음!
--> 왜? : CURRVAL은 마지막에 성공적으로 수행된 NEXTVAL의 값을 저장해서 보여주는 임시값

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

SELECT SEQ_EMPNO.NEXTVAL FROM DUAL; --> 305

SELECT * FROM USER_SEQUENCES;

SELECT SEQ_EMPNO.CURRVAL FROM DUAL;

3. 시퀀스 변경[표현법]
   ALTER SEQUENCE 시퀀스명
   (INCREMENT BY 증가값)
   (MAXVALUE 최대값)
   (MINVALUE 최소값)
   (CYCLE } NOCYCLE)
   (CACHE 바이트크기 | NOCACHE) ** START WITH는 변경불가! => 정 바꾸고싶다면 삭제했다가 다시 생성
   

ALTER SEQUENCE SEQ_EMPNO
INCREMENT BY 10
MAXVALUE 400;

SELECT * FROM USER_SEQUENCES;

SELECT SEQ_EMPNO.NEXTVAL FROM DUAL; -- 320

-- SEQUENCE 삭제하기
DROP SEQUENCE SEQ_EMPNO;

---

-- 매번 새로운 사번이 발생되는 시퀀스 생성

CREATE SEQUENCE SEQ_EID
START WITH 300;

-- 사원이 추가될 때 실행할 INSERT문

INSERT INTO EMPLOYEE(EMP_ID, EMP_NAME, EMP_NO, JOB_CODE, HIRE_DATE)
VALUES(SEQ_EID.NEXTVAL, '홍길동', '910816-1209331', 'J2', SYSDATE);

INSERT INTO EMPLOYEE(EMP_ID, EMP_NAME, EMP_NO, JOB_CODE, HIRE_DATE)
VALUES(SEQ_EID.NEXTVAL, '홍길녀', '910816-2209331', 'J3', SYSDATE);

-- 사원에 대해 추가 "요청"시 실행할 SQL문
INSERT
INTO EMPLOYEE
(
EMP_ID
, EMP_NAME
, EMP_NO
, JOB_CODE
, HIRE_DATE
)
VALUES
(
SEQ_EID.NEXTVAL
, ? /사용자가 입력한 값/
, ? /사용자가 입력한 값/
, ? /사용자가 입력한 값/
, SYSDATE
)

PL/SQL (PROCUDURE LANGUAGE EXTENSION TO SQL)

:오라클 자체에 내장되어 있는 절차적인 언어

• 변수 정의, 조건처리(IF), 반복처리(LOOP, FOR, WHILE) 등을 지원하여 SQL의 단점을 보완
• 다수의 SQL문을 한번에 실행 가능(BLOCK구조)

PL/SQL 구조

• 선언부 (DECLARE SECTION)
  : DECLARE로 시작, 변수나 상수를 선언 및 초기화하는 부분
  => 생략가능
• 실행부 (EXECUTABLE SECTION)
  : BEGIN로 시작, SQL문 또는 제어문(조건문, 반복문) 등의 로직을 기술하는 부분
  => 생략불가
• 예외처리부 (EXCEPTION SECTION)
  : EXCEPTION로 시작, 예외발생시 실행할 구문을 기술해두는 부분
  => 생략가능

-- 간단하게 화면에 HELLO ORACLE 출력
SET SERVEROUTPUT ON
BEGIN 
    -- System.out.println("hello oracle")와 같음;
    DBMS_OUTPUT.PUT_LINE('HELLO ORACLE');
END;
/

1) DECLARE 선언부

: 변수 및 상수 선언해놓는 공간(선언과 동시에 초기화도 가능)
일반타입변수, 레퍼런스타입 변수, ROW타입변수

(1)-1 일반타입변수 선언 및 초기화

[표현법]
변수명 (CONSTANT) 자료형 (:= 값);

DECLARE 
    EID NUMBER;
    ENAME VARCHAR2(20);
    PI CONSTANT NUMBER := 3.14;
BEGIN 
   --  EID := 800;
   -- ENAME := '배장남';
   
   -- 사용자에게 입력받은 값 출력하기
    EID := &사번;
    ENAME := '&이름';
    DBMS_OUTPUT.PUT_LINE('EID:' || EID );
    DBMS_OUTPUT.PUT_LINE('ENAME:' || ENAME );
    DBMS_OUTPUT.PUT_LINE('PI:' || PI );
END;
/

(1)-2 레퍼런스 타입 변수 선언 및 초기화 (현재 존재하고있는 특정 컬럼의 데이터타입으로 지정)

[표현법]
변수명 테이블명.컬럼명%TYPE;

DECLARE 
    EID EMPLOYEE.EMP_ID%TYPE;
    ENAME EMPLOYEE.EMP_NAME%TYPE;
    SAL EMPLOYEE.SALARY%TYPE;
BEGIN
    /*
    EID := '200';
    ENAME := '선동일';
    SAL := 8000000;
    */
    -- 사번이 200인 사원의 사번, 이름, 급여
    SELECT EMP_ID , EMP_NAME , SALARY 
    INTO EID, ENAME, SAL -- 셀렉한 결과값을 변수값에 담고싶다면 INTO 붙여서 이렇게 하기! 
    FROM EMPLOYEE
    -- WHERE EMP_ID = '200';
    WHERE EMP_ID = &사번; -- 입력받은 값으로 조회하기
    -- WHERE DEPT_CODE = '&부서코드'; 
    => 오류남 !! 
       왜? SELECT INTO를 이용해서 조회결과를 변수에 담고자 한다면 반드시 한 행으로 조회되어야 함!
    
    DBMS_OUTPUT.PUT_LINE('EID:' || EID );
    DBMS_OUTPUT.PUT_LINE('ENAME:' || ENAME );
    DBMS_OUTPUT.PUT_LINE('SAL:' || SAL );
END;
/

----실습문제
/*
레퍼런스타입변수로 EID, ENAME, JCODE, SAL, DTITLE을 선언하고
각 자료형으로 EMPLOYEE의 EMP_ID, EMP_NAME, JOB_CODE, SALARY / DEPARTMENT의 DEPT_TITLE 컬럼들의 데이터타입을 참조하게끔

대체변수로 입력한 사원명과 일치하는 사원을 조회해서 각 변수에 대입 후 출력

*/

DECLARE
EID EMPLOYEE.EMP_ID%TYPE;
ENAME EMPLOYEE.EMP_NAME%TYPE;
JCODE EMPLOYEE.JOB_CODE%TYPE;
SAL EMPLOYEE.SALARY%TYPE;
DTITLE DEPARTMENT.DEPT_TITLE%TYPE;
BEGIN
SELECT EMP_ID, EMP_NAME, JOB_CODE, SALARY, DEPT_TITLE
INTO EID, ENAME, JCODE, SAL, DTITLE
FROM EMPLOYEE
JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
WHERE EMP_NAME = '&사원명';

DBMS_OUTPUT.PUT_LINE(EID || ', ' || ENAME || ', ' || JCODE || ', ' || SAL || ', ' || DTITLE);

END;
/

---

/*
1_3) ROW타입 변수
어떤 테이블의 한 행에 대한 모든 컬럼값을 다 담을 수 있는 변수

     [표현법] 변수명 테이블명%ROWTYPE;

*/

DECLARE
E EMPLOYEE%ROWTYPE;
BEGIN
SELECT *
INTO E
FROM EMPLOYEE
--WHERE EMP_ID = 201;
WHERE EMP_NAME = '&사원명';

--DBMS_OUTPUT.PUT_LINE(E);
DBMS_OUTPUT.PUT_LINE('사번 : ' || E.EMP_ID);
DBMS_OUTPUT.PUT_LINE('사원명 : ' || E.EMP_NAME);
DBMS_OUTPUT.PUT_LINE('급여 : ' || E.SALARY);
DBMS_OUTPUT.PUT_LINE('연봉 : ' || E.SALARY * 12);
DBMS_OUTPUT.PUT_LINE('보너스 포함 연봉 : ' || (E.SALARY + E.SALARY * NVL(E.BONUS, 0)) * 12);
-- 출력문 안에 산술연산식, 함수식 기술 가능

END;
/