ORACLE SQL - How to insert data with a user defined Function-CodePudding

I am a little bit new in Oracle and I am going to write a function which can insert data into another table in oracle SQL.

There is a Grade Table:

CREATE TABLE GRADE 
( ID NUMBER(38,0), 
  SUBJECTID NUMBER(38,0), 
  STUDENTID NUMBER(38,0), 
  GRADE NUMBER(38,0))

There is AVGGRADE Table:

CREATE TABLE AVGGRADE 
( ID NUMBER(38,0), 
  STUDENTID NUMBER(38,0), 
  AVG NUMBER(38,0))

I am going to calculate the average of grades in Grade table then insert them into AVGGRADE table by function. But I do not know how. I am appreciate you to help me.

CodePudding user response：

I guess that's for educational purposes; otherwise, a view would do (as sticky bit commented). Here's my suggestion:

create a sequence (which will be used to populate avggrade.id column)
create a function which will calculate average grade for a student
create a procedure which will call the function and insert result it returns into the target table

Something like this:

SQL> create sequence seqgrade;

Sequence created.

SQL> create or replace function f_avg_grade (par_studentid in grade.studentid%type)
  2    return number
  3  is
  4    l_avg number;
  5  begin
  6    select round(avg(g.grade), 2)
  7    into l_avg
  8    from grade g
  9    where g.studentid = par_studentid;
 10
 11    return l_avg;
 12  end f_avg_grade;
 13  /

Function created.

SQL> create or replace procedure p_avg_grade (par_studentid in grade.studentid%type)
  2  is
  3  begin
  4    insert into avggrade (id, studentid, avg_grade)
  5      values (seqgrade.nextval, par_studentid, f_avg_grade(par_studentid));
  6  end;
  7  /

Procedure created.

Testing:

SQL> select * From grade order by studentid;

        ID  SUBJECTID  STUDENTID      GRADE
---------- ---------- ---------- ----------
         1        100       1000          2
         2        101       1000          3
         3        102       1000          5
         4         10       2000          3

SQL> begin
  2    p_avg_grade(1000);
  3  end p_avg_grade;
  4  /

PL/SQL procedure successfully completed.

SQL> select * from avggrade;

        ID  STUDENTID  AVG_GRADE
---------- ---------- ----------
         1       1000       3,33

SQL>

CodePudding user response：

if you are creating a new table just to select average grade its not recommended to do in such way, it may create issues later on when you want to change data and will be difficult to mange it.

In case, your data is so high may be you need to de de-normalize your table then you can proceed with it. I think in your case it will be very rare to de-normalize table because data may not reach so much

i think simple query could do the job

select
    studentid
    avg(grade) as avg_grade
from
    GRADE 
group by  studentid