데이터베이스를 사랑하는 사람들의 모임 데이터베이스 사랑넷

뷰는 테이블이나 다른 뷰에 기초하여 만드는 가상의 테이블이다. 뷰에는 실제의 데이터가 저장되는 것이 아니라 단지 뷰를 만드는 SQL 문만 가지고 있으면서 뷰를 사용할 때 딕셔너리에 저장된 SQL 문을 이용하여 원래의 테이블을 사용하는 명령으로 확장되어 실행된다. 위의 SQL 문의 잘못된 부분을 수정해서 뷰를 만든 다음에 딕셔너리를 보면 다음과 같이 소스 텍스트가 들어 있음을 알 수 있다.

SELECT TEXT

FROM USER_VIEWS

WHERE VIEW_NAME='VIEWEMP';

TEXT

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

SELECT empno, ename, sal, deptno

FROM emp

WHERE sal > 2000 and deptno = 10

WITH CHECK OPTION

뷰를 이용하면 원래의 테이블을 사용하는 SQL 문을 만들어 내기 위한 파싱 작업에 약간의 오버헤드가 걸린지만 일반적으로 SQL 문의 수행 시간에 비하면 파싱 시간은 무시할 정도이기 때문에 직접 테이블을 사용하는 SQL 문과 비교했을 때 속도 문제는 거의 발생하지 않는다고 볼 수 있다.

뷰를 사용하는 이유는 여러 가지가 있다. 예를 들면 복잡한 조인으로 얽혀 있는 SQL 문을 사용자들이 간단하게 쓸 수 있도록 한다거나 또는 테이블의 중요한 컬럼을 노출시키지 않음으로써 보안을 강화하는 목적으로도 사용된다.

(a)를 보면 뷰를 만들 때 FORCE 옵션을 사용하였는데 FORCE를 지정하면 뷰가 참조하는 테이블이 아직 존재하지 않거나 참조할 권한이 없는 경우에도 일단 뷰를 생성한다. 물론 FORCE를 이용해서 만들더라도 실제로 뷰를 사용하기 위해서는 뷰에서 참조하는 객체가 모두 만들어져 있어야 한다.

뷰를 통해서도 원래의 테이블에 제한적이지만 데이터를 추가하거나 수정, 삭제할 수 있는데 WITH CHECK OPTION을 사용하면 뷰가 SELECT 할 수 있는 레코드에 한해서 추가, 수정, 삭제할 수 있다. 그리고 WITH READ ONLY는 뷰를 SELECT 이외의 용도로는 사용할 수 없도록 한다. 따라서 이 두 가지 옵션은 서로 상충된 의미를 가지고 있기 때문에 함께 사용할 수는 없다.

정답 : (e) (<- 마우스로 정답 부분을 선택하세요)

12-2. 다음 중 뷰에 대한 설명으로 옳지 않은 것은? (세 가지)

a. 뷰를 만들 때에는 여러 개의 테이블을 참조할 수 있다.

b. 뷰는 참조하는 테이블과 같은 테이블스페이스에 저장된다.

c. 다른 뷰를 참조하는 뷰도 만들 수 있다.

d. 뷰에도 인덱스를 만들 수 있다.

e. 조인을 사용해서 만든 뷰를 통한 데이터 추가, 수정, 삭제는 불가능하다.

<해설>

뷰는 하나의 테이블이나 뷰를 참조해서 만들 수도 있고 여러 개의 테이블을 조인해서 만들기도 한다. 특별한 뷰에는 데이터 분할을 위한 파티션 뷰와 관계형 테이블을 객체로 포장하는 오브젝트 뷰가 있는데 여기서는 다루지 않는다.

뷰에는 데이터가 저장되는 것이 아니라 단지 시스템 테이블스페이스의 데이터 딕셔너리에 SELECT 문을 가지고 있을 뿐이다. 따라서 뷰에 대한 모든 정보는 시스템 테이블스페이스에 저장되므로 (b)는 옳지 않다.

뷰는 SELECT 문에 불과하므로 뷰에 직접 인덱스를 만들 수는 없으며 뷰가 참조하는 테이블에 걸려있는 인덱스를 그대로 사용하게 된다. 그러므로 만약 인덱스가 필요하다면 뷰에서 참조하는 테이블에 인덱스를 만들어야 한다.

과거에는 뷰를 만들 때 조인을 사용하면 뷰를 통한 데이터 변경이 불가능했지만 현재는 조인을 사용한 경우에도 제한적이지만 뷰를 통한 데이터의 추가, 변경, 삭제 작업을 할 수 있다. 자세한 내용은 다른 과목에서 다루기로 한다.

정답 : (b) (d) (e) (<- 마우스로 정답 부분을 선택하세요)

12-3. 다음 SQL 문에서 뷰를 통한 데이터 변경을 불가능하게 만드는 요소를 모두 골라라.

a. CREATE OR REPLACE VIEW viewemp

b. AS SELECT DISTINCT deptno, AVG(sal) SAL

c. FROM (SELECT * FROM emp)

d. WHERE empno > 7000

e. GROUP BY deptno

<해설>

하나의 테이블을 사용한 뷰(simple view)에서도 데이터의 변경이 허락되지 않는 경우가 많이 있다. 예를 들면 집합 함수나 그룹 함수를 사용한 경우, SELECT 리스트에서 DISTINCT를 지정한 경우, GROUP BY, CONNECT BY, START WITH 절을 사용한 뷰는 조회만 가능하다.

정답 : (b) (e) (<- 마우스로 정답 부분을 선택하세요)

12-4. 기존의 뷰를 변경해서 새로운 뷰를 만들고자 할 때 사용하는 명령은?

a. ALTER VIEW

b. CREATE VIEW

c. CREATE OR REPLACE VIEW

<해설>

ALTER VIEW 명령은 뷰를 컴파일 할 때 사용된다. 뷰는 참조하는 테이블의 존재 여부와 권한 등을 확인하기 위해서 컴파일 과정을 거치는데 뷰가 참조하는 테이블이 변경되었다면 해당 뷰를 사용할 때 자동으로 컴파일을 하게 된다. 만약 ALTER VIEW 명령으로 미리 컴파일을 해 두면 실행시에 컴파일 과정 없이 처리된다.

CREATE OR REPLACE VIEW 명령은 실제로 뷰를 만들 때 가장 많이 사용되는 명령이다. CREATE VIEW 명령을 사용하면 기존에 같은 이름의 뷰가 있다면 에러가 발생하지만 CREATE OR REPLACE VIEW는 같은 이름의 뷰가 있어도 새로운 뷰로 대체한다.

정답 : (c) (<- 마우스로 정답 부분을 선택하세요)

오라클 데이터 딕셔너리

13-1. 자신이 사용할 수 있는 테이블 이름을 확인할 때 사용되는 딕셔너리는?

a. DBA_TABLES

b. ALL_TABLES

c. USER_TABLES

d. V$DATABASE

e. TABS

<해설>

오라클의 데이터 딕셔너리는 데이터베이스의 핵심 정보가 기록되는 곳이다. 데이터 딕셔너리 테이블은 데이터베이스 엔진에서 전적으로 관리하며 사용자가 직접 변경하면 데이터베이스에 문제가 발생할 수 있으므로 단지 데이터 딕셔너리 뷰를 통해서 필요한 정보를 조회만 해야 한다.

데이터 딕셔너리 뷰에는 크게 세 가지가 있는데 각각은 DBA_, ALL_, USER_로 시작하는 접두어를 가지고 있으며 이 분류에 포함되지 않는 것도 일부 존재한다.

1) DBA 딕셔너리 뷰는 모든 사용자의 객체에 대한 정보를 보여 준다.

2) ALL 딕셔너리 뷰는 자신이 접근할 수 있는 모든 객체에 대한 정보를 보여준다.

3) USER 딕셔너리 뷰는 자신이 소유한 객체에 대한 정보를 보여준다.

다이나믹 퍼포먼스 뷰는 데이터베이스가 사용되고 있는 동안 동적으로 데이터베이스와 시스템 등의 상태를 반영하여 보여주는데 주로 퍼포먼스와 관련된 정보를 제공한다. 다이나믹 퍼포먼스 뷰는 V_$로 시작하는 이름을 가지고 있으며 catalog.sql 스크립트가 실행되면서 만들어진다. 실제로는 V$로 시작하는는 동의어(symonym)을 통해서 사용하게 된다.

