在动态SQL中使用Oracle集合
我需要检查,如果过程参数为空,如果没有,则在WHERE子句中使用它。例如:在动态SQL中使用Oracle集合
sqlquery := 'SELECT * FROM table WHERE a_col = a AND';
IF b IS NOT NULL THEN
sqlquery := sqlquery || ' b_col = :b';
END IF;
IF c IS NOT NULL THEN
sqlquery := sqlquery || ' c_col = :c';
END IF;
等等。
然后我需要使用OPEN-FOR-USING语句来打开形成的sqlquery的游标,但以前我应该决定使用USING子句发送哪些值。我看到的唯一方法是使用大量的IF子句:
IF b IS NOT NULL AND c IS NOT NULL THEN
OPEN cur FOR sqlquery USING b, c;
ELSIF b IS NOT NULL THEN
OPEN cur FOR sqlquery USING b;
ELSIF c IS NOT NULL THEN
OPEN cur FOR sqlquery USING c;
ELSE
OPEN cur FOR sqlquery;
对于N个值,我得到大量的IF子句。我怎样才能解决这个问题没有太多的IFs?我认为可以使用Oracle集合,但我没有找到任何示例。
您需要构建您的查询,使得它总是引用:B和:C是这样的:
sqlquery := 'SELECT * FROM table WHERE a_col = a';
IF b IS NOT NULL THEN
sqlquery := sqlquery || ' AND b_col = :b';
ELSE
sqlquery := sqlquery || ' AND :b IS NULL';
END IF;
IF c IS NOT NULL THEN
sqlquery := sqlquery || ' AND c_col = :c';
ELSE
sqlquery := sqlquery || ' AND :c IS NULL';
END IF;
然后你可以使用它像这样:
OPEN cur FOR sqlquery USING b, c;
其实,我觉得这样做可能会更有效率:
sqlquery := 'SELECT * FROM table WHERE a_col = a';
IF b IS NOT NULL THEN
sqlquery := sqlquery || ' AND b_col = :b';
ELSE
sqlquery := sqlquery || ' AND (1=1 OR :b IS NULL)';
END IF;
IF c IS NOT NULL THEN
sqlquery := sqlquery || ' AND c_col = :c';
ELSE
sqlquery := sqlquery || ' AND (1=1 OR :c IS NULL)';
END IF;
...因为那么优化器可以识别出1 = 1总是tr因此,可以完全忽略该谓词。我记得在某个地方读过这篇文章(我认为Oracle Mag中的Tom Kyte文章),但现在找不到参考。
您可以使SQL查询完成检查每个项目为NULL的工作。
sqlquery := 'SELECT * FROM table WHERE a_col = a'||
' AND (:b IS NULL OR b_col = :b)'||
' AND (:c IS NULL OR b_col = :c)';
OPEN cur FOR sqlquery USING b,b,c,c;
您必须重复使用USING子句中的变量,这可能会导致错别字导致混淆错误。但至少你只需要做一次。
哦,好主意!多谢。 – 2010-08-25 15:44:07
这会失去进行动态查询的优势;它可能总会导致全表扫描。 – 2010-08-25 15:48:42
你试过这个吗?
select * from my_table
where a_col = :a
and b_col = (case when :b is not null then :b else b_col end)
and c_col = (case when :c is not null then :c else c_col end)
and d_col = (case when :d is not null then :d else d_col end)
;
我有类似的情况。
我计算出可行的执行计划是什么,并对它们使用不同的查询。
例如,假设我正在搜索人,并且参数是first_name,last_name。性别,日期。该表的索引是(last_name,first_name)和(date_of_birth)。
IF :p_firstname IS NOT NULL and :p_lastname IS NOT NULL THEN
OPEN cur FOR
'SELECT * FROM PEOPLE WHERE last_name=:a AND first_name=:b AND
(date_of_birth = :c or :c is NULL) AND (gender = :d or :d IS NULL)' USING ....
ELSIF :p_lastname IS NOT NULL THEN
OPEN cur FOR
'SELECT * FROM PEOPLE WHERE last_name=:a AND
(date_of_birth = :c or :c is NULL) AND (gender = :d or :d IS NULL)' USING ....
ELSIF :p_dateofbirth IS NOT NULL THEN
OPEN cur FOR
'SELECT * FROM PEOPLE WHERE date_of_birth=:a AND
(first_name=:b OR :b IS NULL) AND (gender = :d or :d IS NULL)' USING ....
ELSE
RAISE_APPLICATION_ERROR(-20001,'Last Name or Date of Birth MUST be supplied);
END IF;
本页有一些讨论和指向文章的链接,我相信您指的是: http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID: 1669972300346534908 – 2010-08-26 00:31:54
Thx,那就是我一直在寻找=) – 2010-08-26 08:26:00
@帕特里克:谢谢,这就是我的意思。甲骨文似乎已经调整了他们的所有网址,并且美味的链接我已经不再工作了! – 2010-08-26 08:32:37