*** 단일행 함수 : 하나의 행 단위 실행
1. 수학함수
SQL> select sin(10), cos(10), tan(10), log(10, 2), power(6, 5) from dual;
SIN(10) COS(10) TAN(10) LOG(10,2)
---------- ---------- ---------- ----------
-.54402111 -.83907153 .648360827 .301029996 7776
--반올림 관련 함수***
SQL> select round(246.5412, 2 소수점 2째자리), round(246.5412, -2 정수 2째자리), round(246.5412, 0 정수 0째자리) from dual; // 자리수 지정한 반올림
ROUND(246.5412,2) ROUND(246.5412,-2) ROUND(246.5412,0)
----------------- ------------------ -----------------
246.54 200 247
SQL> select trunc(246.5412, 2), trunc(246.5412, -2), trunc(246.5412, 0) from dual; // 자리수 지정한 버림
TRUNC(246.5412,2) TRUNC(246.5412,-2) TRUNC(246.5412,0)
----------------- ------------------ -----------------
246.54 200 246
SQL> select ceil(10.1 1째자리 ), floor(10.9 ) from dual;
CEIL(10.1) FLOOR(10.9)
---------- -----------
11 10
-- 사원명, 급여, 월급여(급여/12), 세금(급여의 3.3%)를 추출
-- 단, 월급은 십단위에서 반올림하고, 세금은 일단위에서 절삭
select saname, sapay, round(sapay/12, -2) "월급", trunc(sapay*0.033, -1) "세금" from sawon;
2. 문자열 함수
SQL> select length('한국직업전문학교'), lengthb('한국직업전문학교') from dual;
LENGTH('한국직업전문학교') LENGTHB('한국직업전문학교')
-------------------------- ---------------------------
8 16 --
-- 공백제거 관련 함수
SQL> select trim(' korea VC '), ltrim(' korea VC '), rtrim(' korea VC ') from dual;
TRIM('KOR LTRIM('KOREA RTRIM('KORE
--------- ------------ -----------
korea VC korea VC korea VC
SQL> select trim('k' from 'koreaVC') from dual;
TRIM('
------
oreaVC
SQL> select lower('I am a koreavc'), upper('I am a koreavc'), initcap('I am a koreavc') from dual;
LOWER('IAMAKOR UPPER('IAMAKOR INITCAP('IAMAK
-------------- -------------- --------------
i am a koreavc I AM A KOREAVC I Am A Koreavc
SQL> select replace('koreavc koreavc','r' ,'x') from dual; --
REPLACE('KOREAV
---------------
koxeavc koxeavc
-- 채우기 함수*** lpad & rpad(데이터, 전체크기, '채울문자)
SQL> col name format a20 // 출력되는 컬럼의 크기 설정 : col 컬럼명(별칭) format aXX(byte)
SQL> select lpad(saname, '20', '@') name, rpad(saname, 20, '^') name from sawon
NAME NAME
-------------------- --------------------
@@@@@@@@@@@@@@홍길동 홍길동^^^^^^^^^^^^^^
@@@@@@@@@@@@@@한국남 한국남^^^^^^^^^^^^^^
@@@@@@@@@@@@@@이순신 이순신^^^^^^^^^^^^^^
@@@@@@@@@@@@@@이순라 이순라^^^^^^^^^^^^^^
@@@@@@@@@@@@@@놀기만 놀기만^^^^^^^^^^^^^^
@@@@@@@@@@@@@@류별나 류별나^^^^^^^^^^^^^^
@@@@@@@@@@@@@@채시라 채시라^^^^^^^^^^^^^^
-- 사원명, 급여, 급여현황(급여 100단위에 * 하나)
박길동 1500 ***************
이길동 1000 **********
col 급여현황 format a50
select saname, sapay, lpad('*', sapay/100, '*') 급여현황 from sawon;
-- 문자열 추출 함수*** substr(문자열, N번째, N개)
SQL> select substr('koreavc koreavc', 3, 4) from dual;
SUBS
----
reav
-- 고객명, 주민번호1(******-3182565), 주민번호2(041201-*******)를 추출
col 주민번호1 format a14
col 주민번호2 format a14
select goname, lpad(substr(gojumin, 7, 8), 14, '*') 주민번호1, rpad(substr(gojumin, 1, 7), 14, '*') 주민번호2 from gogek;
-- 검색 문자의 위치값 instr(문자열, '찾을문자', 1|-1(뒤), N번째)
SQL> select instr('koreavc koreavc', 'k', 1, 2) from dual;
INSTR('KOREAVCKOREAVC','K',1,2)
-------------------------------
9
SQL> select instr('koreavc koreavc', 'k', -1, 2) from dual;
INSTR('KOREAVCKOREAVC','K',-1,2)
--------------------------------
1
3. 조건함수
- decode함수 : decode(데이터, 조건1, 결과1, 조건2, 결과2,........, 그외 결과) "컬럼명"
- case문 : case [데이터] when 조건1 then 결과1 when 조건2 then 결과2 ....... else 그외 결과 end "컬럼명"
select saname, deptno, decode(deptno, 10,'총무',20,'관리',30,'영업','전산') "부서명" from sawon;
select saname, deptno,
case deptno when 10 then '총무' when 20 then '영업' when 30 then '전산' else '관리' end "부서명" from sawon;
SANAME DEPTNO 부서
---------- ---------- ----
홍길동 10 총무
한국남 20 관리
이순신 20 관리
이순라 20 관리
놀기만 20 관리
류별나 20 관리
채시라 20 관리
이성계 30 영업
--고객명, 전화번호, 성별을 추출
(decode)
select goname, gotel, gojumin, decode(substr(gojumin, 8, 1), 1, '남자', 3, '남자', '여자') sex from gogek;
(case)
select goname, gotel,
case substr(gojumin, 8, 1) when '1' then '남자' when '3' then '남자' else '여자' end 성별
from gogek;
4. 변환 함수
--to_char('날짜' | '숫자', '형식') : 날짜 또는 숫자를 지정한 형식의 문자*로 변환
--to_date('날짜 형태의 문자열' , '형식') : 날짜 형태의 문자열을 날짜로 변환 // 시간표현***
--to_number('숫자 형태의 문자열') : 숫자 형태의 문자열을 숫자로 변환
--현재 세션의 날짜관련 형식 정보
SQL> col parameter format a50
SQL> col value format a50
SQL> select * from nls_session_parameters;
PARAMETER VALUE
-------------------------------------------------- ---------------------------------------------
NLS_LANGUAGE KOREAN --***
NLS_TERRITORY KOREA
NLS_CURRENCY ￦ --***
NLS_ISO_CURRENCY KOREA
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT RR/MM/DD --***
NLS_DATE_LANGUAGE KOREAN --***
NLS_SORT BINARY
NLS_TIME_FORMAT HH24:MI:SSXFF
NLS_TIMESTAMP_FORMAT RR/MM/DD HH24:MI:SSXFF
PARAMETER VALUE
-------------------------------------------------- ---------------------------------------------
NLS_TIME_TZ_FORMAT HH24:MI:SSXFF TZR
NLS_TIMESTAMP_TZ_FORMAT RR/MM/DD HH24:MI:SSXFF TZR
NLS_DUAL_CURRENCY ￦
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
17 개의 행이 선택되었습니다.
--세션의 형식 변경
SQL> alter session set nls_date_format='yyyy.mm.dd hh24:mi:ss'; // 변수=값
세션이 변경되었습니다.
SQL> select sysdate from dual;
SYSDATE
-------------------
2023.10.31 09:36:03
--날짜 관련 형식 종류들
SQL> select sysdate, to_char(sysdate, 'year') from dual;
SYSDATE TO_CHAR(SYSDATE,'YEAR')
-------- ------------------------------------------
23/10/31 twenty twenty-three
'yy'
'month'
'mon'
'mm'
'q' -- 4(분기)
'd' -- 3(요일, 1-일요일) : 요일순서대로 정렬하기 위함
'day'
'dy'
'dd'
'ddd' -- 304(365 기준)
'hh'
'hh24'
'miss'
'am' | 'pm'
SQL> select sysdate, to_char(sysdate, 'yyyy.mm.dd day am hh:mi:ss') from dual;
SYSDATE TO_CHAR(SYSDATE,'YYYY.MM.DDDAYAMHH
-------- ----------------------------------
23/10/31 2023.10.31 화요일 오전 10:13:57
SQL> select saname, to_char(sahire,'fmyyyy.mmsp.ddth day am hh:mi:ss') hiredate from sawon; // fm:(0제거), sp(영문추출), th
SANAME HIREDATE
---------- ----------------------------------------
홍길동 1980.one.1st 화요일 오전 12:0:0
한국남 1988.eleven.1st 화요일 오전 12:0:0
이순신 1985.three.1st 금요일 오전 12:0:0
-- 오늘 날짜를 [2023년 10월 31일 화요일] 형태로 추출
SQL> select sysdate, to_char(sysdate, 'fmyyyy"년" mm"월" dd"일" day') from dual;
-- 숫자관련 형식(9-값이 없다면 출력하지 않음, 0-값이 없어도 출력)
SQL> select saname, to_char(sapay, 'fm$9,990') sapay from sawon;
SANAME SAPAY
---------- -------
홍길동 $5,000
한국남 $3,000
이순신 $3,500
이순라 $1,200
놀기만 $2,300
류별나 $1,600
채시라 $3,400
이성계 $2,803
무궁화 $3,000
5. 날짜 함수
SQL> select last_day(sysdate), next_day(sysdate, '월'), add_months(sysdate, 3) from dual;
LAST_DAY NEXT_DAY ADD_MONT
-------- -------- --------
23/10/31 23/11/06 24/01/31
-- 두 날짜 사이의 개월수를 반환하는 함수*** months_between(A, B)
SQL> select months_between(sysdate, '04/12/01') from dual;
MONTHS_BETWEEN(SYSDATE,'04/12/01')
----------------------------------
226.982077
-- 사원명, 입사일, 근무기간(XX년 XX개월)을 추출
col 근무기간 format a20
select saname, sahire,
floor(months_between(sysdate, sahire)/12) || '년 ' ||
floor(mod(months_between(sysdate, sahire), 12)) || '개월' "근무기간"
from sawon;
-- 날짜와 연산되는 함수
SQL> select saname, sahire, sahire + to_yminterval('01-06') from sawon; // '년-개월'
SQL> select saname, sahire, sahire + to_dsinterval('100 00:00:00') from sawon; // '일수 시:분:초'
-- 날짜에 사용되는 round, trunc 함수 : 기준형식에서 하위 개념의 형식에서 반올림 또는 버림
SQL> select sysdate, round(sysdate, 'year') from dual;
SYSDATE ROUND(SY
-------- --------
23/10/31 24/01/01
SQL> select sysdate, round(sysdate, 'month') from dual;
SYSDATE ROUND(SY
-------- --------
23/10/31 23/11/01
SQL> select sysdate, trunc(sysdate, 'year') from dual;
SYSDATE TRUNC(SY
-------- --------
23/10/31 23/01/01
SQL> select sysdate, trunc(sysdate, 'month') from dual;
SYSDATE TRUNC(SY
-------- --------
23/10/31 23/10/01
6. 통계 함수(rank 함수)
-- 형식 : rank() over([partition by 컬럼명] order by 컬럼명 (asc)|desc) "석차"
-- 입사일 순서대로 석차를 추출
select saname, sahire, rank() over(order by sahire) "근무연차 랭킹" from sawon;
--where sahire is not null
--부서별로 급여를 많이 받는 순서대로 석차를 추출(단, 급여가 널인 사람을 제외)
select deptno, saname, sapay, rank() over(partition by deptno order by sapay desc) "급여석차" from sawon
where sapay is not null;
