PL/SQL存储过程实现订单及多子订单录入功能咨询

你好，根据你的需求，我们需要新增一个支持主订单+2~10个可变数量子订单录入的存储过程，同时可以和现有的VIEW_ORDER查询过程联动。下面是详细的实现步骤和代码示例：

1. 先明确表结构（假设） #

因为你没提供主/子订单表的定义，我先基于常见的业务场景假设表结构，如果和你的实际表不符，直接替换字段即可：

-- 主订单表
CREATE TABLE ORDERS (
    ORDER_NO CHAR(10) PRIMARY KEY, -- 订单号，和你现有VIEW_ORDER的入参类型一致
    CUSTOMER_ID VARCHAR2(20) NOT NULL,
    ORDER_DATE DATE DEFAULT SYSDATE,
    TOTAL_AMOUNT NUMBER(10,2)
);

-- 子订单表（你现有VIEW_ORDER查询的表）
CREATE TABLE SUBORDERS (
    ORDER_NO CHAR(10) REFERENCES ORDERS(ORDER_NO),
    PRODUCT_CODE VARCHAR2(20) NOT NULL,
    QUANTITY NUMBER(5) NOT NULL CHECK(QUANTITY > 0),
    UNIT_PRICE NUMBER(8,2) NOT NULL,
    PRIMARY KEY(ORDER_NO, PRODUCT_CODE)
);

2. 创建子订单数据的集合类型 #

因为子订单数量是2_{10的可变值，我们用`VARRAY`（固定长度数组）来传递子订单数据，刚好匹配2}10的范围限制：

-- 定义子订单记录类型
CREATE OR REPLACE TYPE SUBORDER_REC AS OBJECT (
    PRODUCT_CODE VARCHAR2(20),
    QUANTITY NUMBER(5),
    UNIT_PRICE NUMBER(8,2)
);

-- 定义子订单集合类型（最多10条，最少后续在存储过程里校验）
CREATE OR REPLACE TYPE SUBORDER_LIST AS VARRAY(10) OF SUBORDER_REC;

3. 编写录入主/子订单的存储过程 #

这个过程会处理主订单插入、子订单批量插入，同时校验子订单数量范围，最后可以调用你的VIEW_ORDER来展示结果：

CREATE OR REPLACE PROCEDURE INSERT_ORDER(
    P_ORDER_NO IN CHAR, -- 主订单号
    P_CUSTOMER_ID IN VARCHAR2,
    P_SUBORDERS IN SUBORDER_LIST,
    P_TOTAL_AMOUNT IN NUMBER DEFAULT NULL -- 可选：如果不传入可以自动计算
) AS
    V_TOTAL_AMOUNT NUMBER(10,2) := 0;
BEGIN
    -- 校验子订单数量：必须在2~10之间
    IF P_SUBORDERS.COUNT < 2 OR P_SUBORDERS.COUNT > 10 THEN
        RAISE_APPLICATION_ERROR(-20001, '子订单数量必须在2到10之间');
    END IF;

    -- 计算总金额（如果用户没传入的话）
    IF P_TOTAL_AMOUNT IS NULL THEN
        FOR I IN 1..P_SUBORDERS.COUNT LOOP
            V_TOTAL_AMOUNT := V_TOTAL_AMOUNT + (P_SUBORDERS(I).QUANTITY * P_SUBORDERS(I).UNIT_PRICE);
        END LOOP;
    ELSE
        V_TOTAL_AMOUNT := P_TOTAL_AMOUNT;
    END IF;

    -- 插入主订单
    INSERT INTO ORDERS(ORDER_NO, CUSTOMER_ID, TOTAL_AMOUNT)
    VALUES(P_ORDER_NO, P_CUSTOMER_ID, V_TOTAL_AMOUNT);

    -- 批量插入子订单
    FOR I IN 1..P_SUBORDERS.COUNT LOOP
        INSERT INTO SUBORDERS(ORDER_NO, PRODUCT_CODE, QUANTITY, UNIT_PRICE)
        VALUES(P_ORDER_NO, P_SUBORDERS(I).PRODUCT_CODE, P_SUBORDERS(I).QUANTITY, P_SUBORDERS(I).UNIT_PRICE);
    END LOOP;

    COMMIT;
    DBMS_OUTPUT.PUT_LINE('订单录入成功！订单号：' || P_ORDER_NO);

    -- 调用现有VIEW_ORDER存储过程，展示刚录入的子订单信息
    VIEW_ORDER(P_ORDER_NO);

EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN
        ROLLBACK;
        DBMS_OUTPUT.PUT_LINE('错误：订单号或子订单商品编码已存在');
    WHEN OTHERS THEN
        ROLLBACK;
        DBMS_OUTPUT.PUT_LINE('录入失败：' || SQLERRM);
END;
/

4. 在SQLPlus中调用这个存储过程 #

你可以通过声明集合变量并赋值的方式调用，示例如下：

SET SERVEROUTPUT ON; -- 打开输出，才能看到DBMS_OUTPUT的内容

DECLARE
    -- 定义子订单集合变量
    V_SUBORDERS SUBORDER_LIST;
BEGIN
    -- 初始化集合，这里示例录入3条子订单（符合2~10的要求）
    V_SUBORDERS := SUBORDER_LIST(
        SUBORDER_REC('PROD001', 5, 100.00),
        SUBORDER_REC('PROD002', 2, 200.00),
        SUBORDER_REC('PROD003', 1, 500.00)
    );

    -- 调用录入存储过程
    INSERT_ORDER(
        P_ORDER_NO => 'ORD2024001',
        P_CUSTOMER_ID => 'CUS001',
        P_SUBORDERS => V_SUBORDERS
        -- 这里可以不传入P_TOTAL_AMOUNT，过程会自动计算
    );
END;
/

5. 和现有VIEW_ORDER的整合说明 #

• 我们在INSERT_ORDER的末尾直接调用了VIEW_ORDER，这样录入完成后会自动输出子订单的详情，和你的现有功能无缝衔接。
• 如果不需要自动展示，你可以删除VIEW_ORDER(P_ORDER_NO);这一行，之后单独调用VIEW_ORDER来查询。

注意事项 #

• 确保你的现有VIEW_ORDER存储过程中的SUBORDERS表和我们假设的结构一致，如果字段不同，需要调整INSERT_ORDER中的插入逻辑。
• 所有输入参数的类型要和表字段类型匹配，比如ORDER_NO是CHAR(10)，调用时要传入长度符合的值。
• 在SQLPlus中必须开启SET SERVEROUTPUT ON;才能看到输出信息。

内容的提问来源于stack exchange，提问作者James