Oracle SELECT with an array using an IN
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
|
declare
-- CREATE OR REPLACE TYPE PROD_TYPES.TYPE_NUMBER_ARRAY AS TABLE OF NUMBER
nt_src prod_types.type_number_array := PROD_TYPES.TYPE_NUMBER_ARRAY(); -- Constructor
nt_dest prod_types.type_number_array := PROD_TYPES.TYPE_NUMBER_ARRAY(); -- Constructor
begin
-- Test data (no order)
select
product_version_id
bulk collect
into
nt_src
from
product_version v
where
v.product_version_id between
138120 and
140000;
-- select * from table(sys.dbms_debug_vc2coll(1,2,'a')) order by 1 desc
-- Data ordered
select
product_version_id
bulk collect
into
nt_dest
from
product_version
where
product_version.product_version_id in
(select
column_value from
table(cast(nt_src AS
prod_types.type_number_array)))
order
by
product_version.expiration_date2 desc,
product_version.base_price desc,
product_version.expiration_date2 desc;
for
i in
nt_dest.first
.. nt_dest.last
loop
dbms_output.put_line(to_char(nt_dest(i)));
end
loop;
exception
when
others then
dbms_output.put_line('sqlerrm : '||sqlerrm);
|
1422
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
