어떻게 선택적 매개 변수 SQL 쿼리를 처리하기 위해?

내가 샘플 테이블이 말 :

 id_pk  value
------------
 1       a
 2       b
 3       c

그리고 현재 배열에 하나의 행을 선택하는 쿼리가있는 샘플 PL / SQL 블록을 가지고 :

declare

  type t_table is table of myTable%rowtype;

  n_RequiredId myTable.id_pk%type := 1;  
  t_Output t_table := t_table();

begin

  select m.id_pk, m.value
    bulk collect into t_Output
    from myTable m 
   where m.id_pk = n_RequiredId;

end;

내가해야 할 것은 위의 블록에 도시 된 바와 같이, 배열로 하나의 행을 선택하는 기능을 구현하기 위해, 또는 사용자에게 실제로 입력 파라미터 n_RequiredID는,로 설정되어있는 경우, 배열에있는 모든 행을 선택하는 것 없는.

그리고, 문제는, 이러한 상황을 처리 할 수있는 가장 좋은 방법은 무엇인가?

나는이 같은 내 쿼리의 where 절을 수정 생각할 수 있습니다 :

where m.id_pk = nvl(n_RequiredId, m.id_pk);

하지만 매개 변수가 null하지 않을 경우 쿼리를 늦출 것 그 생각, 나는 카이트는이 방법에 대한 뭔가 정말 나쁜 말했다 기억한다.

또한 다음과 같은 PL / SQL 로직을 구현 생각할 수 있습니다 :

if n_RequiredId is null then 

  select m.id_pk, m.value bulk collect into t_Output from myTable m;

else

  select m.id_pk, m.value bulk collect
    into t_Output
    from myTable m
   where m.id_pk = n_RequiredId;

end if;

나는 이러한 종류의 하나 개 이상의 매개 변수가 발생한다면 너무 복잡 될 것입니다.

당신은 무엇을 나에게 조언을 것?

해결법

==============================
1.예, 다음 중 하나를 사용하여 :

예, 다음 중 하나를 사용하여 :
```
WHERE m.id_pk = NVL(n_RequiredId, m.id_pk);
WHERE m.id_pk = COALESCE(n_RequiredId, m.id_pk);
WHERE (n_RequiredId IS NULL OR m.id_pk = n_RequiredId);
```
... 스 SARGable 수 없습니다. 그들은 작동하지만 사용할 수있는 옵션의 최악을 수행합니다.

당신은 단지 ELSE 하나 개의 매개 변수의 IF를 / 가지고 별도의 경우, 맞춤형 문은 더 나은 대안이다.

