类似string sql="select * from table where 1=1 ";if(项目部门输入) { sql+=" and 项目部门条件" } if(项目名称输入) { sql+=" and 项目名称条件" }
我的意思是在SQL语句里判断,不在页面上进行判断。这条SQL语句是固定的。
你看是这个意思吗? 条件为空就不加到where子句中去: SQL> begin 2 :ename:='FORD'; 3 end; 4 /PL/SQL 过程已成功完成。SQL> print :ename;ENAME -------------------------------- FORDSQL> select empno,ename from emp where (ename=:ename or :ename is null); EMPNO ENAME ---------- ---------- 7902 FORDSQL> begin 2 :ename:=null; 3 end; 4 /PL/SQL 过程已成功完成。SQL> select empno,ename from emp where (ename=:ename or :ename is null); EMPNO ENAME ---------- ---------- 7369 SMITH 7499 ALLEN 7521 WARD 7566 JONES 7654 MARTIN 7698 BLAKE 7782 CLARK 7788 SCOTT 7839 KING 7844 TURNER 7876 ADAMS EMPNO ENAME ---------- ---------- 7900 JAMES 7902 FORD 7934 MILLER已选择14行。在这个帖子里有讨论,可以看一下: http://topic.csdn.net/u/20081128/09/5c7e8313-f4a2-4a11-9455-a4b9dc6c9702.html
select * from project where projectDep like '%s%' or projectName like '%s%';
那就直接 字段 like '%'+输入值+'%'如果没有输入值则 like '%%'相当于全部数据
类似的存储过程实现吧create or replace procedure pro(v_1 varchar2, v_2 varchar2, cu out sys_refcursor) as begin if v_1 is not null and v_2 is null then open cu for select * from t where col1 = v_1; elsif v_1 is null and v_2 is not null then open cu for select * from t where col1 = v_2; elsif v_1 is not null and v_2 is not null then open cu for select * from t where col1 = v_1 and col1 = v_2; end if; end;
{
sql+=" and 项目部门条件"
}
if(项目名称输入)
{
sql+=" and 项目名称条件"
}
SQL> begin
2 :ename:='FORD';
3 end;
4 /PL/SQL 过程已成功完成。SQL> print :ename;ENAME
--------------------------------
FORDSQL> select empno,ename from emp where (ename=:ename or :ename is null); EMPNO ENAME
---------- ----------
7902 FORDSQL> begin
2 :ename:=null;
3 end;
4 /PL/SQL 过程已成功完成。SQL> select empno,ename from emp where (ename=:ename or :ename is null); EMPNO ENAME
---------- ----------
7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
7782 CLARK
7788 SCOTT
7839 KING
7844 TURNER
7876 ADAMS EMPNO ENAME
---------- ----------
7900 JAMES
7902 FORD
7934 MILLER已选择14行。在这个帖子里有讨论,可以看一下:
http://topic.csdn.net/u/20081128/09/5c7e8313-f4a2-4a11-9455-a4b9dc6c9702.html
select *
from project
where projectDep like '%s%' or projectName like '%s%';
字段 like '%'+输入值+'%'如果没有输入值则 like '%%'相当于全部数据
v_2 varchar2,
cu out sys_refcursor) as
begin
if v_1 is not null and v_2 is null then
open cu for
select * from t where col1 = v_1;
elsif v_1 is null and v_2 is not null then
open cu for
select * from t where col1 = v_2;
elsif v_1 is not null and v_2 is not null then
open cu for
select *
from t
where col1 = v_1
and col1 = v_2;
end if;
end;