복붙노트

[SQL] NVL과 합체 사이 오라클의 차이

SQL

NVL과 합체 사이 오라클의 차이

오라클의 NVL과 합체 사이의 비 명백한 차이가 있습니까?

명백한 차이는, 그렇지 않으면 두 번째를 반환, 그것이 null가 아닌 경우, NVL는 두 개의 매개 변수와 반환 첫 소요 반면, 그 유착이 매개 변수 목록의 첫 번째 null 이외의 항목을 반환 할 수 있습니다.

NVL 단지 유착의 '기본 케이스 "버전 일 수 있습니다 것으로 보인다.

나는 뭔가를 놓치고 있습니까?

해결법

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

    1.COALESCE는 ANSI-92 표준의 일부입니다보다 현대적인 기능입니다.

    COALESCE는 ANSI-92 표준의 일부입니다보다 현대적인 기능입니다.

    NVL는 기준이되기 전에 그것은 80 년대에 도입 된 오라클 고유의 것입니다.

    두 값의 경우, 그들은 동의어입니다.

    그러나, 그들은 다르게 구현됩니다.

    COALESCE는 일반적으로는 최초의 비 NULL (예 : 순서 NEXTVAL과 같은 몇 가지 예외가있다) 발견 할 때마다 평가가 정지하면서 NVL은 항상, 두 인수를 평가 :

    SELECT  SUM(val)
FROM    (
        SELECT  NVL(1, LENGTH(RAWTOHEX(SYS_GUID()))) AS val
        FROM    dual
        CONNECT BY
                level <= 10000
        )

    (1)에도 불구하고 거의 0.5 초 동안이 실행, 그것은 SYS_GUID를 생성 이후는 ()의 아닌 NULL 인.

    SELECT  SUM(val)
FROM    (
        SELECT  COALESCE(1, LENGTH(RAWTOHEX(SYS_GUID()))) AS val
        FROM    dual
        CONNECT BY
                level <= 10000
        )

    이것은 1은 NULL이 2 번째의 인수를 평가하지 않는다는 것을 잘 알고 있습니다.

    SYS_GUID의가 생성되지 않습니다 쿼리는 순간이다.

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

    2.다음은 오류를하지 않도록 NVL는 첫 번째 매개 변수의 데이터 유형에 대한 암시 적 변환을 할 것입니다

    다음은 오류를하지 않도록 NVL는 첫 번째 매개 변수의 데이터 유형에 대한 암시 적 변환을 할 것입니다

    select nvl('a',sysdate) from dual;

    COALESCE 일관된 데이터 유형을 기대하고있다.

    select coalesce('a',sysdate) from dual;

    는 '일관성없는 데이터 형식 오류'가 발생합니다

  3. ==============================

    3.NVL과 유착은 열이 NULL을 반환하는 경우 기본값을 제공하는 동일한 기능을 수행하는 데 사용됩니다.

    NVL과 유착은 열이 NULL을 반환하는 경우 기본값을 제공하는 동일한 기능을 수행하는 데 사용됩니다.

    차이점은 다음과 같습니다 :

    세 번째 경우의 예. 다른 경우는 간단합니다.

    선택 NVL ( 'ABC', 10)의 이중; NVL로겠습니까 작업은 문자열로 숫자 10의 암시 적 변환을 할 것입니다.

    병합을 선택 ( 'ABC', 10)의 이중; 오류로 실패합니다 - 일치하지 않는 데이터 유형을 : 예상 CHAR 가지고 번호를

    UNION 유스 케이스에 대한 예

    SELECT COALESCE(a, sysdate) 
from (select null as a from dual 
      union 
      select null as a from dual
      );

    ORA-00932와 함께 실패합니다 : 일치하지 않는 데이터 유형을 : 예상 CHAR는 DATE있어

    SELECT NVL(a, sysdate) 
from (select null as a from dual 
      union 
      select null as a from dual
      ) ;

    성공합니다.

    더 많은 정보 : http://www.plsqlinformation.com/2016/04/difference-between-nvl-and-coalesce-in-oracle.html

  4. ==============================

    4.차이도 계획 처리에이다.

    차이도 계획 처리에이다.

    오라클은 검색 인덱스 컬럼 NVL 결과의 비교를 포함 가지 필터의 연결에 최적화 된 계획이 수 형태이다.

    create table tt(a, b) as
