Chapter 6 PL SQL(Procedural Language/SQL) - 6

PLT 6.6 CURSOR

ORACLE SERVER은 SQL문장을 실행하기 위하여 Private SQL Area이라 불리는 작업 영역을 사용합니다. Private SQL Area에 이름을 붙이고 저장된 정보를 액세스하기 위해 PL/SQL CURSOR를 사용한다. 블록의 실행 부분이 SQL문장을 실행할 때 PL/SQL은 SQL식별자를 가지는 암시적 CURSOR를 생성하고 자동적으로 이 CURSOR를 관리합니다. 명시적 CURSOR는 명시적으로 선언되고 프로그래머에 의해 명명됩니다.

암시적 CURSOR

ORACLE SERVER은 명시적으로 선언된 CURSOR와 관련 없는 각 SQL문장을 수행하기 위해CURSOR를 암시적으로 생성하여 사용한다. PL/SQL은 SQL CURSOR로써 가장 최근의 암시적CURSOR를 참조할 수 있도록 해 줍니다.

SQL CURSOR를 제어하기 위해 OPEN, FETCH, CLOSE를 사용할 수 없지만 가장 최근에 실행된 SQL문장에 대한 정보를 얻기 위한 CURSOR속성을 사용할 수 있다.

(SQL%ROWCOUNT, SQL%FOUND, SQL%NOTFOUND, SQL%ISOPEN등)

명시적 CURSOR

다중 행 SELECT 문장에 의해 RETURN되는 각 행을 개별적으로 처리하기 위해 명시적CURSOR를 사용합니다. 다중 행 질의에 의해 RETURN된 행의 집합은 result set이라 불립니다.그것의 크기는 검색 조건에 일치하는 행의 수입니다. 암시적 CURSOR에 대한 인출(FETCH)은 배열 인출(FETCH)이며, 두 번째 행의 존재는 여전히 TOO_MANY_ROWS 예외가 발생합니다. 그러므로 다중 인출을 수행하고 작업 영역에서 구문 분석된 질의를 재실행하기 위해 명시적CURSOR를 사용할 수 있습니다.

명시적 CURSOR의 함수

1.         질의에 의해 RETURN된 첫 번째 행부터 행 하나씩 처리할 수 있다.

2.         현재 처리되는 행의 트랙을 유지 합니다.

3.         프로그래머가 PL/SQL 블록에서 수동으로 제어할 수 있습니다.

명시적 CURSOR의 제어

명시적 CURSOR를 사용하기 위해서는 4가지 단계를 거처야 한다.

1.    수행되기 위한 질의의 구조를 정의하고 이름을 지정함으로써 CURSOR를 선언한다.

2.    CURSOR를OPEN한다. OPEN문장은 질의를 실행하고 참조되는 임의의 변수를 바인드 합니다. 질의에 의해 식별된 행을 active set이라 불리고 인출(FETCH) 가능합니다.

3.    CURSOR에서 데이터를 인출(FETCH)합니다. FETCH문장은 CURSOR에서 변수로 현재 행을 로드합니다. 각 인출(FETCH)은 활성 셋(active set)에서 다음 행으로 그 포인터를 이동하도록 합니다.

4.    CURSOR를 CLOSE합니다. CLOSE 문장은 행의 할성 셋(active set)을 해제 합니다. 이제 새로운 할성 셋(active set)을 생성하기 위해 CURSOR를 다시 OPEN할 수 있습니다.

DECLARE CURSOR

명시적으로 CURSOR를 선언하기 위해 CURSOR문장을 사용한다. 질의 내에서 변수를 참조할 수 있지만 CURSOR 문장 전에 선언되어야 한다.

CURSOR cursor_name IS           select_statement;

cursor_name : PL/SQL 식별자

select_statement : INTO절이 없는 SELECT 문장

OPEN CURSOR

질의를 수행하고 검색 조건을 충족하는 모든 행으로 구성된 결과 셋을 생성하기 위해 CURSOR를 OPEN한다. CURSOR는 이제 결과 셋에서 첫 번째 행을 가리킴니다.

OPEN  cursor_name;

OPEN문장은 다음의 작업을 수행한다.

• 문맥 영역에 대해 동적으로 메모리를 할당하여 중요한 프로세싱 정보를 포함 합니다.

• SELECT문장을 구문 분석합니다.

• 입력 변수를 바인드 합니다.

• 결과 셋을 식별합니다. 즉 검색 조건을 충족시키는 행의 집합입니다. OPEN문장이 실행될 때 결과 셋에 있는 행을 변수로 읽어 들이지 않습니다. 그대신 FETCH문장이 행을 읽습니다.

• 포인터는 활성 셋에서 첫 번째 행에 위치합니다.

FETCH CURSOR

FETCH 문장은 결과 셋에서 하나의 행을 읽어 들입니다. 각 인출(FETCH) 후에 CURSOR는 결과 셋에서 다음 행으로 이동한다.

