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;
/
Wednesday, September 13, 2006
Just, Why?
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.
Subscribe to:
Post Comments (Atom)
14 comments:
That's easy. They were told to redesign the database schema without changing existing client code.
It looks like they only got as far as putting a proxy system in place without actually changing the database tables.
everbody knows that this sort of thing shouldn't happen. that's why we have ORM tools like hibernate. ;P
I'm pretty torn about Hibernate. On the one hand, it lets me ignore 90% of the SQL and focus on the 10% that matters. On the other hand, it can be painfully oblique -- especially its exceptions.
What was going through the programmers mind? Who knows, but maybe they should lay off the drugs!
Looks to me like this was an attempt at "optimization" - only updating the columns that have actually been changed. Just a shame that if you change 20 columns, you get 20 separate updates... so more like "pessimization" really.
personally, im still on the fence on the whole ORM thing. part of me screams that its just a bad idea. but on the other hand, I can see it being very useful in some simple cases, but then, if the cases are simple what's wrong with just hand coding some data access mechanisms....
I can't help feeling the 'IF old_emp.empno != new_emp.empno THEN RETURN' would be nicely complemented by a classic 'WHEN OTHERS THEN NULL', or perhaps a bit of code to raise a random error on the third day after bill run when the month has an 'A' in it.
I'm probably biased, but my experience of Hibernate goes like this...
Web Developer: The database contains wrong/no data.
Oracle Developer: I see data, what SP are you calling / SQL are you running?
Web Developer: I'm using Hibernate, it doesn't show you the SQL.
Oracle Developer: Sorry, can't really help too much then.
...at which point the Web Developer retires to his desk to spend a week transfixed without food or water, emerging some time later to announce that he has fixed a 'problem' with the Hibernate mappings.
Which is presumably where the name Hibernate comes from?
"...emerging some time later to announce that he has fixed a 'problem' with the Hibernate mappings."
...And isn't the "fix" normally to replace the rows and columns in the tables with Bee-Lobs that the application "manages" itself?
Padders, If the developer doesn't know how to see the SQL that Hibernate generates he/she ought to be shown the door. Or or you could take pity on the poor bastard and type "hibernate show sql" into google.
Covenant, I'm torn as well. Though I have to admit that just now it's serving me well. I have a WebServiceCommand class that I've sub-classed in many ways to represent the various dialogs we need. Then I can store them all via Hibernate and fetch them when the web service is up (not often enough thank you very much) and simply call "execute()" on each of them. Hibernate handles all of the sub-classing for me such that I don't have to keep track of what execute() does for any of the various commands. 'Course catch me in a week and I'll be cursing it again.
I might be missing something but my guess would be that this code predates "rename table" and has never been changed.
From the days when you could create an INSTEAD-OF trigger but not rename a table, you mean?
What's wrong with storing all your data in xml in Bee-Lobs anyway? If they are in an Oracle table, then surely that's relational enough for most people!
At lunch, they were thinking:
"One more beer won't hurt, will it?"
That's something my *project manager* could have written, and checked into the source repository. Without telling the developers of his changes, of course (nor testing it, btw)
Post a Comment