oracle 在oracle中为游标动态添加where子句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/377464/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Dynamically add where clauses to a cursor in oracle
提问by Trigger
I have plsql procedure which accepts certain parameters e.g. v_name, v_country, v_type.
我有接受某些参数的 plsql 程序，例如 v_name、v_country、v_type。
I wish to have a cursor with a select statement like this:
我希望有一个带有像这样的 select 语句的游标：
select column from table1 t1, table2 t2
where t1.name = v_name
and t1.country = v_country
and t1.id = t2.id
and t2.type = v_type
If certain parameters are empty can I only add the relevant where clauses to the cursor? Or is there a better way to accomplish this?
如果某些参数为空，我可以只将相关的 where 子句添加到游标吗？或者有没有更好的方法来实现这一点？
回答by WW.
The best way to use this is with DBMS_SQL.
使用它的最佳方法是使用 DBMS_SQL。
You create a string that represents your SQL statement. You still use bind variables. It's painful.
您创建一个字符串来表示您的 SQL 语句。您仍然使用绑定变量。这是痛苦的。
It goes something like this (I haven't compiled this, but it should be close) :-
它是这样的（我还没有编译这个，但应该很接近）：-
CREATE OR REPLACE FUNCTION find_country( v_name t1.country%TYPE,
v_type t2.type%TYPE) /* Hmm, column called type? */
DECLARE
v_SQL varchar2(2000);
v_select INTEGER; /* "Pointer" to a DBMS_SQL select statement */
v_execute INTEGER;
BEGIN
v_SQL := 'select column from table1 t1, table2 t2 ||
'where t1.id = t2.id';
IF v_name IS NOT NULL THEN
v_SQL := v_SQL || ' AND t1.country = :v_name'
END IF;
IF v_type IS NOT NULL THEN
v_SQL := v_SQL || ' AND t2.type = :v_type';
END IF;
/* Setup Cursor */
v_select := dbms_sql.open_cursor;
dbms_sql.parse( v_select, v_SQL, DBMS_SQL.native);
IF v_name IS NOT NULL THEN
dbms_sql.bind_variable( v_select, ':v_name', v_name );
END IF;
IF v_type IS NOT NULL THEN
dbms_sql.bind_variable( v_select, ':v_type', v_type );
END IF;
DBMS_SQL.DEFINE_COLUMN(v_select, 1, v_column); /* This is what we have selected */
/* Return value from EXECUTE is undefined for a SELECT */
v_execute := DBMS_SQL.EXECUTE( v_select );
IF DBMS_SQL.FETCH_ROWS( v_select ) > 0 THEN
/* A row was found
DBMS_SQL.COLUMN_VALUE( v_select, 1, v_column);
/* Tidy Up */
DBMS_SQL.CLOSE_CURSOR(v_select);
RETURN v_ID_address;
ELSE
DBMS_SQL.CLOSE_CURSOR(v_select);
/* No row */
RETURN NULL;
END IF;
EXCEPTION
WHEN OTHERS THEN
IF DBMS_SQL.IS_open(v_select) THEN
DBMS_SQL.CLOSE_CURSOR(v_select);
END IF;
RAISE;
END;
This approach is so painful compared to just writing the SQL inline that unless you have heaps of columns sometimes it's just easier writing a couple of different versions using this syntax:
与仅编写内联 SQL 相比，这种方法非常痛苦，除非您有时有大量列，否则使用以下语法编写几个不同的版本会更容易：
FOR r IN (SELECT blah FROM blah WHERE t1 = v_t1) LOOP
func( r.blah );
END LOOP;
回答by WW.
It's not directly what you're asking, but it may be an acceptable solution:
这不是您要问的直接问题，但它可能是一个可以接受的解决方案：
select column from table1 t1, table2 t2
where
(v_name is null or t1.name = v_name)
and (v_country is null or t1.country = v_country)
and t1.id = t2.id
and (v_type is null or t2.type = v_type)
回答by hamishmcn
One way would be to build up your query as a string then use execute immediate
一种方法是将查询构建为字符串，然后使用立即执行
回答by Stew S
The bestway to do this would be to use Oracle's Application Context feature, best defined as best performance and security.
执行此操作的最佳方法是使用 Oracle 的应用程序上下文特性，最好将其定义为最佳性能和安全性。
The fasterway would be what hamishmcn suggested, using EXECUTE IMMEDIATE. I'd choose that over WW's suggestion of DBMS_SQL every time.
该快的方式是hamishmcn建议是什么，使用EXECUTE IMMEDIATE。我每次都会选择 WW 的 DBMS_SQL 建议。
Another way that's quickest to writebut won't perform as well would be something like this:
另一种编写速度最快但性能不佳的方法是这样的：
select column from table1 t1, table2 t2
where t1.name = nvl(v_name, t1.name)
and t1.country = nvl(v_country, t1.country)
and t1.id = t2.id
and t2.type = nvl(v_type, t2.type)
回答by Edwin
You do not have to use dbms_sql to solve this problem and you can still use normal cursor by using a ref cursor.
您不必使用 dbms_sql 来解决此问题，您仍然可以通过使用 ref 游标来使用普通游标。
Sample:
样本：
DECLARE
TYPE cursor_ref IS REF CURSOR;
c1 cursor_ref;
r1 table1.column%type;
BEGIN
l_sql := 'select t1.column from table1 t1, table2 t2 where t1.id = t2.id ';
if v_name is not null then
l_sql := l_sql||' and t1.name = '||v_name ;
end if;
if v_country is not null then
l_sql := l_sql||' and t1.country = '||v_country';
end if;
if v_type is not null then
l_sql := l_sql||' and t2.type = '||v_type';
end if;
open c1 for l_sql;
loop
fetch c1 into r1;
exit when c1%notfound;
-- do something
end loop;
close c1;
end;
/
You can make this better by binding the variables with the command 'using' like this:
您可以通过像这样将变量与命令 'using' 绑定来使其更好：
open c1 for l_sql using v_name, v_country;
