| functions and procedures..... [message #4345] |
Sat, 30 November 2002 07:55  |
pdyson
Messages: 1
Registered: November 2002
|
Junior Member |
|
|
I am creating 3 different functions that will run within a procedure: All of the functions create and the procedure runs...but it won't give me any DBMS statements out and if I try creating a procedure to run just one of the functions..I get the error..function returned without a value?? So, obviously my functions are not correct..this is a school project and I am a complete newbie...help!!!
Here are my functions and procedure:
CREATE OR REPLACE FUNCTION credit_calc
(new_sec_id COURSE_SECTION.c_sec_id%TYPE)
RETURN NUMBER IS
new_credits NUMBER;
BEGIN
SELECT credits
INTO new_credits
FROM course
WHERE course_id IN (SELECT course_id
FROM course_section
WHERE c_sec_id = new_sec_id);
RETURN new_credits;
END;
/
CREATE OR REPLACE FUNCTION point_calc
(new_grade enrollment.grade%TYPE,
new_credits course.credits%TYPE)
RETURN NUMBER IS
new_points NUMBER;
BEGIN
new_points := (new_grade * new_credits);
END;
/
CREATE OR REPLACE FUNCTION gpa_calc
(new_s_id student.s_id%TYPE)
RETURN NUMBER IS
new_gpa NUMBER (3,2);
t_points student.total_points%TYPE;
t_credits student.total_credits%TYPE;
BEGIN
SELECT total_points/total_credits
INTO new_gpa
FROM student
WHERE s_id = new_s_id;
RETURN new_gpa;
end;
/
CREATE OR REPLACE PROCEDURE student_record
(new_s_id student.s_id%TYPE,
new_sec_id enrollment.c_sec_id%TYPE,
new_grade enrollment.grade%TYPE)
IS
t_points student.total_points%TYPE;
t_credits student.total_credits%TYPE;
n_gpa student.gpa%TYPE;
BEGIN
t_points :=point_calc (new_grade, t_credits);
UPDATE student
SET total_points = total_points + t_points
WHERE s_id = new_s_id;
t_credits := credit_calc(new_sec_id);
UPDATE student
SET total_credits =total_credits + t_credits
WHERE s_id = new_s_id;
n_gpa := gpa_calc(new_s_id);
UPDATE student
SET gpa = n_gpa
WHERE s_id = new_s_id;
END;
/
When I run these it actually takes away the values that reside in the database before it runs. BAD!!!
Thanks in advance for any help!!!
|
|
|
|
| Re: functions and procedures..... [message #4364 is a reply to message #4345] |
Mon, 02 December 2002 20:56  |
 |
Barbara Boehmer
Messages: 9094
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
You left the return clause out of the body of your second function:
CREATE OR REPLACE FUNCTION point_calc
(new_grade enrollment.grade%TYPE,
new_credits course.credits%TYPE)
RETURN NUMBER
IS
new_points NUMBER;
BEGIN
new_points := (new_grade * new_credits);
-- add line below:
RETURN new_points;
END;
/
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
