Changes between Version 56 and Version 57 of DatabaseProgramming


Ignore:
Timestamp:
05/20/26 22:03:40 (6 days ago)
Author:
231109
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • DatabaseProgramming

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