# 计费规则表
CREATE TABLE BILLING_RULE (
RULE_ID VARCHAR(32) NOT NULL,
RULE_NAME VARCHAR(100) NOT NULL,
RULE_TYPE VARCHAR(20) NOT NULL,
BILLING_UNIT VARCHAR(20) NOT NULL,
UNIT_PRICE DECIMAL(18, 4) NOT NULL,
MIN_CHARGE DECIMAL(18, 2) DEFAULT 0,
MAX_CHARGE DECIMAL(18, 2),
EFFECTIVE_DATE DATE NOT NULL,
EXPIRE_DATE DATE,
IS_ACTIVE CHAR(1) DEFAULT ‘Y’,
CONSTRAINT PK_BILLING_RULE PRIMARY KEY (RULE_ID)
);

CREATE INDEX IDX_RULE_TYPE ON BILLING_RULE(RULE_TYPE, EFFECTIVE_DATE);

# 阶梯计费规则表
CREATE TABLE TIERED_BILLING_RULE (
TIER_ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY,
RULE_ID VARCHAR(32) NOT NULL,
MIN_VALUE DECIMAL(18, 2) NOT NULL,
MAX_VALUE DECIMAL(18, 2),
UNIT_PRICE DECIMAL(18, 4) NOT NULL,
CONSTRAINT PK_TIERED_BILLING_RULE PRIMARY KEY (TIER_ID)
);

CREATE INDEX IDX_TIERED_RULE ON TIERED_BILLING_RULE(RULE_ID, MIN_VALUE);

# 计费记录表
CREATE TABLE BILLING_RECORD (
RECORD_ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY,
CUSTOMER_ID VARCHAR(32) NOT NULL,
SERVICE_ID VARCHAR(32) NOT NULL,
RULE_ID VARCHAR(32) NOT NULL,
USAGE_VALUE DECIMAL(18, 2) NOT NULL,
BILLING_AMOUNT DECIMAL(18, 2) NOT NULL,
BILLING_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
BILLING_PERIOD VARCHAR(20) NOT NULL,
CONSTRAINT PK_BILLING_RECORD PRIMARY KEY (RECORD_ID)
) PARTITION BY RANGE (BILLING_TIME)
(
PARTITION P202604 STARTING ‘2026-04-01’ ENDING ‘2026-04-30’
);

CREATE INDEX IDX_BILLING_CUSTOMER ON BILLING_RECORD(CUSTOMER_ID, BILLING_TIME DESC);
CREATE INDEX IDX_BILLING_SERVICE ON BILLING_RECORD(SERVICE_ID, BILLING_TIME DESC);

# 账单表
CREATE TABLE BILLING_INVOICE (
INVOICE_ID VARCHAR(32) NOT NULL,
CUSTOMER_ID VARCHAR(32) NOT NULL,
BILLING_PERIOD VARCHAR(20) NOT NULL,
TOTAL_AMOUNT DECIMAL(18, 2) NOT NULL,
PAID_AMOUNT DECIMAL(18, 2) DEFAULT 0,
INVOICE_STATUS VARCHAR(20) NOT NULL DEFAULT ‘UNPAID’,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
DUE_DATE DATE NOT NULL,
CONSTRAINT PK_BILLING_INVOICE PRIMARY KEY (INVOICE_ID)
);

CREATE INDEX IDX_INVOICE_CUSTOMER ON BILLING_INVOICE(CUSTOMER_ID, BILLING_PERIOD);

# 套餐定义表
CREATE TABLE SERVICE_PACKAGE (
PACKAGE_ID VARCHAR(32) NOT NULL,
PACKAGE_NAME VARCHAR(100) NOT NULL,
PACKAGE_TYPE VARCHAR(20) NOT NULL,
MONTHLY_FEE DECIMAL(18, 2) NOT NULL,
INCLUDED_USAGE DECIMAL(18, 2) DEFAULT 0,
OVERAGE_PRICE DECIMAL(18, 4),
EFFECTIVE_DATE DATE NOT NULL,
EXPIRE_DATE DATE,
IS_ACTIVE CHAR(1) DEFAULT ‘Y’,
CONSTRAINT PK_SERVICE_PACKAGE PRIMARY KEY (PACKAGE_ID)
);

# 客户套餐订阅表
CREATE TABLE CUSTOMER_PACKAGE (
SUBSCRIPTION_ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY,
CUSTOMER_ID VARCHAR(32) NOT NULL,
PACKAGE_ID VARCHAR(32) NOT NULL,
SUBSCRIBE_DATE DATE NOT NULL,
UNSUBSCRIBE_DATE DATE,
STATUS VARCHAR(20) NOT NULL DEFAULT ‘ACTIVE’,
CONSTRAINT PK_CUSTOMER_PACKAGE PRIMARY KEY (SUBSCRIPTION_ID)
);

