SQL PL/SQL 프로시저 5_ 커서와 커서활용 / 코드연구

CURSOR

커서는 SQL 문을 실행했을 때 실행 결과를 가리키는 데이터 구조이며 SQL문을 처리하는 정보를 저장한 메모리 공간을 말한다.
커서는 주로 PL/SQL 블록이나 저장 프로시저 내에서 SQL 쿼리의 결과 집합의 행을 참조하고 조작할 수 있는 메커니즘을 제공한다.

커서의 종류
  . 암시적(암시적) 커서
    - Oracle 엔진이 자동으로 생성하는 커서로 주로 단일 SQL 문을 실행할 때 사용한다.
  . 명시적 커서
    - 사용자가 직접 이름을 정의해서 만든 커서를 말한다.

커서의 속성
  암시적 커서의 속성
    - SQL%FOUND: 해당 SQL문에 의해 반환된 행수가 1개 이상이면 true를 리턴한다.
    - SQL%NOTFOUND: 해당 SQL문에 의해 반환된 행수가 더 이상 없을 경우 true를 리턴한다.
    - SQL%ISOPEN: 커서가 OPEN되어 있으면 true를 리턴한다.. (암시적 커서가 열려있는지에 대한 여부를 판단할 때 사용한다.)
    - SQL%ROWCOUNT: 커서가 열려 있는 동안 fetch된 행의 수를 반환한다.

  . 명시적 커서의 속성
    - 명시적 커서를 이용해서 사용자가 명시적으로 선언하고 제어할 때 사용한다. 
    - 이 경우에는 커서 이름 뒤에 커서명%FOUND, 커서명%NOTFOUND, 커서명%ROWCOUNT 으로 속성을 사용한다.
    - %FOUND: fetch한 데이터가 행을 반환하면 true를 리턴한다.
    - %NOTFOUND: fetch한 데이터가 행을 반환하지 않으면 true를 리턴한다. (loop를 끝낼 때 사용한다.)
    - %ISOPEN: 커서가 OPEN되어 있으면 true를 리턴한다.
    - %ROWCOUNT: 커서가 열려 있는 동안 fetch된 행의 수를 반환한다.

CURSOR생성

--커서--

--서버 on으로 열어주기--
set serveroutput on;
show serveroutput;

--커서 이용하지 않고 출력--
declare
v_empno emp.empno%type;
v_ename emp.ename%type;
v_job emp.job%type;
begin
select empno, ename, job into v_empno, v_ename, v_job from emp where ename='SMITH';
dbms_output.put_line('직원번호: '||v_empno);
dbms_output.put_line('직원이름: '||v_ename);
dbms_output.put_line('직원직업: '||v_job);
end;
/

커서를 이용하지 않고 익명함수로 직원의 정보를 추출한 결과이다. 이것과 같은 결과를 커서로 출력하려면

--커서 이용해서 출력하기--
declare
v_empno emp.empno%type;
v_ename emp.ename%type;
v_deptno emp.deptno%type;
cursor emp_cursor is
select empno, ename, deptno from emp where empno=7788;
begin
open emp_cursor;
fetch emp_cursor into v_empno, v_ename, v_deptno;
dbms_output.put_line('v_empno: '|| v_empno);
dbms_output.put_line('v_ename: '|| v_ename);
dbms_output.put_line('v_deptno: '|| v_deptno);
close emp_cursor;
end;
/

위와같은 방법으로 커서를 실행시켜 적어준다. 커서는 명시적 커서를 이용한다고 하더라도 create문을 이용하지 않고 익명변수를 생성할때와 비슷한 방법으로 커서를 실행시키는데, declare절에 커서문에서 사용할 변수와 커서를 정의해준다. 커서를 정의하는 방법은 cursor 커서명 is sql문의 형식으로 적어주며 begin절에서 커서를 열어주고(open)fect를 통해 커서를 이동시켜준다. 커서를 open하면 커서는 커서문에서 참조하는 테이블의 가장 첫번째 행을 가리키고있으며 fetch를 사용하면 커서가 바로 밑으로 내려가며 순서가 이동된다.

 

 

