2011년 6월 1일 수요일

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;

댓글 없음:

댓글 쓰기

블록체인 개요 및 오픈소스 동향

블록체인(block chain) 블록체인은 공공 거래장부이며 가상 화폐로 거래할때 발생할때 발생할 수 있는 해킹을 막는 기술. 분산 데이터베이스의 한 형태로, 지속적으로 성장하는 데이터 기록 리스트로서 분산 노드의 운영자에 의한 임의 조작이 불가...