Home » SQL & PL/SQL » SQL & PL/SQL » HELP! Multiple Cursors
HELP! Multiple Cursors [message #205421] Fri, 24 November 2006 11:45 Go to next message
powder
Messages: 7
Registered: November 2006
Junior Member
I am new to SQL and need to write a program to calculate the cumulative GPA for students in a table. The instructions said to use 2 cursors - one to retrieve student data and one to retrieve course info and calculate each students gpa.
To get the info I need to calculate the gpa I have to convert the grade to points ('A' = 4, 'B' = 3...) and do the calculation based on this formula:
SUM(course credits*course grade points)/SUM(course credits). I kept getting many error messages and don't know how to solve it.
Here is my code:

DECLARE

v_s_id student.s_id%TYPE;
v_last student.s_last%TYPE;
v_first student.s_first%TYPE;
grade_points NUMBER;
calc_gpa NUMBER:= (course_credits * grade_points) / (course_credits);

CURSOR student_cursor IS
SELECT s_id, s_last, s_first
FROM student
WHERE s_id IN
(SELECT s.s_id
FROM student s, enrollment e, course c, course_section cs
WHERE c.course_id = cs.course_id
AND cs.c_sec_id = e.c_sec_id
AND e.s_id = s.s_id
AND e.grade IS NOT NULL);
student_row student_cursor%ROWTYPE;

CURSOR enrollment_cursor IS
(SELECT grade, credits
FROM student s,enrollment e, course c
WHERE s.s_id = e.s_id);
enrollment_row enrollment_cursor%ROWTYPE;

IF grade = 'A' THEN
grade_points := 4;
ELSIF grade = 'B' THEN
grade_points := 3;
ELSIF grade = 'C' THEN
grade_points := 2;
ELSIF grade = 'D' THEN
grade_points := 1;
END IF;

BEGIN

FOR enrollment_row IN enrollment_cursor LOOP
DBMS_OUTPUT.PUT_LINE('Student: ' || student_row.s_first || ' ' || student_row.s_last);
DBMS_OUTPUT.PUT_LINE('s_id' || ' = '|| student_row.s_id);
END LOOP;

DBMS_OUTPUT.PUT_LINE('GPA: ' || calc_gpa);
END LOOP;
END;
/


Any suggestions???
Thanks.
Re: HELP! Multiple Cursors [message #205977 is a reply to message #205421] Tue, 28 November 2006 04:47 Go to previous message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
So, what errors do you get? We cannot simulate the problem as we don't have you table structures.

Also, please indent your code!
Previous Topic: About Error
Next Topic: regarding ROLE
Goto Forum:
  


Current Time: Sun Dec 04 12:36:17 CST 2016

Total time taken to generate the page: 0.07282 seconds