[Database/Oracle] 단일행 함수-수학함수, 문자열 함수, 조건함수, 변환함수, format

minj-j·2022년 7월 26일
0

Oracle

목록 보기
13/13
post-thumbnail

🌞 1. 수학 함수

select sin(10), cos(10), tan(10), log(10,2), power(6,4) 
from dual;

   SIN(10)    COS(10)    TAN(10)  LOG(10,2) POWER(6,4)
---------- ---------- ---------- ---------- ----------
-.54402111 -.83907153 .648360827 .301029996       1296

반올림 관련 함수

[round 반올림]

select round(12540.379, 2), round(12540.379, -2), round(12540.579, 0) 
from dual;

ROUND(12540.379,2) //소수점 둘째 자리까지 나타내기 (셋째 자리에서 반올림) 
------------------ 
          12540.38 
ROUND(12540.379,-2) //십의 지리에서 반올림 
-------------------
              12500 
ROUND(12540.579,0) //소수점 첫째 자리에서 반올림
-------------------
             12541

[trunc 내림]

[trunc 내림]
select trunc(12540.379, 2), trunc(12540.379, -2), trunc(12540.579, 0) 
from dual;

TRUNC(12540.379,2) //소수점 둘째 자리까지 나타내기 (셋째 자리에서 버림) 
------------------
          12540.37 
TRUNC(12540.379,-2) //십의 지리에서 버림
------------------
             12500 
TRUNC(12540.579,0) //소수점 첫째 자리에서 버림
------------------
             12540

[floor와 ceil]

[floor와 ceil]
select floor(10.98), ceil(10.14) from dual; // 정수값 반환

FLOOR(10.98) // floor은 10~11사이에서 11에 가장 가깝게 있더라도 
가장 최하에 있는 수를 추출한다. 
------------ 
		  10
CEIL(10.14) // ceil은 10~11사이에서 11에 가장 멀리 있더라도 
가장 최상에 있는 수를 추출한다.
-----------
          11

🌜 연습해보기

사원명, 급여, 월급(급여/12), 세금(급여의 3.3%)을 추출
단, 월급은 십단위에서 반올림하고, 세금은 원단위에서 절삭하여 추출한다.

select saname, sapay, round(sapay/12, -2) "월급", 
trunc(sapay*0.033, -1) "세금" from sawon;

SANAME          SAPAY       월급       세금
---------- ---------- ---------- ----------
홍길동           5000        400        160
한국남           3000        300         90
이순신           3500        300        110
이순라           1200        100         30
놀기만           2300        200         70
류별나           1600        100         50
채시라           3400        300        110
이성계           2803        200         90
무궁화           3000        300         90
임꺽정           2200        200         70
깨똥이           4500        400        140

🌞 2. 문자열 함수

select length('IT여성기업협회'), //길이를 구하라
lengthb('IT여성기업협회') from dual; //문자열의 길이를 바이트 단위로 출력함

LENGTH('IT여성기업협회') LENGTHB('IT여성기업협회')
------------------------ -------------------------
                       8                        14

공백제거 함수 trim, ltrim, rtrim

select trim('  kibwa  kibwa  ') a, rtrim('  kibwa  kibwa  ') a, 
ltrim('  kibwa  kibwa  ') a from dual;

