mingmong's devlog

오라클 시스템 함수인 ROWNUM, ROW_NUMBER를 활용하여 행에 순번을 매길 수 있다.이 함수들에 대하여 알아보자. 1) ROWNUM?: SELECT 해온 데이터에 대해 일련번호를 붙이는 것이다.주로 테이블에서 원하는 만큼의 행만 가져오고 싶을 때,행의 개수를 제한하는 용도로 사용한다.ex) 페이징처리 2) ROWNUM 사용법ROWNUM의 기본 사용법을 아래 EMP_TABLE 테이블을 예시로 설명해보겠다.※ 참고: 위 테이블은 아래 쿼리로 임시 테이블을 구성한 것이다.- EMP_NO: 사원번호, EMP_NM: 사원명, HIRE_DATE: 입사일WITH EMP_TABLE AS ( SELECT '5020' AS EMP_NO, 'SCOOT' AS EMP_NM, '20120302' AS HIRE_D..

oracle에서 Sequence 값을 순서대로 채번하여 테이블의 key 값으로 넣어주는 경우가 있을 것이다.보통은 단순하게 MAX + 1을 하여 값을 저장하게 된다.그런데 채번하여 저장된 이 값의 중간 데이터가 삭제되는 경우, key값이 중간에 빠지는 경우가 발생하게 된다.이러한 경우를 방지하기 위하여 채번할 때 중간에 빠지는 값이 없이 저장하는 방법에 대해 알아보자. 1) 채번하는 코드의 시작과 끝을 아는 경우: 이 경우는 채번값의 시작과 끝이 어떤 값으로 저장되는지, 즉 코드값의 범위를 아는 경우이다.예를 들어, key 값이 한 자리 수이고 시작 채번값이 1이라면 범위는 1~9일 것이다.이 경우에는 아래 예시와 같이 코드를 작성하면 된다.WITH DATA_TABLE AS ( SELECT '1' ..

문자열 치환 관련 함수에는 대표적으로 REPLACE 함수가 있다.이와 비슷한 역할을 하는 또다른 문자열 함수로는 TRANSLATE 함수가 있다.REPLACE와 TRANSLATE 함수 둘 다 해당 지정된 문자열을 바꿔주는 역할을 하지만, 약간의 차이점이 있다.이 차이점에 대해 알아보자. 1) REPLACE 함수1-1) 문법: REPLACE("컬럼명 or 문자열", "찾을문자", "치환문자")1-2) 예시1SELECT REPLACE('WATERMELON', 'E', '@#!')FROM DUAL;결과값 1-3) 예시2SELECT REPLACE('WATERMELON', 'WTR', '@#!')FROM DUAL;결과값 1-4) 특징: 치환할 문자열인 char 문자열에 대하여, 찾을 문자열이 정확히 맞지 않으면 치..

Oracle에서 현재 날짜 및 시간을 조회할 때 SYSDATE 함수를 사용한다.쿼리에 날짜 조건을 사용하는 경우가 많기 때문에, 날짜 함수를 잘 활용할 수 있어야 한다. 1) SYSDATE: 시스템의 현재 날짜 및 시간(yyyy-mm-dd hh24:mi:ss)을 반환한다.date 타입으로 시간 데이터를 가지고 있음'+', '-' 연산자로 날짜를 더하기 빼기 연산할 수 있음SELECT SYSDATE AS TODAY, SYSDATE + 1 AS TOMORROW, SYSDATE - 1 AS YESTERDAYFROM DUAL;결과값 2) 날짜 포맷 정하기 (TO_CHAR 함수): TO_CHAR() 함수를 통하여 SYSDATE의 포맷을 다양하게 설정할 수 있다.-- 포맷을 정하여 날짜 출력SELECT ..

DB에서 사용 중인 모든 테이블, 모든 컬럼에 대한 정보를 알고 싶은 경우,혹은 USER 별로 권한을 갖고 있는 테이블 정보를 알고 싶은 경우 등다양한 이유로 컬럼이나 테이블을 검색하고 싶을 때 활용 가능한 View가 있다. 1) ALL_TABLES: 로그인 된 계정의 권한으로 접근할 수 있는 모든 테이블 조회관리자 계정일 경우, 모든 계정의 DB 테이블 전부를 조회할 수 있음SELECT * FROM ALL_TABLES WHERE TABLE_NAME LIKE '%PROD%'; 2) USER_TABLES: 로그인 된 계정이 소유하고 있는 테이블 조회SELECT *FROM USER_TABLESWHERE TABLE_NAME LIKE '%PROD%'; 3) ALL_TAB_COLUMNS: 로그인 된 계정의 권한으..

