Thanks for the question, Don.
Asked: January 26, 2017 - 4:09 pm UTC
Last updated: January 26, 2017 - 11:52 pm UTC
Version: 11g
Viewed 10K+ times! This question is
-- table of test data create table foo (test_nbr number(19,0), is_prime char(1)); insert into foo values (1, 'N'); insert into foo values (2, 'Y'); insert into foo values (3, 'Y'); insert into foo values (4, 'N'); insert into foo values (5, 'Y'); commit; / declare type foo_type is record (x foo.test_nbr%type); type bar is table of foo_type; prime_not bar; prime_yes bar; begin null; select test_nbr bulk collect into prime_not from foo where is_prime = 'N'; select test_nbr bulk collect into prime_yes from foo where is_prime = 'Y'; for i in 1 .. prime_not.count loop DBMS_OUTPUT.put_line('Not Prime: ' || prime_not(i).x); end loop i; for i in 1 .. prime_yes.count loop DBMS_OUTPUT.put_line('Is Prime: ' || prime_yes(i).x); end loop i; end; / drop table foo; /
create table foo (test_nbr number(19,0), is_prime char(1)); insert into foo values (1, 'N'); insert into foo values (2, 'Y'); insert into foo values (3, 'Y'); insert into foo values (4, 'N'); insert into foo values (5, 'Y'); commit; / create or replace type tp as table of integer; / declare prime tp; not_prime tp; begin select cast(collect(case when is_prime = 'Y' then test_nbr end) as tp), cast(collect(case when is_prime = 'N' then test_nbr end) as tp) into prime, not_prime from foo; for i in prime.first .. prime.last loop dbms_output.put_line('PRIME: ' || prime(i)); end loop; for i in not_prime.first .. not_prime.last loop dbms_output.put_line('NOT PRIME: ' || not_prime(i)); end loop; end; / PRIME: 2 PRIME: 3 PRIME: 5 NOT PRIME: 1 NOT PRIME: 4
Don Simpson, January 26, 2017 - 5:07 pm UTC
Check out more PL/SQL tutorials on our LiveSQL tool.
PL/SQL reference manual from the Oracle documentation library