存储过程问题!

Question

我的一个select两个表,然后insert另外一个表,然后在jbuilder里面
public void run(){
    setcall();
    while(true){
      try{
        cst.executeUpdate() ;
        threadSleep(1000);        
      } catch(SQLException exce){
        System.out.println("[smservice.procedure.CallProdure.run]:"+myName +" failed. sql is:"+mySql+" cause:"+exce.getMessage()) ;       
        setcall();
      }
      threadSleep(threadtime);
    }
  }
  public void setcall(){
    while(true){
      threadSleep(100);
      try{
       if(cst!=null) {
       cst.close();
       cst=null;     
       }
        cst=con.prepareCall(this.mySql);
        return;
      } catch(SQLException es){
        System.out.println("[smservice.procedure.CallProdure.setcall]:"+myName +" failed. sql is:"+mySql+" cause:"+es.getMessage()) ;
      }
    }
  }
可是运行一段时间后却报错说超过打开的最大游标,可是我认为不是jbuilder的调用问题.请哪位大哥指教看到底是哪里的问题,存储过程代码如下:
create or replace procedure Pro_match_submit is
cid                               integer;
selectsql                         varchar2(4000);
insertsql                         varchar2(4000);
nofrows                           integer;
lrowid                            rowid;
errpsn                            integer;
sqlfcd                            integer;
errc                              integer;
errm                              varchar2(2000);
row_count                         integer;
rows_processed                    integer;
cid2                              integer;
p_mtsequence1                       number(10);
p_mtsequence2                       number(10);
p_mtsequence3                       number(10);
eid                               varchar2(20);
spnumber                          varchar2(21);
chargenumber                      varchar2(21);
corpid                            varchar2(21);
servicetype                       varchar2(10);
feetype                           integer;
feevalue                          varchar2(6);
givenvalue                        varchar2(21);
agentflag                         integer;
morelatetomtflag                  integer;
priority                          integer;
exprietime                        varchar2(17);
scheduletime                      varchar2(17);
reportflag                        integer;
tp_pid                            integer;
tp_udhi                           integer;
messagecoding                     integer;
messagetype                       integer;
messagelength                     integer;
messagecontent                    varchar2(160);
submittime                        date;
result                            integer;
insertdate                        date;
usernumber                        varchar2(21);
reserve                           varchar2(8);
status                            integer;begin
  selectsql:='select a.mtsequence1,a.mtsequence2,a.mtsequence3,a.eid,a.spnumber,a.chargenumber,a.corpid,a.servicetype,a.feetype,a.feevalue,a.givenvalue,a.agentflag,a.morelatetomtflag,a.priority,a.exprietime,a.scheduletime,a.reportflag,a.tp_pid,a.tp_udhi,a.messagecoding,a.messagetype,a.messagelength,a.messagecontent,a.submittime,b.result,b.insertdate,a.usernumber,a.reserve from smssubmit a,smssubmitresp b where a.mtsequence1=b.mtsequence1 and a.mtsequence2=b.mtsequence2 and a.mtsequence3=b.mtsequence3 and a.status=-2';
  begin
  cid:=dbms_sql.open_cursor;
  dbms_sql.parse(cid,selectsql,dbms_sql.native);  dbms_sql.define_column(cid,1,p_mtsequence1);
  dbms_sql.define_column(cid,2,p_mtsequence2);
  dbms_sql.define_column(cid,3,p_mtsequence3);
  dbms_sql.define_column(cid,4,eid,20);
  dbms_sql.define_column(cid,5,spnumber,21);
  dbms_sql.define_column(cid,6,chargenumber,21);
  dbms_sql.define_column(cid,7,corpid,21);
  dbms_sql.define_column(cid,8,servicetype,10);
  dbms_sql.define_column(cid,9,feetype);
  dbms_sql.define_column(cid,10,feevalue,6);
  dbms_sql.define_column(cid,11,givenvalue,6);
  dbms_sql.define_column(cid,12,agentflag);
  dbms_sql.define_column(cid,13,morelatetomtflag);
  dbms_sql.define_column(cid,14,priority);
  dbms_sql.define_column(cid,15,exprietime,17);
  dbms_sql.define_column(cid,16,scheduletime,17);
  dbms_sql.define_column(cid,17,reportflag);
  dbms_sql.define_column(cid,18,tp_pid);
  dbms_sql.define_column(cid,19,tp_udhi);
  dbms_sql.define_column(cid,20,messagecoding);
  dbms_sql.define_column(cid,21,messagetype);
  dbms_sql.define_column(cid,22,messagelength);
  dbms_sql.define_column(cid,23,messagecontent,160);
  dbms_sql.define_column(cid,24,submittime);
  dbms_sql.define_column(cid,25,result);
  dbms_sql.define_column(cid,26,insertdate);
  dbms_sql.define_column(cid,27,usernumber,21);
  dbms_sql.define_column(cid,28,reserve,8);

