[SQL] 심지어 테이블에 존재하지 않는 IN 목록의 요소에 대한 결과를 반환

심지어 테이블에 존재하지 않는 IN 목록의 요소에 대한 결과를 반환

좀 값은 또는 테이블에 존재하지 않는 경우를 나타내는 결과 집합을 반환하는 가장 쉬운 방법을 찾기 위해 노력하고있다. 이 테이블을 고려 :

id 
------
  1
  2
  3
  7
  23

나는 ID 목록을받을거야 난 테이블에 존재하는 의미, 동일한 목록에 응답해야합니다. 목록 내가 이런 모습 얻을 경우 '1', '2', '3', '4', '8', '23', 나는 결과 세트를 생성 할 필요가있는 다음과 같다 :

id  |  status
-------------
  1 | present
  2 | present
  3 | present
  4 | missing
  8 | missing
 23 | present

지금까지 내가 뭔가 사용 UNPIVOT을 마련하기 위해 관리했습니다 :

select id, 'present' as status
from my_table
where id in ('1','2','3')
union
select subq.v as id, 'missing' as status
from (
        select v
        from
        (
          (
            select '1' v1, '2' v2, '3' v3 from dual
          )
          unpivot
          (
            v
            for x in (v1,v2,v3)
          )
        )
      ) subq
where subq.v not in
(
   select id
   from my_table 
   where id in ('1','2','3')
);

그것은 조금 이상한 보이지만,이 작업을 수행합니다. 내가 문을 준비한 JDBC로이 채울 수있는 방법을 아무 생각이 :이 문제는 '2'V2, 이중 일부에서 '3'V3, 선택 '1'V1입니다. 이 쿼리를 사용하는 함수를 호출 할 때마다 다른 ID리스트를 전달할 수 있도록 ID 목록은 고정되지 않는다.

이 작업을 수행하려면 다른 방법이 있습니까? 나는 분명 뭔가 빠진 것 같아요,하지만 난 모르겠어요 ...

(오라클 11 일)

해결법

