StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Use ...from table(... :</p> <pre><code>create or replace type numbertype as object (nr number(20,10) ) / create or replace type number_table as table of numbertype / create or replace procedure tableselect ( p_numbers in number_table , p_ref_result out sys_refcursor) is begin open p_ref_result for select * from employees , (select /*+ cardinality(tab 10) */ tab.nr from table(p_numbers) tab) tbnrs where id = tbnrs.nr; end; / </code></pre> <p>This is one of the rare cases where you need a hint, else Oracle will not use the index on column id. One of the advantages of this approach is that Oracle doesn't need to hard parse the query again and again. Using a temporary table is most of the times slower. </p> <p><strong>edit 1</strong> simplified the procedure (thanks to jimmyorr) + example</p> <pre><code>create or replace procedure tableselect ( p_numbers in number_table , p_ref_result out sys_refcursor) is begin open p_ref_result for select /*+ cardinality(tab 10) */ emp.* from employees emp , table(p_numbers) tab where tab.nr = id; end; / </code></pre> <p>Example:</p> <pre><code>set serveroutput on create table employees ( id number(10),name varchar2(100)); insert into employees values (3,'Raymond'); insert into employees values (4,'Hans'); commit; declare l_number number_table := number_table(); l_sys_refcursor sys_refcursor; l_employee employees%rowtype; begin l_number.extend; l_number(1) := numbertype(3); l_number.extend; l_number(2) := numbertype(4); tableselect(l_number, l_sys_refcursor); loop fetch l_sys_refcursor into l_employee; exit when l_sys_refcursor%notfound; dbms_output.put_line(l_employee.name); end loop; close l_sys_refcursor; end; / </code></pre> <p>This will output:</p> <pre><code>Raymond Hans </code></pre>

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