[DB] 함수

<함수>

단일 행 함수=단일 행에서 적용 가능, 하나의 결과만 return

다중 행 함수=(그룹 함수) 복수의 행을 조작하여 그룹당 하나의 결과 return

 

<단일 행 함수>

=데이터 값 조작

인수 받고 하나의 결과 return

(종류= 문자, 숫자, 날짜, 변환 함수 등... )

 

<문자 조작 함수>

🍏LOWER, UPPER

=문자를 모두 소문자 혹은 모두 대문자로 변경

🍏 INITCAP

=첫글자만 대문자, 나머지는 소문자로 변경

select last_name, LOWER(last_name), INITCAP(last_name),UPPER(last_name) 
from employees;

 

🍏 LENGTH=문자열 길이

🍏 INSTR=주어진 문자 위치 반환

select first_name, length(first_name), instr(first_name,'a')
from employees;

 

🍏 SUBSTR=부분 문자열 반환

🍏 CONCAT=합친 문자열 반환

select first_name, substr(first_name,1,3),concat(first_name, last_name)
from employees;

 

🍏 LPAD=왼쪽에 공백 부분을 문자로 채움

🍏 RPAD=오른쪽 공백 부분을 문자로 채움

select rpad(first_name,10,'-')as name, lpad(salary,10,'*')as sal
from employees;

 

🍏 LTRIM=문자열의 왼쪽부터 지정된 단어가 발견되면 제거

🍏 RTRIM=오른쪽 부터 지정된 단어 제거

문자 지정하지 않으면 공백을 제거

select ltrim('JavaSpecialist','Jav') from dual;
select rtrim('JavaSpecialist','list')from dual;
select ltrim(' JavaSpecialist') from dual;
select trim(' JavaSpecialist ') from dual;

 

🍏 REPLACE=지정된 문자열을 새로운 문자열로 대체

🍏 TRANSLATE=정의된 문자열을 1대1 대응시킨다

select replace('JavaSpecialisst','Java','BigData')from dual;
select replace('Java Specialist',' ','') from dual;
select translate('jacaspecialist','abcdefghijklmnopqrstuvwxyz', 'defghijklmnopqrstuvwxyzabc') from dual;

 

<정규표현식 함수>

정규 표현식 함수

(Regular Expression function)

=해당 패턴을 가지는 행을 찾는것에 사용

(특정 패턴을 가지는 문자를 뽑아내거나 치환하기도 하지만 잘 안함)

REGEXP_LIKE	정규표현으로 검색 실시
REGEXP_INSTR	정규표현 만족하는 부분의 최초 위치 반환. 검색 시작 위치 지정도 가능
REGEXP_SUBSTR	정규 표현 만족하는 부분 문자열 반환
REGEXP_REPLACE	정규 표현 만족하는 부분을 지정한 다른 문자열로 치환.

 

정규표현식 메타 문자

메타문자	기능	설명
?	0 또는 1	0 or 1개 ex) colu?r 는 color, colour
*	0회 이상	0번 이상 ex) ab*c 는 ac,abc,abbc,abbbc
+	1회 이상	1번 이상 ex) ab+c 는 abc,abbc,abbbc
{n}	n회	정확히 n번 일치
{m, }	m회 이상	m번 이상 일치
{m,n}	m회 이상, n회 이하	m이상 n 미만 일치 ex) a{1,3}b 는 ab, aab, aaab

.	문자	문자 한개 (줄바꿈 문자 제외)
[ ]	문자들	범위 안의 문자와 일치 ex) [abc]는 ab,bd,cd ex) [A-Z]는 A~Z중 하나 ex) [1-9]는 1~9중 하나
[^ ]	부정	해당 문자 포함하지 않는 문자 찾기 ex) [^abc]는 ab,bd,cd는 포함하지 않고 ed,fd 등은 포함함 ex) [^a-z]는 알파벳 소문자로 시작하지 안흔 ㄴ모든 문자
^	처음	문자열이나 행의 처음
$	끝	문자열이나 행의 끝
( )	그룹 묶기	식을 하나로 묶는다.

\n	n번째 패턴	1~9번째 패턴 선택
\w	"_"와 영숫자	_,영문자,숫자와 일치
\W	\w 반대	_,영문자,숫자 제외한 문자열과 일치
\s	공백	공백 문자 의미
\S	공백 제외	공백 제외 모든 것과 일치
\d	숫자	숫자 의미
\D	숫자 제외	숫자가 아닌 항목과 일치

 

문자 클래스

 

REGEXP_LIKE

= 정규 표현식 조건으로 검색.

