Example 1.
a) Should include IN parameters.
b) Should include DML operations.
c) Should include declaring variables and using SELECT .. INTO statement.
d) Should include user-defined exception handling.
CREATE OR REPLACE PROCEDURE AddReservation(
p_client_id IN NUMBER,
p_computer_id IN NUMBER,
p_start_time IN TIMESTAMP,
p_end_time IN TIMESTAMP,
p_error_code OUT NUMBER,
p_error_desc OUT VARCHAR2
)
AS
v_computer_status VARCHAR2(20);
e_invalid_time EXCEPTION;
e_computer_unavailable EXCEPTION;
BEGIN
IF p_start_time >= p_end_time THEN
RAISE e_invalid_time;
END IF;
SELECT status INTO v_computer_status
FROM Computers
WHERE computer_id = p_computer_id;
IF v_computer_status != 'available' THEN
RAISE e_computer_unavailable;
END IF;
INSERT INTO Reservations (client_id, computer_id, start_time, end_time)
VALUES (p_client_id, p_computer_id, p_start_time, p_end_time);
UPDATE Computers
SET status = 'occupied'
WHERE computer_id = p_computer_id;
p_error_code := 0;
p_error_desc := 'Reservation added successfully';
EXCEPTION
WHEN e_invalid_time THEN
p_error_code := -1;
p_error_desc := 'Invalid time. Start time must be earlier than end time.';
WHEN e_computer_unavailable THEN
p_error_code := -2;
p_error_desc := 'Computer is not available for reservation.';
WHEN NO_DATA_FOUND THEN
p_error_code := -3;
p_error_desc := 'Computer ID does not exist.';
WHEN OTHERS THEN
p_error_code := SQLCODE;
p_error_desc := SQLERRM;
END;
/
Example 2.
a) Should include OUT parameters. OUT parameters must include parameters for error
code and error description. Use oracle provided functions for defining error code and
error text. (Hint: use SQLERRM, SQLCODE functions).
b) Should include using of explicit cursor. Create a temporary table, where you will write
records based on what excplicit cursor returns.
c) Should include IF/ELSE statement.
d) Should include error handling.
CREATE OR REPLACE PROCEDURE FilterAndLogReservations(
p_min_duration IN NUMBER,
p_error_code OUT NUMBER,
p_error_desc OUT VARCHAR2
)
AS
CURSOR reservation_cursor IS
SELECT r.reservation_id, r.client_id, r.computer_id, r.start_time, r.end_time,
ROUND((r.end_time - r.start_time) * 24 * 60) AS duration_minutes
FROM Reservations r;
v_reservation_id NUMBER;
v_client_id NUMBER;
v_computer_id NUMBER;
v_start_time TIMESTAMP;
v_end_time TIMESTAMP;
v_duration_minutes NUMBER;
e_invalid_duration EXCEPTION;
BEGIN
IF p_min_duration < 0 THEN
RAISE e_invalid_duration;
END IF;
EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE TempReservations (
reservation_id NUMBER,
client_id NUMBER,
computer_id NUMBER,
start_time TIMESTAMP,
end_time TIMESTAMP,
duration_minutes NUMBER
) ON COMMIT PRESERVE ROWS';
OPEN reservation_cursor;
LOOP
FETCH reservation_cursor INTO v_reservation_id, v_client_id, v_computer_id,
v_start_time, v_end_time, v_duration_minutes;
EXIT WHEN reservation_cursor%NOTFOUND;
IF v_duration_minutes >= p_min_duration THEN
INSERT INTO TempReservations (reservation_id, client_id, computer_id,
start_time, end_time, duration_minutes)
VALUES (v_reservation_id, v_client_id, v_computer_id, v_start_time,
v_end_time, v_duration_minutes);
END IF;
END LOOP;
CLOSE reservation_cursor;
p_error_code := 0;
p_error_desc := 'Reservations filtered and logged successfully.';
EXCEPTION
WHEN e_invalid_duration THEN
p_error_code := -1;
p_error_desc := 'Invalid duration. Minimum duration must be greater than or equal to
0.';
WHEN OTHERS THEN
p_error_code := SQLCODE;
p_error_desc := SQLERRM;
END;
/
