Oracle
21. Chapter3 Single-row SQL Functions(20081106)
Gungume
2009. 1. 9. 10:50
Character Functions (P3-7)
- TRIM : 첫번째 인자의 문자열에서, 두번째 인자의 문자열을 왼쪽 또는 오른쪽에서 찾아서 잘라냄(중간꺼는 패스~)
- LTRIM() : 왼쪽 끝에 대해서 수행
- RTRIM() : 오른쪽 끝에 대해서 수행
- SQL> SELECT RTRIM('abcdeefeeeee', 'e') FROM dual;
- RTRIM('
-------
abcdeef - SQL> SELECT LTRIM('eeabcdeefeeeee', 'e') FROM dual;
- LTRIM('EEABC
------------
abcdeefeeeee
Character Functions (P3-9)
- CHR() : 인자(아스키코드값)에 해당하는 문자 출력
- ASCII() : 인자(문자)에 해당되는 아스키코드값 출력
- SQL> SELECT CHR(79) FROM dual;
- C
-
O - SQL> SELECT ASCII('A') FROM dual;
- ASCII('A')
----------
65 - SQL> SELECT ASCII('O') FROM dual;
- ASCII('O')
----------
79
- REPLACE() : 첫번째 인자의 문자열에서 두번째 문자열을 찾아서 찾은 것을 세번째 문자열로 바꾼다.
- SQL> SELECT REPLACE('Oracle DB System', 'DB', 'Database') FROM dual;
- REPLACE('ORACLEDBSYSTE
----------------------
Oracle Database System
- SUBSTR() : 문자열 자르기 -> 1부터 인덱스 시작 -> 첫번째 인자의 문자열에서. 두번째 인자의 값을 인덱스로 시작으로 해서. 세번째 인자의 크기만큼 잘라내서 출력~
- SQL> SELECT SUBSTR('Oracle DB System', 2, 4) FROM dual;
- SUBS
----
racl
- // -5 : 끝에서 5번째부터 3개의 문자를 자름~
- SQL> SELECT first_name, SUBSTR(first_name, -5, 3) FROM s_emp;
- FIRST_NAME SUBSTR
------------------------- ------
Carmen arm
LaDoris Dor
Midori ido
Mark
Audry Aud
Molly Mol
Roberta ber
Ben
Antoinette net
Marta Mar
Colin Col
Henry Hen
Yasmin asm
Mai
Andre And
Elena Ele
George eor
Akira Aki
Vikram ikr
Chad
Alexander and
Eddie Edd
Radha Rad
Bela
Sylvie ylv - 25 rows selected.
- TRANSLATE() : 첫번째 인자의 문자열을 대상으로 번역, 두번째 인자의 문자열을, 세번째 인자의 문자열로 바꿈 -> 전체 문자열이 아닌 2, 3번째 문자열의 각각의 문자를 맵핑~
- A -> 1
- B -> 2
- C -> 3
- D -> 4
- SQL> SELECT TRANSLATE('Oracle DBMS', 'ABCD', '1234') FROM dual;
- TRANSLATE('
-----------
Oracle 42MS
- INSTR() : 첫번째 문자열에서 두번째 문자열을 찾아서 인덱스(1부터 시작) 리턴~, 없으면 0 리턴~
3번째 인자는 시작위치 지정 -> 검색 시작위치를 지정할 뿐 인덱스는 처음부터 시작
네번째 인자는 몇번째에 해당되냐를 정함 -> 2로 지정은 검색 결과중 2번째에 해당되는 인덱스를 지정)
- SQL> SELECT INSTR('Oracle DBMS', 'a') FROM dual;
- INSTR('ORACLEDBMS','A')
-----------------------
3
- LENGTH() : 문자열로 display되는 길이를 return, 날짜의 경우는 문자열로 바뀔때 해당 문자열의 길이
Date Functioins (P3-10)
- ADD_MONTHS() : 첫번째 인자의 날짜로부터 몇개월 후 또는 전
- SQL> SELECT ADD_MONTHS(sysdate, 5), ADD_MONTHS(sysdate, -5) FROM dual;
- ADD_MONT ADD_MONT
-------- --------
09/04/06 08/06/06
- LAST_DAY() : 인자로 지정된 날짜의 마지막 날
- SQL> SELECT LAST_DAY('03/01/01'), LAST_DAY('03/02/01') FROM dual;
- LAST_DAY LAST_DAY
-------- --------
03/01/31 03/02/28
- MONTHS_BETWEEN() : 첫번째와 두번째 날짜의 개월 차
- SQL> SELECT MONTHS_BETWEEN('03/01/01', '03/07/01') FROM dual;
- MONTHS_BETWEEN('03/01/01','03/07/01')
-------------------------------------
-6
- NEW_TIME() : 시간대 변경하는것
- NEXT_DAY() : 첫번째 인자(날짜) 기준으로 두번째 인자(요일)이 언제인지 알려줌(요일은 NLS_DATE_LANGUAGE에 따라 달라짐)
- SQL> SELECT NEXT_DAY(sysdate, '일요일') FROM dual;
- NEXT_DAY
--------
08/11/09
- ROUND() : YEAR, MONTH, DAY를 기준으로 반올림(TO_DATE() 함수를 써서 문자열형으로 된 날짜를 날짜형으로 변경)
- TRUNC() :
- // 연도를 기준
- SQL> SELECT ROUND(TO_DATE('03/07/16'), 'YEAR') FROM dual;
- ROUND(TO
--------
04/01/01 - // 월을 기준
- SQL> SELECT ROUND(TO_DATE('03/07/16'), 'MONTH') FROM dual;
- ROUND(TO
--------
03/08/01 - // 요일을 기준
- SQL> SELECT ROUND(TO_DATE('03/07/16'), 'DAY') FROM dual;
- ROUND(TO
--------
03/07/13
Date Arithmetic Operations (P3-11)
- 날짜에 대한 연산
- 성이 Biri인 사원의 근무일수
- SQL> SELECT sysdate - start_date FROM s_emp WHERE last_name='Biri';
- SYSDATE-START_DATE
------------------
6788.61247
Conversion Functions (P3-12)
- TO_CHAR() : 문자열로 변환하는 함수 -> 인자 d=날짜, n=숫자 -> 포맷 문자를 알아야 함
- TO_DATE() : 문자열을 날짜 데이터로 변경 -> 포맷 : 해당 문자열을 어떻게 해석할지 알려줌, Y, M, D가 어디에 해당하는지 알려줌
Format Elements for Number (P3-14)
-
TO_CHAR() : 숫자 or 날짜를 문자열로 변경해 줌, 포맷을 통해서 출력형식 결정
- 첫번째 : 전체를 5자리로 잡고 자릿수 남으면 공백문자열 채움 -> 첫번째 결과인 3,000 앞에 공백문자열 있으니 LENGTH=5 (고정길이 문자열)
- 두번째 : 전체를 6자리로 잡고 자룻시 남으면 0으로 채움
- 세번째 : FM을 씀으로써 첫번째의 결과에서 공백문자열 대신 아예 비워둠 (가변길이 문자열)
- 네번째 : 소수점 아래 1번째 자리 있던 없던 무조건 출력
- SQL> SELECT TO_CHAR(salary, '99,999'), TO_CHAR(salary, '099,999'),
2 TO_CHAR(salary, 'FM99,999'), TO_CHAR(salary, '99,999.0')
3 FROM s_emp; - TO_CHAR TO_CHAR( TO_CHAR TO_CHAR(S
------- -------- ------- ---------
3,000 003,000 3,000 3,000.0
1,450 001,450 1,450 1,450.0
1,400 001,400 1,400 1,400.0
3,000 003,000 3,000 3,000.0
3,000 003,000 3,000 3,000.0
1,200 001,200 1,200 1,200.0
1,250 001,250 1,250 1,250.0
1,100 001,100 1,100 1,100.0
1,300 001,300 1,300 1,300.0
1,307 001,307 1,307 1,307.0
1,400 001,400 1,400 1,400.0
1,490 001,490 1,490 1,490.0
1,515 001,515 1,515 1,515.0
1,525 001,525 1,525 1,525.0
1,450 001,450 1,450 1,450.0
1,400 001,400 1,400 1,400.0
940 000,940 940 940.0
1,200 001,200 1,200 1,200.0
795 000,795 795 795.0
750 000,750 750 750.0
850 000,850 850 850.0
800 000,800 800 800.0
795 000,795 795 795.0
860 000,860 860 860.0
1,100 001,100 1,100 1,100.0 - 25 rows selected.
- 9, 0 : 자리수 표현
- $ : $표시
- L : Local Currency 표현 -> 날짜나 지역에 따라 달라지는 값 설정
- SQL> SELECT value FROM nls_session_parameters
2 WHERE parameter = 'NLS_CURRENCY'; - VALUE
-------------------------------------------------------------------------------- - ￦
- SQL> ALTER SESSION SET NLS_CURRENCY='\';
- Session altered.
- // '$' -> 그냥 $
- // 'L' -> 현재 세션의 통화단위
- SQL> SELECT TO_CHAR(salary, '$99,999'), TO_CHAR(salary, 'L99,999')
2 FROM s_emp; - TO_CHAR( TO_CHAR(SALARY,'L
-------- -----------------
$3,000 \3,000
$1,450 \1,450
$1,400 \1,400
$3,000 \3,000
$3,000 \3,000
$1,200 \1,200
$1,250 \1,250
$1,100 \1,100
$1,300 \1,300
$1,307 \1,307
$1,400 \1,400
$1,490 \1,490
$1,515 \1,515
$1,525 \1,525
$1,450 \1,450
$1,400 \1,400
$940 \940
$1,200 \1,200
$795 \795
$750 \750
$850 \850
$800 \800
$795 \795
$860 \860
$1,100 \1,100 - 25 rows selected.
- MI : 숫자가 음수면 끝에 '-'가 붙음
- PR : 숫자가 음수면 괄호가 붙음
- RN : 해당 숫자를 로마숫자로 변경 (RN을 대문자, 소문자 어떤걸 쓰냐에 따라서 결과 로마문자도 바뀜)
- S : 양수면 +, 음수면 -
- X : 16진수로 변경해서 출력 (X = 대문자, x = 소문자 -> 16진수중 A~F 값 표현시)
- TO_NUMBER() : 매개인자인 문자열을 숫자로 변경함, 숫자와 숫자앞에 +, -를 붙혀주면 별도의 포맷 없이 변환 가능
숫자외의 문자가 들어있는 경우에 포맷을 이용해서 숫자에 해당하는 부분을 표현해줘야함
Format Elements for Date (P3-15)
-
사용시기
- TO_CHAR(날짜, 포맷) : 날짜가 어떤 형식인지 알려줌
- TO_DATE(문자열, 포맷) : '문자열'을 'DATE'형식으로 바꿀때 '포맷' 형식으로 해석해서 변경해라~
- SS : 0~59 사이의 초 (1분을 초 단위로 나눈 것)
- SSSSS : 정오(0시)부터 현재까지 몇 초가 지났는지 알려줌
- MI : 0~59 사이의 분 (1시간을 분 단위로 나눈 것)
- HH : 0~12로 시간 표시
- H24 : 0~23으로 시간 표시
- AM, PM : 둘중 어떤 것을 쓰던간에 오전이면 AM, 오후이면 PM으로 표시됨.
- DD ; 1~31 사이의 월(한달)
- DAY : 요일(일요일, 월요일~ / SUNDAY~)
- DY : 요일의 약자(일, 욜~ / SUN~)
- D : 요일을 의미하는 숫자 (1~7)
- DDD : 1년중 오늘이 몇일인지 (1~366)
- 포맷 문자 : ' ~ ' 사이
- 일반 문자 : " ~ " 사이
- P3-15 예제
- SQL> SELECT TO_CHAR(sysdate, 'HH24"시" MI"분" SS"초"') FROM dual;
- TO_CHAR(SYSDAT
--------------
15시 28분 24초 - // AM을 쓰던 PM을 쓰던 결과는 똑같음 (언어셋이 한글이라 '오후'로 출력됨~)
- SQL> SELECT TO_CHAR(sysdate, 'HHAM'), TO_CHAR(sysdate, 'HHPM') FROM dual;
- TO_CHA TO_CHA
------ ------
03오후 03오후 - // '15시 28분 24초'를 'HH24"시" MI"분" SS"초"'로 해석해서 TO_DATE 시킴~
- // 현재 세션에서 날짜를 표시하는 방법 때문에 결과에서 '시, 분, 초' 가 안나오는 것~
- SQL> SELECT TO_DATE('15시 28분 24초', 'HH24"시" MI"분" SS"초"') FROM dual;
- TO_DATE(
--------
08/11/01
- SQL> SELECT TO_CHAR(sysdate, 'DDD"일"') FROM dual;
- TO_CH
-----
311일
SQL> SELECT TO_CHAR(sysdate, 'DAY DY') FROM dual;- TO_CHAR(SYSDAT
--------------
목요일 목
Format Elements for Date (P3-16)
- W : 이번달의 몇번째 주 인지 알려줌 (1~5)
- WW : 올해의 몇번째 주 인지 알려줌 (1~53)
- MM : 월 (1~12)
- MON : 월의 약어(JAN, ~)
- MONTH : 월의 FullName(JANUARY, ~)
- Q : 분기 (1~4)
- RM : 월을 로마숫자로 표현 (I~XII)
- AD, BC : 기원전, 후
- Y, YY, YYY : 연도의 자릿수별 표현
- YYYY, SYYYY : 연도가 기원전인 경우 -가 붙음
- // 1년중 45번째 주, 1주일중 5번째 일
- SQL> SELECT TO_CHAR(sysdate, 'WW"주" D"일"') FROM dual;
- TO_CHAR(
--------
45주 5일
- SQL> SELECT TO_CHAR(start_date, 'BC YYYY Q MM DD') FROM s_emp;
- TO_CHAR(START_DATE,
-------------------
서기 2090 1 03 03
서기 1990 1 03 08
서기 1991 2 06 17
서기 1990 2 04 07
서기 1990 1 03 04
서기 1991 1 01 18
서기 1990 2 05 14
서기 1990 2 04 07
서기 1992 1 02 09
서기 1991 1 02 27
서기 1990 2 05 14
서기 1992 1 01 18
서기 1991 1 02 18
서기 1992 1 01 22
서기 1991 4 10 09
서기 1992 1 02 07
서기 1990 1 03 08
서기 1991 1 02 09
서기 1991 3 08 06
서기 1991 3 07 21
서기 1991 2 05 26
서기 1990 4 11 30
서기 1990 4 10 17
서기 1991 1 03 17
서기 1991 2 05 09 - 25 rows selected.
- //BC -> AD, MM ->MON으로 변경후 출력~
- SQL> SELECT TO_CHAR(start_date, 'AD YY Q MON DD') FROM s_emp;
- TO_CHAR(START_DATE,'ADY
-----------------------
서기 90 1 3월 03
서기 90 1 3월 08
서기 91 2 6월 17
서기 90 2 4월 07
서기 90 1 3월 04
서기 91 1 1월 18
서기 90 2 5월 14
서기 90 2 4월 07
서기 92 1 2월 09
서기 91 1 2월 27
서기 90 2 5월 14
서기 92 1 1월 18
서기 91 1 2월 18
서기 92 1 1월 22
서기 91 4 10월 09
서기 92 1 2월 07
서기 90 1 3월 08
서기 91 1 2월 09
서기 91 3 8월 06
서기 91 3 7월 21
서기 91 2 5월 26
서기 90 4 11월 30
서기 90 4 10월 17
서기 91 1 3월 17
서기 91 2 5월 09
- // 나머지는 올해의 현재 월로~
- SQL> SELECT TO_DATE('03', 'YY') FROM dual;
- TO_DATE(
--------
03/11/01 - SQL> SELECT TO_DATE('03', 'MM') FROM dual;
- TO_DATE(
--------
08/03/01 - SQL> SELECT TO_DATE('03', 'DD') FROM dual;
- TO_DATE(
--------
08/11/03
Format Elements for Date (P3-17)
- YEAR, SYEAR
- SQL> SELECT TO_CHAR(sysdate, 'YEAR') FROM dual;
- TO_CHAR(SYSDATE,'YEAR')
------------------------------------------
TWO THOUSAND EIGHT - SQL> SELECT TO_CHAR(sysdate, 'SYEAR') FROM dual;
- TO_CHAR(SYSDATE,'SYEAR')
-------------------------------------------
TWO THOUSAND EIGHT
- CC, SCC : 세기
- J : 줄리앙 데이트
- SP : 앞에 나오는 숫자를 스펠링으로 변경
- TH : 상수로 변경
- // CC(세기)를 SP영문으로 표시
- SQL> SELECT TO_CHAR(sysdate, 'CCSP QTH') FROM dual;
- TO_CHAR(SYSDATE,'
-----------------
TWENTY-ONE 4TH
RR Format (P3-18)
- Y2K 문제를 해결하려고 나온 포맷
- 예전에는 연도를 2자리로 표시
- 시간이 지나면서 2000년을 00으로 표시하면 1900인지 2000인지 모름
- 00이 들어왔을때 포맷에 따라 1900년도인지 2000년도인지 구분함
Miscellaneous Functions (P3-19)
- GREATEST() : 매개인자로 몇개가 오던 그 중에 가장 큰것 반환
- LEAST() : 매개인자로 몇개가 오던 그 중에 가장 작은것 반환
- USER() : 로그인한 유저
- VSIZE() : 매개인자로 들어온 걸 저장하기 위해 몇바이트를 사용하는지 알려줌
- DECODE() : 매개인자로 몇개가 오던 상관 없음, 첫번째 매개변수를 대상으로 하고, 마지막은 기본값, 중간에서는 2개를 쌍으로 해서 2개의 쌍중 첫번째 값과, 첫번째 매개변수를 비교해서 맞는 것에 대해 2개의 쌍중 2번째 값을 리턴~
- SQL> SELECT DECODE(dept_ID,
2 10, salary*1.1,
3 50, salary*1.2,
4 salary)
5 FROM s_emp; - DECODE(DEPT_ID,10,SALARY*1.1,50,SALARY*1.2,SALARY)
--------------------------------------------------
3600
1450
1400
3300
3600
1200
1250
1100
1300
1307
1400
1490
1515
1525
1450
1400
940
1200
795
750
850
800
795
860
1100 - 25 rows selected.
Miscellaneous Functions (P3-20)
- NVL() : 첫번째 매개변수가 null이면 두번째 매개변수를 리턴, null이 아니라면 첫번째 매개변수 리턴
- // commission_pct 컬럼이 0인 경우 그냥 아무것도 출력 안됨
- SQL> SELECT id, commission_pct FROM s_emp;
- ID COMMISSION_PCT
---------- --------------
1
2
3
4
5
6
7
8
9
10
11 10
12 12.5
13 10
14 15
15 17.5
16
17
18
19
20
21
22
23
24
25 - 25 rows selected.
- // commission_pct 컬럼의 값이 null인 경우 0으로 출력~
- SQL> SELECT id, NVL(commission_pct, 0) FROM s_emp;
- ID NVL(COMMISSION_PCT,0)
---------- ---------------------
1 0
2 0
3 0
4 0
5 0
6 0
7 0
8 0
9 0
10 0
11 10
12 12.5
13 10
14 15
15 17.5
16 0
17 0
18 0
19 0
20 0
21 0
22 0
23 0
24 0
25 0 - 25 rows selected.
- NVL2() : 첫번째 매개변수가 null이 아니면 두번째 매개변수 리턴, null이면 세번째 매개변수 리턴~
- NULLIF() : 첫번째, 두번째 매개변수가 같으면 null 리턴, 그렇지 않으면 첫번째 리턴
- COALESCE() : 매개인자로 들어온 여러 컬럼의 값중 최초로 나온 null이 아닌 값을 리턴
Nesting Single-Row Functions (P3-21)
- Single-Row 함수는 함수안에 함수 쓰는 것이 가능함
이 글은 스프링노트에서 작성되었습니다.