SQL 기초 - 17. 커서와 동적쿼리

이 글은 '김상형의 SQL 정복'의 예제 코드를 설명하는 방식으로 SQL 쿼리 예제를 소개하고 있습니다.

 

SQL 기초 - 17. 커서와 동적쿼리

커서는 결과셋을 한 행씩 순회할 수 있다. 결과셋을 순회하려면 서버는 결과셋을 메모리에 저장해 두고 커서를 통해 한 줄씩 읽는다. 이런 처리를 하려면 내부적인 준비가 필요하고 다 사용한 후 정리도 해야 한다. 커서를 생성하고 사용하는 절차는 정형화되어 있다.

1. 어떤 결과셋을 순회할 것인지 커서를 정의 : CURSOR 커서 이름 IS SELECT...

2. OPEN 명령으로 커서를 연다. SELECT 문을 실행하여 결과셋을 만들고 커서를 첫 행 이전에 위치시켜 순회 준비를 한다.

3. FETCH 명령으로 다음 행을 읽어 변수에 대입하고 읽은 값으로 원하는 처리를 한다. NOTFOUND일때까지 이동을 반복하는 식으로 전체 결과셋을 순회한다.

4. 결과셋을 다 순회했거나 원하는 값을 찾았으면 CLOSE 명령으로 커서를 닫는다. 이 단계에서 결과셋을 저장하던 메모리를 해제한다.

-- 커서 코드 예제. 이 정도만 알면 바꿔서 쓰면 된다.
DECLARE
    CURSOR v_cursor IS SELECT name FROM tCity;
    v_name CHAR(10);
BEGIN
    OPEN v_cursor;
    LOOP
        FETCH v_cursor INTO v_name;
        EXIT WHEN v_cursor%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_name);
    END LOOP;
    CLOSE v_cursor;
END;

DECLARE
    CURSOR v_cursor IS SELECT * FROM tCity;
    v_city tCity%ROWTYPE;
BEGIN
    OPEN v_cursor;
    LOOP
        FETCH v_cursor INTO v_city;
        EXIT WHEN v_cursor%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_city.region || v_city.name || v_city.area);
    END LOOP;
    CLOSE v_cursor;
END;

-- 전체 행을 순회한다면 IN을 통해 간략화할 수 있다.
DECLARE
BEGIN
    FOR v_cursor IN (SELECT name FROM tCity)
    LOOP
        DBMS_OUTPUT.PUT_LINE(v_cursor.name);
    END LOOP;
END;

BEGIN
    UPDATE tCity SET popu = popu WHERE region = '강원';
    IF SQL%FOUND THEN DBMS_OUTPUT.PUT_LINE('결과셋이 있음'); END IF;
    DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || '개의 행이 바뀜');
END;

-- 커서 인수를 이용하면 코드 블록 간에 결과셋을 전달하는 용도로 사용할 수 있다.
DECLARE
    v_cursor SYS_REFCURSOR;
    v_name CHAR(10);
BEGIN
    OPEN v_cursor FOR SELECT name FROM tCity;
    LOOP
        FETCH v_cursor INTO v_name;
        EXIT WHEN v_cursor%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_name);
    END LOOP;
    CLOSE v_cursor;
END;

-- 커서 인수를 OUTPUT값으로 설정할 수 있다.
CREATE OR REPLACE PROCEDURE SP_OutCityName(p_region IN CHAR, o_cursor OUT SYS_REFCURSOR)
AS
BEGIN
    OPEN o_cursor FOR SELECT name FROM tCity WHERE region = p_region;
END;

-- 프로시저를 통해 전달받은 커서인수를 활용해 순회를 진행할 수 있다.
DECLARE
    v_cursor SYS_REFCURSOR;
    v_name CHAR(10);
BEGIN
    SP_OutCityName('전라', v_cursor);
    LOOP
        FETCH v_cursor INTO v_name;
        EXIT WHEN v_cursor%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_name);
    END LOOP;
END;

