Oracle Procedure (Cursor, for loop Cursor)

--- ○ Cursor
create or replace procedure p_empinfo4
(v_jik in emp.job%type)
is
  cursor cur_empinfo
  -- cursor 커서명 is 쿼리문(select문) : 쿼리문조건에 맞는 값을 커서명으로 정의한다.
  is
  select empno, ename, job, coalesce(sal*12+comm, sal*12, 0) "YEARPAY"
  from EMP
  where upper(job) = upper(v_jik);
 
  v_empno emp.empno%type;
  v_ename emp.ename%type;
  v_job emp.job%type;
  v_yearpay number;
begin
  dbms_output.put_line('----------------------------');
  dbms_output.put_line('사원번호 사원명  직종  연봉');
  dbms_output.put_line('----------------------------');
 
  open cur_empinfo;
     loop
       fetch cur_empinfo into v_empno, v_ename, v_job, v_yearpay;
       exit when cur_empinfo%notfound;
       dbms_output.put_line(v_empno || ' ' ||
          v_ename || ' ' ||
         v_job   || ' ' ||
         v_yearpay);   
     end loop;
  close cur_empinfo;
  -- open커서명으로 커서를 열고 close커서명으로 커서를 닫는다.
  -- fetch 커서명 into 변수는 한행한행의 값을 변수에 기억시킨다
  -- loop로 반복해서 행값을 계속 변수에 기억만 하면 무한 loop에 빠지니
  -- exit when 조건, 으로 반복문에서 빠져나올 조건을 만든다.
  -- exit when 커서명%notfound는 커서명으로 정의한것의 값이 행이 찾아지지 않는
  -- 즉, 행이 끝나면 반복문을 나오게 된다.
end;
/
----------------------------------------------------------------------------------------
--- ○ For loop Cursor
create or replace procedure p_empinfo5
(v_jik in emp.job%type)
is
  cursor cur_empinfo
  is
  select empno, ename, job, coalesce(sal*12+comm, sal*12, 0) "YEARPAY"
  from EMP
  where upper(job) = upper(v_jik);
 
begin
  for result in cur_empinfo loop
    dbms_output.put_line(result.empno || ' ' ||
              result.ename || ' ' ||
              result.job   || ' ' ||
              result.yearpay);
  end loop;
  -- for loop cursor문은 해당 커서가 행이 끝날때까지 반복되어진다.
  -- 그냥 cursor처럼 open,end,fetch 할 필요가 없다. (프로시져가 간단해진다.)
end;
/
----------------------------------------------------------------------------------------
select * from sawon;
select * from v_sung;
---|| 연습문제 : exec p_sawon('남'); 를 실행하면 ('여'를 치면 여자만)
-- 남자의 (사원번호 사원명 현재나이 입사일자 정년일자)가 나오게 프로시져를 만들어라.
create or replace view v_sung
as
select sano, case when substr(Y.minbun,7,1) in ('1','3') then '남'
             else '여' end sex,
       name, age, hiredate,
    add_months(to_char(sysdate,'yyyy') || '-02-05', 12*(60-age)) jung   
from (select sano, name, minbun,
     case when substr(minbun,7,1) in ('1','2')
       then extract(year from sysdate) - (to_number(substr(minbun,1,2)) + 1899)
          when substr(minbun,7,1) in ('3','4')
    then extract(year from sysdate) - (to_number(substr(minbun,1,2)) + 1999)
       end age,
             to_char(hiredate,'yyyy-mm-dd') hiredate
      from SAWON) Y;

create or replace procedure p_sex
(v_sung in varchar2)
is
  cursor cur_sexgubun
  is
  select sano, name, age, hiredate, jung
  from v_sung
  where sex = v_sung
begin
  dbms_output.put_line('----------------------------------');
  dbms_output.put_line('사원번호 사원명 나이 입사일 정년일');
  dbms_output.put_line('----------------------------------');
 
  for result in cur_sexgubun loop
  dbms_output.put_line(result.sano       || ' ' ||
          result.name       || ' ' ||
          result.age        || ' ' ||
          result.hiredate   || ' ' ||
          result.jung);
  end loop;
end;
/
exec p_sex('남')
----------------------------------------------------------------------------------------
--|| 위문제에서 '남', '여'가 아닌 0(남여모두), 1(남자), 2(여자) 이경우를 출력하여라
--  완 료 못 했 음 나 중 에 해 보 기 --
create or replace view v_sung
as
select sano, case when substr(Y.minbun,7,1) in ('1','3') then '남'
    else '여' end sex,
       name, age, hiredate,
    add_months(to_char(sysdate,'yyyy') || '-02-05', 12*(60-age)) jung   
from (select sano, name, minbun,
     case when substr(minbun,7,1) in ('1','2')
       then extract(year from sysdate) - (to_number(substr(minbun,1,2)) + 1899)
          when substr(minbun,7,1) in ('3','4')
    then extract(year from sysdate) - (to_number(substr(minbun,1,2)) + 1999)
       end age,
             to_char(hiredate,'yyyy-mm-dd') hiredate
      from SAWON) Y;

create or replace procedure p_sex
(v_sung in number)
is
  cursor cur_sexgubun
  is
  select sano, name, age, hiredate, jung
  from v_sung
  where sex = v_sung;
    
begin
  dbms_output.put_line('----------------------------------');
  dbms_output.put_line('사원번호 사원명 나이 입사일 정년일');
  dbms_output.put_line('----------------------------------');
 
  for result in cur_sexgubun loop
  dbms_output.put_line(result.sano       || ' ' ||
          result.name       || ' ' ||
          result.age        || ' ' ||
          result.hiredate   || ' ' ||
          result.jung);
  end loop;
end;
/
-------------------------------------------------------------------------------------------------
select *
from user_source;  -- 유저가 만든 피로시져, 함수등의 소스를 보여준다.
-------------------------------------------------------------------------------------------------