REGEXP_LIKE(목적문자열, 정규표현식[, match_parameter])

match_parameter=i,c, n, m, x (흔히 사용하지는 않음)

 

ex) 숫자로 시작하고 영소문자로 끝나는 행 찾기

select * from test
where REGEXP_LIKE(col1,'[0-9][a-z]');

 

ex) XXX-XXXX형식으로 끝나는 행 찾기 (X는 숫자)

--WAY1
select * from test
where REGEXP_LIKE (col1,'[0-9]{3}=[0-9]{4}$');
--WAY2
select * from test
where REGEXP_LIKE (col1,'[[:digit:]]{3}=[[:digit:]]{4}$');

 

ex) phone_number열에서 XXX.XXX.XXXX형식 전화 번호를 출력

select first_name, phone_number from emoployees
where regexp_like(phone_number, '^[0-9]{3}.[0-9]{3}.[0-9]{4}$');

REGEXP_INSTR

=정규표현 만족하는 위치 찾기. 

REGEXP_INSTR(목적문자열, 정규표현식 [,검색시작위치, 발생횟수, 반환옵션, match_parameter])

select col1,
	REGEXP_INSTR(col1,'[0-9]') as data1,
    REGEXP_INSTR(col1,'%') as data2
from test;

 

REGEXP_SUBSTR

=정규표현 만족하는 부분 문자열 반환

REGEXP_SUBSTR(목적문자열, 정규표현식 [,시작위치, 발생횟수, match_parameter])

 

ex) C~Z알파벳 뽑아내기

select col1, REGEXP_SUBSTR(col1,'[C-Z]+')
from test;

 

REGEXP_REPLACE

정규표현 만족하는 부분을 다른 문자열로 치환

REGEXP_REPLACE(목적문자열, 정규표현식 [,치환문자열, 검색시작위치, 발생횟수, match_parameter])

ex) 0~2까지의 문자를 *로 변환

select col1, REGEXP_REPLACE(col1,'[0-2]+','*')
from test;

 

<숫자 함수>

ROUND(num,n)	n번째까지 반올림.  select round(45.923,-1) from dual; → 50 select round(45.923,2) from dual; → 45.92
TRUNC(num,n)	n자리 까지 삭제 select trunc(45.923) from dual; → 45 select trunc(45.923,-1) from dual; →40 select trunc(45.923,2) from dual; →45.92
ABS(num)	절대값 반환
SIGN(num)	값이 양수인지 음수인지 0 인지 (1,-1,0) 반환
CEIL(num)	소수 무조건 올림    ex) 99.2 → 100    -1.6 → -2
FLOOR(num)	소수 무조건 무시    ex) 3.4 → 3     -5.4 → -5     -2.5 → -3
REMAINDER(m,n)	m을 n으로 나눈 나머지 반환 (MOD와 다르게 REMAINDER은 BINARY_DOUBLE 타입도 가능)
POWER(m,n)	m의 n제곱
SQRT(n)	n의 제곱근
SIN(r), COS(r), TAN(r)	sin, cos, tangent 값 계산
SINH(r), COSH(r), TANH(r)	Hyperbolic sin, Hyperbolic cos, Hyperbolic tangent 값 반환
EXP(num)	e^num 반환 (e=자연상수)
LN(num)	ln(num)값 반환
LOG(m,n)	log m (n)값 반환
MOD(m,n)	m을 n으로 나눈 나머지 return

 

<날짜 함수>

SYSDATE	현재 날짜 반환 select sysdate from dual;
SYSTIMESTAMP	현재 날짜+시간 반환 select systimestamp from dual;
DUAL	SYSDATE보기 위한 dummy table

 

ex) 부서 60 사원들의 이름, 근무한 주 합계 출력

select first_name,(SYSDATE-hire_date)/7 as "Weeks"
from employees where department_id=60;

 

