SQL 함수
. 문자 함수
하나의 특별한 목적의 작업을 수행하기 위해 독립적으로 설계된 프로그램 코드의 집합
ex) print(출력할 값) << 이런 형식이 함수
내장함수
오라클에서 기본적으로 제공하고 있는 함수
1.1 문자함수
- UPPER(데이터)
데이터를 대문자로 변환
- LOWER(데이터)
데이터를 소문자로 변환
ex) 이름과 이름(대문자), 메일과 메일(소문자)로 출력
SELECT first_name, UPPER(first_name), email, LOWER (email)
FROM employees;
- LENGTH(데이터)
문자열의 길이를 구하는 함수
ex) 이름과 길이 8 이상인 직원만 출력
SELECT first_name, LENGTH(first_name)
FROM employees
WHERE LENGTH(first_name) >= 8;
- SUBSTR(데이터,시작위치,추출길이)
문자열 추출하는 함수
SUBSTR 데이터 시작위치 : 시작 위치부터 끝까지 추출
SUBSTR 데이터 시작위치 추출길이 : 시작 위치부터 추출길이만큼 추출
ex) JOB_ID에서 _기준으로 앞 뒤 따로 출력
SELECT JOB_ID
, SUBSTR(JOB_ID,1,2) AS 앞
, SUBSTR(JOB_ID,4) AS 뒤
FROM employees;
실습 입사일을 출력하시오 연도 월 일
SELECT SUBSTR(hire_date,1,2) AS 연도
, SUBSTR(hire_date,3,2) AS 월
, SUBSTR(hire_date,6,2) AS 일
FROM employees;
- REPLACE(데이터, 찾을 문자, 대체할 문자)
문자열 대체하는 함수
대체할 문자를 안 적는 경우 삭제로 처리
SELECT hire_date
, REPLACE(hire_date, '/', '-') as 하이픈
, REPLACE(hire_date, '/') as 제거
FROM employees;
- CONCAT(데이터, 데이터)
문자열 합치는 함수
SELECT CONCAT('입사일은 ', hire_date)
FROM employees;여기서 특이점.
CONCAT(데이터, 데이터, 데이터)는 에러발생,
ORA-00909: invalid number of arguments
00909. 00000 - "invalid number of arguments"
함수가 3개 이상 문자열 합치는 것을 지원하지 않음
해결방안.
- || 문자열을 연결
SELECT '입사일은 '|| hire_date || '입니다'
FROM employees;
- TRIM
삭제할 문자가 없는 경우 공백 제거
TRIM('문자열') 공백 삭제
SELECT '[' || ' ' ||'공백' || ' ' || ']' as "공백 제거 전"
, '[' || TRIM(' ' ||'공백' || ' ') || ']' as "공백 제거 후"
FROM DUAL; -- SYS 더미테이블, TEST용으로 이용
TRIM( 옵션 '제거할 문자' FROM '문자열') 문자 삭제
- 삭제옵션
LEADING 왼쪽
TRAILING 오른쪽
BOTH 양쪽글자
SELECT '1문자1' as "문자 제거 전"
, TRIM(LEADING '1' FROM '1문자1') as " 왼쪽 문자 제거"
, TRIM(TRAILING '1' FROM '1문자1') as " 오른쪾 문자 제거"
, TRIM(BOTH '1' FROM '1문자1') as " 양쪽 문자 제거"
, TRIM(LEADING '1' FROM '111문자111') as " 왼쪽 문자 제거2"
, TRIM(TRAILING '1' FROM '111문자111') as " 오른쪽 문자 제거2"
, TRIM(BOTH '1' FROM '111문자111') as " 양쪽 문자 제거2"
FROM DUAL;
2. 숫자함수
- MOD(나눗셈될 숫자, 나누고 싶은 숫자)
나머지를 반환하는 함수
SELECT MOD(15, 6)
FROM DUAL;
-- 결과값 3출력SELECT MOD(15, 2) AS 홀수
, MOD(16, 2) AS 짝수
FROM DUAL;
-- 결과값 홀수 1 짝수 0
- ROUND
반올림하는 함수
- TRUNC
버림 함수
SELECT ROUND(15.2468)
, TRUNC(15.2468)
FROM DUAL;
-- 결과값 15 15 출력함수(숫자, 소수점자릿수)
SELECT ROUND(15.2468,3)
, TRUNC(15.2468, 3)
FROM DUAL;
-- 결과값 15.247 15.246 출력
3. 날짜함수
- SYSDATE
오라클 DB에 있는 OS의 현재 날짜와 시간
SELECT SYSDATE
FROM DUAL;
-- 현재 날짜와 시간 표시* 도구 > 환경설정 > 데이터베이스 > NLS에서 날짜형식 YYYY/MM/DD HH24:MI:SS 변경을 해야 출력이 됩니다.
각각 하루, 1시간, 1분, 1초 추가가 가능합니다.
SELECT SYSDATE as 현재
, SYSDATE + 1 as 하루더함
, SYSDATE + 1/24 as 한시간더함
, SYSDATE + 1/24/60 as 일분더함
, SYSDATE + 1/24/60/60 as 일초더함
FROM DUAL;
- ADD_MONTHS
몇 개월 이후 날짜를 구하는 함수
SELECT SYSDATE as 현재
, ADD_MONTHS(SYSDATE, 1) as "1개월 후"
, ADD_MONTHS(SYSDATE, -1) as "1개월 전"
FROM DUAL;
4. 형변환 함수
- TO CHAR
문자 데이터로 변환
SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD/DAY')
FROM DUAL;
-- 출력값 2023/05/04/목요일
- TO_NUMBER
숫자로 변환
SELECT TO_NUMBER('1')+1
FROM DUAL;* 단 숫자만 가능 NUMBER('100원')의 경우 오류 발생
- TO_DATE
날짜로 변환
- 암시적 형변환
자동으로 형변환을 한다.
SELECT '1'+1
FROM DUAL;
-- 출력값 2
5. NULL 함수
NULL에 산술 연산 을 하면 NULL
NULL 비교 연산하면 FALSE
NVL(검사데이터, NULL일 때)
NVL(검사데이터, NULL 아닐 때, NULL일 때)
SELECT FIRST_NAME
, NVL(FIRST_NAME, '이름없음')
FROM employees;
-- 직원 이름이 NULL의 경우 이름없음 출력SELECT FIRST_NAME
, NVL2(FIRST_NAME, '이름있음','이름없음')
FROM employees;
-- 직원의 이름이 있는 경우 이름 있음, NULL 경우 이름 없음 출력
실습
커미션이 NULL 일떄 0으로 대체하서 출력
SELECT NVL(commission_pct, '0')
FROM employees;매니저가 있는 직원은 직원으로 매니저가 업슨 직원은 관리자로 출력
SELECT manager_id
, NVL2(manager_id, '직원', '관리자')
FROM employees;
6. DECODE (검사대상, 비교값 1, 일치 시 반환될 값,... , 일치하지 않을 때 반환할 값)
부서에 점수에 따라서 급여 인상률을 변경해서 급여 출력
SELECT DEPARTMENT_ID, SALARY
, DECODE(DEPARTMENT_ID
, 100, SALARY*2
, 90, SALARY*1.9
, 80, SALARY*1.8
, SALARY) as UPSALARY
FROM employees;
7. 그룹함수
- sum : 합
- count : 개수
- min : 최소값
- max : 최대값
- ave : 평균
-- 급여의 총합
SELECT SUM(SALARY)
FROM EMPLOYEES;SELECT min(SALARY)
FROM EMPLOYEES; -- 급여 최소값
SELECT max(SALARY)
FROM EMPLOYEES; -- 급여 최대값
SELECT AVG(SALARY)
FROM EMPLOYEES; -- 급여 평균
8. ORDER BY 함수
- 오름차순 ASC
- 내림차순 DESC
- ORDER BY정렬한 칼럼, 옵션(오름, 내림)
SELECT department_id
FROM employees
ORDER BY department_id ASC;