while loop사용/ 다중행 출력

--커서 while loop사용/ 다중행 출력--
declare
v_empno emp.empno%type;
v_ename emp.ename%type;
v_deptno emp.deptno%type;
v_job emp.job%type;
cursor emp_cursor is
select empno, ename, deptno, job from emp;
begin
open emp_cursor;
fetch emp_cursor into v_empno, v_ename, v_deptno, v_job;
while emp_cursor%found loop
dbms_output.put_line('v_empno: '|| v_empno || '   '||
                     'v_ename: '|| v_ename || '   '||
                     'v_deptno: '|| v_deptno || '    '||
                    'v_job: '|| v_job || '   ');
fetch emp_cursor into v_empno, v_ename, v_deptno, v_job;
end loop;
close emp_cursor;
end;
/

커서를 사용하지 않고 익명변수로 출력하게 되면 단일행밖에 출력할 수 없지만, 커서와 반복문을 활용하면 쿼리문의 조건에 맞는 다중행도 출력이 가능하다. 다중행을 사용하는 경우에는 단일행을 사용하는 방법과 크게 다르진 않지만,

begin
open emp_cursor;
fetch emp_cursor into v_empno, v_ename, v_deptno, v_job;
while emp_cursor%found loop
dbms_output.put_line('v_empno: '|| v_empno || '   '||
                     'v_ename: '|| v_ename || '   '||
                     'v_deptno: '|| v_deptno || '    '||
                    'v_job: '|| v_job || '   ');
fetch emp_cursor into v_empno, v_ename, v_deptno, v_job;
end loop;

이 실행문 부분이 약간 다르다. 똑같이 커서를 열어주고 fetch를 사용해서 커서를 이동시켜줄건데, 커서가 자동적으로 반복되어 이동하여야 하기때문에 실행문인begin절에서 while문을 작성해야한다. 이때 while문은 조건이 참일동안만 반복되는 것이기때문에 행이 있기만 하면 참을 출력하는 %found함수를 사용하여 자동적으로 반환할 행이 없을때 반복문이 멈출수있도록 하며, 반복해서 커서가 이동해야하기때문에 반복문안에 fetch를 한번더 재정의하여 반복문이 끝났을때 자동적으로 커서가 이동되도록 설계했다.

 

 

CURSOR 코드개발연구 (join사용과 group by)

커서코드를 작성하면서 실무에서는 단일행보다는 분명 다중행을 주로 사용할것같다는 생각이들었다. 현재 위에 쓰여진 커서문을 보면 커서가 참조하고있는 쿼리문이 전부 한테이블에서 값을 전부 참조하고있는데, join이나 group by된 쿼리문도 사용이 가능한지 테스트 해보고자 한다.

 

첫번쨰 코드

--커서 코드연구--
--커서에서 join이 가능한가?--
select e.empno, e.ename, e.deptno, d.dname
from emp e, dept d
where e.deptno=d.deptno;

--커서에 join대입하여 사용해보기--
declare
v_empno emp.empno%type;
v_ename emp.ename%type;
v_deptno emp.deptno%type;
v_dname dept.dname%type; --부서이름 정보는 emp테이블에 없으므로 dept테이블로 정의--
cursor join_cursor is
select e.empno, e.ename, e.deptno, d.dname from emp e, dept d
where e.deptno=d.deptno;
--커서의 sql쿼리문에서 inner join을 사용하여 dept테이블에 있는 부서정보까지같이 출력되도록함--
begin
open join_cursor;
fetch join_cursor into v_empno,v_ename,v_deptno,v_dname;
while join_cursor%found loop
dbms_output.put_line('v_empno: '|| v_empno || '   '||
                     'v_ename: '|| v_ename || '   '||
                     'v_deptno: '|| v_deptno || '    '||
                    'v_dname: '|| v_dname || '   ');
fetch join_cursor into v_empno,v_ename,v_deptno,v_dname;
end loop;
dbms_output.put_line('직원정보 출력 종료');
close join_cursor; --생략가능--
end join_cursor;
/