자신이 사용할 수 있는 테이블에 관한 정보는 ALL_TABLES를 통해서 확인할 수 있다. TABS는 USER_TABLES에 대한 동의어이다.

정답 : (b) (<- 마우스로 정답 부분을 선택하세요)

13-2. dept 테이블의 deptno 컬럼에 걸려 있는 제약 조건의 이름을 알고 싶다면 어떤 딕셔너리를 참조해야 하는가?

a. USER_CONSTRAINTS

b. USER_TAB_CONSTRAINTS

c. USER_CONS_COLUMNS

d. USER_COL_CONSTRAINTS

e. USER_INDEXES

<해설>

제약 조건을 확인할 때에는 두 가지 뷰를 참조하면 된다. USER_CONSTRAINTS 뷰는 현재 만들어져 있는 제약 조건의 자세한 정보를 가지고 있지만 컬럼에 관한 정보는 찾아볼 수 없다. 따라서 어떤 컬럼에 어떤 제약 조건이 만들어져 있는지를 확인하려면 USER_CONS_COLUMNS도 함께 참조해야 한다. 예를 들어 dept 테이블에 걸려 있는 제약 조건 정보를 종합적으로 보려면 다음과 같은 SQL 문을 사용할 수 있다.

SELECT x.CONSTRAINT_NAME, x.CONSTRAINT_TYPE, y.COLUMN_NAME

FROM USER_CONSTRAINTS x, USER_CONS_COLUMNS y

WHERE x.CONSTRAINT_NAME = y.CONSTRAINT_NAME and x.TABLE_NAME = 'DEPT'

USER_TAB_CONSTRAINTS, USER_COL_CONSTRAINTS란 뷰는 존재하지 않으며 USER_INDEXES에서는 인덱스 정보를 제공한다.

정답 : (c) (<- 마우스로 정답 부분을 선택하세요)

13-3. 다음과 같은 명령으로 테이블을 생성하였다.

CREATE TABLE emp

(empno NUMBER(4) PRIMARY KEY,

ename VARCHAR2(14) NOT NULL,

sal NUMBER CHECK(SAL > 0),

email VARCHAR2(30) UNIQUE,

deptno NUMBER(2) REFERENCES dept(deptno))

각 제약 조건의 종류는 USER_CONSTRAINTS에서 하나의 알파벳으로 표시된다. 컬럼 순서대로 정확하게 나타낸 것은?

a. P N C U R

b. P C C U R

c. K N C U F

d. P N C U F

e. K C C U R

<해설>

테이블을 생성한 스크립트가 없을 때 테이블에 걸려 있는 제약 조건을 확인하기 곤란한 경우가 있다. 이 때에는 USER_CONSTRAINTS 테이블의 CONSTRAINT_TYPE 컬럼을 참조해서 제약 조건의 종류를 확인할 수 있다. 제약 조건은 다음과 같이 하나의 알파벳으로 표시된다.

PRIMARY KEY P

NOT NULL C

CHECK C

UNIQUE U

FOREIGN KEY R

참고로 뷰의 WITH CHECK OPTION은 V, WITH READ ONLY 는 O로 표시된다.

FOREIGN KEY가 F가 아닌 R로 표시되는 점을 기억하기 바라며 NOT NULL과 CHECK 제약 조건이 모두 C로 표시된다는 것도 특이한 점이다. NOT NULL 제약 조건은 CHECK 제약 조건으로 구현할 수 있기 때문인데 NOT NULL을 CHECK 제약 조건으로 표현하면 다음과 같이 된다.

ename VARCHAR2(14) CHECK (ename IS NOT NULL)

정답 : (b) (<- 마우스로 정답 부분을 선택하세요)

13-4. 자신이 사용할 수 있는 데이터 딕셔너리의 이름과 설명이 들어 있는 딕셔너리 뷰는? (주관식)

<해설>

DICTIONARY 뷰에는 두 개의 컬럼이 있는데 TABLE_NAME 컬럼에는 딕셔너리의 이름이 들어 있고 COMMENTS 컬럼에는 간단한 설명이 나와 있다. 따라서 매뉴얼이 없는 상황에서 딕셔너리의 용도를 확인할 때 요긴하게 사용할 수 있다.

정답 : DICTIONARY (<- 마우스로 정답 부분을 선택하세요)

그 밖의 데이터베이스 객체

14-1. 다음과 같이 시퀀스를 생성하였다.

CREATE SEQUENCE myseq

START WITH 1

MAXVALUE 9999

INCREMENT BY 1

CACHE 30

그리고 아래의 SQL 문을 5회 실행하였는데 갑자기 정전이 되면서 서버가 다운되었다.

INSERT INTO emp

VALUES(myseq.NEXTVAL, SYSDATE, NULL, NULL)

나중에 서버가 기동된 후에 아래의 SQL 문을 실행하면 어떤 값이 출력되겠는가?

SELECT myseq.NEXTVAL

FROM dual

a. 1

b. 5

c. 6

d. 30

e. 31

<해설>

시퀀스는 주로 키 값을 생성할 때 사용되는 객체이다. 응용 프로그램에서 키 값을 만들기 위해서 시퀀스 기능을 구현하는 것보다 훨씬 안정적이고 낮은 부하로 키 값을 제공할 수 있기 때문에 많이 사용된다.

시퀀스를 만들 때 지정할 수 있는 옵션에는 여러 가지가 있지만 대부분 디폴트 값이 설정되기 때문에 다음과 같이 아무런 옵션 없이 시퀀스를 만들 수도 있다.

CREATE SEQUENCE myseq

시퀀스에서 지정할 수 있는 옵션은 다음과 같은 것들이 있다.

MINVALUE : 시퀀스의 최소 값

MAXVALUE : 시퀀스의 최대 값

START WITH : 시퀀스의 시작 값

CACHE : 시퀀스를 빨리 제공하기 위해서 미리 메모리에 CACHE 개 만큼의 시퀀스를 만들어서 준비해 두고 있다. 디폴트는 20개.

ORDER : 시퀀스는 주로 키 값을 만들어 내기 위한 목적으로 사용되기 때문에 실제의 값 보다는 유일성이 가장 중요시된다. 오라클 병렬 서버를 병렬 모드로 사용하는 경우 짧은 시간에 여러 사용자가 시퀀스를 사용하면 요청 순서에 따라 시퀀스 값이 할당되지 않을 수도 있다. 만약 요청 순서에 따라서 정확하게 시퀀스 값을 제공하고 싶다면 ORDER 옵션을 지정해 주어야 한다. 디폴트는 NOORDER 상태이다. 병렬 서버가 아닌 경우에는 옵션과 관계 없이 요청 순서에 따라서 시퀀스가 정확하게 할당된다.

CYCLE : 시퀀스를 사용하다가 MAXVALUE에 도달하면 다시 MINVALUE부터 시작하라는 옵션이다. 디폴트는 NOCYCLE이다.

INCREMENT BY : 시퀀스를 얼마 씩 증가시킬 것인가를 지정한다. 만약 음수로 설정하면 시퀀스 값은 계속해서 감소하게 된다.

오라클에서는 CACHE 개 만큼의 시퀀스를 미리 메모리에 보관하고 있기 때문에 만약 시스템이 비정상적으로 종료된다면 그 내용이 모두 사라지게 된다. 따라서 나중에 시퀀스를 새롭게 사용할 때에는 CACHE에 들어 있던 내용 다음부터 시작하게 된다.

이 문제에서는 1부터 30까지의 시퀀스 값이 캐시에 들어 있는 상황에 시스템이 다운되었으므로 다음의 시퀀스는 31부터 시작된다.

정답 : (e) (<- 마우스로 정답 부분을 선택하세요)

14-2. 시퀀스를 생성한 후에 변경할 수 없는 것은?

a. 초기 값(START WITH)

b. 최대 값(MAXVALUE)

c. 증가분(INCREMENT BY)

d. 캐시 값(CACHE)

