关于ORACLE字符串拼接的问题

Question

问题表述：多项联合查询：比如：踢实况的时候买球员需要搜索：
搜索条件有年龄范围，身高范围，各项指数的范围等等
我只选择年龄和综合评价范围，其余为空，在存储过程中，为空的条件就不拼接字符串了,就把这两个条件拼到条件字符串里面。最后select [item1][item2][item3] from[table1][table2] where sqlStr(这里where后面的条件怎么弄，刚弄存储过程没几天，什么都不太清楚，望高手指点一下字符串拼接)还有一个问题，条件很多为number型，在java里面，应该用setInt或者setDouble方法设置吧，但是知识有限，貌似null值只能用setString方法。有人说把控制设为特殊符号或者-1，但是比较又成了一个问题，number型和-1无法直接比较。
求高手解惑。
代码如下：
CREATE OR REPLACE PROCEDURE CRM.search_proc(
customerId IN       varchar2,
customerName IN     varchar2,
sex IN  VARCHAR2,
age1 IN int,
age2    IN  int,
fixedAsset1  IN  number,
fixedAsset2  IN  number,
flowAsset1  IN  number,
flowAsset2  IN  number,
consume1    in  number,
consume2    in  number,
assetint1    in number,
assetint2    in  number,
debtvalue1  in  number,
debtvalue2  in  number,
fundId  in  varchar2,
productId in    varchar2,
customerValue   in varchar2,
customerLevel   in varchar2,
p_cursor   OUT      cursorpackage.p_cursor
)
IS
  sqlStr varchar2(2000);
    
BEGIN 
    sqlStr:='';
  IF customerId is not null THEN
     sqlStr:='c1.CUSTOMER_ID='||customerId||'';
  END IF;
   
  IF customerName is not null THEN
      IF sqlStr is not null THEN
         sqlStr:=sqlStr||' and c1.CUSTOMER_NAME='||customerName||'';
      ELSE
         sqlStr:='c1.CUSTOMER_NAME='||customerName||'';
      END IF;
  END IF;
  
  IF sex is not null THEN
      IF sqlStr is not null THEN 
         sqlStr:=sqlStr||' and c1.SEX='||sex||'';
      ELSE
         sqlStr:='c1.SEX='||sex||'';
              END IF;
          END IF;
          
            IF age1 is not null and age2 is not null THEN
              IF sqlStr is not null THEN 
                 sqlStr:=sqlStr||' and (c1.AGE between'||age1||' and'||age2||')';
              ELSE
                 sqlStr:='c1.AGE between'||age1||' and'||age2||'';
                 END IF;
           END IF;
           
            IF fixedAsset1 is not null and fixedAsset is not null THEN
              IF sqlStr is not null THEN 
                 sqlStr:=sqlStr||' and (c2.PERMANENT_ASSET between'||fixedAsset1||' and'||fixedAsset2||')';
              ELSE
                 sqlStr:='c2.PERMANENT_ASSET between'||fixedAsset1||' and'||fixedAsset2||'';
                 END IF;
           END IF;
           
            IF flowAsset1 is not null and flowAsset2 is not null THEN
              IF sqlStr is not null THEN 
                 sqlStr:=sqlStr||' and (c3.FLOATING_ASSET between'||flowAsset1||' and'||flowAsset2||')';
              ELSE
                 sqlStr:='c3.FLOATING_ASSET between'||flowAsset1||' and'||flowAsset2||'';
                 END IF;
           END IF;
           
            IF consume1 is not null and consume2 is not null THEN
              IF sqlStr is not null THEN 
                 sqlStr:=sqlStr||' and (c1.consume between'||consume1||' and'||consume2||')';
              ELSE
                 sqlStr:='c1.consume between'||consume1||' and'||consume2||'';
                 END IF;
           END IF;
           
            IF consume1 is not null and consume2 is not null THEN
              IF sqlStr is not null THEN 
                 sqlStr:=sqlStr||' and (c3.ASSET between'||consume1||' and'||consume2||')';
              ELSE
                 sqlStr:='c3.ASSET between'||consume1||' and'||consume2||'';
                 END IF;
           END IF;
           
            IF debtvalue1 is not null and debtvalue2 is not null THEN
              IF sqlStr is not null THEN 
                 sqlStr:=sqlStr||' and (c4.DEBT_VALUE between'||debtvalue1||' and'||debtvalue2||')';
              ELSE
                 sqlStr:='c4.DEBT_VALUE between'||debtvalue1||' and'||debtvalue2||'';
                 END IF;
           END IF;
           
            IF fundId is not null THEN
              IF sqlStr is not null THEN 
                 sqlStr:=sqlStr||' and c5.FUND_ID='||fundId||'';
              ELSE
                 sqlStr:='c5.FUND_ID='||fundId||'';
                 END IF;
           END IF;
           
           IF productId is not null THEN   
              IF sqlStr is not null THEN 
                 sqlStr:=sqlStr||' and c6.BANK_PRODUCT_ID='||productId||'';
              ELSE
                 sqlStr:='c6.BANK_PRODUCT_ID='||productId||'';
                 END IF;
           END IF;
           
           IF customerValue is not null THEN  
              IF sqlStr is not null THEN
                 sqlStr:=sqlStr||' and c1.CUSTOMER_VALUE='||customerValue||'';
              ELSE
                 sqlStr:='c1.CUSTOMER_VALUE='||customerValue||'';
                 END IF;
           END IF;
           
           IF customerLevel is not null THEN       
              IF sqlStr is not null THEN
                 sqlStr:=sqlStr||' and c1.CUSTOMER_LEVEL='||customerLevel||'';
              ELSE
                 sqlStr:='c1.CUSTOMER_LEVEL='||customerLevel||'';
                 END IF;
           END IF;
           
           --dbms_output.put_line(sqlStr);
           
         SELECT c1.CUSTOMER_Id,c1.CUSTOMER_NAME,c1.SEX,c1.AGE,c2.PERMANENT_ASSET,c3.FLOATING_ASSET, c1.CONSUME,c3.ASSET_NUMBER,
         c4.DEBT_VALUE,c5.FUND_ID, c6.BANK_PRODUCT_ID, c1.CUSTOMER_VALUE,c1.CUSTOMER_LEVEL 
         FROM INFO_CUSTOMER_BASIC c1,INFO_PERMANENT_ASSET c2,INFO_FLOATING_ASSET c3,INFO_CUSTOMER_DEBT c4,INFO_FUND c5,INFO_BANK_PRODUCT c6 
         where sqlStr;
          
          END;
