오라클 NVL, NVL2 함수 사용법 해당 칼럼의 값이 NULL 값인 경우 특정값으로 출력하고 싶으면 NVL 함수를 사용하고, NULL 값이 아닐 경우 특정값으로 출력하고 싶으면 NVL2 함수를 사용하면 된다. NVL 함수 NVL 함수는 값이 NULL인 경우 지정값을 출력하고, NULL이 아니면 원래 값을 그대로 출력한다. - 함수 : NVL("값", "지정값") WITH emp AS ( SELECT 7839 empno, 'KING' ename, 'PRESIDENT' job, NULL comm FROM dual UNION ALL SELECT 7654 empno, 'MARTIN' ename, 'SALESMAN' job, 1400 comm FROM dual ) SELECT empno , ename , com..
1. ROWNUM 키워드 이용하는 방법 - 조회된 순서되로 순번을 매긴다. SELECT ROWNUM , a.* FROM emp a - ORDER BY 를 사용하면 순번이 뒤섞이므로 정렬된 서브쿼리 결과에 ROWNUM 을 매겨야 한다. SELECT ROWNUM , x.* FROM ( SELECT a.* FROM emp a ORDER BY a.ename ) x 2. ROW_NUMBER() 함수를 사용하는 방법 - ORDER BY 된 결과에 순번을 매길때에는 ROWNUM 보다 ROW_NUMBER() 함수가 더 편하다. SELECT ROW_NUMBER() OVER(ORDER BY a.job, a.ename) row_num , a.* FROM emp a ORDER BY a.job, a.ename - 그룹별(PAR..
WM_CONCAT DISTINCT 오류 시 대체 방법 오라클 10g 부터 WM_CONCAT 함수를 사용하여 컬럼 값을 합칠수 있다. (오라클 12C 부터는 사용못함)WM_CONCAT(DISTINCT JOB) 을 쿼리툴에서는 잘 실행이 되지만 Procedure, Function 에서 사용시 에러가 발생한다. PL/SQL: ORA-30482: DISTINCT 옵션은 이 함수에 사용할 수 없습니다 WM_CONCAT보다는 조금 복잡하지만 아래의 방법으로 대체할 수 있다. --Oracle 10g, 11g r1 SELECT REGEXP_REPLACE( SUBSTR(XMLAGG(XMLELEMENT(JOB, ',', JOB) ORDER BY JOB).EXTRACT('//text()'), 2) , '([^,]+)(,\1..
오라클 버전 확인 쿼리 오라클 함수를 사용하기 위해 오라클 버전을 확인해야 하는 경우가 있다. 아래의 쿼리문을 사용하여 오라클 버전을 확인하면 된다. 첫 번째 방법 SELECT * FROM PRODUCT_COMPONENT_VERSION 두 번째 방법 SELECT * FROM v$version
오라클 문자열 날짜 포맷 변환 방법 날짜형의 포맷은 TO_CHAR 함수를 사용하여 쉽게 변경할 수 있다. 그러나 문자열 날짜, 예를 들어 "20180823"을 "2018-08-23"으로 변경하기 위해서는 번거로운 작업이 필요하다. 예시) 20180823 → 2018-08-23 (문자열 → 문자열) 아래는 2가지의 방법을 사용하여 포맷을 변경하는 예제이다. 문자열을 TO_DATE 함수로 날짜형으로 변경한 다음 다시 TO_CHAR 함수를 사용하여 원하는 포맷으로 변경하는 방법 정규식함수 REGEXP_REPLACE를 사용하여 원하는 패턴으로 문자열의 형식을 변경하는 방법 (오라클 10g 이상) 날짜로 변환 후 다시 문자로 변경 SELECT TO_CHAR(TO_DATE('20180823', 'YYYYMMDD..
오라클 문자 앞 부분 숫자 제거 방법 오라클 10g 버전 부터 정규식 함수 (REGEXP_REPLACE 등)가 추가 되었다 정규식 함수를 사용하면 문자열을 다양한 패턴으로 조작 할 수 있다 SELECT REGEXP_REPLACE('080821HM','^\d+') RESULT1 , REGEXP_REPLACE('080821HM','^[0-9]+') RESULT2 , REGEXP_REPLACE('080821HM','^[[:digit:]]+') RESULT3 FROM DUAL "080821H" 이라는 코드의 앞 숫자 부분 "080821" 제거하는 예제이다 ^ 문자열이나 행의 처음을 의미 \d 숫자 / [0-9], [:digit:] 숫자를 의미하는 동일한 표현식 + 1회 이상 포함 하나 이상의 숫자로 시작하는 부..
오라클 조회 시 컬럼명 별칭(Alias) 대소문자 적용 방법 오라클에서 데이터 조회시 컬럼 별칭을 대소문자를 구분하여 쿼리문을 작성해도조회결과의 컬럼명은 모두 대문자로 변경이 된다. 컬럼명에 지정한 별칭 그대로 결과에 표시 되도록 하기 위해서는 별칭을 쌍따옴표("")로 감싸주면 된다. 쌍따옴표를 사용하면 대소문자도 구분가능하며 한글로 별칭을 부여할 수 있다.
오라클에서 수치값의 음수 부호를 제거하고 절댓값을 구하기 위해서는 ABS 함수를 사용하면 된다. 절댓값은 수치값의 부호를 뺀 값을 의미한다. 양수는 부호가 없기 때문에 그대로 출력되고 음수는 마이너스(빼기) 부호를 제거한 값이 출력된다. 절댓값 함수 : ABS("숫자") SELECT -10 AS result1 , ABS(-10) AS result2 FROM dual 절댓값 함수를 사용하면 음수 부호를 없애고 값을 반환하는 것을 확인할 수 있다. SELECT ABS(10) , ABS(12.5) , ABS(0) , ABS(-10) , ABS(-12.5) FROM dual 양수와 0은 그대로 출력되고 음수는 부호가 제거된 값이 출력되는 것을 확인할 수 있다.
오라클 IN 파라미터 최대 개수 오라클에서 IN을 사용하면서 항상 최대 몇개까지 파라미터를 넣을수 있는지 궁금 했다. 간단히 임시 날짜 테이블을 생성해서 테스트한 결과 최대 1000까지 가능하다. 파리미터 개수 초과 시 해당 에러가 발생한다. "ORA-01795: 목록에 지정 가능한 식의 최대수는 1000 입니다" 텍스트 파라미터는 1000개 최대 이지만 서브쿼리로 IN 파리미터를 넣으면 무한정 가능하다 SELECT * FROM TEMP_DATE WHERE YYYYMMDD IN ( SELECT AA.CODE FROM CODET AA )
오라클 조건문에 WHERE 1=1 사용하는 이유 쿼리를 분석하다 보면 조건문에 의미없는 조건(1=1)이 코딩되어 있는 경우가 있다. 과거 시스템을 마이그레이션 할 때 포함 되었거나 의미를 몰라서 사용한 경우라고 생각한다. 동적쿼리에서 if문을 사용하여 조건을 유동적으로 바꾸고 싶을때 WHERE 1=1 으로 첫 조건을 선언 후AND 조건을 유동으로 바꾸어도 WHERE이라는 구문은 신경쓰지 않아도 된다. 동적 쿼리문이라도 첫 WHERE 조건이 명확하다면 1=1을 사용하지 않고 첫번째 조건의 컬럼을 사용하면 된다. 일반 쿼리문 작성 시 WHERE 조건에 1=1는 무의미 하다. 사용하지 말것. 동적 쿼리문을 작성 시 첫번째 조건 WHERE 구문에 어떤 조건이 해당될지 불명확할 때는 WHERE 조건줄을 1=1로 ..