e. 최소 값(MINVALUE)

<해설>

시퀀스의 옵션은 대부분 변경할 수 있지만 START WITH로 지정한 초기 값은 바꿀 수 없다. 시퀀스를 만든 다음에 한 번도 사용하지 않은 상태라고 하더라도 초기 값의 변경이 허락되지 않는다.

정답 : (a) (<- 마우스로 정답 부분을 선택하세요)

14-3. (14-1)에서 만든 myseq 시퀀스를 사용하였더니 다음과 같이 출력되었다.

SELECT myseq.CURRVAL

FROM dual

499

이 때 다음 SELECT 문을 실행하여 출력되는 내용으로 옳은 것은?

SELECT myseq.CURRVAL, myseq.NEXTVAL, myseq.NEXTVAL*2

FROM dual

a. 499 500 1002

b. 499 500 1000

c. 500 500 1000

d. 500 501 1002

e. 에러 발생

<해설>

하나의 SQL 문에서 시퀀스를 사용할 때에는 여러 번 사용하여도 항상 동일한 값이 제공된다. 특히 CURRVAL과 NEXTVAL을 함께 사용할 때에는 그 순서와는 관계 없이 CURRVAL도 NEXTVAL의 값을 따라가게 된다.

이 문제에서 만약 CURRVAL을 단독으로 사용했다면 499가 반환되겠지만 NEXTVAL 과 함께 사용했기 때문에 모두 500이 되며 두 번째 사용한 NEXTVAL의 값도 역시 500이 된다.

정답 : (c) (<- 마우스로 정답 부분을 선택하세요)

14-4. 다음 컬럼 중에서 인덱스를 만들기에 가장 적당하지 않은 컬럼은?

a. 성 별

b. 나 이

c. 고객번호

d. 이 름

e. 전화번호

<해설>

오라클에서는 B*TREE 방식의 인덱스를 사용한다. 인덱스를 만드는 컬럼을 선택할 때 가장 중요한 것은 분포도이다. 분포도란 어떤 컬럼의 값이 테이블 전체에 평균적으로 분포되어 있는 정도를 말하는 것으로 예를 들어 성별 컬럼이라면 남/여 두 개의 경우밖에 없으므로 분포도는 50%가 된다.

인덱스는 분포도가 낮은 컬럼에 만드는 것이 원칙인데 손익 분기점의 기준은 대략 10~15%가 통용된다. 즉, 분포도가 10~15% 보다 큰 컬럼이라면 인덱스를 쓰지 않고 테이블 스캔을 하는 편이 낫다는 의미이다.

위의 보기에서는 성별을 제외한 다른 컬럼들 분포도가 낮은 컬럼들이기 때문에 인덱스를 만들기 위한 좋은 후보가 될 수 있다. 그러나 성별 컬럼은 분포도가 워낙 높기 때문에 인덱스를 만들면 오히려 역효과가 발생한다.

이와 같이 분포도가 높은 컬럼에는 비트맵 인덱스를 만들면 효과적인데 비트맵 인덱스는 다른 과목에서 다루기로 하겠다.

정답 : (a) (<- 마우스로 정답 부분을 선택하세요)

14-5. 다음 SQL 문을 실행하면 몇 개의 인덱스가 생성되는가?

CREATE TABLE emp

(empno NUMBER(4),

ename VARCHAR2(20),

phone VARCHAR2(18) UNIQUE,

sal NUMBER NOT NULL,

comm NUMBER NOT NULL,

depot NUMBER(2) REFERENCES dept(deptno),

CONSTRAINT pk_emp PRIMARY KEY(empno, ename))

a. 0

b. 1

c. 2

d. 3

e. 4

<해설>

이전에도 설명한 적이 있지만 중요한 내용이므로 다시 한번 다루었다. 인덱스를 별도로 만들지 않더라도 PRIMARY KEY와 UNIQUE 제약 조건을 만들면 오라클은 자동으로 인덱스를 생성한다. NOT NULL, CHECK, FOREIGN KEY 제약 조건은 인덱스를 수반하지 않는다. 특히 FOREIGN KEY가 만들어지는 테이블에 인덱스가 생성되는 것이 아니라 인덱스는 FOREIGN KEY가 참조하는 테이블의 컬럼에 걸려 있음을 잊지 말기 바란다. 그리고 여러 개의 컬럼을 결합하여 PRIMARY KEY를 만들어도 인덱스는 1개만 생성된다.

정답 : (c) (<- 마우스로 정답 부분을 선택하세요)

사용자 관리와 권한

15-1. 다음과 같이 일련의 SQL 문을 실행했을 때 에러가 발생하는 곳은?

a. CONNECT system/manager

b. CREATE USER scott IDENTIFIED BY tiger

c. GRANT CONNECT TO scott

d. GRANT RESOURCE TO scott

e. CONNECT scott/tiger

f. GRANT CONNECT TO larry

<해설>

롤(roles)은 일종의 권한(privileges) 저장소라고 할 수 있다. 각 사용자에게 일일이 권한을 부여하면 관리하기가 매우 복잡하다는 단점이 있다. 그래서 필요한 기능 별로 롤을 생성한 다음에 이 롤에 적절한 권한을 할당해 주고 사용자에게는 롤을 부여함으로써 보다 직관적으로 권한을 관리할 수 있다. 마치 Windows NT/2000에서의 그룹 개념과 비슷하다고 보면 된다.

문제에서는 scott가 system 으로부터 CONNECT와 RESOURCE 롤을 부여 받았다. 그러나 단순히 사용 권한을 받았을 뿐이고 이 권한을 다른 유저에게 주거나 또는 빼앗을 수 있는 관리 권한은 갖고 있지 않은 상태이다.

scott가 CONNECT, RESOURCE 롤의 관리까지 할 수 있으려면

GRANT CONNECT TO scott WITH ADMIN OPTION

과 같이 권한을 주어야 한다.

자신이 사용할 수 있는 롤을 확인하려면 USER_ROLE_PRIVS를 보면 알 수 있다.

SELECT * FROM USER_ROLE_PRIVS

명령을 실행하면 아래와 같은 결과가 나타난다.

USERNAME     GRANTED_ROLE      ADM   DEF     OS_ 
SCOTT            CONNECT              YES    YES     NO 
SCOTT            RESOURCE             NO     YES     NO

출력된 내용을 보면 현재 scott는 CONNECT, RESOURCE 두 개의 롤을 사용할 수 있으며 특히 CONNECT 롤은 WITH GRANT OPTION을 통해서 관리 권한을 부여 받았음을 알 수 있다(ADM 컬럼의 값이 YES로 되어 있음).

참고로 CONNECT, RESOURCE, DBA 롤은 향후에는 지원되지 않을 예정이므로 가급적 이 롤에 의존하지 않는 것이 바람직하다.

정답 : (f) (<- 마우스로 정답 부분을 선택하세요)

15-2. 보기 중에서 시스템 권한에 해당되는 것은?

a. EXECUTE

b. CREATE TABLE

c. REFERENCES

d. INDEX

e. READ

<해설>

권한은 크게 시스템 권한과 객체 권한으로 나눌 수 있다. 시스템 권한은 시스템 전반에 관한 작업 또는 어떠한 유형의 객체에 대한 작업을 하기 위한 권한을 의미하고 객체 권한은 구체적인 객체(테이블, 뷰 등)에 대한 작업(조회, 수정, 삭제 등)을 하기 위한 권한을 말한다.

시스템 권한과 객체 권한은 그 의미를 생각해 보면 큰 어려움 없이 구분할 수 있지만 막상 시험에서는 정확히 고르기가 쉽지 않다. 시스템 권한은 종류가 매우 많기 때문에 외우기란 불가능하지만 객체 권한은 단 9개 밖에 되지 않으므로 그 종류를 모두 암기해 두는 것이 좋다. 객체 권한의 종류는 다음과 같다.

ALTER DELETE EXECUTE INDEX INSERT READ REFERENCES SELECT UPDATE

이 중에서 ALTER, EXECUTE, READ, REFERENCES 는 약간 생소할 수 있으므로 반드시 기억해 두어야 한다.

