기본값이 아닌 NLS_NUMERIC_CHARACHARTS를 사용하여 Oracle PL/SQL에서 텍스트를 숫자로 효율적으로 변환하는 방법은 무엇입니까?

기본값이 아닌 NLS_NUMERIC_CHARACHARTS를 사용하여 Oracle PL/SQL에서 텍스트를 숫자로 효율적으로 변환하는 방법은 무엇입니까?

저는 PL/SQL에서 문자열에서 숫자로 변환하는 효율적이고 일반적인 방법을 찾고 있습니다. 여기서 NLS_NUMERIC_CHARACHARERS 설정에 대한 로컬 설정은 예측할 수 없습니다. 가급적이면 만지지 않습니다.입력 형식은 프로그래밍 표준 "123.456789"이지만 소수점의 각 면에 알 수 없는 숫자가 있습니다.

select to_number('123.456789') from dual;
  -- only works if nls_numeric_characters is '.,'

select to_number('123.456789', '99999.9999999999') from dual;
  -- only works if the number of digits in the format is large enough
  -- but I don't want to guess...

to_number세 번째 매개 변수를 사용할 수 있지만 이 경우 두 번째 매개 변수도 지정해야 하며 "기본값"에 대한 형식 규격이 없습니다.

select to_number('123.456789', null, 'nls_numeric_characters=''.,''') from dual;
  -- returns null

select to_number('123.456789', '99999D9999999999', 'nls_numeric_characters=''.,''') from dual;
  -- "works" with the same caveat as (2), so it's rather pointless...

PL/SQL을 사용하는 다른 방법은 다음과 같습니다.

CREATE OR REPLACE
FUNCTION STRING2NUMBER (p_string varchar2) RETURN NUMBER
IS
  v_decimal char;
BEGIN
  SELECT substr(VALUE, 1, 1)
  INTO v_decimal
  FROM NLS_SESSION_PARAMETERS
  WHERE PARAMETER = 'NLS_NUMERIC_CHARACTERS';
  return to_number(replace(p_string, '.', v_decimal));
END;
/

select string2number('123.456789') from dual;

정확히 제가 원하는 것을 해주지만, 만약 당신이 그것을 쿼리로 여러 번 한다면 효율적이지 않아 보입니다.v_decimal 값은 NLS_NUMERIC_CHARACTERS에 대한 세션 값을 변경한 후 다시 중단되는지 여부를 모르기 때문에 캐시할 수 없습니다(한 번 가져와 패키지 변수에 저장).

제가 뭔가 간과하고 있나요?아니면 제가 너무 걱정하고 있는 것일까요? Oracle은 제가 생각하는 것보다 훨씬 더 효율적입니다.

다음이 작동해야 합니다.

SELECT to_number(:x, 
                 translate(:x, '012345678-+', '999999999SS'), 
                 'nls_numeric_characters=''.,''') 
  FROM dual;

올바른 두 번째 인수를 작성합니다.999.999999효율적으로translate숫자가 몇 자리인지 미리 알 필요가 없습니다.지원되는 모든 Oracle 번호 형식(최대 62자리의 유효 숫자(10.2.0.3)과 함께 작동합니다.

흥미롭게도, 만약 당신이 정말 큰 끈을 가지고 있다면, 간단한.to_number(:x)작동하는 반면 이 방법은 실패합니다.

편집: soliver 덕분에 음수 지원.

세션당 많은 작업을 수행하는 경우 작업을 시작할 때 ALTER SESSION SET NLS_NUMERIC_CHARACHARTS = '.'를 사용하는 것이 좋습니다.

물론 같은 세션에서 많은 다른 코드가 실행되면 펑키한 결과가 나올 수 있습니다 :-) 그러나 데이터 로드를 위한 전용 프로그램이 있기 때문에 데이터 로드 절차에서 이 방법을 사용할 수 있습니다.

죄송합니다, 당신의 질문이 반대라는 것을 나중에 알게 되었습니다.그럼에도 불구하고 주목할 점은 반대 방향으로 쉬운 해결책이 있다는 것입니다.

조금 늦었지만, 오늘 저는 https://docs.oracle.com/cloud/latest/db112/SQLRF/sql_elements004.htm#SQLRF00210 에서 "텍스트 최소 숫자 형식 모델이 (십진수 출력으로) 가능한 최소 문자 수를 반환합니다."라고 설명되는 특수 형식 마스크 'TM9'와 'TME'를 발견했습니다.

TM9는 이 특정 문제를 해결하기 위해 발명된 것처럼 보입니다.

select to_char(1234.5678, 'TM9', 'NLS_NUMERIC_CHARACTERS=''.,''') from dual;

는 결는입니다.'1234.5678'선행 또는 후행 공백 없이, 그리고 소수점이 포함된 내 환경에도 불구하고.NLS_LANG=GERMAN_GERMANY.WE8MSWIN1252COMA가 합니다.

select to_number(replace(:X,'.',to_char(0,'fmd'))) from dual;

그건 그렇고.

select to_number(replace('1.2345e-6','.',to_char(0,'fmd'))) from dual;

그리고 만약 당신이 더 엄격하게 원한다면.

select to_number(translate(:X,to_char(0,'fmd')||'.','.'||to_char(0,'fmd'))) from dual;

자릿수가 무제한이라는 것이 현실적입니까?그렇다면 요구사항을 좀 더 꼼꼼히 살펴봐야 할 이유가 되지 않을까요?

초기 문자열이 매우 길 때 환상적인 상황이 발생하면 다음과 같은 방법이 사용됩니다.

select 
  to_number(
    '11111111.2222'
  , 'FM' || lpad('9', 32, '9') || 'D' || lpad('9', 30, '9')
  , 'NLS_NUMERIC_CHARACTERS=''.,'''
  ) 
from 
  dual

언급URL : https://stackoverflow.com/questions/4142891/how-to-efficiently-convert-text-to-number-in-oracle-pl-sql-with-non-default-nls