| Function in procedure to insert [message #22349] |
Mon, 07 October 2002 05:23  |
Mark
Messages: 284
Registered: July 1998
|
Senior Member |
|
|
The program below calculates and displays a students gpa. I want to update a column(s_gpa)in the student table using a function, and then putting that function in an update procedure to update the s_gpa column. The function should receiev a student id(s_id) as input and returns the gpa. Not sure how to process this?
Thanks and hope this is clear,
DECLARE
CURSOR C1 IS
SELECT distinct s.s_id, s_last,s_first
FROM student s,enrollment e
WHERE s.s_id = e.s_id
AND grade is not null;
s_c C1%ROWTYPE;
CURSOR C2 IS
SELECT s_id,credits,grade
FROM enrollment e,course_section cs, course c
WHERE cs.course_id=c.course_id
AND e.c_sec_id=cs.c_sec_id;
s_c2 C2%ROWTYPE;
--declare var
num_grade number;
t_gpt NUMBER;
t_cred NUMBER;
tt_cred NUMBER;
gpa NUMBER(3,2);
line VARCHAR2(50):='+ +';
--open cursor 1 to determine data to be processed
BEGIN
FOR s_c IN C1 LOOP
--zero the accumulator and print
t_gpt:=0;
tt_cred:=0;
dbms_output.put_line(line);
dbms_output.put_line('Student: '||s_c.s_first||' '||S_C.S_LAST);
dbms_output.put_line('s_id '||' = '||s_c.s_id);
-- open cursor 2 to calculate gpa-matching id-and convert grade to number
FOR S_C2 IN C2 LOOP
IF s_c.s_id = s_c2.s_id THEN
CASE
WHEN s_c2.grade='A' THEN num_grade:=4;
WHEN s_c2.grade='B' THEN num_grade:=3;
WHEN s_c2.grade='C' THEN num_grade:=2;
WHEN s_c2.grade='D' THEN num_grade:=1;
WHEN s_c2.grade='F' THEN num_grade:=0;
ELSE NULL;
END CASE;
--calculate totals for gpa
t_cred:=s_c2.credits * num_grade;
t_gpt:= t_gpt + t_cred;
tt_cred:= tt_cred + s_c2.credits;
gpa:=t_gpt / tt_cred;
END IF;
END LOOP;
dbms_output.put_line('GPA '||to_char(gpa,9.99));
END LOOP;
END;
/
results:
SQL> @a:4bcase9
+ +
Student: Sarah Miller
s_id = 100
GPA 3.50
+ +
Student: Brian Umato
s_id = 101
GPA 3.00
+ +
Student: Daniel Black
s_id = 102
GPA 2.00
+ +
Student: Ruben Sanchez
s_id = 104
GPA 2.20
PL/SQL procedure successfully completed.
|
|
|
|
| Re: Function in procedure to insert [message #22354 is a reply to message #22349] |
Mon, 07 October 2002 09:34  |
Amit Chauhan
Messages: 74
Registered: July 1999
|
Member |
|
|
Hi,
Think of something like this :
create function calculate_gpa (s_id IN number) returns number
...
gpa number;
...
begin
...
return gpa;
end;
Then if you want to call this function from a procedure, simply call it in you for loop as above :
gpa = calulate_gpa (s_id);
or you can even call it from you UPDATE SQL query :
UPDATE students
SET gpa = (SELECT calulate_gpa (s_id) from dual)
Hope that helps a little.
Thanks
Amit
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
