PL SQL function that includes multiple tables

With some sample data (minimal column set):

SQL> select * from products order by product_id;

PRODUCT_ID PRODUCT_NAME
---------- ----------------
         1 BMW
         2 Audi
SQL> select * From order_items;

PRODUCT_ID CUSTOM   QUANTITY UNIT_PRICE
---------- ------ ---------- ----------
         1 Little        100          1
         1 Little        200          2
         2 Foot          300          3

If we check some totals:

SQL> select o.product_id,
  2       o.customer_id,
  3       sum(o.quantity * o.unit_price) total
  4     from order_items o
  5     group by o.product_id, o.customer_id;

PRODUCT_ID CUSTOM      TOTAL
---------- ------ ----------
         2 Little        400
         1 Little        100
         2 Foot          900

SQL>

It says that

• for customer Little, product 2 was sold with total = 400 - that's our choice for Little
• for customer Little, product 1 was sold with total = 100
• for customer Foot, product 2 was sold with total = 900 - that's our choice for Foot

Query might then look like this:

• temp CTE calculates totals per each customer
• rank_them CTE ranks them in descending order per each customer; row_number so that you get only one product, even if there are ties
  • finally, select the one that ranks as the highest

SQL> with
  2  temp as
  3    (select o.product_id,
  4            o.customer_id,
  5            sum(o.quantity * o.unit_price) total
  6     from order_items o
  7     group by o.product_id, o.customer_id
  8    ),
  9  rank_them as
 10    (select t.customer_id,
 11            t.product_id,
 12            row_number() over (partition by t.customer_id order by t.total desc) rn
 13     from temp t
 14    )
 15  select * From rank_them;

CUSTOM PRODUCT_ID         RN
------ ---------- ----------
Foot            2          1  --> for Foot, product 2 ranks as the highest
Little          2          1  --> for Little, product 1 ranks as the highest
Little          1          2

SQL>

Moved to a function:

SQL> create or replace function f_product (par_customer_id in order_items.customer_id%type)
  2    return products.product_name%type
  3  is
  4    retval products.product_name%type;
  5  begin
  6    with
  7      temp as
  8        (select o.product_id,
  9                o.customer_id,
 10                sum(o.quantity * o.unit_price) total
 11         from order_items o
 12         group by o.product_id, o.customer_id
 13        ),
 14      rank_them as
 15        (select t.customer_id,
 16                t.product_id,
 17                row_number() over (partition by t.customer_id order by t.total desc) rn
 18         from temp t
 19        )
 20      select p.product_name
 21        into retval
 22      from rank_them r join products p on p.product_id = r.product_id
 23      where r.customer_id = par_customer_id
 24        and r.rn = 1;
 25
 26      return retval;
 27  end;
 28  /

Function created.

SQL>

Testing:

SQL> select f_product ('Little') result from dual;

RESULT
--------------------------------------------------------------------------------
Audi

SQL> select f_product ('Foot') result from dual;

RESULT
--------------------------------------------------------------------------------
Audi

SQL>

Now, you can improve it so that you'd care about no data found issue (when customer didn't buy anything), ties (but you'd then return a collection or a refcursor instead of a scalar value) etc.

[EDIT] I'm sorry, ORDERS table has to be included into the temp CTE; your data model is correct, you don't have to do anything about it - my query was wrong (small screen + late hours issue; not a real excuse, just saying).

So:

with 
temp as
  (select i.product_id,
          o.customer_id,
          sum(i.quantity * i.unit_price) total
   from order_items i join orders o on o.order_id = i.order_id
   group by i.product_id, o.customer_id
  ),

The rest of my code is - otherwise - unmodified.