Changes between Version 55 and Version 56 of DatabaseProgramming

Timestamp:

05/20/26 22:01:45 (6 days ago)

Author:

231109

Comment:

--

DatabaseProgramming

@@ -17 +17 @@
 [[Image("Screenshot 2026-05-20 160911.png", 300px)]]
 
+```sql
+CREATE OR REPLACE PROCEDURE pr_sell_ticket(
+3           IN p_passenger_embg CHAR(13),
+4           IN p_passenger_id INT4,
+5           IN p_trip_id INT4,
+6           IN p_start_station_id INT4,
+7           IN p_end_station_id INT4,
+8           IN p_seat_number INT4,
+9           IN p_carriage_number INT4,
+10          IN p_price NUMERIC(2, 0),
+11          IN p_payment_method VARCHAR(20),
+12          OUT o_ticket_id INT4,
+13          OUT o_payment_id INT4,
+14          IN p_reservation_id INT4 DEFAULT NULL
+15      )
+16      LANGUAGE plpgsql
+17      AS $$
+18      DECLARE
+19          v_passenger_exists INT;
+20          v_trip_exists INT;
+21          v_station_start_exists INT;
+22          v_station_end_exists INT;
+23          v_seat_taken INT;
+24      BEGIN
+25          SELECT COUNT(*) INTO v_passenger_exists
+26          FROM Passenger
+27          WHERE PersonEMBG = p_passenger_embg AND passenger_id = p_passenger_id;
+28      
+29          IF v_passenger_exists = 0 THEN
+30              RAISE EXCEPTION 'Passenger with EMBG % and ID % does not exist.', p_passenger_embg, p_passenger_id;
+31          END IF;
+32      
+33          SELECT COUNT(*) INTO v_trip_exists FROM "Train Trip" WHERE trip_id = p_trip_id;
+34          IF v_trip_exists = 0 THEN
+35              RAISE EXCEPTION 'Train Trip ID % does not exist.', p_trip_id;
+36          END IF;
+37      
+38          SELECT COUNT(*) INTO v_station_start_exists FROM Station WHERE station_id = p_start_station_id;
+39          SELECT COUNT(*) INTO v_station_end_exists FROM Station WHERE station_id = p_end_station_id;
+40          IF v_station_start_exists = 0 OR v_station_end_exists = 0 THEN
+41              RAISE EXCEPTION 'Invalid origin or destination station provided.';
+42          END IF;
+43      
+44          SELECT COUNT(*) INTO v_seat_taken
+45          FROM Ticket
+46          WHERE "Train Triptrip_id" = p_trip_id
+47            AND carriage_number = p_carriage_number
+48            AND seat_number = p_seat_number
+49            AND ticket_status = 'Active';
+50      
+51          IF v_seat_taken > 0 THEN
+52              RAISE EXCEPTION 'Seat % in Carriage % is already booked for Trip %.', p_seat_number, p_carriage_number, p_trip_id;
+53          END IF;
+54      
+55          IF p_reservation_id IS NOT NULL THEN
+56              UPDATE Reservation
+57              SET status = 'Completed'
+58              WHERE reservation_id = p_reservation_id;
+59          END IF;
+60      
+61          INSERT INTO Payment (
+62              payment_method,
+63              amount,
+64              transaction_date,
+65              Reservationreservation_id,
+66              Passengerpassenger_id,
+67              PassengerPersonEMBG2
+68          )
+69          VALUES (
+70              p_payment_method,
+71              p_price,
+72              CURRENT_DATE,
+73              p_reservation_id,
+74              p_passenger_id,
+75              p_passenger_embg
+76          )
+77          RETURNING payment_id INTO o_payment_id;
+78      
+79          INSERT INTO Ticket (
+80              seat_number,
+81              carriage_number,
+82              price,
+83              ticket_status,
+84              Paymentpayment_id,
+85              "Train Triptrip_id",
+86              Stationstation_id,
+87              Stationstation_id2
+88          )
+89          VALUES (
+90              p_seat_number,
+91              p_carriage_number,
+92              p_price,
+93              'Active',
+94              o_payment_id,
+95              p_trip_id,
+96              p_start_station_id,
+97              p_end_station_id
+98          )
+99          RETURNING ticket_id INTO o_ticket_id;
+100     
+101     EXCEPTION
+102         WHEN OTHERS THEN
+103             RAISE NOTICE 'Transaction rolled back due to error: %', SQLERRM;
+104             RAISE;
+105     END;
+106     $$;
+107     
+108     
+109     DO $$
+110     DECLARE
+111         v_ticket_output INT4;
+112         v_payment_output INT4;
+113     BEGIN
+114         CALL pr_sell_ticket(
+115             p_passenger_embg   => '2510985918703',
+116             p_passenger_id     => 290,
+117             p_trip_id          => 4,
+118             p_start_station_id => 10,
+119             p_end_station_id   => 14,
+120             p_seat_number      => 22,
+121             p_carriage_number  => 2,
+122             p_price            => 45,
+123             p_payment_method   => 'Apple Pay',
+124             o_ticket_id        => v_ticket_output,
+125             o_payment_id       => v_payment_output,
+126             p_reservation_id   => NULL
+127         );
+128     
+129         RAISE NOTICE 'Sale Complete! Ticket ID: %, Payment ID: %', v_ticket_output, v_payment_output;
+130     END $$;
+131     
+132     select * from ticket where ticket_id='23989062';
+
 Оваа процедура автоматски продава билет во системот за возови. Прво проверува дали постојат патникот, патувањето и станиците, и дали седиштето е слободно. Потоа креира плаќање и билет и ако постои резервација ја означува како "Completed". Се користи за автоматизирана и сигурна продажба на билети без грешки и дупли резервации.