SET SERVEROUTPUT ON;
ACCEPT redeem_id CHAR PROMPT 'Enter Redeem ID: ';
ACCEPT matric_num CHAR PROMPT 'Enter Matric Number: ';
ACCEPT prod_id CHAR PROMPT 'Enter Product ID: ';
DECLARE
v_prod_name VARCHAR2(100);
v_req_points NUMBER;
v_total_points NUMBER;
v_remaining_points NUMBER;
BEGIN
SELECT prod_name INTO v_prod_name
FROM cat_management
WHERE prod_id = :prod_id;
SELECT req_points INTO v_req_points
FROM cat_management
WHERE prod_id = :prod_id;
SELECT total_points INTO v_total_points
FROM point_calc
WHERE matric_num = :matric_num;
v_remaining_points := v_total_points - v_req_points;
INSERT INTO prod_redemption (redeem_id, matric_num, redeem_date, redeem_time, prod_id, prod_name, req_points, remaining_points)
VALUES (:redeem_id, :matric_num, SYSDATE, TO_CHAR(SYSDATE, 'HH24:MI:SS'), :prod_id, v_prod_name, v_req_points, v_remaining_points);
DBMS_OUTPUT.PUT_LINE('Redemption successfully registered.');
DBMS_OUTPUT.PUT_LINE('Redeem ID: ' :redeem_id);
DBMS_OUTPUT.PUT_LINE('Matric Number: ' :matric_num);
DBMS_OUTPUT.PUT_LINE('Product ID: ' :prod_id);
DBMS_OUTPUT.PUT_LINE('Product Name: ' v_prod_name);
DBMS_OUTPUT.PUT_LINE('Remaining Points: ' v_remaining_points);
DBMS_OUTPUT.PUT_LINE('Current Date and Time: ' TO_CHAR(SYSDATE, 'DD-Mon-YYYY HH24:MI:SS'));
END;
/
Asked by sofiya k
(1 rep)
Jan 13, 2025, 02:26 PM
Last activity: Jan 13, 2025, 03:08 PM
Last activity: Jan 13, 2025, 03:08 PM