[SQL]function 기초

function 기초

arguments 를 fx로 input해서 output을 던져준다.

단일 행 함수는 하나의 row의 값을 input해서 output 하는 것을 말한다.

다중 행 함수는 여러개의 row값음을input해서 output 하는 것을 말한다.

타입별로 사용할 수 있는 함수, 여러 타입에 범용적으로 사용할 수 있는 함수

otn.oracle.com 에서 software download에 들어가면 원하는 소프트웨어를 다운 받을 수 있다.(상업적 이용 불가.)

또한 documentation에서 메뉴얼을 참고할 수 있다.

문자함수

대소문자 변환 함수

함수	결과
LOWER('TEST Example')	test example
UPPER('TEST Example')	TEST EXAMPLE
INITCAP('TEST Example')	Test Example

다음 예시를 참고하자

select last_name, lower(last_name), upper(last_name), initcap(last_name)
from employees;

select employee_id, last_name, department_id
from employees
where last_name = 'higgins';

select upper('test')
from dual;

dual Table은 row가 하나만 있는 테이블이다.

문자 조작 함수

함수	결과
CONCAT('Hello', 'World')	HelloWorld 두개의 문자열을 합쳐준다
SUBSTR('HelloWorld',1,5)	Hello 1번째 자리부터 5번째 자리까지 출력
LENGTH('HelloWorld') LENGTHB('삼이승')	10(문자열의 길이를 확인) 9(byte수를 확인)
INSTR('HelloWorld', 'W') INSTR('HelloWorld', 'o', 2)	6 두번째 o가 나오는 위치
LPAD(salary,10,'*')	*****24000
RPAD(salary, 10, '*')	24000*****
REPLACE ('JACK and JUE','J','BL')	BLACK and BLUE
TRIM('H' FROM 'HelloWorld') LTRIM(왼쪽에서 시작) RTRIM(오른쪽에서 시작)	elloWorld (trim 함수는 가장 끝에있는 문자만 제거 가능하다.)

숫자 함수

함수	결과
ROUND(45.926, 2) 반올림	45.93  (소수 두번째 자리까지(10^-2) 반올림)
TRUNC(45.926, 2) 짜르기	45.92 (소수 두번째 자리까지(10^-2) 짜름)
MOD(1600, 300)	100 나머지

날짜함수

RR 날짜 형식과 YY 날짜 형식이 있다.

YY는 현재 년도의 앞 두자리 기준이므로 이해하기 쉽지만

RR의 경우 현재연도 뒤의 두자리와 지정된 연도의 뒤의 두자리에 따라 해석되는 날짜의 앞 두자리의 연도가 변경된다.

RR일때 현재 연도의 뒷 두자리가 0-49일경우 지정된 연도의 뒷 두자리가 0-49이면 반환 날짜는 현재세기의 날짜이고, 지정된 연도의 뒷 두자리가 50-99이면 반환 날짜는 이전 세기의 날짜이다.

현재 연도의 뒷 두자리가 50-99이면 지정된 연도의 뒷 두자리가 0-49이면 반환 날짜는 이후세기의 날짜이고, 지정된 연도의 뒷 두자리가 50-99이면 반환 날짜는 현재 세기의 날짜이다.

다음 표를 참고해 이해하도록 하자.

현재 연도	지정된 날짜	해석(RR)	해석(YY)
1994	27-OCT-95	1995	1995
1994	27-OCT-17	2017	1917
2001	27-OCT-17	2017	2017
2048	27-OCT-52	1952	2052
2051	27-OCT-47	2147	2047

SYSDATE 함수는 format을 따라서 현재 날짜와 시간을 표시해준다.

시간은 oracle DB가 설치된 서버의 시간을 가져온다.

날짜연산

작업	결과	설명
날짜 + 숫자	날짜	날짜 + 일수
날짜 - 숫자	날짜	날짜 - 일수
날짜 - 날짜	일 수	날짜 - 날짜
날짜 + 숫자/24	날짜	날짜 + 시간수
날짜 + 날짜	ERROR	ERROR

다음 예시를 참고하자.

select sysdate, sysdate +1, sysdate + 1/24
from dual;

각각 현재 날짜와 시간, 1일을 더한 날짜와 시간, 1시간을 더한 날짜와 시간이 표시된다.

select last_name, hire_date, round(sysdate-hire_date)
from employees;

다음은 last_name을 표현하고 hire_date와 hire_date에서 현재날짜를 뺀 값을 정수 반올림을 해준다.

날짜 조작 함수

함수	결과
MONTHS_BETWEEN	두 날짜 간의 개월수
ADD_MONTHS	날짜에 개월 추가
NEXT_DAT	지정된 날짜의 다음 날
LAST_DAT	월의 마지막 날
ROUND	날짜 반올림
TRUNC	날짜 truncate

함수	결과
ROUND(SYSDATE,'MONTH')	01-AUG-03
ROUND(SYSDATE,'YEAR')	01-JAN-04
TRUNC(SYSDATE,'MONTH')	01-JUL-03
TRUNC(SYSDATE,'YEAR')	01-JAN-03

다음 예시를 참고하자.

SELECT SYSDATE
      ,TRUNC(SYSDATE,'DAY')  -- 해당 일이 포함된 주의 일요일 00:00:00을 나타낸다
      ,TRUNC(SYSDATE,'DY')   -- 위와 동일
      ,TRUNC(SYSDATE,'D')    -- 위와 동일
      ,TRUNC(SYSDATE,'YYYY') -- 해당 일이 포함된 연도의 1월1일의 00:00:00을 나타낸다
      ,TRUNC(SYSDATE,'Q')    -- 위와 동일
      ,TRUNC(SYSDATE,'MM')   -- 해당 일이 포함된 달의 1일 00:00:00을 나타낸다
      ,TRUNC(SYSDATE,'DD')   -- 해당 일이 포함된 일의 00:00:00을 나타낸다
      ,TRUNC(SYSDATE,'HH')   -- 해당 일이 포함된 시간의 HH:00:00을 나타낸다
      ,TRUNC(SYSDATE,'MI')   -- 해당 일이 포함된 시간의 HH:MI:00을 나타낸다.
FROM DUAL ; 

SQL을 배운지 얼마 되지 않아 잘못된 내용이 있을 수 있습니다. 틀린 내용이있다면, 댓글로 달아주세요.