oracle基础积累-for循环中使用子查询

最新推荐文章于 2025-01-21 16:24:11 发布

原创最新推荐文章于 2025-01-21 16:24:11 发布 · 3.4k 阅读

4 ·

CC 4.0 BY-SA版权

文章标签：

#oracle基础积累-for循环中使用子查询

L01-数据库专栏收录该内容

41 篇文章

订阅专栏

本文介绍在Oracle Database 11g环境下，如何在PL/SQL中利用for循环结合子查询进行数据处理。通过一个具体示例，展示了从两个不同类型的传感器数据表中提取并整合温度与湿度数据，最终插入到详细数据表的过程。

场景:
for循环中使用子查询
环境:
Oracle Database 11g; PL/SQL Developer
1.for循环中使用子查询存储过程

CREATE OR REPLACE PROCEDURE PRO_TEST_CURSOR_FOR2(ERRORMSG OUT VARCHAR2) IS
BEGIN
  BEGIN
    ERRORMSG := '';
    FOR TMP IN (SELECT WENDU.SENSOR_ID,
                       WENDU.DATA_DATE,
                       WENDU.REGION,
                       DECODE(WENDU.S1, NULL, 0, WENDU.S1) AS WENDU1,
                       DECODE(WENDU.S2, NULL, 0, WENDU.S2) AS WENDU2,
                       DECODE(WENDU.S3, NULL, 0, WENDU.S3) AS WENDU3,
                       DECODE(SHIDU.S1, NULL, 0, SHIDU.S1) AS SHIDU1,
                       DECODE(SHIDU.S2, NULL, 0, SHIDU.S2) AS SHIDU2,
                       DECODE(SHIDU.S3, NULL, 0, SHIDU.S3) AS SHIDU3
                  FROM (SELECT * FROM SENSOR_COLLECT_DATA WHERE DATA_TYPE = 1) WENDU
                  LEFT JOIN (SELECT *FROM SENSOR_COLLECT_DATA WHERE DATA_TYPE = 2) SHIDU
                  ON WENDU.SENSOR_ID = SHIDU.SENSOR_ID
                  AND WENDU.DATA_DATE = SHIDU.DATA_DATE) LOOP
      INSERT INTO SENSOR_COLLECT_DATA_DETAIL
        (SENSOR_ID,DATA_DATE,REGION,WENDU1,WENDU2,WENDU3,SHIDU1,SHIDU2,SHIDU3)
      VALUES
        (TMP.SENSOR_ID,TMP.DATA_DATE,TMP.REGION,TMP.WENDU1,TMP.WENDU2,TMP.WENDU3,TMP.SHIDU1,TMP.SHIDU2,TMP.SHIDU3);
    END LOOP;
    COMMIT;
  END;
EXCEPTION
  WHEN OTHERS THEN
    ERRORMSG := 'PRO_TEST_CURSOR_FOR2抛出异常: ' || SQLERRM;
END PRO_TEST_CURSOR_FOR2;

2.原始数据
select * from SENSOR_COLLECT_DATA

3.执行后数据
select * from SENSOR_COLLECT_DATA_DETAIL

4.附建表语句1

create table SENSOR_COLLECT_DATA
(
  sensor_id         NUMBER(16) not null,
  data_date       DATE not null,
  data_type       NUMBER(2) not null,
  region          VARCHAR2(16) not null,
  s1       NUMBER(6,3),        
  s2       NUMBER(6,3),       
  s3       NUMBER(6,3)
);
comment on table SENSOR_COLLECT_DATA
  is '传感器采集数据';
comment on column SENSOR_COLLECT_DATA.sensor_id
  is '数据id实体唯一标识';
comment on column SENSOR_COLLECT_DATA.data_date
  is '数据日期';
comment on column SENSOR_COLLECT_DATA.data_type
  is '数据类型(1:温度、2:湿度)';
comment on column SENSOR_COLLECT_DATA.region
  is '传感器安装区域';
comment on column SENSOR_COLLECT_DATA.s1
  is '传感器采集的值1';
comment on column SENSOR_COLLECT_DATA.s2
  is '传感器采集的值2';
comment on column SENSOR_COLLECT_DATA.s3
  is '传感器采集的值3';

5.附建表语句2

create table SENSOR_COLLECT_DATA_DETAIL
(
  sensor_id    NUMBER(16) not null,
  data_date    DATE not null,
  region       VARCHAR2(16) not null,
  wendu1       NUMBER(6,3),        
  wendu2       NUMBER(6,3),       
  wendu3       NUMBER(6,3),
  shidu1       NUMBER(6,3),        
  shidu2       NUMBER(6,3),       
  shidu3       NUMBER(6,3)
);
comment on table SENSOR_COLLECT_DATA_DETAIL
  is '传感器采集数据';
comment on column SENSOR_COLLECT_DATA_DETAIL.sensor_id
  is '数据id实体唯一标识';
comment on column SENSOR_COLLECT_DATA_DETAIL.data_date
  is '数据日期';
comment on column SENSOR_COLLECT_DATA_DETAIL.region
  is '传感器安装区域';
comment on column SENSOR_COLLECT_DATA_DETAIL.wendu1
  is '传感器采集的温度值1';
comment on column SENSOR_COLLECT_DATA_DETAIL.wendu2
  is '传感器采集的温度值2';
comment on column SENSOR_COLLECT_DATA_DETAIL.wendu3
  is '传感器采集的温度值3';
comment on column SENSOR_COLLECT_DATA_DETAIL.shidu1
  is '传感器采集的湿度值1';
comment on column SENSOR_COLLECT_DATA_DETAIL.shidu2
  is '传感器采集的湿度值2';
comment on column SENSOR_COLLECT_DATA_DETAIL.shidu3
  is '传感器采集的湿度值3';

以上,感谢.