Oracle数据库Loop循环实例（记录）

最新推荐文章于 2023-06-02 23:01:51 发布

Particle丶

最新推荐文章于 2023-06-02 23:01:51 发布

阅读量4.3k

点赞数

分类专栏：数据库文章标签： Oracle Loop

本文链接：https://blog.youkuaiyun.com/m0_43427766/article/details/84633326

版权

本文档记录了一个Oracle数据库的Loop循环实例，用于解决一个涉及多表连接和大量列导出为CSV的需求。原本的控制台程序处理时间过长，后经DBA建议，在数据库包内使用Loop进行数据处理，显著提高了效率，执行时间缩短至10分钟左右。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

需求分析

大概需求就是如下流程图，完成后需要连接另外B表、C表，连接后超过2K列，导出为.CSV文档到固定位置。（逻辑感觉很乱，数据也是查相近，但就是客户这样需求）
大概逻辑

解决方案

首先是控制台程序处理，完成后发现处理时间超过12h，就请教了DBA，DBA说程序不断循环与数据库交互会很影响时间，然后选择在数据库包里做数据处理，通过程序执行，10分钟左右执行完成。

记录

代码过长，只做一个记录，只为以后遇到能有个参考：Loop将表当做程序中的Datatable来循环使用。

// An highlighted block
CREATE OR REPLACE PROCEDURE PTSMGR.UPD_PANDA_DATA_AABCLOGIC_MAIN(COND_MACHINE VARCHAR2,DCURRENT_DATE in varchar2) IS
   M_COUNT      INTEGER;

   
   START_TIME    VARCHAR2 (2000);
   END_TIME    VARCHAR2 (2000);
   END_DTIME    VARCHAR2 (2000);
   
   START_TIMEKEY    VARCHAR2 (2000);
   END_TIMEKEY    VARCHAR2 (2000);
   
   --錯誤記录
   RETURN_CODE NUMBER;
   RETURN_SQL  VARCHAR2(200);
   V_TIMEKEY   VARCHAR2(20);
   MODELNAMEI  VARCHAR2(200);
 

