PL/SQL Tutorial - Cursor

 Cursor : 쿼리 결과를 가리키는 포인터 / 쿼리 결과를 잠깐 동안 저장하고 있는 변수라고 생각하면 된다.

- 암시적커서

- 명시적커서

(1) Implicit cursors (암시적 커서)

- SELECT INTO, INSERT, UPDATE, DELETE는 implicit cursor을 생성

- cursor는 SQL%ROWCOUNT, SQL%ISOPEN, SQL%FOUND, SQL%NOTFOUND의 status를 가지고 있음

- 만일 query가 zero or multiple row일 경우 exception을 보여줌

(2) Explicit cursors (명시적 커서)

- OPEN, FETCH, CLOSE의 execution cycle을 가지고 있음.

 

 사실 잘 이해는 안간다. 예시를 통해 이해해보도록 하자. 커서의 기본 구조는 아래와 같다.

CURSOR cursor_name IS query;

커서를 사용하기 위해 커서를 열어준다.

OPEN cursor_name;

FETCH는 row를 cursor에 저장해준다.

FETCH cursor_name INTO variable_list;

fetching이 끝나면 커서를 닫아준다.

CLOSE cursor_name;

함수나 블록 구간의 커서는 끝나면 자동으로 닫히고, 필요하다면 임의로 닫아줄 수도 있다. 하지만 패키지 기반 커서는 직접 닫아주어야 한다.

 

 커서는 4개의 attribute를 가지고 있으며 attribute format이다.

cursor_name%attribute

1) %ISOPEN

- 둘 중에 하나 딱 고름  TRUE, FALSE

2) %FOUND

- NULL (before the first fetch)

- TRUE (fetch 다 함)

- FALSE (return되는 row가 없을 때)

- INVALID_CURSOR (cursor 안열림)

3) %NOTFOUND

- NULL (before the first fetch)

- FALSE (fetch 다함)

- TURE ( row X)

- INVALID_CURSOR (안열림)

4) %ROWCOUNT

- returns the number of rows fetched (fetch된 row의 개수를 리턴)

 

예를 들어서 이해해보자.

마찬가지로 example table이 있다. 

CREATE VIEW sales AS
SELECT customer_id,
       SUM(unit_price * quantity) total,
       ROUND(SUM(unit_price * quantity) * 0.05) credit
FROM order_items
INNER JOIN orders USING (order_id)
WHERE status = 'Shipped'
GROUP BY customer_id;

sales 라는 view를 생성한다. 만일 권한 부족으로 생성되지 않으면 관리자 계정으로 로그인 이후 권한을 쥐어준다.

sqlplus "as /sysdba"
SQL> grant create view to scott;
Grant succeeded.

sales view는 무슨 정보를 담고 있을까? customer_id로 그룹을 지어서 status가 shipped인 애들만 뽑아주었다. 이후 custmer_id(건당 그룹 지어진), total(가격 칭구칭구의 sum 값), round(위에꺼 * 0.05 -> 매출의 5%) credit이다.

 다음과 같은 상황이 주어졌다고 생각해본다.

- 모든 고객의 신용한도를 0으로 초기화한다.

- sales를 내림차순으로 정렬하여 백만원의 예산에서 새로운 한도를 만든다.

DECLARE
  n_budget NUMBER := 1000000;
   -- cursor
  CURSOR cur_sales IS
  SELECT  *  FROM sales  
  ORDER BY total DESC;
   -- record    
   r_sales cur_sales%ROWTYPE;
BEGIN
 
  -- reset credit limit of all customers
  UPDATE customers SET credit_limit = 0;
 
  OPEN cur_sales;
 
  LOOP
    FETCH  cur_sales  INTO r_sales;
    EXIT WHEN cur_sales%NOTFOUND;
 
    -- update credit for the current customer
    UPDATE 
        customers
    SET  
        credit_limit = 
            CASE WHEN n_budget > r_sales.credit 
                        THEN r_sales.credit 
                            ELSE n_budget
            END
    WHERE 
        customer_id = r_sales.customer_id;
 
    --  reduce the budget for credit limit
    n_budget := n_budget - r_sales.credit;
 
    DBMS_OUTPUT.PUT_LINE( 'Customer id: ' ||r_sales.customer_id || 
' Credit: ' || r_sales.credit || ' Remaining Budget: ' || n_budget );
 
    -- check the budget
    EXIT WHEN n_budget <= 0;
  END LOOP;
 
  CLOSE cur_sales;
END;

익명 블록이 완료되었습니다.
Customer id: 47 Credit: 155419 Remaining Budget: 844581
Customer id: 49 Credit: 122625 Remaining Budget: 721956
Customer id: 1 Credit: 120304 Remaining Budget: 601652
Customer id: 48 Credit: 110282 Remaining Budget: 491370
Customer id: 44 Credit: 97908 Remaining Budget: 393462
Customer id: 18 Credit: 89511 Remaining Budget: 303951
Customer id: 46 Credit: 80418 Remaining Budget: 223533
Customer id: 6 Credit: 62224 Remaining Budget: 161309
Customer id: 17 Credit: 60233 Remaining Budget: 101076
Customer id: 16 Credit: 59579 Remaining Budget: 41497
Customer id: 45 Credit: 56295 Remaining Budget: -14798

계산된 total만큼 budget에서 빼고 0원 이하가 되면 끝난다. 

한번 credit limit가 바뀌었는지 살펴보자.

SELECT customer_id,
       name,
       credit_limit
FROM customers
ORDER BY credit_limit DESC;

limit의 값이 0인 항목들도 있다. 알고리즘이 잘못된 것인지 확인하려면 SUM을 사용하면 된다.

SELECT
  SUM( credit_limit )
FROM
  customers;