ALTER는 ALTER TABLE, ALTER SEQUENCE 명령처럼 테이블이나 시퀀스의 정의를 변경할 수 있는 권한이다. 예를 들면 GRANT ALTER ON emp TO larry 와 같이 부여하면 된다. 참고로 GRANT ALTER ANY TABLE TO scott 명령에서 사용한 ALTER ANY TABLE은 시스템 권한이므로 착오 없기 바란다.

REFERENCES는 어떠한 테이블(컬럼)을 참조하는 FOREIGN KEY를 만들 수 있는 권한이다. REFERENCES와 INDEX 권한은 롤에는 부여할 수 없다는 것도 특이한 점이다.

EXECUTE는 스토어드 프로시저나 함수를 실행할 수 있는 권한이다.

READ는 디렉토리 객체가 가리키는 운영 체제의 디렉토리에서 파일을 읽을 수 있는 권한이다.

자신이 부여 받은 권한을 다른 유저에게 주기 위해서는 관리 권한을 함께 받아야 한다. 시스템 객체에서 관리 권한까지 함께 부여할 때에는 WITH ADMIN OPTION 이라고 지정하고 객체 권한에서는 WITH GRANT OPTION이라고 주면 된다. 예를 들면 다음과 같다.

GRANT CREATE SESSION TO scott WITH ADMIN OPTION

GRANT SELECT, UPDATE, DELETE ON dept TO larry WITH GRANT OPTION

정답 : (b) (<- 마우스로 정답 부분을 선택하세요)

15-3. 사용자가 자신의 패스워드를 변경하기 위해서 필요한 권한은?

a. CREATE ANY USER

b. CHANGE PASSWORD

c. ALTER PASSWORD

d. ALTER ANY USER

e. 필요 없음

<해설>

자신의 패스워드를 바꾸는 데에는 아무런 권한도 필요로 하지 않는다. 어떤 사용자든지 보유한 권한과는 상관 없이 자신의 패스워드를 변경할 수 있다.

정답 : (e) (<- 마우스로 정답 부분을 선택하세요)

15-4. scott 유저가 다음과 같이 larry에게 dept 테이블을 참조할 수 있는 권한을 부여하였다.

GRANT REFERENCES ON dept TO larry

그 후에 larry는 scott dept 테이블을 참조하는 테이블을 만들었다.

CREATE TABLE emp

(empno NUMBER(4) PRIMARY KEY,

ename VARCHAR2(14),

deptno NUMBER(2) REFERENCES scott.dept(deptno),

sal NUMBER,

comm NUMBER)

얼마 후에 scott는 larry가 자신의 dept 테이블을 참조할 수 없도록 하고 싶어졌다면 어떤 명령을 실행해야 하는가?

a. REVOKE REFERENCES ON dept FROM larry

b. REVOKE REFERENCES ON dept FROM larry WITH GRANT OPTION

c. REVOKE REFERENCES ON dept FROM larry CASCADE CONSTRAINTS

d. REVOKE REFERENCES ON dept FROM larry WITH GRANT OPTION CASCADE CONSTRAINTS

<해설>

larry가 dept 테이블을 참조할 수 없도록 하려면 dept 테이블에 대한 REFERENCES 권한을 취소하면 된다. 만약 아직 larry가 dept 테이블을 참조하는 FOREIGN KEY를 만들지 않았다면 (a)와 (c) 모두 답이 될 수 있다. 그러나 이미 emp 테이블을 만들면서 dept 테이블에 대한 FOREIGN KEY를 설정했기 때문에 (a)와 같이 실행하면 에러가 발생한다. 따라서 참조 권한을 뺏음과 동시에 larry 유저의 dept 테이블에 걸려 있는 FOREIGN KEY 까지 모두 삭제하려면 (c)처럼 실행해야 한다. 이 때 FOREIGN KEY가 삭제되더라도 emp 테이블에 들어 있던 데이터는 삭제되지 않는다.

정답 : (c) (<- 마우스로 정답 부분을 선택하세요)

PL/SQL 개요

16-1. PL/SQL의 장점에 대한 설명으로 옳은 것은? (정답은 세가지)

a. 업계 표준이다

b. 포팅이 용이하다

c. 성능을 향상시킬 수 있다.

d. 생산성이 높다

e. 써드 파티 도구에서도 지원한다

<해설>

SQL은 강력한 능력을 가지고 있지만 개별적인 SQL 명령 만으로는 처리하기 어려운 사안들이 많기 때문에 일련의 SQL 명령을 모아서 스크립트를 만들어 사용하기도 하는데 오라클에서는 PL/SQL이라고 하는 자체 언어를 통해서 SQL의 능력을 확장하였다.

PL/SQL이란 SQL에 절차형 언어의 특성을 부여한 것으로 기본적으로는 SQL을 사용하면서 변수, 조건문, 반복문, 제어문, 에러 처리 등의 기능이 추가된 것이다. 따라서 단순한 SQL 스크립트로는 처리할 수 없는 기능도 쉽게 구현할 수 있다.

PL/SQL은 오라클 제품에서는 대부분 지원하기 때문에 서버를 다른 운영 체제로 변경하여도 코드 수정 없이 그대로 사용할 수 있으며 스토어드 프로시저나 패키지는 써드 파티 도구에서도 지원하므로 클라이언트 도구가 변경되어도 걱정할 필요가 없다. 단, 써드 파티 도구에는 PL/SQL 엔진이 내장되어 있지 않기 때문에 PL/SQL 블록을 자체적으로 처리하지는 못한다.

또한 다량의 SQL 명령이 한 번에 PL/SQL 엔진으로 전달되므로 서버에 작업을 요청하는 회수가 감소하여 전체적인 처리 속도가 빨라진다.

그러나 업계 표준은 아니기 때문에 오라클 제품에서만 지원하므로 써드 파트 도구에서는 스토어드 프로시저나 패키지의 형태로 사용하는 정도에 만족해야 한다.

정답 : (b) (c) (d) (<- 마우스로 정답 부분을 선택하세요)

16-2. 이름이 없는 PL/SQL 블록(anonymous block)의 구조는 다음과 같다.

( )

선언부(declarative section)

( )

실행부(executable section)

( )

예외처리부(exception handling section)

( )

괄호 안에 들어가는 키워드가 올바르게 나열된 것은?

a. 없음 BEGIN EXCEPTION END

b. DECLARE BEGIN EXCEPTION END

c. DECLARE BEGIN END EXCEPTION

d. VAR BEGIN EXCEPTION END

e. DECLARE BEGIN ERROR END

<해설>

PL/SQL 블록에는 이름이 있는 것(named block)과 없는 것(anonymous block)이 있다. 이름이 있는 것은 스토어드 프로시저나 함수, 패키지와 같이 데이터베이스에 객체로서 저장되어 있는 것을 말하며 이름이 없는 것은 데이터베이스에 저장되지 않고 주로 스크립트 형태로 보관되어 실행되는 PL/SQL 블록을 뜻한다.

이름이 없는 PL/SQL 블록은 선언부, 실행부, 예외처리부의 섹션으로 이루어져 있다. 선언부에서는 프로그램에서 사용할 각종 변수와 상수 등을 정의하고, 실행부에서는 실제로 각종 명령을 사용해서 작업 내용을 기술하며 예외처리부에는 에러가 발생했을 때 어떻게 처리할 것인가를 지정하는 에러 처리 루틴이 들어간다. 예외처리부가 끝난 다음에 마지막으로 END 키워드가 나옴을 주의해야 한다.

정답 : (b) (<- 마우스로 정답 부분을 선택하세요)

16-3. 이름이 없는 PL/SQL 블록의 섹션을 구성하는 키워드 중에서 반드시 필요한 것은? (정답은 두 가지)

a. DECLARE

b. BEGIN

c. EXCEPTION

d. END

<해설>

이름이 없는 PL/SQL 블록을 구성하는 키워드는 (16-2)에서 본 바와 같이 4개로 이루어져 있지만 이들 모두가 꼭 요구되지는 않는다. 변수나 상수를 사용하지 않는다면 DECALRE 절과 선언부를 사용할 필요가 없고 에러 처리 루틴을 별도로 만들지 않겠다면 EXCEPTION 절과 예외처리부도 필요 없다. 그러나 BEGIN과 END는 PL/SQL 블록을 완성하는데 반드시 필요한 요소이다.

