PL/SQL Tutorial - Parameters

CURSOR cursor_name (parameter_list) 
IS
cursor_query;

parameter가 뭔지도 알고 cursor이 뭔지도 아는데 이걸 합치니 뭔지 모르겠다. 한 번 살펴보자.

 

일단 cursor open은 이렇게 한다.

OPEN cursor_name (value_list);

DECLARE
    rec_product products%ROWTYPE;
    CURSOR cur_product (low_price NUMBER, high_price NUMBER)
    IS
        SELECT *
        FROM products
        WHERE list_price BETWEEN low_price AND high_price;
BEGIN
    -- mass products
    DBMS_OUTPUT.PUT_LINE('Mass products: ');
    OPEN cur_product(50,100);
    LOOP
        FETCH cur_product INTO rec_product;
        EXIT WHEN cur_product%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(rec_product.product_name || ': ' ||rec_product.list_price);
    END LOOP;
    CLOSE cur_product;
 
    -- Luxury products
    DBMS_OUTPUT.PUT_LINE('Luxury products: ');
    OPEN cur_product(800,1000);
    LOOP
        FETCH cur_product INTO rec_product;
        EXIT WHEN cur_product%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(rec_product.product_name || ': ' ||rec_product.list_price);
    END LOOP;
    CLOSE cur_product;
 
END;

Crucial: 811.99
Corsair Vengeance Pro: 808.92
Corsair Dominator Platinum: 804.99

핵심은 CURSOR cur_product (low_price NUMBER, high_price NUMBER) 이 친구다. 안에 변수를 담고 있어서 내가 원하는 값을 넣으면 커서가 그 조건에 맞는 ROW를 가져온다. 이후 LOOP가 돌아간다.

 물론 default values 값도 넣어줄 수 있다.

CURSOR cursor_name (
    parameter_name datatype := default_value, 
    parameter_name datatype := default_value, 
    ...
) IS 
    cursor_query;

DECLARE
    CURSOR cur_revenue (p_year NUMBER :=2017 , p_customer_id NUMBER := 1)
    IS
        SELECT SUM(quantity * unit_price) revenue
        FROM order_items
        INNER JOIN orders USING (order_id)
        WHERE status = 'Shipped' AND EXTRACT( YEAR FROM order_date) = p_year
        GROUP BY customer_id
        HAVING customer_id = p_customer_id;
    rec_revenue cur_revenue%ROWTYPE;
BEGIN
    OPEN cur_revenue;
    LOOP
        FETCH cur_revenue INTO rec_revenue;
        EXIT    WHEN cur_revenue%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(rec_revenue.revenue);
    END LOOP;
    CLOSE cur_revenue;
END;

익명 블록이 완료되었습니다.
2406081.53

어떻게 되는지는 보면 알 수 있을 것이다.