join은 성공적으로 사용이 가능했다...! emp테이블에서 필요한 직원들의 정보를 추출하고 dname의 정보는 dept테이블에서 참조하도록 했다. 또한 커서의 쿼리문에서 단순 쿼리문이 아니라 inner join을 활용하여 부서이름을 join해준 테이블로 출력되도록 설계하였으며, loop문 밖에 직원정보의 출력이 끝났다는것을 알리는 문구도 같이 적어보았다. 또한 다중행 쿼리문에서 뽑았을때 스크립트 창에서 간격정리가 잘 안된것 같아, 이번에는 공백으로 간격을  조절하여 보다 깔끔하게 보이도록 했으나 결과를 보니 큰 차이는 없는것 같다...ㅎㅎ

 

 

두번쨰 코드

--커서에 인라인뷰 / group by사용--
--커서에 인라인뷰를 사용한 group by의 결과도 출력이 가능한가?--
declare
v_dname dept.dname%type;
v_deptno emp.deptno%type;
v_count number; --집계함수의 결과를 받아와야하기때문에 number라는 값으로 변경--
cursor emp_cursor is
select e.dname, e.deptno, e.emp_count from(
select t.dname, t.deptno, count(t.empno) as emp_count
from (select e.empno, e.ename, e.deptno, d.dname
from emp e, dept d
where e.deptno=d.deptno) t
group by t.deptno, t.dname )e;
begin
open emp_cursor;
fetch emp_cursor into v_dname,v_deptno,v_count;
while emp_cursor%found loop
dbms_output.put_line('v_dname: '|| v_dname || '   '||
                     'v_deptno: '|| v_deptno || '   '||
                     'v_count: '|| v_count || '    '||);
fetch emp_cursor into v_dname,v_deptno,v_count;
end loop;
dbms_output.put_line('직원정보 출력 종료');
close emp_cursor; --생략가능--
end;
/

이번에는 인라인뷰와 group by를 이용하여 집계함수도 같이 출력이되나 확인해보려고했지만..! 프로시저가 실행되지 않았다..이것저것 찾아보며 시도를 해봤는데 역시 아직도 문제를 찾지는 못했다..ㅠㅎㅎ 하지만 이 과정을 통해 알게된 몇가지가 있다.

 

1. 집계함수를 확인하려면 참조테이블이 없기때문에 변수를 정의할때 그냥 number로 정의해야한다.

2. 집계함수의 별칭은 커서 쿼리문안에서 혼동이 생기지 않도록 count보다는 emp_count와 같이 이름으로 정의해줘야한다.

 

사실 굳이 인라인뷰를 사용할필요는 없을것 같아 join으로 바꿔 실행했는데 그건 됐다. 하지만 그렇게 하면 위와 다른점이 뭔지 잘 모르겠어서..꼭 서브쿼리를 이용해보고싶었다. 결론적으로 프로시저가 실행되진 않았으나, 몰랐던 부분을 알게되어 더 공부할 수 있는 부분이 생겼다..!

 

---

학습일기

익명 프로시저, 저장프로시저, 함수에서 커서까지 왔는데 전부다 처음 배웠을때보다는 확실히 수월하게 이해되는게 너무 신기했다..

익명 프로시저, 저장프로시저, 함수,커서 전부 사용 쿼리문이 비슷하면서도 약간씩 달라서 그부분 아직 조금헷갈리거나 오타때문에 나는 오류가 있긴하지만 처음배웠을때는 사실 아예 이해자체를 못해서 학원에서 그냥 거의 앵무새처럼 이해할때까지 코드 따라쓰기만 했었는데...지금은 궁금한건 코드로 직접 작성해볼 정도까지는 왔고, 어느정도 활용도 가능하다..! 아직도 오류가 중간중간 발생하고 그 오류가 왜 발생했는지 찾는데 시간이 조금 걸리긴 하지만 그래도 가장 어려워했던 부분인 프로시저부분을 점점 이해하고있다는게 느껴진다. 정말 이제는 프로시저 부분을 몇번복습하는건지도 모르겠지만 확실한건 복습 할때마다 훨씬 쉽게 느껴지니까..!

어렵더라도 계속해서 쿼리문을 적어보자...!