hello Tom,
i wrote that mentioned plsql update salary procedure.It compiled also successfully.But it didn't change any of my salary row.Actually,I am new in programming.I want to know .how does is work.
how can i confirm that it is working & how can i see the result.
thanks
Baizeed rony
in below my procedure.
------------------------
create or replace procedure UpdateAllSalary
IS
CURSOR C_EMPSALARY_CURSOR IS SELECT * FROM HR_MONTHLYSALARY
FOR UPDATE OF BASIC;
R_EMP C_EMPSALARY_CURSOR%ROWTYPE;
BEGIN
OPEN C_EMPSALARY_CURSOR;
LOOP
FETCH C_EMPSALARY_CURSOR INTO R_EMP;
EXIT WHEN C_EMPSALARY_CURSOR%NOTFOUND;
update hr_monthlysalary
set basic=BASIC+111
where CURRENT OF C_EMPSALARY_CURSOR;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20000, 'ERROR');
CLOSE C_EMPSALARY_CURSOR;
END;
Maybe you can't see the change because your manager hasn't approved your salary increase? ;)
Anyway, your code looks like it's incrementing every row's salary by 111 to me. Query the data before and after to verify.
Subbing in the classic employees table and it does the job for me:
select employee_id, salary from hr.employees
where department_id = 20;
EMPLOYEE_ID SALARY
201 13000
202 6000
declare
cursor c_empsalary_cursor is
select *
from hr.employees
where department_id = 20
for update of salary;
r_emp c_empsalary_cursor%rowtype;
begin
open c_empsalary_cursor;
loop
fetch c_empsalary_cursor into r_emp;
exit when c_empsalary_cursor%notfound;
update hr.employees
set salary = salary + 111
where current of c_empsalary_cursor;
end loop;
exception
when others then
raise_application_error(-20000,'ERROR' );
close c_empsalary_cursor;
end;
/
select employee_id,
salary
from hr.employees
where department_id = 20;
EMPLOYEE_ID SALARY
201 13111
202 6111If you're doing this in a procedure you need to execute it to do the update:
exec UpdateAllSalary;
But it is a lot of unnecessary code!
An update changes the values of all rows that match your where clause. So all you need is a single update:
rollback;
select employee_id,
salary
from hr.employees
where department_id = 20;
EMPLOYEE_ID SALARY
201 13000
202 6000
update hr.employees
set salary = salary + 111;
select employee_id,
salary
from hr.employees
where department_id = 20;
EMPLOYEE_ID SALARY
201 13111
202 6111