Procedure To Check FOR ITEM LEVEL FROM THE ITEMS TABLE

Below I have tried to explain Creation of Items_table and Procedure to Find Items less in Quantity from the Items Table; 

SQL> CREATE TABLE ITEMS_TABLE(

  2  ITEM_CODE VARCHAR2(12),

  3  MINLEVL NUMBER(5),

  4  QTYNOW NUMBER(5),

  5  MAXLEVL NUMBER(5),

  6  RATE NUMBER(9,2)

  7  );

 TABLE CREATED.

SQL> INSERT INTO ITEMS_TABLE VALUES ('ITEM_01',100,80,175,120.99);

1 ROW CREATED.

SQL> INSERT INTO ITEMS_TABLE VALUES('ITEM_02',125,100,250,200.99);

1 ROW CREATED.

SQL> INSERT INTO ITEMS_TABLE VALUES('ITEM_03',100,200,325,200.99);

1 ROW CREATED.

SQL> INSERT INTO ITEMS_TABLE VALUES('ITEM_05',100,200,325,200.00);

1 ROW CREATED.

SQL> SELECT * FROM ITEMS_TABLE;

ITEM_CODE       MINLEVL     QTYNOW    MAXLEVL       RATE

------------ ---------- ---------- ---------- ----------

ITEM_01             100         80        175     120.99

ITEM_02             125        100        250     200.99

ITEM_03             100        200        325     200.99

ITEM_05             100        200        325        200

SQL>  CREATE OR REPLACE PROCEDURE ITEMS_PROC(ITEM VARCHAR2)

  2   IS

  3   QTY_NOW NUMBER;

  4   MIN_LEVEL NUMBER;

  5   MAX_LEVEL NUMBER;

  6   BEGIN

  7   SELECT

  8   QTYNOW,

  9   MINLEVL,

 10   MAXLEVL INTO QTY_NOW,MIN_LEVEL,MAX_LEVEL FROM

 11   ITEMS_TABLE WHERE ITEM_CODE=ITEM;

 12   IF QTY_NOW < MIN_LEVEL THEN

 13    UPDATE ITEMS_TABLE SET QTYNOW=MINLEVL+QTYNOW WHERE ITEM_CODE=ITEM;

 14   DBMS_OUTPUT.PUT_LINE('QTY UPDATED');

 15     ELSE

 16     DBMS_OUTPUT.PUT_LINE('ITEM PRESENT');

 17     END IF;

 18     EXCEPTION

 19     WHEN NO_DATA_FOUND THEN

 20     DBMS_OUTPUT.PUT_LINE('NO DATA RETURNED');

 21    WHEN TOO_MANY_ROWS THEN

 22     DBMS_OUTPUT.PUT_LINE('MANY ITEMS WITH SAME ITEM CODE');

 23    WHEN OTHERS THEN

 24     DBMS_OUTPUT.PUT_LINE('SOME OTHER ERROR');

 25     END;

 26   /

PROCEDURE CREATED.

SQL> EXEC ITEMS_PROC('ITEM_01');

PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.

SQL> SELECT * FROM ITEMS_TABLE;

ITEM_CODE       MINLEVL     QTYNOW    MAXLEVL       RATE

------------ ---------- ---------- ---------- ----------

ITEM_01             100        180        175     120.99

ITEM_02             125        100        250     200.99

ITEM_03             100        200        325     200.99

ITEM_05             100        200        325        200

SQL> EXEC ITEMS_PROC('ITEM_03');

PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.

SQL> SELECT * FROM ITEMS_TABLE;

ITEM_CODE       MINLEVL     QTYNOW    MAXLEVL       RATE

------------ ---------- ---------- ---------- ----------

ITEM_01             100        180        175     120.99

ITEM_02             125        100        250     200.99

ITEM_03             100        200        325     200.99

ITEM_05             100        200        325        200