如何优化PL/SQL动态SQL_DBMS_SQL包与复杂动态游标处理-Oracle-PHP中文网

DBMS_SQL.PARSE报ORA-00900因未预编译，仅执行时解析；须先用EXECUTE IMMEDIATE验证，再用DBMS_SQL；PARSE后DDL需立即EXECUTE；动态列需DESCRIBE_COLUMNS获取元数据并正确DEFINE_COLUMN；FETCH_ROWS返回0非结束标志，须结合COLUMN_VALUE状态判断。

DBMS_SQL.PARSE 为什么总报 ORA-00900？

动态 sql 字符串里多一个空格、少一个分号，或者用了绑定变量但没声明，dbms_sql.parse 就直接抛 ora-00900: invalid sql statement。这不是语法校验不严，是它根本没做预编译——只在执行时才真正解析。

实操建议：

先用 EXECUTE IMMEDIATE 测试语句是否能跑通，再挪到 DBMS_SQL 流程里；
拼接 SQL 时，用 DBMS_ASSERT.SQL_OBJECT_NAME 或 DBMS_ASSERT.ENQUOTE_LITERAL 处理用户输入，别自己写正则过滤；
PARSE 的第四个参数（language_flag）别硬写 DBMS_SQL.NATIVE，Oracle 12c+ 推荐用 DBMS_SQL.V7 兼容老逻辑，尤其涉及 PIVOT 或子查询列别名时；
如果 SQL 含 DDL（比如 CREATE TABLE AS SELECT），必须在 PARSE 后立刻调 EXECUTE，不能走 DEFINE_COLUMN + FETCH_ROWS 流程。

动态游标里字段数不固定，DEFINE_COLUMN 怎么写不崩？

用 DBMS_SQL.DESCRIBE_COLUMNS 获取列元数据是必须的，但很多人卡在「不知道列类型就调 DEFINE_COLUMN」——比如把 NUMBER 当 VARCHAR2 定义，FETCH 时直接报 ORA-06502: PL/SQL: numeric or value error。

实操建议：

先调 DESCRIBE_COLUMNS 拿到 desc_tab，遍历每列的 col_type：1 是 VARCHAR2，2 是 NUMBER，12 是 DATE，180 是 TIMESTAMP；
对 NUMBER 类型，用 DEFINE_COLUMN 时第三个参数（长度）传 38，别传 NULL 或 0，否则 12c+ 会截断精度；
遇到 CLOB 或 BLOB，别用 DEFINE_COLUMN，改用 DEFINE_COLUMN_LONG，且后续必须用 COLUMN_VALUE_LONG 取值；
如果列名含大小写或特殊字符（如 "User_ID"），DESCRIBE_COLUMNS 返回的 col_name 是带引号的，定义变量时名字得匹配，否则 COLUMN_VALUE 找不到对应列。

DBMS_SQL.EXECUTE 和 EXECUTE IMMEDIATE 性能差多少？

单次执行，EXECUTE IMMEDIATE 快 20%~40%；但循环执行 1000 次相同结构 SQL 时，预编译过的 DBMS_SQL 反而稳 15% 以上——关键不在函数本身，而在 Oracle 是否复用 cursor handle 和执行计划。

实操建议：

高频、结构稳定（仅 where 条件变）的动态 SQL，优先用 DBMS_SQL：PARSE 一次，多次 BIND_VARIABLE + EXECUTE；
如果 SQL 字符串每次拼接都不同（比如动态加 5 个 or 条件），EXECUTE IMMEDIATE 更省心，硬解析开销可控；
DBMS_SQL 的 handle 必须显式 CLOSE_CURSOR，漏掉会导致 session 级游标泄漏，查 v$open_cursor 能看到 user_name 为空的游标堆积；
Oracle 19c 开始，DBMS_SQL 在 PGA 中缓存的 cursor 信息比以前更重，频繁创建/关闭小游标反而比 EXECUTE IMMEDIATE 占更多内存。

FETCH_ROWS 返回 0 却没报错，数据去哪了？

FETCH_ROWS 返回 0 只表示当前 fetch 没拿到行，不是结束标志——它不等价于 %NOTFOUND。常见陷阱是循环里只靠返回值判断，结果跳过最后一行或重复 fetch 一次空行。

实操建议：

必须配合 DBMS_SQL.COLUMN_VALUE 的返回状态：成功取值返回 0，无数据返回 100，出错返回其他负值；
别用 WHILE DBMS_SQL.FETCH_ROWS(c) > 0 LOOP，正确写法是 LOOP ... EXIT WHEN DBMS_SQL.FETCH_ROWS(c) = 0;；
如果 SQL 有聚合（GROUP BY）或 ROWNUM ，FETCH_ROWS 可能提前返回 0，但实际还有行——因为 DBMS_SQL 按 buffer 批量 fetch，默认 100 行一包，需设 SET_ROW_COUNT 调整；
调试时加 DBMS_OUTPUT.PUT_LINE('fetched: ' || DBMS_SQL.FETCH_ROWS(c));，但上线前删掉，这个 IO 会让性能掉 3 倍以上。