1, league tables (student number, course, scores)
2, the database table t_tongji include: student id, with an average grade two fields;
3, write a stored procedure, computing each student's grade point average, and inserted into the t_tongji table;

How to implement, the great god give the answer!!

CodePudding user response:

Select student number, the sum (grades)/count (1) the average result from result table group by student number

CodePudding user response:

The CREATE OR REPLACE PROCEDURE insert_t_tongji IS
The BEGIN
FOR I IN the SELECT student id, AVG (results) the average result FROM result table GROUP BY student id LOOP
INSERT INTO t_tongji VALUES (i. student id, i. grade point average).
END LOOP;
END;

CodePudding user response:

Write the wrong
FOR I IN (SELECT student id, AVG (results) the average result FROM result table GROUP BY student id) LOOP

Add parentheses

CodePudding user response:

The create or replace procedure P
As
The begin
Insert into t_tongji select student id, avg (grades) from league tables group by student number;
End P;

CodePudding user response:

The create table grade (
Stuid varchar2 (20),
The class varchar2 (20),
Grade number (4, 2)
);
- create a TAB
The create table t_tongji (
Stuid varchar2 (20),
Grade_avg number (4, 2)
);
- insert test data
The begin
For varA in 1.. 10
Loop
For varB in 21.. 26
Loop
Insert into grade values (varA, varB | | 'class' dbms_random. Value * 100);
End loop;
End loop;
The end;

- stored procedure
The create or replace procedure grade_tongji as
The begin
For varA in (select stuid, avg (grade) grade_avg from grade group by stuid)
Loop
Insert into t_tongji values (varA. Stuid, varA. Grade_avg);
End loop;
The end;

- the above steps after test no problem, I can try