CURSOR IN PROCEDURE PL/SQL

Question

i have successfully created procedure for checking my stock item, here's the syntax :

create or replace procedure check_stock
AS
CURSOR check_stock IS
select category.category_name, item.item_name, item.stock from category join item on category.category_id = item.category_id;
begin
for stock in check_stock
LOOP
DBMS_OUTPUT.PUT_LINE(stock.category_name||' '||stock.item_name||' '||stock.stock);
END LOOP;
End;

now i want to create the same procedure but i need to input the item name so the output is the stock of that item name i have inputted, can someone show me the way/syntax using the syntax i've given above ? thanks

Answer 1

create or replace procedure check_stock ( v_item_name in Integer )
AS
CURSOR check_stock IS
select category.category_name, item.item_name, item.stock from category join item on category.category_id = item.category_id where item.item_name = v_item_name ;
begin
for stock in check_stock
LOOP
DBMS_OUTPUT.PUT_LINE(stock.category_name||' '||stock.item_name||' '||stock.stock);
END LOOP;
End;

Answer 2

You need to use one IN and one OUT parameter :

SQL> SET SERVEROUTPUT ON
SQL> CREATE OR REPLACE PROCEDURE check_stock( 
                                             i_item_name in  item.item_name%type,
                                             o_stock     out category.stock%type 
                                            ) AS
  CURSOR check_stock IS
  SELECT c.category_name, i.item_name, i.stock
    FROM category c
    JOIN item i
      ON c.category_id = i.category_id
   WHERE i.item_name = i_item_name;
BEGIN
  FOR stock IN check_stock 
  LOOP
    DBMS_OUTPUT.PUT_LINE(stock.category_name || ' ' || stock.item_name || ' ' || stock.stock);
    o_stock := nvl(o_stock,0) + stock.stock;
  END LOOP;
END;
/

but this way, you get the last value of stock from the cursor for multiple returning rows. It's unclear which value for stock value. So, I considered the summing up the returning stock values.