Untitled

-- TRIGGER 11: Trigger before inserting a row into Employee table
CREATE OR REPLACE TRIGGER before_insert_employee
BEFORE INSERT ON employee
FOR EACH ROW
BEGIN
    DBMS_OUTPUT.PUT_LINE('New employee ' || :NEW.employee_name || ' is being added.');
END;
/

-- PACKAGE SPECIFICATION
CREATE OR REPLACE PACKAGE School_api AS
    PROCEDURE Get_name_address(
        p_table_name VARCHAR2,
        p_id NUMBER,
        p_first_name OUT VARCHAR2,
        p_last_name OUT VARCHAR2,
        p_street OUT VARCHAR2,
        p_city OUT VARCHAR2,
        p_state OUT VARCHAR2,
        p_zip OUT VARCHAR2
    );

    FUNCTION Instructor_status(p_instructor_id NUMBER) RETURN VARCHAR2;

    PROCEDURE remove_student(p_student_id NUMBER, p_ri VARCHAR2 DEFAULT 'R');
END School_api;
/

-- PACKAGE BODY
CREATE OR REPLACE PACKAGE BODY School_api AS

    -- PROCEDURE: Get_name_address
    PROCEDURE Get_name_address(
        p_table_name VARCHAR2,
        p_id NUMBER,
        p_first_name OUT VARCHAR2,
        p_last_name OUT VARCHAR2,
        p_street OUT VARCHAR2,
        p_city OUT VARCHAR2,
        p_state OUT VARCHAR2,
        p_zip OUT VARCHAR2
    ) AS
        v_sql VARCHAR2(1000);
    BEGIN
        v_sql := 'SELECT first_name, last_name, street, city, state, zip
                  FROM ' || p_table_name || ' WHERE id = :1';

        EXECUTE IMMEDIATE v_sql INTO p_first_name, p_last_name, p_street, p_city, p_state, p_zip USING p_id;
    END Get_name_address;

    -- FUNCTION: Instructor_status
    FUNCTION Instructor_status(p_instructor_id NUMBER) RETURN VARCHAR2 AS
        v_count NUMBER;
    BEGIN
        SELECT COUNT(*) INTO v_count FROM sections WHERE instructor_id = p_instructor_id;

        IF v_count >= 3 THEN
            RETURN 'Instructor needs a vacation!';
        ELSE
            RETURN 'Instructor is teaching ' || v_count || ' sections.';
        END IF;
    END Instructor_status;

    -- PROCEDURE: Remove Student
    PROCEDURE remove_student(p_student_id NUMBER, p_ri VARCHAR2 DEFAULT 'R') AS
        v_count NUMBER;
    BEGIN
        -- Check enrollments for DELETE RESTRICT
        IF p_ri = 'R' THEN
            SELECT COUNT(*) INTO v_count FROM enrollments WHERE student_id = p_student_id;

            IF v_count > 0 THEN
                RAISE_APPLICATION_ERROR(-20001, 'Cannot delete student with enrollments.');
            END IF;
        ELSIF p_ri = 'C' THEN
            -- Perform DELETE CASCADE
            DELETE FROM enrollments WHERE student_id = p_student_id;
            DELETE FROM payments WHERE student_id = p_student_id;
        ELSE
            RAISE_APPLICATION_ERROR(-20002, 'Invalid option. Use R (Restrict) or C (Cascade).');
        END IF;

        -- Delete the student
        DELETE FROM students WHERE student_id = p_student_id;
        COMMIT;
    END remove_student;

END School_api;
/

-- TESTING THE PACKAGE
DECLARE
    v_first_name VARCHAR2(50);
    v_last_name VARCHAR2(50);
    v_street VARCHAR2(100);
    v_city VARCHAR2(50);
    v_state VARCHAR2(50);
    v_zip VARCHAR2(10);
    v_status VARCHAR2(100);
BEGIN
    -- Get Name and Address of a Student or Instructor
    School_api.Get_name_address('students', 101, v_first_name, v_last_name, v_street, v_city, v_state, v_zip);
    DBMS_OUTPUT.PUT_LINE('Name: ' || v_first_name || ' ' || v_last_name);
    DBMS_OUTPUT.PUT_LINE('Address: ' || v_street || ', ' || v_city || ', ' || v_state || ' - ' || v_zip);

    -- Get Instructor Status
    v_status := School_api.Instructor_status(202);
    DBMS_OUTPUT.PUT_LINE(v_status);

    -- Remove a Student (Use 'R' for Restrict, 'C' for Cascade)
    -- School_api.remove_student(105, 'R'); -- Restrict Delete
    -- School_api.remove_student(105, 'C'); -- Cascade Delete
END;
/

-- LOBs: Storing and Retrieving Large Objects
DECLARE
    v_file BLOB;
    v_text CLOB;
BEGIN
    -- Storing a BLOB (Binary Large Object)
    SELECT document INTO v_file FROM documents WHERE doc_id = 1;

    -- Storing a CLOB (Character Large Object)
    SELECT notes INTO v_text FROM student_notes WHERE student_id = 101;

    DBMS_OUTPUT.PUT_LINE('LOBs Retrieved Successfully.');
END;
/

-- TRANSACTION PROCESSING EXAMPLES
BEGIN
    SAVEPOINT start_trans;

    -- Example INSERT operation
    INSERT INTO students (student_id, first_name, last_name, department) VALUES (110, 'John', 'Doe', 'CS');
   
    -- Example UPDATE operation
    UPDATE students SET department = 'Math' WHERE student_id = 110;

    -- Commit transaction
    COMMIT;

EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK TO start_trans;
        DBMS_OUTPUT.PUT_LINE('Transaction failed, changes rolled back.');
END;
/