정답 : (b) (d) (<- 마우스로 정답 부분을 선택하세요)

16-4. PL/SQL에서 변수를 선언할 때 잘못된 것은? (정답은 세 가지)

a. hiredate, expire DATE;

b. deptno NUMBER(2) := 10;

c. ename CHAR(10) NOT NULL;

d. pi CONSTANT REAL;

e. male BOOLEAN DEFAULT FALSE;

<해설>

PL/SQL에서는 오라클 데이터베이스의 데이터타입과 호환되는 다양한 변수를 제공할 뿐 아니라 몇 가지 추가된 것도 있다.

변수를 선언할 때 NOT NULL을 지정하여 변수에 NULL 값이 들어갈 수 없도록 할 수 있는데 이 때에는 반드시 초기값을 할당해야 한다. 즉,

ename CHAR(10) NOT NULL := ‘SMITH’;

와 같이 변경해야 한다. 초기값을 지정할 때에는 DEFAULT 키워드를 사용해도 된다.

대부분의 프로그래밍 언어와는 달리 한 번에 여러 개의 변수를 선언하지 못한다는 것은 약간 불편한 점이다. 즉, 변수는 한 번에 하나씩만 선언해야 하므로 (a)와 같이 사용하면 에러가 발생하며 다음처럼 나누어서 기술해야 한다.

hiredate DATE;

expire DATE;

상수를 선언할 때에는 타입과 함께 반드시 값을 지정해 주어야 한다. (d)를 올바르게 고치면 아래와 같다.

pi CONSTANT REAL := 3.141592;

정답 : (a) (c) (d) (<- 마우스로 정답 부분을 선택하세요)

PL/SQL - 실행부

17-1. SQL*Plus에서 myproc라는 스토어드 프로시저를 실행하는 방법으로 옳지 않은 것은? 단, myproc의 파라미터는 없다. (정답은 두 가지)

a. SQL> EXEC myproc;

b. SQL> EXECUTE myproc();

c. SQL> EXECUTE BEGIN myproc(null); END;

d. SQL> EXECUTE BEGIN myproc; END;

e. SQL> myproc();

f. SQL> BEGIN myproc; END;

<해설>

스토어드 프로시저란 CREATE PROCEDURE 명령을 이용해서 데이터베이스에 생성한 프로시저로 실행 권한을 가진 사용자라면 누구나 불러서 사용할 수 있다. 스토어드 프로시저는 이름이 있는 PL/SQL 블록의 하나인데 SQL*Plus에서 스토어드 프로시저를 실행할 때에는 EXECUTE 또는 EXEC 명령을 사용한다. 그리고 스토어드 프로시저와 주고 받는 파라미터가 없는 경우에는 () 표시를 하지 않아도 상관없다.

(c)에서 처럼 파라미터가 없다는 점을 강조하기 위해서 null이라고 지정하면 에러가 발생0한다.

스토어드 프로시저는 PL/SQL 패밀리이므로 PL/SQL 블록 안에서는 바로 사용할 수 있기 때문에 (f)도 옳은 사용방법에 해당된다.

정답 : (c) (e) (<- 마우스로 정답 부분을 선택하세요)

17-2. 다음과 같이 원의 면적을 계산하는 함수를 생성하였다.

CREATE OR REPLACE FUNCTION area(r IN NUMBER)

RETURN NUMBER IS

BEGIN

RETURN 3.14*r*r;

END;

이 함수를 SQL*Plus에서 사용하는 방법으로 옳은 것은?

a. SQL> EXEC :result := area(3);

b. SQL> :result := SELECT area(3) FROM dual;

c. SQL> :result := area(3);

d. SQL> SELECT area(3) INTO :result FROM dual;

<해설>

내장 함수는 하나의 결과 값을 돌려준다는 특징이 있기 때문에 SQL*Plus에서 결과를 받기 위해서 바인드 변수를 사용해야 한다. 이전에도 설명했듯이 바인드 변수는 앞에 콜론(:)을 붙여서 구별하도록 되어 있다. area(3) 값을 가져오는 과정을 알아보자.

먼저 다음과 같이 바인드 변수를 선언한다.

SQL>VARIABLE result NUMBER;

그 다음에 함수를 실행해서 결과를 받아와야 하는데 (a)와 같이 하면 된다. 그리고 결과를 확인하려면

SQL> PRINT :result;

라고 실행하면 된다.

(b)는 전혀 성립될 수 없는 문장이며 (c), (d)는 PL/SQL 블록 안에서만 사용되는 구문이다.

정답 : (a) (<- 마우스로 정답 부분을 선택하세요)

17-3. 서브 프로그램의 파라미터에 관한 설명으로 옳은 것은?

a. 파라미터 모드에는 IN, OUT, IN OUT의 세 가지가 있다.

b. 디폴트 파라미터 모드는 IN OUT이다.

c. Actual 파라미터와 Formal 파라미터의 이름은 동일해야 한다.

d. IN 모드로 전달된 파라미터의 값을 서브프로그램에서 변경할 수 있다.

<해설>

메인 프로그램은 서브 프로그램과 데이터를 교환하기 위해서 파라미터를 사용한다. 파라미터에는 IN, OUT, IN OUT의 세 가지 모드가 있으며 아무것도 지정하지 않으면 IN 모드로 인식된다. IN 모드는 메인 프로그램으로부터 데이터를 받을 때 사용되고 OUT 모드는 서브프로그램에서 메인 프로그램으로 데이터를 넘겨 줄 때 사용된다. IN OUT 모드는 두 가지 용도로 모두 쓰일 수 있다.

IN 모드로 전달된 파라미터는 서브프로그램에서는 마치 상수처럼 취급되기 때문에 그 값을 읽을 수만 있고 변경할 수는 없다.

Actual 파라미터란 메인 프로그램에서 서브 프로그램을 호출할 때 사용되는 파라미터이다. 예를 들어

SQL> EXEC myproc(‘hello’);

에서 Actual 파라미터는 ‘hello’ 가 된다.

Formal 파라미터는 서브 프로그램을 정의할 때 사용한 파라미터로 (17-2) 의 area 함수를 예로 들자면 r 이 Formal 파라미터가 된다. Actual 파라미터에는 상수, 변수, 수식 등을 사용할 수 있기 때문에 Formal 파라미터와 이름이 같지 않을 수도 있으며 일반적으로 서로 다른 이름을 사용하는 것이 바람직하다.

정답 : (a) (<- 마우스로 정답 부분을 선택하세요)

PL/SQL - 제어문

18-1. PL/SQL 에서 지원하는 반복문의 종류가 아닌 것은?

a. LOOP(basic loop)

b. FOR-LOOP

c. WHILE-LOOP

d. DO-WHILE-LOOP

<해설>

PL/SQL은 세 가지 종류의 반복문을 제공하는데 각각의 구조는 다음과 같다.

1) LOOP(basic loop)

         LOOP
                  명령문;
         END LOOP;  


2) WHILE-LOOP

         WHILE 조건식 LOOP
                  명령문;
         END LOOP;  


3) FOR-LOOP

         FOR 카운터 IN 하한값..상한값 LOOP
                  명령문;
         END LOOP;

LOOP(basic loop)는 무한 루프라고도 부르는데 루프에서 빠져나가는 조건을 지정하지 않으면 영원히 루프 안에서 맴돌게 된다. 루프에서 빠져나갈 때에는 EXIT 명령을 사용할 수 있는데

         IF 조건식 THEN
                  EXIT;
         END IF;

와 같이 IF 문을 사용할 수도 있고

EXIT WHEN 조건식;

처럼 쓸 수도 있다. EXIT WHEN 방식이 간편하기 때문에 보다 많이 사용된다.

WHILE-LOOP는 조건식이 참(TRUE)인 동안 계속 루프를 돌고 FOR-LOOP는 카운터의 값이 하한값부터 시작해서 상한값에 도달할 때 까지 루프를 도는데 REVERSE 옵션을 사용하면 큰 값부터 작은 값으로 카운터를 역으로 진행시킬 수도 있다.

