SQL (7) OBJECT VIEW, OBJECT SEQUENCE, PL/SQL

VIEW

SELECT 쿼리의 실행 결과를 화면에 저장한 논리적 가상 테이블실제 테이블과는 다르게 실질적 데이터를 저장하고 있진 않지만사용자는 테이블을 사용하는 것과 동일하게 사용 가능

 

예시1

CREATE OR REPLACE VIEW V_EMPLOYEE

AS SELECT EMP_ID, EMP_NAME, DEPT_TITLE, NATIONAL_NAME

FROM EMPLOYEE

LEFT JOIN DEPARTMENT ON(DEPT_ID = DEPT_CODE)

LEFT JOIN LOCATION ON(LOCATION_ID = LOCAL_CODE)

LEFT JOIN NATIONAL USING(NATIONAL_CODE);

SELECT * FROM V_EMPLOYEE;

DML명령어로 VIEW 조작이 불가능한 경우

1. 뷰 정의에 포함되지 않은 컬럼을 조작하는 경우

2. 뷰에 포함되지 않은 컬럼 중에 베이스가 되는 컬럼이NOT NULL 제약조건이 지정된 경우

3. 산술 표현식으로 정의된 경우

4. 그룹함수나 GROUP BY절을 포함한 경우

5. DISTINCT를 포함한 경우

6. JOIN을 이용해 여러 테이블을 연결한 경우

 

1. 뷰 정의에 포함되지 않은 컬럼을 조작하는 경우

CREATE OR REPLACE VIEW V_JOB2

AS SELECT JOB_CODE

FROM JOB;

INSERT INTO V_JOB2 VALUES(‘J8’, ‘인턴’);

* 뷰 정의에 포함되지 않은 컬럼을 INSERT/UPDATE하는 경우 에러 발생* 단 DELETE는 가능

 

2. 뷰에 포함되지 않은 컬럼 중에 베이스가 되는 테이블 컬럼이

NOT NULL제약조건이 지정된 경우

CREATE OR REPLACE VIEW V_JOB3

AS SELECT JOB_NAME FROM JOB;

INSERT INTO V_JOB3 VALUES(‘인턴’);

* 뷰에 포함되지 않은 NOT NULL제약조건이 있는 컬럼이 존재하면INSERT/UPDATE 시 에러 발생 * 단 DELETE는 가능

 

3. 산술 표현식으로 정의된 경우

CREATE OR REPLACE VIEW EMP_SAL

AS SELECT EMP_ID, EMP_NAME, SALARY, (SALARY + (SALARY*NVL(BONUS, 0)))*12 연봉FROM EMPLOYEE; INSERT INTO EMP_SAL VALUES(800, ‘정진훈’, 3000000, 4000000);

* 뷰에 산술 계산식이 포함된 경우 INSERT/UPDATE 시 에러 발생 * 단 DELETE는 가능

 

4. 그룹함수 또는 GROUP BY절을 포함한 경우

CREATE OR REPLACE VIEW V_GROUPDEPT

AS SELECT DEPT_CODE, SUM(SALARY) 합계, AVG(SALARY) 평균

FROM EMPLOYEE GROUP BY DEPT_CODE;

INSERT INTO V_GROUPDEPT VALUES(‘D10’, 6000000, 4000000);

DELETE FROM V_GROUPDEPT WHERE DEPT_CODE = ‘D1’;

* 그룹함수 또는 GROUP BY를 사용한 경우 INSERT/UPDATE/DELETE 시 에러 발생

 

5. DISTINCT를 포함한 경우

CREATE OR REPLACE VIEW V_DT_EMP

AS SELECT DISTINCT JOB_CODE

FROM EMPLOYEE;

INSERT INTO V_DT_EMP VALUES(‘J9’);

DELETE FROM V_DT_EMP WHERE JOB_CODE = ‘J1’;

* DISTINCT를 사용한 경우 INSERT/UPDATE/DELETE 시 에러 발생

 

6. JOIN을 이용해 여러 테이블을 연결한 경우

CREATE OR REPLACE VIEW V_JOINEMP

AS SELECT EMP_ID, EMP_NAME, DEPT_TITLE

FROM EMPLOYEE

JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID);

INSERT INTO V_JOINEMP VALUES(888, ‘조세오’, ‘인사관리부’);

* 뷰 정의 시 JOIN을 사용한 경우 INSERT/UPDATE 시 에러 발생 * 단 DELETE는 가능

VIEW 구조

뷰 정의 시 사용한 쿼리 문장이 TEXT컬럼에 저장되어 있으며뷰가 실행할 때는 TEXT에 기록된 SELECT에 문장이 다시 실행되면서결과를 보여주는 구조 SELECT * FROM USER_VIEWS;

VIEW 옵션

1. OR REPLACE 옵션 생성한 뷰가 존재하면 뷰를 갱신함 2. FORCE/NOFORCE 옵션 FORCE옵션은 기본 테이블이 존재하지 않더라도 뷰 생성NOFORCE 옵션이 기본 값으로 지정되어 있음3. WITH CHECK OPTION 옵션 옵션을 설정한 컬럼의 값을 수정 불가능하게 함4. WITH READ ONLY 옵션 뷰에 대해 조회만 가능하고 삽입, 수정, 삭제 등은 불가능하게 함

