时间戳

首页 > 解决方案 > SQL 循环按预期执行

plsql - SQL 循环按预期执行

问题描述

我正在尝试运行此代码，我知道循环的元素正在工作，并且我知道更新语句确实更新了。当我使用一些打印语句运行脚本时，它首先打印 UPDATE 信息，然后打印 LOOP 信息，因此更新没有信息。

PROCEDURE UpdateGridStats(p_grid_name VARCHAR2, p_region_name VARCHAR2) IS
    CURSOR c1 IS
    SELECT grd.globalid grid_globalid, wp.globalid workpoint_globalid, wp.feature_class_name workpoint_fcname,
            tt.work_order_task_type task_type_name
    FROM workorderpoint_evw wp, rpt_grid grd, workordertasktype_evw tt 
    WHERE grd.grid_name = p_grid_name
    AND wp.work_order_task_type_globalid = tt.globalid
    AND grd.rpt_region = p_region_name
    AND sde.st_relation_operators.st_within_f(wp.shape, grd.shape) = 1;
    v_count NUMBER := 0;
    v_pole_insp_count NUMBER := 0;
    v_pole_damage_count NUMBER := 0;
    v_cond_damage_count NUMBER := 0;
BEGIN
    FOR work_rec IN c1
    LOOP
      BEGIN
        v_count := v_count + 1;

        IF work_rec.task_type_name = 'Pole Inspection'
        THEN
            v_pole_insp_count := v_pole_insp_count + 1;
        END IF;
        IF work_rec.task_type_name = 'Pole Damage'
        THEN
            v_pole_damage_count := v_pole_damage_count + 1;
        END IF;        

      EXCEPTION
      WHEN NO_DATA_FOUND THEN
          dbms_output.put_line('==> No data found for work record '); 
      END;
    END LOOP;
    dbms_output.put_line(v_pole_damage_count || ',' ||v_pole_insp_count);
    UPDATE rpt_grid grd SET da_pole_count = v_pole_damage_count, ins_structure_count = v_pole_insp_count
    WHERE grd.grid_name = p_grid_name
    AND grd.rpt_region = p_region_name;
    

END UpdateGridStats;

标签： plsql

解决方案

也许您只是忘记提交更改？

这是您的存储过程的更简单版本。

CREATE OR REPLACE PROCEDURE update_grid_stats(p_grid_name VARCHAR2, p_region_name VARCHAR2)
IS
  v_ins_structure_count  rpt_grid.ins_structure_count%TYPE;
  v_da_pole_count        rpt_grid.da_pole_count%TYPE;
BEGIN
  UPDATE rpt_grid grd 
  SET (ins_structure_count, da_pole_count) =
  (
    SELECT
      COUNT(CASE WHEN tt.work_order_task_type = 'Pole Inspection' THEN 1 END),
      COUNT(CASE WHEN tt.work_order_task_type = 'Pole Damage' THEN 1 END)
    FROM workorderpoint_evw wp
    JOIN workordertasktype_evw tt ON wp.work_order_task_type_globalid = tt.globalid
    WHERE sde.st_relation_operators.st_within_f(wp.shape, grd.shape) = 1
  )
  WHERE grd.grid_name = p_grid_name
  AND grd.rpt_region = p_region_name
  RETURNING ins_structure_count, da_pole_count
       INTO v_ins_structure_count, v_da_pole_count;

  dbms_output.put_line(
    SQL%ROWCOUNT || ' rows got updated. Values: ' ||
    'ins_structure_count = ' || v_ins_structure_count ||
    ', da_pole_count = ' || v_da_pole_count
  );

  COMMIT;
END update_grid_stats;

变量和RETURNING子句仅用于输出。如果您不需要输出，则可以删除它们。

推荐阅读