Use of the middle stream of the Oracle stored procedure (including cursors with parameters)

Recently because the project needs to write stored procedures, has not been written before, contact is contact with, in the soft through the time contact, it is Huawei's project that a few stored procedures are very complex, but also very messy, commented also less, read a general. The last one months, before and after also written seven or eight simple points stored procedures, but also a simple understanding of the stored procedures, in fact, it is not difficult, more information, more practice. Gossip does not pull, the following mainly said a combination of downstream of the use, recorded even after a long time not to forget.

Scene: There are several tables, now to deal with the data in table B according to business requirements, but first of all to do a standard to deal with it is it, this standard in table A, the standard in table A is not a, now need to take a table of the standard B table data are processed on one side. This is business.

Thinking: First to install a table to deal with, is not to cycle a table in the data to deal with that. To be sure, a cursor should be first to control the A-table loop. The next step is to analyze the data in table B according to the criteria of table A. That according to Java understanding should be in the loop of a table to deal with the data of table B, in fact, here is the same, it also needs a cursor to control the entire layer of the loop, to this will consider how to nested cursor loop, you can Baidu " Use of Oracle nested Cursors "Look, this does not record those, because the business needs B table data volume is relatively large, a table of a standard is only a part of the B table data, that is to say, but when nested loops also need a table in the record of some field information. This nesting time to consider a little more, and finally I chose to use a cursor with parameters to implement, the need for a table parameter passed into B table.

Specific examples:

Create or replace procedure P_sbzl_bldy is--Define variable v_xlbh SP_DATA_TQI.
     Xlbh%type; V_XINGB Sp_data_tqi.
     Xingb%type;
     V_QSLC Sp_data_tqi.tqilc%type;
    
     V_ZZLC Sp_data_tqi.tqilc%type;
     --Defines a "standard" cursor CURSOR Bzs is SELECT * from Sp_dic_bldystandard b; The cursor CURSOR SJS (vxlbh varchar2,vxingb VARCHAR2,VQSLC number) is select NUMBER,VZZLC that defines the specific data to be processed--- M,t.xingb,t.xingbmc,t.tqisum,t.tqilc,t.dyid,p.rpcgs,p.ypcgs,p.cpcgs from Sp_data_tqi T-left join SP_SBZL_PCGS p on T.dyid = P.dyid where T.tqilc > Vqslc and T.TQILC<VZZLC and t.xlbh = vxlbh and t.xingb = VXINGB and P.JCN
              

y = To_char (sysdate, ' yyyy-mm ') and To_char (T.jcrq, ' yyyy-mm ') = To_char (sysdate, ' yyyy-mm ');
  Begin-Clears the month data delete sp_sbzl_bldyxx t where T.scny = To_char (sysdate, ' yyyy-mm ');
  
  Commit
       --loop standard for C in Bzs loop begin v_xlbh: = C.XLBH;
       V_XINGB: = C.XINGB;
       V_QSLC: = C.QSLC;
 V_ZZLC: = C.ZZLC;      
       --Cycle specific data pass the relevant parameters into the for BHS in SjS (V_XLBH,V_XINGB,V_QSLC,V_ZZLC) loop begin --one level if Bhs.tqisum<c.tqii and (Bhs.ypcgs<c.cari) and (BHS.CPCGS&LT;C.CZYPCI) and (BHS.RPCGS&LT;C.TCYP CI) then insert into SP_SBZL_BLDYXX values (S_COMM_PK. Nextval,bhs.xlbh,bhs.xlm,bhs.xingb,bhs.xingbmc,bhs.tqilc,bhs.tqisum,bhs.dyid,to_char (sysdate, ' yyyy-mm '), ' Ⅰ ',
             C.id);
           Commit --Level 2 elsif bhs.tqisum>c.tqiii or (Bhs.ypcgs>c.carii) or (BHS.CPCGS&GT;C.CZYPCII) or (BHS.RPCGS&GT;C.TCYP 
                CII) then--dbms_output.put_line (' 2 '); INSERT into SP_SBZL_BLDYXX values (S_COMM_PK. Nextval,bhs.xlbh,bhs.xlm,bhs.xingb,bhs.xingbmc,bhs.tqilc,bhs.tqisum,bhs.dyid,to_char (sysdate, ' yyyy-mm '), ' Ⅱ ',
               C.id);
           Commit --Level 3 elsif (BHS.YPCGS&GT;C.CARIII1) and (BHS.YPCGS&GT;C.PCIII1)) or (Bhs.ypcgs>c.carii I2) and (bhs.tqisum>c.tqiiII1)) or (Bhs.tqisum>c.tqiiii2 and (Bhs.cpcgs>c.pciii2 or BHS.RPCGS&GT;C.PCIII2)) or (BHS.YPCGS&GT;C.CARIII3) then insert into SP_SBZL_BLDYXX values (S_COMM_PK. Nextval,bhs.xlbh,bhs.xlm,bhs.xingb,bhs.xingbmc,bhs.tqilc,bhs.tqisum,bhs.dyid,to_char (sysdate, ' yyyy-mm '), ' Ⅲ ',
               C.id);
               
            Commit --Level 4 elsif (Bhs.tqisum>c.tqiiv and (BHS.YPCGS&GT;C.CARIV1) and (Bhs.cpcgs > C.czypciv) or (Bhs.rpcgs & Gt C.TCYPCIV)), or (BHS.YPCGS&GT;C.CARIV2) then insert into SP_SBZL_BLDYXX values (S_comm _PK. Nextval,bhs.xlbh,bhs.xlm,bhs.xingb,bhs.xingbmc,bhs.tqilc,bhs.tqisum,bhs.dyid,to_char (sysdate, ' yyyy-mm '), ' Ⅳ ',
               C.id);   
            Commit
           
           
           
          End If;
          End
      End Loop;
  End
  
End Loop;
  /* Open CS;
       Loop FETCH CS into REC_TEST2.QSLC, cur_test1; Exit when CS%notfound; 
    Dbms_output.put_line (REC_TEST2.QSLC);
         Loop fetch cur_test1 into rec_test1;
       Exit when Cur_test1%notfound; 
    Dbms_output.put_line (REC_TEST1.TQILC);
  End Loop;
    End Loop; //*for C in CS loop BEGIN cursor ZS are select * from Sp_data_tqi t left join Sp_sbzl_pcgs p on t
          
          . Dyid = P.dyid where T.tqilc > C.QSLC and t.tqilc<c.zzlc; For s in ZS Loop begin if S.tqiz<c.tqii then-I. dbms_output. 
              Put_Line (' 1 '); 
            elsif s.tqiz > C.tqii then Dbms_output.put_line (' 2 ');  
    End
  End; End Loop;*/End P_sbzl_bldy;

Some specific wording of the cursor I do not record, the main purpose here is to encounter this kind of need can be implemented in this way.