DO-WHILE-LOOP는 PL/SQL에서는 지원되지 않는다.

정답 : (d) (<- 마우스로 정답 부분을 선택하세요)

18-2. 아래의 루프는 무한 루프를 돌도록 되어 있다. 루프에서 빠져 나오는 조건을 기술한 것 중에서 옳지 않은 것은?

         DECLARE
                  cnt NUMBER := 0;
         BEGIN
         LOOP
                           cnt := cnt+1;
         END LOOP;
                  BEGIN
                           <>
                           DELETE bonus WHERE rownum <= cnt;
                  END;
         END;  

a. EXIT WHEN cnt > 10;
b. IF cnt > 10 THEN 
         EXIT;
   END IF;
c. IF cnt > 10 THEN
         GOTO next;
   END IF;
d. EXIT;

<해설>

LOOP에서 빠져나갈 때에는 EXIT 명령을 사용한다. (a), (b)가 가장 일반적으로 사용되는 형태이고 (d)처럼 그냥 EXIT를 실행하면 무조건적으로 루프에서 빠져 나온다.

그다지 권장되지는 않지만 GOTO 문을 사용하면 프로그램을 원하는 곳으로 분기시킬 수 있으므로 루프에서 빠져 나올 수 있다. 그러나 GOTO 문은 자기가 속한 블록의 서브블록으로는 분기할 수 없다는 제약이 있기 때문에 (c)를 실행하면 에러가 발생한다.

정답 : (c) (<- 마우스로 정답 부분을 선택하세요)

PL/SQL - 복합 데이터타입 다루기

19-1. 아래와 같이 선언된 변수를 옳게 사용한 것은? (정답은 두 가지)

이름                    널?                     유형 
DEPTNO             NOT NULL           NUMBER(2) 
DNAME                                         VARCHAR2(14) 
LOC                                             VARCHAR2(13)

<변수 선언>

dept_row dept%ROWTYPE;

v_deptno dept.deptno%TYPE;

a. v_deptno := NULL;

b. dept_row := (20, ‘SALES’, ‘NEW YORK’);

c. INSERT INTO dept VALUES(dept_row);

d. SELECT deptno, dname, loc INTO dept_row FROM dept WHERE deptno=20;

<해설>

%TYPE은 다른 변수나 컬럼과 동일한 타입으로 변수를 선언할 때 사용하는 방법이다. v_deptno를 dept.deptno%TYPE으로 선언하면 dept 테이블의 deptno 컬럼과 같은 데이터 타입, 즉 NUMBER(2)가 된다. 이렇게 하면 deptno 컬럼이 어떤 타입인지를 몰라도 되고 deptno 컬럼의 타입이 변경되더라도 프로그램을 수정할 필요성이 줄어들기 때문에 많이 이용된다.

%ROWTYPE은 테이블의 레코드와 같은 구조의 변수를 선언하는 방법으로 dept_row는 dept 테이블의 레코드와 같은 구조체 형태로 만들어진다. 실제로 사용할 때에는 구조체 전체를 가지고 작업할 수도 있고(항상 가능한 것은 아님) dept_row.deptno와 같이 각 구성 요소 별로 작업할 수도 있다.

(a)에서는 v_deptno 변수에 NULL 값을 넣을 수 있는지를 물어보고 있다. v_deptno는 dept 테이블의 deptno 컬럼과 같은 타입으로 선언되었을 뿐 deptno 컬럼에 걸려 있는 NOT NULL 제약조건까지 그대로 따라가는 것은 아니기 때문에 얼마든지 NULL 값을 할당할 수 있다.

%ROWTYPE으로 선언된 변수에 값을 할당할 때에는 (b)와 같은 방법은 지원되지 않으며 (c)처럼 한번에 INSERT 할 수도 없다. 그러나 (d)와 같이 SELECT INTO를 이용하여 한번에 데이터를 받아오는 것은 허용된다.

정답 : (a) (d) (<- 마우스로 정답 부분을 선택하세요)

19-2. 다음과 같이 TimeRec이라는 RECORD 타입을 선언하였다.

TYPE TimeRec IS RECORD

(day SMALLINT,

month SMALLINT,

year SMALLINT);

이 타입 선언을 수정하여 TimeRec RECORD를 초기화하도록 만든 것으로 옳은 것은?

a. TYPE TimeRec IS RECORD

(day SMALLINT, month SMALLINT, year SMALLINT ) := (0, 0, 0);

b. TYPE TimeRec IS RECORD

(day SMALLINT, month SMALLINT, year SMALLINT ) := 0;

c. TYPE TimeRec IS RECORD

(day SMALLINT, month SMALLINT, year SMALLINT ) := 0, 0, 0;

d. TYPE TimeRec IS RECORD

(day SMALLINT := 0, month SMALLINT := 0, year SMALLINT := 0);

<해설>

PL/SQL은 RECORD와 PL/SQL 테이블이라는 두 가지 복합 데이터타입을 제공한다. Oracle8부터는 중첩 테이블(nested table)과 VARRAY가 등장하면서 PL/SQL 테이블이란 용어 대신 index-by 테이블로 구별해서 부르고 있지만 본 과목의 출제 경향에 따라 PL/SQL 테이블과 RECORD 만을 설명하고자 한다.

RECORD는 %ROWTYPE과 같이 구조체를 떠올리면 되는데 이들은 서로 비슷한 특성을 가지고 있지만 %ROWTYPE은 테이블이나 커서의 구조를 그대로 사용하는 반면 RECORD 타입은 구성 요소를 사용자가 직접 정의할 수 있다는 차이점이 있다. 그래서 사용자 정의 RECORD라고 부른다.

RECORD 타입을 선언하면서 초기값을 설정할 때에는 (d)와 같이 각 구성 요소 별로 초기값을 주어야 한다.

정답 : (d) (<- 마우스로 정답 부분을 선택하세요)

19-3. 다음과 같이 선언된 PL/SQL 테이블을 사용하는 방법으로 옳은 것은?

TYPE EmpnoTabType IS TABLE OF NUMBER(4) NOT NULL INDEX BY BINARY_INTEGER;

empnotab EmpnoTabType;

a. empnotab(-3) := 7862;

b. SELECT empno INTO empnotab FROM emp;

c. INSERT INTO emp(empno) VALUES (empnotab);

d. Empnotab(-3) := NULL;

<해설>

PL/SQL 테이블은 PL/SQL 개발자에게 단비와 같은 존재로 배열이 필요할 때 매우 요긴하게 사용되는 기능이다. PL/SQL 테이블은 이름은 테이블이지만 실제로 데이터베이스에 저장될 수는 없고 메모리 상에 존재하면서 키를 이용해서 액세스 된다.

위에서는 EmpnoTabType 이란 PL/SQL 테이블 타입을 선언하였고 배열의 요소는 NUMBER(4) 타입이며 NULL 값은 입력할 수 없도록 하였다.

PL/SQL 테이블의 키는 BINARY_INTEGER 타입으로 되어 있는데 그 범위가 –2^31-1 ~ +2^31-1 이므로 (a)처럼 키 값으로 음수를 사용할 수도 있다. BINARY_INTEGER 타입은 NUMBER 타입보다 처리 속도가 빠른 특성을 가지고 있다.

어레이 프로세싱이란 어레이(배열)에 들어있는 데이터를 하나 씩 처리하지 않고 한 번에 오라클에 전달하여 처리함으로써 퍼포먼스를 높이는 방법이다. 상황에 따라 다르겠지만 보통 수 배 이상의 성능 향상을 얻을 수 있는데 과거에는 PL/SQL 테이블의 어레이 프로세싱이 지원되지 않았지만 현재는 FOR ALL, BULK COLLECT와 같은 새로운 구문을 사용하여 구현할 수 있게 됨으로써 PL/SQL의 성능이 비약적으로 발전하게 되었다. 구체적인 방법은 본 시험의 범위를 벗어나므로 생략하기로 하고 여기서는 (b), (c)와 같은 고전적인 문장은 에러를 유발함을 알아두기로 하자.

EmpnoTabType 선언 시에 NOT NULL로 지정했기 때문에 (d)와 같이 NULL 값을 할당할 수도 없다.

