问一个存储过程的问题，各位高手来看看，我卡住了

Question

本帖最后由 a8723832 于 2012-02-22 19:02:17 编辑

Answer 1

贴游标定义代码……
估计是sql语句错误……

Answer 2

 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;

Answer 3

同意，贴完整代码，否则，不好定位。字面上看，如果存在if存在，过程最基本的 begin 关键字就失效了
 

Answer 4

你把最外面那个begin end写到if里面了。最外壳要有一个begin end if里面高兴可以再写，不能把外面的套在if里面。

Answer 5

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;

Answer 6

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;

Answer 7

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;

Answer 8

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;