중복 할인 함수

중복 할인 함수를 생성해보자. 퀴즈로 배우는 SQL : 중복 할인 금액 구하기 글에서 아이디어를 얻었다.

테스트를 위해 아래와 같이 테이블을 생성하자.

-- 1
DROP TABLE t1 PURGE;
CREATE TABLE t1 (seq NUMBER, amt NUMBER, rat NUMBER);

INSERT INTO t1 VALUES (1, 2000, NULL);
INSERT INTO t1 VALUES (2, NULL, 10);
INSERT INTO t1 VALUES (3, 3000, NULL);
INSERT INTO t1 VALUES (4, NULL, 20);
COMMIT;

아래와 같이 중복 할인 함수를 생성하자. 두 번째 인수로 커서를 입력받는다.

-- 2
CREATE OR REPLACE FUNCTION fn_discount (
    i_prc    IN    NUMBER
  , i_cur    IN    SYS_REFCURSOR
)
    RETURN NUMBER
IS
    l_amt    NUMBER;
    l_rat    NUMBER;
    l_ret    NUMBER := i_prc;
BEGIN
    LOOP
        FETCH i_cur INTO l_amt, l_rat;
        EXIT WHEN i_cur%NOTFOUND;
        CASE
            WHEN l_amt IS NOT NULL THEN l_ret := l_ret - l_amt;
            WHEN l_rat IS NOT NULL THEN l_ret := l_ret - (l_ret * (l_rat / 100));
            ELSE NULL;
        END CASE;
    END LOOP;

    CLOSE i_cur;

    RETURN l_ret;
END fn_discount;
/

아래 쿼리로 중복 할인된 금액을 구할 수 있다.

-- 3
SELECT fn_discount (20000, CURSOR (SELECT amt, rat FROM t1 ORDER BY seq)) AS rem
  FROM DUAL;

  REM
-----
10560

1개의 행이 선택되었습니다.