对表类型中的数据执行计算 [英] perform calculations on data in table type

问题描述

1. 创建了一个记录类型-trec
2. 然后创建了上面记录类型为abc的表v1
3. 使用游标，已加载的学生，课程，通过获取和循环将多个表中的数据计入表类型v1.
4. 现在我想根据v1计算特定项的平均得分.

有人可以建议如何对t_tab中存在的数据进行平均运算

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;    

请告诉我问题是否明确

推荐答案

是否有理由不在查询中选择它?

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;

这将在您的记录中添加一个名为avg_score的新字段，其中包含给定学生的平均分数.

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

或者，您可以为记录结构创建数据库级别的TYPE声明.如果要在对象类型上使用DML，则必须在数据库级别(而不是PL/SQL级别)创建DML，因为数据库不知道PL/SQL定义的类型.

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;

然后，在您的代码中:

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.

这篇关于对表类型中的数据执行计算的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！