原生动态SQL上动态查询的动态绑定

Question

我发现自己所处的情况是，我的代码需要执行一个动态语句，该语句由一个未知的数目(至少一个，但可能更多)由'intersect‘和'union’操作符连接的其他动态语句组成。

下面是一个有三个查询的示例(我知道可以用一个查询来解决这个问题，我试图保持简单)：

sql1 varchar2(500) := 'select empno from emp where deptno = :1';
sql2 varchar2(500) := 'select empno from emp where sal > :2 and hiredate >=:3'; 
sql3 varchar2(500) := 'select empno from emp where sal <= :2 and hiredate =:3'
realStatement varchar(1500) := sql1 || ' insersect ' || sql2 || ' union ' sql3;

现在，假设子语句的数量在运行时是未知的，但是所有绑定变量的值都是已知的(例如，deptno、sal和hiredate将始终是:1、:2和:3 )。我不能使用‘执行立即realStatement使用’表单，因为它的绑定是位置的，在本例中，我应该两次传递sal和hiredate参数，结果是语句：

EXECUTE IMMEDIATE realStatement USING l_deptno,l_sal,l_hiredate,l_sal,l_hiredate;

我不可能事先知道每个子陈述的所有重复。

我知道我可以使用带有bind()函数的DBMS_SQL包，但是性能比本机动态(来自oracle )差1.5到3倍，在这种情况下性能是相关的。

因此，我实际上要做的是用l_deptno替换所有的':1‘事件，用l_sal替换所有':2’事件，用‘to_date’替换‘':3’事件(‘’溶胶‘to_date’‘，’DD/MM/YYYY‘’)‘’，然后执行如下操作：

realStatement := replace(realStatement,':1',l_deptno);
realStatement := replace(realStatement,':2',l_sal);
realStatement := replace(realStatement,':3','to_date(''' || l_hiredate || ''',''DD/MM/YYYY'')');
EXECUTE IMMEDIATE realStatement;

但我不确定这是否是最好的解决办法，问题：

1. 是否有一种方法可以提高性能或使用本机动态SQL动态传递绑定？
2. DBSM_SQL包的使用会带来比所选解决方案更好的性能吗？

Answer 1

重写您的声明：

sql1 varchar2(500) := 'select empno from emp where deptno = :1';
sql2 varchar2(500) := 'select empno from emp where sal > :2 and hiredate >=:3'; 
sql3 varchar2(500) := 'select empno from emp where sal <= :2 and hiredate =:3'
realStatement varchar(1500) := sql1 || ' insersect ' || sql2 || ' union ' sql3;

若要使用WITH子句，如下所示：

sql0 varchar2(500) := 'WITH par AS (SELECT :1 AS P1, :2 AS P2, :3 AS P3 FROM dual)';
sql1 varchar2(500) := '(select empno from emp join par where deptno = par.p1)';
sql2 varchar2(500) := '(select empno from emp join par where sal > par.p2 and hiredate >=par.p3)'; 
sql3 varchar2(500) := '(select empno from emp join par where sal <= par.p2 and hiredate = par.p3)';
realStatement varchar(2000) := sql0 || ', sql1 as ' || sql1 || ', sql2 as ' || sql2 || ', sql3 as ' || sql3 || ' select * from sql1 intersect select * from sql2 union select * from sql3';

或(当不重用子查询时)：

sql0 varchar2(500) := 'WITH par AS (SELECT :1 AS P1, :2 AS P2, :3 AS P3 FROM dual)';
sql1 varchar2(500) := 'select empno from emp join par where deptno = par.p1';
sql2 varchar2(500) := 'select empno from emp join par where sal > par.p2 and hiredate >=par.p3'; 
sql3 varchar2(500) := 'select empno from emp join par where sal <= par.p2 and hiredate = par.p3';
realStatement varchar(2000) := sql0 || ' ' || sql1 || ' intersect ' || sql2 || ' union ' || sql3;

然后使用3个绑定变量执行它：EXECUTE IMMEDIATE realStatement USING l_deptno,l_sal,l_hiredate

Answer 2

正如您已经提到的，Oracle (sql.htm#BJEBACEH)说：

使用本机动态SQL的程序比使用DBMS_SQL包的程序快得多。通常，本机动态SQL语句的性能是等效DBMS_SQL调用的1.5至3倍。

但是从另一方面-你可以在这里看到一些比较：wiki/231.dbms-sql-vs-execute-immediate.aspx

与立即执行相比，使用DBMS_SQL有许多优点：

• 它更容易拼写和打字(无论如何-我不能拼写IMMEDAITE IMMEADIATE立即
• 较少使用DBMS_SQL锁存。
• 使用DBMS_SQL的解析较少。
• 更好的缩放您的应用程序与DBMS_SQL，因为以上原因。

缺点：

• 使用DBMS_SQL进行更全面的输入。

我想说的是--如果您有很多这样的调用，那么绑定变量和减少解析计数将产生显著的影响。但是--如果您没有多个调用，并且单个查询返回了许多行--那么在没有绑定变量的情况下立即执行(使用替换的解决方案)可能会更快。

在任何情况下--如果性能相关--请同时使用执行立即和DBMS_SQL的解决方案，并对结果进行比较。

Answer 3

几年前，当我在一次培训中，性能差异是一个话题(它是Oracle 9i)。我们能够复制出这种差异。然而，当我今天(用Oracle 11)做同样的测试时，我已经没有任何性能差异了。使用对你更方便的方式。