Subscribe

RSS Feed (xml)

Powered By

Skin Design:
Free Blogger Skins

Powered by Blogger

oracle search

Wednesday, August 6, 2008

Just, Why? ....oracle

I'm currently debugging a procedure of the form illustrated below, lovingly recreated using the ubiquitous scott.emp table. I'm also taking suggestions on what might've been going through the head of the person who wrote it.

RENAME emp TO emp_table
/
CREATE OR REPLACE VIEW emp
AS
SELECT *
FROM emp_table
/
CREATE OR REPLACE TRIGGER mod_emp
INSTEAD OF UPDATE
ON emp
FOR EACH ROW
DECLARE
new_emp emp_table%ROWTYPE;
old_emp emp_table%ROWTYPE;
BEGIN
new_emp.empno := :NEW.empno;
new_emp.ename := :NEW.ename;
new_emp.job := :NEW.job;
new_emp.mgr := :NEW.mgr;
new_emp.hiredate := :NEW.hiredate;
new_emp.sal := :NEW.sal;
new_emp.comm := :NEW.comm;
new_emp.deptno := :NEW.deptno;
old_emp.empno := :OLD.empno;
old_emp.ename := :OLD.ename;
old_emp.job := :OLD.job;
old_emp.mgr := :OLD.mgr;
old_emp.hiredate := :OLD.hiredate;
old_emp.sal := :OLD.sal;
old_emp.comm := :OLD.comm;
old_emp.deptno := :OLD.deptno;
update_emp (old_emp, new_emp);
END;
/

CREATE OR REPLACE PROCEDURE update_emp (
old_emp emp_table%ROWTYPE
,new_emp emp_table%ROWTYPE
)
IS
BEGIN
IF old_emp.empno != new_emp.empno
THEN
RETURN; --can't update primary key attribute
END IF;

IF old_emp.empno IS NOT NULL
AND new_emp.empno IS NULL
THEN
DELETE FROM emp_table
WHERE empno = old_emp.empno;

RETURN;
END IF;

IF ( old_emp.ename IS NULL
AND new_emp.ename IS NOT NULL)
OR ( old_emp.ename IS NOT NULL
AND new_emp.ename != old_emp.ename)
THEN
UPDATE emp_table
SET ename = new_emp.ename
WHERE empno = old_emp.empno;
END IF;
-- ..
-- ..
-- ..
IF ( old_emp.deptno IS NULL
AND new_emp.deptno IS NOT NULL)
OR ( old_emp.deptno IS NOT NULL
AND new_emp.deptno != old_emp.deptno)
THEN
UPDATE emp_table
SET deptno = new_emp.deptno
WHERE empno = old_emp.empno;
END IF;
END;
/

No comments: