需要帮助提高功能相对于删除的效率

Question

create or replace function "dropCourse" (studentId integer, sectionId integer)
returns void
as
$$
declare studentGrade varchar;
BEGIN
studentGrade = (select grade from "StudentSelection" where "studentId" = studentId and "sectionId" = sectionId);
if (studentGrade is not null) then raise exception 'Can''t drop a course with grade';
else
delete from "StudentSelection" where "studentId" = studentId and "sectionId" = sectionId;
update "CourseSection" set "leftCapacity" = "leftCapacity" + 1 where "sectionId" = sectionId;
end if;
END
$$ LANGUAGE 'plpgsql';

这是我的原始代码。整个过程是让学生去掉一个没有分数的课程部分，并在课程部分的左侧容量上加上数字1。

where "studentId" = studentId and "sectionId" = sectionId

但上面的where语句实际上执行了两次，包括第一次查询学生成绩(找出是否为空(和第二次删除。

是否可以通过其他更有效的方式进行一次查询？

Answer 1

我是按照Patrick的帖子写这篇文章的。我们想出了几乎相同的解决方案。我强烈建议您丢失标识符的camel大小写。对于以后维护数据库的人来说，必须引用所有标识符，这是一场噩梦。使用所有小写或snake大小写和noooo空格。你稍后会感谢我的。

CREATE OR REPLACE FUNCTION dropcourse(studentID INTEGER, sectionID INTEGER)
RETURNS VOID
AS $$
DECLARE
studentGrade VARCHAR;
BEGIN
DELETE FROM "StudentSelection"
WHERE ss."studentId" = studentID
AND ss."sectionId" = sectionID
AND ss.grade IS NOT NULL
;
IF FOUND
THEN
UPDATE "CourseSection"
SET "leftCapacity" = "leftCapacity" + 1
WHERE "sectionId" = sectionID
;
ELSE
RAISE EXCEPTION 'Can''t drop a course with a grade';
END IF;
RETURN;
END;
$$ LANGUAGE PLPGSQL;

Answer 2

您可以简单地执行DELETE命令，然后检查发生了什么，这样您就不必首先确定是否存在记录：

CREATE FUNCTION dropCourse (studId integer, sectId integer) RETURNS void AS
$$
BEGIN
DELETE FROM "StudentSelection"
WHERE "studentId" = studId
AND "sectionId" = sectId
AND grade IS NULL;
IF NOT FOUND THEN -- No record was deleted so grade was probably NOT NULL
RAISE EXCEPTION 'Can''t drop a course with grade';
END IF;
-- If the function reaches here, update the capacity
UPDATE "CourseSection" SET "leftCapacity" = "leftCapacity" + 1
WHERE "sectionId" = sectId;
END;
$$ LANGUAGE 'plpgsql';

通常，您应该REALLY避免函数参数与表列名相同。