如何從 oracle plsql 中的表列中獲取 where 子句條件。
例如表中的資料 Condition 1.sourceSystemId = 'SN' 2.AND(coverageType='AD',amountType1='PREMIUM',premiumFrequency='REGULAR',yearOfPremium='1')
例如查詢：select * from xyz where rule='abc' and "sourceSystemId = 'SN'"
select * from xyz where rule='abc' AND(coverageType='AD',amountType1='PREMIUM',premiumFrequency='REGULAR',yearOfPremium='1')
uj5u.com熱心網友回復：
不完全確定你在這里問什么，但我想
select * from xyz where rule='abc' AND(coverageType='AD',amountType1='PREMIUM',premiumFrequency='REGULAR',yearOfPremium='1')
會成為
select * from xyz
where rule='abc'
AND coverageType='AD'
and amountType1='PREMIUM'
and premiumFrequency='REGULAR'
and yearOfPremium='1'
uj5u.com熱心網友回復：
我想你想要類似的東西：
DECLARE
l_query VARCHAR2(2000) := 'select * from xyz where rule=''abc''';
l_result xyz%ROWTYPE;
l_cursor SYS_REFCURSOR;
BEGIN
dbms_output.put_line(l_query);
FOR clause IN (SELECT condition
FROM conditions)
LOOP
l_query := l_query||' AND '||clause.condition;
END LOOP;
OPEN l_cursor FOR l_query;
LOOP
FETCH l_cursor INTO l_result;
EXIT WHEN l_cursor%NOTFOUND;
..
-- your processing
END LOOP;
CLOSE l_cursor;
END;
uj5u.com熱心網友回復：
這是 SQL 解決方案的示例。我使用了第一個和最后一個條件，但你可以得到它們......
WITH
xyz As
(
Select 1 "ID", 'abc' "RULE", 'AD' "COVERAGETYPE", 'PREMIUM' "AMOUNTTYPE1", 'REGULAR' "PREMIUMFREQUENCY", '1' "YEAROFPREMIUM" From Dual
UNION
Select 2 "ID", 'abc' "RULE", 'BF' "COVERAGETYPE", 'ORDINARY' "AMOUNTTYPE1", 'EXTRA' "PREMIUMFREQUENCY", '2' "YEAROFPREMIUM" From Dual
UNION
Select 3 "ID", 'abc' "RULE", 'AD' "COVERAGETYPE", 'PREMIUM' "AMOUNTTYPE1", 'REGULAR' "PREMIUMFREQUENCY", '1' "YEAROFPREMIUM" From Dual
),
conditions As
(
SELECT UPPER('coverageType=AD,amountType1=PREMIUM,premiumFrequency=REGULAR,yearOfPremium=1') "CND" From Dual
)
SELECT
x.ID, x.RULE, x.COVERAGETYPE, x.AMOUNTTYPE1, x.PREMIUMFREQUENCY, x.YEAROFPREMIUM
FROM
xyz x
INNER JOIN
conditions c ON(1=1)
WHERE
x.RULE = 'abc' And
x.COVERAGETYPE = CASE WHEN InStr(c.CND || ',', 'COVERAGETYPE=') = 0 THEN x.COVERAGETYPE
ELSE SubStr(SubStr(c.CND || ',', InStr(c.CND || ',', 'COVERAGETYPE=') Length('COVERAGETYPE=')), 1, InStr(SubStr(c.CND || ',', InStr(c.CND || ',', 'COVERAGETYPE=') Length('COVERAGETYPE=') 1), ',')) END And
x.YEAROFPREMIUM = CASE WHEN InStr(c.CND || ',', 'YEAROFPREMIUM=') = 0 THEN x.YEAROFPREMIUM
ELSE SubStr(SubStr(c.CND || ',', InStr(c.CND || ',', 'YEAROFPREMIUM=') Length('YEAROFPREMIUM=')), 1, InStr(SubStr(c.CND || ',', InStr(c.CND || ',', 'YEAROFPREMIUM=') Length('YEAROFPREMIUM=') 1), ',')) END
結果：
ID RULE COVERAGETYPE AMOUNTTYPE1 PREMIUMFREQUENCY YEAROFPREMIUM
1 abc AD PREMIUM REGULAR 1
3 abc AD PREMIUM REGULAR 1
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/476282.html
上一篇：無法在MySQL中使用帶有大量特殊字符的like查詢資料
下一篇：物件中的物件元素