初学－求一段存储过程

Question

班级表
class_no   char(3)       not null primary key,                --班级号
class_name varchar2(20)  not null                             --班级名学生表
stu_no   char(7)      not null primary key,                   --学号
stu_name varchar2(8)  not null,                               --学生姓名
sex      char(2)      not null check(sex in('男','女')),      --性别
class_no char(3)      not null,                               --班级号课程表
lesson_no   char(3)      not null primary key,                --课程号
lesson_name varchar2(20) not null,                            --课程名
stu_credit    numeric(2,1) not null                           --学分成绩表
stu_no     char(7)      not null,                             --学号
lesson_no  char(3)      not null,                             --课程号
       numeric(4,2) not null,                             --成绩创建存储过程完成的功能：统计某个学生的平均分和总学分（成绩大于60分可获得该门课程的学分）本来有在sql下的代码，现在要换要oracle下实现就有很多错了，不知道怎么改了，求大侠帮忙！

Answer 1

你把sql下的代码贴下，大家改

Answer 2

select b.stu_no,avg(b.) avg_,sum(
case
when b.>=60 then a.stu_credit
else 0
end) tot_credit
from 课程表 a,成绩表 b
where a.lesson_no=b.lesson_no
group by b.stu_no一句SQL就出来，还需要什么存储过程啊

Answer 3

C'est la Vie
        －－－－－－－－人创造了"存储过程"就有它存在的道理～～～～～～～～

Answer 4

下面是我写的，有错。大家帮忙改下吧。谢谢。create or replace procedure pro_stu_mk(sno char(7)) is  cursor stat is
       select d.stu_name,d.lesson_name,d.,d.stu_credit
  from(select a.stu_no,a.stu_name,lesson_name,c.,b.stu_credit from student a,lesson b, c
          where a.stu_no=c.stu_no and b.lesson_no=c.lesson_no) d
       where d.stu_no=sno;
    sname student.stu_name%type;
    lname lesson.lesson%type;
    stu_ .%type;
    stu_credit lesson.stu_credit%type;
    sum_ .%type;
    lcount .%type;
    lavg .%type;
    sum_credit lesson.stu_credit%type;
  begin
    open stat;
    sum_:=0;
    lcount:=0;
    lavg:=0;
    sum_credit:=0;
    loop
      fetch stat into sname,lname,stu_,stu_credit;
      exit when stat %notfound;
      if stu_>59 then
        bengin
  sum_credit:=sum_credit+stu_credit;
          sum_:=sum_+stu_;
          lcount:=lcount+1;
        end
      end if
      lavg:=sum_/lcount;
      dbms_output.put_line('学生:'||sname||' 平均成绩:'||lavg||' 所得学分:'||sum_credit);
    end loop
  end
end pro_stu_mk

Answer 5

你的存储过程最后是输出一条纪录对吧，SNO也是唯一的对吧，所以里面其实可以不需要游标的

Answer 6

你这里面的错误地方,用红色标出来
create or replace procedure pro_stu_mk(sno char(7)) is   cursor stat is 
       select d.stu_name,d.lesson_name,d.,d.stu_credit 
  from(select a.stu_no,a.stu_name,lesson_name,c.,b.stu_credit from student a,lesson b, c 
          where a.stu_no=c.stu_no and b.lesson_no=c.lesson_no) d 
       where d.stu_no=sno; 
    sname student.stu_name%type; 
    lname lesson.lesson%type; 
    stu_ .%type; 
    stu_credit lesson.stu_credit%type; 
    sum_ .%type; 
    lcount .%type; 
    lavg .%type; 
    sum_credit lesson.stu_credit%type; 
  begin 
    open stat; 
    sum_:=0; 
    lcount:=0; 
    lavg:=0; 
    sum_credit:=0; 
    loop 
      fetch stat into sname,lname,stu_,stu_credit; 
      exit when stat %notfound; 
      if stu_>59 then 
        bengin  应该是begin
  sum_credit:=sum_credit+stu_credit; 
          sum_:=sum_+stu_; 
          lcount:=lcount+1; 
        end 少;
      end if 少;
      lavg:=sum_/lcount; 
      dbms_output.put_line('学生:' ¦ ¦，两上|之间是不是有空格sname ¦ ¦' 平均成绩:' ¦ ¦lavg ¦ ¦' 所得学分:' ¦ ¦sum_credit); 
    end loop 少;
  end 少;
end pro_stu_mk

Answer 7

SNO是唯一的话，可以这样写CREATE OR REPLACE PROCEDURE pro_stu_mk (sno CHAR)
IS
   sname        student.stu_name%TYPE;
   lavg         .%TYPE;
   sum_credit   lesson.stu_credit%TYPE;
