I have a table with the following content

Now i would like to order them so that the sum of two directly successive packages shall correspond to the value '4'.

In the case of the table above a right result could look like this:

My question is if this is possible with Oracle SQL functions and if so how someone would realise this ?

CodePudding user response：

You can create a PIPELINED function to get the pairs.

First create the types:

CREATE TYPE table_name__obj AS OBJECT(
  id     NUMBER,
  name   VARCHAR2(20),
  "SIZE" NUMBER
);

CREATE TYPE table_name__tbl AS TABLE OF table_name__obj;

And then the function:

CREATE FUNCTION paired_products(
  v_total IN PLS_INTEGER
) RETURN table_name__tbl PIPELINED
IS
  v_tbl table_name__tbl;
  i     PLS_INTEGER;
  i_nxt PLS_INTEGER;
  j     PLS_INTEGER;
BEGIN
  SELECT table_name__obj(id, name, "SIZE")
  BULK COLLECT INTO v_tbl
  FROM   table_name
  ORDER BY "SIZE";
  
  i := v_tbl.FIRST;
  WHILE i IS NOT NULL LOOP
    j := v_tbl.NEXT(i);
    WHILE j IS NOT NULL LOOP
      IF v_tbl(i)."SIZE"   v_tbl(j)."SIZE" = v_total THEN
        EXIT;
      END IF;
      j := v_tbl.NEXT(j);
    END LOOP;

    PIPE ROW(v_tbl(i));
    IF j IS NOT NULL THEN
      PIPE ROW(v_tbl(j));
      v_tbl.DELETE(j);
    END IF;

    i_nxt := v_tbl.NEXT(i);
    v_tbl.DELETE(i);
    i := i_nxt;
  END LOOP;
END;
/

Which, for the sample data:

CREATE TABLE table_name (ID, NAME, "SIZE") AS
SELECT 1, 'PACKAGE A', 3 FROM DUAL UNION ALL
SELECT 2, 'PACKAGE B', 2 FROM DUAL UNION ALL
SELECT 3, 'PACKAGE C', 1 FROM DUAL UNION ALL
SELECT 4, 'PACKAGE D', 2 FROM DUAL UNION ALL
SELECT 5, 'PACKAGE E', 5 FROM DUAL;

Outputs the rows in the order starting from the smallest size and the corresponding pair and then in increasing sized pairs. If a row is found that does not have a matching pair then it is output immediately it is found not to have a pair.

ID	NAME	SIZE
3	PACKAGE C	1
1	PACKAGE A	3
2	PACKAGE B	2
4	PACKAGE D	2
5	PACKAGE E	5

db<>fiddle here