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;