CODEWITHSUNDEEP
stored-proceduresplsql
Ryoss
Ryoss

Reputation: 59

How to limit the data entered within a stored procedure?

I have a stored procedure that prompts user for input and stores/updates my table. My issue is that when trying to add limitations when entering number it does not work as planned.

Here is the table:

       Drop table incident;
       CREATE TABLE INCIDENT 
       (ICD_ID VARCHAR2 (8) NOT NULL PRIMARY KEY,
       PLR_ID VARCHAR2 (11),
       M_ID VARCHAR2 (10),
       PP_ID VARCHAR2 (10),
       I_POINTS NUMBER (8));


       INSERT INTO INCIDENT VALUES
       ('ICD01', 'CHE01', 'M01', 'PP01', NULL);

       INSERT INTO INCIDENT VALUES
       ('ICD02', 'CHE03', 'M07', 'PP02', NULL);

       INSERT INTO INCIDENT VALUES
       ('ICD03', 'CHE03', 'M04', 'PP03', NULL);

       INSERT INTO INCIDENT VALUES
       ('ICD04', 'KLN04', 'M07', 'PP02', NULL);

       INSERT INTO INCIDENT VALUES
       ('ICD05', 'CHE01', 'M04', 'PP03', NULL);

When prompted, I enter the ICD_ID (for example 'ICD03') *must be capital* entry.

The procedure:

   DECLARE 
   v_icd_id incident.icd_id%type := &INCIDENT_ID; 
   v_inc_I_POINTS incident.i_points%type := &PENALTY_POINTS; 
   v_I_POINTS incident.I_POINTS%type;
   V_PP_ID INCIDENT.PP_ID%TYPE;    
   E_TOO_MANY_POINTS exception;
   E_UPDATE_HIGH exception;   
   E_A exception;
   E_B exception;
   E_C exception;
   BEGIN 
   SELECT I_POINTS INTO v_I_POINTS 
   FROM incident WHERE icd_id = v_icd_id;

   IF v_I_POINTS > 10 THEN 
   RAISE E_UPDATE_HIGH; 
   END IF;

   IF v_inc_I_POINTS >10 THEN
   RAISE E_TOO_MANY_POINTS; 
   END IF;


   IF V_PP_ID = 'PP03' OR V_INC_I_POINTS > 10 OR V_INC_I_POINTS < 10 THEN
   RAISE E_A;
   END IF;


  UPDATE incident SET i_points =  (v_inc_i_points + NVL(i_points,0))               
   WHERE icd_id=v_icd_id;



  DBMS_OUTPUT.PUT_LINE('');
  DBMS_OUTPUT.PUT_LINE('=====================');
  DBMS_OUTPUT.PUT_LINE('PENALTY POINTS UPDATED!');
  DBMS_OUTPUT.PUT_LINE('=====================');
  DBMS_OUTPUT.PUT_LINE('');

  EXCEPTION 
  WHEN E_TOO_MANY_POINTS THEN 
  DBMS_OUTPUT.PUT_LINE('==========================================================');
  DBMS_OUTPUT.PUT_LINE('TOO MANY POINTS ALLOCATED THERE! ACTION ABORTED.');
  DBMS_OUTPUT.PUT_LINE('==========================================================');


  WHEN E_UPDATE_HIGH THEN 
        DBMS_OUTPUT.PUT_LINE('=============================================================');
  DBMS_OUTPUT.PUT_LINE('CAN NOT STORE THAT MANY PENALTY POINTS THERE! ACTION ABORTED.');
    DBMS_OUTPUT.PUT_LINE('=============================================================');


  WHEN E_A THEN 
  DBMS_OUTPUT.PUT_LINE('=============================================================');
  DBMS_OUTPUT.PUT_LINE('CAN ONLY ALLOCATED 10 POINTS HERE! ACTION ABORTED.');
  DBMS_OUTPUT.PUT_LINE('=============================================================');


  WHEN E_B THEN 
  DBMS_OUTPUT.PUT_LINE('=============================================================');
  DBMS_OUTPUT.PUT_LINE('CAN ONLY ALLOCATED 5 POINTS HERE! ACTION ABORTED.');
  DBMS_OUTPUT.PUT_LINE('=============================================================');

  WHEN E_C THEN 
  DBMS_OUTPUT.PUT_LINE('=============================================================');
  DBMS_OUTPUT.PUT_LINE('CAN ONLY ALLOCATED 1 POINTS HERE! ACTION ABORTED.');
  DBMS_OUTPUT.PUT_LINE('=============================================================');

 END;
  /

What I am trying to accomplish:

Is to prevent people entering values of more than 10 and less than 10 where the PP_ID is "PP03".

For this I applied:

   IF V_PP_ID = 'PP03' OR V_INC_I_POINTS > 10 OR V_INC_I_POINTS < 10 THEN
   RAISE E_A;
   END IF;

That works fine, but when I add another limitation like:

To prevent people entering values of more than 5 and less than 5 where the PP_ID is "PP02". 

    IF V_PP_ID = 'PP02' OR V_INC_I_POINTS > 5 OR V_INC_I_POINTS < 5 THEN
    RAISE E_B;
    END IF;

OR

To prevent people entering values of more than 1 and less than 1 where the PP_ID is "PP01". 

    IF V_PP_ID = 'PP01' OR V_INC_I_POINTS > 1 OR V_INC_I_POINTS < 1 THEN
    RAISE E_C;
    END IF;

It does not work and only follows the first one. How can I overcome this? Thanks in advance. Help is greatly appreciated.

Upvotes: 0

Views: 117

Answers (1)

gkalpak
gkalpak

Reputation: 48211

I think your condition is incorrect:

IF V_PP_ID = 'PP03' OR V_INC_I_POINTS > 10 OR V_INC_I_POINTS < 10 THEN will evaluate to true whenever V_PP_ID equals 'PP03' (regardless of the value of V_INC_I_POINTS) AND whenever V_INC_I_POINTS is not equal to 10 (regardless of the value of V_PP_ID).

I believe the intended coniditon is:

IF V_PP_ID = 'PP03' AND (V_INC_I_POINTS > 10 OR V_INC_I_POINTS < 10) THEN

or simply:

IF V_PP_ID = 'PP03' AND V_INC_I_POINTS != 10 THEN

(The rest of your conditions regarding 'PP02' and 'PP01' should be modified accordingly.)

EDIT:

I missed it the first time, but it seems that "V_PP_ID" is never set to any value. We need to instanciate it to the value of the selected row's PP_ID column.

Try replacing:

SELECT I_POINTS INTO v_I_POINTS 
    FROM incident WHERE icd_id = v_icd_id;

with

SELECT I_POINTS, PP_ID INTO v_I_POINTS, V_PP_ID 
    FROM incident WHERE icd_id = v_icd_id;

Upvotes: 1

Related Questions