create or replace procedure pro_addstu
(p_id in graduate_info.id%type,
p_name in graduate_info.name%type,
p_sex in graduate_info.sex%type,
p_birthday in graduate_info.birthday%type,
p_classno in graduate_info.classno%type,
p_major in graduate_info.major%type,
p_director in graduate_info.director%type,
p_source in graduate_info.source%type,
p_score in graduate_info.score%type,
p_bonus in graduate_info.bonus%type,
v_re out number)
as
v_count number;
v_expt exception;
v_expt1 exception;
cursor exit_cursor is select count(*) from graduate_info where name=p_name;begin
if p_name is null then
raise v_expt;--学生姓名不能为空
end if;
open exit_cursor;
fetch exit_cursor into v_count;
if v_count>0 then
raise v_expt1;--学生名称已经存在
else
insert into graduate_info values(id.NEXTVAL,p_name,p_sex,p_birthday,p_classno,p_major,p_director,p_source,p_score,p_bonus);
v_re:=1;--增加成功返回1
end if;
close exit_cursor;
exception
when v_expt then
v_re:=0;
when v_expt1 then
v_re:=-1;
end;)
(p_id in graduate_info.id%type,
p_name in graduate_info.name%type,
p_sex in graduate_info.sex%type,
p_birthday in graduate_info.birthday%type,
p_classno in graduate_info.classno%type,
p_major in graduate_info.major%type,
p_director in graduate_info.director%type,
p_source in graduate_info.source%type,
p_score in graduate_info.score%type,
p_bonus in graduate_info.bonus%type,
v_re out number)
as
v_count number;
v_expt exception;
v_expt1 exception;
cursor exit_cursor is select count(*) from graduate_info where name=p_name;begin
if p_name is null then
raise v_expt;--学生姓名不能为空
end if;
open exit_cursor;
fetch exit_cursor into v_count;
if v_count>0 then
raise v_expt1;--学生名称已经存在
else
insert into graduate_info values(id.NEXTVAL,p_name,p_sex,p_birthday,p_classno,p_major,p_director,p_source,p_score,p_bonus);
v_re:=1;--增加成功返回1
end if;
close exit_cursor;
exception
when v_expt then
v_re:=0;
when v_expt1 then
v_re:=-1;
end;)
解决方案 »
- Oracle求助啊 11g
- 远程数据库的视图(包含图片blob)
- oracle提取方案中的表名的查询语句
- 求sql语句一条,工作需要,急,望大家帮忙
- 怪事,相同版本却不能导入备份dmp文件
- oracle8i客户端安装后连不上数据库?急!急急急急!
- 两条pl/sql该怎么写?(pl/sql高手请进!!!!!!分不够再加)
- oracle默认用户那个有什么都能干的权限?
- oracle中一个关于不同时间复合查询的请教!
- MySQL#求问Got error 1 from storage engine解决方法
- oracle 11 g 比 ms sql server 2008 快多少?
- oracle定时任务时间出错
你的过程最后end;后面还有半个括号不知道是什么意思?
2. name字段建立unique约束,直接野蛮插入数据,成功后返回1,捕获异常时,说明数据已经存在,返回0;
(p_id in graduate_info.id%type,
p_name in graduate_info.name%type,
p_sex in graduate_info.sex%type,
p_birthday in graduate_info.birthday%type,
p_classno in graduate_info.classno%type,
p_major in graduate_info.major%type,
p_director in graduate_info.director%type,
p_source in graduate_info.source%type,
p_score in graduate_info.score%type,
p_bonus in graduate_info.bonus%type,
v_re out number)
as
v_count number;
v_expt exception;
v_expt1 exception; cursor exit_cursor is select count(*) from graduate_info where name=p_name; --可以不定义该游标begin if p_name is null then
raise v_expt;--学生姓名不能为空
end if; open exit_cursor; fetch exit_cursor into v_count;
if v_count>0 then
raise v_expt1;--学生名称已经存在
else
insert into graduate_info values(id.NEXTVAL,p_name,p_sex,p_birthday,p_classno,p_major,p_director,p_source,p_score,p_bonus);
v_re:=1;--增加成功返回1
end if; close exit_cursor; exception
when v_expt then
v_re:=0;
when v_expt1 then
v_re:=-1;
when others then --在此处可以捕获一下其他异常信息,并打印出来
dbms_output.put_line(sqlcode||sqlerrm);
end;
)
好像在最后多了个右括号。