procedure-and-function.sql

--Function

--compile first
SET SERVEROUTPUT ON;
CREATE OR REPLACE FUNCTION PROC_GRADE_FUN(MARKS IN NUMBER,NAME_ IN VARCHAR2, ROLL_ IN NUMBER)
    RETURN VARCHAR2 IS
    RESULT VARCHAR2(50);
    BEGIN
        IF(MARKS >= 990 AND MARKS <= 1500)THEN
            RESULT := 'DISTINCTION';
        ELSIF(MARKS >= 900 AND MARKS <= 989)THEN
            RESULT := 'FIRST CLASS';
        ELSIF(MARKS > 825 AND MARKS <= 899)THEN
            RESULT := 'HIGHER SECOND CLASS';
        ELSE
            RESULT := 'PASS';
        END IF;
        RETURN RESULT;
    END;


SET SERVEROUTPUT ON;
DECLARE
    VAL VARCHAR2(50);
    NAME_ STUD_MARKS.NAME%TYPE;
    MARKS_ STUD_MARKS.TOTAL_MARKS%TYPE;
    ROLL STUD_MARKS.ROLL_NO%TYPE;
    CURSOR STUDENT IS
        SELECT NAME, TOTAL_MARKS, ROLL_NO FROM STUD_MARKS;
BEGIN
    OPEN STUDENT;
    LOOP
    FETCH STUDENT INTO NAME_, MARKS_, ROLL;
    VAL := PROC_GRADE_FUN(MARKS_, NAME_, ROLL);
    INSERT INTO RESULT VALUES(ROLL, NAME_, VAL);
        EXIT WHEN STUDENT%NOTFOUND;
    END LOOP;
END;

--procedure 

SET SERVEROUTPUT ON;
CREATE OR REPLACE PROCEDURE PROC_GRADE(MARKS IN NUMBER,NAME_ IN VARCHAR2, ROLL_ IN NUMBER)
    AS
    BEGIN
        IF(MARKS >= 990 AND MARKS <= 1500)THEN
            INSERT INTO RESULT VALUES(ROLL_, NAME_, 'DISTINCTION');
        ELSIF(MARKS >= 900 AND MARKS <= 989)THEN
            INSERT INTO RESULT VALUES(ROLL_, NAME_, 'FIRST CLASS');
        ELSIF(MARKS > 825 AND MARKS <= 899)THEN
            INSERT INTO RESULT VALUES(ROLL_, NAME_, 'HIGHER SECOND CLASS');
        ELSE
            INSERT INTO RESULT VALUES(ROLL_, NAME_, 'PASS');
        END IF;
    END;
    
    

SET SERVEROUTPUT ON;
DECLARE
    NAME_ STUD_MARKS.NAME%TYPE;
    MARKS_ STUD_MARKS.TOTAL_MARKS%TYPE;
    CURSOR STUDENT IS
        SELECT NAME, TOTAL_MARKS FROM STUD_MARKS;

BEGIN
    OPEN STUDENT;
    LOOP
    FETCH STUDENT INTO NAME_, MARKS_;
    PROC_GRADE(MARKS_, NAME_, ROLL_);
        EXIT WHEN STUDENT%NOTFOUND;
    END LOOP;
END;