오라클 인라인 뷰(Oracle Inline View)

 

오라클 인라인 뷰(Oracle Inline View)

인라인 뷰inline view는 실제 뷰가 아니라 SELECT의 FROM절에 있는 서브 쿼리sub query로 FROM 절에서 테이블 처럼 사용될 데이터를 정의할 때 사용 합니다. FROM절의 테이블을 대체 할 수 있다고 해서 파생 테이블이라고도 부릅니다.

서브 쿼리는 FROM절에서도 사용 가능 한데 이를 뷰(View)인데 CREATE VIEW 명령어로 만들지 않고 SQL문의 해당 라인에 직접 기술한다고 해서 인라인 뷰(InLine View)라고 합니다. SQL문 라인에 직접 기술하니 필요한 시점에만 사용되는 특징이 있습니다.

인라인 뷰를 사용하면 조인 작업을 제거하거나 개별 쿼리를 단일 쿼리로 통합해서 복잡한 쿼리를 단순화 할 수 있습니다. 또한 복잡한 조인 연산시 조인의 타겟 테이블의 데이터 개수를 줄일 수 있습니다.

인라인뷰를 이용한 쿼리 예문을 실습해 보겠습니다.

실습

인라인 뷰를 이용하여 EMP 테이블에서 부서코드별 급여 평균, 합을 구하고 부서코드(deptno)를 조인 키로 DEPT 테이블과 조인하여 부서명을 읽습니다.

인라인뷰를 사용하여 사원(EMP), 부서(DEPT) 테이블에서 부서별로 부서명, 급여의 평균, 급여의 합계를 출력 하세요. (부서명으로 오름차순 정렬, 급여 평균은 소수이하 첫째 자리에서 반올림)

SELECT DNAME, AVG_SAL, SUM_SAL

FROM ( 

              SELECT DEPTNO, ROUND(AVG(SAL)) AVG_SAL, SUM(SAL) SUM_SAL

              FROM    EMP

              GROUP BY DEPTNO

           ) E, DEPT D

WHERE E.DEPTNO = D.DEPTNO

ORDER BY DNAME;

<실행결과>

	DNAME	AVG_SAL	SUM_SAL
1	ACCOUNTING	1875	3750
2	RESEARCH	2175	10875
3	SALES	1567	9400

ROUND 함수에 별다른 파라미터를 사용하지 않으면 소수이하 첫째 자리에서 반올림 합니다.

인라인 뷰의 모양은 FROM절 아래의 서브 쿼리 형태이며 이 부분이 바깥쪽 메인 쿼리보다 먼저 실행되고 DEPT 테이블과 조인 후 최종 결과가 추출 됩니다.

이번에는 인라인 뷰를 이용하지 않고 동일한 결과를 만들어 보겠습니다.

인라인뷰를 사용하지 않고 사원(EMP), 부서(DEPT) 테이블에서 부서별로 부서명, 급여의 평균, 급여의 합계를 출력 하세요. (부서명으로 오름차순 정렬, 급여 평균은 소수이하 첫째 자리에서 반올림)

SELECT DNAME, ROUND(AVG(SAL)) AVG_SAL, SUM(SAL) SUM_SAL

FROM EMP E, DEPT D

WHERE E.DEPTNO = D.DEPTNO

GROUP BY DNAME

ORDER BY DNAME;

<실행결과>

	DNAME	AVG_SAL	SUM_SAL
1	ACCOUNTING	1875	3750
2	RESEARCH	2175	10875
3	SALES	1567	9400

 

#inlineview, #인라인뷰, #오라클인라인뷰, #ORACLE, #오라클, #오라클교육, #ORACLE강좌

오라클 읽기 전용 테이블(ORACLE Read-Only Tables)

 

오라클 읽기 전용 테이블(ORACLE Read-Only Tables)

오라클 11g 이전 버전에서 테이블은 다른 사용자에게 SELECT 권한을 주는 경우에만 읽기전용으로 운영이 가능했고, 테이블을 생성한  소유자(OWNER)는 테이블에 Read/Write 권한이 있어 이 테이블을 읽기전용으로 운영하는 것은 불가능 했습니다. 하지만 오라클 11g에서부터 “ALTER TABLE ~ READ ONLY” 명령으로 테이블스페이스의 다른 테이블에 영향을 주지 않고 원하는  테이블을 읽기 전용으로 만들 수 있습니다. 

읽기 전용으로 지정되면  TRUNCATE/INSERT/UPDATE/DELETE 작업과 ALTER TABLE ADD/MODIFY/RENAME/DROP COLUMNS 등의 작업은 불가능 하며 DROP TABLE, ALTER TABLE ADD/MODIFY/RENAME,  ALTER TABLE RENAME 등의 작업은 가능 합니다.

[기본형식]

ALTER TABLE table_name READ ONLY ALTER TABLE table_name READ WRITE

