DBMSL Ass 5 - Named Block (Proc and Func)

SET SERVEROUTPUT ON;

CREATE TABLE STUD_MARKS (
    roll            NUMBER              PRIMARY KEY,
    name            VARCHAR2(100),
    total_marks     NUMBER
);

CREATE TABLE RESULTS (
    roll            NUMBER              PRIMARY KEY,
    name            VARCHAR2(100),
    class           VARCHAR2(50),
    CONSTRAINT fk_results_studmarks FOREIGN KEY (roll) REFERENCES STUD_MARKS(roll)
);

CREATE OR REPLACE PROCEDURE proc_grade (p_roll IN NUMBER, p_name IN VARCHAR2, p_marks IN NUMBER)
IS
    v_class VARCHAR2(30);
BEGIN
    IF p_marks BETWEEN 990 AND 1500 THEN
        v_class := 'Distinction';
    ELSIF p_marks BETWEEN 900 AND 989 THEN
        v_class := 'First Class';
    ELSIF p_marks BETWEEN 825 AND 899 THEN
        v_class := 'Higher Second Class';
    ELSE
        v_class := 'NA';
    END IF;

    INSERT INTO RESULTS (roll, name, class)
    VALUES (p_roll, p_name, v_class);

    DBMS_OUTPUT.PUT_LINE('Roll: ' || p_roll || ', Name: ' || p_name || ', Class: ' || v_class);
END;

CREATE TRIGGER trg_compute_grade
AFTER INSERT ON STUD_MARKS
FOR EACH ROW
BEGIN
    proc_grade(:NEW.roll, :NEW.name, :NEW.total_marks);
END;

INSERT INTO STUD_MARKS (roll, name, total_marks) VALUES (1, 'Adam', 995);
INSERT INTO STUD_MARKS (roll, name, total_marks) VALUES (2, 'Bob', 910);
INSERT INTO STUD_MARKS (roll, name, total_marks) VALUES (3, 'Charles', 870);
INSERT INTO STUD_MARKS (roll, name, total_marks) VALUES (4, 'Daniel', 800);

SELECT * FROM RESULTS;