Oracle 개발자 수업 44일차 - 커서(CURSOR), 프로시저(PROCEDURE)

1. CURSOR

2. PROCEDURE

---

1. CURSOR

- SELECT INTO절과 유사한 부분이 많으므로 비교하면서 공부하자.

 

[ SELECT INTO & CURSOR ]

  1) SELECT INTO

   - 결과셋의 레코드가 1개일 때만 사용 가능하다.

   - 결과셋의 컬럼은 1개 이상이다.

   - 자바의 Iterator 구조와 유사하다.

 

  2) CURSOR

   - 결과셋의 레코드가 0개 이상일 때 사용 가능하다.(N개)

   - 컬럼셋의 컬럼은 1개 이상이다.

   - 일반적으로 결과셋의 레코드가 2개 이상일 때 권장한다.

   - 기본 문법 

DECLARE
    커서 선언;
BEGIN
    커서 열기;
        LOOP
            레코드 접근 -> 커서 사용
        END LOOP;
    커서 닫기;
END;

 

[ SELECT INTO & CURSOR 예제]

- 두 쿼리를 보면서 어떤점이 다른지 살펴보자

SET SERVEROUTPUT ON;

-- SELECT INTO
DECLARE
    VNAME TBLINSA.NAME%TYPE;
BEGIN
    NAME INTO VNAME FROM TBLINSA WHERE NUM = 1001;
    DBMS_OUTPUT.PUT_LINE(VNAME);
END;

-- CURSOR 예제1
DECLARE
    VNAME TBLINSA.NAME%TYPE;
    CURSOR VCURSOR IS SELECT NAME FROM TBLINSA; -- 커서 선언: 결과셋 참조 객체
BEGIN
    OPEN VCURSOR; -- 커서 열기: 커서가 가지고 있는 SELECT문이 실행된다. > 결과셋 > 커서가 참조
        LOOP -- 커서 조작 -> 결과셋 탐색
            FETCH VCURSOR INTO VNAME; -- 커서가 가르키는 레코드의 컬럼 접근(읽기)
            EXIT WHEN VCURSOR%NOTFOUND; -- 커서 프로퍼티: 탈출 -> 더이상 읽어올 레코드가 없을 때
            DBMS_OUTPUT.PUT_LINE(VNAME);
        END LOOP;
    CLOSE VCURSOR; -- 커서 닫기
END;

-- CURSOR 예제2
DECLARE
    CURSOR VCURSOR
        IS SELECT NAME, CAPITAL, POPULATION FROM TBLCOUNTRY ORDER BY NAME ASC;
    VNAME TBLCOUNTRY.NAME%TYPE;
    VCAPITAL TBLCOUNTRY.CAPITAL%TYPE;
    VPOPULATION TBLCOUNTRY.POPULATION%TYPE;
BEGIN
    OPEN VCURSOR;
        LOOP
            FETCH VCURSOR INTO VNAME, VCAPITAL, VPOPULATION;
            EXIT WHEN VCURSOR%NOTFOUND;
            DBMS_OUTPUT.PUT_LINE(VNAME || '-' || VCAPITAL || '-' || VPOPULATION);
        END LOOP;
    CLOSE VCURSOR;
END;

 

[ CURSOR를 좀 더 쉽게 사용하는 방법 2가지]

-- 일반 CURSOR 사용예제
DECLARE
    CURSOR VCURSOR IS SELECT * FROM TBLINSA WHERE BUSEO = '개발부';
    VROW TBLINSA%ROWTYPE; -- 레코드 참조 변수(컬럼 전체) --> TBLINSA 컬럼 10개
BEGIN
    OPEN VCURSOR;
        LOOP
            FETCH VCURSOR INTO VROW; -- 컬럼 10개 -> 변수 10개 복사
            EXIT WHEN VCURSOR%NOTFOUND;
            
            DBMS_OUTPUT.PUT_LINE(VROW.NAME);
            DBMS_OUTPUT.PUT_LINE(VROW.BUSEO);
            DBMS_OUTPUT.PUT_LINE(VROW.JIKWI);
        END LOOP;
    CLOSE VCURSOR;
END;

-- 위의 CURSOR 사용예제를 좀 더 간단하게 하는 방법 1
DECLARE
    CURSOR VCURSOR IS SELECT * FROM TBLINSA WHERE BUSEO = '개발부';
BEGIN
    FOR VROW IN VCURSOR LOOP -- VROW + LOOP + FETCH + EXIT WHEN 모든구문을 포함한다.    
        DBMS_OUTPUT.PUT_LINE(VROW.NAME);
        DBMS_OUTPUT.PUT_LINE(VROW.BUSEO);
        DBMS_OUTPUT.PUT_LINE(VROW.JIKWI);
    END LOOP;
END;

-- 위의 CURSOR 사용예제를 좀 더 간단하게 하는 방법 2
-- 인라인 커서(권장안함)
-- WHY? 가독성이 낮다, 예제 처럼 단순한 쿼리에서만 사용이 가능하다.
BEGIN
    FOR VROW IN (SELECT * FROM TBLINSA WHERE BUSEO = '개발부') LOOP
        DBMS_OUTPUT.PUT_LINE(VROW.NAME);
        DBMS_OUTPUT.PUT_LINE(VROW.BUSEO);
        DBMS_OUTPUT.PUT_LINE(VROW.JIKWI);
    END LOOP;
END;

 

2. PROCEDURE

- PL/SQL 블럭(DECLARE, BEGIN, EXCEPTION, END)

- 프로시저 종류

  1) 익명 프로시저

    - 1회용(DB에 저장이 안됨, HDD에 *.sql로 저장됨)

    - 객체가 아니다.

    - 속도가 느리다.

    - 테스트용, 개발자용

 

  2) 실명 프로시저 

    - 저장용(DB에 저장이 된다.)

    - 객체이다.

    - 재사용 가능하다, 타 유저 공유 가능

    - 속도가 빠르다.

    - 업무용

- 실명 프로시저 > 저장 프로시저(Stored Procedure)

   1) 저장 프로시저(Stored Procedure)

    - 매개 변수 구성 OR 반환값 구성 -> 자유

 

   2) 저장 함수(Stored Function)

    - 매개 변수 필수, 반환값 필수 -> 고정

---

MEMO>

 

# 프로시저, 뷰, SELECT 사용 의미와 어떨 때 쓰는지 아직 감이 안잡힌다. 프로시저에 대해서 좀 더 공부해야 겠다.

 

# 프로시저 자바의 메소드 역활과 비슷하다고 생각하자.

 

# 데이터베이스 학원 관리 프로젝트 진행중!!