테이블을 생성하고 읽기전용으로 만들어 값을 입력해 보겠습니다. DML(INSERT/UPDATE/DELETE) 및 DDL(CREATE/DROP/TRUNCATE)들이 작동하는지를 확인해 봅니다.

실습

실습을 위해 EMP10 테이블을 생성 합니다. 

다음과 같은 조건으로 EMP10 테이블을 생성하세요. 사번(empno) number, generated as identity primary key(column level constraints) 성명(ename) varchar2(50)

CREATE TABLE EMP10 (

     EMPNO NUMBER GENERATED AS IDENTITY 

                                       CONSTRAINTS PK_EMP10 PRIMARY KEY ,                                          

     ENAME VARCHAR2(50)

);

<실행결과>

Table EMP10이(가) 생성되었습니다.

‘홍길동1’ 데이터를 입력하세요.

INSERT INTO EMP10 (ENAME) VALUES ('홍길동1');

<실행결과>

1 행 이(가) 삽입되었습니다.

DML 테스트를 위해 테이블을 읽기 전용으로 변경 합니다.

EMP10 테이블을 읽기전용으로 변경 하세요.

ALTER TABLE EMP10 READ ONLY;

<실행결과>

Table EMP10이(가) 변경되었습니다.

DML(INSERT/UPDATE/DELETE)을 테스트 해 보겠습니다.

‘홍길동2’ 데이터를 입력하세요.

INSERT INTO EMP10 (ENAME) VALUES ('홍길동2');

<실행결과>

ORA-12081: "SCOTT"."EMP10" 테이블에 작업을 업데이트하는 것이 허용되지 않습니다

‘홍길동1’ 데이터의 이름을 ‘홍길동2;로 변경 하세요.

UPDATE EMP10

SET        ENAME = '홍길동2'

WHERE  ENAME = '홍길동1';

<실행결과>

ORA-12081: "SCOTT"."EMP10" 테이블에 작업을 업데이트하는 것이 허용되지 않습니다

‘홍길동1’ 데이터를 삭제하세요.

DELETE FROM EMP10

WHERE ENAME = '홍길동1';

<실행결과>

ORA-12081: "SCOTT"."EMP10" 테이블에 작업을 업데이트하는 것이 허용되지 않습니다

DDL 명령어중 하나인 TRUNCATE TABLE을 테스트 해 보겠습니다.

EMP10 테이블의 모든 데이터를 롤백 정보 없이 삭제 하세요.

TRUNCATE TABLE EMP10;

<실행결과>

ORA-12081: "SCOTT"."EMP10" 테이블에 작업을 업데이트하는 것이 허용되지 않습니다.

TRUNCATE TABLE 명령은 테이블의 데이터를 롤백 정보를 보관하지 않고 순식간에 잘라 버리는 명령으로 대용량의 테이블 데이터라고 할지라도 한순간에 삭제해 버릴 수 있습니다. 물론 롤백 정보를 보관하지 않으므로 삭제 후 복구는 불가능 합니다. 하지만 테이블에 트리거, 인덱스 및 기타 종속성이 많은 경우 DELETE문으로 행을 모두 제거하는 것보다 TRUNCATE TABLE 문이 있는 행을 제거하는 것이 더 빠를 수 있습니다. TRUNCATE TABLE  구문은 DDL 구문으로 WHERE 절을 사용할 수는 없습니다.

읽고 쓰기로 테이블의 상태를 변경 후 데이터를 한건 입력해 보고 다시 읽기전용으로 변경 하여 테이블을 삭제해 보겠습니다.

EMP10 테이블을 읽고 쓸수 있는 상태로 변경한 후 ‘홍길동2’ 데이터를 입력하세요.

ALTER TABLE EMP10 READ WRITE;

INSERT INTO EMP10 (ENAME) VALUES ('홍길동2');

<실행결과>

1 행 이(가) 삽입되었습니다.

READ WRITE 상태로 변경을 하니 데이터가 잘 입력 됩니다.

EMP10 테이블을 읽기 전용 상태로 변경 후 테이블을 삭제 하세요.

ALTER TABLE EMP10 READ ONLY;

DROP TABLE EMP10;

<실행결과>

Table EMP10이(가) 삭제되었습니다.​

 

#읽기전용테이블, #readonlytable, #오라클, #ORACLE, #ORACLE강좌, #ORACLE교육

오라클 가상컬럼(Oracle Virtual Columns)

 

오라클 가상컬럼(Oracle Virtual Columns)

오라클 11g에서 새로 소개된 가상 컬럼(Virtual Column)은 다른 컬럼 값 또는 다른 표현식을 사용하여 값이 자동으로 계산되는 테이블의 가상 컬럼 입니다. 다른 컬럼의 값들을 이용하여 새로운 값을 만들어 내는 경우에 유용하게 사용될 수 있습니다.

[기본형식]

