On 9/28/05, Ranko Mosic <ranko.mosic@(protected)> wrote: > > Hi, > requirement: > - input parameters are codes p_cd1, p_cd2, ... > - for these codes I get descriptions ( select descr1 into v_descr1 from > lkp_table1 where cd = p_cd1; select descr2 into v_descr2 from lkp_table2 where > cd = p_cd2 etc ) > - check if table t has records where t.descr1 = v_descr1 > and t.descr2 = v_descr2 and on and on ....; > - if row exists return primary key; > - if not then insert. > > What is the best way of doing it ( simplest ) ? > > Regards, Ranko. > > > Ranko,
"Simplest way" is to solicit opinions without using a search engine or checking the documentation. Its also usually "simplest" to leverage the existing provided functionality, rather than writing your own routines, error handling, etc.
A search of "oracle 10.1 upsert" in google.com <http://google.com> + "I'm feeling lucky" produced this for me. Perhaps you might get lucky too.
Paul
http://www.psoug.org/reference/merge.html
MERGE <hint> INTO <table_name> USING <table_view_or_query> ON (<condition>) WHEN MATCHED THEN <update_clause> WHEN NOT MATCHED THEN <insert_clause>;
On 9/28/05, <b class="gmail_sendername">Ranko Mosic</b> <<a href="mailto :ranko.mosic@(protected)">ranko.mosic@(protected)</a>> wrote:<div><span class= "gmail_quote"></span><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"> <pre>Hi, <br>requirement: <br>- input parameters are codes p_cd1, p_cd2, ...<br >- for these codes I get descriptions ( select descr1 into v_descr1 from <br>lkp _table1 where cd = p_cd1; select descr2 into v_descr2 from lkp_table2 where <br>cd = p_cd2 etc )<br>- check if table t has records where t.descr1 = v _descr1<br> and t.descr2 = v_descr2 and on and on ....; <br>- if row exists return primary key; <br>- if not then insert. <br><br>What is the best way of doing it ( simplest ) ? <br><br>Regards, Ranko. <br></pre><br>
</blockquote></div><br>Ranko,<br> <br> "Simplest way" is to solicit opinions without using a search engine or checking the documentation.<br> Its also usually "simplest" to leverage the existing provided functionality, rather than writing your own routines, error handling, etc.<br> <br> A search of "oracle 10.1 upsert" in <a href="http://google.com" >google.com</a> + "I'm feeling lucky" produced this for me.<br> Perhaps you might get lucky too.<br> <br> Paul<br> <br> <a href="http://www.psoug.org/reference/merge.html">http://www.psoug.org /reference/merge.html</a><br> <br> <table bgcolor="#d8d8c4" border="1" width="90%"> <tbody><tr><td rowspan="2" width="25%"><br> </td> <td width="75%"><font face="Courier">MERGE <hint> INTO <table _name><br> USING <table_view_or_query><br> ON (<condition>)<br> WHEN MATCHED THEN <update_clause><br> WHEN NOT MATCHED THEN <insert_clause>;</font></td></tr></tbody> </table> <br>