INLINE VIEW

일반적으로 FROM절에 사용된 서브쿼리의 결과 화면에별칭을 붙인 것을 말함 FROM절에 서브쿼리를 직접 사용해도 되고 따로 뷰를 생성 후FROM절에 생성한 뷰를 사용해도 됨

SEQUENCE

순차적으로 정수 값을 자동으로 생성하는 객체로 자동 번호 발생기 역할을 함

SELECT * FROM USER_SEQUENCES;

NEXTVAL/CURRVAL 사용 가능 여부

사용가능	사용불가
서브쿼리가 아닌 SELECT문	VIEW의 SELECT절
INSERT문의 SELECT절	DISTINCT키워드가 있는 SELECT문
INSERT문의 VALUE절	GROUP BY,HAVING,ORDER BY절이 있는 SELECT문
UPDATE문의 SET절	SELECT, DELETE, UPDATE의 서브쿼리
	CREATE TABLE, ALTER TABLE 명령의 DEFAULT값

시퀀스 수정 시 CREATE에 사용한 옵션을 변경 가능 단

START WITH 값 변경은 불가하기 때문에 변경하려면 삭제 후 다시 생성

 

ALTER SEQUENCE SEQ_EMPID

INCREMENT BY 10

MAXVALUE 400

NOCYCLE NOCACHE;

PL/SQL

Procedural Language extension to SQL의 약자로 오라클 자체에 내장되어 있는 절차적 언어 SQL의 단점을 보완하여 SQL문장 내에서 변수의 정의, 조건 처리, 반복 처리 등 지원

구조	설명
DECLARE SECTION (선언부)	DECLARE로 시작 변수나 상수를 선언하는 부분
EXECUTABLE SECTION (실행부)	BEGIN으로 시작제어문, 반복문, 함수 정의 등 로직 기술
EXCEPTION SECTION (예외처리부)	EXCEPTION으로 시작예외사항 발생 시 해결하기 위한 문장 기술

SET SERVEROUTPUT ON; * 프로시저를 사용하여 출력하는 내용을 화면에 보여주도록 설정하는

환경변수로기본 값은 OFF여서 ON으로 변경

BEGIN

DBMS_OUTPUT.PUT_LINE(‘HELLO WORLD’);

END; /

* PUT_LINE이라는 프로시저를 이용하여 출력(DBMS_OTUPUT패키지에 속해있음)

 

변수의 선언과 초기화, 변수 값 출력

DECLARE

EMP_ID NUMBER; EMP_NAME VARCHAR2(30);

BEGIN

EMP_ID := 888;

EMP_NAME := ‘배장남’;

DBMS_OUTPUT.PUT_LINE(‘EMP_ID : ’ || EMP_ID);

DBMS_OUTPUT.PUT_LINE(‘EMP_NAME : ’ || EMP_NAME);

END;/

 

레퍼런스 변수의 선언과 초기화, 변수 값 출력

DECLARE

EMP_ID EMPLOYEE.EMP_ID%TYPE;

EMP_NAME EMPLOYEE.EMP_NAME%TYPE;

BEGIN

SELECT EMP_ID, EMP_NAME

INTO EMP_ID, EMP_NAME

FROM EMPLOYEE

WHERE EMP_ID = ‘&EMP_ID’;

DBMS_OUTPUT.PUT_LINE(‘EMP_ID : ’ || EMP_ID);

DBMS_OUTPUT.PUT_LINE(‘EMP_NAME : ’ || EMP_NAME);

END;/

 

한 행에 대한 ROWTYPE변수의 선언과 초기화, 값 출력

DECLARE

E EMPLOYEE%ROWTYPE;

BEGIN

SELECT * INTO E

FROM EMPLOYEE

WHERE EMP_ID = ‘&EMP_ID’;

DBMS_OUTPUT.PUT_LINE(‘EMP_ID : ’ || E.EMP_ID);

DBMS_OUTPUT.PUT_LINE(‘EMP_NAME : ’ || E.EMP_NAME);

DBMS_OUTPUT.PUT_LINE(‘EMP_NO : ’ || E.EMP_NO);

DBMS_OUTPUT.PUT_LINE(‘SALARY : ’ || E.SALARY);

END;

/

테이블 타입의 변수 선언과 초기화, 변수 값 출력

DECLARE

TYPE EMP_ID_TABLE_TYPE IS TABLE OF EMPLOYEE.EMP_ID%TYPE

INDEX BY BINARY_INTEGER;

TYPE EMP_NAME_TABLE_TYPE IS TABLE OF EMPLOYEE.EMP_NAME%TYPE

INDEX BY BINARY_INTEGER;

EMP_ID_TABLE EMP_ID_TABLE_TYPE;

EMP_NAME_TABLE EMP_NAME_TABLE_TYPE;

I BINARY_INTEGER := 0;

BEGIN

FOR K IN (SELECT EMP_ID, EMP_NAME FROM EMPLOYEE)

