[Oracle] SQL 연산자와 주요 SQL함수

SQL 연산자란 피연산자를 대상으로 특정 연산을 수행후 결화를 반호나하는 역할을 한다.

 

SQL에서 피연산자는 개별 데이터나 데이터로 이루어진 표현식이다.

 

연산자	설명
+	두 수나 두 날짜를 더함
-	두 수나 두 날짜를 뺌
*	두 수를 곱함
/	왼쪽 수를 오른쪽 수로 나눔
||	두 문자를 결합함

 

 아래와 같이 || 연산자 같은경우 , dual테이블 (오라클에서 제공하는 임시테이블)로 문자열을 결합해 반환할수 있다

select 'a' || 'b'
from dual;

 

 

SQL 함수는 크게 숫자형, 문자형, 날짜형, 형변환 함수가 주요 함수들만 테이블로 정의를 해보겠다

 

*숫자형 함수

함수명	기능	사용 예
ABS(n)	n의 절대값을 반환	SELECT ABS( -1) FROM DUAL; -> 1
CEIL(n)	n과 같거나 큰 최소 정수 반환	SELECT CEIL( 10.6) FROM DUAL; -> 11
EXP(n)	e(e= 2.71828183....)의 n승을 반환	SELECT EXP( 10 ) FROM DUAL; -> 22026.4655......
FLOOR(n)	n과 같거나 작은 최대 정수 반환	SELECT FLOOR( 10.6) FROM DUAL; ->10
LN(n)	n의 자연로그 값 반환. n은 0보다 커야함	SELECT LN( 10) FROM DUAL; -> 2.3025850929405
LOG( n2, n1)	n2는 밑, n1은 진수. n1은 양수,n2는 0과1이 아닌 양수 여야함	SELECT LOG( 10,100) FROM DUAL; -> 2
MOD( n2, n1)	n2를 n1로 나눈 나머지 반환	SELECT MOD( 11,4) FROM DUAL; -> 3
POWER( n2, n1)	n2의 n1승을 반환	SELECT POWER(3, 2) FROM DUAL; -> 9
ROUND( n, i)	n의 소수점 기준 (i +1) 번째에서 반올림한 값을 반환. 정수로 만들시 i는 0(소수점 첫째 자리 기준 반올림)	SELECT ROUND( 10.545, 2) FROM DUAL;  -> 10.55
SIGN(n)	n의 부호 반환, n이 양수면 1 음수면 -1 0이면 0반환	SELECT SIGN (-110) FROM DUAL; -> -1
SQRT(n)	n의 제곱근 값을 반환	SELECT SQRT( 2) FROM DUAL; -> 1.4142135623731
TRUNC( n1, n2)	n1의 소수점 기준 n2 자리에서 절삭, n2 생략시 0	SELECT TRUNC (10.545,2) FROM DUAL; -> 10.54

 

 

*문자형 함수 

 ( 문자형 데이터를 반환하는 문자형 함수 )

함수명	기능	사용예
CHR(n)	n은 숫자로 n 값에 해당되는 문자를반환	SELECT CHR ( 65 ) || CHR ( 66 ) || CHR( 67)  FROM DUAL; -> ABC
CONCAT( char1, char2)	char1과 char2 문자를 결합한 결과를 반환, ||와 같은기능	SELECT CONCAT ( 'A' , 'B') FROM DUAL; -> AB
INITCAP( char)	char의 첫번째 문자를 대문자로 변환	SELECT INITCAP( 'the') FROM DUAL; -> The
LOWER (char)	char을 소문자로 변환	SELECT LOWER( 'THE') FROM DUAL; -> the
UPPER (char)	char을 대문자로 변환	SELECT UPPER( 'the') FROM DUAL; -> THE
LPAD (expr1, n, expr2)	expr1을 반환하는데, expr2를 (n - expr1 길이) 만큼 왼쪽을 채워 반환	SELECT LPAD( 'THE', 5, '*') FROM DUAL; -> **THE
RPAD (expr1, n, expr2)	expr1을 반환하는데, expr2를 (n - expr1 길이) 만큼 오른쪽을 채워 반환	SELECT RPAD( 'THE',5, '*') FROM DUAL; -> THE**
LTRIM (expr1, expr2)	expr1의 왼쪽에서 expr2 를 제거한 결과를 반환	SELECT LTRIM( '**THE**', '*') FROM DUAL; -> THE**
RTRIM (expr1, expr2)	expr1의 오른쪽에서 expr2 를 제거한 결과를 반환	SELECT RTRIM ( '**THE**', '*') FROM DUAL; ->**THE
SUBSTR (char, n1,n2)	char에서 n1위치에서 시작해 n2길이 만큼 잘라낸 결과 반환 -n1을 0으로 명시하면 1이 적용 -n1이 음수면 char 오른쪽끝에서부터 거꾸로 가져옴 -n2를 생략하면 n1부터 끝까지 반환 -n2 값을 1미만으로 지정하면 NULL반환	SELECT SUBSTR('ABCDEFG',3,2) FROM DUAL; ->CD
TRIM( char)	char의 양쪽 끝 공백을 제거한 결과 반환	SELECT TRIM( ' ABCD EFG ') FROM DUAL; -> ABCD EFG

*문자형 함수 

 ( 숫자형 데이터를 반환하는 문자형 함수 )