COLUMN_NAME [DATATYPE] [GENERATED ALWAYS] AS [EXPRESSION] [VIRTUAL] COLUMN_NAME : 가상 컬럼의 이름 DATATYPE: 가상 컬럼의 데이터 유형을 지정합니다. 생략하면 가상 컬럼은 표현식 결과의 데이터 유형을 사용합니다. AS EXPRESSION: AS 키워드뒤에 표현식을 기술 합니다. 이 식의 값이 가상컬럼에 대입됩니다. GENERATED ALWAYS, VIRTUAL : 구문의 명확성을 위해 기술 합니다. 생략가능

일반 컬럼과 유사하지만 다음과 같은 차이가 있습니다.

- 표현식등에 의해 정의.(사원 테이블에서 급여와 수당 컬럼을 이용하여 연봉을 계산)

- DB에 저장되지는 않으며 실행 중에 계산됩니다..

- UPDATE, DELETE의 WHERE절에 나타날 수 있지만 컬럼값 자체에 대한 변경은 불가능 합니다.

실습

가상컬럼을 이용하여 테이블을 생성하고 생성된 가상칼럼을 확인해 보겠습니다.

다음과 같은 조건으로 EMP10 테이블을 생성하세요. 사번(empno) number, primary key 성명(ename) varchar2(50) 급여(pay) number 연봉(annaul_income) : pay * 10 표현식을 기반으로 가상컬럼으로.

CREATE TABLE EMP10 (

  EMPNO NUMBER CONSTRAINTS PK_EMP10 PRIMARY KEY,

  ENAME VARCHAR2(50),

  PAY NUMBER,

  ANNUAL_INCOME NUMBER GENERATED ALWAYS AS (PAY * 12) VIRTUAL

);

<실행결과>

Table EMP10이(가) 생성되었습니다.

생성된 EMP10 테이블의 컬럼들을 조회해 보겠습니다.

SELECT  COLUMN_NAME

              , DATA_TYPE

              , DATA_LENGTH

              , DATA_DEFAULT

              , VIRTUAL_COLUMN

 FROM    USER_TAB_COLS

 WHERE TABLE_NAME = 'EMP10';

\<실행결과>

	COLUMN_NAME	DATA_TYPE	DATA_LENGTH	DATA_DEFAULT	VIRTUAL_COLUMN
1	EMPNO	NUMBER	22		NO
2	ENAME	VARCHAR2	50		NO
3	PAY	NUMBER	22		NO
4	ANNUAL_INCOME	NUMBER	22	PAY*12	YES

실습

생성한 EMP10 테이블에 EMP 테이블의 컬럼 및 데이터를 근간으로 값을 입력합니다. 단 annual_income 컬럼에는 값을 입력하지 않습니다. 다른 컬럼값(pay)에 의해 자동으로 생성되니까요. INSERT 후 EMP10 테이블의 데이터를 조회 합니다.

EMP10 테이블에 데이터를 입력합니다. EMP 테이블의 empno, ename, sal 컬럼을 EMP10 테이블의 empmno, ename, pay 컬럼으로 입력 합니다.

 INSERT INTO EMP10 (EMPNO, ENAME, PAY)

 SELECT EMPNO, ENAME, SAL FROM EMP;

<실행결과>

14개 행 이(가) 삽입되었습니다.

EMP10 테이블의 전체 데이터를 조회 합니다. annual_income 컬럼에 값이 생성 되어 있는것을 확인 하세요.

SELECT * FROM EMP10;

<실행결과>

	EMPNO	ENAME	PAY	ANNUAL_INCOME
1	7369	SMITH	800	9600
...	...	...	...	...
13	7902	FORD	3000	36000
14	7934	MILLER	1300	15600

실습

EMP10 테이블에 가상컬럼을 추가해 보겠습니다. 컬럼명 뒤에 AS 온다는 사실 항상 기억하세요. 가상컬럼의 데이터타입은 별도로 가술하지 않으면 표현식의 데이터타입을 따라갑니다.

EMP10 테이블에 급여등급(grade) 칼럼을 가상컬럼으로 추가 하세요. pay<= 1190 dlaus ‘Low’, pay> 1200 AND pay<= 2500 이면 ‘Normal’, pay> 2500 이면 ‘High’ 값을 갖는 컬럼 입니다.

ALTER TABLE EMP10 ADD

(

    GRADE AS (

            CASE 

                WHEN PAY <= 1190 THEN 'Low' 

                WHEN PAY > 1200 AND PAY <= 2500 THEN 'Normal'

                WHEN PAY > 2500 THEN 'High'

            END)

);

<실행결과>

Table EMP10이(가) 변경되었습니다.

EMP10 테이블의 전체 데이터를 조회 합니다. grade컬럼의 값을 확인 하세요.

SELECT * FROM EMP10;

<실행결과>

	EMPNO	ENAME	PAY	ANNUAL_INCOME	GRADE
1	7369	SMITH	800	9600	Low
...	...	...	...	...
13	7902	FORD	3000	36000	High
14	7934	MILLER	1300	15600	Normal

 

#가상컬럼, #가상칼럼, #Virtual컬럼, #VirtualColumn, #Oracle강좌, #ORACLE교육