NVL과 병합의 Oracle 차이점
Oracle에서 NVL과 Coalesce 사이에 명확한 차이가 있습니까?
분명한 차이점은 병합은 파라미터 목록의 첫 번째 non null 항목을 반환하는 반면 nvl은 두 개의 파라미터만 가져가고 nvl이 null이 아닌 경우 첫 번째 항목을 반환하고 그렇지 않으면 두 번째 항목을 반환한다는 것입니다.
NVL은 단지 통합의 '베이스 케이스' 버전인 것 같습니다.
내가 뭘 빼놓았나요?
COALESCE으로서 보다 is is is의 입니다.ANSI-92★★★★★★ 。
NVLOracle구체적으로는, 에서 도입되었습니다.80표준이 존재하기 전의 것입니다.
값이 두 개일 경우 동의어입니다.
다만, 실장 방법은 다릅니다.
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()에도 불구하고1 아닌 모양새가 아니다NULL.
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은 인수를 모두 평가하고 Null이 아닌 값이 처음 발생하면 COALESCE가 중지됩니다.
- NVL은 지정된 첫 번째 인수를 기반으로 암묵적인 데이터 유형 변환을 수행합니다.COALESCE에서는 모든 인수가 동일한 데이터 유형이어야 합니다.
- COALESCE는 UNION 절을 사용하는 쿼리에서 문제를 제공합니다.다음 예
- COLESCE는 ANSI 표준이며 NVL은 Oracle 고유합니다.
세 번째 케이스의 예.다른 경우는 간단하다.
select nvl('abc',10) from dual;는 NVL이 숫자 10을 문자열로 암묵적으로 변환하기 때문에 동작합니다.
select coalesce('abc',10) from dual;오류 - 일치하지 않는 데이터 유형: 예상 CHAR get 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에 있습니다.
moalesce()가 null이 아닌 첫 번째 값에 대한 평가를 중지하지 않는다는 또 다른 증거:
SELECT COALESCE(1, my_sequence.nextval) AS answer FROM dual;
실행 후 확인my_sequence.currval;
NVL: null을 값으로 바꿉니다.
COALESCE: 식 목록에서 null이 아닌 첫 번째 식을 반환합니다.
표: PRICE_LIST
+----------------+-----------+
| Purchase_Price | Min_Price |
+----------------+-----------+
| 10 | null |
| 20 | |
| 50 | 30 |
| 100 | 80 |
| null | null |
+----------------+-----------+
다음으로 의 예를 제시하겠습니다.
[1] 모든 제품에 10%의 이익을 더한 판매 가격을 설정한다.
[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]를 달성할 수 있습니다.
그러나 COLSECE를 사용하면 세 가지 규칙을 모두 달성할 수 있습니다.
이것은 명백하고, 심지어 이 질문을 한 톰이 제시한 방식으로도 언급되었습니다.하지만 다시 한 번 참아보자.
NVL에는 2개의 인수만 사용할 수 있습니다.병합은 3개보다 많을 수 있습니다.
select nvl('','',1) from dual;//결과:ORA-00909: 잘못된 인수 수
select coalesce('','','1') from dual;//출력: 1을 반환합니다.
사실 나는 각각의 진술에 동의할 수 없다.
"COALESCE는 모든 인수가 동일한 데이터 유형이어야 합니다."
이것은 틀렸습니다.아래를 참조해 주세요.인수에는 다른 데이터 유형을 지정할 수 있습니다.이 데이터 유형도 문서화되어 있습니다.expr의 모든 항목이 숫자 데이터 유형 또는 숫자 데이터 유형으로 암묵적으로 변환할 수 있는 숫자 이외의 데이터 유형인 경우 Oracle Database는 가장 높은 숫자 우선 순위를 가진 인수를 결정하고 나머지 인수를 해당 데이터 유형으로 암묵적으로 변환한 다음 해당 데이터 유형을 반환합니다.실제로 이는 일반적인 표현인 "COALESCE stops at first occurrence of a non-null value"에도 반한다.그렇지 않으면 테스트 사례 4가 오류를 발생시키지 않아야 한다.
또한 테스트 케이스 No.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!
언급URL : https://stackoverflow.com/questions/950084/oracle-differences-between-nvl-and-coalesce
'programing' 카테고리의 다른 글
| 스프링 부트: 2개의 데이터 소스 구성 및 사용 (0) | 2023.02.22 |
|---|---|
| AngularJs 문자열의 첫 글자를 대문자로 표시 (0) | 2023.02.22 |
| JavaScript를 사용하는 AJAX와 jQuery의 차이점은 무엇입니까? (0) | 2023.02.22 |
| WordPress - 커스텀 투고 타입, 커스텀 롤, 커스텀 기능 (0) | 2023.02.22 |
| JSON 문자열에서 해시 맵 생성 (0) | 2023.02.18 |