select level, mod(level,10)
from dual
connect by level<=1e4;

alter table tt add constraint ix_tt_a primary key(a);
create index ix_tt_b on tt(b);

explain plan for
select * from tt
where a=nvl(:1,a)
  and b=:2;

explain plan for
select * from tt
where a=coalesce(:1,a)
  and b=:2;

    NVL :

    -----------------------------------------------------------------------------------------
| Id  | Operation                     | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |         |     2 |    52 |     2   (0)| 00:00:01 |
|   1 |  CONCATENATION                |         |       |       |            |          |
|*  2 |   FILTER                      |         |       |       |            |          |
|*  3 |    TABLE ACCESS BY INDEX ROWID| TT      |     1 |    26 |     1   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | IX_TT_B |     7 |       |     1   (0)| 00:00:01 |
|*  5 |   FILTER                      |         |       |       |            |          |
|*  6 |    TABLE ACCESS BY INDEX ROWID| TT      |     1 |    26 |     1   (0)| 00:00:01 |
|*  7 |     INDEX UNIQUE SCAN         | IX_TT_A |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(:1 IS NULL)
   3 - filter("A" IS NOT NULL)
   4 - access("B"=TO_NUMBER(:2))
   5 - filter(:1 IS NOT NULL)
   6 - filter("B"=TO_NUMBER(:2))
   7 - access("A"=:1)

    COALESCE :

    ---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |     1 |    26 |     1   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| TT      |     1 |    26 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IX_TT_B |    40 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("A"=COALESCE(:1,"A"))
   2 - access("B"=TO_NUMBER(:2))

    크레딧 http://www.xt-r.com/2012/03/nvl-coalesce-concatenation.html로 이동합니다.

  5. ==============================

    5.사실은 내가 각 진술에 동의 할 수 없다.

    사실은 내가 각 진술에 동의 할 수 없다.

    "COALESCE는 모든 인수가 같은 데이터 형식이 될 것으로 기대하고있다."

    이것은 잘못된 것입니다, 아래를 참조하십시오. 암시 적으로, EXPR의 모든 항목이 숫자 데이터 유형 또는 암묵적으로 수치 데이터 형식으로 변환 할 수있는 모든 숫자가 아닌 데이터 유형 인 경우, 오라클 데이터베이스는 가장 높은 숫자 우선 순위가 인수를 결정 인수도 설명되어 서로 다른 데이터 유형이 될 수 있습니다 변환 데이터 유형 나머지 인수, 데이터 타입 ... 사실 이것도 일반 식 모순 "COALESCE NULL이 아닌 값이 처음 나타나는 정차"된다 달리 테스트 케이스 번호 4는 에러를 발생하지 않도록 리턴 .

    또한 테스트 케이스 번호 5 유착에 따른 인자의 내재적 변환한다.

    DECLARE
    int_val INTEGER := 1;
    string_val VARCHAR2(10) := 'foo';
BEGIN

    BEGIN
    DBMS_OUTPUT.PUT_LINE( '1. NVL(int_val,string_val) -> '|| NVL(int_val,string_val) );
    EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('1. NVL(int_val,string_val) -> '||SQLERRM ); 
    END;

    BEGIN
    DBMS_OUTPUT.PUT_LINE( '2. NVL(string_val, int_val) -> '|| NVL(string_val, int_val) );
    EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('2. NVL(string_val, int_val) -> '||SQLERRM ); 
    END;

    BEGIN
    DBMS_OUTPUT.PUT_LINE( '3. COALESCE(int_val,string_val) -> '|| COALESCE(int_val,string_val) );
    EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('3. COALESCE(int_val,string_val) -> '||SQLERRM ); 
    END;

    BEGIN
    DBMS_OUTPUT.PUT_LINE( '4. COALESCE(string_val, int_val) -> '|| COALESCE(string_val, int_val) );
    EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('4. COALESCE(string_val, int_val) -> '||SQLERRM ); 
    END;

    DBMS_OUTPUT.PUT_LINE( '5. COALESCE(SYSDATE,SYSTIMESTAMP) -> '|| COALESCE(SYSDATE,SYSTIMESTAMP) );

