oracle - 문자열, 숫자, 날짜 조작 함수

19.11.25. 문자열, 숫자, 날짜 조작 함수

데이터베이스 구현

2019-12-01 23:39:02

---

함수

function.

크게 single row function 과 multi row function 으로 나눌 수 있다.

 

-single row function : 단일 행을 기준으로 작업하고, 행당 하나의 결과를 반환한다.

ex) 특정 컬럼의 문자열 길이 : length(ename)

 

-multi row function : 여러 행을 기준으로, 하나의 결과를 반환.

ex) 그룹 함수 : count, sum, avg

 

* DUAL TABLE

sys 계정에 있는 테이블로, 누구나 사용 가능하다.

dummy 컬럼 하나만 존재하며 값은 'X' 이며 데이터는 한 행만 존재.

사용 용도 : 데이터와 관련 없이 함수 실행, 시퀀스 실행. merge 문에서, 데이터 복제 시.

 

▶ CHARACTER

-대소문자 변환

ex) LOWER, UPPER, INITCAP ( 첫 단어만 대문자로.)

 

--

SLECT LOWER('Hello, World'), UPPER ('Hellp, World'), INITCAP('Hello, World')

FROM dual;

  => 행당 한 번의 함수가 실행된다. 1개의 행이 input --> 1개의 행으로 out put(컬럼)

       dual 테이블에는 한 행만 존재하기 때문에 한 행에 한 번씩만 출력된다.

 

--만일 위를 emp테이블에서 실행시킨다면?

SLECT LOWER('Hello, World'), UPPER ('Hellp, World'), INITCAP('Hello, World')

FROM emp;

  => emp테이블에는 총 14건의 데이터(직원)가 존재. (=14개의 행이 존재.)

       따라서 같은 내용이 14행이 출력된다.

 

-- 컬럼에 function 적용

SELECT empno, LOWER (ename)

FROM emp;

WHERE ename = 'SMITH';

  => select 할 때에만 데이터가 LOWER 함수에 의해 소문자로 보이는 것뿐.

       실제 데이터는 변함이 없다. 따라서 위의 쿼리는 조회가 된다.

 

--where 절에도 사용이 가능.

SELECT empno, LOWER (ename)

FROM emp

WHERE ename = UPPER ('smith')

 

--아래처럼 좌변을 가공하지 말 것.

SELECT empno, LOWER(ename)

FROM emp

WHERE LOWER(ename) = 'smith';

※위 쿼리에서 where 절에서 ename 이라는 테이블에 함수를 적용했지만, 이 방식은 사용하면 안 된다.

좌변을 가공하면 안 되기 때문. 위위 쿼리처럼 치환시켜서 변수 부분을 가공하는 것이 좋다.

좌변을 가공할 경우 인덱스 활용이 안되기 때문이다.

실제 테이블의 원본을 가공해줘야 결과가 나오는데, 소문자로 만들려면 그 테이블 전체를 읽어야 함.

테이블 컬럼을 가공해도 동일한 결과를 얻을 수 있지만, 테이블 컬럼보다는 상수 쪽을 가공하는 것이

속도면에서 유리하다.

해당 컬럼에 인덱스가 존재하더라도 함수를 적용하게 되면 값이 달라지게 되어

인덱스를 활용할 수 없게 된다.

예외 : FBI(Function Based Index)

(무슨 소린지 모르겠지만 반년 후엔 이해되겠지?)

 

▶ 문자열 조작 함수

1. CONCAT : 문자열 합침

2. SUBSTR : 문자열에서 일부분을 추출

3. LENGTH : 문자열의 길이

4. INSTR : 문자열에 특정 문자열이 들어있는지 확인. (해당 문자열의 인덱스를 반환한다.)

5. LPAD(RPAD) : 문자열의 왼쪽(오른쪽)에 특정 문자열을 삽입.

                      설정한 문자열의 길이보다 부족한 만큼 문자열을 채워 넣을 것을 설정해야 함.

6. TRIM : 문자열 앞, 뒤로 공백, 혹은 특정 문자를 제거.

