---- ===== **** CURSOR ( 중요 ) **** ===== -----

  --  PL/SQL 에서 SELECT 되어져 나오는 행의 개수가 2개 이상인 경우에는 위에서 한 것처럼
  --  table 타입의 변수를 사용하여 나타낼 수 있고, 또는 CURSOR 를 사용하여 나타낼 수도 있다. 
  --  table 타입의 변수를 사용하는 것 보다 CURSOR 를 사용하는 것이 더 편하므로 
  --  대부분 CURSOR 를 많이 사용한다.

---

   ----- ===== ***** 명시적 CURSOR 만들기 ***** ===== -----

     ※ 형식

      1.단계 -- CURSOR 의 선언(정의)
         
        CURSOR 커서명
        IS
        SELECT 문;  
    
      2.단계 -- CURSOR 의 OPEN
    
        OPEN 커서명;
    
      3.단계 -- CURSOR 의 FETCH
               (FETCH 란? SELECT 되어진 결과물을 끄집어 내는 작업을 말한다)
        
        FETCH  커서명 INTO 변수;
    
      4.단계 -- CURSOR 의 CLOSE
    
        CLOSE 커서명;

---

       ※ ==== 커서의 속성변수 ==== ※
    
     1. 커서명%ISOPEN   ==> 커서가 OPEN 되어진 상태인가를 체크하는 것.
                           만약에 커서가 OPEN 되어진 상태이라면 TRUE.
    
     2. 커서명%FOUND    ==> FETCH 된 레코드(행)이 있는지 체크하는 것.
                           만약에 FETCH 된 레코드(행)이 있으면 TRUE.
    
     3. 커서명%NOTFOUND ==> FETCH 된 레코드(행)이 없는지 체크하는 것.
                           만약에 FETCH 된 레코드(행)이 없으면 TRUE.
    
     4. 커서명%ROWCOUNT ==> 현재까지 FETCH 된 레코드(행)의 갯수를 반환해줌.

 

 

 

---

[기본 cursor 문]

   create or replace procedure pcd_employees_info_deptid_cursor
   ( p_department_id IN employees.department_id%type )
   is
   
    --  1단계 - CURSOR 의 선언(정의)
        CURSOR cur_empinfo
        IS
        with
        E as
        (
             select employee_id
                  , first_name || ' ' || last_name as ename
                  , hire_date
                  , func_gender(jubun) as gender
                  , func_age(jubun) as age
                  , department_id
             from employees
             where department_id = p_department_id
        )
        ,
        D as
        (
             select department_id, department_name
             from departments
        )
        select E.department_id, D.department_name, E.employee_id, e.ename, to_char( e.hire_date, 'yyyy-mm-dd') as hire_date , e.gender, e.age
        from E left join D
        on e.department_id = d.department_id;  
        
        v_department_id       employees.department_id%type; 
        v_department_name     departments.department_name%type; 
        v_employee_id         employees.employee_id%type; 
        v_ename               varchar2(30); 
        v_hiredate            varchar2(10); 
        v_gender              varchar2(6); 
        v_age                 varchar2(3);
        
        v_cnt                 number := 0;
   
   begin
        -- 2단계 - CURSOR 의 OPEN
        OPEN cur_empinfo;
        
        -- 3단계 - CURSOR 의 FETCH (FETCH : SELECT 되어진 결과물을 끄집어 내는 작업을 말한다)
        LOOP
            FETCH cur_empinfo INTO v_department_id, v_department_name, v_employee_id, v_ename, v_hiredate, v_gender, v_age;
            
            v_cnt := cur_empinfo%ROWCOUNT;
            
            EXIT WHEN cur_empinfo%NOTFOUND ;  -- 더이상 select 된 행이 없다면 반복문을 빠져나간다.
            
            if( cur_empinfo%ROWCOUNT = 1 ) then
                dbms_output.put_line( lpad('-',60,'-') );
                dbms_output.put_line( ' 부서번호 부서명     사원번호       사원명        입사일자    성별  나이' );
                dbms_output.put_line( lpad('-',60,'-') );
            end if;
           
           dbms_output.put_line( v_department_id || '  ' ||
                                 v_department_name || '  ' ||
                                 v_employee_id || '  ' || 
                                 v_ename || '  ' || 
                                 v_hiredate || '  ' || 
                                 v_gender || '  ' || 
                                 v_age );
            
        END LOOP;
        
        
        --  4. 커서명%ROWCOUNT ==> 현재까지 FETCH 된 레코드(행)의 갯수를 반환해줌.
        CLOSE cur_empinfo;
        
        if( v_cnt = 0 ) then
            dbms_output.put_line('>> 부서번호 ' || p_department_id || '은 존재하지 않습니다. <<');
        else 
            dbms_output.put_line(' ');
            dbms_output.put_line('>> 조회된 행의 개수: ' || v_cnt || '개 <<');
        end if;    
      
   end pcd_employees_info_deptid_cursor;   
   
   -- Procedure PCD_EMPLOYEES_INFO_DEPTID_CURSOR이(가) 컴파일되었습니다.

