Oracle 21c下用PL/SQL游标实现巴士乘客分配（转SQL Server脚本）

核心逻辑说明 #

通过嵌套游标遍历巴士列表和未分配乘客列表，按巴士剩余容量依次分配乘客，直到巴士满员或所有乘客分配完成，最终生成乘客-巴士分配记录。

1. 表结构创建与测试数据插入 #

-- 创建巴士表
CREATE TABLE bus (
    bus_id NUMBER GENERATED AS IDENTITY PRIMARY KEY,
    bus_number VARCHAR2(10) NOT NULL,
    capacity NUMBER NOT NULL CHECK (capacity > 0)
);

-- 创建乘客表
CREATE TABLE passenger (
    passenger_id NUMBER GENERATED AS IDENTITY PRIMARY KEY,
    passenger_name VARCHAR2(50) NOT NULL
);

-- 创建分配记录表
CREATE TABLE passenger_bus_assignment (
    assignment_id NUMBER GENERATED AS IDENTITY PRIMARY KEY,
    passenger_id NUMBER NOT NULL REFERENCES passenger(passenger_id),
    bus_id NUMBER NOT NULL REFERENCES bus(bus_id)
);

-- 插入巴士测试数据
INSERT INTO bus (bus_number, capacity) VALUES ('BUS-001', 2);
INSERT INTO bus (bus_number, capacity) VALUES ('BUS-002', 3);
INSERT INTO bus (bus_number, capacity) VALUES ('BUS-003', 1);

-- 插入乘客测试数据
INSERT INTO passenger (passenger_name) VALUES ('Alice');
INSERT INTO passenger (passenger_name) VALUES ('Bob');
INSERT INTO passenger (passenger_name) VALUES ('Charlie');
INSERT INTO passenger (passenger_name) VALUES ('David');
INSERT INTO passenger (passenger_name) VALUES ('Eve');
INSERT INTO passenger (passenger_name) VALUES ('Frank');
COMMIT;

2. PL/SQL游标实现分配逻辑 #

DECLARE
    -- 巴士游标：按ID顺序遍历所有巴士
    CURSOR c_buses IS
        SELECT bus_id, capacity
        FROM bus
        ORDER BY bus_id;
    
    -- 乘客游标：按ID顺序遍历未分配的乘客
    CURSOR c_passengers IS
        SELECT passenger_id
        FROM passenger
        WHERE passenger_id NOT IN (
            SELECT passenger_id FROM passenger_bus_assignment
        )
        ORDER BY passenger_id;
    
    v_bus_id bus.bus_id%TYPE;
    v_remaining_capacity bus.capacity%TYPE;
    v_passenger_id passenger.passenger_id%TYPE;
BEGIN
    -- 遍历每一辆巴士
    OPEN c_buses;
    LOOP
        FETCH c_buses INTO v_bus_id, v_remaining_capacity;
        EXIT WHEN c_buses%NOTFOUND;
        
        -- 为当前巴士分配乘客，直到容量耗尽或无乘客可分配
        OPEN c_passengers;
        LOOP
            EXIT WHEN v_remaining_capacity <= 0;
            
            FETCH c_passengers INTO v_passenger_id;
            EXIT WHEN c_passengers%NOTFOUND;
            
            -- 插入分配记录
            INSERT INTO passenger_bus_assignment (passenger_id, bus_id)
            VALUES (v_passenger_id, v_bus_id);
            
            v_remaining_capacity := v_remaining_capacity - 1;
        END LOOP;
        CLOSE c_passengers;
    END LOOP;
    CLOSE c_buses;
    
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('乘客分配完成');
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        DBMS_OUTPUT.PUT_LINE('分配失败：' || SQLERRM);
END;
/

3. 验证分配结果 #

执行以下查询查看最终分配记录：

SELECT 
    p.passenger_name,
    b.bus_number
FROM passenger_bus_assignment pa
JOIN passenger p ON pa.passenger_id = p.passenger_id
JOIN bus b ON pa.bus_id = b.bus_id
ORDER BY b.bus_id, p.passenger_id;

预期结果：

PASSENGER_NAME | BUS_NUMBER
---------------|-----------
Alice          | BUS-001
Bob            | BUS-001
Charlie        | BUS-002
David          | BUS-002
Eve            | BUS-002
Frank          | BUS-003

关键语法说明 #

• Oracle中自增列使用GENERATED AS IDENTITY替代SQL Server的IDENTITY
• 显式游标需手动执行OPEN/FETCH/CLOSE操作，通过%NOTFOUND判断遍历结束
• 用变量v_remaining_capacity跟踪巴士剩余座位，确保分配不超过容量限制

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