Data Base/PL SQL

231030 PL/SQL CURSOR, 명시적 CURSOR

잇꼬 2023. 10. 30. 18:21
728x90
반응형
SMALL

■ CURSOR
- SQL 문 실행 메모리 영역
- PARSE, BIND, EXECUTE(ACTIVE SET), FETCH(ACTIVE SET 결과를 화면 출력 VS 프로그램 변수에 LOAD 하는 단계) 
- 암시적 커서(Implicit CURSOR) 
1. SELECT .. INTO 절 : 반드시 1개 행 값만 FETCH해야 함.
    2. 오라클이 자동으로 커서 관리를 한다.
- 0개 행 : NO_DATA_FOUND
- 2개 이상 : TOO_MANY_ROWS
3. DML(INSERT, UPDATE, DELETE, MERGE), TCL(COMMIT, ROLLBACK, SAVEPOINT)
- 명시적 커서(Explicit CURSOR)
1. 여러개의 행을 FETCH 해야 한다면 명시적 커서를 이용해야 한다. 
2. 프로그래머(사용자) 커서를 관리해야 한다.
3. 0건, 1건, 여러건 이든 상관없이 사용가능.

# 암시적커서 : 1개의 행만 출력

SELECT * FROM hr.employees WHERE employee_id = 100;


# 명시적커서(중복성+null 값 포함) : 0개의 행이상 출력

SELECT * FROM hr.employees WHERE department_id = 20;

 

CURSOR 속성
암시적 CURSOR 명시적 CURSOR
SQL%ROWCONT 처리건수 확인. 
가장 최근 수행된 영향받은 행의 수
커서명%ROWCOUNT FETCH 한 행의 수 를 리턴.
현재까지 반환된 모든 행의 수
SQL%FOUND 영향을 입은행(ROW)가
있다면 TRUE, 
없으면 FLASE 을 리턴
커서명%FOUND FETCH 한 행이 
없으면 FALSE, 
있으면 TRUE 를 리턴
SQL%NOTFOUND 영향을 입은 행(ROW)가 
없다면 TRUE, 
있다면 FALSE 을 리턴
커서명%NOTFOUND FETCH 한 행이 
없으면 TRUE,
있으면 FALSE 를 리턴
SQL%ISOPEN 암시적 커서가 열려있는지 여부 확인
항상 FALSE
커서명%ISOPEN 열린 상태인지 여부를 판별


# 명시적 CURSOR

DECLARE
    /* 1. (명시적)cursor 선언 
            단, INTO 는 쓰지 않는 것. */
    CURSOR emp_cur/* 메모리명 */ IS 
        SELECT last_name 
        FROM hr.employees
        WHERE department_id = 20; /* 변수 : BIND 단계 */
    
    /* SELECT 컬럼명만큼 변수 선언 */
    --v_name varchar2(30);
    v_name hr.employees.last_name%TYPE;
BEGIN
    /* 2. cursor open: 메모리 할당, SELECT 문에 대해서 PARSE(실행계획+문법체크+의미분석), BIND(변수), EXECUTE(결과값) 
          ACTIVE SET 결과 -> USER 전달 */
    OPEN emp_cur;
    
    /* 3. FETCH 단계 
          ACTIVE SET 결과 : 첫번째 데이터를 변수에 로드 작업. 
          ※ 여러건의 데이터가 있으면 꼭 '반복문'안에서 FETCH 작업을 수행해야 한다. 
          */
    LOOP 
        FETCH emp_cur INTO v_name;
            EXIT WHEN emp_cur%NOTFOUND; /* FETCH 값(ROW) 없으면 TRUE, 있으면 FALSE */
        DBMS_OUTPUT.PUT_LINE(v_name);
    END LOOP;
    
    /* 4. CURSOR CLOSE : CURSOR 해지
          - 메모리사용량 줄이기 위해서
          - 프로그램 CLOSE 하면 자동적으로 닫혀서 괜찮으나, 습관적으로 써주는 게 좋다. */
    CLOSE emp_cur;
    
END;
/

 

< 작성법 >

  (명시적) CURSOR 
1. (명시적) CURSOR 선언 단계
 DECLARE 
