WITH t AS
(
SELECT 1 sort, 20000 price, 'AMT' gubun, 2000 amt, NULL rate FROM dual
UNION ALL SELECT 2, 20000, 'RATE', NULL,   10 rate FROM dual
UNION ALL SELECT 3, 20000, 'AMT' , 3000, NULL rate FROM dual
UNION ALL SELECT 4, 20000, 'RATE', NULL,   20 rate FROM dual
)
-- 1. Model 절 - 10g
SELECT *
  FROM t
 MODEL
 DIMENSION BY (ROW_NUMBER() OVER(ORDER BY sort) rn)
 MEASURES (sort, price, gubun, amt, rate, 0 dc_amt, 0 remain_amt)
 RULES ( remain_amt[ANY] = NVL(remain_amt[CV()-1], price[1])
                         - NVL( amt[CV()]
                              , NVL(remain_amt[CV()-1], price[1])
                              * rate[CV()] / 100
                              )
       , dc_amt[ANY] = NVL(remain_amt[CV()-1], price[1]) - remain_amt[CV()]
       )
;

WITH t AS
(
SELECT 1 sort, 20000 price, 'AMT' gubun, 2000 amt, NULL rate FROM dual
UNION ALL SELECT 2, 20000, 'RATE', NULL,   10 rate FROM dual
UNION ALL SELECT 3, 20000, 'AMT' , 3000, NULL rate FROM dual
UNION ALL SELECT 4, 20000, 'RATE', NULL,   20 rate FROM dual
)
-- 2. Recursive SQL - 11g
, t1 AS
(
SELECT ROW_NUMBER() OVER(ORDER BY sort) rn
     , sort, price, gubun, amt, rate
  FROM t
)
, t2(rn, sort, price, gubun, amt, rate, dc_amt, remain_amt) AS
(
SELECT rn
     , sort, price, gubun, amt, rate
     , NVL(amt, price * rate / 100) dc_amt
     , price - NVL(amt, price * rate / 100) remain_amt
  FROM t1
 WHERE rn = 1
 UNION ALL
SELECT a.rn
     , a.sort, a.price, a.gubun, a.amt, a.rate
     , NVL(a.amt, b.remain_amt * a.rate / 100) dc_amt
     , b.remain_amt - NVL(a.amt, b.remain_amt * a.rate / 100) remain_amt
  FROM t1 a
     , t2 b
 WHERE a.rn = b.rn + 1
)
SELECT *
  FROM t2
;

 역시 마농님 대단하십니다.

저도 model 로 하는 방법은 했었는데 가독성이 떨어질 것 같아서 다른 방법을 찾고 있었거든요..

제가 별도로 테스트 해보겠지만 속도면에서 재귀쿼리가 나을까요? model 이 나을까요?