FETCH  cursor_name  INTO  {variable1[,variable2, . . . .] | record_name};

• SELECT 문장의 열과 같은 개수의 변수를 FETCH문장의 INTO절에 포함시켜 좌측부터1대1 대응 되도록 데이형과 길이가 같아야 합니다.

• CURSOR에 대한 레코드를 정의하고 FETCH INTO절에서 레코드를 참조할 수 있습니다.

• CURSOR가 RETURN할 행을 포함하는지 테스트합니다. FETCH시 아무 값도 읽지 않아도 즉 활성 셋에서 프로세스할 남겨진 행이 없는 경우에도 오류가 발생되지 않습니다.

• FETCH문장은 다음 작업을 수행합니다.

- 활성 셋에서 다음 행으로 포인터를 이동합니다.

- 현재 행에 대한 정보를 출력 PL/SQL변수로 읽어 들입니다.

- 포인터가 활성 셋의 끝에 위치하게 되면 CURSOR는 FOR LOOP를 탈출 합니다.

CLOSE CURSOR

CLOSE문장은 CURSOR를 사용할 수 없게 하고 결과 셋의 정의를 해제합니다. SELECT 문장이 다 처리된 완성 후에는 CURSOR를 닫습니다. 필요하다면 CURSOR를 다시 열수도 있습니다. 그러므로 활성 셋을 여러 번 설정할 수 있다. CLOSE문장은 context area를 해제 합니다. 커서를 닫지 않고 PL/SQL블록을 종료하는 것이 가능하다 할 지라도 리소스를 다시 사용 가능하게 하기 위해 명시적으로 선언된 임의의 커서를 닫는 습관을 들여야 합니다. 데이터베이스 매개변수(initial parameter file)에서 OPEN_CURSORS매개변수에 의해 결정되는 사용자마다 해당하는 커서의 수에는 최대 한계가 있습니다. 디폴트로 OPEN_CURSORS=50입니다.

CLOSE  cursor_name;

명시적 CURSOR의 속성

명시적 CURSOR로 CURSOR에 대해 상태 정보를 얻기 위한 4가지 속성이 있습니다.

속  성	타  입	설     명
%ISOPEN	BOOLEAN	CURSOR가 열리면 TRUE
%NOTFOUND	BOOLEAN	가장 최근의 인출(FETCH)이 행을 RETURN하지 않으면TRUE
%FOUND	BOOLEAN	가장 최근의 인출(FETCH)이 행을 RETURN하면 TRUE
%ROWCOUNT	NUMBER	지금까지 RETURN된 행의 총 수

복수 인출(FETCH) 제어

명시적 CURSOR에서 여러 행을 처리하기 위해서 반복적으로 인출(FETCH)을 수행하는 루프를 정의합니다. 결과적으로 활성 셋의 모든 행은 처리되고 인출(FETCH)이 실패하면%NOTFOUND속성을 TRUE로 설정한다. CURSOR에 대해 참조하기 전에 각 인출(FETCH)의 성공을 테스트하기 위해 명시적 CURSOR를 사용합니다.

ACCEPT  p_deptno PROMPT ' 부서번호를 입력하시오 : ' DECLARE                  v_deptno        emp.deptno%TYPE := &p_deptno;                  v_empno        emp.empno%TYPE;                  v_ename        emp.ename%TYPE;                  v_sal             emp.sal%TYPE;                  v_sal_total        NUMBER(10,2) := 0;                  CURSOR emp_cursor IS                                   SELECT empno,ename,sal                                                     FROM emp                                                     WHERE deptno = v_deptno                                                     ORDER BY empno; BEGIN                  OPEN emp_cursor;                  DBMS_OUTPUT.PUT_LINE('사번    이 름         급   여');                  DBMS_OUTPUT.PUT_LINE('----  ---------- ----------------');                  LOOP                                   FETCH emp_cursor INTO v_empno,v_ename,v_sal;                                   EXIT WHEN emp_cursor%NOTFOUND;                                   v_sal_total := v_sal_total + v_sal;                                   DBMS_OUTPUT.PUT_LINE(RPAD(v_empno,6) ||                                   RPAD(v_ename,12) || LPAD(TO_CHAR(v_sal,'$99,999,990.00'),16));                  END LOOP;                  DBMS_OUTPUT.PUT_LINE('----------------------------------');                  DBMS_OUTPUT.PUT_LINE(RPAD(TO_CHAR(v_deptno),2) || '번 부서의 합    ' ||                                   LPAD(TO_CHAR(v_sal_total,'$99,999,990.00'),16));                  CLOSE emp_cursor; END;

CURSOR와 RECORD

