DBMS SQL Oracle Database 오라클 내장함수

 

 

오라클 내장함수

 

오라클 함수: 오라클 내부에서 기본적으로 제공해 주는 함수

select문을 이용해 함수 사용

 

 

DUAL 테이블

: 임시테이블

어떠한 결과를 출력하기 위해서 임시적으로 만들어지는 테이블

 

 

수치함수

 

합 : sum(필드명)

평균 : avg(필드명)

최대 : max(필드명)

최소 : min(필드명)

개수 : count(필드명)

 

select 함수명(필드명) from 테이블명;

select count(pw) from testmember;

select sum(money) from testmember;

 

select count(pw) as 개수 from testmember;

// 나와야 하는 칼럼명 대신 as로 별칭 부여

 

 

숫자함수

 

abs(n) : n의 절대값을 구하는 함수

select abs(-20), abs(20) from dual;

 

ceil(n) : n은 실수값 n보다 같거나 큰 정수(올림)

select ceil(12.57), ceil(1.234) from dual;

 

floor(n) : n보다 작거나 같은 정수(버림)

select floor(12.57), floor(1.234) from dual;

 

round(n,i) : n의 소수점 자리수를 지정하는 함수 반올림

n은 실수, i는 정수

select round(123.456,1), round(123.456,4) from dual;

 

trunc(n,i) : n의 소수점 자리수를 지정하는 함수 버림

n은 실수, i는 정수

select trunc(123.456,1), trunc(123.456,4) from dual;

 

power(n1, n2) : 거듭제곱 n1의 n2승

select power(2,3) from dual;

 

mod(n1, n2) : n1을 n2로 나눈 나머지

select mod(10,3) from dual;

 

sign(n) : n이 양수인지 음수인지 판별

반환값이 양수이면 1, 음수이면 -1, 0이면 0을 반환

select sign(12), sign(-12), sign(0) from dual;

 

 

문자함수

: 문자열을 관리하는 함수

 

initcap(char) : 문자열의 첫글자는 대문자, 나머지는 소문자로 변환시켜주는 함수

select no, name, initcap(id) from testmember;

 

lower(char)

select no, name, lower(id) from testmember;

 

upper(char)

select no, name, upper(id) from testmember;

 

select initcap(id), lower(id), upper(id) from testmember;

 

concat(char1, char2) : || : car 1과 char2를 붙여 주는 함수

select concat('abc', 'def') from dual;

select concat(name, id) from testmember;

select concat(concat(name, '님의 아이디 : '), id) from testmember;

 

substr(char, pos, len) : char문자열에서 pos번째 문자부터 len 길이만큼 잘라서 반환하는 함수

select substr('abcde', 2, 3) from dual;

2번째(sql의 데이터는 1부터 시작) 문자열부터 3개(문자단위)

select substr('가나다라마바', 2, 3) from dual;

 

select substrb(char, pos, len) : char문자열에서 pos번째 바이트부터  len길이만큼의 byte를 잘라서 반환하는 함수

select substrb('abcde', 2, 3) from dual;

select substrb('가나다라마바', 2, 3) from dual;

// 한글은 3byte이기 때문에 pos값이 2바이트인 경우 값이 나오지 않음

 

length(char) : 문자열의 길이를 구하는 함수

select length('abcdef') from dual;

 

lengthb(char) : 문자열의 바이트 길이를 구하는 함수

select lengthb('abcdef') from dual;

 

trim : 공백제거

ltrim : 왼쪽 공백제거

rtrim : 오른쪽 공백제거

select trip ('           abcdef       ') from dual;

select length('               abcdef              ') from dual;

select length(trim('               abcdef              ')) from dual;

select length(ltrim('               abcdef              ')) from dual;

select length(rtrim('               abcdef              ')) from dual;

 

ltrim(char1, char2) : char1의 좌측부터 char2를 찾아서 삭제 후 반환하는 함수

char2를 생략해도 되지만 default값은 '     '가 지정되어 있다

select ltrim('abcdef', 'cd') from dual;

rtrim(char1, char2) : char1의 우측부터 char2를 찾아서 삭제 후 반환하는 함수

char2를 생략해도 되지만 default값은 '     '가 지정되어 있다

 

trim : 공백제거 ; 키워드를 통해 왼쪽 오른쪽 양쪽을 지정하여 공백을 제거할 수도 있다

leading : 왼쪽

trading : 오른쪽

both : 양쪽 디폴트값

select length('          abc         '), length(trim('          abc         ')), ltrim('          abc         ', 'cd') from dual;
select length('          abc         '), length(trim(leading from '          abc         ')) from dual;
select length('          abc         '), length(trim(trailing from '          abc         ')) from dual;

 

lpad(char1, n, char2) : char1의 왼쪽부터 char2를 채운다 ; n은 문자열 자리수

rpad(char1, n, char2) : char1의 오른쪽부터 char2를 채운다 ; n은 문자열 자리수

select lpad('abc', 7, '$') from dual;
select rpad('abc', 7, '$') from dual;

 

ascii(char) : 문자의 아스키코드값을 반환하는 함수

select ascii('A') from dual;

select ascii('가') from dual;

// 아스키코드 외의 문자를 사용할 경우 DB에서 사용하는 문제 체계(DBCDIC)의 값을 반환한다

select ascii('abcdef') from dual;

// 문자열을 넣었을 때는 첫글자의 아스키코드값을 반환한다

 

decode(input1, serach1, result1, result2) : if~else

: input1의 문자열이 search1과 비교하여 같으면 result1, 다르면 result2를 반환한다

select decode('abcde', 'abcde', 'equal', 'wrong') from dual;
select decode('abcde', 'abcdee', 'equal', 'wrong') from dual;

