oracle 中存储过程一些小应用

1.for ，loop ，while 的应用

/*------------------------------for----------------------------------------------*/
  procedure TEST_FOR is
    N_VALUE        integer;
    N_VALUE_RESULT integer;
  begin
    ---- 1..10之和
    N_VALUE_RESULT := 0;
    for N_VALUE in 1 .. 10 loop
      select N_VALUE + N_VALUE_RESULT into N_VALUE_RESULT from dual;
    end loop;
    pbidebug.UP_SQLSTATEMENT(N_VALUE_RESULT); --打印记录
  end TEST_FOR;
/*---------------------------------------LOOP---------------------------------------------*/
  procedure TEST_LOOP is
    N_VALUE        integer;
    N_VALUE_RESULT integer;
  begin
  
    N_VALUE_RESULT := 0;
    N_VALUE        := 0;
    loop
    
      select N_VALUE + N_VALUE_RESULT into N_VALUE_RESULT from dual;
      N_VALUE := N_VALUE + 1;
    
      if N_VALUE > 10 then
        exit;
      end if;
    
    end loop;
    pbidebug.UP_SQLSTATEMENT(N_VALUE_RESULT); --打印记录
  end TEST_LOOP;
/*----------------------------while----------------------------------------------*/
  procedure TEST_WHILE is
    N_VALUE        integer;
    N_VALUE_RESULT integer;
  begin
  
    N_VALUE_RESULT := 0;
    N_VALUE        := 0;
    while N_VALUE <= 10 loop
    
      select N_VALUE + N_VALUE_RESULT into N_VALUE_RESULT from dual;
      N_VALUE := N_VALUE + 1;
    
    end loop;
    pbidebug.UP_SQLSTATEMENT(N_VALUE_RESULT); --打印记录
  end TEST_WHILE;
/*--------------------------------for 逐行数据处理------------------------------------------*/

  procedure TEST_FOR_1 is
    V_STRTEMP varchar2(30000);
  begin
    
    for www in (select varno from tam_unitinfo) loop
      V_STRTEMP := V_STRTEMP || www.varno ||';';
    end loop;
    pbidebug.UP_SQLSTATEMENT(V_STRTEMP); --打印记录
  end TEST_FOR_1;
/*-------------------------------------------------------------------------------------------*/

2.insert into 使用

 /*----------------------------------insert into----------------------------------------------*/

  procedure TEST_INSETINTO_1 is
    V_STRSQL       VARCHAR2(30000);
    STRSQLINSERT1  VARCHAR2(3000);
    STRSQLINSERT2  VARCHAR2(3000);
    STRSQLINSERT3  VARCHAR2(3000);
    STRSQLINSERT4  VARCHAR2(3000);
    STRSQLINSERT5  VARCHAR2(3000);
    STRSQLINSERT6  VARCHAR2(3000);
    STRSQLINSERT7  VARCHAR2(3000);
    STRSQLINSERT8  VARCHAR2(3000);
    STRSQLINSERT9  VARCHAR2(3000);
    STRSQLINSERT10 VARCHAR2(3000);
    STRSQLINSERT11 VARCHAR2(3000);
  begin
    ---select 为空报错
    V_STRSQL := ' insert into TGL_4AVIEW_DETAILS_FOR4A ' ||
                ' (VARNO, vardescription, PERIODNAME, dtaccountdate, costpoolvarno, costpooltvarname, bank, deptvarno, deptvarname, varabstract, money) ' ||
                ' select ww.varno,ww.vardescription, ww.varname ,ww.dtaccountdate,acc4a.costpoolvarno,acc4a.costpooltvarname, ' ||
                ' cost4a.deptvarno ,cost4a.deptvarname ,ww.varabstract,ww.money from XXXXX';
    EXECUTE IMMEDIATE V_STRSQL;
  
    ---select 为空不报错           
    V_STRSQL := ' insert into TGL_4AVIEW_DETAILS_FOR4A ' ||
               --  ' (VARNO, vardescription, PERIODNAME, dtaccountdate, costpoolvarno, costpooltvarname, bank, deptvarno, deptvarname, varabstract, money) '||
                ' select ww.varno,ww.vardescription, ww.varname ,ww.dtaccountdate,acc4a.costpoolvarno,acc4a.costpooltvarname, ' ||
                ' cost4a.deptvarno ,cost4a.deptvarname ,ww.varabstract,ww.money from XXXXX';
    EXECUTE IMMEDIATE V_STRSQL;
  
    ----insert into表从外部传递变量           
    V_STRSQL := ' INSERT INTO TBI_DEBUG_SQL(SQL1,SQL2,SQL3,SQL4,SQL5,SQL6,SQL7,SQL8,SQL9,SQL10,SQL11,LOGDATE)' ||
                ' VALUES(:V1,:V2,:V3,:V4,:V5,:V6,:V7,:V8,:V9,:V10,:V11,:V12)';
    EXECUTE IMMEDIATE V_STRSQL
      USING STRSQLINSERT1, STRSQLINSERT2, STRSQLINSERT3, STRSQLINSERT4, STRSQLINSERT5, STRSQLINSERT6, STRSQLINSERT7, STRSQLINSERT8, STRSQLINSERT9, STRSQLINSERT10, STRSQLINSERT11, SYSDATE();
  
    ----insert into表从外部传递变量                 
    -----(1)
    V_STRSQL := ' insert into aaa_zw  (varno1, varno2, varno3, varno4, varno5) ' ||
                '  values (101, 1, 2, 3, 4);';
    EXECUTE IMMEDIATE V_STRSQL;
    -----(2)
    V_STRSQL := ' insert into aaa_zw  (varno1, varno2, varno3) ' ||
                '  values (102, 1, 2);';
    EXECUTE IMMEDIATE V_STRSQL;
    -----(3)
    V_STRSQL := ' insert into aaa_zw  (varno1, varno2, varno4) ' ||
                '  values (103, 1, 2);';
    EXECUTE IMMEDIATE V_STRSQL;
  
  end TEST_INSETINTO_1;