1. CURSOR 선언
CURSOR 메모리명 IS
SQL 문 (SELECT 문, INSERT문, UPDATE문, DELETE문) ;
2. CURSOR OPEN 단계 BEGIN
2. CURSOR OPEN
- 메모리 할당, ⓐ parse(실행계획, 문법체크, 의미분석), ⓑ bind(변수), ⓒ execute(결과값) 
- ACTIVE SET 결과 -> USER(프로그래머, 사용자, 개발자 등) 전달
OPEN 메모리명;
3. FETCH 단계  3. FETCH 
ACTIVE SET 결과 : 첫 번째 데이터를 변수에 로드(INSERT) 작업.
※ 여러 개의 데이터가 있으면 꼭 반복문 안에세 FETCH 작업을 수행해야 한다.
- 반복문 : LOOP문, WHILE LOOP문, FOR LOOP문 
LOOP
        FETCH 메모리명 INTO 변수명;
               EXIT WHEN 메모리명%NOTFOUND; --FETCH 값(row) 없으면 true, 있으면 false 
        dbms_output.put_line(변수명);
END LOOP;
4. CURSOR CLOSE 단계 4. CURSOR 해지
1) 메모리사용량 줄이기 위함.
2) 프로그램 CLOSE 하면 자동적으로 시스템닫히기하나, 습관적으로 작성해주자!
CLOSE 메모리명;
  END;
/

 

# cursor 선언 단계에서 변수

DECLARE
    CURSOR emp_cur IS 
        SELECT e.last_name, e.salary, d.department_name 
        FROM hr.employees e, hr.departments d
        WHERE e.department_id = 20 
        AND d.department_id = 20;
    
    /* 종속관계 */
    v_name hr.employees.last_name%TYPE;
    v_sal hr.employees.salary%TYPE;
    v_dept_name hr.departments.department_name%TYPE;
BEGIN
    OPEN emp_cur;
    
    LOOP 
        FETCH emp_cur INTO v_name, v_sal, v_dept_name;
            EXIT WHEN emp_cur%NOTFOUND; /* FETCH 값(ROW) 없으면 TRUE, 있으면 FALSE */
        DBMS_OUTPUT.PUT_LINE(v_name||' '||v_sal||' '||v_dept_name);
    END LOOP;
    
    CLOSE emp_cur;
END;
/

 

# 변수 → 레코드 타입으로 변경 

DECLARE
    CURSOR emp_cur IS 
        SELECT e.last_name, e.salary, d.department_name 
        FROM hr.employees e, hr.departments d
        WHERE e.department_id = 20 
        AND d.department_id = 20;
    
    /* rowtype 으로 변경 */    
    v_rec emp_cur%ROWTYPE;
    
BEGIN

    OPEN emp_cur;
    
    LOOP 
        FETCH emp_cur INTO v_rec;
            EXIT WHEN emp_cur%NOTFOUND; 
        /* 컬럼명이 아닌 필드명으로 작성. */
        DBMS_OUTPUT.PUT_LINE(v_rec.last_name||' '||v_rec.salary||' '||v_rec.department_name);
    END LOOP;
    
    CLOSE emp_cur;
END;
/

 

# 코드의 간결화, 가독성 + 적은 메모리화

DECLARE
    CURSOR emp_cur IS 
        SELECT e.last_name, e.salary, d.department_name 
        FROM hr.employees e, hr.departments d
        WHERE e.department_id = 20 
        AND d.department_id = 20;
    
BEGIN
    /* 암시적으로 선언: 레코드 변수(자동 생성) v_rec  */
    FOR v_rec IN emp_cur LOOP 
        DBMS_OUTPUT.PUT_LINE(v_rec.last_name);
        DBMS_OUTPUT.PUT_LINE(v_rec.salary);
        DBMS_OUTPUT.PUT_LINE(v_rec.department_name);
    END LOOP;

END;
/

 

# CURSOR TABLE 에 SELECT 문 생성. 

→ 이 코드에서는 명시적커서의 속성을 사용할 수 없다. 

BEGIN
    FOR v_rec IN ( SELECT e.last_name, e.salary, d.department_name 
                   FROM hr.employees e, hr.departments d
                   WHERE e.department_id = 20 
                   AND d.department_id = 20 ) LOOP 
        DBMS_OUTPUT.PUT_LINE(v_rec.last_name);
        DBMS_OUTPUT.PUT_LINE(v_rec.salary);
        DBMS_OUTPUT.PUT_LINE(v_rec.department_name);
    END LOOP;
END;
/

 

728x90
반응형
LIST