SQLD 기출문제 예상문제 102번

다음 문제를 풀어보세요.

다음 중 아래 SQL에 대한 설명으로 가장 적절한 것을 고르시오.

[테이블 구조]

CREATE TABLE ORDERS (
    ORDER_ID INT PRIMARY KEY,
    CUSTOMER_ID INT,
    ORDER_DATE DATE,
    TOTAL_AMOUNT DECIMAL(10, 2)
);

CREATE TABLE CUSTOMERS (
    CUSTOMER_ID INT PRIMARY KEY,
    CUSTOMER_NAME VARCHAR(255),
    CITY VARCHAR(255)
);

CREATE TABLE PRODUCTS (
    PRODUCT_ID INT PRIMARY KEY,
    PRODUCT_NAME VARCHAR(255),
    PRICE DECIMAL(10, 2)
);

CREATE TABLE ORDER_ITEMS (
    ORDER_ID INT,
    PRODUCT_ID INT,
    QUANTITY INT,
    PRIMARY KEY (ORDER_ID, PRODUCT_ID),
    FOREIGN KEY (ORDER_ID) REFERENCES ORDERS(ORDER_ID),
    FOREIGN KEY (PRODUCT_ID) REFERENCES PRODUCTS(PRODUCT_ID)
);

CREATE OR REPLACE PROCEDURE GET_CUSTOMER_ORDERS (
    p_customer_id IN CUSTOMERS.CUSTOMER_ID%TYPE,
    p_start_date IN DATE,
    p_end_date IN DATE,
    p_cursor OUT SYS_REFCURSOR
) AS
BEGIN
    OPEN p_cursor FOR
    SELECT
        O.ORDER_ID,
        O.ORDER_DATE,
        O.TOTAL_AMOUNT
    FROM
        ORDERS O
    WHERE
        O.CUSTOMER_ID = p_customer_id
        AND O.ORDER_DATE BETWEEN p_start_date AND p_end_date;
END;
/

CREATE OR REPLACE PACKAGE CUSTOMER_PKG AS
    TYPE order_cursor_type IS REF CURSOR;
    PROCEDURE get_customer_orders_pkg (
        p_customer_id IN CUSTOMERS.CUSTOMER_ID%TYPE,
        p_start_date IN DATE,
        p_end_date IN DATE,
        p_cursor OUT order_cursor_type
    );
END CUSTOMER_PKG;
/

CREATE OR REPLACE PACKAGE BODY CUSTOMER_PKG AS
    PROCEDURE get_customer_orders_pkg (
        p_customer_id IN CUSTOMERS.CUSTOMER_ID%TYPE,
        p_start_date IN DATE,
        p_end_date IN DATE,
        p_cursor OUT order_cursor_type
    ) AS
    BEGIN
        OPEN p_cursor FOR
        SELECT
            O.ORDER_ID,
            O.ORDER_DATE,
            O.TOTAL_AMOUNT
        FROM
            ORDERS O
        WHERE
            O.CUSTOMER_ID = p_customer_id
            AND O.ORDER_DATE BETWEEN p_start_date AND p_end_date;
    END get_customer_orders_pkg;
END CUSTOMER_PKG;
/