05. SQL syntax(3) - Function
- Function
+ Single Row Function : 1개의 Record에서 사용하는 단일 행 함수.
-> NULL Value : NULL값을 0으로 출력 할 때. 해당 COLUM의 DATATYPE을 준수한다.
1 2 3 4 5 6 7 | SELECT NVL(salary,0) * 12 [AS] "연봉 (상여금 제외)" FROM table; /* OR */ SELECT NVL(salary*12,0) [AS] "연봉 (상여금 제외)" FROM table; | cs |
-> NULL Value2 : 해당 COLUM의 DATATYPE에 관계 없이 필요에 따른 결과를 출력한다.
1 | SELECT NVL2(salary, '있음', '없음') * 12 [AS] "연봉 (상여금 제외)" FROM table; | cs |
-> DECODE : 조건문.
1 2 3 4 5 6 7 8 9 10 11 12 | /* DECODE(해당COLUM, 조건, 참일때 값, 거짓일때 값) OR DECODE(해당COLUM, 조건, 참일때 값, [다른 조건, 참일때 값,] 거짓일때 값) */ SELECT DECODE(title, NULL, '직급없음', title) 직급 from table; SELECT DECODE(title, NULL, '직급없음', '사장', '난사장임', title) 직급 from table; | cs |
-> ROUND(VALUE, 반올림자리수) : 반올림. 2번째 Argument의 Default는 0. 대입하지 않아도 가능.
-> TRUNC (VALUE, 버림자리수) : 버림. 2번째 Argument의 Default는 0. 대입하지 않아도 가능.
-> MOD(VALUE, VALUE) : 나머지
-> ABS(VALUE) : 절대값
-> CONCAT(VALUE, VALUE) : append
-> SUBSTR(문자, 시작위치, 잘라낼 개수) : index는 1부터
-> REPLACE(문자, 치환할 대상, 치환할 문자) : 치환
-> TRANSLATE(문자, 치환할 대상, 치환할 문자) : 글자 단위로 치환
-> MONTHS_BETWEEN(끝날짜, 시작날짜) : 개월수 계산
-> TO_CHAR(value format) : 문자변환
TO_DATE(value format) : 날자변환
TO_NUMBER(value) : 숫자변환
-> 날짜 문자 형식
2077-07-07
'YEAR' : 글자형태 출력(twenty seventy-seven)
'YYYY' : 숫자형태 출력(2077)
'YY' : 숫자형태 출력(77)
'MONTH' : 글자형태 출력(7월)
'MM' : 숫자형태 출력(2077)
'DAY' : 요일형태 출력(수요일)
'DY' : 요일형태 출력(수)
'D' : 요일의 Index 출력(4)
'DD' : 일 출력(07)
-> 조건문을 활용한 예제
WHERE TO_CHAR(in_date,'mm')='07'
WHERE TO_CHAR(in_date, 'D')=2;
-> Locale 정보 이용(국가에 따른 표시 단위)
TO_CHAR(NVL(salary,0)*12, 'L999,999.99')
=> ₩119,904.00
-> LPAD('Oracle DBMS', 13, 'x') : Oracle DBMS의 맨 뒷문자에서 출발.
13번째까지 문자 출력.
공백이 있으면 x로 출력.
-> RPAD('Oracle DBMS', 13, 'x') : Oracle DBMS의 맨 앞문자에서 출발.
13번째까지 문자 출력.
공백이 있으면 x로 출력.
-> SIN(VALUE) : sin 삼각함수
-> SIGN(VALUE) : 양수, 음수 판단.
-> EXP(VALUE) : Exponential
-> LOW(VALUE) : 소문자 변환
-> UPPER(VALUE) : 대문자 변환
-> INITCAP(VALUE) : 공백 분리
+ Multi Row Function(Group) : 1개의 Column에서 사용하는 다중행 함수
WHERE에 작성할 수 없다.
-> COUNT(s/n) : 전체 레코드의 수
Ex :
1 2 3 4 5 6 | select count(*) 레코드, count(salary) 월급수령자, =>NULL은 계산에서 제외 count(nvl(salary,0)) "월급수령자 (null포함)", count(distinct salary) 월급종류, count(distinct nvl(salary,0)) "월급종류 (null포함)" from emp_test; | cs |
1 2 3 4 5 | select count(*) 영업부직원, count(dept_name) 영업부직원2, count(salary) 영업부직원월급수령자 from emp_test where dept_name='영업부'; |
-> ROWNUM : 행의 순번을 나타내는 가상 컬럼
-> SUM, MAX, MIN, AVG 모두 Multi Row Function다.
Multi Row Function은 모든 연산에서 NULL을 제외한다.
Ex :
1 2 3 4 5 6 7 | select sum(salary), max(salary), min(salary), avg(salary), avg(nvl(salary,0)) from emp_test where dept_name='영업부'; | cs |
-> 출력시 주의할 점.
출력할 값들은 1:1의 관계를 유지해야 된다.
단일의 값은 Field를 말하며 한개의 자료를 가지고 있다.
다중의 값은 Column을 말하며, 여러개의 자료를 하나의 묶음으로 가지고 있다.
단일의 값과 다중의 값을 동시 출력하게 되면 1:N의 관계를 가지므로 같이 출력 할 수 없다.
1 2 3 | select name, max(salary) or dept_name, max(salary) from emp_test where dept_name='영업부'; |
2행에 오류:
ORA-00937: 단일 그룹의 그룹 함수가 아닙니다
단일의 값을 출력하기 위한 해결책은 GROUP BY다.
GROUP BY
특정한 Column의 자료들을 다른 자료들과 비교함으로서, 유일한 값에 따라 단체로 묶는 것이다.
1 2 3 4 | select dept_name, max(salary) from emp_test where dept_name='영업부' group by dept_name; |
1 2 3 | select nvl(dept_name,'부서배치전'), nvl(avg(salary),0) from emp_test group by dept_name; | cs |
HAVING
Multi Row Function을 이용하여 조건을 부여할 때 사용한다.
1 2 3 4 5 | select dept_name, avg(salary), sum(salary), count(dept_name) from emp_test where dept_name is not null group by dept_name having count(dept_name)>=5 and avg(salary)>=5000; |
'Database > Theory' 카테고리의 다른 글
[Database/Theory]07. SQL syntax(5) -Join (0) | 2016.06.07 |
---|---|
[Database/Theory]06. SQL syntax(4) - Constraint, rest (0) | 2016.06.07 |
[Database/Theory]04. SQL syntax(2) - Query (0) | 2016.06.06 |
[Database/Theory]03. SQL syntax(1) - DDL, DML (0) | 2016.06.06 |
[Database/Theory]02. Notation rule, Data type (0) | 2016.06.06 |