How to find the number of passengers using Oracle 21c

Question

I need your help to solve this problem in PL/SQL.

I'm learning PL/SQL and unfortunately I'm not very good at it and I need your help to solve this using a cursor.

I also created a DbFiddle for Oracle 21c because that's what I use and I apologize for not knowing how to separate text from code or how to edit a question correctly.

I put my attempt in the DbFiddle

https://dbfiddle.uk/uTFrwjsM

I just want to convert the script that goes in Sql Server to Oracle. It is for educational purpose only. Maybe you can help me please. Thanks!

I will also put the data here

create table bus(
bus_id number,
arrival_time number,
capacity number);

insert into bus values(1,2,1);
insert into bus values(2,4,10);
insert into bus values(3,7,2);
commit;

select *
from bus;

create table passenger(
passenger_id number,
arrival_time number);

insert into passenger values(11,1);
insert into passenger values(12,1);
insert into passenger values(13,5);
insert into passenger values(14,6);
insert into passenger values(15,7);
commit;

Result

bus_id  capacity    b_arrival   spot    passenger_id    p_arrival
1   1   2   1   11  1
2   10  4   1   12  1
2   10  4   2   NULL    NULL
2   10  4   3   NULL    NULL
2   10  4   4   NULL    NULL
2   10  4   5   NULL    NULL
2   10  4   6   NULL    NULL
2   10  4   7   NULL    NULL
2   10  4   8   NULL    NULL
2   10  4   9   NULL    NULL
2   10  4   10  NULL    NULL
3   2   7   1   13  5
3   2   7   2   14  6

Thank you so much

Answer 1

Another PL/SQL solution that works more like an old COBOL program from the '70s / '80s: just "merging" (in a non-SQL sense) two result sets.

    create or replace force type t_bus_passenger as object(
      bus_id number,
      b_arrival number,
      capacity number,
      spot number,
      passenger_id number,
      p_arrival number
    )
    /
    create or replace type tt_bus_passenger as table of t_bus_passenger
    /
    create or replace function bus_passenger_schedule
    return tt_bus_passenger pipelined is
      cursor cur_bp is
        select t_bus_passenger(
          BUS_ID, ARRIVAL_TIME, CAPACITY, SPOT, null, null
        ) obj
        from bus, lateral(
          select level spot from dual
          connect by level <= capacity
        )
        order by arrival_time, spot;
      cursor cur_passenger is
        select * from passenger
        order by arrival_time, passenger_id;
      rec_passenger cur_passenger%rowtype;
    begin
      open cur_passenger;
      fetch cur_passenger into rec_passenger;
      for rec_bp in cur_bp loop
        if rec_bp.obj.b_arrival >= rec_passenger.arrival_time then
          rec_bp.obj.passenger_id := rec_passenger.passenger_id;
          rec_bp.obj.p_arrival := rec_passenger.arrival_time;
          fetch cur_passenger into rec_passenger; 
        end if;
        pipe row(rec_bp.obj);
      end loop;
      close cur_passenger;
      return;
    end;
    /
    select * from bus_passenger_schedule();

Answer 2

Boneist asked for a solution using a single SELECT statement. Here is one, using not one but two MATCH_RECOGNIZE clauses! The basic idea is, SQL doesn't let us juggle two input streams, so I put the input into one stream and then figure out which rows to scrunch together.

    select * from (
      select BUS_ID, CAPACITY, SPOT, PASSENGER_ID,
        coalesce(b.arrival_time, p.arrival_time) arrival_time
      from bus b
      cross apply(
        select level spot from dual connect by level <= capacity
      )
      full join passenger p on 1=0
    )
    match_recognize (
      order by arrival_time, PASSENGER_ID, spot
      measures classifier() cl, match_number() mn,
        case classifier() when 'B' then count(b.*) else count(p.*) end rn
      all rows per match
      pattern(p+ b+)
      define b as bus_id is not null,
        p as passenger_id is not null
    )
    match_recognize(
      order by mn, rn, cl
      measures first(bus_id) bus_id, first(capacity) capacity,
        first(arrival_time) b_arrival, first(spot) spot,
        last(passenger_id) passenger_id, last(arrival_time) p_arrival
      pattern(a+)
      define a as (mn, rn, 'B') = (( first(mn), first(rn), first(cl) ))
    )

Answer 3

Based on your SQL Server code of:

DECLARE @id INT
DECLARE   @arrival int    
DECLARE cur CURSOR FOR
SELECT p.passenger_id, P.arrival_time
FROM dbo.passenger p
ORDER BY p.arrival_time, p.passenger_id

OPEN cur
FETCH NEXT FROM cur INTO @id, @arrival;

WITH cte_bus (bus_id, capacity, b_arrival, spot)
AS (
SELECT bus_id, capacity, arrival_time AS b_arrival, 1 AS spot FROM bus 
UNION ALL
SELECT bus_id, capacity, b_arrival, spot+1 FROM cte_bus
WHERE spot < capacity
)
SELECT *, NULL AS passenger_id, NULL AS p_arrival
INTO #tmp
FROM cte_bus ORDER BY bus_id, spot  
    
WHILE @@FETCH_STATUS = 0 BEGIN                   
UPDATE TOP (1) #tmp SET passenger_id = @id, p_arrival = @arrival WHERE b_arrival>=@arrival AND passenger_id IS NULL  
FETCH NEXT FROM cur INTO @id, @arrival        
END
CLOSE cur
DEALLOCATE cur
SELECT * FROM #tmp t
order by 1

I think what you need is to create a global temporary table (GTT), which is a permanent table but holds data on a per-session basis (n.b. the default is to delete data when a commit is issued after you have populated the GTT):

create global temporary table tmp (bus_id number,
                                   capacity number,
                                   b_arrival number,
                                   spot number,
                                   passenger_id number,
                                   p_arrival number);

and the following does what you want:

BEGIN
  -- initial population of the GTT
  insert into tmp (bus_id,
                   capacity,
                   b_arrival,
                   spot)
  WITH cte_bus (bus_id, capacity, b_arrival, spot)
       AS (SELECT bus_id, capacity, arrival_time AS b_arrival, 1 AS spot FROM bus 
           UNION ALL
           SELECT bus_id, capacity, b_arrival, spot+1 FROM cte_bus
           WHERE spot < capacity)
  SELECT bus_id,
         capacity,
         b_arrival,
         spot
  FROM   cte_bus 
  ORDER BY bus_id,
           spot;

  -- loop through the passengers and assign them to a bus
  for rec in (SELECT p.passenger_id, P.arrival_time
              FROM passenger p
              ORDER BY p.arrival_time, p.passenger_id)
  loop
    -- update the tmp GTT
    -- find the lowest row that hasn't already got a passenger assigned
    -- and update that row to assign the current passenger to it.
    merge into tmp tgt
    using (select bus_id,
                  capacity,
                  b_arrival,
                  spot,
                  row_number() over (order by bus_id, spot) rn
           from   tmp
           where  b_arrival >= rec.arrival_time
           and    passenger_id is null) src
      on (tgt.bus_id = src.bus_id
          and tgt.spot = src.spot
          and src.rn = 1)
    when matched then
    update set tgt.passenger_id = rec.passenger_id,
               tgt.p_arrival = rec.arrival_time;
  END LOOP;
END;
/

See this dbfiddle for results.

I still think it would be possible to output the results just using a single SELECT statement (probably via use of the MODEL clause), but I lack the wherewithal to do that.