Why coloumn not updating using in pl/SQL

See more:

Oracle

i am trying to calculate the cgpa of a student from his term gpa. and store the gpa value in gpa coloumn in the result table.

my code link:http://paste.ubuntu.com/21677789/.

finally it's showing the output: hello term1

1307001 2 1 2

hello year2

hello term1

1307001 3 1 1.7

hello year3

hello roll1307001

hello term1

1307002 2 1 1.71315789473684210526315789473684210526

hello year2

hello term1

1307002 3 1 1.76315789473684210526315789473684210526

hello year3

hello roll1307002

PL/SQL procedure successfully completed.

and gpa value is not updated in result value..why value of tt showing too long ...help please..thanks in advance.

What I have tried:

drop table result;
drop table cgpa_cal;
drop table courses;
drop table student;
create table student(
roll number(10),
fname varchar(30),
lname varchar(30),
department varchar(50)
);
ALTER TABLE student ADD PRIMARY KEY (roll)


create table courses(
c_id varchar(30) NOT NULL,
c_name varchar(30),
credit integer CHECK(credit>0 and credit<5)
);

ALTER TABLE COURSES ADD PRIMARY KEY (c_id)
ALTER TABLE COURSES ADD UNIQUE KEY (c_id)



create table result(
id number(2),
roll number(10),
c_id varchar(30),
marks number (6,2),
year number(3),
term number(3),
points number(3,2),
credit NUMBER CHECK(credit>0 and credit<5),
grade varchar (4),
gpa number(3,2)
);

ALTER TABLE result ADD FOREIGN KEY (roll) REFERENCES student(roll)
ALTER TABLE result ADD FOREIGN KEY (c_id) REFERENCES courses(c_id)



describe student;
describe courses;
describe result




insert into student (roll,fname,lname,department) values (1307001,'a','ss','cse');
insert into student (roll,fname,lname,department) values (1307002,'b','gg','cse');
insert into student (roll,fname,lname,department) values (1307003,'c','ll','cse');
insert into student (roll,fname,lname,department) values (1307004,'d','kk','cse');


insert into courses (c_id,c_name,credit) values ('cse3101','toc',3.00);
insert into courses (c_id,c_name,credit) values ('cse3103','micro',3.00);
insert into courses (c_id,c_name,credit) values ('cse3109','database',3.00);
insert into courses (c_id,c_name,credit) values ('cse3119','se',3.00);


set serveroutput on
CREATE TRIGGER TR_GRAD
BEFORE INSERT OR UPDATE ON result
FOR EACH ROW
BEGIN
IF :NEW.MARKS>=80 THEN
:NEW.GRADE:='A+';
:NEW.POINTS:=4.00;
ELSIF :NEW.MARKS>=70 AND :NEW.MARKS<80 THEN
:NEW.GRADE:='B';
:NEW.POINTS:=3.30;
ELSIF :NEW.MARKS>=60 AND :NEW.MARKS<70 THEN
:NEW.GRADE:='C';
:NEW.POINTS:=3.10;
ELSIF :NEW.MARKS>=50 AND :NEW.MARKS<60 THEN
:NEW.GRADE:='D';
:NEW.POINTS:=3.00;
ELSIF :NEW.MARKS>40 AND :NEW.MARKS<50 THEN
:NEW.GRADE:='E';
:NEW.POINTS:=2.75;
ELSIF :NEW.MARKS<=40 THEN
:NEW.GRADE:='F';
:NEW.POINTS:=0.00;
END IF;
END TR_GRAD;
/



insert into result (id,roll,c_id,marks,year,term,credit,points,grade,gpa) values (1,1307001,'cse 3103',200,2,1,3,NULL,NULL,NULL);
insert into result (id,roll,c_id,marks,year,term,credit,points,grade,gpa) values (2,1307001,'cse 3105',200,2,1,3,NULL,NULL,NULL);
insert into result (id,roll,c_id,marks,year,term,credit,points,grade,gpa) values (3,1307001,'cse 3109',200,2,1,4,NULL,NULL,NULL);
insert into result (id,roll,c_id,marks,year,term,credit,points,grade,gpa) values (4,1307001,'cse 3119',200,2,1,3,NULL,NULL,NULL);
insert into result (id,roll,c_id,marks,year,term,credit,points,grade,gpa) values (5,1307001,'cse 3103',200,2,1,3,NULL,NULL,NULL);
insert into result (id,roll,c_id,marks,year,term,credit,points,grade,gpa) values (6,1307001,'cse 3103',200,2,1,3,NULL,NULL,NULL);