/

Answer 1

附上java代码的问题
 call procedure
try {
callableStmt = connection.prepareCall("{call search_proc(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}");
callableStmt.setString(1, customerId);
callableStmt.setString(2, customerName);
callableStmt.setString(3, gender); if(!smallerAge.equals("null")){
int age1=Integer.parseInt(smallerAge);
callableStmt.setInt(4, age1);
}
else callableStmt.setInt(4, -1);

if(!biggerAge.equals("null")){
int age2=Integer.parseInt(biggerAge);
callableStmt.setInt(5, age2);
}
else callableStmt.setInt(5, -1);

if(!bottomFixedAsset.equals("null")){
int fixedAsset1=Integer.parseInt(bottomFixedAsset);
callableStmt.setDouble(6, fixedAsset1);
}
else callableStmt.setDouble(6, -1);

if(!topFixedAsset.equals("null")){
int fixedAsset2=Integer.parseInt(topFixedAsset);
callableStmt.setDouble(7, fixedAsset2);
}
else callableStmt.setDouble(7, -1);

if(!bottomFlowAsset.equals("null")){
int flowAsset1=Integer.parseInt(bottomFlowAsset);
callableStmt.setDouble(8, flowAsset1);
}
else callableStmt.setDouble(8, -1);

if(!topFlowAsset.equals("null")){
int flowAsset2=Integer.parseInt(topFlowAsset);
callableStmt.setDouble(9, flowAsset2);
}
else callableStmt.setDouble(9, -1);

if(!bottomExpense.equals("null")){
int consume1=Integer.parseInt(bottomExpense);
callableStmt.setDouble(10, consume1);
}
else callableStmt.setDouble(10, -1); if(!topExpense.equals("null")){
int consume2=Integer.parseInt(topExpense);
callableStmt.setDouble(11, consume2);
}
else callableStmt.setDouble(11, -1);

if(!bottomDeposit.equals("null")){
int assetnumber1=Integer.parseInt(bottomDeposit);
callableStmt.setDouble(12, assetnumber1);
}
else callableStmt.setDouble(12, -1);

if(!topDeposit.equals("null")){
int assetnumber2=Integer.parseInt(topDeposit);
callableStmt.setDouble(13, assetnumber2);
}
else callableStmt.setDouble(13, -1);

if(!bottomLoan.equals("null")){
int debtvalue1=Integer.parseInt(bottomLoan);
callableStmt.setDouble(14, debtvalue1);
}
else callableStmt.setDouble(14, -1);

if(!topLoan.equals("null")){
int debtvalue2=Integer.parseInt(topLoan);
callableStmt.setDouble(15, debtvalue2);
}
else callableStmt.setDouble(15, -1);

callableStmt.setString(16, fundId);
callableStmt.setString(17, productId);
callableStmt.setString(18, riskLevel);
callableStmt.setString(19, valueLevel);
callableStmt.registerOutParameter(20, oracle.jdbc.OracleTypes.CURSOR);
callableStmt.execute();

Answer 2

代码有点错误，重新贴一下
try {
callableStmt = connection.prepareCall("{call search_proc(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}");
callableStmt.setString(1, customerId);
callableStmt.setString(2, customerName);
callableStmt.setString(3, gender); if(!smallerAge.equals("null")){
int age1=Integer.parseInt(smallerAge);
callableStmt.setInt(4, age1);
}
else callableStmt.setInt(4, -1);

if(!biggerAge.equals("null")){
int age2=Integer.parseInt(biggerAge);
callableStmt.setInt(5, age2);
}
else callableStmt.setInt(5, -1);

if(!bottomFixedAsset.equals("null")){
double fixedAsset1=Double.parseDouble(bottomFixedAsset);
callableStmt.setDouble(6, fixedAsset1);
}
else callableStmt.setDouble(6, -1);

if(!topFixedAsset.equals("null")){
double fixedAsset2=Double.parseDouble(topFixedAsset);
callableStmt.setDouble(7, fixedAsset2);
}
else callableStmt.setDouble(7, -1);

if(!bottomFlowAsset.equals("null")){
double flowAsset1=Double.parseDouble(bottomFlowAsset);
callableStmt.setDouble(8, flowAsset1);
}
else callableStmt.setDouble(8, -1);

if(!topFlowAsset.equals("null")){
double flowAsset2=Double.parseDouble(topFlowAsset);
callableStmt.setDouble(9, flowAsset2);
}
else callableStmt.setDouble(9, -1);

if(!bottomExpense.equals("null")){
double consume1=Double.parseDouble(bottomExpense);
callableStmt.setDouble(10, consume1);
}
else callableStmt.setDouble(10, -1); if(!topExpense.equals("null")){
double consume2=Double.parseDouble(topExpense);
callableStmt.setDouble(11, consume2);
}
else callableStmt.setDouble(11, -1);

if(!bottomDeposit.equals("null")){
double assetnumber1=Double.parseDouble(bottomDeposit);
callableStmt.setDouble(12, assetnumber1);
}
else callableStmt.setDouble(12, -1);

if(!topDeposit.equals("null")){
double assetnumber2=Double.parseDouble(topDeposit);
callableStmt.setDouble(13, assetnumber2);
}
else callableStmt.setDouble(13, -1);

if(!bottomLoan.equals("null")){
double debtvalue1=Double.parseDouble(bottomLoan);
callableStmt.setDouble(14, debtvalue1);
}
else callableStmt.setDouble(14, -1);

if(!topLoan.equals("null")){
double debtvalue2=Double.parseDouble(topLoan);
callableStmt.setDouble(15, debtvalue2);
}
else callableStmt.setDouble(15, -1);

callableStmt.setString(16, fundId);
callableStmt.setString(17, productId);
callableStmt.setString(18, riskLevel);
callableStmt.setString(19, valueLevel);
callableStmt.registerOutParameter(20, oracle.jdbc.OracleTypes.CURSOR);
callableStmt.execute();

Answer 3

-- Created on 2011-11-30 by QIUDF 
declare 
  -- Local variables here
  sqlstr VARCHAR2(2000);
begin
  -- Test statements here
  sqlstr:=' connect by rownum<10';
  sqlstr:='select rownum from dual'|| sqlstr;
  --EXECUTE immediate(sqlstr);
  dbms_output.put_line(sqlstr);
end;
--result:
select rownum from dual connect by rownum<10--把where 条件连接起来,即可执行!

Answer 4

我用execute immediate 'SELECT c1.CUSTOMER_Id,c1.CUSTOMER_NAME,c1.SEX,c1.AGE,c2.PERMANENT_ASSET,c3.FLOATING_ASSET, c1.CONSUME,c3.ASSET_NUMBER,
         c4.DEBT_VALUE,c5.FUND_ID, c6.BANK_PRODUCT_ID, c1.CUSTOMER_VALUE,c1.CUSTOMER_LEVEL 
         FROM INFO_CUSTOMER_BASIC c1,INFO_PERMANENT_ASSET c2,INFO_FLOATING_ASSET c3,INFO_CUSTOMER_DEBT c4,INFO_FUND c5,INFO_BANK_PRODUCT c6 
         where'||sqlStr解决了SQL的问题

Answer 5

callableStmt.setNull(4, OracleTypes.NUMBER);解决掉问题，结贴了