您可以使用分析函数来生成金额的累计总额，并使用递归查询将其拆分为多页：

WITH cte_rec (
  order_id,
  denom_id,
  denom_val,
  amount,
  max_page_amount,
  prev_total,
  total,
  start_page,
  end_page
) AS (
  SELECT order_id,
         denom_id,
         denom_val,
         amount,
         max_page_amount,
         SUM(amount) OVER (ORDER BY order_id, denom_val DESC, amount DESC, ROWNUM) - amount,
         SUM(amount) OVER (ORDER BY order_id, denom_val DESC, amount DESC, ROWNUM),
         GREATEST(
           CEIL(
             (SUM(amount)
                OVER (ORDER BY order_id, denom_val DESC, amount DESC, ROWNUM) - amount)
             / max_page_amount
           ),
           1
         ),
         CEIL(
           SUM(amount) OVER (ORDER BY order_id, denom_val DESC, amount, ROWNUM)
           / max_page_amount
         )
  FROM   my_table
UNION ALL
  SELECT order_id,
         denom_id,
         denom_val,
         amount,
         max_page_amount,
         prev_total,
         total,
         start_page + 1,
         end_page
  FROM   cte_rec
  WHERE  start_page < end_page
)
SEARCH DEPTH FIRST BY order_id, denom_val DESC, amount DESC SET o_id
SELECT start_page AS page_num,
       order_id,
       denom_id,
       denom_val,
       amount,
       LEAST(start_page * max_page_amount, total)
         - GREATEST((start_page - 1) * max_page_amount, prev_total) AS new_amount,
       max_page_amount
FROM   cte_rec;

其中，对于样本数据：

CREATE TABLE my_table (ORDER_ID, DENOM_ID, DENOM_VAL, AMOUNT, MAX_PAGE_AMOUNT) AS
SELECT 'AAAAAAAA', 'EUR100', 100, 2800000, 1000000 FROM DUAL UNION ALL
SELECT 'AAAAAAAA', 'EUR050',  50,  700000, 1000000 FROM DUAL UNION ALL
SELECT 'AAAAAAAA', 'EUR010',  10,  150000, 1000000 FROM DUAL;

输出：
| 页码|订单ID| DENOM_ID| DENOM_瓦尔|金额|新_金额|最大页数|
| - -----|- -----|- -----|- -----|- -----|- -----|- -----|
| 一个|AAAAAAAA| 100欧元|一百|二百八十万|100万|100万|
| 2| AAAAAAAA| 100欧元|一百|二百八十万|100万|100万|
| 3| AAAAAAAA| 100欧元|一百|二百八十万|八十万|100万|
| 3| AAAAAAAA| 050欧元|五十|七十万|二十万|100万|
| 4| AAAAAAAA| 050欧元|五十|七十万|五十万|100万|
| 4| AAAAAAAA| EUR010|十个|十五万|十五万|100万|
fiddle

使用CONNECT BY LEVEL根据amount除以max_page_amount生成所需的行数。

SELECT
      order_id
    , denom_id
    , denom_val
    , amount
    , max_page_amount
FROM my_table CONNECT BY LEVEL <= CEIL(amount / max_page_amount)
WHERE PRIOR order_id = order_id
    AND PRIOR denom_id = denom_id
    AND PRIOR denom_val = denom_val
    AND PRIOR amount = amount
    AND PRIOR max_page_amount = max_page_amount

CEIL用于确保包含所有行，即使除法不精确，PRIOR关键字用于确保重复行与原始行具有相同的值。
如果您不喜欢使用Oracle特定的函数，那么使用“递归CTE”也应该有效：

WITH CTE
AS (
    SELECT order_id, denom_id, denom_val, amount, max_page_amount, 1 AS lvl
    FROM my_table
    
    UNION ALL
    
    SELECT order_id, denom_id, denom_val, amount, max_page_amount, lvl + 1
    FROM CTE
    WHERE lvl < CEIL(amount / max_page_amount)
    )
SELECT
      order_id
    , denom_id
    , denom_val
    , amount
    , max_page_amount
FROM CTE;

还有另一种解决方案基于对数据和max_page_amount的桶的间隔计算，比较数据集的性能可能很有趣，这里递归仅用于生成桶：

with intervals as (
    select d.*, lag(sum_amount,1,0) over(order by rn) as start_interval, sum_amount as end_interval
    from (
        select rownum as rn,  order_id, denom_id, denom_val, amount, max_page_amount,
        sum(amount) over(order by rownum) as sum_amount
        from my_table 
    ) d
)
, maxdata as (
    select max_page_amount, sum(amount) as max_amount from my_table1
    group by max_page_amount
)
, buckets as (
    select level as bucket_id, (level-1) * max_page_amount as start_bucket, level * max_page_amount as end_bucket 
    from maxdata
    connect by (level-1) * max_page_amount < max_amount 
)
select bucket_id as page_num, order_id, denom_id, denom_val, amount, max_page_amount,
    case when end_interval > end_bucket 
        then 
            least(max_page_amount, end_bucket - start_interval)
        else
            least(amount, end_interval - start_bucket)
    end as new_page_amount
from intervals
join buckets on start_bucket <= end_interval and end_bucket >= start_interval
;