不一定,如 declare ...... begin begin insert into test(c1,c2) value(dd,ff); exception when others then ........... end; update ................. exception when others then ................ end ;
可是,为什么我下面的这个程序有错误: create or replace procedure NO_new_stu(stu_name in student.stuname%type, stu_id out student.stuid%type) as num char(4); s_id char(4); begin select max(stuid) into num from student; select stuid into s_id from student where stuname=stu_name; if s_id is 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); end; update student set stuid=stu_id where stu_name=stuname; end; /警告: 创建的过程带有编译错误。SQL> show err PROCEDURE NO_NEW_STU 出现错误:LINE/COL ERROR -------- -------------------------------- 20/1 PLS-00103: 出现符号 "UPDATE" 而且,如果我把update语句放到when others then dbms_output.put_line(SQLERRM);下面的话:SQL> create or replace procedure NO_new_stu(stu_name in student.stuname%type, 2 stu_id out student.stuid%type) 3 as 4 num char(4); 5 s_id char(4); 6 begin 7 select max(stuid) into num from student; 8 select stuid into s_id from student where stuname=stu_name; 9 if s_id is null then 10 if substr(num,3,2) between '01' and '09' then 11 stu_id:='1000'+substr(num,4,1)+1; 12 else 13 stu_id:='1000'+substr(num,3,2)+1; 14 end if; 15 else 16 RAISE_APPLICATION_ERROR(-20001,'The student is existed!'); 17 end if; 18 exception 19 when others then dbms_output.put_line(SQLERRM); 20 update student set stuid=stu_id where stu_name=stuname; 21 end; 22 /过程已创建。update这句干脆就不执行了,表student没有任何变化。只有把update这句放到Exception的上方才对表进行了更改: create or replace procedure NO_new_stu(stu_name in student.stuname%type, stu_id out student.stuid%type) as num char(4); s_id char(4); begin select max(stuid) into num from student; select stuid into s_id from student where stuname=stu_name; if s_id is 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; update student set stuid=stu_id where stu_name=stuname; exception when others then dbms_output.put_line(SQLERRM); end; /这是怎么回事呢??
create or replace procedure NO_new_stu(stu_name in student.stuname%type, stu_id out student.stuid%type) as num char(4); s_id char(4); begin begin select max(stuid) into num from student; select stuid into s_id from student where stuname=stu_name; if s_id is 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); end; update student set stuid=stu_id where stu_name=stuname; end; /少了个BEGIN. 有BEGIN就有END.
begin ................ begin ............... exception end; .................. begin ............... exception end;exception begin ...... exception end end; 就是这样一个原则一个begin和end 之间可以有一个exception
declare
......
begin
begin
insert into test(c1,c2) value(dd,ff);
exception
when others then
...........
end;
update .................
exception
when others then
................
end ;
create or replace procedure NO_new_stu(stu_name in student.stuname%type,
stu_id out student.stuid%type)
as
num char(4);
s_id char(4);
begin
select max(stuid) into num from student;
select stuid into s_id from student where stuname=stu_name;
if s_id is 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);
end;
update student set stuid=stu_id where stu_name=stuname;
end;
/警告: 创建的过程带有编译错误。SQL> show err
PROCEDURE NO_NEW_STU 出现错误:LINE/COL ERROR
-------- --------------------------------
20/1 PLS-00103: 出现符号 "UPDATE"
而且,如果我把update语句放到when others then dbms_output.put_line(SQLERRM);下面的话:SQL> create or replace procedure NO_new_stu(stu_name in student.stuname%type,
2 stu_id out student.stuid%type)
3 as
4 num char(4);
5 s_id char(4);
6 begin
7 select max(stuid) into num from student;
8 select stuid into s_id from student where stuname=stu_name;
9 if s_id is null then
10 if substr(num,3,2) between '01' and '09' then
11 stu_id:='1000'+substr(num,4,1)+1;
12 else
13 stu_id:='1000'+substr(num,3,2)+1;
14 end if;
15 else
16 RAISE_APPLICATION_ERROR(-20001,'The student is existed!');
17 end if;
18 exception
19 when others then dbms_output.put_line(SQLERRM);
20 update student set stuid=stu_id where stu_name=stuname;
21 end;
22 /过程已创建。update这句干脆就不执行了,表student没有任何变化。只有把update这句放到Exception的上方才对表进行了更改:
create or replace procedure NO_new_stu(stu_name in student.stuname%type,
stu_id out student.stuid%type)
as
num char(4);
s_id char(4);
begin
select max(stuid) into num from student;
select stuid into s_id from student where stuname=stu_name;
if s_id is 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;
update student set stuid=stu_id where stu_name=stuname;
exception
when others then dbms_output.put_line(SQLERRM);
end;
/这是怎么回事呢??
stu_id out student.stuid%type)
as
num char(4);
s_id char(4);
begin
begin
select max(stuid) into num from student;
select stuid into s_id from student where stuname=stu_name;
if s_id is 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);
end;
update student set stuid=stu_id where stu_name=stuname;
end;
/少了个BEGIN.
有BEGIN就有END.
................
begin
...............
exception
end;
..................
begin
...............
exception
end;exception
begin
......
exception
end
end;
就是这样一个原则一个begin和end 之间可以有一个exception