14. PL/SQL - 커서

커서

1. 커서

- 일반 프로그래밍 언어의 파일처리와 방법이 비슷, 행의 집합을 다룰 때 많은 편리한 기능 제공

- 테이블에서 여러 행을 SELECT한 후 쿼리 결과인 행의 집합을 한 행씩 처리하기 위함.

​

2 . 커서작동순서

- 커서 선언 → 커서 열기 → ( 커서 데이터 가져오기 → 데이터 처리 ) → 커서 닫기

                                                ㄴ LOOP문으로 가져올 행이 없을 때까지 반복

 * 파일처리순서: 파일 open → 처음 데이터 읽음 → 파일의 끝(EOF: End Of File)까지 반복 → 파일 close

 

3. 형식

DECLARE
  CURSOR  커서이름  IS 실행문(select sql문);  -- 커서 선언
BEGIN
  OPEN  커서이름;                    -- 커서 열기
  FETCH  커서이름  INTO  변수이름;   -- 커서로부터 데이터를 읽어와 변수에 저장 
  CLOSE  커서이름                    -- 커서 닫기
END;

* FETCH문

- 셋에서 행 단위로 데이터를 읽음. 각 FETCH 후 CURSOR는 결과셋에서 다음 행으로 이동

- 현재 행에 대한 정보를 얻어 와 INTO 뒤에 기술한 변수에 저장한 후 다음 행으로 이동.

- 얻어진 여러 행에 대한 결과값을 모두 처리하려면 반복문에 FETCH 기술

 

 

4. 커서의 속성

속성	의미
%NOTFOUND	커서 영역의 자료가 모두 FETCH 되었다면 true
%FOUND	커서 영역에 FETCH 되지 않은 자료가 있다면 true
%ISOPEN	커서가 OPEN된 상태이면 true
%ROWCOUNT	커서가 얻어 온 레코드의 개수

​

명시적 커서와 묵시적 커서

1. 명시적 커서

- 사용자가 직접 정의해서 사용하는 커서로 여러개의 행을 처리할 수 있다.

- 명시적 커서 선언 → 명시적 커서 오픈 → 커서에서 데이터 추출 → 커서 사용 종료

​

2. 묵시적 커서

- 오라클 내부에서 자동 생성되어 사용하는 커서, PL/SQL 블록에서 실행하는 문장이 실행될 때마다 자동으로 만들어져 사용.

- 커서 속성을 통해 해당 커서에 대한 정보를 얻을 수 있다.(사용자는 생성 유무는 알 수 없다.)

- 묵시적 커서에 저장되는 데이터는 1행만 가능하다.

- OPEN, FETCH, CLOSE 지정 없음

 

 

%TYPE / %ROWTYPE

1. %TYPE

- 해당 변수명은 지정한 테이블 컬럼의 데이터타입, 크기를 따라감(이미 선언된 다른 변수나 컬럼의 데이터 타입 이용하여 선언)

- 기술한 컬럼의 데이터 타입을 모를 경우 사용할 수 있고, 코딩 이후 컬럼 데이터타입이 변경되어도 수정 필요X

​

2. %ROWTYPE:

- 하나 이상의 데이터 값을 갖는 데이터 타입으로 배열과 비슷한 역할을 하며 재사용 가능

- 테이블, 뷰 내부 컬럼 데이터형, 크기, 속성 등을 그대로 사용 가능

- 지정된 테이블의 구조와 동일한 구조 갖는 변수 선언 가능

- 컬럼들의 수나 데이터 타입을 모를 때 쓸 수 있어 편리

- 데이블의 데이터 컬럼의 데이터타입이 변경될 경우 프로그램 재수정 필요 없음

​

▶ 차이점

%TYPE은 특정 한 컬럼의 한 개의 데이터만 저장하고

%ROWTYPE은 테이블의 행 단위이기 때문에 모든 컬럼에 대한 값을 저장할 수 있으나 한 번에 한 개의 데이터만 저장

​

%ROWTYPE

 

예제1: 커서를 사용하여 부서 테이블의 모든 내용을 출력1 (LOOP반복)

DECLARE
 vdept dept_table%ROWTYPE;     -- 변수명 지정: vdept
 CURSOR c1                                               
 IS
 SELECT deptno, dname, loc FROM dept_table;     --  c1커서 선언, 실행될 SELECT문 설정​​
BEGIN
 DBMS_OUTPUT.PUT_LINE('부서번호 | 부서명 | 지역명');
 DBMS_OUTPUT.PUT_LINE('------------------------');
 OPEN c1;                       -- 커서 열기           ​        
LOOP 
 FETCH c1 INTO vdept.deptno, vdept.dname, vdept.loc;    -- 커서로부터 데이터를 읽어와 변수에 저장
 EXIT WHEN c1%NOTFOUND;                                 -- 커서 c1에서 더이상 읽어올 데이터가 없다면 LOOP 탈출
 DBMS_OUTPUT.PUT_LINE(vdept.deptno || '  ' || vdept.dname || '  ' || vdept.loc);
END LOOP;
END;

 

예제2: 커서를 사용하여 부서 테이블의 모든 내용을 출력2(FOR문)

DECLARE
 vdept dept_table%ROWTYPE;
 CURSOR c1
 IS 
 SELECT * FROM dept_table;
BEGIN 
 DBMS_OUTPUT.PUT_LINE('부서번호 | 부서명 | 지역명');
 DBMS_OUTPUT.PUT_LINE('------------------------'); 

 FOR vdept IN c1 LOOP
 EXIT WHEN c1%NOTFOUND;
 DBMS_OUTPUT.PUT_LINE(vdept.deptno|| '  ' || vdept.dname ||'  ' ||vdept.loc);
 END LOOP;
END;

 

 

%TYPE

예제1: emp_table에 저장되어 있는 deptno, empno, ename, sal 정보 출력(부서번호 20에 해당하는 행들만 가져오기)

DECLARE
 vdeptno emp_table.deptno%type;
 vempno emp_table.empno%type;
 vename emp_table.ename%type;
 vsal emp_table.sal%type;
 CURSOR c2
 IS
 SELECT deptno, empno, ename, sal FROM emp_table WHERE deptno = 20;
BEGIN
 OPEN ​c2;
 DBMS_OUTPUT.PUT_LINE('부서번호 | 사원번호 | 사원이름 | 급여');
 DBMS_OUTPUT.PUT_LINE('---------------------------------'); 

LOOP 
 FETCH c2 INTO vdeptno, vempno, vename, vsal;
 EXIT WHEN c2%NOTFOUND;
  DBMS_OUTPUT.PUT_LINE(vdeptno || ' ' || vempno || ' ' || vename || ' ' || vsal);
END LOOP;
END;

 

예제2: emp_table에서 job을 입력받아서 deptno, ename, job 출력

DECLARE
 v_deptno emp_table.deptno%type;
 v_ename emp_table.ename%type;
 v_job emp_Table.job%type;
 CURSOR c3
 IS 
 SELECT deptno, ename, job FROM emp_table WHERE job = '&job'; -- 주의 (숫자 &~  / 문자 '&~~')
BEGIN 
 OPEN c3;
 DBMS_OUTPUT.PUT_LINE('부서번호 | 사원이름 | 직무');
 DBMS_OUTPUT.PUT_LINE('---------------------------------');

LOOP 
 FETCH c3 INTO v_deptno, v_ename, v_job;
 EXIT WHEN c3%NOTFOUND;
 DBMS_OUTPUT.PUT_LINE(v_deptno || '  ' || v_ename || '  ' || v_job);
END LOOP;
END;