题目要求:现在存在一个录入新生的表(student),里面只有学生姓名是完整的,学生编号(stuid)通过输入的姓名来递增产生,比如,表(见下)中的Jack没有stuid,这时输入Jack,程序就根据表中stuid字段的最大值+1生成Jack的编号。这是我的student表的内容:
STUID STUNAME STUBIRTHDATE STUENGSCORE STUMATHSCORE STUADDRE CLASSID TEACHERID
----- -------- ------------ ----------- ------------ -------- ------- ----------
1001 Tom 01-1月-85 80 80 Yantai 001 01
1002 Peter 03-12月-86 58 70 Yantai 002 01
1003 John 07-5月-86 70 65 Qingdao 002 02
1004 Pauls 04-11月-87 90 52 JiNan 003 03
Jack 70 95 Qingdao 002 03
1006 Marry 03-11月-84 56 49 JiNan 002 01
1009 Mobby 08-12月-87 75 70 Yantai 004 02
1008 Bober 04-12月-86 80 85 Yantai 004 01
Jacky 01-1月-85 70 80 Jinan 001 01我编写的过程:
create or replace procedure NO_new_stu(stu_name in student.stuname%type,stu_id out student.stuid%type)
as
num char(4);
begin
select max(stuid) into num from student;
if substr(num,3,2) between '01' and '09' then
stu_id:='1000'+substr(num,4,1)+1;
else
stu_id:='1000'+substr(num,3,2)+1;
end if;
update student set stuid=stu_id where stu_name=stuname;
end;
但是,我的这个过程有个问题,就是如果接连输入同一个姓名的话,那这个姓名的stuid就不断的增加,而不是增加新的记录,我现在想加上一段异常,使得如果表中已有这个名字,并且stuid不为空,那么stuid就不+1,而是抛出一个异常,告知此学生名已存在。但是我加完后出现错误:
create or replace procedure NO_new_stu(stu_name in student.stuname%type,stu_id out student.stuid%type)
as
num char(4);
begin
select max(stuid) into num from student;
if student.stuid is not null then
if substr(num,3,2) between '01' and '09' then
stu_id:='1000'+substr(num,4,1)+1;
else
stu_id:='1000'+substr(num,3,2)+1;
end if;
else
exception
when others then
dbms_output.put_line('The student is existed!');
end if;
update student set stuid=stu_id where stu_name=stuname;
end;
错误如下:
PROCEDURE NO_NEW_STU 出现错误:LINE/COL ERROR
-------- -----------------------------------------------------------
13/1 PLS-00103: 出现符号 "EXCEPTION"在需要下列之一时:
begin case declare
exit for goto if loop mod null pragma raise return select
update while with
<<
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge
pipe16/5 PLS-00103: 出现符号 "IF"在需要下列之一时:
;
delete exists prior
谁知道是什么问题吗,应该怎么写呢?谢谢了!!
STUID STUNAME STUBIRTHDATE STUENGSCORE STUMATHSCORE STUADDRE CLASSID TEACHERID
----- -------- ------------ ----------- ------------ -------- ------- ----------
1001 Tom 01-1月-85 80 80 Yantai 001 01
1002 Peter 03-12月-86 58 70 Yantai 002 01
1003 John 07-5月-86 70 65 Qingdao 002 02
1004 Pauls 04-11月-87 90 52 JiNan 003 03
Jack 70 95 Qingdao 002 03
1006 Marry 03-11月-84 56 49 JiNan 002 01
1009 Mobby 08-12月-87 75 70 Yantai 004 02
1008 Bober 04-12月-86 80 85 Yantai 004 01
Jacky 01-1月-85 70 80 Jinan 001 01我编写的过程:
create or replace procedure NO_new_stu(stu_name in student.stuname%type,stu_id out student.stuid%type)
as
num char(4);
begin
select max(stuid) into num from student;
if substr(num,3,2) between '01' and '09' then
stu_id:='1000'+substr(num,4,1)+1;
else
stu_id:='1000'+substr(num,3,2)+1;
end if;
update student set stuid=stu_id where stu_name=stuname;
end;
但是,我的这个过程有个问题,就是如果接连输入同一个姓名的话,那这个姓名的stuid就不断的增加,而不是增加新的记录,我现在想加上一段异常,使得如果表中已有这个名字,并且stuid不为空,那么stuid就不+1,而是抛出一个异常,告知此学生名已存在。但是我加完后出现错误:
create or replace procedure NO_new_stu(stu_name in student.stuname%type,stu_id out student.stuid%type)
as
num char(4);
begin
select max(stuid) into num from student;
if student.stuid is not null then
if substr(num,3,2) between '01' and '09' then
stu_id:='1000'+substr(num,4,1)+1;
else
stu_id:='1000'+substr(num,3,2)+1;
end if;
else
exception
when others then
dbms_output.put_line('The student is existed!');
end if;
update student set stuid=stu_id where stu_name=stuname;
end;
错误如下:
PROCEDURE NO_NEW_STU 出现错误:LINE/COL ERROR
-------- -----------------------------------------------------------
13/1 PLS-00103: 出现符号 "EXCEPTION"在需要下列之一时:
begin case declare
exit for goto if loop mod null pragma raise return select
update while with
<<
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge
pipe16/5 PLS-00103: 出现符号 "IF"在需要下列之一时:
;
delete exists prior
谁知道是什么问题吗,应该怎么写呢?谢谢了!!
解决方案 »
- 求高手帮我看看怎么解决一个小问题!
- oracle OracleOraHome90TNSListener 启动了有停止啦
- 新手问题
- ora-20015;ora-20010;ora-00600
- 如果select语句中使用sysdate取系统时间,请问是select取数据前的时间还是取到数据后的时间?
- 为什么不让我建立存储过程~!!~~~oracle新手问题
- 请教:在存储过程中可否查询或修改其它DB中的数据
- ORA-12560:TNS:协议适配器错误
- 请教一个复杂的SQL语句
- 資料庫數據的導出
- *走过路过进来坐坐呀* 如何实现oracle数据库和sqlserver数据库远程同步?
- 高手指点OMS登录问题!有贴图!
as
num char(4);
begin
select max(stuid) into num from student;
if student.stuid is not null then
if substr(num,3,2) between '01' and '09' then
stu_id:='1000'+substr(num,4,1)+1;
else
stu_id:='1000'+substr(num,3,2)+1;
end if;
else
RAISE_APPLICATION_ERROR(-20001,'The student is existed!');
end if;
exception
when others then dbms_output.put_line(SQLERRM);
update student set stuid=stu_id where stu_name=stuname;
end;
/
不能吧exception语句块包含在if end if 之间。
create or replace procedure NO_new_stu(stu_name in student.stuname%type,stu_id out student.stuid%type)
as
num char(4);
begin
select max(stuid) into num from student;
if student.stuid is not null then
if substr(num,3,2) between '01' and '09' then
stu_id:='1000'+substr(num,4,1)+1;
else
stu_id:='1000'+substr(num,3,2)+1;
end if;
else
RAISE_APPLICATION_ERROR(-20001,'The student is existed!');
end if;
exception
--如果你需要在内部作一场处理应该使用匿名块
when others then dbms_output.put_line(SQLERRM);
update student set stuid=stu_id where stu_name=stuname;
commit;--提交数据
end NO_new_stu;