Passing parameters

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.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s