필기시험
-
다음 중 PL/SQL의 특징이 아닌 것은? (2)
1) PL/SQL은 오류 처리를 할 수 있다.
2) PL/SQL로 작성한 프로그램은 모두 데이터베이스에 P-Code로 저장된다.
3) PL/SQL로 조건문 및 루프와 같은 제어구조 프로그래밍을 할 수 있다.
4) PL/SQL은 SQL을 확장한 절차적 언어(Procedural Language)를 나타냅니다. -
다음 중 변수선언 방법이 옳은것은? (1)
1) v_hire_date DATE;
2) v_dept_id emp%RECORDTYPE;
3) v_emp_name VARCHAR2;
4) v_salary NUMBER(7) NOT NULL; -
변수이름에 대한 요구사항이 틀린 것은 ? (3)
1) 문자로 시작해야한다.
2) 문자,숫자, 특수문자(#,$,_) 사용가능하다.
3) 100 이하 문자 포함할수있다.
4) 예약어를 포함하면 안된다. -
다음 %TYPE 속성에 대한 설명 중 옳은 것을 고르시오. (3)
1) 테이블 명을 접두어로 붙여서 사용한다.
2) 해당 테이블 및 컬럼의 속성 뿐만 아니라 해당 Row의 데이터가 기본(Default)값으로 변수에 할당된다.
3) 이전에 선언한 변수를 접두어로 붙여서 쓸 수 있다.
4) 해당 컬럼의 제약조건도 할당이 된다. -
바인드 변수 설명으로 틀린것은 (4)
1) 호스트 환경에서 생성하며 호스트 변수라고 한다.
2) VARIABLE 키워드를 사용하여 생성한다.
3) SQL문과 익명블록 구조에서 콜론(:)접두어로 붙여서 사용한다.
4) 프로시저와 함수에서 사용할 수 있다. -
PL/SQL 표현식에서 숫자, 문자, 변환, 날짜 단일 행 함수와 같은 대부분의 SQL 단일 행 함수를 사용할 수 있는가? (1)
1) 맞습니다.
2) 틀립니다. -
PL/SQL에서 암시적 커서는 SELECT INTO 절이 필요하며 query 에서 한 행 이상 반환할 수 있습니다. (2)
1) 맞습니다.
2) 틀립니다. -
가장 최근의 update문이 한 행 이상에 영향을 미친 경우 TRUE로 평가되는 부울 속성은? (2)
1) SQL%ROWCOUNT
2) SQL%FOUND
3) SQL%NOTFOUND
4) SQL%ISOPEN -
매번 반복을 시작할 때마다 조건이 평가되어야 하는 LOOP는? (2)
1) 기본 LOOP
2) WHILE
3) FOR
4) IF -
조합 데이터 유형에 대한 설명 중 옳은 것을 고르시오 (1)
1) 서로 다른 데이터 유형의 값을 저장하려는 경우 PL/SQL 레코드를 사용한다.
2) PL/SQL Record에는 EXISTS, COUNT, DELETE 같은 레코드의 Method 가 제공된다.
3) %TYPE을 이용하여 PL/SQL Record 형으로 변수를 선언할 수 있다.
4) 가변 크기 배열(VARRAY)은 VARRAY 크기에 제약이 없다. -
명시적 커서 단계가 아닌 것은? (3)
1) 선언
2) OPEN
3) EXIT
4) FETCH -
명시적 커서 속성중에 가장 최근 패치(fetch)가 행을 반환하면 TRUE로 평가하는 속성은? (1)
1) %FOUND
2) %NOTFOUND
3) %ROWCOUNT
4) %ISOPEN -
명시적커서 속성중에 지금까지 fetch한 수를 평가하는 속성은? (3)
1) %FOUND
2) %NOTFOUND
3) %ROWCOUNT
4) %ISOPEN -
Exception 트래핑과 관계없는 한 가지를 고르시오. (4)
1) SQLCODE
2) SQLERRM
3) PRAGMA EXCEPTION_INIT
4) %TYPE -
예외 트랩에 대해 틀린 것은? (4)
1) 예외 처리 섹션은 EXCEPTION 키워드로 시작합니다.
2) 여러 예외 처리기를 사용할 수 있습니다.
3) 블록을 종료하기 전 하나의 처리기만 실행됩니다.
4) WHEN OTHERS는 맨처음에 입력하는 절입니다. -
내가 만든 오류 코드 및 오류 메시지를 출력하는 프로시져는? (4)
1) SQLCODE
2) SQLERRM
3) PRAGMA EXCEPTION_INIT
4) RAISE_APPLICATION_ERROR -
익명 블록의 설명이 틀린 것은? (3)
1) 이름이 지정되지 않은 PL/SQL 블록
2) 매번 실행시에 컬파일 발생
3) 다른 응용 프로그램에서 호출할 수 있다
4) 데이터베이스에 저장되지 않습니다.
-
서브 프로그램에 대한 설명이다. 옳은 것은? (3)
1) 함수는 Return을 이용하여 호출환경으로 값을 전달할 수 있지만 프로시져는 호출환경으로 값을 전달할 수 없다.
2) 함수는 DML 문장을 포함할 수 없고 프로시져는 포함할 수 있다.
3) 함수는SQL 표현식의 일부로 호출될 수 있지만 프로시져는 불가능 하다.
4) 함수와 프로시져 모두 Oracle Server에 소스코드 형태로 존재하며 런타임에서 Parsing 작업이 일어난다. -
파라미터 설명으로 틀린 것은? (4)
1) IN 파라미터는 상수 값을 호출 환경에서 프로시저로 전달합니다.
2) OUT 파라미터는 값을 프로시저에서 호출 환경으로 전달합니다.
3) IN OUT 파라미터는 값을 호출 환경에서 프로시저로 전달하며, 동일한 파라미터를 사용하여 값을 프로시저에서 다시 호출 환경으로 전달합니다.
4) OUT 파라미터는 기본값을 할당할 수 있습니다. -
서브 프로그램을 호출할 때 이름지정 방식의 부호는 ? (2)
1) =
2) =>
3) :
4) := -
함수 설명으로 틀린 것은? (4)
1) 값을 반환하는 이름이 있는 PL/SQL 블록입니다.
2) 반복 실행을 위해 데이터베이스에 스키마 객체로 저장한다.
3) 표현식의 일부로 호출되거나 또 다른 서브 프로그램에 파라미터 값을 제공하는 데 사용됩니다.
4) PL/SQL 패키지로 그룹화할 수 없습니다. -
논리적으로 관련된 PL/SQL 유형, 변수 및 서브프로그램을 그룹화하는 객체는? (3)
1) 함수
2) 프로시저
3) 패키지
4) 트리거 -
Package에 대한 설명으로 옳은것은? (1)
1) SPEC 부분에는 Public 변수가 선언된다.
2) Body에서 생성된 Private 변수나 Private 프로시져는 외부의 프로그램에서 호출되어질 수 있다.
3) Package 를 생성하기 위해서는 CREATE PACKAGE 시스템 권한을 필요로 한다.
4) SPEC 없이 BODY만으로도 Package 구성이 가능하다. -
Trigger에 대한 설명으로 옳지 않은 것은? (1)
1) INSTEAD OF는 테이블에서 사용한다.
2) FOR EACH ROW 키워드를 이용하면 Row Trigger를 작성할 수 있다.
3) OLD 및 NEW 수식자는 Row Trigger에서 사용할 수 있습니다
4) 암시적으로 호출되는 스키마 객체이다.
25.로컬 서브 프로그램의 설명으로 틀린 것은? (4)
1) 반복되는 코드를 줄일 수 있습니다.
2) 코드 가독성이 향상되고 유지 관리가 쉽습니다.
3) 유지 관리할 프로그램이 두 개가 아닌 한 개이기 때문에 관리 작업을 줄일 수 있습니다.
4) 로컬 서브 프로그램을 외부에서도 호출할수있다.
실기시험
[문제1] 사원의 last_name 값을 입력 받아서 그 사원의 employee_id, last_name, department_name 출력하고
만약의 없는 last_name을 입력 할경우에는 "James 라는 사원은 존재하지 않습니다." 출력 하는 익명 블록 프로그램을 만드세요.
입력값 : king
Employee Id = 156 Name = King Department Name = Sales
Employee Id = 100 Name = King Department Name = Executive
입력값 : james
James 라는 사원은 존재하지 않습니다.
<풀이>
VARIABLE b_last_name varchar2(30);
DECLARE
TYPE v_rec_type IS RECORD(
v_id hr.employees.employee_id%type,
v_name hr.employees.last_name%type,
v_dept_name hr.departments.department_name%type);
TYPE v_tab_type IS TABLE OF v_rec_type;
v_tab v_tab_type;
v_err EXCEPTION;
BEGIN
:b_last_name := 'james';
SELECT employee_id, last_name, (SELECT department_name
FROM hr.departments
WHERE department_id = a.department_id)
BULK COLLECT INTO v_tab
FROM hr.employees a
WHERE last_name = :b_last_name;
IF v_tab.count > 0 THEN
FOR i IN v_tab.first..v_tab.last LOOP
dbms_output.put_line('Employee Id = '||v_tab(i).v_id || ' Name = ' || v_tab(i).v_name || ' Department Name = '||
v_tab(i).v_dept_name);
END LOOP;
ELSE
RAISE v_err;
END IF;
EXCEPTION
WHEN v_err THEN
dbms_output.put_line(:b_last_name||' 라는 사원은 존재하지 않습니다.');
END;
/[문제2] 부서 코드를 입력값으로 받아서 그 부서 사원들의 번호, 급여 근무년수, 부서이름을 출력하는 프로시저를 만드세요.
<화면 출력>
SQL> exec dept_year_proc(30)
번호: 114 급여: 11000 근무년수: 21 부서이름: Purchasing
번호: 115 급여: 3100 근무년수: 21 부서이름: Purchasing
번호: 116 급여: 2900 근무년수: 18 부서이름: Purchasing
번호: 117 급여: 2800 근무년수: 18 부서이름: Purchasing
번호: 118 급여: 2600 근무년수: 17 부서이름: Purchasing
번호: 119 급여: 2500 근무년수: 16 부서이름: Purchasing
SQL> exec dept_year_proc(120)
120 부서는 존재하지 않습니다.
PL/SQL procedure successfully completed.
<풀이>
CREATE OR REPLACE PROCEDURE dept_year_proc(
dept_id IN number)
IS
TYPE v_rec_type IS RECORD(
v_emp_id number,
v_sal number,
v_year number,
v_dept_name varchar2(30));
TYPE v_tab_type IS TABLE OF v_rec_type;
v_tab v_tab_type;
v_err EXCEPTION;
BEGIN
SELECT employee_id, salary, trunc(months_between(sysdate,hire_date)/12) as work_year,
(SELECT department_name
FROM hr.departments
WHERE department_id = a.department_id)
BULK COLLECT INTO v_tab
FROM hr.employees a
WHERE department_id = dept_id;
IF v_tab.count > 0 THEN
FOR i IN v_tab.first..v_tab.last LOOP
dbms_output.put_line('번호: ' || v_tab(i).v_emp_id||' 급여: '||v_tab(i).v_sal||' 근무년수: ' || v_tab(i).v_year ||
' 부서이름: ' || v_tab(i).v_dept_name);
END LOOP;
ELSE
RAISE v_err;
END IF;
EXCEPTION
WHEN v_err THEN
dbms_output.put_line(dept_id || '부서는 존재하지 않습니다.');
END;
/[문제3] 급여를 계산하는 get_annual_comp 함수를 생성하세요.
SQL> SELECT employee_id,
(salary12) + (commission_pctsalary12) ann_sal,
nvl(salary,0)12 + nvl(commission_pct,0)nvl(salary,0)12 ann_sal2,
get_annual_comp(salary,commission_pct) ann_sal3
FROM employees;
EMPLOYEE_ID ANN_SAL ANN_SAL2 ANN_SAL3
100 288000 288000
101 204000 204000
102 204000 204000
103 108000 108000
104 72000 72000
105 57600 57600
106 57600 57600
107 50400 50400
108 144096 144096
109 108000 108000
110 98400 98400<풀이>
CREATE OR REPLACE FUNCTION get_annual_comp(
sal IN number,
comm IN number)
RETURN number
IS
BEGIN
IF sal IS NOT NULL and comm IS NOT NULL THEN
RETURN sal*12 + comm*sal*12;
ELSIF sal IS NULL and comm IS NOT NULL THEN
RETURN 0;
ELSIF sal IS NOT NULL and comm IS NULL THEN
RETURN sal*12;
ELSE
RETURN 0;
END IF;
END;
/[문제4] 사원들의 급여를 10% 인상하는 패키지 프로그램을 생성해주세요.
declare
v_num emp_pkg.numlist := emp_pkg.numlist(100,103,107,110,112,115,160,170,180,190,200);
begin
emp_pkg.update_sal(v_num);
rollback;
end;
/
사원번호 : 100 사원이름 : King 이전 급여 : 24000 수정 급여 : 26400
사원번호 : 103 사원이름 : Hunold 이전 급여 : 9000 수정 급여 : 9900
사원번호 : 107 사원이름 : Lorentz 이전 급여 : 4200 수정 급여 : 4620
사원번호 : 110 사원이름 : Chen 이전 급여 : 8200 수정 급여 : 9020
사원번호 : 112 사원이름 : Urman 이전 급여 : 7800 수정 급여 : 8580
사원번호 : 115 사원이름 : Khoo 이전 급여 : 3100 수정 급여 : 3410
사원번호 : 160 사원이름 : Doran 이전 급여 : 7500 수정 급여 : 8250
사원번호 : 170 사원이름 : Fox 이전 급여 : 9600 수정 급여 : 10560
사원번호 : 180 사원이름 : Taylor 이전 급여 : 3200 수정 급여 : 3520
사원번호 : 190 사원이름 : Gates 이전 급여 : 2900 수정 급여 : 3190
사원번호 : 200 사원이름 : Whalen 이전 급여 : 4400 수정 급여 : 4840
<풀이>
CREATE OR REPLACE PACKAGE emp_pkg
IS
TYPE numlist IS TABLE OF number;
PROCEDURE update_sal (
id_tab IN numlist);
END emp_pkg;
/
CREATE OR REPLACE PACKAGE BODY emp_pkg
IS
PROCEDURE update_sal (
id_tab IN numlist)
IS
TYPE v_rec_type IS RECORD(
v_id number,
v_name varchar2(30),
after_sal number);
TYPE v_tab_type IS TABLE OF v_rec_type;
v_tab v_tab_type;
TYPE v_tab_type2 IS TABLE OF number INDEX BY pls_integer;
v_tab_2 v_tab_type2;
BEGIN
FOR i IN id_tab.first..id_tab.last LOOP
SELECT salary
INTO v_tab_2(i)
FROM hr.employees
WHERE employee_id = id_tab(i);
END LOOP;
FORALL i IN id_tab.first..id_tab.last
UPDATE hr.employees
SET salary = salary * 1.1
WHERE employee_id = id_tab(i)
RETURNING employee_id, last_name, salary BULK COLLECT INTO v_tab;
FOR i IN v_tab.first..v_tab.last LOOP
dbms_output.put_line('사원번호 : ' || v_tab(i).v_id || ' 사원이름 : ' || v_tab(i).v_name ||
' 이전 급여 : ' || v_tab_2(i) ||' 수정 급여 : ' || v_tab(i).after_sal);
END LOOP;
END update_sal;
END emp_pkg;
/[문제5] 사원들의 급여를 수정할 때 그 사원의 job_id 별 최저 임금에서 최고 임금 사이에 급여값으로만 수정하도록 하는 트리거를 이용해서 체크 하는 프로그램을 작성하세요.
SQL> select * from jobs;
JOB_ID JOB_TITLE MIN_SALARY MAX_SALARY
AD_PRES President 20080 40000
AD_VP Administration Vice President 15000 30000
AD_ASST Administration Assistant 3000 6000
FI_MGR Finance Manager 8200 16000
FI_ACCOUNT Accountant 4200 9000
AC_MGR Accounting Manager 8200 16000
AC_ACCOUNT Public Accountant 4200 9000
SA_MAN Sales Manager 10000 20080
SA_REP Sales Representative 6000 12008
PU_MAN Purchasing Manager 8000 15000
PU_CLERK Purchasing Clerk 2500 5500
ST_MAN Stock Manager 5500 8500
ST_CLERK Stock Clerk 2008 5000
SH_CLERK Shipping Clerk 2500 5500
IT_PROG Programmer 4000 10000
MK_MAN Marketing Manager 9000 15000
MK_REP Marketing Representative 4000 9000
HR_REP Human Resources Representative 4000 9000
PR_REP Public Relations Representative 4500 10500
19 rows selected.
SQL> update employees
set salary = 3000
where employee_id = 115;
1 row updated.
SQL> rollback;
Rollback complete.
SQL> update employees
set salary = 6000
where employee_id = 115;
update employees
*
ERROR at line 1:
ORA-20100: Invalid salary $6000. Salaries for job PU_CLERK must be between $2500 and $5500
ORA-06512: at "HR.CHECK_SALARY", line 10
ORA-06512: at "HR.CHECK_SALARY_TRG", line 2
ORA-04088: error during execution of trigger 'HR.CHECK_SALARY_TRG'
SQL> insert into employees(employee_id, last_name, email, hire_date, job_id, salary)
values(300, 'happy','happy',sysdate,'PU_CLERK',5000);
1 row created.
SQL> rollback;
Rollback complete.
SQL> insert into employees(employee_id, last_name, email, hire_date, job_id, salary)
values(300, 'happy','happy',sysdate,'PU_CLERK',6000);
insert into employees(employee_id, last_name, email, hire_date, job_id, salary)
*
ERROR at line 1:
ORA-20100: Invalid salary $6000. Salaries for job PU_CLERK must be between $2500 and $5500
ORA-06512: at "HR.CHECK_SALARY", line 10
ORA-06512: at "HR.CHECK_SALARY_TRG", line 2
ORA-04088: error during execution of trigger 'HR.CHECK_SALARY_TRG'
<풀이>
CREATE OR REPLACE TRIGGER emp_trigger
AFTER
INSERT OR UPDATE OF salary ON hr.employees
FOR EACH ROW
DECLARE
min_sal number;
max_sal number;
BEGIN
SELECT min_salary,max_salary
INTO min_sal, max_sal
FROM hr.jobs
WHERE job_id = :new.job_id;
IF UPDATING or INSERTING THEN
IF NOT :new.salary BETWEEN min_sal and max_sal THEN
RAISE_APPLICATION_ERROR(-20000,'Invalid salary $'||:new.salary||'. Salaries for job '||:new.job_id||' must be between $'||min_sal||' and $'||max_sal);
END IF;
END IF;
END emp_trigger;
/- 트리거는 작성시 제한 용량이 있다. 그러한 오류를 해결하고자 트리거안에 프로시저를 수행하게 하면 문제를 해결할 수 있다.
<트리거 안에서 프로시저 수행>
CREATE OR REPLACE TRIGGER CHECK_SALARY_TRG
AFTER
INSERT OR UPDATE OF salary ON hr.employees
FOR EACH ROW
BEGIN
check_salary(:new.job_id, :new.salary); --트리거 수행
END CHECK_SALARY_TRG;
/
CREATE OR REPLACE PROCEDURE check_salary(
p_job IN varchar2,
p_sal IN number)
IS
min_sal number;
max_sal number;
BEGIN
SELECT min_salary,max_salary
INTO min_sal, max_sal
FROM hr.jobs
WHERE job_id = UPPER(p_job);
IF p_sal NOT BETWEEN min_sal and max_sal THEN
RAISE_APPLICATION_ERROR(-20100,'Invalid salary $'||p_sal||'. Salaries for job '||p_job||' must be between $'||min_sal||' and $'||max_sal);
END IF;
END check_salary;
