存储过程将成绩转换为等级

将指定课程号的学生选课成绩从百分制改为等级制（即分数最高的前10%(包括10%)是1, 分数在前10%30%(不包括10%)是2，分数在前30%50%(不包括30%)是3，分数在前50%~90%(不包括50%)是4，剩下的是5）
create or replace procedure proc_chglev(p_cno int)
as $$
declare
curs1 CURSOR(v_cname varchar) FOR
SELECT sc.sno, sc.cno, sc.grade,dense_rank() over (order by sc.grade desc) as rank
FROM course, sc
where course.cno = sc.cno and course.cname = v_cname
order by sc.grade desc;
v_cnt int;
v_num int;
p_cno int;
v_sno int;
v_cno int;
v_grade int;
v_rank int;
v_lastlevel int;
stack text;
begin
select cno into p_cno from course where cname=p_cname;
select count(sno) into v_cnt from sc where cno=p_cno;//选修该课程的总人数
v_num := 0;
OPEN curs1(p_cname);
LOOP
FETCH curs1 INTO v_sno, v_cno, v_grade,v_rank;
EXIT WHEN NOT FOUND;
v_num := v_num + 1;
IF (v_rank/v_cnt<=0.1) THEN
v_lastlevel=1;
ELSIF (v_rank/v_cnt<=0.3) THEN
v_lastlevel=2;
ELSIF (v_rank/v_cnt<=0.5) THEN
v_lastlevel=3;
ELSIF (v_rank/v_cnt<=0.9) THEN
v_lastlevel=4;
ELSE
v_lastlevel=5;
END IF;
UPDATE sc SET grade = v_lastlevel WHERE CURRENT OF curs1;
END LOOP;
EXCEPTION WHEN OTHERS THEN
CLOSE curs1;
GET STACKED DIAGNOSTICS stack = PG_CONTEXT;
RAISE NOTICE '--- Call Stack ---\n%', stack;
end;
$$ language plpgsql;

1、你的存储过程中的 p_cname 没有任何来源，BEGIN后的第一句就报错产生异常，而异常中的第一句为关闭游标，而此时的游标 curs1 还未打开，因此才会报游标不存在错误
2、我猜你的存储过程的传入参数应该是p_cname，通过他来获取课程编码，再统计课程选修总人数……
3、实际这个功能不需要存储过程，一条SQL即可实现：（这里假设 sc.sno 为 sc 表的主键，如果不是，最后更新中的WHERE条件及前面的提取字段sno修改为主键字段即可）

    WITH course AS ( SELECT '1' cno, '历史' cname UNION ALL SELECT '2' cno, '美术' cname )
        , sc AS (
            SELECT 1 sno, '1' cno, 30 grade UNION ALL SELECT 2 sno, '1' cno, 110 grade UNION ALL SELECT 3 sno, '1' cno, 90 grade UNION ALL
            SELECT 4 sno, '1' cno, 130 grade UNION ALL SELECT 5 sno, '1' cno, 60 grade UNION ALL SELECT 6 sno, '1' cno, 88 grade UNION ALL
            SELECT 7 sno, '1' cno, 20 grade UNION ALL SELECT 8 sno, '1' cno, 140 grade UNION ALL SELECT 9 sno, '1' cno, 75 grade UNION ALL
            SELECT 10 sno, '1' cno, 66 grade UNION ALL SELECT 11 sno, '1' cno, 100 grade UNION ALL SELECT 12 sno, '1' cno, 77 grade UNION ALL
            SELECT 13 sno, '2' cno, 30 grade UNION ALL SELECT 14 sno, '2' cno, 110 grade UNION ALL SELECT 15 sno, '2' cno, 90 grade )
        , t AS (
            SELECT sc.sno, sc.cno, sc.grade, cnt, DENSE_RANK( ) OVER( ORDER BY sc.grade DESC) / sc.cnt::dec AS lastlevel
              FROM course kc, LATERAL ( SELECT sno, cno, grade, count( 1 ) OVER( PARTITION BY cno ) cnt FROM sc WHERE sc.cno = kc.cno ) sc 
             WHERE kc.cno = sc.cno AND kc.cname = '历史' )
    SELECT t.*, ( CASE WHEN t.lastlevel <= 0.1 THEN 1 WHEN t.lastlevel <= 0.3 THEN 2 WHEN t.lastlevel <= 0.5 THEN 3 WHEN t.lastlevel <= 0.9 THEN 4 ELSE 5 END ) lvl FROM t; -- 预计输出验证
/*    UPDATE sc SET grade = ( CASE WHEN t.lastlevel <= 0.1 THEN 1 WHEN t.lastlevel <= 0.3 THEN 2 WHEN t.lastlevel <= 0.5 THEN 3 WHEN t.lastlevel <= 0.9 THEN 4 ELSE 5 END )
      FROM t WHERE sc.sno = t.sno; */

我没有建立表结构，因此将更新语句注释掉了，换成了 预计输出验证的SELECT语句