Oracle 多重查询问题

我有一个Argos报告，它接受一个查询，并根据这些条目输出一个报告。我的问题是有5个可能的查询。我尝试使用和/或 (我已经尝试过CASE函数) 在其条目上提取数据库。查询是; Receiver # 、User_ID、金额、代码和日期范围。它可以是仅收据，也可以是任何其他查询的组合。以下是一个样本

<选择不同的TBB.RECEIPT_NUMBER作为 “收据”，
TBB_USER作为 “ID”，
TBB_NAME为 “名称”，
TBB_DESC作为 “描述”，
TBR_TRANS_DATE为 “Trans_Date”，
TBR_FEED_DOC_CODE为 "Feed_Code"，
Tbr _ 金额为 “金额”，
TBR_PAY_DETAIL_CODE为 “Pay_Code”，
TBR_CHARGE_DETAIL_CODE为 “Charge_Code”
从tbb，
tbr
其中: f1_parm_BT_runButton = 1和
(TBB_RECEIPT_NUMBER =: f1_parm_eb_receip_number和: f1_parm_eb_receipment <> 0)
或者 (tbb_user = :f1_parm_eb_bannerid1和tbb_user = tbr_user和: f1_parm_eb_bannerid1 <> '')
或者 (tbr_feed_doc_code = :f1_parm_EB_FeedCode和tbr_receipt_number = TBB_RECEIPT_NUMBER和: f1_parm_EB_FeedCode<> ')
或者 (tbr_amount = :f1_parm_eb_amount 1和tbr_receip_number = tbb_receip_number和: f1_parm_eb_amount1 <> 0)
或 (tbr_pay_detail_code = :f1_parm_EB_AcctCode和tbr_receip_number = tbb_receip_number和: f1_parm_EB_AcctCode <> ')
或者 (tbr_charge_detail_code = :f1_parm_EB_AcctCode和tbr_receip_number = tbb_receip_number和: f1_parm_EB_AcctCode <> ')
或 (trunc(tbr_trans_date) 之间的trunc(to_date(:f1_parm_DT_startdate)，'DDD') 和trunc(to_date(:f1_parm_DT_enddate)，'DDD') 和trunc(tbb_activity_date) = trunc(tbr_trans_date)
)>
有什么建议吗？

通用的一刀切查询通常最终对所有排列都表现不佳。

这是动态SQL是您的朋友的情况之一。

所以你有一个概念，你:

-将传递的参数收集到绑定变量数组中
-构建SQL零星时尚
-根据您拥有的绑定数量执行它

type bindlist is table of varchar2(100) index by pls_integer;
bind bindlist;

l_sql := 
'select distinct TBB.RECEIPT_NUMBER as "Receipt",
TBB_USER as "ID",
TBB_NAME as "Name",
TBB_DESC as "Description",
TBR_TRANS_DATE as "Trans_Date",
TBR_FEED_DOC_CODE as "Feed_Code",
TBR_AMOUNT as "Amount",
TBR_PAY_DETAIL_CODE as "Pay_Code",
TBR_CHARGE_DETAIL_CODE as "Charge_Code"
from tbb,
tbr'

if :f1_parm_EB_Receipt is not null then
  l_sql := l_sql || ' and TBB_RECEIPT_NUMBER = :1';
  bind(bind.count+1) := f1_parm_EB_Receipt;
end if;

if :f1_parm_eb_bannerid1 is not null then
  l_sql := l_sql || ' and tbb_user = :2';
  bind(bind.count+1) := f1_parm_eb_bannerid1;
end if;

if :f1_parm_EB_AcctCode is not null then
  l_sql := l_sql || ' and tbr_charge_detail_code = to_number(:3)';
  bind(bind.count+1) := f1_parm_EB_Receipt;
end if;

...
...



if bind.count = 1 then
  open my_cursor for l_sql using bind(1);
elsif bind.count = 2 then
  open my_cursor for l_sql using bind(1),bind(2);
elsif bind.count = 3 then
  open my_cursor for l_sql using bind(1),bind(2),bind(3);
elsif bind.count = 4 then
  open my_cursor for l_sql using bind(1),bind(2),bind(3),bind(4);
...  
  
复制