function.sql
함수
-
단일행 함수 : 여러건의 데이터를 한번에 하나씩만 처리하는 함수입니다
여러행 함수 : 여러건의 데이터를 입력받아서 결과값 한건을 만들어주는 함수입니다
DUAL 테이블
-- > 한행으로 결과를 출려가기 위한 테이블 입니다.
SELECT 23*25
FROM DUAL; 결과 :
DUAL 테이블은 DUMMY 라는 하나의 컬럼을 구성되어 있습니다
DESC DUAL;출력시 :
DUAL 테이블은 DUMMY 라는 하나의 컬럼에 X 라는 하나의 로우만을 저장하고 있지만 의미없느 값 입니다
SELECT * FROM DUAL;출력 :
ABS
절대값을 구하는함수입니다.
SELECT -10, ABS(-10)
FROM DUAL;출력 :
FLOOR
소수점 아래를 버리는 함수입니다
SELECT 12.1234, FLOOR(12.1234)
FROM DUAL;출력 :
ROUND
반올림하는 함수입니다.
SELECT 34.5678, ROUND(34.5678)
FROM DUAL;출력 : 
TRUNC
지정한 자릴수 이하는 버린 결과입니다
SELECT TRUNC(34.5678, 2), TRUNC(34.5678)
FROM DUAL;출력 : 
MOD
나눗셈 연산을 한 후에 나온 나머지를 구하는 함수입니다
SELECT MOD(10, 3), MOD(10, 4)
FROM DUAL;출력 :
문자 함수
UPPER
대문자로 변환하는 함수입니다
SELECT 'Hello Oracle', UPPER('Hello Oracle')
FROM DUAL;출력 :
INITCAP
문자열의 이니셜만 대문자로 변경합니다
SELECT 'hello oracle', INITCAP('hello oracle')
FROM DUAL;출력 : 
LENGTH
문자열의 길이를 byte 단위로 처리합니다
SELECT LENGTH('ORACLE'), LENGTH('오라클')
FROM DUAL;
출력 :
LENGTHB
문자열의 길이를 byte 단위로 처리합니다
SELECT LENGTHB('ORACLE'), LENGTHB('오라클')
FROM DUAL;
출력 :
SUBSTR
문자열의 시작 위치부터 선택한 갯수만큼의 문자를 추출합니다
SUBSTR( 대상, 시작위치, 추출한 갯수)
오라클에서 index 는 1부터 시작합니다.
SELECT SUBSTR('oracle string test',8, 6)
FROM DUAL;출력 :
12월에 입사한 사원을 출력
SELECT *
FROM emp
WHERE SUBSTR(hiredate, 4, 2)='12';출력 :
INSTR
특정 문자가 있는 위치를 알려줍니다
INSTR( 대상, 검색 글자, 시작위치, 몇번째 검색 )
SELECT INSTR('step by step', 't')
FROM DUAL;
SELECT INSTR('step by step', 't', 3)
FROM DUAL;
SELECT INSTR('step by step', 'e', 2, 2)
FROM DUAL;
SELECT INSTR('데이터베이스', '이', 3)
FROM DUAL;
LPAD
대상 문자열을 명시된 자릿수에서 오른쪽에 표시하고, 남은 왼쪽 자리들은 기호로 채웁니다.
SELECT LPAD('padding', 10, '#')
FROM DUAL;출력 :
LTRIM
문자열 왼쪽 (앞)의 공백 문자를 제거합니다.
SELECT LTRIM(' TRIM TEST ')
FROM DUAL;
SELECT RTRIM(' TRIM TEST ')
FROM DUAL;
SELECT TRIM(' TRIM TEST ')
FROM DUAL;날짜 함수
SYSDATE
시스템의 현재 날짜를 반환하는 함수입니다
SELECT SYSDATE
FROM DUAL;출력 :
날짜 + 숫자 : 해당 날짜부터 그 기간만큼 지난 날짜를 계산
날짜 - 숫자 : 해당 날짜부터 그 기가만큼 이전 날짜를 계산
날짜 - 날짜 : 두 날짜 사이의 기간을 계산
SELECT SYSDATE - 1 어제, SYSDATE 오늘, SYSDATE +2 모레
FROM DUAL; 출력 :
ROUND 에 포맷 모델 날짜를 사용해서, 날짜를 반올림 할 수 있습니다.
포맷 모델 단위
DDD 일단위 기준
HH 시간단위 기준
MONTH 월단위 기준( 16 기준)
emp 테이블의 입사일자를 월을 기준으로 반올림
SELECT ename,hiredate, ROUND(hiredate, 'MONTH')
FROM emp;결과 :
TRUNC 함수에 포맷형식을 사용해서,날짜를 잘라낼 수 있습니다.
emp 테이블의 입사일자의 월을 기준으로 날짜 자르기
SELECT ename, hiredate,TRUNC(hiredate, 'MONTH')
FROM emp;
결과 :
MONTHS_ BETWEEN
날짜와 날짜 사이의 개월수를 구합니다
MONTHS_BETWEEN( date_1, data_2 )
-근무 개월 수
SELECT ename, SYSDATE, hiredate, MONTHS_BETWEEN( SYSDATE, hiredate)
FROM emp;결과 :
NEXT DAY
날짜를 기준으로 최초로 돌아오는 요일에 해당하는 날짜를 반환하는 함수입니다.
SELECT SYSDATE, NEXT_DAY(SYSDATE, '월요일')
FROM DUAL;결과 :
형변환 함수
- 숫자, 문자 , 날짜의 데이터 타입을 다른 데이터 타입으로 변환합니다.
-
TO_CHAR : 날짜또는 숫자 타입을 문자형을 변환
T0_DATE : 문자 타입을 날짜 타입으로 변환
TO_NUMBER : 문자 타입을 숫자 타입으로 변환
TO_CHAR
-
TO_CHAR( 날짜데이터, '출력형식')
-
출력형식 종류 의미
YYYY 년도(4자리)
YY 년도(2자리)
MM 월을 숫자로 표현
MON 월을 알파벳으로 표현
DAY 요일 표현
현재 날짜를 다른 형태로 출력
SELECT SYSDATE, TO_CHAR(SYSDATE, 'YYYY-MM-DD')
FROM DUAL;결과
시간 종류 출력 의미
AM or PM 오전(AM), 오후(PM)
HH or HH12 시간(1 ~ 12)
HH24 24시간
MI 분
SS 초현재 날짜와 시간 출력
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD AM HH24:MI:SS') "현재시간"
FROM DUAL;결과 :
숫자 출력 형식
-
구분 의미
0 자릿수를 나타내며, 자릴수가 맞지 않으면 0으로 채운다
9 자릿수를 나타내며, 자릴수가 맞지 않으면 0으로 채우지 않는다
. 소수점
, 천단위 자리 구분
숫자를 문자 형태로 변환
SELECT TO_CHAR(12300)
FROM DUAL;결과 : 
자리 채우기
SELECT TO_CHAR(12345, '0000000'), TO_CHAR(12345,'9999999')
FROM DUAL;결과 :
통화기호를 붙이면서 천단위마다 ',' 출력
SELECT ename, sal, TO_CHAR(sal, '999,999L')
FROM emp;결과 :
TO_DATE
-
문자열을 날짜 형식으로 변환합니다
-
TO_DATE( '문자', 'format')
숫자를 날짜형으로 변환
SELECT ename, hiredate
FROM emp
WHERE hiredate= TO_DATE(19801217,'YYYYMMDD');결과 : 
TO_NUMBER
- 데이터를 숫자형으로 변환SELECT TO_NUMBER('20,000','99,999')-TO_NUMBER('12,000','99,999')
FROM DUAL;결과 :
quiz
emp 테이블에서 사원번호가 홀수인 사원을 출력하세요
SELECT *
FROM emp
WHERE MOD(empno,2) =1;
emp 테이블에서 소문자 manager 로 직급을 검색해서 출력하세요
SELECT *
FROM emp
WHERE job=UPPER('manadger');
emp 테이블에서 조회하는 이름을 소문자로 사용해서, 사원번호, 이름, 직급, 부서번호를 출력하세요
SELECT empno, ename ,job, deptno
FROM emp
WHERE LOWER(ename) = 'smith';
dept 테이블에서 첫글자만 대문자로 변환하여 모든 정보를 출력하세요
SELECT INITCAP('deptno'), INITCAP('dname'),INITCAP('loc')
FROM dept;
emp 테이블의 ename 컬럼의 마지막 문자 하나만 추출해서 이름이 E 로 끝나는 사원을 출력하세요
SELECT *
FROM emp
WHERE SUBSTR(ename, -1, 1) = 'E';
emp 테이블에서 이름의 세번째 자리가 R 인 사원을 출력하세요
SELECT *
FROM emp
WHERE SUBSTR(ename,3,1) = 'R';
emp 테이블에서 20번 부서의 사원번호, 이름, 이름의 글자수, 급여, 급여의 자릿수를 출력하세요
SELECT empno, ename, LENGTH(ename) , sal, LENGTH(sal)
FROM emp
WHERE deptno = 20;
emp 테이블에서 현재까지 근무일수가 몇일 인지를 구하고, 근무일수가 많은 순서로 출력하세요
SELECT ename, hiredate, SYSDATE, TRUNC(SYSDATE-hiredate) "근무일수"
FROM emp
ORDER BY TRUNC(SYSDATE-hiredate) DESC;
코딩 일기장