我的表空间有一个表temp1,有三个字段PKID,UserName,QQ,其中PKID是Number型的,UserName,QQ都是字符型的.我写了个存储过程(过程),过程源码如下:
create or replace procedure Insert_Ro(uname varchar2,qqnum varchar2,flag out number)
as
tempname temp1.username%type;
begin
flag:=1;
select username into tempname from temp1 where username=tempname;
if tempname is null then
begin
insert into temp1(pkid,username,qq) values(aaa.nextval,uname,qqnum);
flag:=aaa.currval;
end;
else
flag:=0;
end if ;
end Insert_Ro;存储过程的意思就是插入一条记录,在插入记录前,先检查数据库中是否存在有相同用户名的记录,如果有,则返回0,表示已经存在这个用户名,不允许插入,否则就插入该条记录,并且返回该条记录的PKID,aaa是一个序列.这个过程系统能够通过编译.但在执行时,出现了以下问题:SQL> /declare fl number;
exec insert_to('wang','123456',f1);ORA-06550: 第 5 行, 第 0 列:
PLS-00103: 出现符号 "end-of-file"在需要下列之一时:
begin function
package pragma procedure subtype type use <an identifier>
<a double-quoted delimited-identifier> form current cursor
为什么会出现上述问题,请高人指点下.万分感激!!!
create or replace procedure Insert_Ro(uname varchar2,qqnum varchar2,flag out number)
as
tempname temp1.username%type;
begin
flag:=1;
select username into tempname from temp1 where username=tempname;
if tempname is null then
begin
insert into temp1(pkid,username,qq) values(aaa.nextval,uname,qqnum);
flag:=aaa.currval;
end;
else
flag:=0;
end if ;
end Insert_Ro;存储过程的意思就是插入一条记录,在插入记录前,先检查数据库中是否存在有相同用户名的记录,如果有,则返回0,表示已经存在这个用户名,不允许插入,否则就插入该条记录,并且返回该条记录的PKID,aaa是一个序列.这个过程系统能够通过编译.但在执行时,出现了以下问题:SQL> /declare fl number;
exec insert_to('wang','123456',f1);ORA-06550: 第 5 行, 第 0 列:
PLS-00103: 出现符号 "end-of-file"在需要下列之一时:
begin function
package pragma procedure subtype type use <an identifier>
<a double-quoted delimited-identifier> form current cursor
为什么会出现上述问题,请高人指点下.万分感激!!!
......
vSqlAll := 'select username from temp1 where username=tempname';
EXECUTE IMMEDIATE vSqlAll INTO tempname
........
if tempname is null then
(
uname varchar2,
qqnum varchar2,
flag out number
)
as
tempname temp1.username%type;
begin
select username into tempname from temp1 where username=tempname;
flag:=0;
exception
when NO_DATA_FOUND then
begin
insert into temp1(pkid,username,qq) values(aaa.nextval,uname,qqnum);
flag:=aaa.currval;
end;
when others then
raise;
end Insert_Ro;
===>
select username into tempname from temp1 where username=uname;
(
uname varchar2,
qqnum varchar2,
flag out number
)
as
tempname temp1.username%type;
begin
select username into tempname from temp1 where username=uname;
flag:=0;
exception
when NO_DATA_FOUND then
begin
insert into temp1(pkid,username,qq) values(aaa.nextval,uname,qqnum);
select aaa.currval into flag from dual;
end;
when others then
raise;
end Insert_Ro;