[Oracle] 커서(Cursor) 예제들

예제 1

요구사항: 부서 이름을 입력하여 해당 부서에 속한 사원의 정보(사원 아이디, 사원 이름)를 얻습니다. 부서 이름을 입력할 떄 대소문자의 구분이 필요하지 않습니다.

 

EXEC emp_info('it')


SET SERVEROUTPUT ON

CREATE OR REPLACE PROCEDURE emp_info ( p_dept departments.department_name%TYPE)
IS
CURSOR emp_cur IS

        SELECT employee_id, last_name
        FROM employees e JOIN departments d
        ON d.department_id = e.department_id
        WHERE department_name LIKE UPPER(p_dept);

l_emp_id employees.employee_id%TYPE;
l_emp_name employees.last_name%TYPE;

BEGIN

       OPEN emp_cur;

          LOOP

                    FETCH emp_cur INTO l_emp_id, l_emp_name;
                    EXIT WHEN emp_cur%NOTFOUND;
                    DBMS_OUTPUT.PUT_LINE(l_emp_id || '' || l_emp_name);

          END LOOP;

       CLOSE emp_cur;

END emp_info;

 

 

예제 2

요구사항: 부서 이름을 입력받으면 해당 부서의 사원에 대해 급여가 많은 순으로 정보를 제공한다.

부서의 전체 인원에 대한 정보를 함께 제시한다.

 

EXEC emp_salary_info('IT')

CREATE OR REPLACE PROCEDURE emp_salary_info (p_dept departments.department_name%TYPE)
IS
CURSOR emp_cur IS

             SELECT employee_id, last_name, salary
             FROM employees e JOIN departments d
             ON d.department_id = e.department_id
             WHERE department_name LIKE UPPER(p_dept)
             ORDER BY salary DESC;

l_emp employees%ROWTYPE;

BEGIN

           OPEN emp_cur;

                      LOOP
                      FETCH emp_cur INTO l_emp.employee_id, l_emp.last_name, l_emp.salary;
                      EXIT WHEN emp_cur%NOTFOUND;
                      
                      DBMS_OUTPUT.PUT_LINE(l_emp.employee_id || ', ' || l_emp.last_name || ', ' || l_emp-.salary);

                      END LOOP;

           DBMS_OUTPUT.PUT_LINE('Total Employee Number of' || p_dept || 'department : ' || emp_cur%ROWCOUNT);



           CLOSE emp_cur;

END emp_salary_info;

 

예제3

요구사항 : 특정 숫자를 입력하면 부서에 소속한 사원의 수가 입력된 숫자 이상일 때 해당 부서와 소속 사원의 전체 수를 제시

 

EXEC count_dept(10)


CREATE OR REPLACE PROCEDURE count_dept ( p_num IN NUMBER)
IS

        CURSOR c1 IS
        SELECT t1.department_id, department_name, staff
        FROM departments t1,
       (
                   SELECT department_id, COUNT(*) as staff
                   FROM employees
                  GROUP BY department_id
      )t2

      WHERE t1.department_id = t2.department_id
      AND staff >= p_num;

BEGIN

      FOR dept IN c1
      LOOP

                dbms_output.put_line(dept.department_name || 'Department has '|| dept.staff|| ' staff');
     END LOOP;

END count_dept;