정답 : (a) (<- 마우스로 정답 부분을 선택하세요)

PL/SQL 커서 이용법

20-1. 커서를 구성하는 SQL 문은 커서 처리의 어떤 단계에서 실행되는가?

a. DECLARE

b. OPEN

c. FETCH

d. CLOSE

<해설>

오라클은 SQL 문을 처리할 때 커서를 생성하여 작업 한다. 사용자가 직접 커서를 선언한 경우를 외부 커서(explicit cursor)라고 하고 그 외의 경우를 내부 커서(implicit cursor)라고 부른다.

커서는 DECLARE-OPEN-FETCH-CLOSE의 네 단계에 걸쳐서 실행되는데 외부 커서를 사용할 때에는 각 단계를 사용자가 직접 코딩해야 하지만 내부 커서는 오라클에 의해서 자동으로 처리된다.

<커서 처리 과정>

DECLARE - 커서를 구성하는 SQL문을 선언한다.

OPEN - 커서를 구성하는 SQL문을 파싱하고, 파라미터에 대한 바인딩을 실시한 다음에 실행한다.

FETCH - OPEN된 커서로부터 데이터를 가져와서 변수에 할당한다.

CLOSE - 커서의 사용을 마친다.

실제로 커서를 구성하는 SQL 문이 실행되는 시점은 OPEN 단계이며 FETCH 단계에서는 이미 OPEN시에 결정된 액티브 셋(active set)으로부터 단순히 데이터를 가져오는 것임을 기억해 두어야 한다.

정답 : (b) (<- 마우스로 정답 부분을 선택하세요)

20-2. 다음 PL/SQL 블록을 실행했을 때 화면에 출력되는 내용은?

BEGIN
        UPDATE emp
        SET sal = sal*2
        WHERE sal > 3200;

        IF SQL%ISOPEN=TRUE
                THEN dbms_output.put_line(‘OPEN’);
        ELSE dbms_output.put_line(‘CLOSED’);
        END IF;
END;

a. OPEN

b. CLOSED

<해설>

커서는 상태를 알려주는 속성을 몇 가지 가지고 있는데 내부 커서의 속성을 표현할 때에는 ‘SQL%’라는 접두어를 사용하고 외부 커서의 경우에는 ‘커서이름%’ 접두어를 사용한다. 커서의 속성에는 다음과 같은 것들이 있다.

%ISOPEN 커서가 OPEN된 상태이면 TRUE

%ROWCOUNT 커서에 의해서 처리된 레코드 수

%NOTFOUND 커서에 의해서 처리된 레코드가 0일 때 TRUE

%FOUND %NOTFOUND의 반대

내부 커서를 사용할 때에는 사용자가 직접 커서를 조작할 방법이 없으며 SQL 문을 실행하면 자동으로 커서가 생성되고 실행된 다음 닫히기 때문에 사용자 입장에서 볼 때에는 SQL%ISOPEN 값은 항상 FALSE 값을 갖게 된다.

참고로 dbms_putput 패키지는 PL/SQL에서 화면 출력 기능을 제공하는 것으로 dbms_output.put_line(‘문자열’)과 같이 실행하면 문자열이 화면에 출력되고 다음 라인으로 넘어간다. 단, 모든 환경에서 사용할 수 있는 것은 아니며 SQL*Plus에서는 화면 출력 내용을 보기 위해서 먼저 SET SERVEROUTPUT ON 명령을 실행해 주어야 한다.

정답 : (b) (<- 마우스로 정답 부분을 선택하세요)

20-3. 다음 코드에서 에러가 발생하는 위치는?

DECLARE
        CURSOR c1 IS SELECT ename, job, sal, comm FROM bonus WHERE sal > 3000;
BEGIN
        IF c1rec%ISOPEN = FALSE THEN
                FOR c1rec IN c1 LOOP
                        INSERT INTO temp VALUES(c1rec.ename, c1rec.job, c1rec.sal, c1rec.comm);
                END LOOP;
        END IF;
END;

a. CURSOR c1 선언문

b. IF 문

c. FOR 문

d. INSERT 문

<해설>

커서 FOR-LOOP를 사용하면 커서의 OPEN, FETCH, CLOSE 작업이 자동으로 처리되기 때문에 매우 편리하다. 그런데 c1rec는 커서 FOR-LOOP 내에서만 사용하기 위해서 만들어지는 RECORD의 일종으로 커서 FOR-LOOP 안에서만 유효하며 외부에서는 이 변수를 인식할 수 없다.

위와 같이 커서 FOR-LOOP를 작성하면 루프를 돌 때 마다 다음 데이터를 가져오며 마지막 데이터까지 가져온 다음에 루프를 빠져나간다.

정답 : (b) (<- 마우스로 정답 부분을 선택하세요)

20-4. dept 테이블에 다음과 같은 데이터가 들어 있다.

DEPTNO           DNAME              LOC 
10                    ACCOUNTING     NEW YORK 
20                    RESEARCH         DALLAS 
30                    SALES                CHICAGO 
40                    OPERATIONS      BOSTON

아래의 코드를 실행했을 때 UPDATE 명령에 의해서 변경되는 레코드의 deptno는?

DECLARE
       CURSOR c1 IS SELECT dname FROM dept ORDER BY dname FOR UPDATE;
       v_dname VARCHAR(20);
BEGIN
       OPEN c1;
       FETCH c1 INTO v_dname;
       FETCH c1 INTO v_dname;
       FETCH c1 INTO v_dname;
       UPDATE dept SET LOC = ‘LA’ WHERE CURRENT OF c1;
       CLOSE c1;
END;

a. 10

b. 20

c. 30

d. 40

<해설>

커서를 선언할 때 SELECT 문 뒤에 FOR UPDATE 옵션을 사용하면 SELECT 된 레코드를 수정하거나 삭제할 것이라는 뜻으로 오라클은 조회할 레코드들에 락(lock)을 걸게 된다. 그 다음에 UPDATE 문에서 ‘WHERE CURRENT OF 커서명’ 과 같이 기술하면 현재 커서가 가리키고 있는 레코드를 대상으로 작업을 할 수 있다. FOR UPDATE 옵션을 사용하지 않고 WHERE CURRENT OF 절을 쓰면 에러가 발생한다.

c1 커서가 dname을 기준으로 오름차순으로 선언되었으므로 dname이 출력되는 순서는 ACCOUNTING-OPERATIONS-RESEARCH-SALES와 같다. FETCH를 세 번 한 다음에는 커서가 RESEARCH 레코드를 가리키고 있으며 이 레코드의 LOC 컬럼의 값이 LA로 변경된다. 따라서 이 때의 deptno는 20임을 알 수 있다.

정답 : (b) (<- 마우스로 정답 부분을 선택하세요)

PL/SQL - 예외처리

21-1. 다음 PL/SQL 블록을 실행했을 때 발생할 것으로 예상되는 에러는?

DEPTNO           DNAME              LOC 
10                    ACCOUNTING     NEW YORK 
20                    RESEARCH         DALLAS 
30                    SALES                CHICAGO 
40                    OPERATIONS      BOSTON 

DEPTNO
DECLARE
        v_deptno NUMBER(2);
BEGIN
        SELECT deptno INTO v_deptno FROM dept;
END;

a. NO_DATA_FOUND

b. INVALID_CURSOR

c. STORAGE_ERROR

d. TOO_MANY_ROWS

<해설>

PL/SQL에서는 에러 처리를 담당하는 예외 처리부(exception part)를 구성할 수 있다. 절대적으로 필요한 것은 아니지만 이 곳에서 예외 처리 루틴(exception handler)을 만들어서 사용하면 코드의 판독성이 높아지고 한 곳에서 에러를 관리하므로 결과적으로 프로그램의 길이도 짧게 만들 수 있다.

PL/SQL에서 에러가 발생하면 프로그램 수행은 예외 처리부로 넘어간다. 예외 처리부에서는 발생한 에러에 따라서 적절한 조치를 취해주어야 하는데 만약 해당 에러에 대한 예외 처리 루틴이 존재하지 않을 경우에는 에러가 호스트 환경으로 전달된다. 자주 발생하는 에러에 대해서는 STANDARD라는 이름의 패키지에 미리 이름이 정의되어 있으며 필요에 따라 사용자가 직접 만들 수도 있다.

