CODEWITHSUNDEEP
oracle-databaseplsql
VNK
VNK

Reputation: 155

perform calculations on data in table type

  1. created a record type -- trec
  2. Then created a table v1 of above record type abc
  3. using a cursor, loaded student, course, scores data from multiple tables into the table type v1 using fetch and loop.
  4. now i would like to calculate the average score of a particular from the v1.

can some one suggest how to perform average operation on data existing in t_tab

set serveroutput on;
declare 
    type t_rec is record
    (
      student_id number,
      course_id number,
      grade number
    );
    error_msg varchar2(50);
    type abc is table of t_rec index by binary_integer;
    v1 abc;
    x number;
    cursor st_gr
    is 
      with sf as
      (
        select s.student_id as student_id, count(cr.course_id)as no_of_courses
        from grade g, student s, class cs, course cr
        where g.student_id = s.student_id
        and cs.course_id = cr.course_id
        and g.class_id = cs.class_id
        group by s.student_id
      )
      select x.student_id as student_id, cr.course_id as course_id, g.score as grade
      from sf x, grade g, class cs, course cr
      where x.no_of_courses>4 
      and x.student_id = g.student_id 
      and cs.course_id = cr.course_id
      and g.class_id = cs.class_id;

      i binary_integer :=0;
      temp binary_integer :=0;
begin
      open st_gr;
      loop
        i:=i+1;
        fetch st_gr into v1(i);
        exit when st_gr%notfound;
      end loop;
      close st_gr;
        dbms_output.put_line('students who enrolled in more than 4 courses');  
        dbms_output.put_line('student_id'||'    '||'course_id'||'          '||'grade');  
      select student_id, avg(grade)
      from v1
      group by student_id;
end;

pls let me know if question is not clear

Upvotes: 0

Views: 123

Answers (1)

DCookie
DCookie

Reputation: 43533

Is there a reason not to select it in your query?

select x.student_id as student_id, cr.course_id as course_i
     , g.score as grade, AVG(g.score) OVER (PARTITION BY x.student_id) as avg_score
 from sf x, grade g, class cs, course cr
where x.no_of_courses>4 
  and x.student_id = g.student_id 
  and cs.course_id = cr.course_id
  and g.class_id = cs.class_id;

That will put a new field in your record named avg_score, with the average score for a given student.

EDIT: Alternatively, you can create a database level TYPE declaration for the record structure. If you want to use DML on an object type, you'll have to create it at the database level, not at the PL/SQL level, since the database doesn't know about PL/SQL defined types.

CREATE OR REPLACE TYPE t_rec AS OBJECT
(
  student_id number,
  course_id number,
  grade number
);

CREATE OR REPLACE TYPE abc AS TABLE OF t_rec;

Then, in your code:

FOR Rec IN (SELECT student_id, AVG(grade) avg_grade 
              FROM TABLE ( cast( v1 as abc) )
             GROUP BY student_id) 
LOOP
   dbms_output.put_line(Rec.student_id, Rec.avg_grade);
END LOOP;

Totally untested. That's the general idea, though.

Upvotes: 0

Related Questions