Answer 1

exception 
    when others then
      errpsn:=dbms_sql.last_error_position;
      sqlfcd:=dbms_sql.last_sql_function_code;
      lrowid:=dbms_sql.last_row_id;
      errc:=SQLCODE;
      errm:=sqlerrm;
      dbms_output.put_line('Error '||to_char(errc)||' Posn '||to_char(errpsn)||' SQL fCode '||to_char(sqlfcd)||' rowid '||rowidtochar(lrowid));
      raise_application_error(-20000,errm);
      end;
      begin
        row_count:=0;
        nofrows:=dbms_sql.execute(cid);
        cid2:=dbms_sql.open_cursor;
        insertsql:='insert into smssubmitover(mtsequence1,mtsequence2,mtsequence3,eid,spnumber,chargenumber         ,corpid,servicetype,feetype,feevalue,givenvalue,agentflag,morelatetomtflag,priority,exprietime          ,scheduletime,reportflag,tp_pid,tp_udhi,messagecoding,messagetype,messagelength,messagecontent,reserve,usernumber          ,submittime,status,result,insertdate) values(:p_mtsequence1,:p_mtsequence2,:p_mtsequence3,:eid,:spnumber,:chargenumber,:corpid,:servicetype,:feetype,:feevalue,:givenvalue,:agentflag,:morelatetomtflag,:priority,:exprietime,:scheduletime,:reportflag,:tp_pid,:tp_udhi,:messagecoding,:messagetype,:messagelength,:messagecontent,:reserve,:usernumber,:submittime,:status,:result,:insertdate)';                            
        dbms_sql.parse(cid2,insertsql,dbms_sql.native);
        loop
        if dbms_sql.fetch_rows(cid) > 0 then
         begin
          dbms_sql.column_value(cid, 1, p_mtsequence1);
          dbms_sql.column_value(cid, 2, p_mtsequence2);
          dbms_sql.column_value(cid, 3, p_mtsequence3);
          dbms_sql.column_value(cid, 4, eid);   
          dbms_sql.column_value(cid, 5, spnumber);   
          dbms_sql.column_value(cid, 6, chargenumber);   
          dbms_sql.column_value(cid, 7, corpid);   
          dbms_sql.column_value(cid, 8, servicetype);   
          dbms_sql.column_value(cid, 9, feetype);   
          dbms_sql.column_value(cid, 10, feevalue);   
          dbms_sql.column_value(cid, 11, givenvalue);   
          dbms_sql.column_value(cid, 12, agentflag);   
          dbms_sql.column_value(cid, 13, morelatetomtflag);   
          dbms_sql.column_value(cid, 14, priority);   
          dbms_sql.column_value(cid, 15, exprietime);   
          dbms_sql.column_value(cid, 16, scheduletime);   
          dbms_sql.column_value(cid, 17, reportflag);   
          dbms_sql.column_value(cid, 18, tp_pid);   
          dbms_sql.column_value(cid, 19, tp_udhi);   
          dbms_sql.column_value(cid, 20, messagecoding);   
          dbms_sql.column_value(cid, 21, messagetype);   
          dbms_sql.column_value(cid, 22, messagelength);   
          dbms_sql.column_value(cid, 23, messagecontent);   
          dbms_sql.column_value(cid, 24, submittime);   
          dbms_sql.column_value(cid, 25, result);   
          dbms_sql.column_value(cid, 26, insertdate);      
          dbms_sql.column_value(cid, 27, usernumber);         
          dbms_sql.column_value(cid, 28, reserve);         
                   
          dbms_sql.bind_variable(cid2,'p_mtsequence1',p_mtsequence1);
          dbms_sql.bind_variable(cid2,'p_mtsequence2',p_mtsequence2);    
          dbms_sql.bind_variable(cid2,'p_mtsequence3',p_mtsequence3);   
          dbms_sql.bind_variable(cid2,'eid',eid);   
          dbms_sql.bind_variable(cid2,'spnumber',spnumber);               
          dbms_sql.bind_variable(cid2,'chargenumber',chargenumber);               
          dbms_sql.bind_variable(cid2,'corpid',corpid);               
          dbms_sql.bind_variable(cid2,'servicetype',servicetype);               
          dbms_sql.bind_variable(cid2,'feetype',feetype);               
          dbms_sql.bind_variable(cid2,'feevalue',feevalue);               
          dbms_sql.bind_variable(cid2,'givenvalue',givenvalue);               
          dbms_sql.bind_variable(cid2,'agentflag',agentflag);             
          dbms_sql.bind_variable(cid2,'morelatetomtflag',morelatetomtflag);               
          dbms_sql.bind_variable(cid2,'priority',priority);               
          dbms_sql.bind_variable(cid2,'exprietime',exprietime);   
          dbms_sql.bind_variable(cid2,'scheduletime',scheduletime);               
          dbms_sql.bind_variable(cid2,'reportflag',reportflag);               
          dbms_sql.bind_variable(cid2,'tp_pid',tp_pid);               
          dbms_sql.bind_variable(cid2,'tp_udhi',tp_udhi);               
          dbms_sql.bind_variable(cid2,'messagecoding',messagecoding);   
          dbms_sql.bind_variable(cid2,'messagetype',messagetype);          
          dbms_sql.bind_variable(cid2,'messagelength',messagelength);               
          dbms_sql.bind_variable(cid2,'messagecontent',messagecontent);               
          dbms_sql.bind_variable(cid2,'reserve',reserve);               
          dbms_sql.bind_variable(cid2,'usernumber',usernumber);               
          dbms_sql.bind_variable(cid2,'submittime',submittime);      
          status:=0;         
          dbms_sql.bind_variable(cid2,'status',status);                       
          dbms_sql.bind_variable(cid2,'result',result);                          
          dbms_sql.bind_variable(cid2,'insertdate',insertdate);        
                
          
          rows_processed:=dbms_sql.execute(cid2);
                    
          delete_commit('delete from smssubmit a where a.mtsequence1='||p_mtsequence1||' and a.mtsequence2='||p_mtsequence2||' and a.mtsequence3='||p_mtsequence3);
          delete_commit('delete from smssubmitresp a where a.mtsequence1='||p_mtsequence1||' and a.mtsequence2='||p_mtsequence2||' and a.mtsequence3='||p_mtsequence3);
          row_count:=row_count+1;
          if row_count=1000 then
            begin
              commit;
              row_count:=0;
            end;
          end if;
          end;
        else
           begin
           commit;
           exit;
           end;
         end if;
        end loop;
        end;
       exception
      when others then
      errpsn:=dbms_sql.last_error_position;
      sqlfcd:=dbms_sql.last_sql_function_code;
      lrowid:=dbms_sql.last_row_id;
      errc:=SQLCODE;
      errm:=sqlerrm;
      dbms_output.put_line('Error '||to_char(errc)||' Posn '||to_char(errpsn)||' SQL fCode '||to_char(sqlfcd)||' rowid '||rowidtochar(lrowid)||nofrows);
      raise_application_error(-20000,errm);
   begin
        dbms_sql.close_cursor(cid2);
        dbms_sql.close_cursor(cid);     exception 
         when others then 
      errpsn:=dbms_sql.last_error_position;    
      sqlfcd:=dbms_sql.last_sql_function_code;
      lrowid:=dbms_sql.last_row_id;
      errc:=SQLCODE;
      errm:=sqlerrm;
      dbms_output.put_line('Error '||to_char(errc)||' Posn '||to_char(errpsn)||' SQL fCode '||to_char(sqlfcd)||' rowid '||rowidtochar(lrowid));
      raise_application_error(-20000,errm);
      end;   
 
end Pro_match_submit;

Answer 2

1 按照正常流程即程序没有异常，你打开的游标是没有关闭的。所以会出现运行一段时间后报错的情况，内存都被耗光了！
2 在end loop;之后，end 之前 应该加上这两句话。
dbms_sql.close_cursor(cid2);
dbms_sql.close_cursor(cid);

Answer 3

你的procedure中begin太多，估计你原来是用sql server的吧，procedure中只要一个begin end对就可以了，begin end太多可能会造成打开很多隐含游标sql

Answer 4

Lastdrop(空杯) 兄,我从来没有用过sql server,但是你说的很对,肯定是其中的隐式游标我没有关闭,但是问题就是,游标在哪里,我不知道,要是知道就可以解决了,所以才想借助各位啊!
 hushuangyang(hushuangyang) 兄,你说的很对,我10秒钟前把问题锁定在这里,然后看到你的答复,然后看看,确实我是以为关闭了游标的,实际上是出异常后才关闭的:(
按你的修改了,问题暂时好像解决了,结账,分数都给你,谢谢;)