함수명	기능	사용 예
ASCII (char)	char 문자의 ASCII 코드 값을 반환. CHR 함수와 반대 기능을함	SELECT ASCII ( 'A' ) FROM DUAL; -> 65
INSTR (char1, char2, n1, n2)	-char1에서 char2 문자를 찾아 그 시작 위치를 반환함 -n1,n2값 생략시 1이 적용됨	SELECT INSTR( 'ABABAB', 'A', 2) FROM DUAL; -> 3
LENGTH (char)	char 문자의 글자수를 반환함	SELECT LENGTH ('the') FROM DUAL; -> 3

 

*날짜형 함수

함수명	기능	사용 예
SYSDATE	현재 일자와 시간 반환	SELECT SYSDATE  FROM DUAL; -> 21-02-10
ADD_MONTHS( date, n)	date 날짜에 n개월을 더한 날짜를 반환. n이 음수이면 더하지 않고 뺀 날자를 반환	SELECT ADD_MONTHS( SYSDATE, 1) FROM DUAL; -> 21-03-10
MONTHS_BETWEEN( date1, date2)	date1과 date2 두 날짜 사이의 개월수 반환. date1이 date2보다 이후 날짜면 양수, 반대면 음수 반환	SELECT MONTHS_BETWEEN( SYSDATE+31, SYSDATE) FROM DUAL; -> 1.096.....
LAST_DAY (date)	date가 속한 월의 마지막 일자를 반환	SELECT LAST_DAY( SYSDATE) FROM DUAL; -> 21-02-28
NEXT_DAY ( date, expr)	date 날짜를 기준으로 expr에 명시한 날짜 반환.	SELECT NEXT_DAY( SYSDATE, '월요일') FROM DUAL; -> 21-02-15
ROUND ( date, format)	date를 format 기준으로 반올림한 날짜 반환. format은 YEAR,MONTH,DD,HH,HH24,MI등 사용가능	SELECT ROUND( SYSDATE,'YEAR') FROM DUAL; -> 21-01-01
TRUNC ( date, format)	date 를 format 기준으로 잘라낸 날짜 반환	SELECT TRUNC (SYSDATE, 'YEAR') FROM DUAL; -> 21-01-01

 

* 형변환 함수

함수명	기능	사용예
TO_NUMBER (char)	char을 숫자로변환	SELECT TO_NUMBER( '12345') FROM DUAL; -> 12345
TO_CHAR (n, number_format)	숫자인 n을 nuber_format에 맞게 문자로 변환	SELECT TO_CHAR( 12345,'99,999') FROM DUAL; -> '12,345'
TO_CHAR (date,date_format)	날짜인 date를 date_format 에 맞게 문자로 변환	SELECT TO_CHAR (SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL; -> 2021-02-10 16:46:53
TO_DATE (char, date_format)	문자 char을 date_format에 맞게 날짜로 변환	SELECT TO_DATE( '2021-02-10 23:52:01', 'YYYY-MM-DD HH24:MI:SS') FROM DUAL -> 21/02/10

 

* NULL 관련함수

함수명	기능
NVL (expr1, expr2)	expr1 값이 NULL 인경우 expr2를 반환하는 함수 (null 허용 컬럼값을 조회할때 해당 컬럼에 데이터가 있는경우 expr1을 그대로 보여주고 데이터가없는경우 expr2ㄹ르보여줌
NVL2 (expr1, expr2, expr3)	expr1 값이 NULL인경우 expr3을 , NULL이 아닌경우 expr2를 반환
COALESCE (expr1, expr2, ....)	매개변수인 expr1, expr2 expr3, ...에서 첫번째로 NULL아 이닌값 반환
NULLIF( expr1, expr2)	매개변수인 expr1과 expr2 값을 비교해 두값이 같으면 NULL, 같지않으면 expr1 반환

 

*기타함수

함수명	기능
GREATEST (expr1, expr2, ....)	매개변수 expr1, expr2 ... 중 가장 큰값을 찾아 반환
LEAST (expr1, expr2, ....)	매개변수 expr1, expr2 ... 중 가장 작은값을 찾아 반환
DECODE( expr, comp_var1, result1, comp_val2, result2, ..., default_value)	첫번째 매개변수인 expr 값이 comp_val1과 같으면 result1을 반환하고 comp_val2와 같으면 result2를 반환하는 식

 

 

CASE 표현식이 등장한 이후로는 DECODE 함수를 대체하는 경우가 많다.

CASE 표현식은 두가지 형태로 사용할수 있다.

 

단순형 CASE 표현식 구문

(expr 값이 WHEN 다음의 comparision_expr1와 같으면 return_expr1을,2와같으면 2반환, 모든 WHEN 절과 비교해서 같지않으면 ELSE 절로 넘어와 else_expr 반환)

*주의 할점은 return은 모두 같은 데이터형이어야한다

CASE WHEN condition1 THEN return_expr1
     WHEN condition2 THEN return_expr2
    ...
    ELSE else_expr

 

사용 예)

SELECT emp_name,age,
    CASE WHEN age BETWEEN 0 AND 19 THEN '1O대'
         WHEN age BETWEEN 20 AND 29 THEN '2O대'
         WHEN age BETWEEN 30 AND 39 THEN '3O대'
         WHEN age BETWEEN 40 AND 49 THEN '4O대'
         WHEN age BETWEEN 50 AND 59 THEN '5O대'
         ELSE '60대이상'
    END ages
    FROM emp03

CASE 표현식은 코드가 길어지는 단점이 있지만, 가독성도 좋고 다양한 조건식을 사용할수 있는 장점이 있다.