Hits: 327

在 oracle 的 in 中使用長字串搜尋

本文紀錄如何將字串陣列 (list of strings) 轉為長字串 (long string) 放入 cx_Oracle 的參數中進行查詢。

在 client 搜尋

透過 DBeaver 進行下列查詢

SELECT * FROM (
    SELECT TO_CHAR(ROWNUM) ID
    FROM DUAL
    CONNECT BY ROWNUM <= 10
)
WHERE ID IN (:IDS)

參數 IDS 輸入 '1', '2'

查詢結果

在 python 中以 cx_Oracle 進行查詢

同樣的查詢透過 cx_Oracle 在 python 中進行查詢，會查出空值。

import cx_Oracle

conn = cx_Oracle.connect(user, password, dsn, encoding='UTF-8')
cursor = conn.cursor()
sql_str = """
    SELECT * FROM (
        SELECT TO_CHAR(ROWNUM) ID
        FROM DUAL
        CONNECT BY ROWNUM <= 10
    )
    WHERE ID IN (:IDS)
"""
params = {
    'IDS': "'1', '2'"
}

result = cursor.execute(sql_str, params).fetchall()
print(result)

查詢結果為空陣列，代表查不到資料

解法

會有這樣的原因是 cx_Oracle 在 parsing parameter 的時候，只能接受單一字串，不能接受字串串列 (list of strings) ，所以要把 lsit of strings 變成 長字串 (long string) 並透過 cx_Oracle 進行解析

解法1: 使用 INSTR > 0 進行查詢

• 使用 INSTR 進行查詢，用法為 {INSTR} (string , substring [, position [, occurrence]]) ，此函數的目標是取得 substring 在 string 中的位置
• 若 INSTR > 0 則代表 substr 有在 string 中出現

import cx_Oracle

conn = cx_Oracle.connect(user, password, dsn, encoding='UTF-8')
cursor = conn.cursor()
sql_str = """
    SELECT * FROM (
        SELECT TO_CHAR(ROWNUM) ID
        FROM DUAL
        CONNECT BY ROWNUM <= 10
    )
    WHERE INSTR(:IDS, ID) > 0
"""
params = {
    'IDS': '1,2'  # 從 list of strings 變成 long string
}

result = cursor.execute(sql_str, params).fetchall()
print(result)

查詢結果正確

解法2: 使用 REGEXP_STR 與 CONNECT BY LEVEL 進行查詢

import cx_Oracle

conn = cx_Oracle.connect(user, password, dsn, encoding='UTF-8')
cursor = conn.cursor()
sql_str = """
    WITH GET_ID AS(
        SELECT 
            REGEXP_SUBSTR(:IDS, '[^,]+', 1, LEVEL) IDS
        FROM DUAL
        CONNECT BY LEVEL <= LENGTH(:IDS) - LENGTH(REPLACE(:IDS, ',', '')) + 1
    )
    SELECT A.* FROM (
        SELECT TO_CHAR(ROWNUM) ID
        FROM DUAL
        CONNECT BY ROWNUM <= 10
    ) A
    INNER JOIN GET_ID B ON A.ID = B.IDS
"""
params = {
    'IDS': '1,2'  # 從 list of strings 變成 long string
}

result = cursor.execute(sql_str, params).fetchall()
print(result)

查詢結果正確

結論

• INSTR 的方法比較好閱讀，跟透過 client 下的語法比較接近 (SELECT * FROM DUAL WHERE ID IN ('1', '2'))，日後維護上也比較方便，但缺點是因為沒使用 index 所以效能比較差。
• REGEXP_SUBSTR 與 CONNECT BY LEVEL 的寫法因為有使用 index 所以效能比較好，但缺點是指令比較長也比較難維護，而且如果 parameter 有兩個，組成的 temp table 在 join 上會有問題。