231031 PL/SQL 복습 겸 문제

[문제] 2006년도에 입사한 사원들의 근무 도시이름별로 급여의 총액, 평균을 출력하세요. 

< 화면 출력 >

Southlake 도시에 근무하는 사원들의 총액 급여는 ￦13,800 이고 평균급여는 ￦6,900입니다.
South San Francisco 도시에 근무하는 사원들의 총액 급여는 ￦37,800 이고 평균급여는 ￦2,907입니다.
Seattle 도시에 근무하는 사원들의 총액 급여는 ￦7,800 이고 평균급여는 ￦7,800입니다.
Seattle 도시에 근무하는 사원들의 총액 급여는 ￦2,600 이고 평균급여는 ￦2,600입니다.
Oxford 도시에 근무하는 사원들의 총액 급여는 ￦59,100 이고 평균급여는 ￦8,442입니다.

 

 

1. 먼저, SELECT문으로 실행.

SELECT city, sum(sumsal) sumsal, avg(avgsal) avgsal
FROM ( SELECT l.city, e.sumsal, e.avgsal
       FROM 
            ( SELECT department_id, sum(salary) sumsal, trunc(avg(salary)) avgsal
              FROM hr.employees
              WHERE hire_date >= to_date('20060101', 'yyyymmdd')
              AND hire_date < to_date('20070101', 'yyyymmdd')
              GROUP BY department_id ) e , hr.departments d, hr.locations l
              WHERE e.department_id = d.department_id
              AND d.location_id = l.location_id)
GROUP BY city;

PL/SQL 실행

방법1)

DECLARE
    CURSOR city_aggr_cur IS 
        SELECT l.city, e.sumsal, e.avgsal
        FROM ( SELECT department_id, sum(salary) sumsal, trunc(avg(salary)) avgsal
               FROM hr.employees
               WHERE hire_date >= to_date('20060101', 'yyyymmdd')
               AND hire_date < to_date('20070101', 'yyyymmdd')
               GROUP BY department_id ) e , hr.departments d, hr.locations l
        WHERE e.department_id = d.department_id
        AND d.location_id = l.location_id ;
        
        v_rec city_aggr_cur%ROWTYPE;
BEGIN
    OPEN city_aggr_cur;
    
    LOOP
        FETCH city_aggr_cur INTO v_rec;
        EXIT WHEN city_aggr_cur%NOTFOUND;
        
        dbms_output.put_line(v_rec.city||' 도시에 근무하는 사원들의 총액 급여는 '||ltrim(to_char(v_rec.sumsal,'l999,999'))||' 이고 평균급여는 '||ltrim(to_char(v_rec.avgsal, 'l999,999'))||'입니다.');
    END LOOP;
    
    CLOSE city_aggr_cur;
END;
/

 

방법2)

DECLARE
    CURSOR city_aggr_cur IS 
        SELECT l.city, e.sumsal, e.avgsal
        FROM ( SELECT department_id, sum(salary) sumsal, trunc(avg(salary)) avgsal
               FROM hr.employees
               WHERE hire_date >= to_date('20060101', 'yyyymmdd')
               AND hire_date < to_date('20070101', 'yyyymmdd')
               GROUP BY department_id ) e , hr.departments d, hr.locations l
        WHERE e.department_id = d.department_id
        AND d.location_id = l.location_id ;
        
BEGIN
    
    FOR v_rec IN city_aggr_cur LOOP 
        dbms_output.put_line(v_rec.city||' 도시에 근무하는 사원들의 총액 급여는 '||ltrim(to_char(v_rec.sumsal,'l999,999'))||' 이고 평균급여는 '||ltrim(to_char(v_rec.avgsal, 'l999,999'))||'입니다.');
    END LOOP;
    
END;
/

 

방법3) 

BEGIN
    
    FOR v_rec IN ( SELECT l.city, e.sumsal, e.avgsal
                    FROM ( SELECT department_id, sum(salary) sumsal, trunc(avg(salary)) avgsal
                           FROM hr.employees
                           WHERE hire_date >= to_date('20060101', 'yyyymmdd')
                           AND hire_date < to_date('20070101', 'yyyymmdd')
                           GROUP BY department_id ) e , hr.departments d, hr.locations l
                    WHERE e.department_id = d.department_id
                    AND d.location_id = l.location_id ) LOOP 
        dbms_output.put_line(v_rec.city||' 도시에 근무하는 사원들의 총액 급여는 '||ltrim(to_char(v_rec.sumsal,'l999,999'))||' 이고 평균급여는 '||ltrim(to_char(v_rec.avgsal, 'l999,999'))||'입니다.');
    END LOOP;
    
END;
/