insert into result (id,roll,c_id,marks,year,term,credit,points,grade,gpa) values (1,1307001,'cse 3103',69,3,1,4,NULL,NULL,NULL);
insert into result (id,roll,c_id,marks,year,term,credit,points,grade,gpa) values (2,1307001,'cse 3103',78,3,1,3,NULL,NULL,NULL);
insert into result (id,roll,c_id,marks,year,term,credit,points,grade,gpa) values (3,1307001,'cse 3103',89,3,1,3,NULL,NULL,NULL);
insert into result (id,roll,c_id,marks,year,term,credit,points,grade,gpa) values (4,1307001,'cse 3103',57,3,1,3,NULL,NULL,NULL);
insert into result (id,roll,c_id,marks,year,term,credit,points,grade,gpa) values (5,1307001,'cse 3103',60,3,1,3,NULL,NULL,NULL);
insert into result (id,roll,c_id,marks,year,term,credit,points,grade,gpa) values (6,1307001,'cse 3103',90,3,1,3,NULL,NULL,NULL);

insert into result (id,roll,c_id,marks,year,term,credit,points,grade,gpa) values (1,1307002,'cse 3103',200,2,1,3,NULL,NULL,NULL);
insert into result (id,roll,c_id,marks,year,term,credit,points,grade,gpa) values (2,1307002,'cse 3103',64,2,1,3,NULL,NULL,NULL);
insert into result (id,roll,c_id,marks,year,term,credit,points,grade,gpa) values (3,1307002,'cse 3103',56,2,1,4,NULL,NULL,NULL);
insert into result (id,roll,c_id,marks,year,term,credit,points,grade,gpa) values (4,1307002,'cse 3103',80,2,1,3,NULL,NULL,NULL);
insert into result (id,roll,c_id,marks,year,term,credit,points,grade,gpa) values (5,1307002,'cse 3103',70,2,1,3,NULL,NULL,NULL);
insert into result (id,roll,c_id,marks,year,term,credit,points,grade,gpa) values (6,1307002,'cse 3103',78,2,1,3,NULL,NULL,NULL);

insert into result (id,roll,c_id,marks,year,term,credit,points,grade,gpa) values (1,1307002,'cse 3103',100,3,1,4,NULL,NULL,NULL);
insert into result (id,roll,c_id,marks,year,term,credit,points,grade,gpa) values (2,1307002,'cse 3103',70,3,1,3,NULL,NULL,NULL);
insert into result (id,roll,c_id,marks,year,term,credit,points,grade,gpa) values (3,1307002,'cse 3103',78,3,1,3,NULL,NULL,NULL);
insert into result (id,roll,c_id,marks,year,term,credit,points,grade,gpa) values (4,1307002,'cse 3103',60,3,1,3,NULL,NULL,NULL);
insert into result (id,roll,c_id,marks,year,term,credit,points,grade,gpa) values (5,1307002,'cse 3103',77,3,1,3,NULL,NULL,NULL);
insert into result (id,roll,c_id,marks,year,term,credit,points,grade,gpa) values (6,1307002,'cse 3103',89,3,1,3,NULL,NULL,NULL);

commit;



select * from student;
select * from courses;
select * from result;

SET SERVEROUTPUT ON;
DECLARE
rol result.roll%type;
total NUMBER ;
rid number(2);
pointt result.points%type;
credits result.credit%type;
summ result.credit%type;
gpa number;
digit number;
y result.year%type;
t result.term%type;
course result.c_id%type;
TT result.credit%type;
CR result.credit%type;

BEGIN
rol:=1307001;
y:=2;
t:=1;
rid:=1;
total:=0;
TT:=0;
course:=0;
SUMM:=0;
CR:=0;
LOOP
y:=2;
loop
t:=1;




RID:=1;
TT:=0;
TOTAL:=0;
CR:=0;
summ:=0;


LOOP
SELECT points,credit,year,term,c_id into pointt,credits,y,t,course FROM result WHERE roll=rol and year=y and term=t and id=rid;
total:=pointt;


TOTAL:=TOTAL*credits;


TT:=TT+TOTAL;

CR:=CR+credits;

RID:=RID+1;

EXIT WHEN RID>6;




END LOOP;


dbms_output.put_line('hello term'|| t);

SELECT SUM(CREDIT) INTO SUMM FROM result WHERE ROLL=ROL;

TT:=TT/SUMM;
update result set gpa=tt where rol=roll and id=1 and year=y and term=t;
dbms_output.put_line(rol||' '||y||' '||t||' '||tt);

dbms_output.put_line('hello year'|| y);
y:=y+1;
exit when y>3;
end loop;
dbms_output.put_line('hello roll'|| rol);
rol:=rol+1;
exit when rol>1307002;
end loop;




END;
/