17,086
社区成员
发帖
与我相关
我的任务
分享关于ORACLE字符串拼接的问题
问题表述：
多项联合查询：
比如：踢实况的时候买球员需要搜索：
搜索条件有年龄范围，身高范围，各项指数的范围等等
我只选择年龄和综合评价范围，其余为空，在存储过程中，为空的条件就不拼接字符串了,就把这两个条件拼到条件字符串里面。
最后select [item1][item2][item3] from[table1][table2] where sqlStr(这里where后面的条件怎么弄，刚弄存储过程没几天，什么都不太清楚，望高手指点一下字符串拼接)
还有一个问题，条件很多为number型，在java里面，应该用setInt或者setDouble方法设置吧，但是知识有限，貌似null值只能用setString方法。有人说把控制设为特殊符号或者-1，但是比较又成了一个问题，number型和-1无法直接比较。
求高手解惑。
代码如下：
多项联合查询：
比如：踢实况的时候买球员需要搜索：
搜索条件有年龄范围，身高范围，各项指数的范围等等
我只选择年龄和综合评价范围，其余为空，在存储过程中，为空的条件就不拼接字符串了,就把这两个条件拼到条件字符串里面。
最后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;
/
...全文
请发表友善的回复…
发表回复
jdsnhan 2011-12-01
- 打赏
- 举报
亦或者自定义个类型表，将要搜索判断的东西写进去，免得里面拼接那么多次
Programmer99 2011-11-30
- 打赏
- 举报
代码有点错误，重新贴一下
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();Programmer99 2011-11-30
- 打赏
- 举报
附上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();Programmer99 2011-11-30
- 打赏
- 举报
callableStmt.setNull(4, OracleTypes.NUMBER);解决掉问题，结贴了
cosio 2011-11-30
- 打赏
- 举报
-- 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 条件连接起来,即可执行!
Programmer99 2011-11-30
- 打赏
- 举报
我用
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
