StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I would not use an explicit cursor to do this. Steve F. no longer advises people to use explicit cursors when an implicit cursor could be used. </p> <p>The method with <code>count(*)</code> is unsafe. If another session deletes the row that met the condition after the line with the <code>count(*)</code>, and before the line with the <code>select ... into</code>, the code will throw an exception that will not get handled.</p> <p>The second version from the original post does not have this problem, and it is generally preferred.</p> <p>That said, there is a minor overhead using the exception, and if you are 100% sure the data will not change, you can use the <code>count(*)</code>, but I recommend against it.</p> <p>I ran these benchmarks on <em>Oracle 10.2.0.1</em> on <em>32 bit Windows</em>. I am only looking at elapsed time. There are other test harnesses that can give more details (such as latch counts and memory used).</p> <pre><code>SQL>create table t (NEEDED_FIELD number, COND number); </code></pre> <blockquote> <p>Table created.</p> </blockquote> <pre><code>SQL>insert into t (NEEDED_FIELD, cond) values (1, 0); </code></pre> <blockquote> <p>1 row created.</p> </blockquote> <pre><code>declare otherVar number; cnt number; begin for i in 1 .. 50000 loop select count(*) into cnt from t where cond = 1; if (cnt = 1) then select NEEDED_FIELD INTO otherVar from t where cond = 1; else otherVar := 0; end if; end loop; end; / </code></pre> <blockquote> <p>PL/SQL procedure successfully completed.</p> <p>Elapsed: <strong>00:00:02.70</strong></p> </blockquote> <pre><code>declare otherVar number; begin for i in 1 .. 50000 loop begin select NEEDED_FIELD INTO otherVar from t where cond = 1; exception when no_data_found then otherVar := 0; end; end loop; end; / </code></pre> <blockquote> <p>PL/SQL procedure successfully completed.</p> <p>Elapsed: <strong>00:00:03.06</strong></p> </blockquote>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload