Thread: Procedure cursor

i don't know why this code working properly

set serveroutput on;
create or replace procedure proc1
(emp_id in emp.employee_id%type,
emp_na out emp.last_name%type,
emp_sal out emp.salary%type,
emp_job out emp.job_id%type) is

cursor c1 is
select last_name, salary
from emp
where employee_id = emp_id;
excep exception;
excep1 exception;
excep2 exception;
excep3 exception;
excep4 exception;
v_counter number(10) := 0;
begin
open c1;
loop
fetch c1 into emp_na, emp_sal;
v_counter := v_counter + 1;
exit when v_counter = 1 or c1%notfound;
end loop;
close c1;
if emp_sal = 25000 and emp_job = 'AD_PRES' then raise excep;
elsif emp_sal > 25000 and emp_job = 'AD_PRES' then raise excep1;
elsif emp_sal < 25000 and emp_job = 'AD_PRES' then raise excep2;
elsif emp_sal = 3100 and emp_job = 'PU_CLERK' then raise excep3;
elsif emp_sal > 3100 and emp_job = 'PU_CLERK' then raise excep4;
end if;

exception
when excep then
dbms_output.put_line('AD_PRES already have the salary');
when excep1 then
dbms_output.put_line('AD_PRES greater than the update salary');
when excep2 then
update emp
set salary = 26000
where employee_id = emp_id;
when excep3 then
update emp
set salary = 4000
where employee_id = emp_id;
when excep4 then
dbms_output.put_line('PU_CLERK greater than the update salary');
when no_data_found then
dbms_output.put_line('NO RECORD FOUND');


end proc1;
/
variable na varchar2(30)
variable sa number
variable jo varchar2(10)
execute proc1(11235, :na, :sa, :jo)

Hi,
now run the quiery it will run properly.


create or replace procedure proc1
(emp_id in employees.employee_id%type,
emp_na out employees.last_name%type,
emp_sal out employees.salary%type,
emp_job out employees.job_id%type) is
cursor c1 is
select last_name, salary
from employees
where employee_id = emp_id;
excep exception;
excep1 exception;
excep2 exception;
excep3 exception;
excep4 exception;
v_counter number(10) := 0;
begin
open c1;
loop
fetch c1 into emp_na, emp_sal;
v_counter := v_counter + 1;
exit when v_counter = 1 or c1%notfound;
end loop;
close c1;
if emp_sal = 25000 and emp_job = 'AD_PRES' then raise excep;
elsif emp_sal > 25000 and emp_job = 'AD_PRES' then raise excep1;
elsif emp_sal < 25000 and emp_job = 'AD_PRES' then raise excep2;
elsif emp_sal = 3100 and emp_job = 'PU_CLERK' then raise excep3;
elsif emp_sal > 3100 and emp_job = 'PU_CLERK' then raise excep4;
end if;
exception
when excep then
dbms_output.put_line('AD_PRES already have the salary');
when excep1 then
dbms_output.put_line('AD_PRES greater than the update salary');
when excep2 then
update employees
set salary = 26000
where employee_id = emp_id;
when excep3 then
update employees
set salary = 4000
where employee_id = emp_id;
when excep4 then
dbms_output.put_line('PU_CLERK greater than the update salary');
when no_data_found then
dbms_output.put_line('NO RECORD FOUND');
end proc1;