SELECT CASE WHEN GROUPING(A.서비스ID) = 0 THEN A.서비스ID ELSE '합계' END AS 서비스ID,
CASE WHEN GROUPING(B.가입일자) = 0 THEN NVL(B.가입일자, '-') ELSE '소계' END AS 가입일자,
COUNT(B.회원번호) AS 가입건수
FROM 서비스 A LEFT OUTER JOIN 서비스가입 B
ON (A.서비스ID = B.서비스ID AND B.가입일자 BETWEEN '2013-01-01' AND '2013-01-31')
GROUP BY ROLLUP(A.서비스ID, B.가입일자);
해설 : ROLLUP은 계층 구조를 가진 SUB TOTAL을 생성하는 함수로, 나열된 칼럼의 순서가 변경되면 수행 결과도 변경된다. 위의 SQL 문장은 서비스ID에 대해서 가입입자별 가입건수 및 소계와 전체 가입건수를 구하되 OUTER JOIN을 수행하였으므로 가입내역이 없는 서비스ID(004)에 대해서도 SUB TOTAL을 출력하고 있다.
문제 42
Q. 아래의 데이터 모델에서 SQL을 이용하여 표(지역별 월별 이용량)와 같은 형식의 데이터를 추출하려고 할 때 올바른 SQL 문장은?
①
SELECT (CASE GROUPING(B.지역명) WHEN 0 THEN '지역전체' ELSE B.지역명 END) AS 지역명,
(CASE GROUPING(TO_CHAR(A.이용일시, 'YYYY.MM'))
WHEN 0 THEN '월별합계'
ELSE T0_CHAR(A.이용일시, 'YYYY.MM') END) AS 이용월,
SUM(A.이용량 AS) 이용량
FROM 이용내역 A INNER JOIN 지역 B ON (A.지역ID = B.지역ID)
GROUP BY ROLLUP(B.지역명, TO_CHAR(A.이용일시, 'YYYY.MM'));
②
SELECT (CASE GROUPING(B.지역ID) WHEN 1 THEN '지역전체' ELSE MIN(B.지역명) END) AS 지역명,
(CASE GROUPING(TO_CHAR(A.이용일시, 'YYYY.MM'))
WHEN 1 THEN '월별합계'
ELSE TO_CHAR(A.이용일시, 'YYYY.MM') END) AS 이용월,
SUM(A.이용량) AS 이용량
FROM 이용내역 A INNER JOIN 지역 B ON (A.지역ID = B.지역ID)
GROUP BY ROLLUP(B.지역ID, TO_CHAR(A.이용일시, 'YYYY.MM'));
③
SELECT (CASE GROUPING(B, 지역명) WHEN 1 THEN '지역전체' ELSE B.지역명 END) AS 지역명,
(CASE GROUPING(TO_CHAR(A.이용일시, 'YYYY.MM'))
WHEN 1 THEN '월별합계'
ELSE TO_CHAR(A.이용일시, 'YYYY.MM') END) AS 이용월,
SUM(A.이용량) AS 이용량
FROM 이용내역 A INNER JOIN 지역 B ON (A.지역ID = B.지역ID)
GROUP BY CUBE(B.지역명, TO_CHAR(A.이용일시, 'YYYY.MM'));
④
SELECT (CASE GROUPING(B, 지역ID) WHEN 1 THEN '지역전체' ELSE MIN(B.지역명) END) AS 지역명,
(CASE GROUPING(TO_CHAR(A.이용일시, 'YYYY.MM'))
WHEN 1 THEN '월별합계'
ELSE TO_CHAR(A.이용일시, 'YYYY.MM') END) AS 이용월,
SUM(A.이용량) AS 이용량
FROM 이용내역 A INNER JOIN 지역 B ON (A.지역ID = B.지역ID)
GROUP BY GROUPING SETS(B.지역ID, TO_CHAR(A.이용일시, 'YYYY.MM'));
해설 :위의 결과 데이터는 지역에 대해서 월별 이용량 및 소계와 전체 이용량을 출력하였으므로, ROLLUP 함수를 할용할 수 있다. ROLLUP 집계 그룹 함수는 나열된 칼럼에 대해 계층 구조로 집계를 출력하는 함수로써 ROLLUP(A, B)를 수행하면 (A, B)별 집계, A별 집계와 전체 집계를 출력할 수 있다.
① CASE 절의 GROUPING 함수의 사용이 잘못되었다. (0이 아닌 1이 되어야 한다.)
문제 43
Q. 아래 결과를 얻기 위한 SQL문에서 ( ㄱ ) 에 들어갈 함수를 작성하시오.
[SQL문]
SELECT 구매고객, 구매월, COUNT(*) "총 구매건", SUM(구매금액) '총 구매액'
FROM 구매이력
GROUP BY ( ㄱ )(구매고객, 구매월)
해설 :위 SQL의 결과는 (구매고객, 구매월)별, 구매고객별 그리고 전체에 대한 구매건수와 구매금액을 출력한 결과이다. 집계에 계층 구조가 있으므로 나열된 칼럼에 대해 계층 구조로 집계를 출력하는 ROLLUP을 사용하여 집계 SQL을 작성할 수 있다.
문제 44
Q. 다음 설명 중 가장 적절한 것은?
① 일반 그룹 함수를 사용하여 CUBE, GROUPING SETS와 같은 그룹 함수와 동일한 결과를 추출할 수 있으나, ROLLUP 그룹 함수와 동일한 결과는 추출할 수 없다. ② GROUPING SETS 함수의 경우에는 함수의 인자로 주어진 칼럼의 순서에 따라 결과가 달라지므로 컬럼의 순서가 중요하다. ③ CUBE, ROLLUP, GROUPING SETS 함수들의 대상 칼럼 중 집계된 컬럼 이외의 대상 칼럼 값은 해당 컬럼의 데이터 중 가장 작은 값을 반환한다. ④ CUBE 그룹 함수는 인자로 주어진 칼럼의 결합 가능한 모든 조합에 대해서 집계를 수행하므로 다른 그룹 함수에 비해 시스템에 대한 부하가 크다.
① CUBE, GROUPING SETS, ROLLUP 세 가지 그룹 함수 모두 일반 그룹 함수로 동일한 결과를 출력할 수 있다.
② 함수의 인자로 주어진 칼럼의 순서에 따라 다른 결과를 추출하게 되는 그룹 함수는 ROLLUP 이며, 나열된 칼럼에 대해 계층 구조로 집계를 출력한다.
③ CUBE, ROLLUP, GROUPING SETS 함수들에 의해 집계된 레코드에서 집계 대상 칼럼 이외의 GROUP 대상 칼럼의 값은 NULL을 반환한다.
※ Grouping Columns이 가질 수 있는 모든 경우에 대하여 Subtotal을 생성해야 하는 경우에는 CUBE를 사용하는 것이 바람직하나, ROLLUP에 비해 시스템에 많은 부담을 주므로 사용에 주의해야 한다.
문제 45
Q. 아래와 같이 설비와 에너지사용 테이블을 이용하여 결과를 나타내려할 때 SQL으로 가장 적절한 것을 2개 고르시오.
①
SELECT A.설비, B.에너지코드, SUM(B.사용량) AS 사용량합계
FROM 설비 A INNER JOIN 에너지사용량 B
ON (A.설비ID = B.설비ID)
GROUP BY CUBE ((A.설비ID), (B.에너지코드), (A.설비ID, B.에너지코드))
ORDER BY A.설비ID, B.에너지코드;
②
SELECT A.설비, B.에너지코드, SUM(B.사용량) AS 사용량합계
FROM 설비 A INNER JOIN 에너지사용량 B
ON (A.설비ID = B.설비ID)
GROUP BY CUBE (A.설비ID, B.에너지코드)
ORDER BY A.설비ID, B.에너지코드;
③
SELECT A.설비, B.에너지코드, SUM(B.사용량) AS 사용량합계
FROM 설비 A INNER JOIN 에너지사용량 B
ON (A.설비ID = B.설비ID)
GROUP BY GROUPING SETS((A.설비ID), (B.에너지코드), (A.설비ID, B.에너지코드), 0)
ORDER BY A.설비ID, B.에너지코드;
④
SELECT A.설비, B.에너지코드, SUM(B.사용량) AS 사용량합계
FROM 설비 A INNER JOIN 에너지사용량 B
ON (A.설비ID = B.설비ID)
GROUP BY GROUPING SETS((A.설비ID), (B.에너지코드), (A.설비ID, B.에너지코드))
ORDER BY A.설비ID, B.에너지코드;
해설 : SQL의 결과를 보면 설비ID와 에너지코드의 모든 조합에 대하여 사용량합계를 추출하고 있다. CUBE 함수는 인수로 나열된 항목의 가능한 모든 조합에 대하여 GROUPING을 수행한다. 또한 GROUPING SETS은 사용자가 원하는 다양한 조합을 인수로 사용할 수 있다. 위 문제에서 ②번은 CUBE를 사용하였으므로 CUBE 절에 나열된 칼럼의 모든 조합 즉, ((설비ID), (에너지코드), (설비ID, 에너지코드))에 대해 SUB TOTAL을 만들게 된다. ③번은 GROUPING SETS를 할용하여 ②번의 모든 조합을 직접 기술하였다.
※ CUBE는 결합 가능한 모든 값에 대하여 다차원 집계를 생성한다. CUBE도 결과에 대한 정렬이 필요한 경우는 ORDER BY 절에 명시적으로 정렬 칼럼이 표시가 되어야 한다.
문제 46
Q. 자재발주 테이블에 SQL을 수행하여 아래와 같은 결과를 얻었다. 다음 중 ( ㄱ )에 들어갈 문장으로 옳은 것은?
[SQL]
SELECT CASE WHEN GROUPING(자재번호) = 1 THEN '자재전체' ELSE 자재번호 END AS 자재번호,
CASE WHEN GROUPING(발주처ID) = 1 THEN '발주처전체' ELSE 발주처ID END AS 발주처ID,
CASE WHEN GROUPING(발주일자) = 1 THEN '발주일자전체' ELSE 발주일자 END AS 발주일자,
SUM(발주수량) AS 발주수량합계
FROM 자재발주
( ㄱ )
ORDER BY 자재번호, 발주처ID, 발주일자;
① GROUP BY CUBE (자재번호, (발주처ID, 발주일자)) ② GROUP BY CUBE (자재번호, 발주처ID, 발주일자) ③ GROUP BY GROUPING SETS (자재번호, 발주처ID, 발주일자) ④ GROUP BY GROUPING SETS (자재번호, (발주처ID, 발주일자))
해설 : 집계 그룹 함수에는 ROLLUP, CUBE, GROUPING SETS 함수가 있다. 문제의 결과 데이터는 (자재번호별) SUB TOTAL과 (자재번호, 발주처별) SUB TOTAL을 출력하고 있다. GROUPING SETS 함수를 사용하여 입력된 인수들에 대한 개별 집계를 구할 수 있으며, CUBE 함수의 경우는 나열된 모든 인수의 결합 가능한 집계가 출력된다. 그러므로 위의 문제에서는 GROUP BY GROUPING SETS(자재번호, (발주처ID, 발주일자))가 되어야 한다.
※ GROUPING SETS 은 다양한 소계 집합을 만들 수 있는데, GROUPING SETS에 표시된 인수들에 대한 개별 집게를 구할 수 있으며, 이때 표시된 인수들 간에는 계층 구조인 ROLLUP과는 달리 평등한 관계이므로 인수의 순서가 바뀌어도 결과는 같다. 그리고 GROUPING SETS 함수도 결과에 대한 정렬이 필요한 경우는 ORDER BY 절에 명시적으로 정렬 칼럼이 표시가 되어야 한다.
문제 47
Q. 다음 중 월별매출 테이블을 대상으로 아래 SQL을 수행한 결과인 것은?
[SQL]
SELECT 상품ID, 월, SUM(매출액) AS 매출액
FROM 월별매출
WHERE 월 BETWEEN '2014.10' AND '2014.12'
GROUP BY GROUPING SETS((상품ID, 월));
해설 : GROUPING SETS 함수는 표시된 인수들에 대한 개별 집계를 구하는 기능을 하며, 위의 SQL은 (상품ID, 월)별 집계 데이터를 출력한다.
문제 48
Q. 다음 중 윈도우 함수(Window Function, Analytic Function)에 대한 설명으로 가장 부적절한 것은?
① Partition과 Group By 구문은 의미적으로 유사하다. ② Partition 구문이 없으면 전체 집합을 하나의 Partition으로 정의한 것과 동일하다. ③ 윈도우 함수 처리로 인해 결과 건수가 줄어든다. ④ 윈도우 함수 적용 범위는 Partition을 넘을 수 없다.
SELECT 고객번호, 고객명, 매출액, RANK() OVER(ORDER BY 매출액 DESC) AS 순위
FROM (
SELECT A.고객번호, MAX(A.고객명) AS 고객명, SUM(B.매출액) AS 매출액
FROM 고객 A INNER JOIN 월별매출 B
ON (A.고객번호 = B.고객번호)
GROUP BY A.고객번호
)
ORDER BY RNK;
해설 : 위의 SQL은 고객별 매출액과 매출 순위를 구하되 동일 순위일 경우 중간 순위를 비워둔 데이터를 추출한다. 순위를 구하는 함수로는 RANK, DENSE_RANK, ROW_NUMBER 함수가 있다. RANK WINDOW FUNCTION은 동일 값에 대해서는 동일 순위를 부여하고, 중간 순위는 비워 두지만, DENSE_RANK 함수는 동일 순서를 부여하되 중간 순위를 비우지 않는다. ROW_NUMBER 함수는 동일 값에 대해서도 유일한 순위를 부여한다.
※ RANK 함수는 ORDER BY를 포함한 QUERY 문에서 특정 항목(칼럼)에 대한 순위를 구하는 함수이며, 동일한 값에 대해서는 동일한 순위를 부여하게 된다.
문제 50
Q. 아래 데이터 모델에서 활동점수가 높은 고객을 게임상품ID별로 10등까지 선별하여 사은행사를 진행하려고 한다. 다음 SQL 중, 가장 적절한 것은? (단, 활동점수가 동일한 고객은 동일등수로 한다. 아래 결과 예제 참조)
①
SELECT 게임상품ID, 고객ID, 활동점수, 순위
FROM (SELECT DENSE_RANK() OVER(ORDER BY 활동점수 DESC) AS 순위, 고객ID, 게임상품ID, 활동점수
FROM 고객활동)
WHERE 순위 <= 10;
②
SELECT 게임상품ID, 고객ID, 활동점수, 순위
FROM (SELECT DENSE_RANK() OVER(PARTITION BY 게임상품ID ORDER BY 활동점수 DESC) AS 순위,
고객ID, 게임상품ID, 활동점수
FROM 고객활동)
WHERE 순위 <= 10;
③
SELECT 게임상품ID, 고객ID, 활동점수, 순위
FROM (SELECT RANK() OVER(ORDER BY 활동점수 DESC) AS 순위, 고객ID, 게임상품ID, 활동점수
FROM 고객활동)
WHERE 순위 <= 10;
④
SELECT 게임상품ID, 고객ID, 활동점수, 순위
FROM (SELECT RANK() OVER(PARTITION BY 게임상품ID ORDER BY 활동점수 DESC) AS 순위,
고객ID, 게임상품ID, 활동점수
FROM 고객활동);
해설 : 게임상품별로 고객 목록을 추출하기 위해서는 OVER 절에 'PARTITION BY 게임상품ID' 를 적용하여 게임상품별 활동점수로 순위가 추출될 수 있도록 하여야 한다. RANK WINDOW 함수는 OVER 절의 ORDER BY에 대한 결과에 따라 동일한 값을 동일한 등수로 처리함과 동시에 중간 순위를 비우는 반면, DENSE_RANK WINDOW 함수는 중간 순위를 비우지 않는다.
728x90
문제 51
Q. 다음 중 추천내역 테이블에서 아래와 같은 SQL을 수행하였을 때의 결과로 가장 적절한 것은?
[SQL]
SELECT 추천경로, 추천인, 피추천인, 추천점수
FROM (SELECT 추천경로, 추천인, 피추천인, 추천점수,
ROW_NUMBER() OVER(PARTITION BY 추천경로 ORDER BY 추천점수 DESC) AS RNUM
FROM 추천내역)
WHERE RNUM = 1;
해설 : ROW_NUMBER 함수는 ORDER BY 절에 의해 정렬된 데이터에 동일 값이 존재하더라도 유일한 순위를 부여하는 함수로서 데이터 그룹 내에 유일한 순위를 추출할 때 사용할 수 있는 함수이다. 문제의 SQL은 추천경로별(PARTION BY 추천경로)로 추천점수가 가장 높은(ORDER BY 추천점수 DESC) 데이터를 한 건씩만 출력한다.
※ ROW_NUMBER 함수는 RANK나 DENSE_RANK 함수가 동일한 값에 대해서는 동일한 순위를 부여하는데 반해, 동일한 값이라도 고유한 순위를 부여한다.
문제 52
Q. 다음 중 아래의 SQL에 대한 설명으로 가장 적절한 것은?
[SQL]
SELECT 상품분류코드, AVG(상품가격) AS 상품가격,
COUNT(*) OVER(ORDER BY AVG(상품가격) RANGE BETWEEN 10000 PRECEDING
AND 10000 FOLLOWING) AS 유사개수
FROM 상품
GROUP BY 상품분류코드;
① WINDOW FUNCTION을 GROUP BY 절과 함께 사용하였으므로 위의 SQL은 오류가 발생한다. ② WINDOW FUNCTION의 ORDER BY 절에 AVG 집계 함수를 사용하였으므로 위의 SQL은 오류가 발생한다. ③ 유사개수 칼럼은 상품분류코드별 평균상품가격을 서로 비교하여 -10000 ~ +10000 사이에 존재하는 상품분류코드의 개수를 구한 것이다. ④ 유사개수 칼럼은 상품전체의 평균상품가격을 서로 비교하여 -10000 ~ +10000 사이에 존재하는 상품의 개수를 구한 것이다.
해설 : GROUP BY 절의 집합을 원본으로 하는 데이터를 WINDOW FUNCTION과 함께 사용한다면 GROUP BY 절과 함께 WINDOW FUNCTION을 사용한다고 하더라도 오류가 발생하지 않으며, 유사개수 칼럼은 상품분류코드로 GROUPING된 집합을 원본 집합으로 하여 상품분류코드별 평균상품가격을 서로 비교하여 현재 읽혀진 상품분류코드의 평균 가격 대비 -10000 ~ +10000 사이에 존재하는 상품분류코드의 개수를 구한 것이다.
문제 53
Q. 다음 중 [사원] 테이블에 대하여 아래와 같은 SQL을 수행하였을 때 예상되는 결과로 가장 적절한 것은?
[SQL]
SELECT Y.사원ID, Y.부서ID, Y.사원명, Y.연봉
FROM (SELECT 사원ID, MAX(연봉) OVER(PARTITION BY 부서ID) AS 최고연봉
FROM 사원) X, 사원 Y
WHERE X.사원ID = Y.사원ID
AND X.최고연봉 = Y.연봉;
해설 : 안쪽 IN-LINE VIEW에 의해 아래와 같이 사원ID와 부서별 최고연봉이 결과로 생성되며,
이를 다시 사원 테이블과 사원ID = 사원ID AND 최고연봉 = 연봉 으로 JOIN을 하게 되면 부서별 최고연봉의 사원이 출력된다.
문제 54
Q. 다음 중 아래 SQL의 실행 결과로 가장 적절한 것은?
CREATE TABLE TBL
(ID VARCHAR2(10),
START_VAL NUMBER,
END_VAL NUMBER)
SELECT ID, START_VAL, END_VAL
FROM (
SELECT ID, START_VAL, NVL(END_VAL, 99) END_VAL,
(CASE WHEN START_VAL = LAG(END_VAL) OVER (PARITTION BY ID
ORDER BY START_VAL, NVL(END_VAL, 99)) THEN 1 ELSE 0
END) FLAG1,
(CASE WHEN END_VAL = LEAD(START_VAL) OVER (PARTITION BY ID
ORDER BY START_VAL, NVL(END_VAL, 99)) THEN 1 ELSE 0
END) FLAG2
FROM TBL)
WHERE FLAG1 = 0
OR FLAG2 = 0;
해설 : LAG 함수는 현재 읽혀진 데이터의 이전 값을, LEAD 함수는 이후 값을 알아내는 함수이다. 위의 SQL에서 각 레코드별 FLAG1, FLAG2의 값은 다음과 같으며, 메인 쿼리의 WHERE 절이 FLAG1 = 0 OR FLAG2 = 0이므로 1, 4, 5, 6번째의 행이 출력된다.
※ LAG 함수를 이용해 파티션별 윈도우에서 이전 몇 번째 행의 값을 가져올 수 있다. 이후 몇 번째 행의 값을 가져오는 것은 LEAD 함수이며, SQL Server에서는 지원하지 않는 함수이다.
문제 55
Q. 아래 설명 중, ( ㄱ ), ( ㄴ ) 에 해당하는 내용을 작성하시오.
DBMS에 생성된 USER와 다양한 권한들 사이에서 중개 역할을 할 수 있도록 DBMS에서는 ROLE을 제공한다. 이러한 ROLE을 DBMS USER에게 부여하기 위해서는 ( ㄱ ) 명령을 사용하며, ROLE을 회수하기 위해서는 ( ㄴ ) 명령을 사용한다.
해설 : GRANT 명령은 DBMS 사용자에게 권한을 부여할 때 사용하며, REVOKE 명령은 부여된 권한을 회수할 때 사용한다.
문제 56
Q. 다음 중 B_User가 아래의 작업을 수행할 수 있도록 권한을 부여하는 DCL로 가장 적절한 것은?
UPDATE A_User.TB_A
SET col1='AAA'
WHERE col2=3;
① GRANT SELECT, UPDATE TO B_User; ② REVOKE SELECT ON A_User.TB_A FROM B_User; ③ DENY UPDATE ON A_User.TB_A TO B_User; ④ GRANT SELECT, UPDATE ON A_User.TB_A TO B_User;
해설 :권한을 부여하는 명령어는 GRANT이며, WHERE 조건의 데이터를 찾기 위한 SELECT 권한과 데이터 변경을 위한 UPDATE 권한이 필요하다.
문제 57
Q. 아래의 ( ㄱ ) 에 들어갈 내용을 쓰시오.
DBMS에 사용자를 생성하면 기본적으로 많은 권한을 부여해야 한다. 많은 DBMS에서는 DBMS 관리자가 사용자별로 권한을 관리해야 하는 부담과 복잡함을 줄이기 위하여 다양한 권한을 그룹으로 묶어 관리할 수 있도록 사용자와 권한 사이에서 중개 역할을 수행하는 ( ㄱ ) 을 제공한다.
해설 : ROLE 은 많은 DBMS 사용자에게 개별적으로 많은 권한을 부여하는 번거로움과 어려움을 해소하기 위해 다양한 권한을 하나의 그룹으로 묶어놓은 권한의 그룹이다.
문제 58
Q. 사용자 Lee가 릴레이션 R을 생성한 후, 아래와 같은 권한 부여 SQL문들을 실행하였다. 그 이후에 기능이 실행 가능한 SQL을 2개 고르시오. (단, A, B의 데이터 타입은 정수형이다.)
Lee: GRANT SELECT, INSERT, DELETE ON R TO Kim WITH GRANT OPTION;
Kim: GRANT SELECT, INSERT, DELETE ON R TO Park;
Lee: REVOKE DELETE ON R FROM Kim;
Lee: REVOKE INSERT ON R FROM Kim CASCADE;
① Park： SELECT * FROM R WHERE A = 400; ② Park： INSERT INTO R VALUES(400, 600); ③ Park： DELETE FROM R WHERE B = 800; ④ Kim : INSERT INTO R VALUES(500, 600);
① Kim에게 테이블 R에 SELECT, INSERT, DELETE 권한을 주면서 Kim이 다른 유저에게 테이블 R에 동일한 권한을 줄 수 있다.
③ Kim에게서 테이블 R의 DELETE 권한을 취소한다.
문제 59
Q. 다음 중 PL/SQL에 대한 설명으로 가장 부적절한 것은?
① 변수와 상수 등을 사용하여 일반 SQL 문장을 실행할 때 WHERE절의 조건 등으로 대입할 수 있다. ② Procedure, User Defined Function, Trigger 객체를 PL/SQL로 작성할 수 있다. ③ PL/SQL로 작성된 Procedure, User Defined Function은 전체가 하나의 트랜젝션으로 처리되어야 한다. ④ Procedure 내부에 작성된 절차적 코드는 PL/SQL엔진이 처리하고 일반적인 SQL 문장은 SQL실행기가 처리한다.
해설 : PL/SQL로 작성된 Procedure, User Defined Function은 작성자의 기준으로 트랜잭션을 분할할 수 있으며, 또한 프로시저 내에서 다른 프로시저를 호출할 경우에 호출 프로시저의 트랜잭션과는 별도로 PRAGMA AUTONOMOUS_TRANSACTION을 선언하여 자율 트랜잭션 처리를 할 수 있다.
※ PL/SQL의 특징
- PL/SQL은 Block 구조로 되어있어 각 기능별로 모듈화가 가능하다. - 변수, 상수 둥을 선언하여 SQL 문장 간 값을 교환한다. - IF, LOOP 등의 절차형 언어를 사용하여 절차적인 프로그램이 가능하도록 한다. - DBMS 정의 에러나 사용자 정의 에러를 정의하여 사용할 수 있다. - PL/SQL은 Oracle에 내장되어 있으므로 Oracle과 PL/SQL을 지원하는 어떤 서버로도 프로그램을 옮길 수 있다. - PL/SQL은 응용 프로그램의 성능을 향상시킨다. - PL/SQL은 여러 SQL 문장을 Block으로 묶고 한 번에 Block 전부를 서버로 보내기 때문에 통신량을 줄일 수 있다.
문제 60
Q. 아래는 임시부서(TMP_DEPT) 테이블로부터 부서(DEPT) 테이블에 데이터를 입력하는 PL/SQL이다. 부서 테이블에 데이터를 입력하기 전에 부서 테이블의 모든 데이터를 ROLLBACK이 불가능 하도록 삭제하려고 한다. 다음 중 ( ㄱ ) 에 들어갈 내용으로 옳은 것은?
[PL/SQL]
create or replace procedure insert_dept authid current_user as
begin
( ㄱ )
INSERT /* + APPEND */ INTO DEPT (DEDPTNO, DNAME, LOC)
SELECT DEPTNO, DNAME, LOC
FROM TMP_DEPT;
commit;
end;
/
① TRUNCATE TABLE DEPT; ② DELETE FROM DEPT; ③ execute immediate 'TRUNCATE TABLE DEPT;' ④ execute 'TRUNCATE TABLE DEPT';
해설 :PL/SQL에서는 동적 SQL 또는 DDL 문장을 실행할 때 EXECUTE IMMEDIATE를 사용해야 한다. ②번은 ROLLBACK이 가능하도록 삭제하는 것이 아니므로 옳은 답이 아니다.
문제 61
Q. 다음 중 절차형 SQL 모듈에 대한 설명으로 가장 부적절한 것은?
① 저장형 프로시저는 SQL을 로직과 함께 데이터베이스 내에 저장해 놓은 명령문의 집합을 의미한다. ② 저장형 함수(사용자 정의 함수)는 단독적으로 실행되기 보다는 다른 SQL문을 통하여 호출되고 그 결과를 리턴하는 SQL의 보조적인 역할을 한다. ③ 트리거는 특정한 테이블에 INSERT, UPDATE, DELETE와 같은 DML문이 수행되었을 때 데이터베이스에서 자동으로 동작하도록 작성된 프로그램이다. ④ 데이터의 무결성과 일관성을 위해서 사용자 정의 함수를 사용한다.
해설 : Stored Module(ex: PL/SQL, LP/SQL, T-SQL)로 구현 가능한 기능은 ①, ②, ③ 세 가지이며, ④ 데이터의 무결성과 일관성을 위해 사용자 정의 함수를 사용하는 것은 트리거의 용도이다.
※ 저장 모듈(Stored Module)
SQL 문장을 데이터베이스 서버에 저장하여 사용자와 애플리케이션 사이에서 공유할 수 있도록 만든 일종의 SQL 컴포넌트 프로그램이며, 독립적으로 실행되거나 다른 프로그램으로부터 실행될 수 있는 완전한 실행 프로그램이다. Oracle의 저장 모듈에는 Procedure, User Defined Function, Trigger가 있다.
문제 62
Q. 다음 중 Trigger에 대한 설명으로 가장 부적절한 것은?
① Trigger는 데이터베이스에 의해서 자동으로 호출되고 수행된다. ② Trigger는 특정 테이블에 대해서 INSERT, UPDATE, DELETE 문이 수행되었을 때 호출되도록 정의할 수 있다. ③ Trigger는 TCL을 이용하여 트랜잭션을 제어할 수 있다. ④ Trigger는 데이터베이스에 로그인하는 작업에도 정의할 수 있다.
해설 : Trigger는 Procedure와 달리 Commit 및 Rollback 과 같은 TCL을 사용할 수 없다.
문제 63
Q. 다음 중 특정한 테이블에 INSERT, UPDATE, DELETE와 같은 DML문이 수행되었을 때, 데이터베이스에서 자동으로 동작하도록 작성된 저장 프로그램으로 가장 적절한 것은? (단, 사용자가 직접 호출하여 사용하는 것이 아니고 데이터베이스에서 자동적으로 수행하게 된다.)
① PROCEDURE ② USER DEFINED FUNCTION ③ PACKAGE ④ TRIGGER
