例如，如果我有一個像
my_string := ' ''a'' = ''a'' and 1 > 0 '
我可以在程式/函式中做這樣的事情來評估它
execute immediate 'select CASE WHEN(' || my_string || ') THEN 1 ELSE 0 END from dual'
但是有沒有辦法在不使用立即執行的情況下做到這一點？有沒有辦法像在查詢中撰寫的那樣評估字串？
我想這樣做是因為我在表中有通用條件，例如“COD1 like '%x%' OR COD2 = 'Z'”。所以我用這個字串做了一些替換，但然后我想讓它們用 cotraint 評估不使用用戶定義的函式，所以沒有“立即執行”
uj5u.com熱心網友回復：
是的，但是……您實際上必須撰寫自己的運算式決議器：
如果你有這些表：
CREATE TABLE table_name (a, b, c, d) AS
SELECT 'x', 'x', 'x', 'x' FROM DUAL UNION ALL
SELECT 'w', 'x', 'y', 'z' FROM DUAL;
CREATE TABLE filters (filter) AS
SELECT 'a = b AND c <= d' FROM DUAL UNION ALL
SELECT 'a < b AND b < c AND c < d' FROM DUAL UNION ALL
SELECT 'a < ''y''' FROM DUAL UNION ALL
SELECT 'c LIKE ''%y%''' FROM DUAL;
并且您想將其filters應用于table_name然后，從 Oracle 12 開始，您可以使用：
WITH split_filters ( id, filter, left_operand, operator, right_operand, expr, num_expr ) AS (
SELECT ROWID,
filter,
REGEXP_SUBSTR(
filter,
'(''([^'']|'''')*''|[A-Z][A-Z0-9_]*)' -- left_operand
|| '\s*([<>!]?=|[<>]|LIKE)' -- operator
|| '\s*(''([^'']|'''')*''|[A-Z][A-Z0-9_]*)' -- right_operand
|| '\s*($|\sAND\s )', -- expression concatenator
1,
1,
'i',
1
),
REGEXP_SUBSTR(
filter,
'(''([^'']|'''')*''|[A-Z][A-Z0-9_]*)' -- left_operand
|| '\s*([<>!]?=|[<>]|LIKE)' -- operator
|| '\s*(''([^'']|'''')*''|[A-Z][A-Z0-9_]*)' -- right_operand
|| '\s*($|\sAND\s )', -- expression concatenator
1,
1,
'i',
3
),
REGEXP_SUBSTR(
filter,
'(''([^'']|'''')*''|[A-Z][A-Z0-9_]*)' -- left_operand
|| '\s*([<>!]?=|[<>]|LIKE)' -- operator
|| '\s*(''([^'']|'''')*''|[A-Z][A-Z0-9_]*)' -- right_operand
|| '\s*($|\sAND\s )', -- expression concatenator
1,
1,
'i',
4
),
1,
REGEXP_COUNT(
filter,
'(''([^'']|'''')*''|[A-Z][A-Z0-9_]*)' -- left_operand
|| '\s*([<>!]?=|[<>]|LIKE)' -- operator
|| '\s*(''([^'']|'''')*''|[A-Z][A-Z0-9_]*)' -- right_operand
|| '\s*($|\sAND\s )', -- expression concatenator
1,
'i'
)
FROM filters
UNION ALL
SELECT id,
filter,
REGEXP_SUBSTR(
filter,
'(''([^'']|'''')*''|[A-Z][A-Z0-9_]*)' -- left_operand
|| '\s*([<>!]?=|[<>]|LIKE)' -- operator
|| '\s*(''([^'']|'''')*''|[A-Z][A-Z0-9_]*)' -- right_operand
|| '\s*($|\sAND\s )', -- expression concatenator
1,
expr 1,
'i',
1
),
REGEXP_SUBSTR(
filter,
'(''([^'']|'''')*''|[A-Z][A-Z0-9_]*)' -- left_operand
|| '\s*([<>!]?=|[<>]|LIKE)' -- operator
|| '\s*(''([^'']|'''')*''|[A-Z][A-Z0-9_]*)' -- right_operand
|| '\s*($|\sAND\s )', -- expression concatenator
1,
expr 1,
'i',
3
),
REGEXP_SUBSTR(
filter,
'(''([^'']|'''')*''|[A-Z][A-Z0-9_]*)' -- left_operand
|| '\s*([<>!]?=|[<>]|LIKE)' -- operator
|| '\s*(''([^'']|'''')*''|[A-Z][A-Z0-9_]*)' -- right_operand
|| '\s*($|\sAND\s )', -- expression concatenator
1,
expr 1,
'i',
4
),
expr 1,
num_expr
FROM split_filters
WHERE expr < num_expr
)
SELECT *
FROM table_name t
CROSS JOIN LATERAL (
SELECT MAX(filter) AS filter
FROM (
SELECT id,
filter,
CASE
WHEN UPPER(left_operand) = 'A' THEN t.a
WHEN UPPER(left_operand) = 'B' THEN t.b
WHEN UPPER(left_operand) = 'C' THEN t.c
WHEN UPPER(left_operand) = 'D' THEN t.d
WHEN left_operand LIKE '''%''' THEN REPLACE(SUBSTR(left_operand, 2, LENGTH(left_operand) - 2), '''''', '''')
END AS l_op,
operator AS op,
CASE
WHEN UPPER(right_operand) = 'A' THEN t.a
WHEN UPPER(right_operand) = 'B' THEN t.b
WHEN UPPER(right_operand) = 'C' THEN t.c
WHEN UPPER(right_operand) = 'D' THEN t.d
WHEN right_operand LIKE '''%''' THEN REPLACE(SUBSTR(right_operand, 2, LENGTH(right_operand) - 2), '''''', '''')
END AS r_op,
num_expr
FROM split_filters
)
WHERE CASE
WHEN op = '=' AND l_op = r_op THEN 1
WHEN op = '!=' AND l_op != r_op THEN 1
WHEN op = '<' AND l_op < r_op THEN 1
WHEN op = '>' AND l_op > r_op THEN 1
WHEN op = '<=' AND l_op <= r_op THEN 1
WHEN op = '>=' AND l_op >= r_op THEN 1
WHEN op = 'LIKE' AND l_op LIKE r_op THEN 1
END = 1
GROUP BY id
HAVING COUNT(*) = MAX(num_expr)
);
哪些輸出：
一種 乙 C D 篩選 X X X X a = b AND c <= d X X X X <'y' 瓦 X 是 z a < b AND b < c AND c < d 瓦 X 是 z <'y' 瓦 X 是 z c LIKE '%y%'
db<>在這里擺弄
在 Oracle 11g 中，您可以將其重寫為：
WITH split_filters ( id, filter, left_operand, operator, right_operand, expr, num_expr ) AS (
SELECT ROWID,
filter,
REGEXP_SUBSTR(
filter,
'(''([^'']|'''')*''|[A-Z][A-Z0-9_]*)' -- left_operand
|| '\s*([<>!]?=|[<>]|LIKE)' -- operator
|| '\s*(''([^'']|'''')*''|[A-Z][A-Z0-9_]*)' -- right_operand
|| '\s*($|\sAND\s )', -- expression concatenator
1,
1,
'i',
1
),
REGEXP_SUBSTR(
filter,
'(''([^'']|'''')*''|[A-Z][A-Z0-9_]*)' -- left_operand
|| '\s*([<>!]?=|[<>]|LIKE)' -- operator
|| '\s*(''([^'']|'''')*''|[A-Z][A-Z0-9_]*)' -- right_operand
|| '\s*($|\sAND\s )', -- expression concatenator
1,
1,
'i',
3
),
REGEXP_SUBSTR(
filter,
'(''([^'']|'''')*''|[A-Z][A-Z0-9_]*)' -- left_operand
|| '\s*([<>!]?=|[<>]|LIKE)' -- operator
|| '\s*(''([^'']|'''')*''|[A-Z][A-Z0-9_]*)' -- right_operand
|| '\s*($|\sAND\s )', -- expression concatenator
1,
1,
'i',
4
),
1,
REGEXP_COUNT(
filter,
'(''([^'']|'''')*''|[A-Z][A-Z0-9_]*)' -- left_operand
|| '\s*([<>!]?=|[<>]|LIKE)' -- operator
|| '\s*(''([^'']|'''')*''|[A-Z][A-Z0-9_]*)' -- right_operand
|| '\s*($|\sAND\s )', -- expression concatenator
1,
'i'
)
FROM filters
UNION ALL
SELECT id,
filter,
REGEXP_SUBSTR(
filter,
'(''([^'']|'''')*''|[A-Z][A-Z0-9_]*)' -- left_operand
|| '\s*([<>!]?=|[<>]|LIKE)' -- operator
|| '\s*(''([^'']|'''')*''|[A-Z][A-Z0-9_]*)' -- right_operand
|| '\s*($|\sAND\s )', -- expression concatenator
1,
expr 1,
'i',
1
),
REGEXP_SUBSTR(
filter,
'(''([^'']|'''')*''|[A-Z][A-Z0-9_]*)' -- left_operand
|| '\s*([<>!]?=|[<>]|LIKE)' -- operator
|| '\s*(''([^'']|'''')*''|[A-Z][A-Z0-9_]*)' -- right_operand
|| '\s*($|\sAND\s )', -- expression concatenator
1,
expr 1,
'i',
3
),
REGEXP_SUBSTR(
filter,
'(''([^'']|'''')*''|[A-Z][A-Z0-9_]*)' -- left_operand
|| '\s*([<>!]?=|[<>]|LIKE)' -- operator
|| '\s*(''([^'']|'''')*''|[A-Z][A-Z0-9_]*)' -- right_operand
|| '\s*($|\sAND\s )', -- expression concatenator
1,
expr 1,
'i',
4
),
expr 1,
num_expr
FROM split_filters
WHERE expr < num_expr
),
operand_substitutions (t_id, f_id, a, b, c, d, filter, l_op, op, r_op, num_expr) AS (
SELECT t.ROWID,
f.id,
t.a,
t.b,
t.c,
t.d,
filter,
CASE
WHEN UPPER(left_operand) = 'A' THEN t.a
WHEN UPPER(left_operand) = 'B' THEN t.b
WHEN UPPER(left_operand) = 'C' THEN t.c
WHEN UPPER(left_operand) = 'D' THEN t.d
WHEN left_operand LIKE '''%''' THEN REPLACE(SUBSTR(left_operand, 2, LENGTH(left_operand) - 2), '''''', '''')
END,
operator,
CASE
WHEN UPPER(right_operand) = 'A' THEN t.a
WHEN UPPER(right_operand) = 'B' THEN t.b
WHEN UPPER(right_operand) = 'C' THEN t.c
WHEN UPPER(right_operand) = 'D' THEN t.d
WHEN right_operand LIKE '''%''' THEN REPLACE(SUBSTR(right_operand, 2, LENGTH(right_operand) - 2), '''''', '''')
END,
num_expr
FROM split_filters f
CROSS JOIN table_name t
)
SELECT MAX(a) AS a,
MAX(b) AS b,
MAX(c) AS c,
MAX(d) AS d,
MAX(filter) AS filter
FROM operand_substitutions
WHERE CASE
WHEN op = '=' AND l_op = r_op THEN 1
WHEN op = '!=' AND l_op != r_op THEN 1
WHEN op = '<' AND l_op < r_op THEN 1
WHEN op = '>' AND l_op > r_op THEN 1
WHEN op = '<=' AND l_op <= r_op THEN 1
WHEN op = '>=' AND l_op >= r_op THEN 1
WHEN op = 'LIKE' AND l_op LIKE r_op THEN 1
END = 1
GROUP BY t_id, f_id
HAVING COUNT(*) = MAX(num_expr);
db<>在這里擺弄
uj5u.com熱心網友回復：
沒辦法，據我所知。這就是動態 SQL（即execute immediate）的用途。
例如，如果您只將一個條件（為簡單起見）放入表中：
SQL> select * from test;
MY_STRING
---------------------
'a' = 'a' and 1 > 0
并將其交叉連接到另一個表（因為，我希望在始終滿足該條件時回傳所有內容），您會收到錯誤訊息：
SQL> select *
2 from dept d cross join test t
3 where t.mystring;
where t.mystring
*
ERROR at line 3:
ORA-00920: invalid relational operator
while - 如果條件字面上放入where子句中，則它起作用：
SQL> select *
2 from dept d cross join test t
3 where 'a' = 'a' and 1 > 0;
DEPTNO DNAME LOC MY_STRING
---------- -------------- ------------- ---------------------
10 ACCOUNTING NEW YORK 'a' = 'a' and 1 > 0
20 RESEARCH DALLAS 'a' = 'a' and 1 > 0
30 SALES CHICAGO 'a' = 'a' and 1 > 0
40 OPERATIONS BOSTON 'a' = 'a' and 1 > 0
SQL>
所以，恐怕是動態 SQL。
uj5u.com熱心網友回復：
有沒有辦法在不使用立即執行的情況下做到這一點
您可以使用替換變數作為替代方法，例如
SQL> SELECT CASE WHEN(&str) THEN 1 ELSE 0 END
2 FROM dual;
CASEWHEN('A'='A'AND1>0)THEN1EL
------------------------------
1
其中，'a' = 'a' and 1 > 0對于進入&str時提示
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/367117.html
上一篇：加速對長字串中的字符進行迭代
下一篇：從字串中洗掉所有標點符號