[실행]

   exec pcd_employees_info_deptid_cursor(10);
   
   exec pcd_employees_info_deptid_cursor(30); 
   
   exec pcd_employees_info_deptid_cursor(2342);

 

---

 

   ----- ===== ***** FOR LOOP CURSOR 만들기 ***** ===== -----

 

 

     FOR LOOP CURSOR 문을 사용하면
     커서의 OPEN, 커서의 FETCH, 커서의 CLOSE 가 자동적으로 발생되어지기 때문에
     우리는 커서의 OPEN, 커서의 FETCH, 커서의 CLOSE 문장을 기술할 필요가 없다.

 

      ※ 형식
      FOR 변수명(select 되어진 행의 정보가 담기는 변수) IN 커서명 LOOP
          실행문장;
      END LOOP;

 

---

   create or replace procedure pcd_employees_info_deptid_forcursor
   ( p_department_id IN employees.department_id%type )
   is
    --  1단계 - CURSOR 의 선언(정의)
        CURSOR cur_empinfo
        IS
        with
        E as
        (
             select employee_id
                  , first_name || ' ' || last_name as ename
                  , hire_date
                  , func_gender(jubun) as gender
                  , func_age(jubun) as age
                  , department_id
             from employees
             where department_id = p_department_id
        )
        ,
        D as
        (
             select department_id, department_name
             from departments
        )
        select E.department_id, D.department_name, E.employee_id, e.ename, to_char( e.hire_date, 'yyyy-mm-dd') as hire_date , e.gender, e.age
        from E left join D
        on e.department_id = d.department_id;  
        
        v_cnt                 number := 0;
   
   begin
        -- 2단계 - CURSOR 의 OPEN (필요없음)
        -- 3단계 - CURSOR 의 FETCH (FETCH : SELECT 되어진 결과물을 끄집어 내는 작업을 말한다) ( 필요없음 )
        --  4. 커서명%ROWCOUNT ==> 현재까지 FETCH 된 레코드(행)의 갯수를 반환해줌. 
        -- (close 삭제)
        
        /*
        -- for 2단계
        FOR 변수명(select 되어진 행의 정보가 담기는 변수) IN 커서명 LOOP
          실행문장;
        END LOOP;
        */
        FOR v_rcd IN cur_empinfo LOOP -- cur_empinfo 개수만큼 반복, v_rcd 에 행들이 쌓임
        
            v_cnt := cur_empinfo%ROWCOUNT;
            
           if( cur_empinfo%ROWCOUNT = 1 ) then
                dbms_output.put_line( lpad('-',60,'-') );
                dbms_output.put_line( ' 부서번호 부서명     사원번호       사원명        입사일자    성별  나이' );
                dbms_output.put_line( lpad('-',60,'-') );
            end if;
            
            dbms_output.put_line( v_rcd.department_id || ' ' || 
                                  v_rcd.department_name || ' ' || 
                                  v_rcd.employee_id || ' ' || 
                                  v_rcd.ename || ' ' || 
                                  v_rcd.hire_date || ' ' || 
                                  v_rcd.gender || ' ' || 
                                  v_rcd.age);
             
        END LOOP; -- 루프를 나가는 순간 자동적으로 커서는 close 됨
        
        
        if( v_cnt = 0 ) then
            dbms_output.put_line('>> 부서번호 ' || p_department_id || '은 존재하지 않습니다. <<');
        else 
            dbms_output.put_line(' ');
            dbms_output.put_line('>> 조회된 행의 개수: ' || v_cnt || '개 <<');
        end if;    
      
   end pcd_employees_info_deptid_forcursor;   
   
   -- Procedure PCD_EMPLOYEES_INFO_DEPTID_FORCURSOR이(가) 컴파일되었습니다.

[실행]

   exec pcd_employees_info_deptid_forcursor(10);
   
   exec pcd_employees_info_deptid_forcursor(30); 
   
   exec pcd_employees_info_deptid_forcursor(2342);