plsql - How to limit the data entered within a stored procedure? -

i have stored procedure prompts user input , stores/updates table. issue when trying add limitations when entering number not work planned.

here 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 incident values        ('icd01', 'che01', 'm01', 'pp01', null);         insert incident values        ('icd02', 'che03', 'm07', 'pp02', null);         insert incident values        ('icd03', 'che03', 'm04', 'pp03', null);         insert incident values        ('icd04', 'kln04', 'm07', 'pp02', null);         insert incident values        ('icd05', 'che01', 'm04', 'pp03', null); 

when prompted, enter icd_id (for example 'icd03') *must 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 v_i_points     incident icd_id = v_icd_id;     if v_i_points > 10     raise e_update_high;     end if;     if v_inc_i_points >10    raise e_too_many_points;     end if;      if v_pp_id = 'pp03' or v_inc_i_points > 10 or v_inc_i_points < 10    raise e_a;    end if;     update incident set i_points =  (v_inc_i_points + nvl(i_points,0))                   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    dbms_output.put_line('==========================================================');   dbms_output.put_line('too many points allocated there! action aborted.');   dbms_output.put_line('==========================================================');     when e_update_high          dbms_output.put_line('=============================================================');   dbms_output.put_line('can not store many penalty points there! action aborted.');     dbms_output.put_line('=============================================================');     when e_a    dbms_output.put_line('=============================================================');   dbms_output.put_line('can allocated 10 points here! action aborted.');   dbms_output.put_line('=============================================================');     when e_b    dbms_output.put_line('=============================================================');   dbms_output.put_line('can allocated 5 points here! action aborted.');   dbms_output.put_line('=============================================================');    when e_c    dbms_output.put_line('=============================================================');   dbms_output.put_line('can allocated 1 points here! action aborted.');   dbms_output.put_line('=============================================================');   end;   / 

what trying accomplish:

is prevent people entering values of more 10 , less 10 pp_id "pp03".

for applied:

   if v_pp_id = 'pp03' or v_inc_i_points > 10 or v_inc_i_points < 10    raise e_a;    end if; 

that works fine, when add limitation like:

to prevent people entering values of more 5 , less 5 pp_id "pp02".

    if v_pp_id = 'pp02' or v_inc_i_points > 5 or v_inc_i_points < 5     raise e_b;     end if; 

or

to prevent people entering values of more 1 , less 1 pp_id "pp01".

    if v_pp_id = 'pp01' or v_inc_i_points > 1 or v_inc_i_points < 1     raise e_c;     end if; 

it not work , follows first one. how can overcome this? thanks in advance. appreciated.

i think condition incorrect:

if v_pp_id = 'pp03' or v_inc_i_points > 10 or v_inc_i_points < 10 then evaluate true whenever v_pp_id equals 'pp03' (regardless of value of v_inc_i_points) , whenever v_inc_i_points not equal 10 (regardless of value of v_pp_id).

i believe 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' , v_inc_i_points != 10 then

(the rest of conditions regarding 'pp02' , 'pp01' should modified accordingly.)

edit:

i missed first time, seems "v_pp_id" never set value. need instanciate value of selected row's pp_id column.

try replacing:

select i_points v_i_points      incident icd_id = v_icd_id; 

with

select i_points, pp_id v_i_points, v_pp_id      incident icd_id = v_icd_id;