BEGIN
 
    --日期
    select to_char( (TO_DATE(DCURRENT_DATE,'yyyy-mm-dd')) -2,'yyyy-mm-dd')||' 00:00:00' into START_TIME  from dual;
    select to_char( (TO_DATE(DCURRENT_DATE,'yyyy-mm-dd')),'yyyy-mm-dd')||' 00:00:00' into END_DTIME  from dual;
    select to_char( (TO_DATE(DCURRENT_DATE,'yyyy-mm-dd')),'yyyy-mm-dd')||' 23:59:59' into END_TIME  from dual;
    --timekey
    select to_char( (TO_DATE(DCURRENT_DATE,'yyyy-mm-dd')) -2,'yyyymmdd')||'000000000000' into START_TIMEKEY  from dual;
    select to_char( (TO_DATE(DCURRENT_DATE,'yyyy-mm-dd')),'yyyymmdd')||'235959000000' into END_TIMEKEY  from dual;
    
    
    IF COND_MACHINE='X1047' THEN
    
    --清空表
    DELETE X1047_DATA_AABCLOGIC_MAIN;
    COMMIT;
    --導入三天歷史數据
     insert into X1047_DATA_AABCLOGIC_MAIN(G63_EVEN_FLICKER24HZ_P9,G63_EVEN_MAXFLICKER_P1,G63_EVEN_MAXFREQUENCY_P1,G63_EVEN_MAXFLICKER_P3,G63_EVEN_MAXFREQUENCY_P3,G63_EVEN_MAXFLICKER_P5,G63_EVEN_MAXFREQUENCY_P5,G63_EVEN_MAXFLICKER_P7,G63_EVEN_MAXFREQUENCY_P7,G63_EVEN_MAXFLICKER_P9,G63_EVEN_MAXFREQUENCY_P9,I2C_DATA_COMPARE_RESULT,G63_FLICKER39HZ_P1,G63_FLICKER39HZ_P3,G63_FLICKER39HZ_P5,G63_FLICKER39HZ_P7,G63_FLICKER39HZ_P9,G63_FLICKER40HZ_P1,G63_FLICKER40HZ_P3,G63_FLICKER40HZ_P5,G63_FLICKER40HZ_P7,G63_FLICKER40HZ_P9,G63_FLICKER41HZ_P1,G63_FLICKER41HZ_P3,G63_FLICKER41HZ_P5,G63_FLICKER41HZ_P7,G63_FLICKER41HZ_P9,G63_FLICKER42HZ_P1,G63_FLICKER42HZ_P3,G63_FLICKER42HZ_P5,G63_FLICKER42HZ_P7,G63_FLICKER42HZ_P9,G63_FLICKER43HZ_P1,G63_FLICKER43HZ_P3,G63_FLICKER43HZ_P5,G63_FLICKER43HZ_P7,G63_FLICKER43HZ_P9,G63_FLICKER44HZ_P1,G63_FLICKER44HZ_P3,G63_FLICKER44HZ_P5,G63_FLICKER44HZ_P7,G63_FLICKER44HZ_P9,G63_FLICKER45HZ_P1,G63_FLICKER45HZ_P3,G63_FLICKER45HZ_P5,G63_FLICKER45HZ_P7,G63_FLICKER45HZ_P9,G63_FLICKER46HZ_P1,G63_FLICKER46HZ_P3,G63_FLICKER46HZ_P5,G63_FLICKER46HZ_P7,G63_FLICKER46HZ_P9,G63_FLICKER47HZ_P1,G63_FLICKER47HZ_P3,G63_FLICKER47HZ_P5,G63_FLICKER47HZ_P7,G63_FLICKER47HZ_P9,G63_FLICKER48HZ_P1,G63_FLICKER48HZ_P3,G63_FLICKER48HZ_P5,G63_FLICKER48HZ_P7,G63_FLICKER48HZ_P9,G63_FLICKER49HZ_P1,G63_FLICKER49HZ_P3,G63_FLICKER49HZ_P5,G63_FLICKER49HZ_P7,G63_FLICKER49HZ_P9,G63_FLICKER50HZ_P1,G63_FLICKER50HZ_P3,G63_FLICKER50HZ_P5,G63_FLICKER50HZ_P7,G63_FLICKER50HZ_P9,G63_FLICKER51HZ_P1,G63_FLICKER51HZ_P3,G63_FLICKER51HZ_P5,G63_FLICKER51HZ_P7,G63_FLICKER51HZ_P9,G63_FLICKER52HZ_P1,G63_FLICKER52HZ_P3,G63_FLICKER52HZ_P5,G63_FLICKER52HZ_P7,G63_FLICKER52HZ_P9,G63_FLICKER53HZ_P1,G63_FLICKER53HZ_P3,G63_FLICKER53HZ_P5,G63_FLICKER53HZ_P7,G63_FLICKER53HZ_P9,G63_FLICKER54HZ_P1,G63_FLICKER54HZ_P3,G63_FLICKER54HZ_P5,G63_FLICKER54HZ_P7,G63_FLICKER54HZ_P9,G63_FLICKER55HZ_P1,G63_FLICKER55HZ_P3,G63_FLICKER55HZ_P5,G63_FLICKER55HZ_P7,G63_FLICKER55HZ_P9,G63_FLICKER56HZ_P1,G63_FLICKER56HZ_P3,G63_FLICKER56HZ_P5,G63_FLICKER56HZ_P7,G63_FLICKER56HZ_P9,G63_FLICKER57HZ_P1,G63_FLICKER57HZ_P3,G63_FLICKER57HZ_P5,G63_FLICKER57HZ_P7,G63_FLICKER57HZ_P9,G63_FLICKER58HZ_P1,G63_FLICKER58HZ_P3,G63_FLICKER58HZ_P5,G63_FLICKER58HZ_P7,G63_FLICKER58HZ_P9,G63_FLICKER59HZ_P1,G63_FLICKER59HZ_P3,G63_FLICKER59HZ_P5,G63_FLICKER59HZ_P7,G63_FLICKER59HZ_P9,G63_FLICKER60HZ_P1,G63_FLICKER60HZ_P3,G63_FLICKER60HZ_P5,G63_FLICKER60HZ_P7,G63_FLICKER60HZ_P9,G63_FLICKER61HZ_P1,G63_FLICKER61HZ_P3,G63_FLICKER61HZ_P5,G63_FLICKER61HZ_P7,G63_FLICKER61HZ_P9,G63_FLICKER62HZ_P1,G63_FLICKER62HZ_P3,G63_FLICKER62HZ_P5,G63_FLICKER62HZ_P7,G63_FLICKER62HZ_P9,G63_FLICKER63HZ_P1,G63_FLICKER63HZ_P3,G63_FLICKER63HZ_P5,G63_FLICKER63HZ_P7,G63_FLICKER63HZ_P9,G63_FLICKER64HZ_P1,G63_FLICKER64HZ_P3,G63_FLICKER64HZ_P5,G63_FLICKER64HZ_P7,G63_FLICKER64HZ_P9,G63_MAXFLICKER_P1,G63_MAXFREQUENCY_P1,G63_MAXFLICKER_P3,G63_MAXFREQUENCY_P3,G63_MAXFLICKER_P5,G63_MAXFREQUENCY_P5,G63_MAXFLICKER_P7,G63_MAXFREQUENCY_P7,G63_MAXFLICKER_P9,G63_MAXFREQUENCY_P9,G127_ODD_FLICKER12HZ_P1,G127_ODD_FLICKER12HZ_P3,G127_ODD_FLICKER12HZ_P5,G127_ODD_FLICKER12HZ_P7,G127_ODD_FLICKER12HZ_P9,G127_ODD_FLICKER24HZ_P1,G127_ODD_FLICKER24HZ_P3,G127_ODD_FLICKER24HZ_P5,G127_ODD_FLICKER24HZ_P7,G127_ODD_FLICKER24HZ_P9,G127_ODD_MAXFLICKER_P1,G127_ODD_MAXFREQUENCY_P1,G127_ODD_MAXFLICKER_P3,G127_ODD_MAXFREQUENCY_P3,G127_ODD_MAXFLICKER_P5,G127_ODD_MAXFREQUENCY_P5,G127_ODD_MAXFLICKER_P7,G127_ODD_MAXFREQUENCY_P7,G127_ODD_MAXFLICKER_P9,G127_ODD_MAXFREQUENCY_P9,G127_EVEN_FLICKER12HZ_P1,G127_EVEN_FLICKER12HZ_P3,G127_EVEN_FLICKER12HZ_P5,G127_EVEN_FLICKER12HZ_P7,G127_EVEN_FLICKER12HZ_P9,G127_EVEN_FLICKER24HZ_P1,G127_EVEN_FLICKER24HZ_P3,G127_EVEN_FLICKER24HZ_P5,G127_EVEN_FLICKER24HZ_P7,G127_EVEN_FLICKER24HZ_P9,G127_EVEN_MAXFLICKER_P1,G127_EVEN_MAXFREQUENCY_P1,G127_EVEN_MAXFLICKER_P3,G127_EVEN_MAXFREQUENCY_P3,G127_EVEN_MAXFLICKER_P5,G127_EVEN_MAXFREQUENCY_P5,G127_EVEN_MAXFLICKER_P7,G127_EVEN_MAXFREQUENCY_P7,G127_EVEN_MAXFLICKER_P9,G127_EVEN_MAXFREQUENCY_P9,G63_ODD_FLICKER12HZ_P1,G63_ODD_FLICKER12HZ_P3,G63_ODD_FLICKER12HZ_P5,G63_ODD_FLICKER12HZ_P7,G63_ODD_FLICKER12HZ_P9,G63_ODD_FLICKER24HZ_P1,G63_ODD_FLICKER24HZ_P3,G63_ODD_FLICKER24HZ_P5,G63_ODD_FLICKER24HZ_P7,G63_ODD_FLICKER24HZ_P9,G63_ODD_MAXFLICKER_P1,G63_ODD_MAXFREQUENCY_P1,G63_ODD_MAXFLICKER_P3,G63_ODD_MAXFREQUENCY_P3,G63_ODD_MAXFLICKER_P5,G63_ODD_MAXFREQUENCY_P5,G63_ODD_MAXFLICKER_P7,G63_ODD_MAXFREQUENCY_P7,G63_ODD_MAXFLICKER_P9,G63_ODD_MAXFREQUENCY_P9,G63_EVEN_FLICKER12HZ_P1,G63_EVEN_FLICKER12HZ_P3,G63_EVEN_FLICKER12HZ_P5,G63_EVEN_FLICKER12HZ_P7,G63_EVEN_FLICKER12HZ_P9,G63_EVEN_FLICKER24HZ_P1,G63_EVEN_FLICKER24HZ_P3,G63_EVEN_FLICKER24HZ_P5,G63_EVEN_FLICKER24HZ_P7,G127_FLICKER57HZ_P5,G127_FLICKER57HZ_P7,G127_FLICKER57HZ_P9,G127_FLICKER58HZ_P1,G127_FLICKER58HZ_P3,G127_FLICKER58HZ_P5,G127_FLICKER58HZ_P7,G127_FLICKER58HZ_P9,G127_FLICKER59HZ_P1,G127_FLICKER59HZ_P3,G127_FLICKER59HZ_P5,G127_FLICKER59HZ_P7,G127_FLICKER59HZ_P9,G127_FLICKER60HZ_P1,G127_FLICKER60HZ_P3,G127_FLICKER60HZ_P5,G127_FLICKER60HZ_P7,G127_FLICKER60HZ_P9,G127_FLICKER61HZ_P1,G127_FLICKER61HZ_P3,G127_FLICKER61HZ_P5,G127_FLICKER61HZ_P7,G127_FLICKER61HZ_P9,G127_FLICKER62HZ_P1,G127_FLICKER62HZ_P3,G127_FLICKER62HZ_P5,G127_FLICKER62HZ_P7,G127_FLICKER62HZ_P9,G127_FLICKER63HZ_P1,G127_FLICKER63HZ_P3,G127_FLICKER63HZ_P5,G127_FLICKER63HZ_P7,G127_FLICKER63HZ_P9,G127_FLICKER64HZ_P1,G127_FLICKER64HZ_P3,G127_FLICKER64HZ_P5,G127_FLICKER64HZ_P7,G127_FLICKER64HZ_P9,

最低0.47元/天解锁文章