Where a user gives a set of inputs from one table, e.g. "request_table" a:
| User Input | Value | Field Name in Database |
|---|---|---|
| Product | Deposit | product_type |
| Deposit Term (months) | 24 | term |
| Deposit Amount | 200,000 | amount |
| Customer Type | Charity | customer_type |
| Existing Customer | Y | existing_customer |
Would like to use the product selection to pick out SQL scripts embedded in a "pricing_table" b, where the price is made up of components, each of which are affected by one or more of the above inputs:
| Product | Grid | Measures | Value1 | Value1Min | Value1Max | Value2 | Value2Min | Value2Max | Price |
|---|---|---|---|---|---|---|---|---|---|
| Deposit | Term_Amount | a.term>=b.value1min and a.term<b.value2 max and a.amount>=b.value2min and a.amount<b.value2max | 0 | 12 | 0 | 100000 | 1 | ||
| Deposit | Term_Amount | a.term>=b.value1min and a.term<b.value2 max and a.amount>=b.value2min and a.amount<b.value2max | 12 | 36 | 0 | 100000 | 2 | ||
| Deposit | Term_Amount | a.term>=b.value1min and a.term<b.value2 max and a.amount>=b.value2min and a.amount<b.value2max | 36 | 9999 | 0 | 100000 | 3 | ||
| Deposit | Term_Amount | a.term>=b.value1min and a.term<b.value2 max and a.amount>=b.value2min and a.amount<b.value2max | 0 | 12 | 100000 | 500000 | 1.1 | ||
| Deposit | Term_Amount | a.term>=b.value1min and a.term<b.value2 max and a.amount>=b.value2min and a.amount<b.value2max | 12 | 36 | 100000 | 500000 | 2.1 | ||
| Deposit | Term_Amount | a.term>=b.value1min and a.term<b.value2 max and a.amount>=b.value2min and a.amount<b.value2max | 36 | 9999 | 100000 | 500000 | 3.1 | ||
| Deposit | Term_Amount | a.term>=b.value1min and a.term<b.value2 max and a.amount>=b.value2min and a.amount<b.value2max | 0 | 12 | 500000 | 99999999 | 1.2 | ||
| Deposit | Term_Amount | a.term>=b.value1min and a.term<b.value2 max and a.amount>=b.value2min and a.amount<b.value2max | 12 | 36 | 500000 | 99999999 | 2.2 | ||
| Deposit | Term_Amount | a.term>=b.value1min and a.term<b.value2 max and a.amount>=b.value2min and a.amount<b.value2max | 36 | 9999 | 500000 | 99999999 | 3.2 | ||
| Deposit | Customer_Type | a.customer_type=b.value1 | Personal | 0 | |||||
| Deposit | Customer_Type | a.customer_type=b.value1 | Charity | 0.1 | |||||
| Deposit | Customer_Type | a.customer_type=b.value1 | Business | -0.1 | |||||
| Deposit | Existing_Customer | a.existing_customer=b.value1 | Y | 0.1 | |||||
| Deposit | Existing_Customer | a.existing_customer=b.value1 | N | 0 |
Where the query is: select distinct measures from pricing_table where product=(select product_type from request_table). This gives multiple rows where SQL logic is held. Would like to run this SQL logic in a LOOP, e.g.: select b.* from pricing_table b where :measures This would return all rows where the specific metrics are matched. Doing it this way as the exact columns in the input can grow to hundreds, so don't want a really wide table. Any help appreciated thanks.
I've creating tables but am unsure how to loop the measures, and apply the values from that field in a looped query thanks.
CodePudding user response：
In a PL/SQL pipelined function, you can build the SQL query and open a cursor on it, loop on the results and PIPE the rows.