tag:blogger.com,1999:blog-7849367040589270673.post1457863387863459261..comments2024-03-21T22:50:39.997-07:00Comments on Obsessed with Oracle PL/SQL: Don't Return Home Without the RETURNING ClauseSteven Feuersteinhttp://www.blogger.com/profile/18405765731886460622noreply@blogger.comBlogger2125tag:blogger.com,1999:blog-7849367040589270673.post-24043503949419958382016-04-05T04:26:54.969-07:002016-04-05T04:26:54.969-07:00Alsayed:
#1. Thanks so much for pointing out the ...Alsayed:<br /><br />#1. Thanks so much for pointing out the error. I have removed the WHERE clause.<br /><br />#2. There is no difference in performance. I was just a little bit lazy and decided to use a pre-defined collection. From a best practices standpoint, it is probably better to not use the DBMS_SQL types unless you are performing dynamic SQL. Otherwise it will be confusing: "Where's the dynamic SQL?"<br /><br />#3. Thanks for adding that information about populating a collection of records. Excellent point!Steven Feuersteinhttps://www.blogger.com/profile/18405765731886460622noreply@blogger.comtag:blogger.com,1999:blog-7849367040589270673.post-90064146153602803252016-04-04T17:00:27.999-07:002016-04-04T17:00:27.999-07:00Thanks Steven.
------------------
1- a small mispr...Thanks Steven.<br />------------------<br />1- a small misprint in paragraph 4 beginning with ( RETURNING with Multiple Rows Changed ) , you forgot to remove the where clause as you said , so it will work without exceptions<br />------------------<br />2- you used the table of number type defined in DBMS_SQL package , is it equivalent in performance with our defined types in the block or package specification?<br />------------------<br />3- Some readers might be interested , The RETURNING Clause will work also if we used a Collection of records as follows<br /><br />DECLARE<br /> type nt is table of parts%rowtype index by pls_integer;<br /> l_part_numbers nt;<br />BEGIN<br /> UPDATE parts<br /> SET part_name = part_name || '1'<br /> RETURNING part_number,part_name<br /> BULK COLLECT INTO l_part_numbers;<br /><br /> FOR indx IN 1 .. l_part_numbers.COUNT<br /> LOOP<br /> DBMS_OUTPUT.put_line (l_part_numbers (indx).part_name||' , '||l_part_numbers (indx).part_number);<br /> END LOOP;<br />END;<br />/<br />------------------<br />4- The Aggregate Functions with RETURNING is wonderful <br />----------<br />Sorry for taking long , thank youAlsayed Alesaweyhttps://www.blogger.com/profile/09253100489048451302noreply@blogger.com