CURSOR syn_cursor IS select AA.XXCODE, AA.xcode, AA.xname, AA.mobile, AA.CARD_ID from (select iccard_familyinfo.xxcode, iccard_familyinfo.xcode, iccard_familyinfo.xname, iccard_familyinfo.mobile, iccard_card_student.card_id from iccard_card_student inner join iccard_familyinfo on iccard_card_student.xxcode = iccard_familyinfo.xxcode and iccard_card_student.xcode = iccard_familyinfo.xcode where iccard_familyinfo.xxcode in (select xxcode from iccard_limit)) AA left join (select iccard_safearrive_4mulcheck.xxcode, xcode, checktime, iccard_safearrive_4mulcheck.regid, re, regularflag from iccard_safearrive_4mulcheck inner join iccard_school_setting on iccard_safearrive_4mulcheck.regid = iccard_school_setting.regid and iccard_safearrive_4mulcheck.xxcode = iccard_school_setting.xxcode) BB on AA.Xxcode = BB.XXCODE and AA.xcode = BB.xcode where (regid is null or regid in (97, 98, 99)); BEGIN OPEN syn_cursor; LOOP FETCH syn_cursor INTO vxxcode, vxcode, vxname, vmobile, vcard_id; EXIT WHEN syn_cursor%NOTFOUND; INSERT INTO iccard_stat_abstudent_new (xxcode, xcode, xname, mobile, card_id,statdate) values (vxxcode, vxcode, vxname, vmobile, vcard_id,sysdate); ---发短信 INSERT INTO message_out (xxcode, xcode, mobile, msgtype, optype, msg, sendtime, creattime, userid, priority, checkflag) values(vxxcode,vxcode,vmobile,'09','21','...',sysdate,sysdate,'02','',''); commit; END LOOP; CLOSE syn_cursor;
同意,贴完整代码,否则,不好定位。字面上看,如果存在if存在,过程最基本的 begin 关键字就失效了
你把最外面那个begin end写到if里面了。最外壳要有一个begin end if里面高兴可以再写,不能把外面的套在if里面。
CREATE OR REPLACE PROCEDURE iccard_info_check AS vxxcode varchar2(50); vxname varchar2(50); vxcode varchar2(50); vmobile varchar2(30); vcard_id varchar2(30);if(to_number(to_char(sysdate ,'HH24')) < 9) then begin vcard_id := 1 ; return ; end ; end if ; CURSOR syn_cursor IS select AA.XXCODE, AA.xcode, AA.xname, AA.mobile, AA.CARD_ID from (select iccard_familyinfo.xxcode, iccard_familyinfo.xcode, iccard_familyinfo.xname, iccard_familyinfo.mobile, iccard_card_student.card_id from iccard_card_student inner join iccard_familyinfo on iccard_card_student.xxcode = iccard_familyinfo.xxcode and iccard_card_student.xcode = iccard_familyinfo.xcode where iccard_familyinfo.xxcode in (select xxcode from iccard_limit)) AA left join (select iccard_safearrive_4mulcheck.xxcode, xcode, checktime, iccard_safearrive_4mulcheck.regid, re, regularflag from iccard_safearrive_4mulcheck inner join iccard_school_setting on iccard_safearrive_4mulcheck.regid = iccard_school_setting.regid and iccard_safearrive_4mulcheck.xxcode = iccard_school_setting.xxcode) BB on AA.Xxcode = BB.XXCODE and AA.xcode = BB.xcode where (regid is null or regid in (97, 98, 99)); BEGIN OPEN syn_cursor; LOOP FETCH syn_cursor INTO vxxcode, vxcode, vxname, vmobile, vcard_id; EXIT WHEN syn_cursor%NOTFOUND; INSERT INTO iccard_stat_abstudent_new (xxcode, xcode, xname, mobile, card_id,statdate) values (vxxcode, vxcode, vxname, vmobile, vcard_id,sysdate); ---发短信 INSERT INTO message_out (xxcode, xcode, mobile, msgtype, optype, msg, sendtime, creattime, userid, priority, checkflag) values(vxxcode,vxcode,vmobile,'09','21','尊敬的家长,您的孩子'||vxname||'上午没有到校打卡。',sysdate,sysdate,'02','',''); commit; END LOOP; CLOSE syn_cursor;END iccard_info_check;
CREATE OR REPLACE PROCEDURE iccard_info_check AS vxxcode varchar2(50); vxname varchar2(50); vxcode varchar2(50); vmobile varchar2(30); vcard_id varchar2(30);if(to_number(to_char(sysdate ,'HH24')) < 9) then begin vcard_id := 1 ; return ; end ; end if ; CURSOR syn_cursor IS select AA.XXCODE, AA.xcode, AA.xname, AA.mobile, AA.CARD_ID from (select iccard_familyinfo.xxcode, iccard_familyinfo.xcode, iccard_familyinfo.xname, iccard_familyinfo.mobile, iccard_card_student.card_id from iccard_card_student inner join iccard_familyinfo on iccard_card_student.xxcode = iccard_familyinfo.xxcode and iccard_card_student.xcode = iccard_familyinfo.xcode where iccard_familyinfo.xxcode in (select xxcode from iccard_limit)) AA left join (select iccard_safearrive_4mulcheck.xxcode, xcode, checktime, iccard_safearrive_4mulcheck.regid, re, regularflag from iccard_safearrive_4mulcheck inner join iccard_school_setting on iccard_safearrive_4mulcheck.regid = iccard_school_setting.regid and iccard_safearrive_4mulcheck.xxcode = iccard_school_setting.xxcode) BB on AA.Xxcode = BB.XXCODE and AA.xcode = BB.xcode where (regid is null or regid in (97, 98, 99)); BEGIN OPEN syn_cursor; LOOP FETCH syn_cursor INTO vxxcode, vxcode, vxname, vmobile, vcard_id; EXIT WHEN syn_cursor%NOTFOUND; INSERT INTO iccard_stat_abstudent_new (xxcode, xcode, xname, mobile, card_id,statdate) values (vxxcode, vxcode, vxname, vmobile, vcard_id,sysdate); ---发短信 INSERT INTO message_out (xxcode, xcode, mobile, msgtype, optype, msg, sendtime, creattime, userid, priority, checkflag) values(vxxcode,vxcode,vmobile,'09','21','尊敬的家长,您的孩子'||vxname||'上午没有到校打卡。',sysdate,sysdate,'02','',''); commit; END LOOP; CLOSE syn_cursor;END iccard_info_check;
CREATE OR REPLACE PROCEDURE iccard_info_check AS vxxcode varchar2(50); vxname varchar2(50); vxcode varchar2(50); vmobile varchar2(30); vcard_id varchar2(30);if(to_number(to_char(sysdate ,'HH24')) < 9) then begin vcard_id := 1 ; return ; end ; end if ; CURSOR syn_cursor IS select AA.XXCODE, AA.xcode, AA.xname, AA.mobile, AA.CARD_ID from (select iccard_familyinfo.xxcode, iccard_familyinfo.xcode, iccard_familyinfo.xname, iccard_familyinfo.mobile, iccard_card_student.card_id from iccard_card_student inner join iccard_familyinfo on iccard_card_student.xxcode = iccard_familyinfo.xxcode and iccard_card_student.xcode = iccard_familyinfo.xcode where iccard_familyinfo.xxcode in (select xxcode from iccard_limit)) AA left join (select iccard_safearrive_4mulcheck.xxcode, xcode, checktime, iccard_safearrive_4mulcheck.regid, re, regularflag from iccard_safearrive_4mulcheck inner join iccard_school_setting on iccard_safearrive_4mulcheck.regid = iccard_school_setting.regid and iccard_safearrive_4mulcheck.xxcode = iccard_school_setting.xxcode) BB on AA.Xxcode = BB.XXCODE and AA.xcode = BB.xcode where (regid is null or regid in (97, 98, 99)); BEGIN OPEN syn_cursor; LOOP FETCH syn_cursor INTO vxxcode, vxcode, vxname, vmobile, vcard_id; EXIT WHEN syn_cursor%NOTFOUND; INSERT INTO iccard_stat_abstudent_new (xxcode, xcode, xname, mobile, card_id,statdate) values (vxxcode, vxcode, vxname, vmobile, vcard_id,sysdate); ---发短信 INSERT INTO message_out (xxcode, xcode, mobile, msgtype, optype, msg, sendtime, creattime, userid, priority, checkflag) values(vxxcode,vxcode,vmobile,'09','21','尊敬的家长,您的孩子'||vxname||'上午没有到校打卡。',sysdate,sysdate,'02','',''); commit; END LOOP; CLOSE syn_cursor;END iccard_info_check;
CREATE OR REPLACE PROCEDURE iccard_info_check AS vxxcode varchar2(50); vxname varchar2(50); vxcode varchar2(50); vmobile varchar2(30); vcard_id varchar2(30); /*--这里去掉,放到下面 if(to_number(to_char(sysdate ,'HH24')) < 9) then begin vcard_id := 1 ; return ; end ; end if ;*/ CURSOR syn_cursor IS select AA.XXCODE, AA.xcode, AA.xname, AA.mobile, AA.CARD_ID from (select iccard_familyinfo.xxcode, iccard_familyinfo.xcode, iccard_familyinfo.xname, iccard_familyinfo.mobile, iccard_card_student.card_id from iccard_card_student inner join iccard_familyinfo on iccard_card_student.xxcode = iccard_familyinfo.xxcode and iccard_card_student.xcode = iccard_familyinfo.xcode where iccard_familyinfo.xxcode in (select xxcode from iccard_limit)) AA left join (select iccard_safearrive_4mulcheck.xxcode, xcode, checktime, iccard_safearrive_4mulcheck.regid, re, regularflag from iccard_safearrive_4mulcheck inner join iccard_school_setting on iccard_safearrive_4mulcheck.regid = iccard_school_setting.regid and iccard_safearrive_4mulcheck.xxcode = iccard_school_setting.xxcode) BB on AA.Xxcode = BB.XXCODE and AA.xcode = BB.xcode where (regid is null or regid in (97, 98, 99)); BEGIN --放到这里 if(to_number(to_char(sysdate ,'HH24')) < 9) then begin vcard_id := 1 ; return ; end ; end if ; OPEN syn_cursor; LOOP FETCH syn_cursor INTO vxxcode, vxcode, vxname, vmobile, vcard_id; EXIT WHEN syn_cursor%NOTFOUND; INSERT INTO iccard_stat_abstudent_new (xxcode, xcode, xname, mobile, card_id,statdate) values (vxxcode, vxcode, vxname, vmobile, vcard_id,sysdate); ---发短信 INSERT INTO message_out (xxcode, xcode, mobile, msgtype, optype, msg, sendtime, creattime, userid, priority, checkflag) values(vxxcode,vxcode,vmobile,'09','21','尊敬的家长,您的孩子'||vxname||'上午没有到校打卡。',sysdate,sysdate,'02','',''); commit; END LOOP; CLOSE syn_cursor;END iccard_info_check;
估计是sql语句错误……
select AA.XXCODE, AA.xcode, AA.xname, AA.mobile, AA.CARD_ID
from (select iccard_familyinfo.xxcode,
iccard_familyinfo.xcode,
iccard_familyinfo.xname,
iccard_familyinfo.mobile,
iccard_card_student.card_id
from iccard_card_student
inner join iccard_familyinfo on iccard_card_student.xxcode =
iccard_familyinfo.xxcode
and iccard_card_student.xcode =
iccard_familyinfo.xcode
where iccard_familyinfo.xxcode in
(select xxcode from iccard_limit)) AA
left join (select iccard_safearrive_4mulcheck.xxcode,
xcode,
checktime,
iccard_safearrive_4mulcheck.regid,
re,
regularflag
from iccard_safearrive_4mulcheck
inner join iccard_school_setting on iccard_safearrive_4mulcheck.regid =
iccard_school_setting.regid
and iccard_safearrive_4mulcheck.xxcode =
iccard_school_setting.xxcode) BB on AA.Xxcode =
BB.XXCODE
and AA.xcode =
BB.xcode
where (regid is null or regid in (97, 98, 99));
BEGIN
OPEN syn_cursor;
LOOP
FETCH syn_cursor
INTO vxxcode, vxcode, vxname, vmobile, vcard_id;
EXIT WHEN syn_cursor%NOTFOUND;
INSERT INTO iccard_stat_abstudent_new
(xxcode, xcode, xname, mobile, card_id,statdate)
values
(vxxcode, vxcode, vxname, vmobile, vcard_id,sysdate);
---发短信
INSERT INTO message_out
(xxcode,
xcode,
mobile,
msgtype,
optype,
msg,
sendtime,
creattime,
userid,
priority,
checkflag)
values(vxxcode,vxcode,vmobile,'09','21','...',sysdate,sysdate,'02','','');
commit;
END LOOP;
CLOSE syn_cursor;
vxxcode varchar2(50);
vxname varchar2(50);
vxcode varchar2(50);
vmobile varchar2(30);
vcard_id varchar2(30);if(to_number(to_char(sysdate ,'HH24')) < 9) then
begin
vcard_id := 1 ;
return ;
end ;
end if ; CURSOR syn_cursor IS
select AA.XXCODE, AA.xcode, AA.xname, AA.mobile, AA.CARD_ID
from (select iccard_familyinfo.xxcode,
iccard_familyinfo.xcode,
iccard_familyinfo.xname,
iccard_familyinfo.mobile,
iccard_card_student.card_id
from iccard_card_student
inner join iccard_familyinfo on iccard_card_student.xxcode =
iccard_familyinfo.xxcode
and iccard_card_student.xcode =
iccard_familyinfo.xcode
where iccard_familyinfo.xxcode in
(select xxcode from iccard_limit)) AA
left join (select iccard_safearrive_4mulcheck.xxcode,
xcode,
checktime,
iccard_safearrive_4mulcheck.regid,
re,
regularflag
from iccard_safearrive_4mulcheck
inner join iccard_school_setting on iccard_safearrive_4mulcheck.regid =
iccard_school_setting.regid
and iccard_safearrive_4mulcheck.xxcode =
iccard_school_setting.xxcode) BB on AA.Xxcode =
BB.XXCODE
and AA.xcode =
BB.xcode
where (regid is null or regid in (97, 98, 99));
BEGIN
OPEN syn_cursor;
LOOP
FETCH syn_cursor
INTO vxxcode, vxcode, vxname, vmobile, vcard_id;
EXIT WHEN syn_cursor%NOTFOUND;
INSERT INTO iccard_stat_abstudent_new
(xxcode, xcode, xname, mobile, card_id,statdate)
values
(vxxcode, vxcode, vxname, vmobile, vcard_id,sysdate);
---发短信
INSERT INTO message_out
(xxcode,
xcode,
mobile,
msgtype,
optype,
msg,
sendtime,
creattime,
userid,
priority,
checkflag)
values(vxxcode,vxcode,vmobile,'09','21','尊敬的家长,您的孩子'||vxname||'上午没有到校打卡。',sysdate,sysdate,'02','','');
commit;
END LOOP;
CLOSE syn_cursor;END iccard_info_check;
vxxcode varchar2(50);
vxname varchar2(50);
vxcode varchar2(50);
vmobile varchar2(30);
vcard_id varchar2(30);if(to_number(to_char(sysdate ,'HH24')) < 9) then
begin
vcard_id := 1 ;
return ;
end ;
end if ; CURSOR syn_cursor IS
select AA.XXCODE, AA.xcode, AA.xname, AA.mobile, AA.CARD_ID
from (select iccard_familyinfo.xxcode,
iccard_familyinfo.xcode,
iccard_familyinfo.xname,
iccard_familyinfo.mobile,
iccard_card_student.card_id
from iccard_card_student
inner join iccard_familyinfo on iccard_card_student.xxcode =
iccard_familyinfo.xxcode
and iccard_card_student.xcode =
iccard_familyinfo.xcode
where iccard_familyinfo.xxcode in
(select xxcode from iccard_limit)) AA
left join (select iccard_safearrive_4mulcheck.xxcode,
xcode,
checktime,
iccard_safearrive_4mulcheck.regid,
re,
regularflag
from iccard_safearrive_4mulcheck
inner join iccard_school_setting on iccard_safearrive_4mulcheck.regid =
iccard_school_setting.regid
and iccard_safearrive_4mulcheck.xxcode =
iccard_school_setting.xxcode) BB on AA.Xxcode =
BB.XXCODE
and AA.xcode =
BB.xcode
where (regid is null or regid in (97, 98, 99));
BEGIN
OPEN syn_cursor;
LOOP
FETCH syn_cursor
INTO vxxcode, vxcode, vxname, vmobile, vcard_id;
EXIT WHEN syn_cursor%NOTFOUND;
INSERT INTO iccard_stat_abstudent_new
(xxcode, xcode, xname, mobile, card_id,statdate)
values
(vxxcode, vxcode, vxname, vmobile, vcard_id,sysdate);
---发短信
INSERT INTO message_out
(xxcode,
xcode,
mobile,
msgtype,
optype,
msg,
sendtime,
creattime,
userid,
priority,
checkflag)
values(vxxcode,vxcode,vmobile,'09','21','尊敬的家长,您的孩子'||vxname||'上午没有到校打卡。',sysdate,sysdate,'02','','');
commit;
END LOOP;
CLOSE syn_cursor;END iccard_info_check;
CREATE OR REPLACE PROCEDURE iccard_info_check AS
vxxcode varchar2(50);
vxname varchar2(50);
vxcode varchar2(50);
vmobile varchar2(30);
vcard_id varchar2(30);if(to_number(to_char(sysdate ,'HH24')) < 9) then
begin
vcard_id := 1 ;
return ;
end ;
end if ; CURSOR syn_cursor IS
select AA.XXCODE, AA.xcode, AA.xname, AA.mobile, AA.CARD_ID
from (select iccard_familyinfo.xxcode,
iccard_familyinfo.xcode,
iccard_familyinfo.xname,
iccard_familyinfo.mobile,
iccard_card_student.card_id
from iccard_card_student
inner join iccard_familyinfo on iccard_card_student.xxcode =
iccard_familyinfo.xxcode
and iccard_card_student.xcode =
iccard_familyinfo.xcode
where iccard_familyinfo.xxcode in
(select xxcode from iccard_limit)) AA
left join (select iccard_safearrive_4mulcheck.xxcode,
xcode,
checktime,
iccard_safearrive_4mulcheck.regid,
re,
regularflag
from iccard_safearrive_4mulcheck
inner join iccard_school_setting on iccard_safearrive_4mulcheck.regid =
iccard_school_setting.regid
and iccard_safearrive_4mulcheck.xxcode =
iccard_school_setting.xxcode) BB on AA.Xxcode =
BB.XXCODE
and AA.xcode =
BB.xcode
where (regid is null or regid in (97, 98, 99));
BEGIN
OPEN syn_cursor;
LOOP
FETCH syn_cursor
INTO vxxcode, vxcode, vxname, vmobile, vcard_id;
EXIT WHEN syn_cursor%NOTFOUND;
INSERT INTO iccard_stat_abstudent_new
(xxcode, xcode, xname, mobile, card_id,statdate)
values
(vxxcode, vxcode, vxname, vmobile, vcard_id,sysdate);
---发短信
INSERT INTO message_out
(xxcode,
xcode,
mobile,
msgtype,
optype,
msg,
sendtime,
creattime,
userid,
priority,
checkflag)
values(vxxcode,vxcode,vmobile,'09','21','尊敬的家长,您的孩子'||vxname||'上午没有到校打卡。',sysdate,sysdate,'02','','');
commit;
END LOOP;
CLOSE syn_cursor;END iccard_info_check;
vxxcode varchar2(50);
vxname varchar2(50);
vxcode varchar2(50);
vmobile varchar2(30);
vcard_id varchar2(30);
/*--这里去掉,放到下面
if(to_number(to_char(sysdate ,'HH24')) < 9) then
begin
vcard_id := 1 ;
return ;
end ;
end if ;*/ CURSOR syn_cursor IS
select AA.XXCODE, AA.xcode, AA.xname, AA.mobile, AA.CARD_ID
from (select iccard_familyinfo.xxcode,
iccard_familyinfo.xcode,
iccard_familyinfo.xname,
iccard_familyinfo.mobile,
iccard_card_student.card_id
from iccard_card_student
inner join iccard_familyinfo on iccard_card_student.xxcode =
iccard_familyinfo.xxcode
and iccard_card_student.xcode =
iccard_familyinfo.xcode
where iccard_familyinfo.xxcode in
(select xxcode from iccard_limit)) AA
left join (select iccard_safearrive_4mulcheck.xxcode,
xcode,
checktime,
iccard_safearrive_4mulcheck.regid,
re,
regularflag
from iccard_safearrive_4mulcheck
inner join iccard_school_setting on iccard_safearrive_4mulcheck.regid =
iccard_school_setting.regid
and iccard_safearrive_4mulcheck.xxcode =
iccard_school_setting.xxcode) BB on AA.Xxcode =
BB.XXCODE
and AA.xcode =
BB.xcode
where (regid is null or regid in (97, 98, 99));
BEGIN
--放到这里
if(to_number(to_char(sysdate ,'HH24')) < 9) then
begin
vcard_id := 1 ;
return ;
end ;
end if ; OPEN syn_cursor;
LOOP
FETCH syn_cursor
INTO vxxcode, vxcode, vxname, vmobile, vcard_id;
EXIT WHEN syn_cursor%NOTFOUND;
INSERT INTO iccard_stat_abstudent_new
(xxcode, xcode, xname, mobile, card_id,statdate)
values
(vxxcode, vxcode, vxname, vmobile, vcard_id,sysdate);
---发短信
INSERT INTO message_out
(xxcode,
xcode,
mobile,
msgtype,
optype,
msg,
sendtime,
creattime,
userid,
priority,
checkflag)
values(vxxcode,vxcode,vmobile,'09','21','尊敬的家长,您的孩子'||vxname||'上午没有到校打卡。',sysdate,sysdate,'02','','');
commit;
END LOOP;
CLOSE syn_cursor;END iccard_info_check;