#13_140701_PL_SQL_REF CURSOR

# REF CURSOR

l ref cursor : 동적 SQL에서 커서를 사용하려면 ref cursor를 사용해야한다.

l 일반 커서와 ref cursor 의 차이점

- 우리가 알고있는 커서선언은 declare 절에서 수행하는데 ref cursor 는 커서선언이 실행절(begin)에서 이루어진다

l 일반커서

- 수행 순서 : cursor 선언 >> cursor open >> cursor에 있는 내용 fetch >> cursor close

- 일반적인 커서 선언방법

cursor emp_cursor is

select empno,ename,sal

from emp;

- 아래의 출력결과를 emp_cursor 라는 메모리에 올려놓고 cursor를 메모리에 오픈하고 하나씩 하나씩 fetch 함

- for loop 문은 open, fetch, close가 생략됨

문제158. 위의 프로시져를 수정해서 직업을 입력하고 프로시져를 수행하면 해당 사원들의 이름과 직업과 부서번호가 아래와 같이 출력되게 하시오. 그런데 SALESMAN 을 넣고 실행하면 월급이 1500이상인 사원들만 출력될 수 있도록 하시오.

“SCOTT 의 직업은 ANALYST 이고 부서번호가 20번입니다.”

create or replace procedure  list_emp

(p_job varchar2) 

is

type  emp_refcsr  is  ref  cursor;

 emp_cv  emp_refcsr;

 emprec  emp%rowtype;

 stmt  varchar2(200)  ;

begin

 if  p_job = 'SALESMAN' then

  stmt := ' select * from emp  where sal>= 1500 and job= :id';

  open  emp_cv  for stmt using  p_job;

 else

  stmt := ' select * from emp where job = :id';

  open  emp_cv  for stmt using  p_job;

 end  if;

 loop

  fetch  emp_cv  into  emprec;

  exit  when  emp_cv%notfound;

    dbms_output.put_line(emprec.ename || '의 직업은 ' ||  emprec.job ||

  '이고 부서번호가 ' || emprec.deptno || '입니다');

 end  loop;

close  emp_cv;

end;

/

문제196. 위의 결과를 SQL로 구현하시오. 부서번호별 직업별 토탈월급을 출력하시오

CREATE OR REPLACE procedure dynamic_pivot

(p_cursor in out sys_refcursor)

AS

sql_query varchar2(1000) := 'select deptno  ';

begin

 for x in (select distinct job from emp order by 1) loop     

  sql_query := sql_query ||' , sum(decode( job , '''||x.job||''' , sal ) ) as '||x.job;

  dbms_output.put_line(sql_query);

 end loop;

 sql_query := sql_query || ' from emp  group by deptno ';

 open p_cursor for sql_query;

end;

/

variable x refcursor

exec dynamic_pivot(:x)

print x

l x : 레코드 이름

l for 구문에서 in에 커서이름 대신에 select 문장을 직접넣어줘도 됨

l order by 1 : A B C D 순서로 나타내기 위함

l variable x refcursor : refcusrsor 바인드 변수 선언

l exec dynamic_pivot(:x) : x앞에 콜론은 바인드 문법