본 문제의 코드에서는 예외 처리부가 생략되어 있는데 보기에 나와있는 에러의 의미는 다음과 같다.

NO_DATA_FOUND는 SELECT INTO 명령에서 가져오는 데이터가 없는 경우에 발생한다.

INVALID_CURSOR는 OPEN 하지도 않은 커서를 CLOSE 하는 등의 잘못된 커서 작업에서 발생한다.

STORAGE_ERROR는 메모리가 부족하거나 손상되었을 때 발생한다.

TOO_MANY_ROWS는 SELECT INTO 명령에서 여러 개의 레코드를 가져오면 발생한다.

여기서는 SELECT INTO에 의해서 4건의 레코드가 조회되어 TOO_MANY_ROWS 에러가 발생하므로 외부 커서(explicit cursor)를 만들어서 작업을 해야 한다.

정답 : (d) (<- 마우스로 정답 부분을 선택하세요)

21-2. 다음 PL/SQL 블록을 실행했을 때 가장 먼저 화면에 출력되는 내용은?

BEGIN
        DECLARE
                weight CONSTANT NUMBER(2) := 1000;
        BEGIN
                dbms_output.put_line(‘INNER BLOCK’);
        EXCEPTION 
                WHEN VALUE_ERROR THEN
                        dbms_output.put_line(‘VALUE ERROR 1’);
                WHEN OTHERS THEN
                        dbms_output.put_line(‘UNKNOWN ERROR 1’);
        END;
        dbms_output.put_line(‘OUTER BLOCK’);
EXCEPTION
        WHEN VALUE_ERROR THEN
                dbms_output.put_line(‘VALUE ERROR 2’);
        WHEN OTHERS THEN
                dbms_output.put_line(‘UNKNOWN ERROR 2’);
END;

a. INNER BLOCK

b. VALUE ERROR 1

c. UNKNOWN ERROR 1

d. OUTER BLOCK

e. VALUE ERROR 2

f. UNKNOWN ERROR 2

<해설>

선언부(declarative part)에서 weight 상수를 선언할 때 VALUE_ERROR 에러가 발생한다. weight 상수의 타입인 NUMBER(2)에는 1000을 넣을 수 없기 때문이다.

만약 VALUE_ERROR 에러가 선언부가 아닌 실행부(executable part)에서 발생했다면 프로그램 수행은 자신이 속한 블록의 예외 처리부로 넘어가므로 ‘VALUE ERROR 1’ 이라고 출력될 것이다. 다음에 상위 블록의 ‘OUTER BLOCK’ 메시지가 출력되고 프로그램을 마치게 된다.

그런데 선언부에서 발생한 에러는 그 블록에 정의된 예외 처리 루틴에서 처리되지 않고 프로그램 수행이 상위 블록으로 넘어간다. 따라서 언뜻 보기에는 ‘VALUE ERROR 1’ 이라고 출력될 것 같지만 실제로는 상위 블록의 예외 처리 루틴으로 전달되어서 ‘VALUE ERROR 2’ 가 출력되고 프로그램은 종료된다. 에러가 하위 블록의 예외 처리 루틴에서 처리 되지 않았기 때문에 프로그램 수행은 상위 블록의 실행부가 아닌 예외 처리부로 직행하므로 ‘OUTER BLOCK’ 메시지는 출력되지 않는다.

에러는 자신이 속한 블록의 예외 처리부에서 처리되지 않을 경우 계속해서 상위 블록의 예외 처리 루틴을 찾게 되며 최상위의 블록에서도 처리해 주지 않으면 결국 호스트 환경으로 전달된다.

정답 : (e) (<- 마우스로 정답 부분을 선택하세요)

21-3. 아래와 같이 RAISE 명령을 사용하려면 선언부에서 어떻게 기술해야 하는가?

IF SQL%ROWCOUNT > 1000 THEN
        RAISE ex_limit;
END IF;

a. ex_limit PRAGMA EXCEPTION_INIT;

b. PRAGMA EXCEPTION_INIT(ex_limit);

c. ex_limit EXCEPTION;

d. ex_limit EXCEPTION_INIT;

<해설>

사용자가 예외(exception)를 정의하는 방법은 두 가지가 있다.

먼저 간단하게 RAISE 명령으로 예외(exception)를 발생시키는 목적이라면 (c)와 같이 선언만 해주면 된다.

그런데 STANDARD 패키지에 정의된 것과 같이 특정한 에러에 대해서 이름을 할당하고자 할 때에는 추가적으로 PRAGMA EXCEPTION_INIT 을 이용해서 에러 이름과 에러 번호를 지정해 주어야 한다. 예를 들면 다음과 같다.

insufficient_privileges EXCEPTION;

PRAGMA EXCEPTION_INIT(insufficient_privileges, -1031);

PRAGMA는 컴파일러 지시어로 EXCEPTION_INIT과 함께 사용하면 특정한 에러 번호에 사용자가 정의한 이름을 매핑시키는 역할을 한다. 그러면 프로그램 컴파일 단계에서(실행시가 아님) 매핑이 되어서 지정된 이름을 사용하여 예외 처리 루틴을 만들 수 있다.

정답 : (c) (<- 마우스로 정답 부분을 선택하세요)

21-4. 다음 프로그램에서 에러가 발생하는 이유는?

DECLARE
        v_empno NUMBER(4) := 7603;
        v_sal NUMBER(7,2);
BEGIN
        <>
SELECT sal/0 INTO v_sal FROM emp WHERE empno=v_empno;
EXCEPTION
        WHEN OTHERS THEN
                v_empno := 7900;
                ROLLBACK;
                GOTO retry;
END;

a. 예외 처리부에서 ZERO_DIVIDE 에러를 처리하지 않았기 때문에

b. 예외 처리부에서 GOTO를 이용해서 실행부로 되돌아 갈 수 없기 때문에

c. 예외 처리부에서는 ROLLBACK과 같은 트랜잭션 명령을 사용할 수 없기 때문에

d. 예외 처리부에서 WHEN OTHERS 만을 단독으로 사용할 수 없기 때문에

<해설>

이 문제에서는 SELECT 문에서 sal을 0으로 나눌 때 ZERO_DIVIDE 에러가 발생한다. ZERO_DIVIDE 에러를 처리하는 예외 처리 루틴은 준비되어 있지 않지만 그런 경우에는 WHEN OTHERS에 의해서 모두 처리된다.

문제는 GOTO 명령을 이용해서 예외 처리부에서 실행부로 되돌아 가려고 하는 데서 비롯된다. 일단 예외 처리 루틴으로 프로그램 수행이 넘어간 후에는 원래의 실행부로 복귀할 수는 없다. 다만 상위 블록의 실행부는 계속 수행될 수 있다.

예외 처리부에서는 에러에 따라서 트랜잭션을 COMMIT 또는 ROLLBACK 시켜야 하는 경우가 많기 때문에 이런 명령은 당연히 지원된다. 또한 WHEN OTHERS 는 에러 처리 루틴이 정의되어 있지 않은 모든 에러를 처리하며 WHEN OTHERS 만을 사용해도 상관 없다.

정답 : (b) (<- 마우스로 정답 부분을 선택하세요)

[Top]

No.	제목	작성자	작성일	조회
8760	OCP 강좌 - Backup and Recovery (1)	정재익	2001-12-07	9991
8759	OCP 강좌 - Architecture and Administration (2)	정재익	2001-12-07	11186
8758	OCP 강좌 - Architecture and Administration (1)	정재익	2001-12-07	10511
8751	OCP 강좌 - Introduction to Oracle: SQL and PL/SQL (2)	정재익	2001-12-07	14195
8750	OCP 강좌 - Introduction to Oracle: SQL and PL/SQL (1)	정재익	2001-12-07	22065
8679	오라클 강의록 일부	정재익	2001-12-03	9556
8142	XML for Real	정재익	2001-10-27	7147

작업시간: 0.017초, 이곳 서비스는
PostgreSQL v16.2로 자료를 관리합니다