cursors

CREATE TABLE STUDENTS
(
ROLL_NO NUMBER(3),
NAME VARCHAR2(22),
MARK1 NUMBER(5),
MARK2 NUMBER(5),
MARK3 NUMBER(5)
)


INSERT INTO STUDENTS
VALUES(:ROLL_NOS,:NAMES,:MARK1,:MARK2,:MARK3)

CREATE TABLE MY_RESULTS
(
ROLL_NUM NUMBER(3),
NAMES VARCHAR2(22),
TOTAL_MARKS NUMBER(4),
RESULTS VARCHAR2(12)
)

DECLARE
CURSOR STU_CUR IS
SELECT ROLL_NO,NAME,MARK1,MARK2,MARK3 FROM hr.STUDENTS;
TYPE STU_RECS IS RECORD
(
S_ROLL HR.STUDENTS.ROLL_NO%TYPE,
S_NAME HR.STUDENTS.NAME%TYPE,
S_MARK1 HR.STUDENTS.MARK1%TYPE,
S_MARK2 HR.STUDENTS.MARK1%TYPE,
S_MARK3 HR.STUDENTS.MARK1%TYPE
);
R_TOTAL_MARKS HR.MY_RESULTS.TOTAL_MARKS%TYPE;
R_RESULTS HR.MY_RESULTS.RESULTS%TYPE;
ST_REC STU_RECS;
BEGIN
OPEN STU_CUR;
LOOP
FETCH STU_CUR INTO ST_REC;
EXIT WHEN(STU_CUR%NOTFOUND);
R_TOTAL_MARKS:=ST_REC.S_MARK1+ ST_REC.S_MARK2+ST_REC.S_MARK3;
IF(R_TOTAL_MARKS>100) THEN
R_RESULTS:='PASS';
ELSE
R_RESULTS:='FAIL';
END IF;
INSERT INTO HR.MY_RESULTS
VALUES(ST_REC.S_ROLL,ST_REC.S_NAME,R_TOTAL_MARKS,R_RESULTS);
END LOOP;
END;
/


SELECT * FROM HR.MY_RESULTS