CREATE INDEX IDX_CUSTOMER_PACKAGE ON CUSTOMER_PACKAGE(CUSTOMER_ID, STATUS);

# 计费计算存储过程
CREATE OR REPLACE PROCEDURE SP_CALCULATE_BILLING(
IN p_customer_id VARCHAR(32),
IN p_service_id VARCHAR(32),
IN p_usage_value DECIMAL(18, 2),
IN p_billing_period VARCHAR(20),
OUT p_billing_amount DECIMAL(18, 2)
)
LANGUAGE SQL
BEGIN
DECLARE v_rule_id VARCHAR(32);
DECLARE v_rule_type VARCHAR(20);
DECLARE v_unit_price DECIMAL(18, 4);
DECLARE v_min_charge DECIMAL(18, 2);
DECLARE v_max_charge DECIMAL(18, 2);
DECLARE v_package_id VARCHAR(32);
DECLARE v_included_usage DECIMAL(18, 2);
DECLARE v_overage_price DECIMAL(18, 4);

SET p_billing_amount = 0;

— 检查客户套餐
SELECT P.PACKAGE_ID, P.INCLUDED_USAGE, P.OVERAGE_PRICE
INTO v_package_id, v_included_usage, v_overage_price
FROM CUSTOMER_PACKAGE CP
JOIN SERVICE_PACKAGE P ON CP.PACKAGE_ID = P.PACKAGE_ID
WHERE CP.CUSTOMER_ID = p_customer_id
AND CP.STATUS = ‘ACTIVE’
AND CURRENT DATE BETWEEN CP.SUBSCRIBE_DATE AND COALESCE(CP.UNSUBSCRIBE_DATE, ‘9999-12-31’);

IF v_package_id IS NOT NULL THEN
— 套餐计费
IF p_usage_value <= v_included_usage THEN SET p_billing_amount = 0; ELSE SET p_billing_amount = (p_usage_value - v_included_usage) * v_overage_price; END IF; ELSE -- 获取计费规则 SELECT RULE_ID, RULE_TYPE, UNIT_PRICE, MIN_CHARGE, MAX_CHARGE INTO v_rule_id, v_rule_type, v_unit_price, v_min_charge, v_max_charge FROM BILLING_RULE WHERE RULE_TYPE = 'STANDARD' AND IS_ACTIVE = 'Y' AND CURRENT DATE BETWEEN EFFECTIVE_DATE AND COALESCE(EXPIRE_DATE, '9999-12-31') FETCH FIRST 1 ROW ONLY; -- 计算费用 SET p_billing_amount = p_usage_value * v_unit_price; -- 应用最低收费 IF p_billing_amount < v_min_charge THEN SET p_billing_amount = v_min_charge; END IF; -- 应用最高收费 IF v_max_charge IS NOT NULL AND p_billing_amount > v_max_charge THEN
SET p_billing_amount = v_max_charge;
END IF;
END IF;

— 插入计费记录
INSERT INTO BILLING_RECORD (
CUSTOMER_ID, SERVICE_ID, RULE_ID,
USAGE_VALUE, BILLING_AMOUNT, BILLING_PERIOD
) VALUES (
p_customer_id, p_service_id, v_rule_id,
p_usage_value, p_billing_amount, p_billing_period
);
END;

# 阶梯计费存储过程
CREATE OR REPLACE PROCEDURE SP_TIERED_BILLING(
IN p_customer_id VARCHAR(32),
IN p_service_id VARCHAR(32),
IN p_usage_value DECIMAL(18, 2),
IN p_billing_period VARCHAR(20),
OUT p_billing_amount DECIMAL(18, 2)
)
LANGUAGE SQL
BEGIN
DECLARE v_rule_id VARCHAR(32);
DECLARE v_tier_price DECIMAL(18, 4);
DECLARE v_min_value DECIMAL(18, 2);
DECLARE v_max_value DECIMAL(18, 2);
DECLARE v_remaining_usage DECIMAL(18, 2);
DECLARE v_tier_usage DECIMAL(18, 2);

SET p_billing_amount = 0;
SET v_remaining_usage = p_usage_value;

