배열처럼 사용되는 table 타입 변수

   ----- ==== **** 오라클에서는 배열이 없지만 배열처럼 사용되어지는 table 타입 변수가 있습니다. **** ===== -----
   --              그래서 table 타입 변수를 사용하여 자바의 배열처럼 사용합니다. -- 
   

 

---

추출할 행이 1개 이상이면 오류 발생

   create or replace procedure pcd_employees_info_deptid
   ( p_department_id IN employees.department_id%type )
   is
       v_department_id     employees.department_id%type;
       v_employee_id       employees.employee_id%type;
       v_department_name   departments.department_name%type;
       v_ename             varchar2(40);
       v_hire_date         employees.hire_date%type;
       v_gender            varchar2(6);
       v_age               number(3);
   begin
       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, E.employee_id, D.department_name, ename, e.hire_date, e.gender, e.age
              INTO
              v_department_id, v_employee_id, v_department_name, v_ename, v_hire_date, v_gender, v_age
       from E left join D
       on e.department_id = d.department_id;
       
       dbms_output.put_line( lpad('-',55,'-') );
       dbms_output.put_line( ' 부서번호 부서명     사원번호       사원명        입사일자    성별  나이' );
       dbms_output.put_line( lpad('-',55,'-') );
          
       dbms_output.put_line( '  ' || v_department_id || '  ' || v_department_name || v_employee_id || '     ' || '    ' || v_ename || '  ' || v_hire_date || '    ' || v_gender || '   ' || v_age );
       
       EXCEPTION 
            WHEN no_data_found THEN    -- no_data_found 은 오라클에서 데이터가 존재하지 않을 경우 발생하는 오류임.
                 dbms_output.put_line('>> 부서번호 ' || p_department_id || ' 은 존재하지 않습니다. <<');
   end pcd_employees_info_deptid;
   
   -- Procedure PCD_EMPLOYEES_INFO_DEPTID이(가) 컴파일되었습니다.

[실행] - 오류 발생

   exec pcd_employees_info_deptid(10);
   
   exec pcd_employees_info_deptid(30);
   -- ORA-01422: 실제 인출은 요구된 것보다 많은 수의 행을 추출합니다
   -- 30번 부서에 근무하는 직원이 1명 이상이기 때문에 오류 발생

 

---

 

   -- [문제 해결]
   -- table 타입 변수를 사용하면 해결된다.
   -- 현업에선 이런거 안씀 (커서를 씀)

 

   create or replace procedure pcd_employees_info_deptid
   ( p_department_id IN employees.department_id%type )
   is
       type department_id_Type
       is table of employees.department_id%type index by binary_integer;
       
       type department_name_Type
       is table of departments.department_name%type index by binary_integer;
       
       type employee_id_Type
       is table of employees.employee_id%type index by binary_integer;
       
       type ename_Type
       is table of varchar2(30) index by binary_integer;
       
       type hire_date_Type
       is table of varchar2(10) index by binary_integer;
       -- date 가 아니라 to_char 로 타입을 바꿀것임
       
       type gender_Type
       is table of varchar2(6) index by binary_integer;
       
       type age_Type
       is table of varchar2(3) index by binary_integer;
       
       -- v_tab_ 은 테이블 타입이란 뜻
       v_tab_department_id     department_id_Type;
       v_tab_department_name   department_name_Type;
       v_tab_employee_id       employee_id_Type;
       v_tab_ename             ename_Type;
       v_tab_hire_date         hire_date_Type;
       v_tab_gender            gender_Type;
       v_tab_age               age_Type;
       
       i binary_integer := 0;  -- i 는 배열의 방번호 용도처럼 사용된다.
                               -- 그런데 자바에서 배열의 시작은 0 부터인 반면,
                               -- 오라클은 1 부터 시작된다.
       
   begin
         --for 다음에 나오는 변수는 is 에서 선언하지 않아도 된다.
       FOR v_rcd IN (   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, E.employee_id, D.department_name, 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  ) LOOP
           
           i := i+1;
           
           v_tab_department_id(i)   := v_rcd.department_id;
           v_tab_department_name(i) := v_rcd.department_name;
           v_tab_employee_id(i)     := v_rcd.employee_id;
           v_tab_ename(i)           := v_rcd.ename;
           v_tab_hire_date(i)       := v_rcd.hire_date;
           v_tab_gender(i)          := v_rcd.gender;
           v_tab_age(i)             := v_rcd.age;
           
       
       END LOOP;
       
       if (i = 0) then 
          raise no_data_found;
       else   
           dbms_output.put_line( lpad('-',55,'-') );
           dbms_output.put_line( ' 부서번호 부서명     사원번호       사원명        입사일자    성별  나이' );
           dbms_output.put_line( lpad('-',55,'-') );
           
           FOR k IN 1..i LOOP   
                dbms_output.put_line( v_tab_department_id(k) || '  ' || 
                                      v_tab_department_name(k) || '  ' || 
                                      v_tab_employee_id(k) || '     ' || 
                                      v_tab_ename(k) || '  ' || 
                                      v_tab_hire_date(k) || '    ' || 
                                      v_tab_gender(k) || '   ' || 
                                      v_tab_age(k) );
           END LOOP;
      end if;     
      
      EXCEPTION  
          WHEN no_data_found THEN -- no_data_found 은 오라클에서 데이터가 존재하지 않을 경우 발생하는 오류이다.
               dbms_output.put_line( '>> 부서번호 ' || p_department_id || '은 존재하지 않습니다. <<' );
               
   end pcd_employees_info_deptid;
   
   -- Procedure PCD_EMPLOYEES_INFO_DEPTID이(가) 컴파일되었습니다.

[실행]

   exec pcd_employees_info_deptid(10);
   
   exec pcd_employees_info_deptid(30);
   
   exec pcd_employees_info_deptid(2342);