Scribd Logo
Upload

Welcome to Scribd!

  • 5 views

    DSAD

    Uploaded by

    rahulduraisamy761
    The document contains 14 code blocks that define various PL/SQL programs including procedures, functions, and triggers. The code blocks demonstrate: 1) A loop that reverses integers from 1 to 100 and prints the results. 2) A procedure that finds the greatest of three numbers. 3) A procedure that generates the Fibonacci sequence up to a given number. 4) Several procedures and triggers related to flight, reservation, and airport data including fetching data from tables, checking reservation dates, updating related records, and more.

    Copyright:

    © All Rights Reserved
    Download as TXT, PDF, TXT or read online from Scribd

    DSAD

    Uploaded by

    rahulduraisamy761
    5 views5 pages
    The document contains 14 code blocks that define various PL/SQL programs including procedures, functions, and triggers. The code blocks demonstrate: 1) A loop that reverses integers from 1 to 100 and prints the results. 2) A procedure that finds the greatest of three numbers. 3) A procedure that generates the Fibonacci sequence up to a given number. 4) Several procedures and triggers related to flight, reservation, and airport data including fetching data from tables, checking reservation dates, updating related records, and more.

    Copyright

    © © All Rights Reserved

    Available Formats

    TXT, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    The document contains 14 code blocks that define various PL/SQL programs including procedures, functions, and triggers. The code blocks demonstrate: 1) A loop that reverses integers from 1 to 100 and prints the results. 2) A procedure that finds the greatest of three numbers. 3) A procedure that generates the Fibonacci sequence up to a given number. 4) Several procedures and triggers related to flight, reservation, and airport data including fetching data from tables, checking reservation dates, updating related records, and more.

    Copyright:

    © All Rights Reserved
    Download as TXT, PDF, TXT or read online from Scribd
    Download as txt, pdf, or txt
    5 views5 pages

    DSAD

    Uploaded by

    rahulduraisamy761
    The document contains 14 code blocks that define various PL/SQL programs including procedures, functions, and triggers. The code blocks demonstrate: 1) A loop that reverses integers from 1 to 100 and prints the results. 2) A procedure that finds the greatest of three numbers. 3) A procedure that generates the Fibonacci sequence up to a given number. 4) Several procedures and triggers related to flight, reservation, and airport data including fetching data from tables, checking reservation dates, updating related records, and more.

    Copyright:

    © All Rights Reserved
    Download as TXT, PDF, TXT or read online from Scribd
    Download as txt, pdf, or txt
    of 5

    1)

    DECLARE
    i NUMBER;
    rev_number NUMBER;
    original_num NUMBER;
    BEGIN
    FOR i IN 1..100 LOOP
    original_num := i;
    rev_number := 0;
    WHILE original_num > 0 LOOP
    rev_number := rev_number * 10 + MOD(original_num, 10);
    original_num := FLOOR(original_num / 10);
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('Reverse of ' || i || ' is ' || rev_number);
    END LOOP;
    END;
    /

    2)
    DECLARE
    num1 NUMBER := 70;
    num2 NUMBER := 100;
    num3 NUMBER := 777;
    greatest_num NUMBER;
    BEGIN
    IF num1 >= num2 AND num1 >= num3 THEN
    greatest_num := num1;
    ELSIF num2 >= num1 AND num2 >= num3 THEN
    greatest_num := num2;
    ELSE
    greatest_num := num3;
    END IF;

    DBMS_OUTPUT.PUT_LINE('The greatest number is: ' || greatest_num);


    END;
    /

    3)
    DECLARE
    n NUMBER := 14;
    a NUMBER := 0;
    b NUMBER := 1;
    fib_number NUMBER;
    i NUMBER;
    BEGIN
    DBMS_OUTPUT.PUT_LINE('Fibonacci Series:');
    DBMS_OUTPUT.PUT_LINE(a);
    DBMS_OUTPUT.PUT_LINE(b);

    FOR i IN 3..n LOOP


    fib_number := a + b;
    DBMS_OUTPUT.PUT_LINE(fib_number);
    a := b;
    b := fib_number;
    END LOOP;
    END;
    /
    4)

    DECLARE
    reservation_date DATE := TO_DATE('2023-11-05', 'YYYY-MM-DD');
    today_date DATE := TRUNC(SYSDATE);
    BEGIN
    IF reservation_date < today_date THEN
    RAISE_APPLICATION_ERROR(-20001, 'Reservation date cannot be in the past');
    ELSE
    DBMS_OUTPUT.PUT_LINE('Reservation date is valid');
    END IF;
    EXCEPTION
    WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('An error occurred: ');
    END;
    /

    5)

    DECLARE
    CURSOR flight_cursor IS
    SELECT Flight_number,Airline,Weekdays from FLiGHT1;

    flight_rec flight_cursor%ROWTYPE;

    BEGIN
    OPEN flight_cursor;
    LOOP
    FETCH flight_cursor INTO flight_rec;
    EXIT WHEN flight_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('Flight_number :' || flight_rec.Flight_number);
    DBMS_OUTPUT.PUT_LINE('Airline ' || flight_rec.Airline);
    DBMS_OUTPUT.PUT_LINE('Weekdays: ' || flight_rec.Weekdays);
    DBMS_OUTPUT.NEW_LINE;
    END LOOP;
    CLOSE flight_cursor;
    END;
    /

    DECLARE
    CURSOR flight_cursor IS
    SELECT flight_id, flight_name, departure_airport_id,
    destination_airport_id, departuer_time
    FROM flights;

    flight_rec flight_cursor%ROWTYPE;

    BEGIN
    OPEN flight_cursor;
    LOOP
    FETCH flight_cursor INTO flight_rec;
    EXIT WHEN flight_cursor%NOTFOUND; DBMS_OUTPUT.PUT_LINE('Flight ID: '
    || flight_rec.flight_id);
    DBMS_OUTPUT.PUT_LINE('Flight Name: ' || flight_rec.flight_name);
    DBMS_OUTPUT.PUT_LINE('Departure Airport ID: ' ||
    flight_rec.departure_airport_id);
    DBMS_OUTPUT.PUT_LINE('Destination Airport ID: ' ||
    flight_rec.destination_airport_id);
    DBMS_OUTPUT.PUT_LINE('Departure Time: ' || flight_rec.departuer_time);
    DBMS_OUTPUT.NEW_LINE;
    END LOOP;
    CLOSE flight_cursor;
    END;
    /

    6)

    DECLARE
    CURSOR flight_cursor IS
    SELECT *
    FROM flights
    WHERE flight_id BETWEEN 1200 AND 2900;
    flight_rec flights%ROWTYPE;

    BEGIN
    OPEN flight_cursor;
    LOOP
    FETCH flight_cursor INTO flight_rec;
    EXIT WHEN flight_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('Flight ID: ' || flight_rec.flight_id);
    DBMS_OUTPUT.PUT_LINE('Flight Name: ' || flight_rec.flight_name);
    DBMS_OUTPUT.PUT_LINE('Departure Airport ID: ' ||
    flight_rec.departure_airport_id);
    DBMS_OUTPUT.PUT_LINE('Destination Airport ID: ' ||
    flight_rec.destination_airport_id);
    DBMS_OUTPUT.PUT_LINE('DepartureTime: ' || flight_rec.departuer_time);
    DBMS_OUTPUT.NEW_LINE;
    END LOOP;
    CLOSE flight_cursor;
    END;
    /

    7)

    CREATE OR REPLACE PROCEDURE display_reservation_details (p_customer_name IN


    VARCHAR2) ASv_reservation_details reservations.reservation_details%TYPE;
    BEGIN
    SELECT reservation_details
    INTO v_reservation_details
    FROM reservations
    WHERE customer_name = p_customer_name;

    DBMS_OUTPUT.PUT_LINE('Reservation details for ' || p_customer_name || ': ' ||


    v_reservation_details);
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('No reservation details found for ' ||
    p_customer_name);
    WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('An error occurred.');
    END;
    /
    8)

    CREATE OR REPLACE PROCEDURE display_company_airplane IS


    CURSOR company_airplane_cur IS
    SELECT c.company, a.airplane_id FROM Companies c
    JOIN Airplanes a
    ON c.company_id = a.company_id;

    company_name_var VARCHAR2(100);
    airplane_id_var NUMBER;
    BEGIN
    OPEN company_airplane_cur;
    LOOP
    FETCH company_airplane_cur INTO company_name_var, airplane_id_var;
    EXIT WHEN company_airplane_cur%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('Company: ' || company_name_var || ' | Airplane ID: ' ||
    airplane_id_var);
    END LOOP;
    CLOSE company_airplane_cur;
    END;
    /

    9)

    CREATE OR REPLACE FUNCTION get_arriving_flights_count ( p_airport_code VARCHAR2


    ) RETURN NUMBER IS
    v_flight_count NUMBER;
    BEGIN
    SELECT COUNT(*) INTO v_flight_count
    FROM Flights
    WHERE arrival_airport_code = p_airport_code;

    RETURN v_flight_count;
    END;
    /

    10)

    SELECT a.airport_name
    FROM Airports a
    JOIN (
    SELECT ap.airport_code, COUNT(DISTINCT ap.airplane_type) as type_count
    FROM Airplanes ap
    GROUP BY ap.airport_code
    ORDER BY type_count DESC
    ) t
    ON a.airport_code = t.airport_code
    WHERE ROWNUM = 1;

    11)

    CREATE OR REPLACE TRIGGER update_airplane_type


    AFTER UPDATE OF airplane_type ON Airports
    FOR EACH ROW
    BEGIN
    UPDATE Airplanes
    SET airplane_type = :new.airplane_type
    WHERE airport_code = :new.airport_code;
    END;
    /

    12)

    CREATE OR REPLACE TRIGGER delete_foreign_key_references


    BEFORE DELETE ON parent_table
    FOR EACH ROW
    BEGIN
    DELETE FROM Child1 WHERE parent_id = :old.parent_id;
    DELETE FROM Child2 WHERE parent_id = :old.parent_id;
    END;
    /

    13)

    CREATE OR REPLACE TRIGGER check_reservation_date


    BEFORE INSERT ON Reservations
    FOR EACH ROW
    DECLARE
    v_date DATE;
    BEGIN
    v_date := :new.reservation_date;
    IF v_date < SYSDATE THEN
    RAISE_APPLICATION_ERROR(-20001, 'Invalid reservation date. Date should be
    in the future.');
    END IF;
    EXCEPTION
    WHEN OTHERS THEN
    RAISE;
    END;
    /

    14)

    CREATE OR REPLACE TRIGGER update_myflight


    AFTER INSERT ON flight
    FOR EACH ROW
    BEGIN
    -- Update the myflight table with the corresponding data from the flight table
    INSERT INTO myflight (flight_id, flight_name, other_columns)
    VALUES (:new.flight_id, :new.flight_name, :new.other_columns);
    END;
    /

    You might also like