我创建了以下函数,并希望根据“成绩积分”中使用的等级以平均成绩积分查询学生表中的姓名。有任何想法吗?我提供了一条选择语句,将下面的学生,班级和年级表加入其中。
DECLARE
FUNCTION GradePoint(GRADE varchar2)
return number
is
BEGIN
RETURN CASE grade
when 'A+' then 9
when 'A' then 8
when 'A-' then 7
when 'B+' then 6
when 'B' then 5
when 'B-' then 4
when 'C+' then 3
when 'C' then 2
when 'D'then 1
when 'F'then 0
else -1
END;
END;
SELECT student.student_id, student.first_name, student.last_name, avg(gradepoint(class.grade))
FROM CLASS
JOIN STUDENT
ON student.student_id = class.student_id
JOIN course
ON course.course_id = class.course_id
group by 1;
正如@SayanMalakshinov指出的那样,您可以内联函数定义,如果您希望仅在单个查询中使用函数,这非常有用,并且确实需要很多次。但是要使其普遍可用,您必须将其创建为存储过程(过程的泛型)。为此,您必须首先了解PLSQL的块结构。PLSQL过程有2个主要变体:匿名块和命名块。两者最多由3个部分保持一致:声明,执行和异常部分。对于匿名块,声明和异常部分是可选的。然后,整个块以END语句终止。
declare -- Declaration section
-- contains local definitions
begin -- Execution section
-- contains the code
exception -- Exception Section
when ... -- contains code to handle specific exceptions
when ... -- that occur within the execution section
end; -- Block terminates
命名块具有相同的结构,除了还需要声明部分,但它不是以DECLARE开头,而是以FUNCTION或PROCEDURE开头。最后,可以嵌套块:在内部定义一个块,再定义一个块。外部块中定义的任何内容在嵌套块中都可用。在某些限制下,命名块可以嵌套在匿名块中(在声明部分中)。就像匿名块中的任何其他定义一样,当块终止时,命名块的定义也超出了范围。
现在看一下您的代码:
declare
function GradePoint(GRADE varchar2)
...
end;
end;
最后就是您拥有的:一个命名块GradePoint嵌套在一个匿名块中。为了获得一般可用性,请使用编译器指令CREATE替换DECLARE。所以:
create or replace
function GradePoint(grade varchar2)
...
select ...
, case ...
end;
end GradePoint;
至于您的最终查询,我很困惑您想要的平均值。您的描述暗示了整体分数,但是您的初始查询隐含了每个学生的分数。两种方式的avg函数的窗口版本都将获得正确的值。尝试类似:
select distinct
s.student_id
, s.first_name
, s.last_name
, round(avg(GradePoint(cl.grade)) over (partition by s.student_id),2) gpa
, round(avg(GradePoint(cl.grade)) over (),2) overall_gpa
from student s
join class cl on cl.student_id = s.student_id
join course c on c.course_id = cl.course_id
order by s.student_id;
旁注:您应避免使用CamelCase名称。Oracle在内部将它们转换为大写(所有对象类型表,列,视图,过程、、、、所有命名对象。它进行的任何引用都使用大写的值。因此,GradePoint将始终显示为GRADEPOINT。在Oracle中最好使用Snake_Case约定,是的,它也将是大写字母,但仍易于阅读,这意味着更多的理解和更少的错误,尤其是当项目紧密命名时。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句