Re: Arbitrary amount of BETWEENs
- From: "phil_herring@xxxxxxxxxxxx" <phil_herring@xxxxxxxxxxxx>
- Date: Mon, 28 Apr 2008 17:01:23 -0700 (PDT)
Something like this, maybe?
SQL> create table test_pairs
2 (
3 what_we_are_looking_for varchar2(100),
4 the_key integer
5 );
Table created.
SQL>
SQL> insert into test_pairs (what_we_are_looking_for, the_key) values
('one', 1);
1 row created.
SQL>
SQL> insert into test_pairs (what_we_are_looking_for, the_key) values
('two', 2);
1 row created.
SQL>
SQL> insert into test_pairs (what_we_are_looking_for, the_key) values
('three', 3);
1 row created.
SQL>
SQL> insert into test_pairs (what_we_are_looking_for, the_key) values
('four', 4);
1 row created.
SQL>
SQL> insert into test_pairs (what_we_are_looking_for, the_key) values
('five', 5);
1 row created.
SQL>
SQL> insert into test_pairs (what_we_are_looking_for, the_key) values
('six', 6);
1 row created.
SQL>
SQL> insert into test_pairs (what_we_are_looking_for, the_key) values
('seven', 7);
1 row created.
SQL>
SQL> insert into test_pairs (what_we_are_looking_for, the_key) values
('eight', 8);
1 row created.
SQL>
SQL> insert into test_pairs (what_we_are_looking_for, the_key) values
('nine', 9);
1 row created.
SQL>
SQL> insert into test_pairs (what_we_are_looking_for, the_key) values
('ten', 10);
1 row created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> create or replace type pair_of_integers_typ as object
2 (
3 integer_1 integer,
4 integer_2 integer
5 );
6 /
Type created.
SQL>
SQL> create or replace type pair_of_integers_tab as table of
pair_of_integers_typ;
2 /
Type created.
SQL>
SQL> set serveroutput on
SQL>
SQL> declare
2
3 v_pairs_of_integers pair_of_integers_tab :=
pair_of_integers_tab();
4 rec_test_pairs test_pairs%rowtype;
5
6 begin
7
8 v_pairs_of_integers.extend;
9 v_pairs_of_integers(1) := pair_of_integers_typ(4, 5);
10
11 v_pairs_of_integers.extend;
12 v_pairs_of_integers(2) := pair_of_integers_typ(7, 8);
13
14 for rec_test_pairs in
15 (
16 select
17 *
18 from
19 test_pairs tp,
20 table(cast(v_pairs_of_integers as
pair_of_integers_tab)) ranges
21 where
22 tp.the_key between ranges.integer_1 and
ranges.integer_2
23 ) loop
24
25
dbms_output.put_line(rec_test_pairs.what_we_are_looking_for || ' ' ||
rec_test_pairs.the_key);
26
27 end loop;
28
29 end;
30 /
four 4
five 5
seven 7
eight 8
PL/SQL procedure successfully completed.
SQL>
-- Phil
.
- Follow-Ups:
- Re: Arbitrary amount of BETWEENs
- From: dee
- Re: Arbitrary amount of BETWEENs
- References:
- Arbitrary amount of BETWEENs
- From: dee
- Arbitrary amount of BETWEENs
- Prev by Date: Re: Assessing Customer Satisfaction and Agile Project Management - PhD Dissertation
- Next by Date: Re: Post-Install of HTML DB
- Previous by thread: Arbitrary amount of BETWEENs
- Next by thread: Re: Arbitrary amount of BETWEENs
- Index(es):
Relevant Pages
|
