我的存储过程:
CREATE OR REPLACE PROCEDURE PRC_JG_CARMessage
IS
maxid integer;--最大ID号
sposition1 INTEGER;
sposition2 INTEGER;
mysubstr VARCHAR2(10);
starttimes INTEGER;
BEGIN
declare
cursor list is select content, mobile from car_alarm c where a.CL_ALARMTYPE=3 and ci_throughtime>sysdate-5/60/24 ; rs list%rowtype;
begin
for rs in list loop
select nvl(max(t.WSM_ID),1)+1 into maxid from police.guest_wsmsglog@MESSAGEDATA t ;
if nvl(maxid,0)=0 then
maxid:=1;
END IF;
starttimes:=1;
sposition1:=1;
loop
sposition2:=instr(rs.mobile,',',1,starttimes);--将上面查询出的字符串,用逗号分隔
exit when sposition2=0;
mysubstr:=substr(rs.mobile,sposition1,sposition2-sposition1);
sposition1:=sposition2+1;
starttimes:=starttimes+1;INSERT INTO police
( wsm_id, --id,integer
wsm_sdate, --发信时间,date
wsm_ruserno, --接收号码,字符
wsm_suserno, --发信号码,字符
wsm_content, --发信内容,字符
wsm_send, --发信端口,字符
wsm_sflag,
wsm_alias --发信系统标识,字符
)
values (maxid,sysdate,mysubstr,mysubstr,rs.content,'1',1,'OA');
end loop;
end loop;
COMMIT;
END PRC_JG_CARMessage;提示的错误:
Compilation errors for PROCEDURE ZS.PRC_JG_CARMESSAGEError: PLS-00103: 出现符号 "end-of-file"在需要下列之一时:
begin case declare
end exception exit for goto if loop mod null pragma raise
return select update while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge pipe
Line: 79
Text: END PRC_JG_CARMessage;
CREATE OR REPLACE PROCEDURE PRC_JG_CARMessage
IS
maxid integer;--最大ID号
sposition1 INTEGER;
sposition2 INTEGER;
mysubstr VARCHAR2(10);
starttimes INTEGER;
BEGIN
declare
cursor list is select content, mobile from car_alarm c where a.CL_ALARMTYPE=3 and ci_throughtime>sysdate-5/60/24 ; rs list%rowtype;
begin
for rs in list loop
select nvl(max(t.WSM_ID),1)+1 into maxid from police.guest_wsmsglog@MESSAGEDATA t ;
if nvl(maxid,0)=0 then
maxid:=1;
END IF;
starttimes:=1;
sposition1:=1;
loop
sposition2:=instr(rs.mobile,',',1,starttimes);--将上面查询出的字符串,用逗号分隔
exit when sposition2=0;
mysubstr:=substr(rs.mobile,sposition1,sposition2-sposition1);
sposition1:=sposition2+1;
starttimes:=starttimes+1;INSERT INTO police
( wsm_id, --id,integer
wsm_sdate, --发信时间,date
wsm_ruserno, --接收号码,字符
wsm_suserno, --发信号码,字符
wsm_content, --发信内容,字符
wsm_send, --发信端口,字符
wsm_sflag,
wsm_alias --发信系统标识,字符
)
values (maxid,sysdate,mysubstr,mysubstr,rs.content,'1',1,'OA');
end loop;
end loop;
COMMIT;
END PRC_JG_CARMessage;提示的错误:
Compilation errors for PROCEDURE ZS.PRC_JG_CARMESSAGEError: PLS-00103: 出现符号 "end-of-file"在需要下列之一时:
begin case declare
end exception exit for goto if loop mod null pragma raise
return select update while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge pipe
Line: 79
Text: END PRC_JG_CARMessage;
end loop; 去掉一个end loop;试试!
BEGIN
declare
去掉。或者末尾再添一个end;
你这里使用了内部块,却没有相应的end;
CREATE OR REPLACE PROCEDURE PRC_JG_CARMessage
IS
maxid integer;--最大ID号
sposition1 INTEGER;
sposition2 INTEGER;
mysubstr VARCHAR2(10);
starttimes INTEGER;
cursor list is select content, mobile from car_alarm c where a.CL_ALARMTYPE=3 and ci_throughtime>sysdate-5/60/24 ;
rs list%rowtype;
begin
for rs in list_rec loop
select nvl(max(t.WSM_ID),1)+1 into maxid from police.guest_wsmsglog@MESSAGEDATA t ;
if nvl(maxid,0)=0 then
maxid:=1;
END IF;
starttimes:=1;
sposition1:=1;
loop
sposition2:=instr(rs.mobile,',',1,starttimes);--将上面查询出的字符串,用逗号分隔
exit when sposition2=0;
mysubstr:=substr(rs.mobile,sposition1,sposition2-sposition1);
sposition1:=sposition2+1;
starttimes:=starttimes+1;INSERT INTO police
( wsm_id, --id,integer
wsm_sdate, --发信时间,date
wsm_ruserno, --接收号码,字符
wsm_suserno, --发信号码,字符
wsm_content, --发信内容,字符
wsm_send, --发信端口,字符
wsm_sflag,
wsm_alias --发信系统标识,字符
)
values (maxid,sysdate,mysubstr,mysubstr,rs.content,'1',1,'OA');
end loop;
end loop;
COMMIT;
END PRC_JG_CARMessage;
--去掉了开头申明游标的begin declare