#10_140626_PL_SQL_CURSOR

# CURSOR

l 커서 : 수행하는 SQL 문의 결과를 처리하기 위한 메모리 영역
- 암시적 커서 : SQL%rowcount. 메모리에 미리 올려놓고 사용자가 데이터를 업데이트하면 알아서 바꿔줌
  명시적 커서 : 커서선언 -> 커서오픈 -> 커서패치 커서 close

문제61. 직업을 물어보게 하고 직업을 입력하면 해당 사원의 사원이름, 월급과 부서위치가 출력되게하시오

set serveroutput on

accept p_job prompt '직업를 입력하시오! '

DECLARE

 CURSOR emp_cursor IS

  SELECT e.ename, e.sal, d.loc

  FROM EMP e, DEPT d

  WHERE e.job = UPPER('&p_job') AND e.deptno = d.deptno;

  v_ename emp.ename%TYPE;

  v_sal emp.sal%TYPE;

  v_loc dept.loc%TYPE;

BEGIN

 OPEN emp_cursor;

  LOOP

   FETCH emp_cursor INTO v_ename, v_sal, v_loc;

   EXIT WHEN emp_cursor%NOTFOUND;

   dbms_output.put_line(v_ename || '  ' || v_sal || '  ' || v_loc);

  END LOOP;

 CLOSE emp_cursor;

END;

/

문제62. 위의 예제를 basic loop 를 while loop 로 수행하시오

(emp_cursor%found : 커서에서 데이터가 발견 true, emp_cursor%notfound : 커서에서 데이터가 발견안되면 true)

set serveroutput on

accept p_job prompt '직업를 입력하시오! '

DECLARE

 CURSOR emp_cursor IS

  SELECT e.ename, e.sal, d.loc

  FROM EMP e, DEPT d

  WHERE e.job = UPPER('&p_job') AND e.deptno = d.deptno;

  v_ename emp.ename%TYPE;

  v_sal emp.sal%TYPE;

  v_loc dept.loc%TYPE;

BEGIN

 OPEN emp_cursor;

  FETCH emp_cursor INTO v_ename, v_sal, v_loc;

  WHILE emp_cursor%FOUND LOOP

   dbms_output.put_line(v_ename || '  ' || v_sal || '  ' || v_loc);

   FETCH emp_cursor INTO v_ename, v_sal, v_loc;

  END LOOP;

 CLOSE emp_cursor;

END;

/

l fetch 위아래로 2번 써줘야 하고 while 구문안에서 dbms와 fetch의 순서가 맞아야 한다.

문제64. 부서위치를 물어보게 하고 부서위치를 입력하면 해당 부서위치에 속한 사원들의 이름과 월급과 직업을 출력하는 PL/SQL을 작성하는데 for loop 문을 이용한 cursor 문으로 작성하시오

set serveroutput on

accept p_loc prompt '부서위치를 입력하시오! '

DECLARE

 CURSOR emp_dept_cursor IS

  SELECT e.ename, e.sal, e.job

  FROM EMP e, DEPT d

  WHERE d.loc = UPPER('&p_loc') AND e.deptno = d.deptno;

BEGIN

 FOR emp_record IN emp_dept_cursor loop

  dbms_output.put_line(emp_record.ename || '  ' || emp_record.sal || '  ' || emp_record.job);

 END LOOP;

END;

/

문제65. 위의 예제를 수정해서 아래와 같이 결과가 출력되게 하시오. 만약에 월급이 3000 이상이면“고소득자입니다.”이라고 출력하고 3000 보다 작으면 “저소득자입니다”라고 출력하시오

set serveroutput on

accept p_loc prompt '부서위치를 입력하시오! '

DECLARE

 CURSOR emp_dept_cursor IS

  SELECT e.ename, e.sal, e.job

  FROM EMP e, DEPT d

  WHERE d.loc = UPPER('&p_loc') AND e.deptno = d.deptno;

BEGIN

 FOR emp_record IN emp_dept_cursor LOOP

  IF emp_record.sal >= 3000 THEN

  dbms_output.put_line(emp_record.ename || ' 은 고소득자입니다');

  ELSE

  dbms_output.put_line(emp_record.ename || ' 은 저소득자입니다');

  END IF;

 END LOOP;

END;

/

문제66. 사원 테이블에 income이란 컬럼을 문자형으로 추가하고 값을 갱신하는 PL/SQL을 작성하는데 자기의 월급이 자기가 속한 부서번호의 평균월급보다 크면 “고소득자”로 갱신되게 하고 자기의 월급이 자기가 속한 부서번호의 평균월급보다 작으면 “저소득자”로 갱신되게 하시오

set serveroutput on

DECLARE
 CURSOR emp_dept_cursor IS
  SELECT ename, sal, deptno, AVG(sal) OVER(PARTITION BY deptno) avgsal
  FROM EMP;

BEGIN
 FOR emp_record IN emp_dept_cursor LOOP
  IF emp_record.sal >= emp_record.avgsal THEN
   UPDATE EMP
   SET income = '고소득자'
   WHERE ename = emp_record.ename;
  ELSE
   UPDATE EMP
   SET income = '저소득자'
   WHERE ename = emp_record.ename;
  END IF;
 END LOOP;
 dbms_output.put_line (SQL%ROWCOUNT || ' 명이 갱신되었다');
END;
/

문제71. 위의 code 를 수정해서 추가한 rnk의 모든 데이터가 해당 사원의 순위로 모두 갱신되게 하시오

declare

cursor emp_cursor is

select ename, rank() over(order by sal desc) as "순위"

  from emp;

begin

for emp_record in emp_cursor loop

   update emp

   set rnk=emp_record.순위

   where ename=emp_record.ename;

end loop;

end;

/

조류인플루엔자 insert text파일

문제80. 조류인플루엔자에 해당하는 조류가 무엇인지 중복제거 출력하시오

declare

cursor emp_cursor is

select DISTINCT dise_object

 from emp599;

begin

 for emp_record in emp_cursor loop

  dbms_output.put_line(emp_record.dise_object);

 end loop;

end;

/

문제81. 조류인플루엔자로 가장 큰 피해를 입은 농장의 주소를 출력하시오(가장 dise_num 이 높은 것)

declare

cursor emp_cursor is

 SELECT *

 FROM (select address, RANK() OVER(ORDER BY dise_num desc) 순위

 from emp599)

 WHERE 순위=1;

begin

 for emp_record in emp_cursor loop

  dbms_output.put_line(emp_record.address);

 end loop;

end;

/

문제83. 순위를 물어보게 하고 순위를 입력하면 농장주소가 출력되게 하시오

accept p_rank prompt '순위을 입력하시오 '

DECLARE

 v_rank number(10) := &p_rank;

 v_add  emp599.address%TYPE;

BEGIN

 select address INTO v_add

 FROM (select address, RANK() OVER(ORDER BY dise_num desc) 순위

 from emp599)

 WHERE 순위=v_rank;

 dbms_output.put_line(v_add);

end;

/