可以用动态部分扩展静态 SQL 语句吗？

Question

我想创建一个 Oracle 包，其中有一个执行一些动态 SQL 的过程。如果我使用 EXECUTE IMMEDIATE 动态执行这一切，这没有问题，但如果查询的静态部分可以静态编码（以进行编译时检查），那就更好了。

完全动态查询的示例：

-- v_stmt is built dynamically.
v_stmt := 'SELECT count(*) FROM <here some joins> WHERE <here some conditions>';
EXECUTE IMMEDIATE v_stmt
  USING v_param1, v_param2
  RETURNING INTO v_count;

我尝试使 FROM 部分静态化的示例：

-- v_stmt is built dynamically.
v_stmt := 'SELECT count(*) FROM my_package.my_function(:param1, :param2) WHERE <here some conditions>';
EXECUTE IMMEDIATE v_stmt
  USING v_param1, v_param2
  RETURNING INTO v_count;

FUNCTION my_function(
  i_param1 IN VARCHAR2,
  i_param2 IN NUMBER
)
RETURN SYS_REFCURSOR
AS
  v_cursor SYS_REFCURSOR;
BEGIN
  -- Open a cursor for different queries depending on params.
  IF i_param2 = 1 THEN
      OPEN v_cursor FOR <some static query>;
  ELSE
      OPEN v_cursor FOR <some other static query>;
  END IF;
  RETURN v_cursor;
END;

这不起作用，因为无法从 SYS_REFCURSOR 中进行选择（至少这是我在 Google 中发现的）。

有什么办法可以达到这个目标吗？

编辑：根据要求，这里有一些示例：

静态查询：

SELECT a.*, ca.CUS_ID FROM adresses a INNER JOIN customer_adresses ca ON (ca.adr_id = a.adr_id);
SELECT p.*, cp.CUS_ID FROM persons p INNER JOIN customer_persons cp ON (cp.per_id = p.per_id);

然后动态扩展它们，如下例所示：

-- Checks if there is an adress in the customer where the zip is null.
SELECT count(*) FROM <static adresses query> q WHERE q.cus_id = :param1 AND a.zip IS NULL;
-- Checks if there is at least one person in the customer.
SELECT count(*) FROM <static persons query> q WHERE q.cus_id = :param1;

Answer 1

抱歉，但是为什么需要这样做呢？似乎您引入了一个函数，该函数将根据参数列表返回不同类型的数据/表，从而使事情变得过于复杂。我觉得非常令人困惑。此外，你必须在某个地方做这项工作，你只是想把它隐藏在这个函数中（在 if param1=this then x if param1=that then y...）

此外，即使你确实实现了一个光标函数（即使是管道式的），在这种情况下这将是一个坏主意，因为您将迫使 Oracle 做它不一定需要做的工作（暂时忽略所有上下文切换）。为了获得计数，您需要 Oracle 获取每行结果，然后进行计数。很多时候，Oracle 可以通过快速完整索引扫描来获取计数（当然取决于查询）。通常，如果在缓冲区缓存中找到块，则多次运行相同的查询将不需要每次都执行所有工作。我会挑战您使用直接 SQL 与使用返回游标的函数多次运行计数。你可能会感到惊讶。据我所知（请检查我），新的 11g 函数结果缓存不适用于管道函数或返回引用游标的函数（以及其他问题，例如由于依赖表而导致的失效）。

所以，我想说的是为什么不这样做： select count(1) into v_variable from ...;

如果您想隐藏和模块化，那么只需知道您可能会失去什么即可。

Answer 2

您可能想在 function1 中打开一个查询，然后将其结果作为表传输到 function2，然后 function2 将向该“表”添加一个 where 子句。

在这种情况下，您需要将 function1 重写为 流水线表函数

v_stmt := 'SELECT count(*) FROM table(my_package.my_function(:param1, :param2)) WHERE <here some conditions>';
EXECUTE IMMEDIATE v_stmt
  USING v_param1, v_param2
  RETURNING INTO v_count;

CREATE TYPE object_row_type AS OBJECT (
  OWNER              VARCHAR2(30),
  OBJECT_TYPE        VARCHAR2(18),
  OBJECT_NAME        VARCHAR2(30),
  STATUS             VARCHAR2(7)
);


CREATE TYPE object_table_type AS TABLE OF object_row_type;


FUNCTION my_function(
  i_param1 IN VARCHAR2,
  i_param2 IN NUMBER
)
RETURN object_table_type PIPELINED AS
BEGIN 

Answer 3

您可以使用 Oracle 表达式过滤器<进行编译时检查表达式/a>.

它可能比其他解决方案更复杂，但如果您确实需要验证您的条件，它可能会有所帮助。