oracle傳入一個可能為空白的參數進行查詢

標籤：

在我們資料庫的表中的某些欄位可能為空白，且傳入的查詢參數也可能為空白。

例如

select psm.mapping_id,psm.party_id,psm.vendor_id,psm.supplier_reg_id from  pos_supplier_mappings  psm where psm.mapping_id in  (3324,4520067,4520097);

 

其查詢結果集如下

MAPPING_ID	PARTY_ID	VENDOR_ID	SUPPLIER_REG_ID
3324	6092236	3761
4520067			1
4520097			29

 

大多數情況下，我們在java中拼接sql語句時候會判斷傳入的參數是否為空白，

        //組織ID        if((vendor_id != null)&&(!"".equals(vendor_id)))        {          if (clauseCount > 0) {              whereClause.append(" AND ");          }          whereClause.append("vendor_id = :");          whereClause.append(++bindCount);          parameters.addElement(vendor_id);          clauseCount++;        }        else{            if (clauseCount > 0) {                whereClause.append(" AND ");            }            whereClause.append("vendor_id  is null");            clauseCount++;        }

 

拼接出的SQL大多如下：

select psm.mapping_id,psm.party_id,psm.vendor_id,psm.supplier_reg_id from  pos_supplier_mappings  psm where psm.vendor_id is null;select psm.mapping_id,psm.party_id,psm.vendor_id,psm.supplier_reg_id from  pos_supplier_mappings  psm where psm.party_id is null;select psm.mapping_id,psm.party_id,psm.vendor_id,psm.supplier_reg_id from  pos_supplier_mappings  psm where psm.supplier_reg_id is null;

這樣的拼接是繁瑣的，並且容易出錯。

 

方案二：靈活使用nvl(param1,param2)函數。

原始SQL如下：

SELECT psm.mapping_id,       psm.party_id,       psm.vendor_id,       psm.supplier_reg_id  FROM pos_supplier_mappings psm WHERE nvl(psm.party_id,           -99) = nvl(:1,                      nvl(psm.party_id,                          -99))   AND nvl(psm.vendor_id,           -99) = nvl(:2,                      nvl(psm.vendor_id,                          -99))   AND nvl(psm.supplier_reg_id,           -99) = nvl(:3,                      nvl(psm.supplier_reg_id,                          -99))

思路，若傳入的參數為空白，那麼則與表中的空值的列恒等。

傳參形式如下：

this.setWhereClauseParam(0, PARTY_ID);this.setWhereClauseParam(1, VENDOR_ID);this.setWhereClauseParam(2, SUPPLIER_REG_ID);

註：在傳入參數的時候不能使用以下形式

String PARTY_ID = PARTY_ID+"";

此形式會導致在SQL中將空值的PARTY_ID變為字串‘null‘。

 

oracle傳入一個可能為空白的參數進行查詢