테이블에서 열의 구조를 사용하기 위해 RECORD를 정의할 수 있다. 또한 명시적 CURSOR에서의 열 목록을 기초로 하여 RECORD를 정의할 수 있습니다. 이것은 단순히 인출할 수 있기 때문에 활성 셋의 행을 처리하기가 편리하다. 그러므로 행 값은 RECORD의 해당 필드 안으로 직접LOAD된다.

DECLARE                  dept_record       dept%ROWTYPE;                  CURSOR dept_cursor IS                                   SELECT *                                                     FROM dept                                                     ORDER BY deptno; BEGIN                  OPEN dept_cursor;                  DBMS_OUTPUT.PUT_LINE('부서번호    부 서 명       위    치');                  DBMS_OUTPUT.PUT_LINE('--------  ------------- ------------');                  LOOP                                   FETCH dept_cursor INTO dept_record;                                   EXIT WHEN dept_cursor%NOTFOUND;                                    DBMS_OUTPUT.PUT_LINE(LPAD(dept_record.deptno,2) || '        '                                  || RPAD(dept_record.dname,15) || RPAD(dept_record.loc,12));                  END LOOP;                  CLOSE dept_cursor; END;

DECLARE                  TYPE dept_record_type IS RECORD                                   (v_deptno dept.deptno%TYPE,                                   v_dname dept.dname%TYPE,                                   v_loc dept.loc%TYPE);                  dept_record       dept_record_type;                  CURSOR dept_cursor IS                                   SELECT *                                                     FROM dept                                                     ORDER BY deptno; BEGIN                  OPEN dept_cursor;                  DBMS_OUTPUT.PUT_LINE('부서번호    부 서 명       위    치');                  DBMS_OUTPUT.PUT_LINE('--------  ------------- ------------');                  LOOP                                   FETCH dept_cursor INTO dept_record;                                   EXIT WHEN dept_cursor%NOTFOUND;                                   DBMS_OUTPUT.PUT_LINE(LPAD(dept_record.v_deptno,2) || '        '                         || RPAD(dept_record.v_dname,15) ||                         RPAD(dept_record.v_loc,12));                  END LOOP;                  CLOSE dept_cursor; END;

CURSOR와 FOR LOOP

CURSOR FOR LOOP는 명시적 CURSOR에서 행을 처리합니다. LOOP에서 각 반복마다CURSOR를 열고 행을 인출(FETCH)하고 모든 행이 처리되면 자동으로 CURSOR가 CLOSE되므로 사용하기가 편리합니다.

FOR  record_name  IN  cursor_name  LOOP             statement1;             statement2;             . . . . . . END  LOOP;

record_name : 암시적으로 선언된 RECORD 이름

cursor_name : 선언되어 있는 CURSOR의 이름

• LOOP를 제어하는 RECORD를 선언하지 마십시오.

• 필요하다면 LOOP내에서 CURSOR의 속성을 이용하십시오.

• 필요하다면 FOR문 안에서 CURSOR이름 다음에 괄호로 CURSOR에 대한 매개변수를 묶어 사용하십시오.

• CURSOR 작업이 수동으로 처리되어야 할 때는 FOR LOOP를 사용하지 마십시오.

• LOOP가 시작될 때 질의를 정의할 수 있습니다. 질의 표현식은 SELECT 부속문장이라 불리고 CURSOR는 FOR LOOP내에서만 사용할 수 있습니다. 이름을 가지고 CURSOR가 선언되지 않기 때문에 그 속성을 사용할 수는 없습니다.

DECLARE                  CURSOR dept_cursor IS                                   SELECT *                                                     FROM dept                                                     ORDER BY deptno; BEGIN                  DBMS_OUTPUT.PUT_LINE('부서번호    부 서 명       위    치');                  DBMS_OUTPUT.PUT_LINE('--------  ------------- ------------');                  FOR dept_record IN dept_cursor LOOP                                   DBMS_OUTPUT.PUT_LINE(LPAD(dept_record.deptno,2) || '        '                    || RPAD(dept_record.dname,15) || RPAD(dept_record.loc,12));                  END LOOP; END;

SUBQUERY를 사용한 CURSOR FOR LOOP

PL/SQL이 SUBQUERY를 치환 하도록 하기 때문에 CURSOR는 선언할 필요가 없다.

FOR  record_name  IN  (subquery)  LOOP             statement1;             statement2;             . . . . . . END  LOOP;

subquery : SELECT문장을 기술

DECLARE BEGIN                  DBMS_OUTPUT.PUT_LINE('부서번호    부 서 명       위    치');                  DBMS_OUTPUT.PUT_LINE('--------  ------------- ------------');                  FOR dept_record IN (SELECT * FROM dept ORDER BY deptno) LOOP                                   DBMS_OUTPUT.PUT_LINE(LPAD(dept_record.deptno,2) || '        '                    || RPAD(dept_record.dname,15) || RPAD(dept_record.loc,12));                  END LOOP; END;