BEGIN
   sum_ := 0;
   lavg := 0;
   sum_credit := 0;   SELECT a.str_name, AVG (CASE
                              WHEN c. >= 60
                                 THEN c.
                              ELSE NULL
                           END),
          SUM (CASE
                  WHEN c. >= 60
                     THEN b.stu_credit
                  ELSE 0
               END)
     INTO snmae, lavg,
          sum_credit
     FROM student a, lesson b,  c
    WHERE a.stu_no = c.stu_no AND b.lesson_no = c.lesson_no AND a.stu_no = sno;   DBMS_OUTPUT.put_line (   '学生:'
                         || sname
                         || ' 平均成绩:'
                         || lavg
                         || ' 所得学分:'
                         || sum_credit
                        );
END pro_stu_mk;

Answer 8

你的存储过程里少分号的地方好几个，最后那个END也少
还有传进来的sno
不要写char(7)
直接写 sno charcreate or replace procedure pro_stu_mk(sno char (7)不要  ) is    cursor stat is  
       select d.stu_name,d.lesson_name,d.,d.stu_credit  
  from(select a.stu_no,a.stu_name,lesson_name,c.,b.stu_credit from student a,lesson b, c  
          where a.stu_no=c.stu_no and b.lesson_no=c.lesson_no) d  
       where d.stu_no=sno;  
    sname student.stu_name%type;  
    lname lesson.lesson%type;  
    stu_ .%type;  
    stu_credit lesson.stu_credit%type;  
    sum_ .%type;  
    lcount .%type;  
    lavg .%type;  
    sum_credit lesson.stu_credit%type;  
  begin  
    open stat;  
    sum_:=0;  
    lcount:=0;  
    lavg:=0;  
    sum_credit:=0;  
    loop  
      fetch stat into sname,lname,stu_,stu_credit;  
      exit when stat %notfound;  
      if stu_>59 then  
        bengin  应该是begin  
  sum_credit:=sum_credit+stu_credit;  
          sum_:=sum_+stu_;  
          lcount:=lcount+1;  
        end 少; 
      end if 少; 
      lavg:=sum_/lcount;  
      dbms_output.put_line('学生:' ¦ ¦  两个 ¦之间是不是有空格sname ¦ ¦' 平均成绩:' ¦ ¦lavg ¦ ¦' 所得学分:' ¦ ¦sum_credit);  
    end loop  少;  
  end  少; 少; 
end pro_stu_mk 少; 

Answer 9

忘记加GROUP BY 了CREATE OR REPLACE PROCEDURE pro_stu_mk (sno CHAR)
IS
   sname        student.stu_name%TYPE;
   lavg         .%TYPE;
   sum_credit   lesson.stu_credit%TYPE;
BEGIN
   sum_ := 0;
   lavg := 0;
   sum_credit := 0;   SELECT a.str_name, AVG (CASE
                              WHEN c. >= 60
                                 THEN c.
                              ELSE NULL
                           END),
          SUM (CASE
                  WHEN c. >= 60
                     THEN b.stu_credit
                  ELSE 0
               END)
     INTO snmae, lavg,
          sum_credit
     FROM student a, lesson b,  c
    WHERE a.stu_no = c.stu_no AND b.lesson_no = c.lesson_no AND a.stu_no = sno
          GROUP BY a.stu_no,a.stu_name;   DBMS_OUTPUT.put_line (   '学生:'
                         || sname
                         || ' 平均成绩:'
                         || lavg
                         || ' 所得学分:'
                         || sum_credit
                        );
END pro_stu_mk;

Answer 10

C'est la Vie 
        －－－－－－－－你的select语句里中的平均分和总分好像有点问题啊？都没有统计课程数，怎么算平均啊？
         －－－－－－－－你对我程序里的修改也还有问题……不管怎么说，谢谢你了。

Answer 11

不用统计课程数啊
 >=60分的就输出成绩，否则就输出null
最后再用AVG啊
avg函数本身就等于总分除以课程数的啊（NULL的不算的啊）
你可以试试看看啊
没必要去算总成绩再除以课程数（算出来是一样的）
总分的话，你求的是学分总分啊
我这里用CASE
如果成绩>=60，就输出学分，否则输出0，最后加起来就合格的学分啊

Answer 12

你可以两种存储过程都去试试
一般来说能不用游标的地方就别用游标
尤其是数据量大的情况下
数据量小差别不大的，数据量大的话，差别很大的
况且你用了游标存储过程就显得复杂了
一看到算平均，一般都是用AVG的，很少有人会去SUM后再除以数量的
除非你的平均数算法是特殊的

Answer 13

C'est la Vie  
        －－－－－－－－这样啊!知道了。最后还有个问题就是我在执行的时候出错了，你能再帮我看看吗？谢谢。
执行：SQL> exec pro_stu_no('0500101')
 