여러 줄의 행(ROW)을 한 줄로 이어서 출력하고 싶은 경우가 있다.이 경우에는, XMLELEMENT 함수와 XMLAGG 함수를 조합하면 된다.※ Oracle 10g 버전 이상부터는 XMLAGG, WM_CONCAT 함수를 조합하면 보다 간단하게 사용 가능하다. 1) XMLELEMENT 함수: 반환되는 레코드를 XML 요소로 바꾸어주는 함수XMLELEMENT([태그명], 레코드명)ex) SHOP_TABLE 테이블의 SHOP_CD 컬럼을 XML 태그로 출력SELECT XMLEMENET(A, SHOP_CD)FROM SHOP_TABLE;첫 번째 인수에는 XML의 요소로 사용할 태그를 지정하고, 그 뒤에는 SELECT할 필드명을 적어준다.태그명은 옵션 항목으로, 별도로 지정하지 않으면 레코드명 자체가 XML 요..

특정 테이블 혹은 컬럼의 코멘트(정보)를 검색하고 싶은 경우가 있다.이 때 Oracle에서는 코멘트 시스템뷰를 활용하여 쉽게 코멘트 조회가 가능하다. 1) ALL_COL_COMMENTS: 컬럼에 대한 코멘트를 조회하기 위한 뷰※ 권한에 따라 ALL_COL_COMMENTS, USER_COL_COMMENTS, DBA_COL_COMMENTS 뷰 중에서 선택하여 사용하면 된다.OWNER: 소유자TABLE_NAME: 테이블명COLUMN_NAME: 컬럼명COMMENTS: 코멘트(주석)-- 컬럼명에 "SHOP_CD"를 포함하는 모든 컬럼의 코멘트 조회SELECT * FROM ALL_COL_COMMENTSWHERE COLUMN_NAME LIKE '%SHOP_CD%';-- 코멘트에 "매장"을 포함하는 모든 컬럼 조회SE..

프로시저(Procedure), 트리거(Trigger), 함수(Function), 패키지(Package), 타입(Type) 등의 내용을 특정 키워드로 검색하고 싶은 경우가 있다.이 때, USER_SOURCE나 ALL_SOURCE를 활용하면 된다. 1) USER_SOURCE: 현재 유저의 저장 오브젝트에서 텍스트의 내용을 확인 할 수 있다. 조회할 수 있는 컬럼은 아래와 같다.• NAME: 프로그램에 해당하는 객체명• TYPE: 프로그램 타입으로 종류에 따라 PROCEDURE, FUNCTION, PACKAGE, PACKAGE BODY, TYPE 등 의 값이 들어가 있다.• LINE: 프로그램 소스 코드의 라인(줄) 번호• TEXT: 프로그램 소스 코드-- 현재 유저의 저장 오브젝트 타입 종류 조회SELECT..

오라클에서 특정 월의 마지막 날짜(일자)를 구하기 위해서 LAST_DAY 함수를 사용하면 된다.날짜와 관련된 SQL문을 작성할 때 월 별로 마지막 날짜를 직접 계산하는 것이 번거로운데, 이 때 LAST_DAY 함수를 활용하면 특정 월에 대한 마지막 날짜를 쉽게 구할 수 있다. 1) 기본 문법LAST_DAY(날짜): 날짜는 DATE 타입, 문자 타입 둘 다 가능 2) 기본 예시LAST_DAY 함수를 사용하여 이번달에 대한 마지막 날짜를 구하는 예시를 알아보자.SELECT LAST_DAY(SYSDATE) AS DATE_DAY, LAST_DAY(TO_CHAR(SYSDATE, 'YYYYMMDD')) CHAR_DAYFROM dual;위 예시를 살펴보면, LAST_DAY 함수의 인자로 DATE 타입과 V..

오라클 10g부터 정규식을 사용할 수 있도록 함수가 추가되었다.따라서 기존 REPLACE 함수를 정규식을 활용하여 문자열의 패턴을 찾아 더 간단하게 처리할 수 있게 되었다.정규식을 활용한 REPLACE 함수는 REGEXP_REPLACE() 이고 구문(Syntax)는 아래와 같다.  REGXPP_REPALCE( source_char, patten [, replace_string [, position [, occurrence [, match_param] ] ] ..