给 A,B,C 设置一个默认值,这个默认值是实际值不可能有的,例如 allselect * from tbl where (a = A or A = 'all') and (b = B or B = 'all' and (c = C or C = 'all');
拼一个sql串呢: declare sql varchar2(1024) ; col_value varchar2(100); begin sql := 'select x.col from X where 1 = 1 '; if ( :1 is not null && :2 is not null $$ :3 is not null) then sql := sql || 'and x.a = ' || :1 || ' and x.b = ' ||... elsif ... end if;execute immediate sql into col_value;
假设有表T,有字段A,B,C 数据内容如下: A B C 32 告警 1 35 查看 1 36 中国 0 29 哈喽 1 89 哈哈 0 89 哈哈 0 89 得是 0假设我三个条件都选,条件为A(89),B(哈哈),C(0) 查询语句为: select * from T where (89 = A or A = 100000) and ('哈哈' = B or B = 'all' and (0 = C or C = 99); 是这样写吗?如果查询条件只有A(89),B(得是)这个应当怎么写? 对Oracle不熟悉,可能问的问题比较幼稚,请见谅。
拼一个sql串呢: declare sql varchar2(1024) ; col_value varchar2(100); begin sql := 'select X.A,X.B,X.C from X where 1 = 1 '; if ( a is not null && b is not null && c is not null) then sql := sql || 'and x.a = ' || a || ' and x.b = ' || b || ' and x.c = ' || c || 'else if ( a is not null && b is not null&& c is null) then sql := sql || 'and x.a = ' || a || ' and x.b = ' || b else if ( a is not null && b is null&& c is not null) then sql := sql || 'and x.a = ' || a || ' and x.c = ' || c else if ( a is null && b is not null&& c is not null) then sql := sql || 'and x.b = ' || b || ' and x.c = ' || c else if ( a is null && b is null&& c is not null) then sql := sql || ' and x.c = ' || c else if ( a is null && b is not null&& c is null) then sql := sql || ' and x.b = ' || b else if ( a is not null && b is null&& c is null) then sql := sql || ' and x.a = ' || a end if execute immediate sql into col_value;
给 A,B,C 设置一个默认值,这个默认值是实际值不可能有的,例如 allselect * from tbl
where (a = A or A = 'all')
and (b = B or B = 'all'
and (c = C or C = 'all');
declare
sql varchar2(1024) ;
col_value varchar2(100);
begin
sql := 'select x.col from X where 1 = 1 ';
if ( :1 is not null && :2 is not null $$ :3 is not null) then
sql := sql || 'and x.a = ' || :1 || ' and x.b = ' ||...
elsif
...
end if;execute immediate sql into col_value;
数据内容如下:
A B C
32 告警 1
35 查看 1
36 中国 0
29 哈喽 1
89 哈哈 0
89 哈哈 0
89 得是 0假设我三个条件都选,条件为A(89),B(哈哈),C(0)
查询语句为:
select * from T
where (89 = A or A = 100000)
and ('哈哈' = B or B = 'all'
and (0 = C or C = 99);
是这样写吗?如果查询条件只有A(89),B(得是)这个应当怎么写?
对Oracle不熟悉,可能问的问题比较幼稚,请见谅。
declare
sql varchar2(1024) ;
col_value varchar2(100);
begin
sql := 'select X.A,X.B,X.C from X where 1 = 1 ';
if ( a is not null && b is not null && c is not null) then
sql := sql || 'and x.a = ' || a || ' and x.b = ' || b || ' and x.c = ' || c || 'else if ( a is not null && b is not null&& c is null) then
sql := sql || 'and x.a = ' || a || ' and x.b = ' || b
else if ( a is not null && b is null&& c is not null) then
sql := sql || 'and x.a = ' || a || ' and x.c = ' || c
else if ( a is null && b is not null&& c is not null) then
sql := sql || 'and x.b = ' || b || ' and x.c = ' || c
else if ( a is null && b is null&& c is not null) then
sql := sql || ' and x.c = ' || c
else if ( a is null && b is not null&& c is null) then
sql := sql || ' and x.b = ' || b
else if ( a is not null && b is null&& c is null) then
sql := sql || ' and x.a = ' || a
end if
execute immediate sql into col_value;