oracle基础积累-update

场景:
        常见更新操作.
环境:
       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_INFO IN (SELECT C.SENSOR_ID, C.REGION, A.S1 AS S1, B.S1 AS S2
                       FROM SENSOR_COLLECT_DATA C,
                            SENSOR_COLLECT_A    A,
                            SENSOR_COLLECT_B    B
                      WHERE C.SENSOR_ID = A.SENSOR_ID
                        AND A.SENSOR_ID = B.SENSOR_ID
                        AND C.REGION = A.REGION
                        AND A.REGION = B.REGION) LOOP
      IF TMP_INFO.S1 IS NOT NULL THEN
        UPDATE SENSOR_COLLECT_DATA
           SET S1 = TMP_INFO.S1
         WHERE SENSOR_ID = TMP_INFO.SENSOR_ID
           AND REGION = TMP_INFO.REGION;
      END IF;
      IF TMP_INFO.S2 IS NOT NULL THEN
        UPDATE SENSOR_COLLECT_DATA
           SET S2 = TMP_INFO.S2
         WHERE SENSOR_ID = TMP_INFO.SENSOR_ID
           AND REGION = TMP_INFO.REGION;
      END IF;
    END LOOP;
    COMMIT;
  END;
EXCEPTION
  WHEN OTHERS THEN
    ERRORMSG := 'PRO_TEST_CURSOR_FOR2抛出异常: ' || SQLERRM;
END PRO_TEST_CURSOR_FOR2;

2.查出更新(UPDATE)

UPDATE SENSOR_COLLECT_DATA CC
SET CC.S1 = (SELECT AA.S1 FROM SENSOR_COLLECT_A AA WHERE  CC.SENSOR_ID = AA.SENSOR_ID),
    CC.S2 = (SELECT BB.S1 FROM SENSOR_COLLECT_B BB WHERE  CC.SENSOR_ID = BB.SENSOR_ID) ;

3.存在更新

UPDATE SENSOR_COLLECT_DATA CC
SET CC.S1 = 99.88
   WHERE EXISTS (SELECT 1 FROM SENSOR_COLLECT_A AA WHERE CC.SENSOR_ID = AA.SENSOR_ID);

4.更新

UPDATE SENSOR_COLLECT_DATA CC
SET CC.S1 = 12.88,
    CC.S2 = 22.88
WHERE CC.SENSOR_ID = 20191401;

5.附建表语句1

create table SENSOR_COLLECT_DATA
(
  sensor_id         NUMBER(16) 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.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';

6.附建表语句2

create table SENSOR_COLLECT_A
(
  sensor_id         NUMBER(16) not null,
  region          VARCHAR2(16) not null,
  s1       NUMBER(6,3)        
);
comment on table SENSOR_COLLECT_A
  is '传感器采集数据';
comment on column SENSOR_COLLECT_A.sensor_id
  is '数据id实体唯一标识';
comment on column SENSOR_COLLECT_A.region
  is '传感器安装区域';
comment on column SENSOR_COLLECT_A.s1
  is '传感器采集的值1';

7.附建表语句3

create table SENSOR_COLLECT_B
(
  sensor_id         NUMBER(16) not null,
  region          VARCHAR2(16) not null,
  s1       NUMBER(6,3)       
);
comment on table SENSOR_COLLECT_B
  is '传感器采集数据';
comment on column SENSOR_COLLECT_B.sensor_id
  is '数据id实体唯一标识';
comment on column SENSOR_COLLECT_B.region
  is '传感器安装区域';
comment on column SENSOR_COLLECT_B.s1
  is '传感器采集的值1';

以上,感谢.