— 获取阶梯计费规则
FOR tier_cursor AS
SELECT RULE_ID, MIN_VALUE, MAX_VALUE, UNIT_PRICE
FROM TIERED_BILLING_RULE
WHERE RULE_ID = (
SELECT RULE_ID FROM BILLING_RULE
WHERE RULE_TYPE = ‘TIERED’
AND IS_ACTIVE = ‘Y’
AND CURRENT DATE BETWEEN EFFECTIVE_DATE AND COALESCE(EXPIRE_DATE, ‘9999-12-31’)
FETCH FIRST 1 ROW ONLY
)
ORDER BY MIN_VALUE
DO
IF v_remaining_usage > 0 THEN
SET v_tier_usage = tier_cursor.MAX_VALUE – tier_cursor.MIN_VALUE;

IF v_remaining_usage <= v_tier_usage THEN SET p_billing_amount = p_billing_amount + v_remaining_usage * tier_cursor.UNIT_PRICE; SET v_remaining_usage = 0; ELSE SET p_billing_amount = p_billing_amount + v_tier_usage * tier_cursor.UNIT_PRICE; SET v_remaining_usage = v_remaining_usage - v_tier_usage; END IF; END IF; END FOR; -- 插入计费记录 INSERT INTO BILLING_RECORD ( CUSTOMER_ID, SERVICE_ID, RULE_ID, USAGE_VALUE, BILLING_AMOUNT, BILLING_PERIOD ) VALUES ( p_customer_id, p_service_id, v_rule_id, p_usage_value, p_billing_amount, p_billing_period ); END;

# 账单生成存储过程
CREATE OR REPLACE PROCEDURE SP_GENERATE_INVOICE(
IN p_customer_id VARCHAR(32),
IN p_billing_period VARCHAR(20),
OUT p_invoice_id VARCHAR(32),
OUT p_total_amount DECIMAL(18, 2)
)
LANGUAGE SQL
BEGIN
SET p_invoice_id = ‘INV’ || TO_CHAR(CURRENT DATE, ‘YYYYMMDD’) ||
SUBSTR(TO_CHAR(RAND() * 10000), 1, 4);

— 计算总金额
SELECT SUM(BILLING_AMOUNT) INTO p_total_amount
FROM BILLING_RECORD
WHERE CUSTOMER_ID = p_customer_id
AND BILLING_PERIOD = p_billing_period;

— 创建账单
INSERT INTO BILLING_INVOICE (
INVOICE_ID, CUSTOMER_ID, BILLING_PERIOD,
TOTAL_AMOUNT, DUE_DATE
) VALUES (
p_invoice_id, p_customer_id, p_billing_period,
p_total_amount, CURRENT DATE + 15 DAYS
);
END;

# 批量生成账单
CREATE OR REPLACE PROCEDURE SP_BATCH_GENERATE_INVOICE(
IN p_billing_period VARCHAR(20)
)
LANGUAGE SQL
BEGIN
DECLARE v_customer_id VARCHAR(32);
DECLARE v_invoice_id VARCHAR(32);
DECLARE v_total_amount DECIMAL(18, 2);

FOR customer_cursor AS
SELECT DISTINCT CUSTOMER_ID
FROM BILLING_RECORD
WHERE BILLING_PERIOD = p_billing_period
DO
CALL SP_GENERATE_INVOICE(
customer_cursor.CUSTOMER_ID,
p_billing_period,
v_invoice_id,
v_total_amount
);
END FOR;
END;

# 查询客户账单
SELECT
I.INVOICE_ID,
I.CUSTOMER_ID,
I.BILLING_PERIOD,
I.TOTAL_AMOUNT,
I.PAID_AMOUNT,
I.INVOICE_STATUS,
I.DUE_DATE
FROM BILLING_INVOICE I
WHERE I.CUSTOMER_ID = ?
ORDER BY I.CREATE_TIME DESC;

# 查询账单明细
SELECT
R.RECORD_ID,
R.SERVICE_ID,
R.RULE_ID,
R.USAGE_VALUE,
R.BILLING_AMOUNT,
R.BILLING_TIME
FROM BILLING_RECORD R
WHERE R.CUSTOMER_ID = ?
AND R.BILLING_PERIOD = ?
ORDER BY R.BILLING_TIME;

# 统计账单金额
SELECT
BILLING_PERIOD,
COUNT(*) AS INVOICE_COUNT,
SUM(TOTAL_AMOUNT) AS TOTAL_AMOUNT,
SUM(PAID_AMOUNT) AS PAID_AMOUNT,
SUM(TOTAL_AMOUNT – PAID_AMOUNT) AS UNPAID_AMOUNT
FROM BILLING_INVOICE
GROUP BY BILLING_PERIOD
ORDER BY BILLING_PERIOD DESC;