Highest andLowest in PLSQL

Question

I have made a procedure to display the highest and the lowest popular Items for a particular time of a given date. The procedure works with no errors or exceptions and everything is functional. As you can see, the queries are repeated twice for the purpose of showing the first record on Items, but the only differences are in Order (ASC and DESC).

Is there any way I can reduce the amount of the code? How can I show the highest and lowest Items in one query rather than two? I only want to make the code neat and easy to read.

      create or replace procedure hight_lowest (param in date)
    as
    V_PNO_LOW   number(5);
    V_PNO_HIGH  number(5);
    BEGIN
    SELECT Item_no INTO V_PNO_LOW
    FROM
    (SELECT Items.Item_no SUM(Items.Quantity) AS total,  
   TO_CHAR(Prodcution_d,     
   'dd-mm-yyyy') AS pro_date
   FROM Items
   JOIN Parts 
   ON Parts.Serial_no = Items.Serial_no
   GROUP BY Item_no, TO_CHAR(Prodcution_d, 'dd-mm-yyyy')
   ORDER BY SUM(Items.Quantity) ASC)
   WHERE ROWNUM = 1
   AND pro_date = TO_CHAR(Param_DATE,'mm-yyyy') ;

   DBMS_OUTPUT.PUT_LINE('LOWEST ITEM: ' || V_PNO_LOW);

  SELECT Item_no INTO V_PNO_LOW
  FROM
  (SELECT Items.Item_no SUM(Items.Quantity) AS total, TO_CHAR(Prodcution_d,   
  'dd-mm-yyyy') AS pro_date
  FROM Items
  JOIN Parts 
  ON Parts.Serial_no = Items.Serial_no
  GROUP BY Item_no, TO_CHAR(Prodcution_d, 'dd-mm-yyyy')
  ORDER BY SUM(Items.Quantity) DESC)
  WHERE ROWNUM = 1
  AND pro_date = TO_CHAR(Param_DATE,'mm-yyyy') ;

 DBMS_OUTPUT.PUT_LINE('HIGHEST POPULAR ITEM: ' || V_PNO_HIGH);

 END;
  /

Answer 1

Your core problem is how to find out top-1 and bottom-1 in a single query. This is essentially a SQL problem and has not much to do with PL/SQL. First I present you the SQL solution and then simply wrap that into PL/SQL.

You didn't provided table definitions. I was lazy and didn't tried to reverse engineer your schema but instead created very simple table that illustrates the solution that you can apply to your real problem.

Other notes that might or might not be relevant to your case:

• Use packages when applicable.

• PL/SQL is case insensitive so it doesn't matter if use UPPER or lower or MiXed case. I prefer simplicity so I always use lower case. YMMV.

• There is no need to convert dates into strings. Instead if you are looking for day granularity use trunc instead of to_char.

trunc example:

SQL> select sysdate, trunc(sysdate), date'2016-01-15' from dual;

SYSDATE             TRUNC(SYSDATE)      DATE'2016-01-15'
------------------- ------------------- -------------------
2016-01-15 11:05:10 2016-01-15 00:00:00 2016-01-15 00:00:00

Elapsed: 00:00:00.16
SQL>

As bonus I also introduced you to standard SQL datetime-literal syntax.

Let's create some data to play with:

-- demonstration purpose only, no resemblance to OP's code
create table items(
 id number
,quantity number
,production_date date
);

-- populate random data
insert into items
select level, floor(dbms_random.value(1, 1000)), sysdate - floor(dbms_random.value(1, 5))
from dual
connect by level <= 1000
;

Use analytic function row_number (you might also consider using rank or dense_rank functions instead) to assign an unique number in ordered sequence to each row. This is the standard way to implement top-N, bottom-N and inner-N queries in Oracle. Note that this example doesn't resolve ties.

with
ordered_items as (
  -- the actual SQL can be arbitrary complex
  select
   id
  ,quantity
  ,production_date
  ,row_number() over (order by quantity asc)  as top_rn
  ,row_number() over (order by quantity desc) as bottom_rn
  from items
  where trunc(production_date) = date'2016-01-14'
)
select
 o1.id as hi_id
,o2.id as lo_id
from       ordered_items o1
inner join ordered_items o2 on o2.bottom_rn = o1.top_rn
where o1.top_rn    = 1
and   o2.bottom_rn = 1
;

The above SQL will turn into the following PL/SQL code:

create or replace procedure get_hi_and_lo_id(
 p_production_date in date
,p_hi_id out number
,p_lo_id out number
) as
begin
  with
  ordered_items as (
    select
     id
    ,quantity
    ,production_date
    ,row_number() over (order by quantity asc)  as top_rn
    ,row_number() over (order by quantity desc) as bottom_rn
    from items
    where trunc(production_date) = trunc(p_production_date)
  )
  select   hi.id,   lo.id
  into   p_hi_id, p_lo_id
  from       ordered_items hi
  inner join ordered_items lo on lo.bottom_rn = hi.top_rn
  where hi.top_rn    = 1
  and   lo.bottom_rn = 1
  ;
exception
  when no_data_found then
    null;
end;
/
show errors

Usage example:

declare
  v_hi_id number;
  v_lo_id number;
begin
  get_hi_and_lo_id(
    p_production_date => sysdate - 1
   ,p_hi_id           => v_hi_id
   ,p_lo_id           => v_lo_id
  );

  dbms_output.put_line('v_hi_id: ' || v_hi_id);
  dbms_output.put_line('v_lo_id: ' || v_lo_id);
end;
/

v_hi_id: 689
v_lo_id: 370

PL/SQL procedure successfully completed.

That seems to be the correct answer with my data:

SQL> select * from items where trunc(production_date) = trunc(sysdate - 1) order by quantity;

        ID   QUANTITY PRODUCTION_DATE
---------- ---------- -------------------
       689         13 2016-01-14 10:44:28
        25         18 2016-01-14 10:44:28
[...]
       334        987 2016-01-14 10:44:28
       370        994 2016-01-14 10:44:28

235 rows selected.

Elapsed: 00:00:00.95
SQL>