plsql - sql raise_salary procedure -

i'm trying make pl/sql program raise salary of managers table , if salary greater 3000 have set @ 3000 don't seem manage make that. have use o procedure , call anonymous block.

in anonymous block, calling procedure every manager. in procedure, raising every manager's pay 500. time block exits, managers going getting quite substantial raise!

step 1 decide if want per manager looping in block or in procedure. step 2 if want raise exception if manager earning > 3000, must check see if manager earning > 3000. check null, it. no, check in exception handler doesn't count. salary had null there.

in example, i've elected looping in procedure. needs know job type gets raise , amount of raise.

create or replace procedure raise_sal( target_job emp.job%type, amount number )     max_sal       constant number := 3000; -- passed in     sal_null      exception;     sal_too_big   exception;     pragma exception_init( sal_null, -20101 );     pragma exception_init( sal_too_big, -20102 );      cursor c1         select  id, sal            emp           job = target_job         update of sal; -- nowait redundant cursors begin     emp in c1 loop begin        if emp.sal null           raise_application_error( -20101, 'salary missing' );        elsif emp.sal > max_sal           raise sal_too_high;        else           update emp set sal = curr_sal + amount current of c1;        end if;     end;     exception         when sal_too_big             update emp set sal = max_sal current of c1;         when others             raise;     end loop; end raise_sal; /  declare   v_amount constant number := 500; begin     raise_sal( 'manager', v_amount ); end; / 

this not production-quality code. 1 thing, i'd pass in maximum salary amount procedure used "salesclerk" or "vicepresident" or "sqldeveloper" or have you. i'm sure each of has different maximum. seem interested in learning technique, take example.

also, gordon's answer points out problem may still missing. checking if salary greater maximum before raise salary. if ends being greater maximum after update?