1. ==============================

   1.는 SQL 측에서 당신은 테이블 유형을 정의하고 실제 데이터에 가입 그것을 사용할 수 있습니다, 같은 :

   는 SQL 측에서 당신은 테이블 유형을 정의하고 실제 데이터에 가입 그것을 사용할 수 있습니다, 같은 :

   create type my_array_type as table of number
   /
   
   create or replace function f42 (in_array my_array_type)
   return sys_refcursor as
     rc sys_refcursor;
   begin
     open rc for
       select a.column_value as id,
         case when t.id is null then 'missing'
           else 'present' end as status
       from table(in_array) a
       left join t42 t on t.id = a.column_value
       order by id;
   
     return rc;
   end f42;
   /
   

   SQL 바이올린 준다 당신이 직접 조회 할 수 있도록 래퍼 함수와 데모 :

           ID STATUS             
   ---------- --------------------
            1 present              
            2 present              
            3 present              
            4 missing              
            8 missing              
           23 present              
   

   자바에서 당신은 자바 배열에서 채울 테이블 유형에 따라 배열을 정의하고, 함수를 직접 호출 할 수 있습니다; 당신의 단일 매개 변수 바인드 변수는 ARRAY, 당신은 다시 그 결과는 정상으로 반복 할 수 있습니다 설정 얻을.

   자바 측의 개요로서 :

   int[] ids = { 1, 2, 3, 4, 8, 23 };
   ArrayDescriptor aDesc = ArrayDescriptor.createDescriptor("MY_ARRAY_TYPE",
     conn);
   oracle.sql.ARRAY ora_ids = new oracle.sql.ARRAY(aDesc, conn, ids);
   
   cStmt = (OracleCallableStatement) conn.prepareCall("{ call ? := f42(?) }");
   cStmt.registerOutParameter(1, OracleTypes.CURSOR);
   cStmt.setArray(2, ora_ids);
   cStmt.execute();
   rSet = (OracleResultSet) cStmt.getCursor(1);
   
   while (rSet.next())
   {
       System.out.println("id " + rSet.getInt(1) + ": " + rSet.getString(2));
   }
   

   어떤 준다 :

   id 1: present
   id 2: present
   id 3: present
   id 4: missing
   id 8: missing
   id 23: present
   

   Maheswaran Ravisankar 언급,이 통과 할 요소의 수는 있습니다; 당신은, 당신가 IN 또는 하나의 구분 된 문자열의 길이에 의해 허용 된 표현의 최대 수에 의해 제한되지 않습니다 컴파일 시간에 얼마나 많은 요소를 알고 (또는 이론적 인 최대 처리)해야하고,하지 않습니다 당신이 작성하고 여러 값을 전달하는 문자열을 분해 할 필요가 없습니다.

   ThinkJet는 지적 당신이 당신의 자신의 테이블 유형을 생성하지 않으려면, 당신은 여기에 입증 미리 정의 된 콜렉션을 사용할 수 있습니다; 주요 기능은 매개 변수의 선언으로부터 이격 같다 :

   create or replace function f42 (in_array sys.odcinumberlist)
   return sys_refcursor as
   ...    
   

   래퍼 기능은 약간 다르게 배열을 채 웁니다하지만 자바에 당신은이 라인을 변경해야합니다 :

   ArrayDescriptor aDesc =
     ArrayDescriptor.createDescriptor("SYS.ODCINUMBERLIST", conn );
   

   (ThinkJet는 지적!)이 아니라 수단을 사용하여이 함수를 정의하지 않고 원래의 독립 실행 형 쿼리를 실행할 수 있습니다 :

   select a.column_value as id,
   case when t.id is null then 'missing'
   else 'present' end as status
   from table(sys.odcinumberlist(1, 2, 3, 4, 8, 23)) a
   left join t42 t on t.id = a.column_value
   order by id;
   

   (SQL 바이올린).

   그리고 그 수단은 자바에서 직접 쿼리를 호출 할 수 있습니다 :

   int[] ids = { 1, 2, 3, 4, 8, 23 };
   ArrayDescriptor aDesc = ArrayDescriptor.createDescriptor("SYS.ODCINUMBERLIST", conn );
   oracle.sql.ARRAY ora_ids = new oracle.sql.ARRAY(aDesc, conn, ids);
   
   sql = "select a.column_value as id, "
       + "case when t.id is null then 'missing' "
       + "else 'present' end as status "
       + "from table(?) a "
       + "left join t42 t on t.id = a.column_value "
       + "order by id";
   pStmt = (OraclePreparedStatement) conn.prepareStatement(sql);
   pStmt.setArray(1, ora_ids);
   rSet = (OracleResultSet) pStmt.executeQuery();
   
   while (rSet.next())
   {
       System.out.println("id " + rSet.getInt(1) + ": " + rSet.getString(2));
   }
   

   당신이 선호 할 수도 ...있다.

   실제로 문자열을 전달하는 경우 미리 정의 된 ODCIVARCHAR2LIST 유형도있다 - 원래 코드가 숫자를 포함하더라도 문자열을 작동하는 것 같군, 그렇게하지 않도록 당신이 정말로 필요로한다.

   이러한 유형이 VARRAY로 정의되어 있기 때문에 자신의 테이블 제거합니다 제한이 정의하면서 (32767)는, 32K 값으로 제한된다 하지만 분명히 유일한 문제는 값이 많이 통과하는 경우 있음.

2. ==============================

   2.SQL 바이올린

   SQL 바이올린

   오라클 11g R2 스키마 설정 :

   create table IDs (id) AS
               SELECT 1 FROM DUAL
     UNION ALL SELECT 2 FROM DUAL
     UNION ALL SELECT 3 FROM DUAL
     UNION ALL SELECT 7 FROM DUAL
     UNION ALL SELECT 23 FROM DUAL
   /
   

   쿼리 1 :

   입력 후 숫자의 목록이 포함 된 문자열로 ID와 행으로 문자열을 분할 계층 적 쿼리 및 정규 표현식을 사용 :

   WITH input AS (
    SELECT '1,2,3,4,8,23' AS input FROM DUAL
   ),
   split_inputs AS (
    SELECT TO_NUMBER( REGEXP_SUBSTR( input, '\d+', 1, LEVEL ) ) AS id
    FROM input
    CONNECT BY LEVEL <= REGEXP_COUNT( input, '\d+' )
   )
   SELECT s.id,
    CASE WHEN i.id IS NULL THEN 'Missing' ELSE 'Present' END AS status
   FROM split_inputs s
    LEFT OUTER JOIN
    IDs i
    ON ( s.id = i.id )
   ORDER BY
    s.id
   

   결과 :

   | ID |  STATUS |
   |----|---------|
   |  1 | Present |
   |  2 | Present |
   |  3 | Present |
   |  4 | Missing |
   |  8 | Missing |
   | 23 | Present |
   

from https://stackoverflow.com/questions/21033216/returning-result-even-for-elements-in-in-list-that-dont-exist-in-table by cc-by-sa and MIT license