I seem to be suffering from a blogging block (like a writers block, but for a blogger). Maybe it’s my new job at AMIS or the new application at the KPN I am working on. It’s a lot of figuring out what is going on. Reading through other peoples code and try to understand their mind setting and the path they followed.
It did however teach me new usage of SQL, for instance the WITH clause which can be used instead of inline views and I recently came across a use of the update statement I didn’t know of.
UPDATE emp a SET ( sal, comm ) = ( SELECT sal * 1.5 newsal , NVL( comm, 0 ) + 100 newcomm FROM emp b WHERE a.empno = b.empno );
I was used to update statements like this:
update emp a set sal = sal * 1.5;
And if you wanted to update two columns, just add another update statement:
update emp a set sal = sal * 1.5;
But, if you would want to update a lot of columns this would involve a lot of statements, hence a lot of round trips to the database. Since I am a PL/SQL guy, I think I would have written a small loop to accomplish the same result:
begin for rec in (select empno , sal * 1.5 newsal , nvl (comm, 0 ) + 100 newcomm from emp) loop update emp set emp.sal = rec.newsal , emp.comm = rec.newcomm where emp.empno = rec.empno; end loop; end; /
This would require even more round trips to the database, but it’s a bit more readable (I think). Maybe not for just two columns, but when you have more columns, it might be more readable.
To speed things up a bit, I would use bulk operations to minimize the round trips to and from the database:
declare -- type declarations type sal_tp is table of emp.sal%type index by binary_integer; type comm_tp is table of emp.comm%type index by binary_integer; type empno_tp is table of emp.empno%type index by binary_integer; -- variable based on previously declared types empsal sal_tp; empcomm comm_tp; empempno empno_tp; begin -- select the values using bulk collect to get them into the local variables select empno , sal * 1.5 newsal , nvl (comm, 0 ) + 100 newcomm bulk collect into empempno, empsal, empcomm from emp; -- use bulk update statement to update the database forall i in empempno.first .. empempno.last update emp set sal = empsal(i) , comm = empcomm(i) where empno = empempno(i); end; /
But, for every column I’d use, I would need to create a type and a variable. If I were to use the QDA on my tables, the code would look something like this:
declare -- table collection emps emp_tp.emp_tc; -- column collections empsal emp_tp.SAL_cc; empcomm emp_tp.COMM_cc; empempno emp_tp.EMPNO_cc; begin -- get all the values from the table in a table collection emps := emp_qp.allrows; -- move the individual columns to their own column collection for i in emps.first .. emps.last loop empsal(i) := emps(i).sal * 1.5; empcomm(i) := nvl(emps(i).comm, 0) + 100; empempno(i) := emps(i).empno; end loop; -- use bulk update statement to update the database forall i in empempno.first .. empempno.last update emp set sal = empsal(i) , comm = empcomm(i) where empno = empempno(i); end; /
All of these examples result in the same outcome, but one takes more time than the other. Depending on the calculations that need to done on the columns you might choose to do this is SQL or in PLSQL. I think most of the real world issues can be solved by just using the SQL approach, but as you can see, there are more ways to solve a problem.
Hey, it seems that I didn’t have a real bloggers block after all. 😉