Oracle에서 NVL과 Coalesce간에 명백한 차이점이 있습니까?
명백한 차이점은 병합이 매개 변수 목록에서 첫 번째 null이 아닌 항목을 반환하는 반면 nvl은 두 개의 매개 변수 만 사용하고 첫 번째가 null이 아닌 경우 첫 번째를 반환하고 그렇지 않으면 두 번째를 반환한다는 것입니다.
NVL은 통합의 ‘기본 사례’버전 일 수 있습니다.
뭔가 빠졌습니까?
답변
COALESCEANSI-92표준 의 일부인보다 현대적인 기능입니다 .
NVL이다 Oracle그것은에 도입 된 특정 80어떤 기준이되기 전에 S ‘이 (가) 있습니다.
두 값의 경우 동의어입니다.
그러나 그것들은 다르게 구현됩니다.
NVL항상 두 인수를 모두 평가하지만 COALESCE일반적으로 첫 번째가 아닌 것을 발견 할 때마다 평가를 중지합니다 NULL(sequence와 같은 예외가 있습니다 NEXTVAL).
SELECT SUM(val)
FROM (
SELECT NVL(1, LENGTH(RAWTOHEX(SYS_GUID()))) AS val
FROM dual
CONNECT BY
level <= 10000
)이 아닌 에도 불구하고 0.5생성하기 때문에 거의 몇 초 동안 실행됩니다 .SYS_GUID()1NULL
SELECT SUM(val)
FROM (
SELECT COALESCE(1, LENGTH(RAWTOHEX(SYS_GUID()))) AS val
FROM dual
CONNECT BY
level <= 10000
)이것은 이것이 1아님을 이해 NULL하고 두 번째 주장을 평가하지 않습니다.
SYS_GUID의 생성되지 않고 쿼리가 즉시 이루어집니다.
답변
NVL은 첫 번째 매개 변수의 데이터 유형으로 암시 적 변환을 수행하므로 다음은 오류가 없습니다.
select nvl('a',sysdate) from dual;COALESCE는 일관된 데이터 유형을 기대합니다.
select coalesce('a',sysdate) from dual;‘일관되지 않은 데이터 유형 오류’가 발생합니다.
답변
NVL 및 COALESCE는 컬럼이 NULL을 리턴하는 경우 기본값을 제공하는 동일한 기능을 달성하는 데 사용됩니다.
차이점은 다음과 같습니다.
- NVL은 2 개의 인수 만 허용하지만 COALESCE는 여러 인수를 취할 수 있습니다
- NVL은 인수를 평가하고 널이 아닌 값이 처음 나타날 때 COALESCE가 중지합니다.
- NVL은 주어진 첫 번째 인수를 기반으로 암시 적 데이터 유형 변환을 수행합니다. COALESCE는 모든 인수가 동일한 데이터 유형이어야합니다.
- COALESCE는 UNION 절을 사용하는 쿼리에서 문제를 제공합니다. 아래 예
- COALESCE는 NVL이 Oracle에 따라 ANSI 표준입니다.
세 번째 사례의 예입니다. 다른 경우는 간단합니다.
select nvl('abc',10) from dual; NVL이 숫자 10을 문자열로 암시 적으로 변환하므로 작동합니다.
select coalesce('abc',10) from dual; 일관되지 않은 데이터 유형 : CHAR에 NUMBER 번이 있습니다.
UNION 사용 사례의 예
SELECT COALESCE(a, sysdate)
from (select null as a from dual
union
select null as a from dual
);실패 ORA-00932: inconsistent datatypes: expected CHAR got 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
답변
계획 처리에도 차이가 있습니다.
Oracle은 검색 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)합병 :
---------------------------------------------------------------------------------------
| 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 로 이동 하십시오 .
답변
coalesce ()가 null이 아닌 첫 번째 값으로 평가를 중단하지 않는다는 또 다른 증거 :
SELECT COALESCE(1, my_sequence.nextval) AS answer FROM dual;이것을 실행하고 확인하십시오. my_sequence.currval;
답변
사실 나는 각 진술에 동의 할 수 없습니다.
“COALESCE는 모든 인수가 동일한 데이터 유형이어야합니다.”
이것은 잘못입니다. 아래를 참조하십시오. 인수는 다른 데이터 유형일 수 있으며 문서화됩니다 . expr의 모든 항목이 숫자 데이터 유형이거나 숫자 데이터 유형으로 내재적으로 변환 될 수있는 숫자가 아닌 데이터 유형 인 경우 Oracle Database는 가장 높은 숫자 우선 순위를 갖는 인수를 내재적으로 판별합니다. 나머지 인수를 해당 데이터 유형으로 변환하고 해당 데이터 유형을 리턴합니다. . 실제로 이것은 일반 표현식 “Null이 아닌 값이 처음 발생할 때 COALESCE가 중지됨”과 모순됩니다. 그렇지 않으면 테스트 케이스 4가 오류를 발생시키지 않아야합니다.
또한 테스트 사례 번호 5에 따르면 COALESCE암시적인 인수 변환이 수행됩니다.
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!답변
이것은 분명하지만이 질문을 한 Tom이 말한 방식으로 언급되었습니다. 그러나 다시 참아 보자.
NVL은 2 개의 인수 만 가질 수 있습니다. 병합에는 2 개 이상이있을 수 있습니다.
select nvl('','',1) from dual;// 결과 : ORA-00909: 잘못된 개수의 인수
select coalesce('','','1') from dual; // 출력 : 1을 반환
