[문제12]
부서코드를 입력값으로 받아서 부서 사원들의 급여의 총액과 부서 이름을 출력해주세요.
<화면출력>
Marketing 부서에 해당하는 사원의 총 급여는 19000
<풀이>
variable b_dept_id number
execute :b_dept_id := 10
DECLARE
v_department_name hr.departments.department_name%type;
v_sum_sal number;
BEGIN
SELECT
(SELECT sum(salary)
FROM hr.employees
WHERE department_id = a.department_id) as total_sal,
department_name
INTO v_sum_sal, v_department_name
FROM hr.departments a
WHERE department_id = :b_dept_id ;
dbms_output.put_line(v_department_name || ' 부서에 해당하는 사원의 총 급여는 ' || v_sum_sal);
END;
/암시적 커서에 대한 SQL 커서 속성
- SELECT INTO 절에 쓰는건 무의미 하다. SELECT INTO은 무조건 1행만 fetch를 해야 하기 때문에 실행되면 무조건 1건이 나오고 0건이 나오는건 실패했다는 뜻이다.
SQL%FOUND
가장 최근의 DML문에 의해 영향을 받은 행이 있으면 TRUE 없으면 FALSE로 평가되는 boolean 속성
BEGIN
UPDATE hr.mgr
SET sal = sal*1.1
WHERE id = 300;
IF sql%found THEN
dbms_output.put_line(sql%rowcount || '개의 행이 수정되었습니다');
ELSE
dbms_output.put_line('수정된 데이터가 없습니다.');
END IF;
ROLLBACK;
END;
/
SQL%NOTFOUND
가장 최근의 DML문에 의해 영향을 받은 행이 없으면 TRUE 있으면 FALSE로 평가되는 boolean 속성
[문제13]
사원들의 근속연수가 20년 이상이면 10% 인상급여로 수정하는 프로그램을 생성해주세요.
<화면 결과>
100 사원의 입사일은 2003-06-17 근속연수는 20년 입니다.
100 사원의 급여가 24000에서 26400으로 수정했습니다.
<풀이>
variable b_emp_id number
execute :b_emp_id := 100
DECLARE
v_sal number;
v_work_day number;
v_hire_day varchar2(30);
v_rev_sal number;
BEGIN
SELECT to_char(hire_date,'yyyy-mm-dd'), trunc(months_between(sysdate,hire_date)/12), salary
INTO v_hire_day , v_work_day, v_sal
FROM hr.emp
WHERE employee_id = :b_emp_id;
IF v_work_day >= 20 THEN
v_rev_sal := v_sal *1.1;
UPDATE hr.emp
SET salary = v_rev_sal
WHERE employee_id = :b_emp_id;
dbms_output.put_line(:b_emp_id || ' 사원의 입사일은 '||v_hire_day || '이고 근속연수는 '|| v_work_day || '년 입니다.');
dbms_output.put_line(:b_emp_id || ' 사원의 급여가 ' || v_sal || '에서 '|| v_rev_sal ||'으로 수정되었습니다.');
ELSE
dbms_output.put_line('근속연수가 20년 미만입니다.');
END IF;
END;
/MERGE를 PL/SQL로 구현
MERGE INTO hr.dw_emp d -- target table(insert,update,delete)
USING hr.oltp_emp o -- source table(데이터 추출,select)
ON(d.employee_id = o.employee_id) -- 조인조건
WHEN MATCHED THEN -- 키 값이 일치가 되면
UPDATE SET -- delete 후 update
d.salary = o.salary*1.1
DELETE WHERE -- delete 먼저 수행
o.flag ='d'
WHEN NOT MATCHED THEN -- 키 값이 일치 되지 않으면
INSERT(d.employee_id, d.last_name, d.salary, d.department_id)
VALUES(o.employee_id, o.last_name, O.salary, o.department_id);해당 MERGE 문을 PL/SQL로 구현하려고 한다.
- 키값이 존재하고 flag = 'd' 데이터 삭제
DELETE FROM hr.dw_emp d
WHERE exists (SELECT 1
FROM hr.oltp_emp
WHERE employee_id = d.employee_id
AND flag = 'd');- DELETE 후 남은 데이터 업데이트 하기
UPDATE hr.dw_emp d
SET salary = (select salary *1.1
FROM hr.oltp_emp
WHERE employee_id = d.employee_id);- 키값이 일치가 되지 않는 데이터에 대한 입력
INSERT INTO hr.dw_emp(employee_id, last_name, salary, department_id)
SELECT employee_id, last_name, salary, department_id
FROM hr.oltp_emp o
WHERE not exists (SELECT 1
FROM hr.dw_emp
WHERE employee_id = o.employee_id)
AND (flag <> 'd'
OR flag is null);- 위에 1~3단계를 익명 블록 구조로 합치기
BEGIN
DELETE FROM hr.dw_emp d
WHERE exists (SELECT 1
FROM hr.oltp_emp
WHERE employee_id = d.employee_id
AND flag = 'd');
dbms_output.put_line('DELETE에 영향을 받은 행수는 ' || sql%rowcount);
UPDATE hr.dw_emp d
SET salary = (select salary *1.1
FROM hr.oltp_emp
WHERE employee_id = d.employee_id);
dbms_output.put_line('UPDATE에 영향을 받은 행수는 ' || sql%rowcount);
INSERT INTO hr.dw_emp(employee_id, last_name, salary, department_id)
SELECT employee_id, last_name, salary, department_id
FROM hr.oltp_emp o
WHERE not exists (SELECT 1
FROM hr.dw_emp
WHERE employee_id = o.employee_id)
AND (flag <> 'd'
OR flag is null);
dbms_output.put_line('INSERT에 영향을 받은 행수는 ' || sql%rowcount);
END;
/
이건 엄청난 I/O를 유발하겠지만 내가 창의적으로 짜본 쿼리라 버리기 아까워 올려본다..
이렇게 짯던 이유는 한행 한행 비교하면서 같으면 IF TRUE 다르면 FALSE로 조건제어문을 주어 INSERT,UPDATE, DELETE를 하고 싶었던 목적이었다. 하지만 이건 무수히 많은 I/O가 생겨 DBA라면 절대 이렇게 작성해서는 안될 것 같다.
DECLARE
v_a_id number;
v_b_id number;
BEGIN
FOR i IN (select rownum, c.* from hr.oltp_emp c) LOOP
SELECT e.a_emp, e.b_emp
INTO v_a_id, v_b_id
FROM(
SELECT ROWNUM as rownumber,a.employee_id as a_emp, b.employee_id as b_emp
FROM hr.oltp_emp a
left join hr.dw_emp b on a.employee_id = b.employee_id
) e
where e.rownumber = i.rownum;
IF v_a_id = coalesce(v_b_id,1000) THEN
UPDATE hr.dw_emp d
SET salary = (select salary * 1.1
FROM hr.oltp_emp
WHERE employee_id = d.employee_id)
WHERE employee_id = v_b_id;
dbms_output.put_line(sql%rowcount||'개의 행이 업데이트 되었습니다');
DELETE FROM hr.dw_emp d
WHERE exists (SELECT 1
FROM hr.oltp_emp
WHERE employee_id = d.employee_id
AND flag = 'd')
AND d.employee_id = v_b_id;
dbms_output.put_line(sql%rowcount||'개의 행이 삭제 되었습니다');
ELSE
INSERT INTO hr.dw_emp(employee_id, last_name, salary, department_id)
SELECT employee_id, last_name, salary, department_id
FROM hr.oltp_emp o
WHERE not exists (SELECT 1
FROM hr.dw_emp
WHERE employee_id = o.employee_id)
AND (flag <> 'd'
OR flag is null)
AND o.employee_id = v_a_id;
dbms_output.put_line(sql%rowcount||'개의 행이 추가 되었습니다');
END IF;
END LOOP;
END;
/