{"id":220,"date":"2008-02-07T19:37:49","date_gmt":"2008-02-07T18:37:49","guid":{"rendered":"http:\/\/bar-solutions.com\/wordpress\/?p=220"},"modified":"2008-02-15T13:53:35","modified_gmt":"2008-02-15T12:53:35","slug":"blogging-block","status":"publish","type":"post","link":"https:\/\/blog.bar-solutions.com\/?p=220","title":{"rendered":"Blogging block…"},"content":{"rendered":"

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<\/a> 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.<\/p>\n

It did however teach me new usage of SQL, for instance the WITH clause<\/a> which can be used instead of inline views and I recently came across a use of the update statement I didn’t know of. <\/p>\n<\/p>\n

\n
UPDATE<\/span> emp a\r\n   <\/span>SET<\/span> ( sal, comm ) <\/span>=<\/span> ( <\/span>SELECT<\/span> sal <\/span>*<\/span> <\/span>1.5<\/span> newsal\r\n                              , NVL( comm, <\/span>0<\/span> ) <\/span>+<\/span> <\/span>100<\/span> newcomm\r\n                          <\/span>FROM<\/span> emp b\r\n                         <\/span>WHERE<\/span> a.empno <\/span>=<\/span> b.empno );\r\n<\/span><\/div><\/pre>\n
<\/div>\n
<\/p>\n
I was used to update statements like this:
\n  <\/p>\n
\n
update<\/span> emp a\r\n   <\/span>set<\/span> sal <\/span>=<\/span> sal <\/span>*<\/span> <\/span>1.5<\/span>;\r\n<\/span><\/div><\/pre>\n
<\/div>\n<\/p>\n
And if you wanted to update two columns, just add another update statement:
\n  <\/p>\n
\n
update<\/span> emp a\r\n   <\/span>set<\/span> sal <\/span>=<\/span> sal <\/span>*<\/span> <\/span>1.5<\/span>;\r\n<\/span><\/div><\/pre>\n
<\/div>\n<\/p>\n
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:
\n  <\/p>\n
\n
begin<\/span>\r\n  <\/span>for<\/span> rec <\/span>in<\/span> (<\/span>select<\/span> empno\r\n                   , sal <\/span>*<\/span> <\/span>1.5<\/span> newsal\r\n                   , nvl (comm, <\/span>0<\/span> ) <\/span>+<\/span> <\/span>100<\/span> newcomm\r\n                <\/span>from<\/span> emp) <\/span>loop<\/span>\r\n    <\/span>update<\/span> emp\r\n       <\/span>set<\/span> emp.sal <\/span>=<\/span> rec.newsal\r\n         , emp.comm <\/span>=<\/span> rec.newcomm\r\n     <\/span>where<\/span> emp.empno <\/span>=<\/span> rec.empno;\r\n  <\/span>end<\/span> <\/span>loop<\/span>;\r\n<\/span>end<\/span>;\r\n<\/span>\/<\/span>\r\n<\/span><\/div><\/pre>\n
<\/div>\n<\/p>\n
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.
\n  
To speed things up a bit, I would use bulk operations to minimize the round trips to and from the database: <\/p>\n
<\/p>\n
\n
declare<\/span>\r\n  <\/span>--<\/span> type declarations<\/span>\r\n<\/span>  <\/span>type<\/span> sal_tp <\/span>is<\/span> <\/span>table<\/span> <\/span>of<\/span> emp.sal%<\/span>type<\/span> <\/span>index<\/span> <\/span>by<\/span> <\/span>binary_integer<\/span>;\r\n  <\/span>type<\/span> comm_tp <\/span>is<\/span> <\/span>table<\/span> <\/span>of<\/span> emp.comm%<\/span>type<\/span> <\/span>index<\/span> <\/span>by<\/span> <\/span>binary_integer<\/span>;\r\n  <\/span>type<\/span> empno_tp <\/span>is<\/span> <\/span>table<\/span> <\/span>of<\/span> emp.empno%<\/span>type<\/span> <\/span>index<\/span> <\/span>by<\/span> <\/span>binary_integer<\/span>;\r\n\r\n  <\/span>--<\/span> variable based on previously declared types<\/span>\r\n<\/span>  empsal sal_tp;\r\n  empcomm comm_tp;\r\n  empempno empno_tp;\r\n<\/span>begin<\/span>\r\n  <\/span>--<\/span> select the values using bulk collect to get them into the local variables<\/span>\r\n<\/span>  <\/span>select<\/span> empno\r\n       , sal <\/span>*<\/span> <\/span>1.5<\/span> newsal\r\n       , nvl (comm, <\/span>0<\/span> ) <\/span>+<\/span> <\/span>100<\/span> newcomm\r\n   <\/span>bulk<\/span> <\/span>collect<\/span> <\/span>into<\/span> empempno, empsal, empcomm\r\n   <\/span>from<\/span> emp;\r\n\r\n  <\/span>--<\/span> use bulk update statement to update the database<\/span>\r\n<\/span>  <\/span>forall<\/span> i <\/span>in<\/span> empempno.first .. empempno.last\r\n    <\/span>update<\/span> emp\r\n       <\/span>set<\/span> sal <\/span>=<\/span> empsal(i)\r\n         , comm <\/span>=<\/span> empcomm(i)\r\n     <\/span>where<\/span> empno <\/span>=<\/span> empempno(i);\r\n<\/span>end<\/span>;\r\n<\/span>\/<\/span>\r\n<\/span><\/div><\/pre>\n
<\/div>\n<\/p>\n
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:<\/p>\n<\/p>\n
\n
declare<\/span>\r\n  <\/span>--<\/span> table collection<\/span>\r\n<\/span>  emps emp_tp.emp_tc;\r\n\r\n  <\/span>--<\/span> column collections<\/span>\r\n<\/span>  empsal emp_tp.SAL_cc;\r\n  empcomm emp_tp.COMM_cc;\r\n  empempno emp_tp.EMPNO_cc;\r\n<\/span>begin<\/span>\r\n  <\/span>--<\/span> get all the values from the table in a table collection<\/span>\r\n<\/span>  emps :<\/span>=<\/span> emp_qp.allrows;\r\n\r\n  <\/span>--<\/span> move the individual columns to their own column collection<\/span>\r\n<\/span>  <\/span>for<\/span> i <\/span>in<\/span> emps.first .. emps.last <\/span>loop<\/span>\r\n    empsal(i) :<\/span>=<\/span> emps(i).sal <\/span>*<\/span> <\/span>1.5<\/span>;\r\n    empcomm(i) :<\/span>=<\/span> nvl(emps(i).comm, <\/span>0<\/span>) <\/span>+<\/span> <\/span>100<\/span>;\r\n    empempno(i) :<\/span>=<\/span> emps(i).empno;\r\n  <\/span>end<\/span> <\/span>loop<\/span>;\r\n\r\n  <\/span>--<\/span> use bulk update statement to update the database<\/span>\r\n<\/span>  <\/span>forall<\/span> i <\/span>in<\/span> empempno.first .. empempno.last\r\n    <\/span>update<\/span> emp\r\n       <\/span>set<\/span> sal <\/span>=<\/span> empsal(i)\r\n         , comm <\/span>=<\/span> empcomm(i)\r\n     <\/span>where<\/span> empno <\/span>=<\/span> empempno(i);\r\n<\/span>end<\/span>;\r\n<\/span>\/<\/span>\r\n<\/span><\/div><\/pre>\n
<\/div>\n<\/p>\n
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.<\/p>\n
Hey, it seems that I didn’t have a real bloggers block after all. \ud83d\ude09<\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[12,5,9],"tags":[],"class_list":["post-220","post","type-post","status-publish","format-standard","hentry","category-codegen","category-plsql","category-sql"],"_links":{"self":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts\/220","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=220"}],"version-history":[{"count":0,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts\/220\/revisions"}],"wp:attachment":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=220"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=220"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=220"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}