CTE에 정의된 값 목록 작성

CTE에 정의된 값 목록 작성

하드 코딩된 값 목록에 대한 CTE를 구축할 수 있는 방법이 있습니까?예를 들어, 알려진 ID 목록(예: 101,102,105,200...)이 있습니다. ID라는 하나의 열로 CTE를 만들 수 있는데 모든 ID 값이 쿼리에서 하드 코딩되어 있습니다. 그런데 Oracle에서 이 쿼리를 실행해야 합니다.감사합니다!

편집: 이전에 권장한 솔루션은 MSSQL에만 적용됩니다.따라서 Oracle 솔루션을 추가합니다.저는 아래의 원본 답변을 지키고 있습니다.

저는 임시 테이블을 사용하는 해결책을 하나 더 생각했습니다. (그래도 저스틴 케이브가 제공한 해결책이 조금 더 나은 것 같지만)

다음과 같이 표시됩니다.

CREATE GLOBAL TEMPORARY TABLE temp_ids
   (id INT)
   ON COMMIT PRESERVE ROWS;

INSERT INTO ids (id) VALUES (101);
INSERT INTO ids (id) VALUES (102);
INSERT INTO ids (id) VALUES (103);

이 솔루션은 Oracle 데이터베이스에 적합해야 합니다.

아래 원답

---

비슷한 문제가 발생하여 솔루션을 소개합니다(댓글에 언급된 Oracle DB에서는 작동하지 않지만 MSSQL에서만 작동함).

WITH cte AS (
    SELECT * FROM (
        VALUES
            (1, 2, 3, ...),
            (2, 3, 4, ...)
        ) AS a (col1, col2, col3, ...)
    )
INSERT INTO ...

이것이 도움이 되길 바랍니다 :)

당신은 다음과 같은 것을 할 수 있습니다.

WITH cte AS (
  SELECT 101 id FROM dual UNION ALL
  SELECT 102 FROM dual UNION ALL
  SELECT 105 FROM dual UNION ALL
  SELECT 200 FROM dual UNION ALL
  ...
)

그러나 실제로 수행하려는 작업에 따라 컬렉션을 선언하고 해당 컬렉션을 사용할 수도 있습니다(쉼표로 구분된 문자열을 구문 분석하는 기능이 있는지 여부에 관계없이).

CREATE TYPE num_tbl
    AS TABLE OF NUMBER;

WITH cte AS (
  SELECT column_value
    FROM TABLE( num_tbl( 101, 102, 105, 200 ))
)

이에 대해 컬렉션 유형을 바인딩 매개 변수로 제출하거나 쿼리 내에 만들 수 있습니다.

저스틴 케이브가 제안한 것처럼 자신만의 컬렉션 유형을 만들 수 있지만,SYS에는 다음과 같이 기본적으로 정의된 기본 유형이 포함되어 있습니다.SYS.ODCIDATELIST(의 경우)DATE),SYS.ODCINUMBERLIST(의 경우)NUMBER/NUMERIC),SYS.ODCIVARCHAR2LIST(의 경우)VARCHAR2최대 4000자), 내부용으로 특별히 제작되지 않은 것으로 보이는 것들 중에서.

정수 ID를 사용하고 있으므로 기본 제공 숫자 모음이 올바르게 작동합니다.

-- column_value and table() work similarily to UNNEST() in Postgres
SELECT column_value as selected_id FROM TABLE(
    SYS.ODCINUMBERLIST(101, 102, 105, 200)
)

호스트 언어가 지원하는 내용에 따라 대신 목록을 매개 변수로 보낼 수도 있습니다.예를 들어 Python에서 cx_Oracle을 직접 사용하는 경우:

import cx_Oracle

query = 'select column_value as selected_id FROM TABLE(:id_list)'

conn = cx_Oracle.connect('user', 'hunter2', '//192.0.2.5:1521/mydb')

OdciNumberList = conn.gettype("SYS.ODCINUMBERLIST")
id_list = OdciNumberList.newobject()

id_list.extend([101, 102, 105, 200])

cur = conn.cursor()
res = cur.execute(query, id_list=id_list )
res.fetchall() # [(101,), (102,), (105,), (200,)]

그럼 CTE로 포장하시면 됩니다.

언급URL : https://stackoverflow.com/questions/22850382/build-a-list-of-defined-values-into-cte