select decode('abcde', 'abcdee', 'equal', 'abcdee', '같다', '같은 게 없다') from dual;

 

 

날짜함수

 

sysdate : 현재 시간을 반환하는 함수(시스템 기준)

select sysdate from dual;

 

current_date : 현재 시간 (접속한 지역 기준)

select current_date from dual;

 

add_months(date, n) : date에 n수만큼 월을 더하거나 빼는 함수

select add_months(sysdate, 2) from dual;

 

months_between(date1, date2) : 두 날짜 사이의 개월수를 반환하는 함수

select months_between(sysdate, '2007-11-14') from dual;

 

last_day(date) : date기준의 월말일을 반환하는 함수

select last_day(sysdate) from dual;

select last_day('2023-02-01') from dual;

select last_day('2022-02-01') from dual;

select last_day('2021-02-01') from dual;

select last_day('2020-02-01') from dual;

select last_day('2019-02-01') from dual;

select last_day('2018-02-01') from dual;

 

ex.

select * from testmember where joindate between '2015-01-01' and last_day('2015-07-01');

next_day(date, char) : date기준으로 char에 명시한 요일의 날짜를 반환하는 함수

요일에 대한 약어를 사용한다

select next_day (sysdate, 1) from dual;

// 1은 일요일부터 ; 자바와 달리 db의 인덱스는 1부터이다

select next_day (sysdate, '월') from dual;

select next_day (sysdate, '월요일') from dual;

// 한국 컴퓨터에서는 영어가 불가하며, 외국 컴퓨터이서는 영어만 가능

날짜 포맷 변환표
형식	RDBMS	변환 문법
'YYYY.MM.DD'	Oracle	TO_CHAR(date_exp, 'YYYY.MM.DD')
	MSSQL	CONVERT(VARCHAR, date_exp, 102)
	DB2	REPLACE(CHAR(DATE(date_exp),ISO), '-', '.')
'HH:MI:SS'	Oracle	TO_CHAR(date_exp, 'HH:MI:SS')
	MSSQL	CONVERT(VARCHAR, date_exp, 108)
	DB2	CHAR(TIME(date_exp) , JIS )
'YYYY/MM/DD'	Oracle	TO_CHAR(date_exp, 'YYYY/MM/DD')
	MSSQL	CONVERT(VARCHAR, date_exp, 111)
	DB2	REPLACE(CHAR(DATE(date_exp), ISO), '-', '/')
'YYYYMMDD'	Oracle	TO_CHAR(date_exp, 'YYYYMMDD')
	MSSQL	CONVERT(VARCHAR, date_exp, 112)
	DB2	CHAR(DATE(date_exp))
'HH24:MI:SS'	Oracle	TO_CHAR(date_exp, 'HH24:MI:SS')
	MSSQL	CONVERT(VARCHAR(8), date_exp, 114)
	DB2	CHAR(TIME(date_exp))
'YYYY.MM.DD HH24:MI'	Oracle	TO_CHAR(date_exp, 'YYYY.MM.DD HH24:MI')
	MSSQL	CONVERT(VARCHAR, date_exp, 102) + ' ' + CONVERT(VARCHAR(5), date_exp, 114)
	DB2	REPLACE(CHAR(DATE(date_exp), ISO), '-', '.') || CAST( TIME(date_exp) AS CHAR(5))
'YYYY/MM/DD HH24:MI:SS'	Oracle	TO_CHAR(date_exp, 'YYYY/MM/DD HH24:MI:SS')
	MSSQL	CONVERT(VARCHAR, date_exp, 111) + ' ' + CONVERT(VARCHAR(8), date_exp, 114)
	DB2	REPLACE(CHAR(DATE(date_exp), ISO), '-', '/') || CAST( TIME(date_exp))

 

 

변환함수

 

to_date (date, format) : 문자형식에 맞는 날짜로 변환하는 함수

주로 데이터의 형식을 날짜로 표시(date형식)로 변환할 때 사용한다

select to_date(sysdate, 'YYYY/MM/DD HH24:MI:SS') from dual;
select to_char(sysdate, 'YYYY/MM/DD HH24:MI:SS') from dual;

select to_char('123456', '$999,999') from dual;

// 숫자 포맷 ; 9하나당 숫자(자릿수) 하나, 자릿수만큼마다 , 사용

 

to_number(data) : 숫자로 변환하는 함수

문자형식의 데이터를 정수처리해야 할 때 사용

select to_number('123456') from dual;

select to_number('1234a56') from dual; // 오류 ; 사용불가

 

 

NULL 함수

 

nvl(input1, input2) : input1컬럼이 null이면 input2를 반환하는 함수

select nvl(pw, '설정안됨') from testmember;

 

distinct : 중복제거

select distinct nvl(pw, '설정안됨') from testmember;

 

nvl2(imput1, input2, input3) : input1컬럼이 NULL이 아니면 input2를 반환하고 NULL이면 input3을 반환하는 함수

select nvl2(pw, 'secret', 'non') from testmember;

 

 

GROUP BY절

: 데이터들을 원하는 그룹으로 나눌 때 사용

 

select [GROUP BY 절에 지정된 컬럼] [GROUP BY별로 집계할 값] from 테이블명 group by [그룹으로 묶을 컬럼];

select department_id, salary from employees;

select depart department_id, avg(salary) from employees group by department_id;

 

 

HAVING절

: GROUP BY절의 조건을 적는 부분

그룹별로 나눈 수치에 대한 조건을 적어야 한다

select department_id, avg(salay) from employees where salary >=5000 group by department_id having avg(salary) >= 1000 ;

조건문 사용 순서 : SELECT > FROM > WHERE > GROUP BY > HAVING > ORDER BY

 

ex.

testmember 마일리지가 가장 많은 사람의 이름을 출력

select name from testmember where money = (select max(money) from testmember);