END;

    Output:

1. NVL(int_val,string_val) -> ORA-06502: PL/SQL: numeric or value error: character to number conversion error
2. NVL(string_val, int_val) -> foo
3. COALESCE(int_val,string_val) -> 1
4. COALESCE(string_val, int_val) -> ORA-06502: PL/SQL: numeric or value error: character to number conversion error
5. COALESCE(SYSDATE,SYSTIMESTAMP) -> 2016-11-30 09:55:55.000000 +1:0 --> This is a TIMESTAMP value, not a DATE value!
  6. ==============================

    6.COALESCE ()가 최초의 null 이외의 값으로 평가를 중단하지 않는 또 다른 증거 :

    COALESCE ()가 최초의 null 이외의 값으로 평가를 중단하지 않는 또 다른 증거 :

    SELECT COALESCE(1, my_sequence.nextval) AS answer FROM dual;

    다음 my_sequence.currval 확인이 실행;

  7. ==============================

    7.이 사람은 분명, 심지어는이 질문을 톰에 의해 넣어하는 방법으로 언급이지만. 그러나 다시 넣을 수 있습니다.

    이 사람은 분명, 심지어는이 질문을 톰에 의해 넣어하는 방법으로 언급이지만. 그러나 다시 넣을 수 있습니다.

    NVL은 2 인자를 가질 수 있습니다. 합체가 2 개 이상있을 수 있습니다.

    NVL ( '' ''1) 이중에서 선택] // 결과 : ORA-00909을 : 인수의 개수가 잘못되었습니다 ( '', '' '1') 이중에서 유착을 선택; // 출력 : 1을 반환

  8. ==============================

    8.NVL : 값이 널 (null)를 교체합니다.

    NVL : 값이 널 (null)를 교체합니다.

    COALESCE : 반환 표현 목록에서 첫 번째 null이 아닌 식입니다.

    테이블 : PRICE_LIST

    +----------------+-----------+
| Purchase_Price | Min_Price |
+----------------+-----------+
| 10             | null      |
| 20             |           |
| 50             | 30        |
| 100            | 80        |
| null           | null      |
+----------------+-----------+

    아래의 예이다 모든 제품에 10 %의 수익을 추가로 [1] 세트 판매 가격. 더 구입 정가가없는 경우 [2] 다음, 판매 가격은 최저 가격입니다. 정리 세일하십시오. [3] 최소 가격은 또한 다음 "50"기본 가격으로 판매 가격을 설정하지가 있다면.

    SELECT
     Purchase_Price,
     Min_Price,
     NVL(Purchase_Price + (Purchase_Price * 0.10), Min_Price)    AS NVL_Sales_Price,
COALESCE(Purchase_Price + (Purchase_Price * 0.10), Min_Price,50) AS Coalesce_Sales_Price
FROM 
Price_List

    실제 실제 예제를 설명한다.

    +----------------+-----------+-----------------+----------------------+
| Purchase_Price | Min_Price | NVL_Sales_Price | Coalesce_Sales_Price |
+----------------+-----------+-----------------+----------------------+
| 10             | null      | 11              |                   11 |
| null           | 20        | 20              |                   20 |
| 50             | 30        | 55              |                   55 |
| 100            | 80        | 110             |                  110 |
| null           | null      | null            |                   50 |
+----------------+-----------+-----------------+----------------------+

    당신은 NVL과 우리가 규칙 [1]를 달성 할 수 [2]를 볼 수 있지만 유착으로 우리는 세 가지 규칙을 달성 할 수있다.

    9. from https://stackoverflow.com/questions/950084/oracle-differences-between-nvl-and-coalesce by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] C #으로 SQL 코드를 파싱 [폐쇄]  (0) 2020.03.18
[SQL] 컬럼에서 고유 한 값을 선택  (0) 2020.03.18
[SQL] 는 SQL 서버 VARCHAR / NVARCHAR 문자열에서 줄 바꿈을 삽입하는 방법  (0) 2020.03.18
[SQL] MySQL의 : 고유 값의 발생을 카운트  (0) 2020.03.18
[SQL] SQL 3 개 테이블 내부는 조인?  (0) 2020.03.18
,

티스토리툴바