Oracle 19c存储过程中For Loop查询指定表分区的正确方法

原存储过程的问题在于：

• 静态SQL里的PARTITION mypartition会被Oracle解析为字面量分区名，而非传入的PL/SQL变量值，导致无法匹配实际分区。
• 你尝试的动态SQL没有将变量值注入到SQL语句中，依然是把mypartition作为字面量处理，所以无效。

下面是两种可行的解决方式：

方式一：动态SQL拼接分区名（适用于传入分区名称的场景） #

通过动态SQL将分区名参数拼接进查询语句，同时用DBMS_ASSERT规避SQL注入风险（若分区名由可信来源传入，此方式安全）：

CREATE OR REPLACE PROCEDURE WriteRecordToFile
(
  mypartition IN VARCHAR2,
  myfilename  IN VARCHAR2,
  mydirloc    IN VARCHAR2
)
IS
  out_file      utl_file.file_type;
  chunk_size    BINARY_INTEGER := 32767;
  v_sql         VARCHAR2(1000);
  v_name        my_table.name%TYPE;
  v_cursor      SYS_REFCURSOR;
BEGIN
  out_file := utl_file.fopen(mydirloc, myfilename, 'w', chunk_size);
  
  -- 拼接动态SQL，用ENQUOTE_NAME处理分区名的转义和大小写问题
  v_sql := 'SELECT name FROM my_table PARTITION (' || DBMS_ASSERT.ENQUOTE_NAME(mypartition) || ')';
  OPEN v_cursor FOR v_sql;
  
  -- 遍历游标写入文件
  LOOP
    FETCH v_cursor INTO v_name;
    EXIT WHEN v_cursor%NOTFOUND;
    utl_file.put(out_file, v_name);
    utl_file.new_line(out_file);
  END LOOP;
  
  CLOSE v_cursor;
  utl_file.fclose(out_file);
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line('Error while writing file: '|| sqlerrm);
    -- 关闭已打开的资源
    IF v_cursor%ISOPEN THEN
      CLOSE v_cursor;
    END IF;
    IF utl_file.is_open(out_file) THEN
      utl_file.fclose(out_file);
    END IF;
END;
/

关键改动说明： #

• DBMS_ASSERT.ENQUOTE_NAME会自动给分区名添加双引号，处理分区名包含小写字母、特殊字符或大小写敏感的场景，同时避免SQL注入。
• 改用显式游标配合动态SQL，替代原静态游标循环，实现变量化的分区查询。

方式二：使用PARTITION FOR（适用于按分区键值定位的场景） #

如果你的分区是按范围/列表/哈希定义的，且可以通过分区键值定位分区，可使用PARTITION FOR语法，无需拼接SQL：

假设my_table的分区键是create_date（DATE类型），要查询对应键值的分区：

-- 示例：传入分区键值，而非分区名称
FOR rec IN (
  SELECT name FROM my_table PARTITION FOR (TO_DATE('20240101', 'YYYYMMDD'))
)
LOOP
  utl_file.put(out_file, rec.name);
  utl_file.new_line(out_file);
END LOOP;

此方式仅适用于已知分区键值的场景，若你需要传入分区名称，方式一更适用。

额外注意事项： #

1. 确保执行存储过程的用户拥有UTL_FILE权限，且mydirloc对应的目录已通过CREATE DIRECTORY在Oracle中创建并授权。
2. 若分区名是Oracle保留字或包含特殊字符，必须用双引号包裹，DBMS_ASSERT.ENQUOTE_NAME会自动完成这一步。

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