Total Pageviews

2020/10/02

[Oracle] [Stored Procedure] Cursor

Assume I have a table which name Product, the data look like:
SELECT ORDER_DATE, PRODUCT_ID, QTY
FROM PRODUCT
WHERE PRODUCT_ID = '1000'
ORDER BY ORDER_DATE;









If I would like to find data in stored procedure, and print data value for each row. We can make good use of CURSOR to fulfill this requirement:
CREATE OR REPLACE PACKAGE PG_PRODUCT AS
    PROCEDURE SP_PRC_GET_PRODUCT(i_product_id IN VARCHAR2);
END PG_PRODUCT;
/
CREATE OR REPLACE PACKAGE BODY PG_PRODUCT AS
    PROCEDURE SP_PRC_GET_PRODUCT(i_product_id IN VARCHAR2)
    AS
        CURSOR cur_product IS SELECT ORDER_DATE, PRODUCT_ID, QTY
                              FROM PRODUCT
                              WHERE PRODUCT_ID = i_product_id
                              ORDER BY ORDER_DATE;
        cur_product_rec cur_product%rowtype;
    BEGIN
        dbms_output.put_line('i_product_id = ' || i_product_id);
        FOR cur_product_rec IN cur_product
            LOOP
                dbms_output.put_line('ORDER_DATE = ' || cur_product_rec.ORDER_DATE ||
                                     ', PRODUCT_ID = ' || cur_product_rec.PRODUCT_ID ||
                                     ', QTY = ' || cur_product_rec.QTY);
            END LOOP;

    END SP_PRC_GET_PRODUCT;
END PG_PRODUCT;
/

Stored procedure execution log:











No comments: