Can someone plese tell me how to test the function below I want to know if it
works.
CREATE OR REPLACE FUNCTION Median_Grade
(p_course_no SECTION.course_no%TYPE,
p_section_no SECTION.section_no%TYPE,
p_grade_type_cd GRADE_TYPE.grade_type_code%TYPE)
RETURN GRADE.numeric_grade%TYPE
AS
CURSOR c_grade IS
SELECT g.numeric_grade, ROWNUM
FROM GRADE g, SECTION s
WHERE g.section_id = s.section_id
AND s.section_no = p_section_no
AND s.course_no = p_course_no
AND g.grade_type_code = p_grade_type_cd
ORDER BY numeric_grade;
---Table to hold out grades------------------------------------
TYPE t_grade_list_type IS TABLE OF
c_grade%ROWTYPE
INDEX BY BINARY_INTEGER;
t_grade_list t_grade_list_type;
BEGIN
-- Loop PL/SQL table with list of grades
FOR r_grade IN c_grade
LOOP
t_grade_list(NVL(t_grade_list.COUNT, 0) +
1).numeric_grade := r_grade.numeric_grade;
END LOOP;
----Median calculation starts here-------------------
IF MOD(t_grade_list.COUNT,2) = 0
THEN
--If grades in table is even
- take two middle and average the grade with this return-----------:
RETURN
(t_grade_list(t_grade_list.COUNT / 2).numeric_grade
+ t_grade_list((t_grade_list.COUNT / 2) +
1).numeric_grade) / 2;
ELSE
RETURN t_grade_list(TRUNC(t_grade_list.COUNT / 2, 0)
+ 1).numeric_grade;
END IF;
END Median_Grade;
/
Received on Fri Dec 14 2001 - 21:45:50 CST