LOOP I := I + 1;

EMP_ID_TABLE(I) := K.EMP_ID;

EMP_NAME_TABLE(I) := K.EMP_NAME;

END LOOP;

FOR J IN 1..I LOOP

DBMS_OUTPUT.PUT_LINE(‘EMP_ID : ’ || EMP_ID_TABLE(J) || ‘, EMP_NAME : ’ || EMP_NAME_TABLE(J));

END LOOP;

END; /

선택문

 IF ~ THEN ~ END IF

DECLARE

EMP_ID EMPLOYEE.EMP_ID%TYPE;

EMP_NAME EMPLOYEE.EMP_NAME%TYPE;

SALARY EMPLOYEE.SALARY%TYPE;

BONUS EMPLOYEE.BONUS%TYPE;

BEGIN

SELECT EMP_ID, EMP_NAME, SALARY, NVL(BONUS, 0)

INTO EMP_ID, EMP_NAME, SALARY, BONUS

FROM EMPLOYEE

WHERE EMP_ID = ‘&EMP_ID’;

DBMS_OUTPUT.PUT_LINE(‘사번 : ’ || EMP_ID);

DBMS_OUTPUT.PUT_LINE(‘이름 : ’ || EMP_NAME);

DBMS_OUTPUT.PUT_LINE(‘급여 : ’ || SALARY);

IF(BONUS = 0)

THEN DBMS_OUTPUT.PUT_LINE(‘보너스를 지급받지 않는 사원입니다.’);

END IF;

DBMS_OUTPUT.PUT_LINE(‘보너스율 : ‘ || BONUS * 100 || ‘%’);

END;

/

IF ~ THEN ~ ELSE ~ END IF

DECLARE

EMP_ID EMPLOYEE.EMP_ID%TYPE;

EMP_NAME EMPLOYEE.EMP_NAME%TYPE;

DEPT_TITLE DEPARTMENT.DEPT_TITLE%TYPE;

NATIONAL_CODE LOCATION.NATIONAL_CODE%TYPE;

TEAM VARCHAR2(20);

BEGIN

SELECT EMP_ID, EMP_NAME, DEPT_TITLE, NATIONAL_CODE

INTO EMP_ID, EMP_NAME, DEPT_TITLE, NATIONAL_CODE

FROM EMPLOYEE E, DEPARTMENT D, LOCATION L

WHERE E.DEPT_CODE = D. DEPT_ID

AND D.LOCATION_ID = L.LOCAL_CODE

AND EMP_ID = '&EMP_ID';

IF(NATIONAL_CODE = 'KO') THEN TEAM := '국내팀';

ELSE TEAM := '해외팀';

END IF;

DBMS_OUTPUT.PUT_LINE('사번 : ' || EMP_ID);

DBMS_OUTPUT.PUT_LINE('이름 : ' || EMP_NAME);

DBMS_OUTPUT.PUT_LINE('부서 : ' || DEPT_TITLE);

DBMS_OUTPUT.PUT_LINE('소속 : ' || TEAM);

END;

/

IF ~ THEN ~ ELSIF ~ ELSE ~ END IF

DECLARE

SCORE INT;

GRADE VARCHAR2(2);

BEGIN

SCORE := ‘&SCORE’;

IF SCORE >= 90 THEN GRADE := ‘A’;

ELSIF SCORE >= 80 THEN GRADE := ‘B’;

ELSIF SCORE >= 70 THEN GRADE := ‘C’;

ELSIF SCORE >= 60 THEN GRADE := ‘D’;

ELSE GRADE := ‘F’;

END IF;

DBMS_OUTPUT.PUT_LINE(‘당신의 점수는 ’ || SCORE || ‘점이고, 학점은 ‘ || GRADE || ‘학점입니다.’);

END;

/

 

BASIC LOOP

DECLARE

N NUMBER := 1;

BEGIN

LOOP

DBMS_OUTPUT.PUT_LINE(N); N := N + 1;

IF N > 5 THEN EXIT;

END IF;

END LOOP;

END;

/

FOR LOOP

BEGIN FOR N IN 1..5 LOOP

DBMS_OUTPUT.PUT_LINE(N);

END LOOP;

END; /

BEGIN

FOR N IN REVERSE 1..5 LOOP

DBMS_OUTPUT.PUT_LINE(N);

END LOOP;

END; /

 

WHILE LOOP

DECLARE

N NUMBER := 1;

BEGIN

WHILE N <= 5 LOOP

DBMS_OUTPUT.PUT_LINE(N);

N := N + 1;

END LOOP;

END;

/

예외처리

미리 정의되지 않은 오라클 SERVER에러 예외 처리

DECLARE

DUP_EMPNO EXCEPTION;

PRAGMA EXCEPTION_INIT(DUP_EMPNO, -00001);

BEGIN

UPDATE EMPLOYEE

SET EMP_ID = ‘&사번’

WHERE EMP_ID = 200;

EXCEPTION

WHEN DUP_EMPNO

THEN DBMS_OUTPUT.PUT_LINE(‘이미 존재하는 사번입니다.’);

END;

/