-- 업데이트 커서 : 순회 중에 테이블을 변경하려면 FOR UPDATE 구문을 붙인다.
--이 구문을 붙인 커서는 현재행에 락을 걸어 외부에서 행을 변경할 수 없도록 잠근다.
DECLARE
    CURSOR v_cursor IS SELECT * FROM tCity FOR UPDATE;
    v_city tCity%ROWTYPE;
BEGIN
    OPEN v_cursor;
    LOOP
        FETCH v_cursor INTO v_city;
        EXIT WHEN v_cursor%NOTFOUND;
        IF v_city.metro = 'y' THEN
            UPDATE tCity SET area = area + 10 WHERE CURRENT OF v_cursor;
        END IF;
    END LOOP;
    CLOSE v_cursor;
END;

-- 동적 쿼리 예제이다.
-- 변수와 제어문을 사용하여 조합할 수 있다.
DECLARE 
    v_orderID INT := 1;
    v_sql VARCHAR(100);
    v_member CHAR(20);
BEGIN
    v_sql := 'SELECT member FROM tOrder WHERE orderID = ' || v_orderID;
    EXECUTE IMMEDIATE v_sql INTO v_member;
    DBMS_OUTPUT.PUT_LINE(v_member);
END;

DECLARE
    v_area INT := 0;
    v_op CHAR(10) := '크다';
    v_sql VARCHAR(100);
    v_name CHAR(10);
BEGIN
    v_sql := 'SELECT name FROM tCity WHERE ';
    IF v_area != 0 THEN
        v_sql := v_sql || 'area ';
        IF v_op = '같다' THEN v_sql := v_sql || '= '; END IF;
        IF v_op = '크다' THEN v_sql := v_sql || '> '; END IF;
        IF v_op = '작다' THEN v_sql := v_sql || '< '; END IF;
        v_sql := v_sql || v_area || ' AND ';
    END IF;
    v_sql := v_sql || ' rownum = 1';
    EXECUTE IMMEDIATE v_sql INTO v_name;
    DBMS_OUTPUT.PUT_LINE(v_name);
END;

SELECT member INTO v_member FROM tOrder WHERE orderID = v_orderID;

v_table VARCHAR(10) := 'tCity';
SELECT name INTO v_name FROM v_table WHERE rownum = 1;

DECLARE 
    v_table VARCHAR(10) := 'tCity';
    v_sql VARCHAR(100);
    v_name CHAR(20);
BEGIN
    v_sql := 'SELECT name FROM ' || v_table || ' WHERE rownum = 1';
    EXECUTE IMMEDIATE v_sql INTO v_name;
    DBMS_OUTPUT.PUT_LINE(v_name);
END;

-- 동적 피봇 예제. 사실 동적 쿼리는 동적 쿼리를 할 때 필요하다고 봐도 무방하다.
DECLARE 
    hour_begin INT := 1;
    hour_end INT := 2;
    hour_now INT := hour_begin;
    v_sql VARCHAR(1000);
    v_cursor SYS_REFCURSOR;
    TYPE linecar IS RECORD(line VARCHAR(100), car VARCHAR(100), t1 VARCHAR(100), t2 VARCHAR(100));
    v_lc linecar;
BEGIN
    v_sql := 'SELECT * FROM tTraffic PIVOT (SUM(traffic) FOR hour IN (';
    WHILE hour_now <= hour_end
    LOOP
        v_sql := v_sql || hour_now;
        IF hour_now != hour_end THEN v_sql := v_sql || ', '; END IF;
        hour_now := hour_now + 1;
    END LOOP;
    v_sql := v_sql || '))'; 
    
    OPEN v_cursor FOR v_sql;
    LOOP
        -- INTO 절에 레코드 타입을 적으면 되는데 타입을 미리 결정할 수 없다.
        FETCH v_cursor INTO v_lc;
        EXIT WHEN v_cursor%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_lc.line || v_lc.car || v_lc.t1 || ',' || v_lc.t2);
    END LOOP;
END;