MONTHS_BETWEEN(date1, date2)	두 날짜 사이 월 수 반환. (결과는 음수 혹은 양수이다.) select MONTHS_BETWEEN(sysdate,hire_date) from employees;
ADD_MONTHS(date, n)	날짜에 월수n을 더한다 select ADD_MONTHS(hire_date,100) from empoloyees; → 입사 후 100개월 때 되는 날짜 return
NEXT_DAY(date, 'char')	date 이후 요일의 날짜 반환 select SYSDATE,NEXT_DATE(SYSDATE,'월') from dual; → 24/12/17 과 24/12/23를 출력 12/17은 화요일이고 12/23은 월요일이다.
LAST_DAY(date)	해당 월의 마지막 날 반환 select last_day(sysdate) from dual; →24/12/31
ROUND(date[,'fmt'])	날짜 반올림. fmt없으면 가장 가까운 날짜로 반올림 select SYSDATE, round(SYSDATE) from dual; → 17/04/04    17/04/05 select round(to_date('17/03/16'),'Month') from dual; → 17/04/01
TRUNC(date[,'fmt'])	날짜 절삭. fmr없으면 가장 가까운 날짜로 절삭 select SYSDATE, trunc(SYSDATE) from dual;  → 17/04/04    17/04/04 select trunc(sysdate,'Month') from dual; → 17/04/01 select trunc(sysdate,'Year') from dual; → 17/01/01 select tunc(to_date('17/03/16'),'Month') from dual; → 17/03/01

 

<변환 함수>

=데이터 형변환

- 암시적 형변환 = 자동으로 데이터 형태 변환

- 명시적 형변환 = 문자와 숫자, 숫자와 날짜 끼리만 변환이 가능하다.

 

🍏 암시적 형변환

값 할당시 아래는 자동으로 변환 가능

VARCHAR2 or CHAR → NUMBER

VARCHAR2 or CHAR → DATE

NUMBER → VARCHAR2

DATE → VARCHAR2

 

🍏 명시적 형변환

TO_CHAR(num [, 'fmt'])	NUMBER → VARCHAR2
TO_CHAR(date [, 'fmt'])	DATE → VARCHAR2 select to_char(hire_date, 'MM/YY') from employees; → 06/03
TO_NUMBER(char [,'fmt'])	CHARACTER → NUMBER select to_number('$5,500.00', '$99,999.99')-4000 from dual;
TO_DATE(char [, 'fmt'])	CHARACTER → DATE select hire_date from employees where hire_date=to_date('2003/06/17', 'YYYY/MM/DD');

 

 

NVL (n,m)	NULL값을 실제값으로 변환. n은 null이 있을 수 있는 열, m은 n이 null일 경우 반환할 값. null아니면 원래 값 반환 select salary*NVL(commission_pct,0) from amployees;
NVL2 (n1,n2,n3)	n1 이 null이 아니면 n2반환, null이면 n3반환 select NVL2(comission_pct,salary+(salary*commission_pct), salary) from employees;
COALESCE (n1,....)	여러 인자 중 null이 아닌 최초의 표현식 하나 반환. 모두 null이면 null반환
LNNVL(f)	f의 결과가 FALSE or UNKNOWN(NULL)이면 TRUE반환. f의 결과가 TRUE이면 FALSE반환 select * from employees where LNNVL(salary*commission_pct >= 650); → 보너스가 650 미만인 사람만 선택
DECODE( n, search1, result1, search2, result2,..., default)	n의 결과가 searchN과 같으면 resultN반환. ( if then else와 유사.) 기본값이 생략되면 searchN이 resultN과 일치하지 않는 곳에 NULL값 반환 select job_id,salary, DECODE(job_id,                                       'IT_PROG', salary*1.10,                                      'FI_MGR', salary*1.15,                                      'FI_ACCOUNT', salary*1.20, salary) as revised_salary from employees;
CASE ~ WHEN ~ THEN	조건문과 같은 기능 WAY1 : CASE뒤에 표현식이 있으면 WHEN절에는 값이 오고 (switch case와 유사) WAY2 : CASE뒤에 아무것도 없으면 WHEN절에는 조건식이 온다 (if else와 유사) --WAY1 select job_id, salary, case job_id      when'IT_PROG' then salary*1.10     when 'FI_MGR' then salary*1.15     else salary     end as revised_salary from employees; --WAY2 select job_id, salary, case when job_id='IT_PROG' then salary*1.10     when job_id='FI_MGR' then salary*1.15     else salary     end as revised_salary from employees

 

<집합 연산자>

=2개 이상의 SQL문 결과로 집합 만드는 연산자

(단 두 테이블의 열 개수와 데이터 타입은 일치해야 한다)

UNION, UNION ALL, INTERSECT, MINUS

UNION	합집합 같은 결과 한번만 출력. 중복 없음 select employee_id,first_name, to_char(hiredate form emploayees union select employee_id, first_name, to_char(department_id) from unions;
UNION ALL	합집합. 중복 있음 select first_name from employees where hure_daet like '14%' union all sekect first_name from employees where department_id=20;
INTERSECT	교집합 출력 select first_name from employees where hire_date like '14%' intersect select first_name from employees where department_id=20;
MINUS	차집합 select first_name from employees where hire_date like '14%' minus select first_name from employees where department_id=20;