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:
Post a Comment