您的示例代码非常简单，几乎没有什么区别，但是在那种情况下，静态版本执行起来可能会更好.

Your example code is so simple that there will be little difference, but in that case the static version would most likely execute better.

使用动态SQL来提高性能的主要原因是，当SQL语句发生显着变化时-即，您可以根据系统状态在运行时向WHERE子句添加额外的代码(受系统限制).如果输入了地址，则对地址进行子查询).

The main reason to use dynamic SQL for performance is when the SQL statement can vary in a significant way - i.e. you might be able to add extra code to the WHERE clause at runtime based on the state of the system (restrict by a sub-query on Address, if Address entered, etc).

另一个原因是，有时将Bind变量用作参数可能适得其反.

Another reason is that sometimes using Bind variables as parameters can be counter-productive.

例如，如果您有一个状态字段之类的东西，其中的数据分布不均匀(但已建立索引).

An example is if you have something like a status field, where data is not evenly distributed (but is indexed).

在处理了95％的数据时，请考虑以下3条语句

Consider the following 3 statements, when 95% of the data is 'P'rocessed

   SELECT col FROM table 
   WHERE status = 'U'-- unprocessed
   AND company = :company

   SELECT col FROM table 
   WHERE status = 'P' -- processed
   AND company = :company

   SELECT col FROM table
   WHERE status = :status
   AND company = :company

在最终版本中，Oracle将选择通用的解释计划.在第一个版本中，它可能会决定***的计划是从状态索引开始(知道'U'处理过的条目只占总数的很小一部分).

In the final version, Oracle will choose a generic explain plan. In the first version, it may decide the best plan is to start with the index on status (knowing that 'U'nprocessed entries are a very small part of the total).

您可以通过不同的静态语句来实现这一点，但是如果您有更复杂的语句(仅需更改几个字符)，则动态SQL可能是一个更好的选择.

You could implement that through different static statements, but where you have more complex statements which only change by a couple of characters, dynamic SQL may be a better option.

缺点

同一条动态SQL语句的每次重复都会产生一个软解析，与静态语句相比，这是一个很小的开销，但是仍然是一个开销.

Each repetition of the same dynamic SQL statement incurs a soft parse, which is a small overhead compared to a static statement, but still an overhead.

每个新的sql语句(动态或静态)也会在SGA(共享内存)上产生锁定，并可能导致推出旧"语句.

Each NEW sql statement (dynamic or static) also incurs a lock on the SGA (shared memory), and can result in pushing 'old' statements out.

一个糟糕但常见的系统设计是，有人使用动态SQL来生成仅随键而异的简单选择，即

A bad, but common, system design is for someone to use dynamic SQL to generate simple selects that only vary by key - i.e.

SELECT col FROM table WHERE id = 5
SELECT col FROM table WHERE id = 20
SELECT col FROM table WHERE id = 7

单独的语句很快，但是由于它会杀死共享资源，因此整个系统的性能会降低.

The individual statements will be quick, but the overall system performance will deteriorate, as it is killing the shared resources.

此外-使用动态SQL在编译时捕获错误要困难得多.如果使用PL/SQL，则会浪费大量的编译时间检查.即使使用JDBC之类的东西(将所有数据库代码都移到字符串中-很好！)，您也可以使用预解析器来验证JDBC内容.动态SQL =仅用于运行时测试.

Also - it is far harder to trap errors at compile time with dynamic SQL. If using PL/SQL this is throwing away a good compilation time check. Even when using something like JDBC (where you move all your database code into strings - good idea!) you can get pre-parsers to validate the JDBC content. Dynamic SQL = runtime testing only.

开销

立即执行的开销很小-大约是千分之一秒-但是，如果这是在循环内/在每个对象调用一次的方法上等，它可以累加起来.我曾经将速度提高了10倍通过用生成的静态SQL替换动态SQL.但是，这使代码复杂化，并且仅仅因为我们需要速度而完成了.

The overhead of execute immediate is small - it is in the thousandths of a second - however, it can add up if this is inside a loop / on a method called once per object / etc. I once got a 10x speed improvement by replacing dynamic SQL with generated static SQL. However, this complicated the code, and was only done because we required the speed.