오라클 유용한 함수(안쓰면 까먹어서 또 찾아봐야하는 그런...) 정리
/*---------------------------
1. 문자 함수
------------------------------*/
①LOWER( column|expression!!! )
LOWER('String') --> string : 소문자로 변환
②UPPER( column|expression!!! )
UPPER('String') --> STRING : 대문자로 변환
③INITCAP( column|expression!!! )
INITCAP('string') --> String : 첫글자만 대문자이고 나머지글자는 소문자로 변환
④CONCAT( column1|expression!!ŋ ,column2|expression!!Ō )
CONCAT('Good','String') --> GoodString : ||와 같은 역할을 한다.
⑤SUBSTR(column|expression!!!, m [,n]) : m값이 음수면 문자값의 끝부터..)
SUBSTR('String',1,3) --> Str : 1번째부터 3개의 문자를 리턴한다.
⑥LENGTH( column|expression!!! )
LENGTH('String') --> 6 : 문자열의 길이를 리턴한다.
⑦INSTR( column|expression!!!, )
INSTR('String','r') --> 3 : 문자열에 'r'이 몇번째 위치하고 있나를 리턴한다.
⑧LPAD( column|expression!!!,n,'string' ) : n 은 전체 길이
LPAD('String',10,'*') --> ****String
: 10자리수중에 모자란 자리수를 '*'로 왼쪽에 채운다.(문자,숫자 가능!!!)
⑨ RPAD('String',10,'*') --> String****
: 10자리수중에 모자란 자리수를 '*'로 오른쪽에 채운다.(문자,숫자 가능!!!)
⑩ LTRIM(' String') --> 'String' : 문자열의 왼쪽 공백을 버린다.
- LTRIM('****AAA***','*') --> 왼쪽에 특정 문자를 지운다.
⑪ RTRIM('String ') --> 'String' : 문자령의 오른쪽 공백을 버린다.
* TRIM(leading/tailing/both, trim_character FROM trim_source )
TRIM( 'S' FROM 'SSMITH') --> MITH
12. TRANSLATE REPLACE와 똑 같지만 이것은 스트링 단위가 아닌 문자 단위로 작동한다.
TRANSLATE('AABBA','B','C') -->AACCA
-- 숫자는 모두 9로 문자는 모두 X로 변환하기
SELECT
TRANSLATE('2KRW229','0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ','9999999999XXXXXXXXXXXXXXXXXXXXXXXXXX')
FROM DUAL
-------------------
9XXX999
13. REPLACE 어떤 스트링이 나타나는 곳마다 다른 스트링으로 바꾼다.
-- TRANSLATE와 확실한 구별 법
SELECT
TRANSLATE('2KRW229','229','0')
FROM DUAL
-------------------------------
0KRW00
SELECT
replace('2KRW229','229','0')
FROM DUAL
-------------------------------
2KRW0
--특정Character의 아스키값 구하기
SELECT CHR(65) "CHR", ASCII('A') "ASCII" FROM DUAL;
SELECT ASCII( CHR(65) ) FROM DUAL;
SELECT LOWER('My name is LKM') "LOWER", UPPER('My name is LKM') "UPPER" FROM DUAL;
SELECT LPAD('LKM', 10, '*') "LPAD", RPAD('LKM', 10, '*') "RPAD" FROM DUAL;
SELECT LPAD('1234567890', 20, '+') || RPAD('1234567890', 20, '^') "12345678901234567890"
FROM DUAL;
SELECT LPAD('1,234,567', 30, ' ') "LPAD사용으로 30자리 맞춤",
'1,234,567' "단순문자 사용",
1234567 "단순숫자 사용"
FROM DUAL;
SELECT LTRIM(' AAA ') "LTRIM", RTRIM(' AAA ') "RTRIM" FROM DUAL;
SELECT LTRIM( RTRIM( ' A A A ' ) ) "TRIM" FROM DUAL;
SELECT REPLACE('ORACLE', 'A', 'BBB') "REPLACE" FROM DUAL;
SELECT EMP_NAME, REPLACE(EMP_NAME, '이', '박') "이->박"
FROM PERSONNEL
WHERE EMP_NAME LIKE '이%';
SELECT SUBSTR('ORACLE PROJECT', 1, 3) SUBSTR1,
SUBSTR('ORACLE PROJECT', 4, 5) SUBSTR2,
SUBSTR('ORACLE PROJECT', 10) SUBSTR3 FROM DUAL ;
SELECT SUBSTRB('ORACLE PROJECT', 1, 3) SUBSTRB1,
SUBSTRB('ORACLE PROJECT', 4, 5) SUBSTRB2,
SUBSTRB('ORACLE PROJECT', 10) SUBSTRB3 FROM DUAL ;
SELECT SUBSTR('오라클 PROJECT', 1, 3) SUBSTR1,
SUBSTR('오라클 PROJECT', 4, 5) SUBSTR2,
SUBSTR('오라클 PROJECT', 10) SUBSTR3 FROM DUAL ;
SELECT SUBSTRB('오라클 PROJECT', 1, 3) SUBSTRB1,
SUBSTRB('오라클 PROJECT', 4, 5) SUBSTRB2,
SUBSTRB('오라클 PROJECT', 10) SUBSTRB3 FROM DUAL ;
SELECT LENGTH ('ORACLE PROJECT') "LENGTH",
LENGTHB('ORACLE PROJECT') "LENGTHB",
FROM DUAL;
SELECT EMPNO, LENGTH(EMPNO), LENGTHB(EMPNO),
EMP_NAME, LENGTH(EMP_NAME), LENGTHB(EMP_NAME)
FROM PERSONNEL
WHERE EMPNO > '98102';
SELECT HOBBY, LENGTH(HOBBY), LENGTHB(HOBBY)
FROM PERSONNEL
WHERE EMPNO > '98102';
SELECT INSTR ('ORACLE PROJECT', 'R', 1, 1) INSTR1,
INSTR ('ORACLE PROJECT', 'R', 1, 2) INSTR2,
INSTR ('ORACLE PROJECT', 'R', 1, 3) INSTR3
FROM DUAL;
SELECT INSTR ('CORPORATE FLOOR','OR', 3, 2) INSTR,
INSTRB ('CORPORATE FLOOR','OR', 3, 2) INSTRB
FROM DUAL;
SELECT HOBBY,
INSTR (HOBBY, ')', 1, 1) INSTR,
INSTRB(HOBBY, ')', 1, 1) INSTRB
FROM PERSONNEL
WHERE EMPNO > '98102';
/*------------------------------
2. 숫자 함수
-----------------------------------*/
① ROUND(45.926, 2) --> 45.93 : 소수점 두자리수까지 보여주면서 반올림한다.
② TRUNC(45.926, 2) --> 45.92 : 소수점 두자리까지만 보여주고 나머지는 버린다.
③ MOD(1600,300) --> 100 : 1600을 300으로 나누고 나머지를 리턴한다.
* ROUND예제(WHOLE NUMBER:정수)
SELECT ROUND(45.923,2), ROUND(45.923,0), ROUND(45.923,-1) FROM DUAL
==> 45.92 46 50
* TRUNC예제
SELECT TRUNC(45.923,2), TRUNC(45.923), TRUNC(45.923,-1) FROM DUAL
==> 45.92 45(n이 생략된면 일의 자리까지 남기고 버린다.) 40
* SYSTEM 날짜를 갖고 오는 방법.
SELECT sysdate FROM dual
--무조건 올림
SELECT CEIL(13.11) FROM DUAL;
SELECT CEIL(13.001) FROM DUAL;
--나머지 구함
SELECT MOD(23, 5) FROM DUAL;
SELECT MOD(57, 145) FROM DUAL;
-- 제곱승
SELECT POWER(3, 2), POWER(3, -2) FROM DUAL;
SELECT POWER(2, 10) FROM DUAL;
--자릿수 지정
SELECT ROUND(345.123, 0) FROM DUAL;
SELECT ROUND(345.123, 2), ROUND(345.123, -1) FROM DUAL;
--지정된 자리까지 잘라내기
SELECT TRUNC(345.123, 1), TRUNC(345.123, 0), TRUNC(345.123, -1) FROM DUAL;
SELECT TRUNC( 345.123 + 0.09, 1 ) FROM DUAL;
SELECT TRUNC( 345.123 + 0.9, 0 ) FROM DUAL;
SELECT TRUNC( 345.123 + 9, -1 ) FROM DUAL;
SELECT SIGN(5.989), SIGN(0), SIGN(-999.098) FROM DUAL;
/*------------------------------------
3. 날짜 계산( 날짜를 숫자로 저장)
-----------------------------------------*/
date + number : date에 number만큼 후의 날자를 보여준다.
date - number : date에 number만큼 전의 날자를 보여준다.
date1 - date2 : date1에서 date2 까지의 총 일수를 보여준다.( date1+date2는 X )
date1 + 숫자/24 : date1에서 시간을 더해 날짜를 보여준다.
4. Date Functions
MONTHS_BETWEEN('01-SEP-95','11-JAN-94') --> 19.6774194
; 두날짜 사이의 달수를 보여준다.
ADD_MONTHS('11-JAN-94', 6) --> 11-JUL-94
; 날짜에 6개월을 더한 날자를 보여준다.
NEXT_DAY('01-SEP-95','FRIDAY') --> '08-SEP-95'
; 해당일 다음에 오는 FRIDAY의 일자를 보여준다.
('SUNDAY'는 1, 'MONDAY'는 2...이런식으로 숫자를 써줘도 된다.)
LAST_DAY('01-SEP-95') --> '30-SEP-95'
; 해당월의 마지막날자를 보여준다.
ROUND('25-JUL-95','MONTH')--> 01-AUG-95 ROUND('25-JUL-95','YEAR')--> 01-JAN-96
TRUNC('25-JUL-95','MONTH') --> 01-JUL-95 TRUNC('25-JUL-95','YEAR') --> 01-JAN-95
/*--------------------------------
5. 타입 변환 함수
----------------------------------*/
nlsparams : 십진수, 그룹구분자, 지역 통화 기호, 국제 통화 기호
TO_CHAR(date,['format'],[nlsparams]) : date를 format에 맞게 문자열로 변환한다.
- Date Format Elements
YYYY --> 1999 (년출력) , YEAR --> nineteen ninety-nine (년출력)
MM --> 12 (월출력) , MONTH --> DECEMBER (월출력), MON --> DEC
D --> 요일을 숫자로 리턴한다.(일요일은 1, 월요일은 2...)
DD --> 07 (달의 일출력)
DDD --> 200 (연의 일출력)그 해의 총 몇 일째인가를 리턴한다.
DAY --> MONDAY (요일출력) DY-->MON
CC --> 20 (몇 세기인지를 보여준다.)
WW --> 그 해의 몇 번째 주인가를 리턴한다.
W --> 그 달의 몇 번째 주인가를 리턴한다.
* Element들을 소문자로 쓰면 소문자로 나오고 대문자로 쓰면 대문자로 출력된다.
HH or HH12 or HH24 / MI(0-59분) / SS(0-59초)
* 문자열은 " " 묶어 추가한다 DD " of " MONTH --> 12 of DECEMBER
*숫자 접미어는 숫자를 문자로 표기. TH(4->4TH)/ SP(4->FOUR)/ SPTH or THSP(4->FOURTH)
ddspth : 14-> fothteenth
* / . , : 구두점은 결과에 그대로 출력한다. * 공백, 선행제로를 제거하는 fm요소가 있다.
TO_CHAR(number,'format',[nlsparams]) : number를 format에 맞게 문자열로 변환한다.
- Number Format Elements
9 : 999,999 --> 1,234 $: 부동 달러 기호 $99999 -> $1234
0 : 099999 --> 001234 99.999EEEE -> 1.234E+03 B: 0값을 공백으로
L : L99,999 --> FF1,234 (NLS_CURRENCY에 설정되어있는 값이 L로 보여진다.)
TO_NUMBER(char,['format'],[nlsparams]) : 숫자형태의 문자열을 숫자로 변한한다.
TO_DATE(char,['format'],[nlsparams]):날자형태의 문자열을 format에 맞게 날자형식으로 변환 한다.
/*--------------------------------------------------------
6. NVL Funcion : 값이 null일 때 설정값을 보여준다.
----------------------------------------------------------*/
NVL(number_column, 0) : null일 때 0을 보여준다.
NVL(date_column, '01-JAN-95') : null일 때 '01-JAN-95'를 보여준다.
NVL(character_column, 'Empty') : null일 때 'Empty'를 보여준다.
* column Type과 표현식의 type이 반드시 일치해야 한다.
/*-------------------------------------------------------------------------
7. DECODE Function : CASE or IF-THEN-ELSE 형식과 비슷하다.
---------------------------------------------------------------------------*/
*DECODE(col/expression!!!, search1, result1 [,search2,result2,…] [,default])
F1 (F2 (F3 (col,arg1),arg2),arg3)
이중 Decode나 Nvl 그리고, To_데이터형은 너무나도 많이 쓰여서 잘 안 까먹지만....
숫자나 문자관련 함수 잘 안쓰면 찾기가 너무 힘들다는..... '그 뭐시기냐 문자열의 위치 찾아내는거
그 함수 뭐냐?' 라면 '그게 뭔데?'라는 반문이-_- 차라리 instr이 뭐하는거지가 더 쉬운 질문이다-_-
대전 있을때 자료전환건이 있어서... 처리를 하려고 보니 특정칼럼 하나에 잘 쓰이지 않는
두개의 필드값을 탭구분자로 나눠서 때려넣어논 꼴을 본적이 있다. instr, chr, replace로 간단히
해결가능할걸 instr과 chr를 잘 몰라 통째로 엑셀로 받아 수작업을 했다라는....
그것도 1만3천건의 자료를 ㅠ.ㅠ
다음은 예전 공부할때 예제들.. 참고삼아
/*--------------------------------
* 함수 - Date Function *
--------------------------------*/
SELECT SYSDATE FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') "SYSDATE" FROM DUAL;
SELECT LAST_DAY(SYSDATE) FROM DUAL;
SELECT TO_CHAR(LAST_DAY(SYSDATE), 'YYYY-MM-DD HH24:MI:SS') "LAST_DAY" FROM DUAL;
SELECT MONTHS_BETWEEN( '2002/01/13', '2002/05/13' ) "MONTHS_BETWEEN (-)",
MONTHS_BETWEEN( '2002/01/13', '2001/11/13' ) "MONTHS_BETWEEN (+)"
FROM DUAL ;
SELECT MONTHS_BETWEEN( '2002/01/13', '2002/01/30' ) "MONTHS_BETWEEN (-)",
MONTHS_BETWEEN( '2002/01/13', '2002/01/01' ) "MONTHS_BETWEEN (+)"
FROM DUAL ;
SELECT ADD_MONTHS(SYSDATE, 1) "ADD_MONTHS (+)",
ADD_MONTHS(SYSDATE, -1) "ADD_MONTHS (-)"
FROM DUAL ;
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';
SELECT ADD_MONTHS( '2002/02/28', 12 ) "1년후",
ADD_MONTHS( '2002/02/28', 24 ) "2년후",
ADD_MONTHS( '2002/02/28', 36 ) "3년후"
FROM DUAL ;
SELECT SYSDATE,
NEXT_DAY(SYSDATE, '일요일') "NEXT_DAY 1",
NEXT_DAY(SYSDATE, 1 ) "NEXT_DAY 2"
FROM DUAL;
SELECT SYSDATE,
NEXT_DAY(SYSDATE, '수요일') "NEXT_DAY 1",
NEXT_DAY(SYSDATE, 4 ) "NEXT_DAY 2"
FROM DUAL;
/*------------------------------------------
* 함수 - Conversion Function *
-------------------------------------*/
SELECT TO_CHAR(1234567.891) "TO_CHAR1",
TO_CHAR(1234567.891, '999') "TO_CHAR2",
TO_CHAR(1234567.891, '9,999,999') "TO_CHAR3",
TO_CHAR(1234567.891, '0.0000') "TO_CHAR5",
TO_CHAR(1234567.891, '9,999,999.0000') "TO_CHAR6",
TO_CHAR(123, '9,999.00') "TO_CHAR7",
TO_CHAR(123, '9,999.99') "TO_CHAR8"
FROM DUAL ;
SELECT TO_CHAR(1234567.891, '9G999G999') "TO_CHAR3",
TO_CHAR(1234567.891, '0D0000') "TO_CHAR5",
TO_CHAR(1234567.891, '9G999G999D0000') "TO_CHAR6",
TO_CHAR(123, '9G999D00') "TO_CHAR7",
TO_CHAR(123, '9G999D99') "TO_CHAR8"
FROM DUAL ;
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') "TO_CHAR1",
TO_CHAR(SYSDATE, 'YYYY/MM') "TO_CHAR2",
TO_CHAR(SYSDATE, 'YYYY') "TO_CHAR3",
TO_CHAR(SYSDATE, 'DD') "TO_CHAR4",
TO_CHAR(SYSDATE, 'DAY') "TO_CHAR5",
TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS') "TO_CHAR6",
TO_CHAR(TO_DATE('20020101','YYYYMMDD'), 'YYYY-MM-DD') "TO_CHAR7",
TO_CHAR(TO_DATE('20020101','YYYYMMDD'), 'YYYYMMDD HHMISS') "TO_CHAR8"
FROM DUAL ;
SELECT TO_NUMBER('123456.9') "TO_NUMBER1",
TO_NUMBER('1234567') "TO_NUMBER2"
FROM DUAL ;
SELECT TO_NUMBER('123,456.9', '999,999.9') "TO_NUMBER1",
TO_NUMBER('1,234,567', '9G999G999') "TO_NUMBER2"
FROM DUAL ;
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
SELECT TO_DATE('20020824') "TO_DATE1",
TO_DATE('2002-08-24') "TO_DATE2",
TO_DATE('200208') "TO_DATE3"
FROM DUAL ;
SELECT TO_DATE('20020824', 'YYYYMMDD') "TO_DATE1",
TO_DATE('2002-08-24', 'YYYY-MM-DD') "TO_DATE2",
TO_DATE('200208', 'YYYYMM') "TO_DATE3"
FROM DUAL ;
SELECT TO_DATE('2002/08/24 08:14:06', 'YYYY/MM/DD HH24:MI:SS') "TO_DATE1",
TO_DATE('2002/08/24 08:14:06 오후', 'YYYY/MM/DD HH:MI:SS AM') "TO_DATE2"
FROM DUAL ;
/*------------------------------------
* 함수 - Group Function *
-------------------------------------*/
SELECT AVG(HEIGHT), AVG(WEIGHT) FROM PERSONNEL;
SELECT MAX(EMPNO), MAX(EMP_NAME), MIN(EMPNO), MIN(EMP_NAME) FROM PERSONNEL;
SELECT MAX(HEIGHT), MIN(HEIGHT) FROM PERSONNEL;
SELECT SUM(WEIGHT) FROM PERSONNEL;
SELECT COUNT(*), COUNT(EMPNO), COUNT(JIKCH_CODE) FROM PERSONNEL;
/*----------------------------
* 함수 - ETC *
----------------------------*/
SELECT EMPNO, EMP_NAME, HOBBY 취미, WELL 특기
FROM PERSONNEL
WHERE EMPNO BETWEEN '98001' AND '98005';
SELECT EMPNO, EMP_NAME, HOBBY 취미, NVL(WELL, '(없다)') 특기
FROM PERSONNEL
WHERE EMPNO BETWEEN '98001' AND '98005';
SELECT DECODE( '나', '나', '맞다', '아니다' ) "나가 나면 맞다, 아니면 아니다"
FROM DUAL ;
SELECT DECODE( '나', '대명', '아니다',
'너' , '아니다',
'그' , '아니다',
'나' , '맞다',
'모르겠다' ) FROM DUAL ;
SELECT EMPNO,
EMP_NAME,
DECODE (HT_CODE, '1', '현재원', '2', '휴직', '퇴사') HT_CODE
FROM PERSONNEL
WHERE EMPNO BETWEEN '98071' AND '98080';
SELECT GREATEST (132, 33, 45, 90, 60.77) GREATEST,
LEAST (132, 33, 45, 90, 60.77) LEAST
FROM DUAL;
SELECT GREATEST ('이공명', '이대명', '최수미') GREATEST,
LEAST ('이공명', '이대명', '최수미') LEAST
FROM DUAL;
SELECT USERENV('LANGUAGE') "LANGUAGE",
USERENV('TERMINAL') "TERMINAL",
USERENV('SESSIONID') "SESSIONID"
FROM DUAL;
SELECT UID, USER FROM DUAL;
---------------------------------------------------------------------------------------------------
오라클 내장함수
Error 관련 내장함수
---------------------------------------------------------------------
SQLCODE 현재 발생한 오류에 따른 오류 코드를 반환한다.
SQLERRM 오라클 오류 코드와 연결된 오류 메시지를 반환한다.
Number 관련 내장함수
---------------------------------------------------------------------
ABS 어떤 수의 절대값을 반환한다.
ACOS 어떤 수의 아크코사인을 라디안 단위로 반환한다.
ASIN 어떤 수의 아크사인을 라디안 단위로 반환한다.
ATAN 어떤 수(x)의 아크탄젠트를 라디안 단위로 반환한다.
ATAN2 어떤 수(y/x)의 아크탄젠트를 라디안 단위로 반환한다.
CEIL 지정된 수보다 크거나 같은 최소 정수 값을 반환한다.
COS 어떤 수의 코사인을 라디안 단위로 반환한다.
COSH 어떤 수의 쌍곡선 코사인을 라디안 단위로 반환한다.
EXP 어떤 수의 지수 값을 반환한다. E=2.7182818
FLOOR 지정된 수보다 크거나 같은 최대 정수 값을 반환한다.
LN 어떤 수 x의 대수 값을 반환한다.
LOG 어떤 수 y에서 x를 밑으로 한 대수 값을 반환한다.
MOD 어떤 수 x를 y로 나눈 나머지를 반환한다.
POWER 어떤 수 x에 y 제곱한 값을 반환한다.
ROUND x를 y자리에서 반올림한 값을 반환한다.
SIGN 숫자 x의 부호에 따라 양수이면 +1, 음수이면 -1, 0이면 0을 반환한다.
SIN 어떤 수의 사인을 라디안 단위로 반환한다.
SINH 어떤 수의 쌍곡선 사인을 라디안 단위로 반환한다.
SQRT 어떤 수 x의 제곱근을 반환한다. X는 반드시 양의 실수이다.
TAN 어떤 수의 탄젠트를 라디안 단위로 반환한다.
TANH 어떤 수의 쌍곡선 탄젠트를 라디안 단위로 반환한다.
TRUNC 어떤 수 x를 y 위치에서 잘라낸다.
Character 관련 내장함수
---------------------------------------------------------------------
ASCII 문자의 ASCII 코드를 반환한다.
CHR 주어진 ASCII 값에 해당하는 문자를 반환한다.
CONCAT 두 개의 스트링을 서로 연결한다. (|| 연산자를 사용한 것과 같다)
INITCAP 각 단어의 첫 글자를 대문자로 나머지는 소문자로 변환한 스트링을 반환한
다(문자가 아닌 값에는 영향이 없다)
INSTR 어떤 스트링에서 특정 스트링의 위치를 반환한다.
INSTRB 어떤 스트링에서 특정 스트링의 위치를 반환하는데, 값을 싱글 바이트 문
자 시스템에 대한 바이트 단위로 반환한다.
LENGTH 뒤에 따라오는 공백을 포함하여 문자 스트링의 길이를 반환한다. 값이 null
이면 null을 반환한다.
LENGTHB 문자 스트링의 길이를 반환하는데, 이것은 값을 싱글 바이트 문자 셋에 대
한 바이트 단위로 반환한다.
LOWER 전체 문자 스트링을 소문자로 바꾼다. 문자가 아닌 값에는 영향이 없다
LPAD 스트링의 왼쪽에 지정한 어떤 스트링을 덧붙인다.
LTRIM 왼쪽에 있는 문자 스트링을 잘라낸다.
NLS_INITCAP NLSSORT에 의해 지정된 다른 정렬방법을 사용할 수 있다는 것을 제외하
고는 INITCAP 함수와 같다.
NLS_LOWER NLSSORT에 의해 지정된 다른 정렬방법을 사용할 수 있다는 것을 제외하
고는 LOWER 함수와 같다.
NLS_UPPER NLSSORT에 의해 지정된 다른 정렬 방법을 사용할 수 있다는 것을 제외
하고는 UPPER 함수와 같다.
NLSSORT 문자를 정렬하는 방법을 바꾼다. 이것은 NLS 함수를 사용하기 전에 지정
해야 한다. 그렇지 않으면 기본 정렬이 사용된다.
REPLACE 어떤 스트링이 나타나는 곳마다 다른 스트링으로 바꾼다.
RPAD 스트링의 오른쪽에 지정된 어떤 스트링을 덧붙인다.
RTRIM 오른쪽에 있는 문자 스트링을 잘라낸다.
SOUNDEX 스트링의 음향을 반환한다. 철자는 다르지만 소리는 비슷한 단어에 유용하
다.
SUBSTR 어떤 스트링에서 스트링의 일부를 반환한다.
SUBSTRB 싱글 바이트 문자 시스템을 처리하기 위해 파라미터가 문자가 아닌 바이트
단위로 표현된다는 것을 제외하고는 SUBSTR 함수와 같다.
TRIM LTRIM과 RTRIM 함수의 기능을 조합한 것이다. 스트링에서 앞 뒤 문자
를 잘라낸다.
TRANSLATE REPLACE와 똑 같지만 이것은 스트링 단위가 아닌 문자 단위로 작동한
다.
UPPER 전체 문자 스트링을 대문자로 바꾼다. 문자가 아닌 값에는 영향이 없다.
Conversion 관련 내장함수
---------------------------------------------------------------------
CHARTOROWID 오라클이 제공하는 외부형식으로 된 CHAR이나 VARCHAR2를 그 내부
바이너리 형식으로 변환한다.
CONVERT 한 문자 셋을 다른 문자 셋으로 변환한다.
HEXTORAW 16진 스트링 값을 내부 원시 값으로 변환한다.
RAWTOHEX 내부 원시 값을 외부 16진 스트링으로 변환한다.
ROWIDTOCHAR ROWID를 외부적인 18 문자 스트링 표현으로 변환한다.
TO_CHAR DATES, MLSLABELS, NUMBERS를 VARCHAR2 스트링으로 변환한다.
TO_DATE CHAR이나 VARCHAR2 스트링을 DATE 값으로 변환한다.
TO_LABEL CHAR이나 VARCHAR2 스트링을 MLSLABEL 값으로 변환한다.
TO_MULTI_BYTE 싱글 바이트 스트링으로 된 문자를 멀티 바이트 스트링으로 변환한다.
TO_NUMBER CHAR이나 VARCHAR2 스트링을 NUMBER 값으로 변환한다.
TO_SINGLE_BYTE 멀티 바이트 스트링으로 된 문자를 싱글 바이트 스트링으로 변환한다.
Date 관련 내장함수
---------------------------------------------------------------------
ADD_MONTHS 지정된 날짜에 1달을 더한다. 만일 결과로 나온 달이 현재일수보다 작은
일수를 갖고 있는 달로 변경되면 그 달의 마지막 날을 반환한다.
LAST_DAY 주어진 달의 마지막 날을 반환한다.
MONTHS_BETWEEN 두 날짜 사이의 개월 수를 계산한다. 만일 두 날짜가 그 달의 마지막 날이
라면 정수를 반환하고 그렇지 않으면 한 달을 31일로 계산한 분수 값을 반
환한다.
NEW_TIME 사용자가 지정한 시간대에 대한 시간/날짜 값을 반환한다.
NEXT_DAY 시작 날짜 다음에 지정된 요일이 처음으로 나오는 날짜를 반환한다.
ROUND 월, 연도, 세기 등과 같이 선택한 날짜 파라미터를 반올림한다.
SYSDATE 시스템 날짜와 시간을 DATE 형식으로 반환한다.
TRUNC 일, 월 등과 같이 지정된 날짜 파라미터를 잘라낸다.
집합함수
---------------------------------------------------------------------
AVG 지정된 컬럼 값의 평균을 구한다.
COUNT 쿼리에서 반환되는 행의 개수를 구한다.
GLC MLSLABEL의 최대 아래쪽 한계를 구한다.
LUB MLSLABEL의 최대 위쪽 한계를 구한다.
MAX 쿼리에서 지정된 컬럼의 값 중 가장 큰 값을 반환한다.
MIN 쿼리에서 지정된 컬럼의 값 중 가장 작은 값을 반환한다.
STDDEV 쿼리에서 선택된 컬럼의 표준 편차를 반환한다.
SUM 쿼리에서 선택된 컬럼의 합계를 구한다.
VARIANCE 쿼리에서 선택된 컬럼의 분산을 구한다.
DISTINCT 쿼리의 유일값만 구한다.
【예제】
SQL> select COUNT(DISTINCT job) from emp;
SQL> select COUNT(job) from emp;
SQL> select COUNT(*) from emp;
기타 내장함수
---------------------------------------------------------------------
BFILENAME 파일이 저장되어 있는 물리적 LOB 바이너리 파일과 연결되어 있는 포인
터를 반환한다. 오라클에서는 포인터를 locator라고 부른다.
DECODE 값 목록 중에서 IF-THEN-ELSE 문과 같이 작동한다.
DUMP VARCHAR2 스트링으로 된 덤프 값을 제공한다. 스트링을 여러 가지 다른
형식으로 된 표현으로 보여주기 위해서이다.
EMPTY_BLOB 데이터를 갖고 있지 않는 컬럼이나 BLOB 변수를 초기화하는데 사용한다.
EMPTY_CLOB 데이터를 갖고 있지 않는 컬럼이나 CLOB 변수를 초기화하는데 사용한다.
GREATEST 값이나 식의 목록에서 가장 큰 값을 반환한다.
GREATEST_LB MLSLABELS 목록에서 최대 아래쪽 한계를 반환한다.
LEAST 값이나 식의 목록에서 가장 작은 값을 반환한다.
LEAST_LB MLSLABELS 목록에서 최소 아래쪽 한계를 반환한다.
NLS_CHARSET_ID NLS 문자셋 이름과 연결된 NLS 문자셋 ID 번호를 반환한다.
NLS_CHARSET_NAME 함수에 전달된 ID와 연결된 NLS 문자셋 이름을 반환한다.
NVL 값이나 식의 목록에서 처음으로 NULL이 아닌 값을 선택한다.
UID 오라클 사용자에게 할당된 사용자 ID를 반환한다.
USER 현재 사용자 이름을 VARCHAR2 스트링으로 반환한다.
USERENV 현재 작업환경에 관한 정보를 반환한다.
VSIZE 어떤 값의 바이트 수를 반환한다.
【예제】
$ sqlplus scott/tiger
SQL> select * from emp;
SQL> select uid from dual;
SQL> select user from dual;
SQL> select count(user) from emp;
SQL> select avg(sal) from emp;
SQL> select max(sal) from emp;
SQL> select min(sal) from emp;
SQL> select stddev(sal) from emp;
SQL> select sysdate from dual;
SQL> select last_day(sysdate) from dual;
SQL> select to_char(sysdate) from dual;
SQL> select to_char(next_day(sysdate,'Monday'),'MM/DD/yyyy')
2 from dual;