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;