PL/SQL 存储过程遍历实例

CREATE OR REPLACE PROCEDURE insert_yeya_zhijia_leaf
as
   v_number1 NUMBER; --工作阻力
   v_number2 NUMBER; --最高高度
   v_count NUMBER; --数量
   v_zhijiaxingshi VARCHAR2(300); --支架型式
   CURSOR cur1 IS SELECT DISTINCT zhijiaxingshi FROM device_yeya_zhijia WHERE delete_flag = 0 AND zhijiaxingshi IS NOT NULL; --游标1
   CURSOR cur2 IS SELECT DISTINCT gongzuo_zuli FROM device_yeya_zhijia WHERE delete_flag = 0 AND gongzuo_zuli IS NOT NULL ORDER BY gongzuo_zuli; --游标2
   CURSOR cur3 IS SELECT DISTINCT zuigao_gaodu FROM device_yeya_zhijia WHERE delete_flag = 0 AND zuigao_gaodu IS NOT NULL ORDER BY zuigao_gaodu; --游标3
BEGIN
  DELETE FROM device_yeya_zhijia_leaf; --清空表
  DBMS_OUTPUT.ENABLE(buffer_size => NULL); --不限制输出字节
  --进行外部循环(三层循环)
  FOR i IN cur1 LOOP
      v_zhijiaxingshi := i.zhijiaxingshi;
      --进行中部循环
      FOR x IN cur2 LOOP
          v_number1 := x.gongzuo_zuli;
          DBMS_OUTPUT.put_line(v_number1); --打印参数
          --进行内部循环
          FOR y IN cur3 LOOP
              v_number2 := y.zuigao_gaodu;
              --DBMS_OUTPUT.put_line(v_number1||'*'||v_number2);
              SELECT COUNT(*) INTO v_count FROM device_yeya_zhijia y WHERE y.gongzuo_zuli = v_number1 AND y.zuigao_gaodu = v_number2 AND y.delete_flag = 0 AND y.zhijiaxingshi = v_zhijiaxingshi;
              DBMS_OUTPUT.put_line(v_number1||'*'||v_number2||'==='||v_count);
              INSERT INTO device_yeya_zhijia_leaf VALUES (sys_guid(), v_number1, v_number2, v_count, SYSDATE(), v_zhijiaxingshi); --循环插入数据
          END LOOP; --循环结束
      END LOOP;
  END LOOP;
  COMMIT; --提交
END;

    因业务需要大量循环显示组合数据，在前端进行循环速度过慢，故考虑使用存储过程，将遍历的压力放置在服务器端，定期执行存储过程，提高页面显示的速度。现将例子记录，以方便大家和自己参考查阅。