如何用PL/SQL循环执行查询获取900+站点关联的变压器数据

针对单个站点的查询示例（你的原始查询）：

SELECT t.*
FROM transformers t
JOIN sites s ON s.Eamid = 'SITE-00266799'
WHERE t.eam_group = 'TRANSFORMER'
  AND t.end_date IS NULL
  AND SDO_RELATE(t.geom, s.geom, 'mask=ANYINTERACT') = 'TRUE';

下面是几种循环实现批量查询的方式，同时补充更高效的非循环方案：

方法1：游标循环逐行处理 #

适合需要对每个站点的结果做额外业务逻辑的场景，代码直观易调试：

DECLARE
  -- 定义游标获取所有站点ID
  CURSOR c_sites IS
    SELECT Eamid FROM sites; -- 替换为你存储所有站点的实际表名
  v_site_id sites.Eamid%TYPE;
BEGIN
  -- 遍历每个站点
  FOR v_site_id IN c_sites LOOP
    -- 执行当前站点的空间查询
    FOR rec_transformer IN (
      SELECT t.*
      FROM transformers t
      JOIN sites s ON s.Eamid = v_site_id
      WHERE t.eam_group = 'TRANSFORMER'
        AND t.end_date IS NULL
        AND SDO_RELATE(t.geom, s.geom, 'mask=ANYINTERACT') = 'TRUE'
    ) LOOP
      -- 处理每条结果：可以打印到控制台，或插入临时表留存
      DBMS_OUTPUT.PUT_LINE('站点: ' || v_site_id || ' | 变压器ID: ' || rec_transformer.Eamid);
      
      -- 示例：插入临时表（需提前创建temp_transformer_results表）
      -- INSERT INTO temp_transformer_results (site_eamid, transformer_eamid, ...)
      -- VALUES (v_site_id, rec_transformer.Eamid, ...);
    END LOOP;
  END LOOP;
  
  -- 若插入了临时表，执行提交
  COMMIT;
END;
/

方法2：批量游标+BULK COLLECT（高效版） #

针对900+站点的场景，批量收集数据能减少PL/SQL与SQL引擎的交互次数，提升执行效率：

DECLARE
  -- 定义存储站点ID的集合类型
  TYPE typ_site_ids IS TABLE OF sites.Eamid%TYPE;
  v_site_ids typ_site_ids;
  
  -- 定义存储变压器结果的记录和集合类型
  TYPE typ_transformer_rec IS RECORD (
    site_eamid sites.Eamid%TYPE,
    transformer_eamid transformers.Eamid%TYPE,
    transformer_geom transformers.geom%TYPE -- 按需添加其他字段
  );
  TYPE typ_transformer_list IS TABLE OF typ_transformer_rec;
  v_transformers typ_transformer_list;
BEGIN
  -- 批量获取所有站点ID
  SELECT Eamid BULK COLLECT INTO v_site_ids FROM sites;
  
  -- 遍历每个站点，批量收集关联的变压器数据
  FOR idx IN 1..v_site_ids.COUNT LOOP
    SELECT v_site_ids(idx) AS site_eamid, t.Eamid, t.geom
    BULK COLLECT INTO v_transformers
    FROM transformers t
    JOIN sites s ON s.Eamid = v_site_ids(idx)
    WHERE t.eam_group = 'TRANSFORMER'
      AND t.end_date IS NULL
      AND SDO_RELATE(t.geom, s.geom, 'mask=ANYINTERACT') = 'TRUE';
    
    -- 批量插入临时表，效率远高于逐行插入
    FORALL rec_idx IN 1..v_transformers.COUNT
      INSERT INTO temp_transformer_results (site_eamid, transformer_eamid, geom)
      VALUES (v_transformers(rec_idx).site_eamid, v_transformers(rec_idx).transformer_eamid, v_transformers(rec_idx).transformer_geom);
  END LOOP;
  
  COMMIT;
END;
/

补充：推荐使用非循环的关联查询 #

其实完全不需要写循环，直接通过SQL关联就能一次性获取所有结果，数据库的优化器会高效处理空间关联，性能比PL/SQL循环更好：

SELECT 
  s.Eamid AS site_eamid,
  t.*
FROM transformers t
JOIN sites s 
  ON SDO_RELATE(t.geom, s.geom, 'mask=ANYINTERACT') = 'TRUE'
WHERE 
  t.eam_group = 'TRANSFORMER'
  AND t.end_date IS NULL;

注意事项 #

• 请将代码中的sites、transformers替换为你的实际表名
• 临时表temp_transformer_results需要提前创建，字段根据需求定义
• 若空间查询性能不佳，确保transformers.geom和sites.geom字段创建了空间索引

内容的提问来源于stack exchange，提问作者Debojyoti Roy