题目是:编写存储过程可实现录入新生并且学生所在班级的人数增加1。
我建的两个表:
student:
名称 是否为空? 类型
----------------------------------------- -------- --------------
STUID CHAR(4)
STUNAME CHAR(30)
STUBIRTHDATE CHAR(30)
STUENGSCORE NUMBER(2)
STUMATHSCORE NUMBER(2)
STUADDRESS CHAR(30)
CLASSID CHAR(3)
TEACHERID CHAR(2)class:
名称 是否为空? 类型
----------------------------------------- -------- -----------
CLASSID CHAR(3)
CLASSNAME CHAR(30)
QTY NUMBER(2)//这个字段是班级人数。我编写的过程如下:
SQL> create or replace procedure new_student
2 (stu_id in student.stuid%type,stu_name in student.stuname%type,
3 stu_birthdate in student.stubirthdate%type,stu_engscore in student.stuengscore%type,
4 stu_mathscore in student.stumathscore%type,stu_address in student.stuaddress%type,
5 stu_classid in student.classid%type,stu_teacherid in student.teacherid%type,
6 class_id in class.classid%type)
7 as
8 class_qty class.qty%type;
9 begin
10 select qty into class_qty from class;
11 select * into
12 stu_id,stu_name,stu_birthdate,stu_engscore,stu_mathscore,
13 stu_address,stu_classid,stu_teacherid from student where stu_id=stuid,stu_name=stuname,stu_birt
hdate=stubirthdate,
14 stu_engscore=stuengscore,stu_mathscore=stumathscore,stu_address=stuaddress,
15 stu_classid=classid,stu_teacherid=teacherid;
16 update class set qty=qty+1 where class_id=classid;
17 class_qty:=class.qty;
18 dbms_output.put_line('学号:'||stu_id);
19 dbms_output.put_line('学生姓名:'||stu_name);
20 dbms_output.put_line('学生年龄:'||stu_birthdate);
21 dbms_output.put_line('英语成绩:'||stu_engscore);
22 dbms_output.put_line('数学成绩:'||stu_mathscore);
23 dbms_output.put_line('家庭住址:'||stu_address);
24 dbms_output.put_line('学生班级:'||stu_classid);
25 dbms_output.put_line('指导教师:'||stu_teacherid);
26 dbms_output.put_line('班级人数:'||class_qty);
27 end;
28 /警告: 创建的过程带有编译错误。SQL> show err
PROCEDURE NEW_STUDENT 出现错误:LINE/COL ERROR
-------- ------------------------------------------------
11/1 PL/SQL: SQL Statement ignored
13/70 PL/SQL: ORA-00933: SQL 命令未正确结束
哪位好心人帮忙看一下是哪里错了吧,谢谢了!!
我建的两个表:
student:
名称 是否为空? 类型
----------------------------------------- -------- --------------
STUID CHAR(4)
STUNAME CHAR(30)
STUBIRTHDATE CHAR(30)
STUENGSCORE NUMBER(2)
STUMATHSCORE NUMBER(2)
STUADDRESS CHAR(30)
CLASSID CHAR(3)
TEACHERID CHAR(2)class:
名称 是否为空? 类型
----------------------------------------- -------- -----------
CLASSID CHAR(3)
CLASSNAME CHAR(30)
QTY NUMBER(2)//这个字段是班级人数。我编写的过程如下:
SQL> create or replace procedure new_student
2 (stu_id in student.stuid%type,stu_name in student.stuname%type,
3 stu_birthdate in student.stubirthdate%type,stu_engscore in student.stuengscore%type,
4 stu_mathscore in student.stumathscore%type,stu_address in student.stuaddress%type,
5 stu_classid in student.classid%type,stu_teacherid in student.teacherid%type,
6 class_id in class.classid%type)
7 as
8 class_qty class.qty%type;
9 begin
10 select qty into class_qty from class;
11 select * into
12 stu_id,stu_name,stu_birthdate,stu_engscore,stu_mathscore,
13 stu_address,stu_classid,stu_teacherid from student where stu_id=stuid,stu_name=stuname,stu_birt
hdate=stubirthdate,
14 stu_engscore=stuengscore,stu_mathscore=stumathscore,stu_address=stuaddress,
15 stu_classid=classid,stu_teacherid=teacherid;
16 update class set qty=qty+1 where class_id=classid;
17 class_qty:=class.qty;
18 dbms_output.put_line('学号:'||stu_id);
19 dbms_output.put_line('学生姓名:'||stu_name);
20 dbms_output.put_line('学生年龄:'||stu_birthdate);
21 dbms_output.put_line('英语成绩:'||stu_engscore);
22 dbms_output.put_line('数学成绩:'||stu_mathscore);
23 dbms_output.put_line('家庭住址:'||stu_address);
24 dbms_output.put_line('学生班级:'||stu_classid);
25 dbms_output.put_line('指导教师:'||stu_teacherid);
26 dbms_output.put_line('班级人数:'||class_qty);
27 end;
28 /警告: 创建的过程带有编译错误。SQL> show err
PROCEDURE NEW_STUDENT 出现错误:LINE/COL ERROR
-------- ------------------------------------------------
11/1 PL/SQL: SQL Statement ignored
13/70 PL/SQL: ORA-00933: SQL 命令未正确结束
哪位好心人帮忙看一下是哪里错了吧,谢谢了!!
from student where stu_id=stuid,stu_name=stuname,stu_birt
hdate=stubirthdate,得写成
where stu_id=stuid and stu_name=stuname
并且返回的记录数必须是1
等等
我都迷糊了,我发完帖子后又改了一下,这次没有出现错误,但不知道我做的符不符合题目的要求,大家帮忙看一下吧:
SQL> create or replace procedure new_student
2 (stu_id in student.stuid%type,stu_name in student.stuname%type,
3 stu_birthdate in student.stubirthdate%type,stu_engscore in student.stuengscore%type,
4 stu_mathscore in student.stumathscore%type,stu_address in student.stuaddress%type,
5 stu_classid in student.classid%type,stu_teacherid in student.teacherid%type,
6 class_id in class.classid%type)
7 as
8 class_qty class.qty%type;
9 begin
10 insert into student values(stu_id,stu_name,stu_birthdate,stu_engscore,
11 stu_mathscore,stu_address,stu_classid,stu_teacherid);
12 update class set qty=qty+1 where class_id=classid;
13 select qty into class_qty from class,student where
14 class.classid=student.classid and
15 stuid=stu_id and stuname=stu_name and stubirthdate=stu_birthdate and
16 stuengscore=stu_engscore and stumathscore=stu_mathscore
17 and stuaddress=stu_address and
18 student.classid=stu_classid and teacherid=stu_teacherid;
19 dbms_output.put_line('学号:'||stu_id);
20 dbms_output.put_line('学生姓名:'||stu_name);
21 dbms_output.put_line('学生年龄:'||stu_birthdate);
22 dbms_output.put_line('英语成绩:'||stu_engscore);
23 dbms_output.put_line('数学成绩:'||stu_mathscore);
24 dbms_output.put_line('家庭住址:'||stu_address);
25 dbms_output.put_line('学生班级:'||stu_classid);
26 dbms_output.put_line('指导教师:'||stu_teacherid);
27 dbms_output.put_line('班级人数:'||class_qty);
28 end;
29 /过程已创建。SQL> execute new_student('1009','Jacky','01-1月-85',70,80,'Jinan','001','01','001');
学号:1009
学生姓名:Jacky
学生年龄:01-1月-85
英语成绩:70
数学成绩:80
家庭住址:Jinan
学生班级:001
指导教师:01
班级人数:21PL/SQL 过程已成功完成。谢谢各位!!