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

PL/SQL is a server-side language so there are no user input facilities provided. All input to the procedure must come from either the database or from parameters passed to the procedure. SQL*Plus, Oracle's command-line interface, does provide some limited ability to have users input data. Perhaps [this manual](https://docs.oracle.com/cd/E11882_01/server.112/e16604/toc.htm) or, more particularly, [this section of the manual](https://docs.oracle.com/cd/E11882_01/server.112/e16604/qstart.htm#SQPUG002) would help you. — Bob Jarvis - Слава Україні, Dec 01 '19 at 22:58

score 1 · Answer 1 · answered Dec 01 '19 at 19:52

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;

score 0 · Answer 2 · answered Dec 01 '19 at 21:18

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.

CURSOR IN PROCEDURE PL/SQL

2 Answers2