* 단일 행 함수
-함수가 정의된 SQL문이 실행될 때 각각의 ROW에 대해 수행되며 ROW 당 하나의 결과를 리턴 해줍니다.
-인수로는 상수,변수,표현식들이 사용될 수 있습니다.
-SELECT,WHERE,ORDER BY 절에 사용할 수 있습니다.
구 분 | 함수 | 내 용
---------------------------------------------------------------------------------------
LOWER 모든 문자를 소문자로
UPPER 모든 문자를 대문자로
INITCAP 첫 글자는 대문자,나머지는 소문자로
CANCAT 첫 번째 문자와 두 번째 문자를 연결
SUBSTR 문자의 길이를 리턴할 때
LENGTH 문자의 길이를 리턴할 때
NVL 널값을 다른 값으로 대체할 때
NVL2 조건에 의해 널값을 다른 값으로 대체할 때
SUBSTR 특정 문자의 문자열중 필요 부분만 선별하여 사용
문자 RTRIM 서브 스트림의 정확한 위치와 길이를 요구(오른쪽)
함수 LTRIM 서브 스트림의 정확한 위치와 길이를 요구(왼쪽)
RPAD 문자열을 제외한 공간에 지정한 문자열로 대체(오른쪽)
LPAD 문자열을 제외한 공간에 지정한 문자열로 대체(왼쪽)
TRANSLATE 첫 문자는 탐색집합의 첫 문자로 대체(2번째도 동일)
REPLACE 특정 문자열을 다른 문자열로 대체
SOUNDX 같은 단어 또는 유사한 사운드 단어를 음성학적으로
LENGTH 문자의 실제 길이를 변환할 때
LENGTHB 문자열의 실제 길이를 변환할 때
INTSTR 문자열 내의 특정 스트림의 위치
NULLIF 조건이 같으면 NULL,다르면 지정된 값을 리턴할 때
COALESCE 조건에 따라 여러 가지 값을 리턴할 때
-----------------------------------------------------------------------------------------
시스템 USER 현재 DB 사용자
함수 USERID 현재 DB 사용자에게 할당되는 사용자번호
-----------------------------------------------------------------------------------------
*문자함수의 예제
EX) -2개의 문자값을 결합합니다.
SQL>SELECT CONCAT(CONCAT(ename, ' is a '),job)
FROM emp;
CONCAT(CONCAT(ENAME,'ISA
---------------------------------
SMITH is a CLERK
ALLEN is a SALESMAN
-정의된 문장 단어의 첫 번째 문자를 대문자로 변환
SOL> SELECT INITCAP( 'the soap') FROM DUAL;
INITCAP(
-----------
The Soap
-정의된 문장의 왼쪽 나머지 공간을 지정한 문자로 채웁니다.
SQL> SELECT LPAD( 'Page 1' , 15 , '*.') FROM DUAL;
LPAD( 'PAGE1',15
---------------------
*.*.*.*.*Page 1
-정의된 문장의 왼쪽부터 지정된 단어가 발견되면 제거합니다.
SQL> SELECT LTRIM( 'xyxXxyLAST WORD','xy') FROM DUAL;
LTRIM('XYXXX
-------------------
XxyLAST WORD
-정의된 문장에서 해당 문자가 발견되면 지정된 문자로 변경합니다.
SQL> SELECT REPLACE( 'JACK and JUE' , 'J' , 'BL') FROM DUAL;
REPLACE( 'JACKA
-----------------------
BLACK and BLUE
-정의된 문자의 오른쪽 나머지 공간을 지정한 문자로 채웁니다.
SQL> SELECT RPAD(ename, 11 ,'ab' ) FROM emp WHERE ename = 'TURNER' ;
RPAD(ENAME , 11 ,'AB')
---------------------------
TURNERababa
-정의된 문자의 오른쪽부터 지정된 단어가 발견되면 제거합니다.
SQL> SELECT RTRIM( 'TURNERyxXxy' , 'xy') FROM DUAL ;
RTRIM( 'TU
-------------
TURNERyxX
-정의된 문장의 지정된 위치부터 해당 길이 만큼만 추출합니다.
SQL> SELECT SUBSTR( 'ABCDEFG' , 3 , 2 ) FROM DUAL ;
SU
-------
CD
-정의된 문장의 뒤에서부터 지정된 위치의 해당 길이 만큼만 추출합니다.
SQL> SELECT SUBSTR ( 'ABCDEFG' , -3 , 2 ) FROM DUAL ;
SU
--------
EF
-문자 'Q'를 ASCII 코드로 변환합니다.
SQL> SELECT ASCII ( 'Q' ) FROM DUAL ;
ASCII ( 'Q' )
---------------
81
-정의된 문장에서 지정된 위치에 존재하는 문자의 위치 값을 찾아 줍니다.
SQL> SELECT INSTR ( 'CORPORATE FLOOR' , 'OR' ,3 ,2 ) FROM DUAL ;
INSTR ( 'CORPORATEFLOOR' , 'OR' , 3 , 2 )
---------------------------------------------
14
-정의된 문장의 길이를 변환합니다.
SQL> SELECT LENGTHB ( '가나다라마바사') FROM DUAL ;
LENGTH( '가나다라마바사' )
---------------------------
14
-정의된 단어 중에 가장 높은 값을 찾아줍니다.
SQL> SELECT GREATEST ( 'HARRY' , 'HARIOT' ,'HALORD' ) FROM DUAL ;
GREAT
---------
HARRY
-정의한 컬럼이 NULL이면 지정한 값으로 대체합니다.
SQL> SELECT NVL (sal , 0 ) , NVL( ename , '*' ) , NVL(hiredate , '01-JAN-02' ) FROM emp ;
NVL(SAL , 0 ) NVL(ENAME , NVL(HIRE
------------------------------------------------
800 SMITH 80/12/17
*시스템 함수
-현재 어떤 사용자로 데이터 베이스에 접속하였는지 알 수 있습니다.
SQL> SELECT USER FROM dual ;
USER
-------
SCOTT
* 숫자함수/날짜함수
------------------------------------------------------------------------------
구 분 함 수 내 용
------------------------------------------------------------------------------
ROUND 해당 소수점 자리에서 반올림할 때
TRUNC 해당 소수점 자리에서 절삭할 때
MOD(m/n) m을 n으로 나누고 남은 나머지를 리턴할 때
ABS 숫자 값을 절대값으로 바꾼다
SIGN 숫자가 양수:+1, 음수:-1, 0:0
숫자함수 FLOOR 실수값을 정수값으로
CEIL 그 수보다 가장 크거나 작은값을 리턴
POWER 해당 수에 대한 지수값을 표현
LOG 로그값으로 변환
SIN SIN값
COS COS값
TAN TAN값
------------------------------------------------------------------------------
SYSDATE 현재 시스템 날짜를 보여줄 때
ADD_MONTHS 지정한 날짜에 몇 월을 추가한 결과의 월을 계산할 때
LAST_DAY 해당 월의 마지막 날짜를 알고자 할 때
날짜함수 NEW_TIME 해당 표준시로 시간을 변환할 때
NEXT_DAY 해당 날짜의 다음 지정한 날짜로 현환할 때
NONTH_BETWEEN 지정된 월 간의 월수를 알고자 할 때
------------------------------------------------------------------------------
* 숫자함수 예제....
-정의된 값을 절대값으로 변환 합니다
SQL> SELECT ABS(-15) FROM DUAL ;
ABS(-15)
-----------
15
-정의된 값의 올림된 값으로 변환합니다
SQL> SELECT CEIL(15.7) FROM DUAL ;
CEIL(15.7)
-------------
16
-정의된 값의 내림된 값으로 변환합니다
SQL> SELECT FLOOR(15.7) FROM DUAL ;
FLOOR(15.7)
---------------
15
-정의된 산술식의 COSINE 값으로 변환합니다
SQL> SELECT COS(180*3.14/180) FROM DUAL ;
COS(180*3.14/180)
--------------------
-.99999873
-정의된 숫자의 지수승값을 계산합니다
SQL> SELECT EXP(4) FROM DUAL ;
EXP(4)
---------
54.59815
-뒤에 정의된 수로 앞에 정의된 수를 나눈 나머지 값을 반환합니다
SQL> SELECT MOD(11,4) FROM DUAL ;
MOD(11,4)
------------
3
-정의된 수를 지정한 자리 수에서 반올림합니다
SQL> SELECT ROUND(15.193 , 1) FROM DUAL ;
ROUND(15.193 , 1)
-------------------
15.2
-정의된 값이 음수이면 -1 , 0 이면 0, 양수이면 1을 리턴합니다
SQL> SELECT SIGN(-15) FROM DUAL ;
SIGN(-15)
------------
-1
-정의된 수를 지정한 자리 수 에서 절삭합니다
SQL> SELECT TRUNC(15.97 , 1) FROM DUAL ;
TRUNC(15.79 , 1)
-----------------
15.7
* 날짜 함수 예제
-현재 시스템 날짜를 제공합니다
SQL> SELECT SYSDATE FROM DUAL ;
SYSDATE
-----------
06/11/13
-해당 날짜에 지정한 달 수만큼 더합니다
SQL> SELECT HIREDATE , ADD_MONTHS(HIREDATE , 1)
FROM EMP WHERE EMPNO = 7782 ;
HIREDATE ADD_MONT
--------------------------
81/06/09 81/07/09
-정의된 날짜의 달에서 마지막 일이 몇 일인지 알 수 있습니다
SQL> SELECT HIREDATE , LAST_DAY(HIREDATE)
FROM EMP WHERE EMPNO = 7782 ;
HIREDATE LAST_DAY
--------------------------
81/06/09 81/06/30
-정의된 두 날짜간의 차이 값을 알 수 있습니다
SQL> SELECT HIREDATE , MONTHS_BETWEEN(SYSDATE , HIREDATE)
FROM EMP WHERE EMPNO = 7782 ;
HIREDATE MONTHS_BETWEEN(SYSDATE , HIREDATE)
------------------------------------------------------------
81/06/09 252.930883
-정의된 날짜를 녀도 값을 기준으로 반올림 합니다
SQL > SELECT
ROUND(TO_DATE(' 27-OCT-98' , 'DD-MON-YY' ) ,
'YEAT' ) FROM DUAL ;
ROUND(TO
------------
99/01/01
변환 함수
---------------------------------------------------------------------------
구 분 함 수 내 용
---------------------------------------------------------------------------
TO_CHAR 숫자,날짜 타입의 Data를 varchar2타입으로 변환
변환함수 TO_NUMBER 숫자를 포함하는 문자 String을 number 타입으로 변환
TO_DATE 문자 String을 날짜 타입으로 변환
---------------------------------------------------------------------------
*날짜 형식 요소
---------------------------------------------------------------------------
형식 요소 설 명
---------------------------------------------------------------------------
MM 달 수(ex : 10)
MON 월 이름을 3자리 문자로 표현(ex : JAN , FEB , MAR 등)
MONTH 월 이름(ex : JANUARY,FEBUARY,MARCH 등)
DD 날짜 (ex : 14)
D 주의 일수 (ex : 4)
DY 요일 이름을 3자리 문자로 표현(ex : SUN , MON , TUE 등)
DAY 요일 이름(ex : SUNDAY , MONDAY 등)
YYYY 년도 4자리 수 (ex : 2002)
YY 년도의 마지막 2자리(ex : 02)
----------------------------------------------------------------------------
*시간 형식 요소
----------------------------------------------------------------------------
형식 요소 설 명
----------------------------------------------------------------------------
9 숫자(ex : 9999 → 1534)
0 자리 수가 비면 0으로 채워줌 ( ex : 09999 → 01534 )
$ 금액에 $를 표시해줌 (ex : $99999 → $1534 )
. 명시한 위치에 소수점을 표시함 (ex : 99999.99 → 1534.00 )
, 명시한 위치에 콤마를 표시함 (ex : 999,999 → 1,534 )
----------------------------------------------------------------------------
변환 함수 예제들.....
-현재 날짜가 한 주에서 몇번째 일인지 알 수 있습니다
SQL> SELECT SYSDATE , TO_CHAR ( SYSDATE , 'D' )
FROM dual ;
SYSDATE TO_CHAR
--------------------------
02/09/26 5
-정의된 날짜의 출력 포맷을 DD-MM-YY로 출력합니다
SQL> SELECT ename , TO_CHAR(hiredate , 'DD-MM-YY' ) HIREDATE
FROM emp ;
ENAME TO_CHAR(hiredate , 'DD-MM-YY' )
------------------------------------------------
SMITH 17 11 90
ALLY 20 02 91
......................
-일자를 출력할 때 0 값을 제거합니다(09 → 9 , 01 → 1)
SQL> SELECT ename , TO_CHAR (hiredate , 'fmDD-MM-YY' ) HIREDATE
FROM emp ;
ENAME TO_CHAR(hiredate , 'fmDD-MM-YY' )
---------------------------------------------------
....................................
MARTIN 9 09 91
....................................
-문자 100을 숫자값으로 출력합니다
SQL> SELECT TO_NUMBER ( '100' ) FROM dual ;
TO_NUMBER
--------------
100
-현재 시간을 AM , PM 표기법으로 출력합니다
SQL> SELECT TO_CHAR( SYSDATE , 'AM HH:MI ' )
FROM dual ;
TO_CHAR(
-----------
오전 10 : 10
-숫자값을 출력할 때 금액표시를 합니다
SQL> SELECT TO_CHAR( 12506 , '$9099,999' ) FROM dual ;
TO_CHAR(
-----------
$010,234
-정의된 날짜를 지정한 포맷으로 출력합니다
SQL> SELECT TO_DATE( '01-JUL-99' , 'DD-MM-YY' )
FROM dual ;
TO_DATE(
-----------
99/07/01
-정의된 시간을 지정한 포맷으로 출력합니다
SQL> SELECT TO_DATE( '01:30' , 'HH24:MI' ) FROM dual ;
TO_DATE
----------
13:30
단일행함수
* NVL2 ( Colum, Express1, Express2 )
-해당 컬럼이 null이면 Express-2 의 값을 , null이 아니면 Express-1의 값을 리턴해 줍니다.
오라클 이전 버전에서 사용되던 NVL함수는 정의된 컬럼의 값이 null인 경우 지정한 값으로
대체하는 기능을 가지고 있었습니다. 반대로, NVL2 함수는 정의된 컬럼의 값이 지정한
값인 경우 null값으로 대체된다.
NVL2 함수는 해당 컬럼이 null 값이 아니면 Express2의 값을 리턴해 주고 null 값이면
Express3의 값을 리턴해 주는 기능을 가지고 있습니다.
SQL> SELECT empno, ename, nv12( comm, comm.*1.1, 0 )
FROM emp ;
EMPNO ENAME COMM NVL2( COMM, COMM*1.1, 0 )
----------------------------------------------------------
7369 SMITH 0
7499 ALLEN 300 330
7521 WARD 500 550
7566 JONES 0
..............................................
->COMM이 null인 경우 null이 아닌 경우
* NULLIF ( Express1, Express2 )
-Express-1과 Express-2의 값을 비교하여 그 값이 같으면 null을 리턴하고 다르면 Express-1의
값을 리턴해 줍니다.
NULLIF 함수는 Express-1 과 Express-2 의 값을 비교하여 그 값이 같으면 null 값을 리턴하고
서로 다른 값을 가지고 있으면 Express-1의 값을 리턴해 주는 함수입니다.
LENGTH(first_name)의 값과 LENGTH(last_name)의 값을 NULLIF 함수로 비교하여 같으면
null 값을 화면에 출력하고 다르면 LENGTH(first_name)의 값을 화면에 출력합니다.
SQL> SELECT empno, ename, nullif( comm, 0 )
FROM emp ;
EMPNO ENAME COMM NULLIF( COMM, 0 )
--------------------------------------------------
7369 SMITH
7370 ALLEN 300 300
7521 WARD 500 500
7566 JONES
7654 MARTIN 1400 1400
.....................................
->COMM이 NULL이므로 NULL, COMM이 NULL이 아니므로 COMM값으로...
*COALLESCE 함수
이 함수는 EXPRESS-1의 값이 NULL값이 아니면 EXPRESS-1의 값을 화면에 출력해 주고
NULL 값이면 EXPRESS-2의 값을 EXPRESS-1의 값과 EXPRESS-2의 값모두 NULL이면
EXPRESS-N의 값을 화면에 출력해 주는 함수입니다. 문법에서 정의된 각 컬럼에서 NULL이
아닌 첫 번째 컬럼의 값을 돌려줍니다.
SQL> SELECT coalesce( comm,sal ) FROM emp ;
COALESCE( COMM,SAL )
-------------------------
800
300
SQL> SELECT coalesce ( comm, 100 ) FROM emp ;
COALESCE( COMM,100 )
-------------------------
100
300
500
->이 문법은 COALESCE([컬럼],[값]) 문법에서 정의된 컬럼이 NULL이면 정의된 값을
더한 결과를 돌려줍니다. 원래 NULL이지만 100을 더한 값이 출력됩니다.
*TRIM함수
이 함수는 오라클 8i 버전에서 추가된 함수이며 이전 버전에서 제공되던 LTRIM 함수와
RTRIM 함수를 결합한 형태의 함수입니다. TRIM 함수는 문자값의 왼쪽 또는 오른쪽 부분에
정의한 문자값이 존재하면 그 문자를 절삭시키는 기능을 가지고 있습니다. TRIM 함수는
3가지 종류의 기능을 가지고 있습니다.
-먼저,LEADING은 이전의 LTRIM 함수와 동일한 기능을 가지고 있습니다.정의된 컬럼의 값
왼쪽에 해당 문자열이 존재하면 문자값을 절삭시켜서 화면에 출력합니다.
SQL> SELECT ename, TRIM( LEADING 'A' FROM ename ) as TRIM
FROM emp
WHERE ename LIKE 'A%' ;
ENAME TRIM
-------------------------
ALLEN LLEN
ADAMS DAMS
-TRAILING은 이전의 RTRIM 함수의 기능을 가지고 있으며 컬럼값의 오른쪽에 존재하는 문자값을
절삭해줍니다.
SQL> SELECT ename, TRIM( TRAILING 'N' FROM ename ) as TRIM
FROM emp
WHERE ename LIKE '%N' ;
ENAME TRIM
---------------------
ALLEN ALLE
MARTIN MARTI
-BOTH는 왼쪽, 오른쪽에 해당 문자열이 있으면 절삭해 주는 기능을 가지고 있습니다.
SQL> SELECT ename, TRIM( BOTH 'A' FROM ename ) as TRIM
FROM emp
WHERE ename LIKE 'A%' ;
ENAME TRIM
--------------------
ALLEN LLEN
ADAMS DAMS
->ENAME 컬럼에서 마지막 문자가 'A'인 값은 발견되지 않았고 첫번째 문자가 'A'인
사원은 ALLEN과 ADAMS
