Developing Simple PL/SQL Stored Procedures for CRUD Operations - How to update a row in a table using a PL/SQL stored procedure
(Page 4 of 5 )
The following is the stored procedure which can be used to modify the information belonging to an employee:
create or replace procedure p_emp_update (p_empno emp.empno%type,
p_ename emp.ename%type, p_sal emp.sal%type, p_deptno emp.deptno%
type) as
Invalid_sal exception;
Invalid_deptno exception;
Invalid_empno exception;
begin
if p_sal<100 or p_sal>10000 then
raise invalid_sal;
end if;
declare
dummy_var varchar(10);
begin
select 'exists' into dummy_var
from dept
where deptno = p_deptno;
exception
when no_data_found then
raise Invalid_deptno;
when others then
raise_application_error(-20011, sqlerrm);
end;
update emp set
ename=p_ename,
sal=p_sal,
deptno=p_deptno
where empno=p_empno;
if sql%notfound or sql%rowcount=0 then
rollback;
raise Invalid_empno;
end if;
commit;
exception
when invalid_sal then
raise_application_error(-20001, 'Salary must be in
between 100 and 10000');
when invalid_deptno then
raise_application_error(-20001, 'Department doesn't
exist');
when invalid_empno then
raise_application_error(-20001, 'Employee does not
exist');
when others then
raise_application_error(-20011, sqlerrm);
end;
/
In the above stored procedure, the following is the code fragment which needs some attention:
if sql%notfound or sql%rowcount=0 then
rollback;
raise Invalid_empno;
end if;
The above IF condition evaluates whether the previous DML statement (UPDATE in this scenario) updated any information successfully or not.
Next: Deleting and retrieving values using PL/SQL stored procedures >>
More Oracle Articles
More By Jagadish Chatarji