7. REPLACE : 대체.

 

--1. COCAT 을 사용해보자

SELECT CONCAT (CONCAT ( 'HELLO' ,  ' , ' ) , 'WORLD' ),

          'HELLO' || ' , ' || 'WORLD'

FROM dual;

  => select 절의 윗줄, 아랫줄은 같은 결과를 나타낸다. 

       하지만, CONCAT 은 2개만 묶을 수 있음에 반해, || 를 사용하면 여러 개를 묶을 수 있다.

 

--2. SUBSTR

SELECT SUBSTR ('HELLO, WORLD', 1, 5)

FROM dual;

  => (문자열, 시작 인덱스, 종료 인덱스)의 순서로 사용된다.

       시작 인덱스는 1부터. 종료 인덱스의 문자열까지 포함한다.

       *자바에서는 시작인덱스 0부터, 종료인덱스의 문자열 미포함!!

 

--4. INSTR

SELECT INSTR ('HELLO, WORLD', 'O')

FROM DUAL;

  => 해당 문자열에 'O' 가 존재하는지 알 수 있다. 존재할 경우,  첫 번째 문자의 인덱스를 리턴한다.

       위의 경우 O 가 5번째, 9번째 인덱스에 있지만 5 가 반환됨.

--

SELECT INSTR('HELLO, WORLD', 'O', 6)

FROM DUAL;

  => 문자열의 6번째 인덱스 이후에 등장하는 'O' 문자열의 인덱스 리턴.

--

SELECT INSTR('HELLO, WORLD', 'O', INSTR('HELLO, WORLD', 'O') +1)

FROM DUAL;

  => 괄호 속의 INSTR 은 위위에서 본 것처럼 5를 반환한다. 거기에 +1 을 하면 6.

       즉 6번째 인덱스 이후에 등장하는 'O' 를 찾으라는 뜻이므로 위의 결과와 같다.

 

--5.  LPAD(RPAD)

SELECT LPAD ('HELLO, WORLD', 15)

FROM dual;

  => 문자열의 길이를 15로 맞추는데, 부족한 길이는 왼쪽에 L(R)PAD의 디폴트 문자(공백) 으로 채운다.

--

SELECT RPAD ('HELLO, WORLD', 15, '*')

FROM dual;

  => 문자열의 길이를 15로 맞추는데, 부족한 길이는 오른쪽에, '*' 로 채운다.

 

--6. REPLACE

SELECT REPLACE ('HELLO, WORLD', 'HELLO', 'hello')

FROM dual;

  => 문자열에서 'HELLO' 를 'hello' 로 대체했다.

 

--7. TRIM

SELECT TRIM('     HELLO, WORLD    ') t1

           TRIM('H' FROM 'HELLO, WORLD') t2

FROM dept;

  => t1 은 문자열 내의 앞, 뒤의 공백을 제거했다.

       t2 는 문자열이 가진 'H'를 제거했다.

       dept는 4행을 가진 테이블이므로, 총 4행 실행되었다.

▶

숫자 조작 함수.

1. ROUND : 반올림.  ROUND(숫자, 반올림 결과 표시할 소수점 자리)

2. TRUNC : 절삭(=내림).  TRUNC(숫자, 절삭할 자리)

3. MOD : 나머지 연산 MOD(피제수, 제수)  ex) MOD (5,2)  =  1

 

1. ROUND

SELECT ROUND( 105.54, 1)

FROM dual;

  => 반올림 결과가 소수점 한자리까지 나온다. (소수점 둘째 자리에서 반올림한다.) 결과 : 105.5

--

SELECT ROUND(10.55, 0)

FROM dual;

  => 소수점 첫 번째 자리에서 반올림. 정수로 나온다. 결과 : 11

--

SELECT ROUND(105.54, -1)

FROM dual;

  => 정수 첫번째 자리에서 반올림. 결과 : 110

 

2. TRUNC

SELECT TRUNC(105.54, 1)

FROM dual;

  => 절삭 결과가 소수점 한자리까지 나온다.

