oracle 阶梯价

### 实现阶梯价格计算

在 Oracle 数据库中实现阶梯价格计算通常涉及创建一个专门用于存储不同阶段价格的数据结构，并编写相应的逻辑来处理这些数据。下面是一个具体的例子，展示如何通过 PL/SQL 来实现这一功能。

#### 创建阶梯价格表

首先定义一张表格 `t_staircase_price` 用来保存各个区间的定价信息：

CREATE TABLE t_staircease_price (
    id NUMBER GENERATED BY DEFAULT AS IDENTITY,
    start_quantity NUMBER NOT NULL,     -- 起始数量
    end_quantity NUMBER,                -- 结束数量 (NULL 表示无上限)
    unit_price NUMBER(10,2) NOT NULL,   -- 单位价格
    PRIMARY KEY(id),
    CHECK(start_quantity >= 0 AND (end_quantity IS NULL OR end_quantity > start_quantity))
);

此表记录了每一段的数量范围及其对应单价[^1]。

#### 插入初始数据

向上述表格插入一些测试性的区间和价格作为基础设定：

INSERT INTO t_staircease_price (start_quantity, end_quantity, unit_price) VALUES (0, 100, 5.99);
INSERT INTO t_staircease_price (start_quantity, end_quantity, unit_price) VALUES (101, 500, 4.75);
INSERT INTO t_staircease_price (start_quantity, end_quantity, unit_price) VALUES (501, null, 3.89);
COMMIT;

这里设置了三个不同的收费梯度：当购买量介于0到100之间时按5.99计费；超过100不超过500则按照较低的费率收取费用；而一旦超过了500，则适用最优惠的价格策略。

#### 编写PL/SQL函数进行计算

为了方便调用并确保业务逻辑的一致性和准确性，建议封装成一个名为 `calculate_total_cost` 的过程或函数来进行总金额的计算工作：

CREATE OR REPLACE FUNCTION calculate_total_cost(purchased_amount IN NUMBER) RETURN NUMBER IS
    total_cost NUMBER := 0;
BEGIN
    FOR rec IN (SELECT * FROM t_staircease_price ORDER BY start_quantity ASC) LOOP
        IF purchased_amount BETWEEN NVL(rec.start_quantity, 0) AND COALESCE(rec.end_quantity - 1, purchased_amount) THEN
            total_cost := total_cost + ((LEAST(purchased_amount, rec.end_quantity) - GREATEST(NVL(rec.start_quantity, 0), 0)) * rec.unit_price);
            EXIT WHEN purchased_amount <= LEAST(rec.end_quantity, purchased_amount);
        END IF;
    END LOOP;

    RETURN ROUND(total_cost, 2);
END;
/

这段代码遍历所有可能匹配的区间，并累加对应的花费直到达到实际采购总量为止。最后返回经过四舍五入后的最终成本数值。