oracle - PLSQL LS-00382:表达式类型错误
问题描述
尝试运行此过程时出现此错误。我想使用嵌套表为每个学生添加每个学期和每年的平均笔记。
谁能帮我弄清楚?
Create or Replace type lista_medii is table of number;
Alter Table studenti Add medii lista_medii nested table medii store as value;
Create or replace procedure populare_medii
as
lista_tmp lista_medii := lista_medii();
begin
For v_student in (Select *from studenti) LOOP
For an in 1 .. v_student.AN LOOP
For medii_student in (select avg(valoare)from note join CURSURI on note.ID_CURS=CURSURI.ID where CURSURI.AN=1 and note.ID_STUDENT=29 group by cursuri.SEMESTRU) LOOP
lista_tmp.extend();
lista_tmp(lista_tmp.COUNT) := medii_student;
end loop;
update STUDENTI set STUDENTI.medii=lista_tmp where studenti.ID=v_student.id;
lista_tmp.delete();
end loop;
end loop;
end populare_medii;
解决方案
它必须是这样的:
For medii_student in (select avg(valoare) as avg_valoare from note join CURSURI on note.ID_CURS=CURSURI.ID where CURSURI.AN=1 and note.ID_STUDENT=29 group by cursuri.SEMESTRU) LOOP
lista_tmp.extend();
lista_tmp(lista_tmp.COUNT) := medii_student.avg_valoare ;
end loop;
或更好:
select avg(valoare)
BULK COLLECT INTO lista_tmp
from note
join CURSURI on note.ID_CURS=CURSURI.ID
where CURSURI.AN=1
and note.ID_STUDENT=29
group by cursuri.SEMESTRU
甚至更好:
update STUDENTI set STUDENTI.medii =
(select avg(valoare)
from note
join CURSURI on note.ID_CURS=CURSURI.ID
where CURSURI.AN=1
and note.ID_STUDENT=29
group by cursuri.SEMESTRU)
where studenti.ID=v_student.id;
或者更好的是在一个单独的 UPDATE 语句中执行它 - 自己尝试!
推荐阅读
- c# - 如何将来自api的流保存为文件?
- excel - 将 Outlook 电子邮件项目和 Excel 工作簿附加到我的电子邮件中
- angular - 拦截器没有拦截,尽管只有一个 HttpClientModule
- vb.net - SSRS Visual Basic 代码仅适用于报告服务
- go - 从单独的包函数返回错误的结构
- ruby-on-rails - benchmark-rspec 在没有任何通知的情况下被卡住
- mysql - 从相同来源导入 SQL 转储时出错
- oracle-apex - 仅当页面成功提交时才执行动态操作?
- python - 如何使用 pandas df.plot.scatter 制作带有子图的图形
- python - 无法在 Jupiter notebook 上使用 python x pandas 检测基本条形图中的错误