그 후 다음 옵션은 동적 SQL이다. 첫 번째 예에서는 비 스 SARGable 술어를 통해 수행하지만 동적 SQL 코딩 쓸모가 없다. 동적 SQL은 여러 경로를 수용하면서 쿼리를 조정할 수 있습니다. 이 패키지에 저장 프로 시저 / 함수 내에서 바람직하게는 (매개 변수화 된 쿼리 뒤에 수행해야하므로 그러나 그것은 또한, SQL 주입 위험.
==============================
2.OMG_Ponies '와 롭 밴 Wijk의 답변이 그냥 보충입니다 완전히 정확합니다.

OMG_Ponies '와 롭 밴 Wijk의 답변이 그냥 보충입니다 완전히 정확합니다.

쉽게 바인드 변수를 사용하고 여전히 동적 SQL을 사용하여 만들 수있는 좋은 트릭이있다. 당신이 처음에 절와의 바인딩을 모두 넣을 경우, 당신은 항상 바인드 변수의 동일한 세트, 여부를 당신이 그들을 사용하려고하고 있습니다.

예를 들어, 날짜 범위와 ID를 나타내는, 당신은 세 개의 매개 변수를 말한다. 그냥 ID를 검색 할 경우,이 같은 쿼리를 함께 넣어 수 :
```
with parameters as (
     select :start_date as start_date,
            :end_date as end_date,
            :search_id as search_id
     from dual)
select * 
from your_table 
     inner join parameters
        on parameters.search_id = your_table.id;
```
당신은 ID와 날짜 범위를 검색해야하는 경우 반면에, 그것은 다음과 같을 수 있습니다 :
```
with parameters as (
     select :start_date as start_date,
            :end_date as end_date,
            :search_id as search_id
     from dual)
select * 
from your_table 
     inner join parameters
         on parameters.search_id = your_table.id
            and your_table.create_date between (parameters.start_date
                                                and parameters.end_date);
```
이는이 문제를 처리하는 방법에 대한 내내했지만 결국 결과처럼 보일 수도 것은 당신이 당신의 동적 SQL을 복잡 아무리의 PL / SQL 호출이 항상 같은 것입니다만큼 그것은 단지 그 세 개의 매개 변수를 필요로 얻을 수 있다는 것입니다 :
```
execute immediate v_SQL using v_start_date, v_end_date, v_search_id;
```
내 경험에 그것을 실제로 실행됩니다 한 줄이 있다고 확인하기 위해 약간 더 위해 복잡 SQL 건설을하는 것이 좋습니다.

==============================

3.NVL 접근 방식은 일반적으로 잘 작동합니다. 옵티마이 저는이 패턴을 인식하고 동적 계획을 구축 할 것입니다. 이 계획은 하나의 값과 NULL에 대한 전체 테이블 스캔에 대한 인덱스를 사용합니다.

NVL 접근 방식은 일반적으로 잘 작동합니다. 옵티마이 저는이 패턴을 인식하고 동적 계획을 구축 할 것입니다. 이 계획은 하나의 값과 NULL에 대한 전체 테이블 스캔에 대한 인덱스를 사용합니다.

샘플 테이블과 데이터

drop table myTable;
create table myTable(
    id_pk number,
    value varchar2(100),
    constraint myTable_pk primary key (id_pk)
);

insert into myTable select level, level from dual connect by level <= 100000;
commit;

다른 조건으로 실행

--Execute predicates that return one row if the ID is set, or all rows if ID is null. 
declare
    type t_table is table of myTable%rowtype;
    n_RequiredId myTable.id_pk%type := 1;  
    t_Output t_table := t_table();
begin
    select /*+ SO_QUERY_1 */ m.id_pk, m.value
    bulk collect into t_Output
    from myTable m
    where m.id_pk = nvl(n_RequiredId, m.id_pk);

    select /*+ SO_QUERY_2 */ m.id_pk, m.value
    bulk collect into t_Output
    from myTable m
    where m.id_pk = COALESCE(n_RequiredId, m.id_pk);

    select /*+ SO_QUERY_3 */ m.id_pk, m.value
    bulk collect into t_Output
    from myTable m
    where (n_RequiredId IS NULL OR m.id_pk = n_RequiredId);
end;
/

실행 계획을 얻으십시오

select sql_id, child_number
from gv$sql
where lower(sql_text) like '%so_query_%'
    and sql_text not like '%QUINE%'
    and sql_text not like 'declare%';

select * from table(dbms_xplan.display_cursor(sql_id => '76ucq3bkgt0qa', cursor_child_no => 1, format => 'basic'));
select * from table(dbms_xplan.display_cursor(sql_id => '4vxf8yy5xd6qv', cursor_child_no => 1, format => 'basic'));
select * from table(dbms_xplan.display_cursor(sql_id => '457ypz0jpk3np', cursor_child_no => 1, format => 'basic'));

COALESCE 및 IS NULL OR에 대한 잘못된 계획

EXPLAINED SQL STATEMENT:
------------------------
SELECT /*+ SO_QUERY_2 */ M.ID_PK, M.VALUE FROM MYTABLE M WHERE M.ID_PK 
= COALESCE(:B1 , M.ID_PK)

Plan hash value: 1229213413

-------------------------------------
| Id  | Operation         | Name    |
-------------------------------------
|   0 | SELECT STATEMENT  |         |
|   1 |  TABLE ACCESS FULL| MYTABLE |
-------------------------------------


EXPLAINED SQL STATEMENT:
------------------------
SELECT /*+ SO_QUERY_3 */ M.ID_PK, M.VALUE FROM MYTABLE M WHERE (:B1 IS 
NULL OR M.ID_PK = :B1 )

Plan hash value: 1229213413

-------------------------------------
| Id  | Operation         | Name    |
-------------------------------------
|   0 | SELECT STATEMENT  |         |
|   1 |  TABLE ACCESS FULL| MYTABLE |
-------------------------------------

NVL 좋은 계획

필터 작업은 입력 값에 따라 최적화는 실행 시간에 다른 계획을 선택할 수 있습니다.

EXPLAINED SQL STATEMENT:
------------------------
SELECT /*+ SO_QUERY_1 */ M.ID_PK, M.VALUE FROM MYTABLE M WHERE M.ID_PK 
= NVL(:B1 , M.ID_PK)

Plan hash value: 730481884

----------------------------------------------------
| Id  | Operation                     | Name       |
----------------------------------------------------
|   0 | SELECT STATEMENT              |            |
|   1 |  CONCATENATION                |            |
|   2 |   FILTER                      |            |
|   3 |    TABLE ACCESS FULL          | MYTABLE    |
|   4 |   FILTER                      |            |
|   5 |    TABLE ACCESS BY INDEX ROWID| MYTABLE    |
|   6 |     INDEX UNIQUE SCAN         | MYTABLE_PK |
----------------------------------------------------

경고

FILTER 작업이 NVL 트릭이 잘 설명되어 있지 않습니다. 나는 확실히 이러한 기능을 도입 버전을 아니에요하지만 11g와 함께 작동합니다. 좀 복잡한 쿼리 제대로 작동하려면 필터를 받고 문제가 있었다했지만,이 같은 간단한 쿼리는 신뢰할 수있다.

from https://stackoverflow.com/questions/3649036/how-to-handle-optional-parameters-in-sql-query by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] 분산 트랜잭션 오류? (0)	2020.06.12
[SQL] 어떻게 Google BigQuery에서 결석 날짜를 차지 최신 시리즈를 생성? (0)	2020.06.12
[SQL] 어떻게 SQL에서 필드 값 당 행의 수를 제한 할 수 있습니까? (0)	2020.06.12
[SQL] 여러 행을 업데이트 할 때 NULL 유형을 주조 (0)	2020.06.12
[SQL] SQL 서버 : 포함 된 VARCHAR (MAX) 필드에서 유효하지 않은 XML 문자를 교체 (0)	2020.06.12

복붙노트

[SQL] 어떻게 선택적 매개 변수 SQL 쿼리를 처리하기 위해?

어떻게 선택적 매개 변수 SQL 쿼리를 처리하기 위해?

해결법

1.예, 다음 중 하나를 사용하여 :

2.OMG_Ponies '와 롭 밴 Wijk의 답변이 그냥 보충입니다 완전히 정확합니다.

3.NVL 접근 방식은 일반적으로 잘 작동합니다. 옵티마이 저는이 패턴을 인식하고 동적 계획을 구축 할 것입니다. 이 계획은 하나의 값과 NULL에 대한 전체 테이블 스캔에 대한 인덱스를 사용합니다.

'SQL' 카테고리의 다른 글

티스토리툴바