Oracle Procedure (배열)

--- ○ 배 열 (테이블 타입형 변수 - oracle에서)
--|| 배열을 쓰기위해서는 출력값이 저장될 변수를 테이블 타입형 변수로 정의를 해주어야 한다.
--|| 반복문 안에 들어갈 쿼리문을 미리 view로 생성해주면 좋다.
create or replace view v_emppay
as
select empno, ename, job, coalesce(sal*12+comm, sal*12, 0) "YEARPAY"
from EMP;

create or replace procedure p_empinfo2
(v_jik in varchar2)
is
  type empnotbltype is table of emp.empno%type index by binary_integer;
  -- type 테이블타입형변수명 is table of 데이타타입지정
  -- index by binary_integer는 쉽게 말해 변수에 들어갈 값들의 방번호를 의미한다.
  type enametbltype is table of emp.ename%type index by binary_integer;
  type jobtbltype is table of emp.job%type index by binary_integer;
  type yearpaytbltype is table of number index by binary_integer;
 
  v_empno empnotbltype;
  v_ename enametbltype;
  v_job jobtbltype;
  v_yearpay yearpaytbltype;
 
  i binary_integer := 0; -- 방번호로 사용할 변수지정 및 시작값 지정
begin
  for result in(select * from v_emppay where upper(job) = upper(v_jik)) loop
     -- for 변수 in다음에는 쿼리문을 넣을수 있고 이것은 해당 쿼리문의 결과값 row가 없으면
  -- for 문을 빠져나오게 된다.
  -- result(for문에 사용되는 변수)에 저장되는 값은 수식이 들어올수 없으므로
  -- 쿼리문을 넣을때는 항시 alias 처리를 해주어야 한다.
  i := i + 1;  --- 오라클에서 방시작은 1부터시작되기때문에 1로 맞추어 주어야 한다.
  v_empno(i) := result.empno;
  v_ename(i) := result.ename;
  v_job(i) := result.job;
  v_yearpay(i) := result.yearpay;
  -- 테이블타입형 변수에 값이 들어가기위해서는 방번호를 입력해야하는데
  -- 방번호 또한 변수가 되기때문에 위에서 방번호로 지정해준 binary_integ로
  -- 지정해주고 테이블타입형 변수()안에 넣어주면 된다.
     end loop;
    
     dbms_output.put_line('-------------------------------');
     dbms_output.put_line('사원번호   사원명   직종   연봉');
     dbms_output.put_line('-------------------------------');
    
     for s in 1..i loop
       dbms_output.put_line(v_empno(s) || '  ' || v_ename(s) || '  ' ||
              v_job(s)   || '  ' || v_yearpay(s));
     end loop;
end;
/
/* exec p_empinfo2('salesman') */
/* 이러한 프로시져를 사용하는것은 오라클서버에 부하를 좀더 줄이기 위해 사용한다. */
----------------------------------------------------------------------------------------
-----|| 연습문제 : exec p_empinfo3(부서명); 을 입력하면
--      부서번호  사원명  직급  연봉  이 출력되는 프로시져를 생성하시오.
create or replace view v_empjoin
as
select E.deptno, ename, job, coalesce(sal*12+comm, sal*12, 0) "YEARPAY"
from EMP E join dept D
on E.deptno = D.deptno;

select * from v_empjoin;
drop view v_empjoin;

create or replace procedure p_empinfo3
(v_buname in varchar2)
is
  type deptnotbltype is table of emp.deptno%type index by binary_integer;
  type enametbltype is table of emp.ename%type index by binary_integer;
  type jobtbltype is table of emp.job%type index by binary_integer;
  type yearpaytbltype is table of number index by binary_integer;
 
  v_deptno deptnotbltype;
  v_ename enametbltype;
  v_job jobtbltype;
  v_yearpay yearpaytbltype;
 
  i binary_integer := 0;
begin
  for result in(select * from v_empjoin
         where deptno = (select distinct D.deptno from emp E, dept D
                      where E.deptno = D.deptno and upper(D.dname) = upper(v_buname))) loop
   i := i + 1;
  v_deptno(i) := result.deptno;
  v_ename(i) := result.ename;
  v_job(i) := result.job;
  v_yearpay(i) := result.yearpay;
  end loop;
 
  dbms_output.put_line('-------------------------------');
     dbms_output.put_line('부서번호   사원명   직급   연봉');
     dbms_output.put_line('-------------------------------');

     for s in 1..i loop
       dbms_output.put_line(v_deptno(s) || '  ' || v_ename(s) || '  ' ||
              v_job(s)    || '  ' || v_yearpay(s));
     end loop; 
end;
/
/* exec p_empinfo3('sales') */
---↓ 선생님 풀이 ↓---
create or replace view v_empdname
as
select D.dname, D.deptno || '   ' ||
                rpad(E.ename,10) || ' ' ||
                rpad(E.job,10) || ' ' ||
                coalesce(E.sal*12+E.comm, E.sal*12, 0) sainfo
from emp E join dept D
on E.deptno=D.deptno;
    
select * from v_empdname;

create or replace procedure p_empinfo3
(v_dname in v_empdname.dname%type)
is
   type empinfotype
   is table of v_empdname.sainfo%type
   index by binary_integer;
  
   i binary_integer := 0;

   v_empinfo empinfotype;
begin

 for result in(select sainfo from v_empdname
               where upper(dname) = upper(v_dname)) loop
     i := i + 1;                  
     v_empinfo(i) := result.sainfo;                 
 end loop;                  

dbms_output.put_line('----------------------------');
dbms_output.put_line('부서번호 사원명  직종  연봉');
dbms_output.put_line('----------------------------');

for s in 1..i loop
  dbms_output.put_line(v_empinfo(s));
end loop;

end;
/
----------------------------------------------------------------------------------------