Oracle for in loop语法使用示例

在函数里使用：

-- 函数，按申请单，查询任务，信息源统计某结果表返回结果数量 

CREATE OR REPLACE FUNCTION countDsQueryResult(p_in_applyid   IN VARCHAR2,
                                              p_in_applydsid IN VARCHAR2,
                                              p_in_dsid      IN VARCHAR2)
  RETURN NUMBER IS
  v_result      NUMBER := 0;
  v_temp_result NUMBER := 0;
  v_sql         VARCHAR2(1000);
BEGIN
  FOR v_tb_name IN (SELECT DISTINCT TABLE_NAME_CASE
              FROM (SELECT t3.ds_table_case AS TABLE_NAME_CASE
                      FROM JW_DS_BASE_INFO t3
                     WHERE t3.ds_id = p_in_dsid
                    UNION ALL
                    SELECT t1.TABLE_NAME_CASE
                      FROM JW_DS_ALL_TABLES_INFO t1
                      LEFT JOIN jw_ds_base_info t2
                        ON t1.ds_id = t2.ds_id
                     WHERE t1.ds_id = p_in_dsid) t) LOOP
    DBMS_OUTPUT.ENABLE(1000000);
    v_sql := 'SELECT COUNT(1) FROM jw_query_apply t1 JOIN jw_query_apply_ds_info t2 ON t1.pid = t2.applyid JOIN ' || v_tb_name.TABLE_NAME_CASE || ' t3 ON t2.apply_ds_id = t3.apply_ds_id  WHERE t1.pid = ''' || p_in_applyid || ''' AND t2.apply_ds_id = ''' || p_in_applydsid || '''
  AND t2.ds_id = ''' || p_in_dsid || '''';
     DBMS_OUTPUT.PUT_LINE('创建语句:' || v_sql);
    EXECUTE IMMEDIATE v_sql INTO v_temp_result;
    v_result := v_result + v_temp_result;
  END LOOP;
  RETURN v_result;
EXCEPTION
  WHEN no_data_found THEN
    dbms_output.put_line('信息源：' || p_in_dsid || '不存在');
    RETURN 0;
END;