Posted by I had an interesting question from a previous work colleague. She had a need to perform a query on attribute pairs. For the sake of discussion, we’ll assume that pairs are a shopping centre name, and a checkout aisle in that shopping centre.
So for example, on screen, a user may say:
Show me the sales coming through the following checkout aisles:
Perth,17 Wembley,32 Floreat,11
She had the beginnings of a package to solve the problem, but got stuck at when it come to referencing the two sets of attributes
SQL> create or replace package DEMO is
2
3 type t_numbers is table of number(5)
4 index by pls_integer;
5
6 type t_strings is table of varchar(20)
7 index by pls_integer;
8
9 procedure list(p_center t_strings, p_checkout t_numbers);
10
11 end;
12 /
Package created.
SQL> create or replace package body DEMO is
2
3 procedure list(p_center t_strings, p_checkout t_numbers) is
4 begin
5 for i in
6 (
7 select *
8 from shopping_stats
9 -- where (center,checkout_num) in
10 -- ( ???? p_center,p_checkout ????)
11 )
12 loop
13 dbms_output.put_line(i.center||','||i.checkout_num||','||i.sales_total);
14 end loop;
15 end;
16
17 end;
18 /
Package body created.
So here was a simple solution for her, which also satisfied the need of not changing the calling interface.
Step 1: We create some database types to match the required structure
SQL> create or replace type each_parameter_row as object (
2 center varchar2(20),
3 checkout_num number(5)
4 );
5 /
Type created.
SQL> create or replace type list_of_parameters as
2 table of each_parameter_row
3 /
Type created.
Step 2: Create a pipelined function to map the plsql array to the new database types
function parms_as_table return list_of_parameters pipelined is
begin
for i in p_center.first .. p_center.last loop
pipe row ( each_parameter_row(p_center(i),p_checkout(i)) );
end loop;
return;
end;
Step 3: Change the base code to query from the output of that function
procedure list(p_center t_strings, p_checkout t_numbers) is
begin
for i in
(
select *
from shopping_stats
where (center, checkout_num) in
( select center, checkout_num
from table(demo.parms_as_table)
)
)
loop
dbms_output.put_line(i.center||','||i.checkout_num||','||i.sales_total);
end loop;
end;
And now we can put it all together…
SQL> create or replace package DEMO is
2
3 type t_numbers is table of number(12)
4 index by pls_integer;
5
6 type t_strings is table of varchar(20)
7 index by pls_integer;
8
9 function parms_as_table(p_center t_strings, p_checkout t_numbers) return list_of_parameters pipelined;
10
11 procedure list(p_center t_strings, p_checkout t_numbers);
12
13 end;
14 /
Package created.
SQL> create or replace package body DEMO is
2
3 function parms_as_table(p_center t_strings, p_checkout t_numbers)
4 return list_of_parameters pipelined is
5 begin
6 for i in p_center.first .. p_center.last loop
7 pipe row ( each_parameter_row(p_center(i),p_checkout(i)) );
8 end loop;
9 return;
10 end;
11
12 procedure list(p_center t_strings, p_checkout t_numbers) is
13 begin
14 for i in
15 (
16 select *
17 from shopping_stats
18 where (center, checkout_num) in
19 ( select center, checkout_num
20 from table(demo.parms_as_table(p_center, p_checkout))
21 )
22 )
23 loop
24 dbms_output.put_line(i.center||','||i.checkout_num||','||i.sales_total);
25 end loop;
26 end;
27
28 end;
29 /
Package body created.
And of course then write a unit test 🙂
SQL> insert into shopping_stats values ('Perth',17,100);
1 row created.
SQL> insert into shopping_stats values ('Wembley',32,110);
1 row created.
SQL> insert into shopping_stats values ('Floreat',11,120);
1 row created.
SQL>
SQL>
SQL> set serverout on
SQL> declare
2 p1 demo.t_strings;
3 p2 demo.t_numbers;
4 begin
5 p1(1) := 'Perth';
6 p2(1) := 17;
7
8 p1(2) := 'Wembley';
9 p2(2) := 32;
10
11 demo.list(p1,p2);
12 end;
13 /
Wembley,32,110
Perth,17,100
PL/SQL procedure successfully completed.
SQL>
SQL> roll;
Rollback complete.
Learning is not a spectator sport 