TRIM('KIBWAK RTRIM('KIBWAKI LTRIM('KIBWAKI
------------ -------------- --------------
kibwa  kibwa   kibwa  kibwa kibwa  kibwa

select trim('k' from 'kibwa  kibwa') from dual;

TRIM('K'FRO
-----------
ibwa  kibwa

문자열 lower, upper, initcap 연습해보기

select 
lower('I am Kibwa'), //전체 다 소문자
upper('I am Kibwa'), //전체 다 대문자
initcap('I am Kibwa') from dual; //각 단어의 첫 글자를 대문자로 나머지는 소문자로 변환하여 반환

결과
LOWER('IAM UPPER('IAM INITCAP('I
---------- ---------- ----------
i am kibwa I AM KIBWA I Am Kibwa
select replace('i am a kibwa', 'a', 'x') from dual;	// 대체 함수

REPLACE('IAM
------------
i xm x kibwx

채우기 함수 lpad & rpad(데이터, 데이터+문자가 입력될 전체크기, '채울문자') :
전체크기만큼을 잡고 데이터가 입력된 후 왼/오른쪽에 문자를 채운다.

select lpad(saname, 20, '#') a, rpad(saname, 20, '&') a from sawon

--사원명, 급여, 급여현황(급여가 100단위에 '*' 하나)을 추출
col 급여현황 format a50
A
----------------------------------------
A
----------------------------------------
##############홍길동
홍길동&&&&&&&&&&&&&&

##############한국남
한국남&&&&&&&&&&&&&&

##############이순신
이순신&&&&&&&&&&&&&&


select saname, sapay, lpad('*', sapay/100, '*') "급여현황" from sawon;

홍길동 2000 ********************
김길동 1500 ***************

instr(문자열, 찾을문자, [1|-1], N번째) :
찾을 문자의 위치값(검색 시 앞에서 부터 또는 뒤에서 부터 선택)

select instr('kibwa_kibwa education center', 'k', 1, 2) from dual;

INSTR('KIBWA_KIBWAEDUCATIONCENTER','K', 1, 2) // K가 처음으로 발견된 후 2번째로 발견 된 위치를 출력하라
-------------------------------------------
                                          7

SQL> select instr('kibwa_kibwa education center', 'k', -1, 2) from dual;

INSTR('KIBWA_KIBWAEDUCATIONCENTER','K',-1,2)
--------------------------------------------
                                           1

substr : 문자열 추출함수(substr(문자열, N번째 부터, N개) 추출

select substr('kibwa_kibwa education center', 5, 6) from dual;

SUBSTR
------
a_kibw

고객명, 전화번호, 주민번호1(**-3182518), 주민번호2(041201-***)를 추출하라

select goname, gotel, lpad(substr(gojumin, 7, 8), 14, '*') jumin1,
rpad(substr(gojumin, 1, 8), 14, '*') jumin2 from gogek;

결과
GONAME     GOTEL                JUMIN1                       JUMIN2
---------- -------------------- ---------------------------- ----------------------------
류민       123-1234             ******-1537915               700113-1******
강민       343-1454             ******-1627914               690216-1******
영희       144-1655             ******-2636215               750320-2******
철이       673-1674             ******-1234567               770430-1******
류완       123-1674             ******-1123675               720521-1******

🌞 3. 조건함수

  • decode : decode(데이터, 조건1, 결과1, 조건2, 결과2,........, 그외 결과) "컬럼명"
select saname, deptno, 
decode(deptno,10,'영업',20,'관리',30,'총무','전산') "부서명" from sawon;

SANAME         DEPTNO 부서
---------- ---------- ----
홍길동             10 영업
한국남             20 관리
이순신             20 관리
이순라             20 관리
놀기만             20 관리
류별나             20 관리
채시라             20 관리
이성계             30 총무
무궁화             10 영업
임꺽정             20 관리
깨똥이             10 영업

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 총무
무궁화             10 영업
임꺽정             20 관리
깨똥이             10 영업

🌜 decode 사용해보기
고객명, 전화번호, 성별을 추출(decode)

select goname, gotel, gojumin, 
decode(substr(gojumin, 8, 1), 1, '남자', 3, '남자', '여자') sex from gogek;

GONAME     GOTEL                GOJUMIN        SEX
---------- -------------------- -------------- ----
류민       123-1234             700113-1537915 남자
강민       343-1454             690216-1627914 남자
영희       144-1655             750320-2636215 여자
철이       673-1674             770430-1234567 남자
류완       123-1674             720521-1123675 남자
  • case문 : case [데이터] when 조건1 then 결과1 when 조건2 then 결과2 .......
    else 그외 결과 end "컬럼명"

사원명, 급여, 보너스를 출력(case)

   단, 보너스는 급여가 1000미만  ->급여의 10%
		    1000~2000->급여의 15%
		    2000초과 ->급여의 20%
		    null        ->0

select saname, sapay, case 	when sapay<1000 then sapay*0.1
			when sapay<=2000 then sapay*0.15
			when sapay>2000 then sapay*0.2
			else 0
		    end "보너스"
from sawon;

🌞 4. 변환 함수

to_char('날짜' | '숫자', '형식') : 날짜 또는 숫자를 지정한 형식의 문자로 변환
to_date('날짜 형태의 문자열' , '형식') : 날짜 형태의 문자열을 날짜로 변환
// 시간표현(*** 많이쓰일 예정! 중요하다)
to_number('숫자 형태의 문자열') : 숫자 형태의 문자열을 숫자로 변환

🌞 5. 컬럼 출력 범위 정하기

col 출력범위를 제한할 컬럼 format a__(바이트 수)

ex) col parameter format a50
col value format a60

cf) 세션 정보 보기

select * from nls_session_parameters; // 세션 정보를 볼 수 있다.

PARAMETER                                          VALUE
-------------------------------------------------- ------------------------------------------------------------
NLS_LANGUAGE                                       KOREAN
profile
minj-j`s Development diary!

0개의 댓글