--

SELECT TRUNK(105.55, 0)

FROM dual;

  => 소수점 첫번째 자리에서 절삭. 결과 : 105

--

SELECT TRUNC(105.54, -1)

FROM dual;

  => 정수 첫번째 자리에서 절삭. 결과 : 100

 

3. MOD

피제수를 제수로 나눈 나머지 값.

MOD(M, 2) 의 결과 종류는 0, 1 이 있다.

0 ~ (제수-1) 개의 결과 종류가 있다.

ex) 3으로 나눌 경우 나머지가 0 혹은 1 혹은 2로,  3개가 올 수 있음.

--

SELECT MOD(142,23)

FROM dual;

=> 결과 : 4

 

▶ 날짜

DATE : 연월일, 시간, 분, 초

* 도구>환경설정>데이터베이스>NLS : 날짜에 대한 기본 설정을 바꿀 수 있다.

 

1. SYSDATE

서버의 현재 DATE 를 리턴하는 내장 함수. 특별한 인자가 없다.

 

2. 날짜 연산 : DATE +/- 정수  =  정수만큼 DATE의 일자를 더하거나 뺀다.

3. 시간 연산 : DATE +/- (정수/24) = 시,분,초 중 시간을 더하거나 뺀다.

                  DATE +/- (정수/24/60) = 시,분,초 중 분을 더하거나 뺀다.

 

* 현재 시간 SYSDATE 의 시,분, 초 까지 표현하려면, TO_CHAR 를 통해서

 데이터를 변환시켜야 한다.

--

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS')

FROM dual;

 

-- 2019년 12월 31일을 DATE 형으로 표현하는 방법

SELECT TO_DATE('2019-12-31', 'YYYY-MM-DD')

FROM dual;

 

4. 형변환

DATE -> CHAR   :  TO_CHAR(DATE, '포맷')

CAHR -> DATE   :  TO_DATE(날짜 문자열, '포맷')

 

5. 포맷

YYYY, MM, DD

D (요일을 숫자로 : 일요일 1, 월요일 2...........토요일 7)

IW(주차 : 1~53)  :  1년은 52주 혹은 53주로 구성되어 있다. IW, HH, MI, SS

 

--정리

SELECT TO_CHAR (SYSDATE, 'YYYY')  : 현재 연도.

SELECT TO_CHAR (SYSDATE, 'MM')   : 현재 월.

SELECT TO_CHAR (SYSDATE, 'DD')    : 현재 일.

SELECT TO_CHAR (SYSDATE, 'D')      : 현재 요일.

SELECT TO_CHAR (SYSDATE, 'IW')     : 현재 주차. (해당 주의 목요일을 주차의 기준으로 잡는다.)

 

--2019년 12월 31일은 몇 주 차가 나오는가?

SELECT TO_CHAR (TO_DATE(2019-12-31', 'YYYY-MM-DD'), 'IW')

FROM dual;

  => 결과 : 1    ∵ 이 주의 목요일이 2020년 1월의 첫째 주이기 때문.

 

▶ 날짜 조작 함수

1. MONTH_BETWEEN ( DATE1, DATE2)   : DATE 1과 DATE 2 사이의 개월 수.

2. ADD_MONTHS (DATE, 가감할 개월 수) : DATE에서 특정 개월 수를 더하거나 뺀 날짜.

3. NEXT_DAY (DATE, WEEKDAY(1~7))     : DATE 이후 첫 번째 위클리 날짜.

4. LAST_DAY (DATE)                           : DATE 가 속한 월의 마지막 날짜.

 

 

*바인드 변수 : PARAM

툴마다 방식이 다르지만, 오라클 sql 에서는 :yyyymm 으로 하면 된다.

--

SELECT :YYYYMM PARAM, TO_CHAR(LAST_DAY(TO_DATE(:YYYYMM,'YYYYMM')), 'DD')

FROM dual;

  => 사용자에게 입력받은 달의 마지막 날짜 'dd'(일) 를 출력한다.