报错： begin pro_stu_no('0500101'); end;
 
     ORA-06550: 第 2 行, 第 7 列: 
     PLS-00306: 调用 'PRO_STU_NO' 时参数个数或类型错误
      ORA-06550: 第 2 行, 第 7 列: 
     PL/SQL: Statement ignored

Answer 14

上面的错了，是执行这个的情况
SQL> exec pro_stu_mk('0500101')
 
begin pro_stu_mk('0500101'); end;
 
ORA-06502: PL/SQL: 数字或值错误 :  数值精度太高
ORA-06512: 在 "X0051398.PRO_STU_MK", line 12
ORA-06512: 在 line 2
 

Answer 15

估计可能是求平均的问题
不知道你是用你原来写的还是我改的
如果是原来写的
lavg:=sum_/lcount
改成lavg:=round()sum_/lcount,2)
如果是我改的SELECT a.str_name, AVG (CASE
                              WHEN c. >= 60
                                 THEN c.
                              ELSE NULL
                           END),
          SUM (CASE
                  WHEN c. >= 60
                     THEN b.stu_credit
                  ELSE 0
               END)
     INTO snmae, lavg,
          sum_credit
     FROM student a, lesson b,  c
    WHERE a.stu_no = c.stu_no AND b.lesson_no = c.lesson_no AND a.stu_no = sno
          GROUP BY a.stu_no,a.stu_name;
改成
SELECT a.str_name, round(AVG (CASE
                              WHEN c. >= 60
                                 THEN c.
                              ELSE NULL
                           END),2),
          SUM (CASE
                  WHEN c. >= 60
                     THEN b.stu_credit
                  ELSE 0
               END)
     INTO snmae, lavg,
          sum_credit
     FROM student a, lesson b,  c
    WHERE a.stu_no = c.stu_no AND b.lesson_no = c.lesson_no AND a.stu_no = sno
          GROUP BY a.stu_no,a.stu_name;

Answer 16

我是用你的，然后学号相关的改成了varchar2型了。
然后执行exec pro_stu_mk('0500101');
依然报错ORA-06502: PL/SQL: 数字或值错误 :  数值精度太高
        ORA-06512: 在 "X0051398.PRO_STU_MK", line 11
       ORA-06512: 在 line 2CREATE OR REPLACE PROCEDURE pro_stu_mk (sno varchar2)
IS
   sname        student.stu_name%TYPE;
   lavg         .%TYPE;
   sum_credit   lesson.stu_credit%TYPE;
   sum_     .%type;
BEGIN
   sum_ := 0;
   lavg := 0;
   sum_credit := 0;
SELECT a.stu_name, round(AVG (CASE 
                              WHEN c. >= 60 
                                 THEN c. 
                              ELSE NULL 
                           END),2), 
          SUM (CASE 
                  WHEN c. >= 60 
                     THEN b.stu_credit 
                  ELSE 0 
               END) 
    INTO sname,lavg,sum_credit 
    FROM student a, lesson b,  c 
    WHERE a.stu_no = c.stu_no AND b.lesson_no = c.lesson_no AND a.stu_no = sno 
          GROUP BY a.stu_no,a.stu_name; 
   DBMS_OUTPUT.put_line (   '学生:'
                         || sname
                         || ' 平均成绩:'
                         || lavg
                         || ' 所得学分:'
                         || sum_credit
                        );
END pro_stu_mk;

Answer 17

CREATE OR REPLACE PROCEDURE pro_stu_mk (sno CHAR)
IS
   sname        student.stu_name%TYPE;
   lavg        number ;--.%TYPE;
   sum_credit   number ;-- lesson.stu_credit%TYPE;
BEGIN
   sum_ := 0;
   lavg := 0;
   sum_credit := 0;   SELECT a.str_name, AVG (CASE
                              WHEN c. >= 60
                                 THEN c.
                              ELSE NULL
                           END),
          SUM (CASE
                  WHEN c. >= 60
                     THEN b.stu_credit
                  ELSE 0
               END)
     INTO snmae, lavg,
          sum_credit
     FROM student a, lesson b,  c
    WHERE a.stu_no = c.stu_no AND b.lesson_no = c.lesson_no AND a.stu_no = sno
          GROUP BY a.stu_no,a.stu_name;   DBMS_OUTPUT.put_line (   '学生:'
                         || sname
                         || ' 平均成绩:'
                         || lavg
                         || ' 所得学分:'
                         || sum_credit
                        );
END pro_stu_mk; 两个变量改成number就可以跑通了，我再研究下怎么回事

Answer 18

看了下，主要是因为
stu_credit    numeric(2,1) not null                           
的精度问题，只有一位整数
一SUM只要>9就超出精度了
你再存储过程里可以写成number，或者精度提高点，就没问题了