※ 내장함수들
--숫자함수
--문자함수
--날짜함수
--오라클 내장함수 : 오라클은 대소문자 구분안하지만 함수라는걸 보이기 위해 대문자사용
--A. 단일행(SingleRow)함수 : 각 행별로 처리되는 함수
--1. 숫자형함수 : MOD() 나머지값, ROUND() 반올림, TRUNC() 버림(truncate), CEIL() 올림 천장값, FLOOR() 내림/버림 바닥값, ABS 절대값, COS 코싸인값 반환,
특정 컬럼에 대한 반올림이 아니라 1234.567 리터럴에 대한 반올림이 얼마인지 보고싶을 때
SELECT ROUND(1234.567)
FROM employees;
employees에 나온 사원 수만큼 107번 반복하면서 반올림값 출력(아래 사진)
이건 번거로우니 테스트용도 테이블인 dual 사용
--반올림
SELECT ROUND(1234.567) --1235
FROM dual;
SELECT ROUND(1234.567) --1235
,ROUND(1234.567, 1)
--소수점 이하 한자리까지 보여준다, 한자리에서 반올림해라가 아님, 1234.6
FROM dual;
SELECT ROUND(1234.567)
,ROUND(1234.567, 0) -- 위와 아래 같다
FROM dual;SELECT ROUND(1234.567, 1) --1234.6
,ROUND(1234.567, -1) --1230
FROM dual;
SELECT ROUND(1234.567) --1235
, ROUND(1234.567, 0) --1235
, ROUND(1234.567, 1) --1234.6
, ROUND(1234.567, -1) --1230
FROM dual;--버리기
SELECT TRUNC(1234.567) --1234
,TRUNC(1234.567, 0) --1234
,TRUNC(1234.567, 1) --1234.5
,TRUNC(1234.567, -1) --1230
FROM dual;--올림,내림
SELECT CEIL(1234.1) --1235
,FLOOR(1234.1) --1234
FROM dual;--사원의 사번, 급여, 수당률, 실급여, 실웝급을 출력한다. 단, 실월급은 소숫점이하 2자리에서 반올림한다
--실급여: 급여+(급여*수당률) --NVL()
--실월급: 실급여/12
SELECT employee_id, salary, commission_pct
,salary+(salary*NVL(commission_pct,0))
,ROUND(salary+(salary*NVL(commission_pct,0))/12, 1)
FROM employees;--2. 문자형함수 : LENGTH() 문자의 길이, INSTR() 특정 문자의 위치를 반환, SUBSTR() 부분문자열, LPAD()/RPAD 왼쪽/오른쪽에 특정 문자 덧대기, TRIM()/LTRIM()/RTRIM() 왼쪽/오른쪽 문자열 제거
UPPER() 모두 대문자로 변경/ LOWER() 모두 소문자로 변경 / INITCAP() 각 단어의 첫 글자를 대문자로 나머지는 소문자로 변환하여 반환, CONCAT 문자결합 = ||
'ABC' || 'DEF'= CONCAT('ABC', 'DEF')
ASCII('A') = 65
CHAR(65) = 'A'
cf) DB에서는 모든 인덱스의 시작은 1, 0 아님
SELECT LENGTH('HELLOORACLE') --11
,LENGTH('안녕하세요') --5
,LENGTHB('안녕하세요') -- 바이트수 계산 15 각 문자 3바이트 한글 UTF-8
,INSTR('HELLOORACLE', 'H') --1
,INSTR('HELLOORACLE', 'L') --L이라는 문자의 첫번째 위치값 3
,INSTR('HELLOORACLE', 'L', 5) -- 5번쨰 문자부터 L 위치 찾기 10
,INSTR('HELLOORACLE', 'X') -- 해당 문자가 없으면 0을 반환
FROM dual;
SELECT SUBSTR('HELLOORACLE', 2, 3) --2번째 문자부터 3개 꺼내기 ELL
,LPAD('HELLO', 8, '*')--***HELLO 8자리까지 만들어라 빈자리는 * 넣기
,'BEGIN'||LTRIM(' HELLO ')||'END' --BEGINHELLO END
,'BEGIN'||RTRIM(' HELLO ')||'END' --BEGIN HELLOEND
,'BEGIN'||TRIM(' HELLO ')||'END' --BEGINHELLOEND
FROM dual;--사원의 이름중 두번째 글자가 'e'인 사원들의 사번, 이름을 출력하시오
SELECT employee_id, first_name
FROM employees
WHERE INSTR(first_name, 'e') = 2;
--사원의 이름중 두번째 글자가 대소문자구별없이 'e'인 사원들의 사번, 이름을 출력하시오
SELECT employee_id, first_name
FROM employees
WHERE INSTR(first_name, 'e') = 2 OR INSTR(first_name, 'E') = 2;
SELECT employee_id, first_name
FROM employees
WHERE INSTR(UPPER(first_name), 'E') = 2;--3. 날짜형함수 : SYSDATE, MONTHS_BETWEEN() 개월 수 계산, ADD_MONTHS() 개월 수 증가,
LAST_DAY() 해당 월의 마지막 일자값, NEXT_DAY()앞으로 다가올 요일에 해당하는 날짜
SELECT SYSDATE --23/08/16 년월일시분초(현재시간)가 다 들어있으나 default로 보여줄때는 년/월/일
,MONTHS_BETWEEN(SYSDATE, '23/01/01') -- 7.4992...
,ADD_MONTHS(SYSDATE, 1) -- 23/09/16 오늘 날짜 기준으로 한달 후, "한달후날짜"
,ADD_MONTHS(SYSDATE, -6) -- 23/02/16 오늘 날짜 기준으로 육개월 전, "6개월전날짜"
,LAST_DAY('23/02/16') --23/02/28 "2월의 마지막 날짜"
,NEXT_DAY(SYSDATE, '일요일') --23/08/20 "일자기준 다가올 일요일에 해당하는 날짜"
FROM dual;--날짜형데이터는 +,- 연산 가능하다
--날짜 + 숫자 → 날짜값을 반환, 날짜 - 숫자 → 날짜값을 반환, 날짜 - 날짜 → 숫자값(일수)를 반환
SELECT SYSDATE --23/08/16
,SYSDATE+1 "내일날짜" --23/08/17
,SYSDATE-1 "어제날짜" --23/08/15
,SYSDATE- TO_DATE('23/01/01') "일수" --227.4750... 소수점 값은 시분초값으로 보면 된다
--오전 11시 22분 40초 - 오전 0시 0분 0초
,TRUNC(SYSDATE- TO_DATE('23/01/01')) --227
FROM dual;
※ MONTHS_BETWEEN(SYSDATE, '23/01/01')
여기서는 문자로 전달한 인자를 날짜 타입으로 변환해서 사용
LAST_DAY('23/02/16')
전달한 (함수의 인자) 문자 자료형을 날짜 타입으로 변환해서 사용
--4. 형변환함수
--자동형변환 : 숫자형 <-> 문자형 <-> 날짜형
SELECT * FROM employees WHERE department_id=30; --OK
SELECT * FROM employees WHERE department_id='30'; --문자형'30'이 숫자형30으로 자동형변환됨
SELECT * FROM employees WHERE department_id='030'; --문자형'030'이 숫자형30으로 자동형변환됨
SELECT * FROM employees WHERE hire_date='03/05/18'; --문자형'03/05/18'이 날짜형으로 자동형변환됨--강제형변환 : 숫자형 <-> 문자형 <-> 날짜형
자동형변환되는거 기다리지말고 명시적으로 강제형변환하는걸 사용하는게 좋다
TO_DATE() : 문자형 → 날짜형, 시분초정보는 자동 0시 0분 0초
TO_CHAR() : 문자형 ← 날짜형
TO_NUMBER() : 문자형 → 숫자형
TO_CHAR() : 문자형 ← 숫자형
숫자형<->날짜형은 불가능. 숫자형→문자형→날짜형 날짜형→문자형→숫자형 이렇게 바꿔야함
--(1)TO_DATE() : 문자형 → 날짜형
--(2)TO_CHAR() : 문자형 ← 날짜형
SELECT TO_DATE('23/12/22') --23/12/22
,TO_DATE('23-12-22') --23/12/22
,TO_DATE('12-22-23 09:10:35', 'mm-dd-yy HH24:mi:ss') --23/12/22
,TO_CHAR(SYSDATE) --23/08/16
,TO_CHAR(SYSDATE, 'yy/mm/dd hh24:mi:ss day') --23/08/16 11:44:30 수요일
FROM dual;--사원의 사번, 입사일자, 오늘까지의 근무일수, 22년 12월 31일까지의 근무일수를 출력하시오
SELECT employee_id, hire_date
,TRUNC(SYSDATE - hire_date) "오늘까지의 근무일수"
,TRUNC(TO_DATE('22/12/31') - hire_date) "22년까지 근무일수"
FROM employees;
--"8"월 입사자들의 사원의 사번, 입사일자, 입사요일를 출력하시오
SELECT employee_id, hire_date, TO_CHAR(hire_date, 'day')
FROM employees
WHERE TO_CHAR(hire_date, 'mm') = '08';--(3)TO_NUMBER() : 문자형 → 숫자형
--(4)TO_CHAR() : 문자형 ← 숫자형
,가 들어가서 문자 이 문자의 구조는 3자리마다 , 소숫점 이하 한자리를 갖는 문자 패턴
SELECT TO_NUMBER('1,234.5', '9,999.9') --1234.5
,TO_NUMBER('1,234,567.8', '9,999,999,999.9')--1234567.8, 문자자릿수보다 많은 패턴자릿수가 필요
,TO_CHAR(1234.5, '9,999.9') -- '1,234.5'
,TO_CHAR(1234567.8, '9,999.9') --숫자자릿수보다 작은 패턴자릿수인 경우 ####
,TO_CHAR(1234567.8, '9,999,999,999.9') --'1,234,567.8'
FROM dual;TO_NUMBER('1,234,567.8', '9,999.9')
TO_CHAR(1234567.8, '9,999.9')
첫번째 : 정수부 부분이 4자리라서 에러 발생, 실제 문자의 자릿수보다 숫자패턴의 자릿수가 모자라서 에러
두번째: 패턴의 자릿수가 숫자자릿수보다 작아서 에러
SELECT TO_CHAR(1234.5, '9,999,999.9') --'1234.5'
,TO_CHAR(1234.5, '0,000,000.0') --'0,001,234.5' 0패턴은 자릿수가 고정, 정수일 때는 잘 안씀
,TO_CHAR(1234.5, '0,000,000.00') --'0,001,234.50' 소숫점 자리 고정하고 싶을 때 0 패턴
FROM dual;
SELECT TO_CHAR(1234.5, 'L9,999,999.00') --'￦1,234.50'
FROM dual;
--5. NULL관련 함수 : NVL(), NVL2(), NULLIF()
--1)NVL(,) : 첫번째 인자값이 NULL이면 두번째 인자값을 반환, NULL이 아니면 첫번째 인자값을 반환
--2)NVL2(, , ) : 첫번째 인자값이 NULL이면 세번째 인자값을 반환, NULL이 아니면 두번째 인자값을 반환
두번째 인자의 자료형과 세번째 인자의 자료형은 서로 같아야한다.
SELECT employee_id, commission_pct, NVL2(commission_pct, '수당있음', '수당없음')
FROM employees;SELECT employee_id, commission_pct, NVL2(commission_pct, commission_pct, '수당없음')
FROM employees;
--commission_pct은 숫자타입, '수당없음' 문자타입이라서 에러 남.
SELECT employee_id, commission_pct, NVL2(commission_pct, TO_CHAR(commission_pct), '수당없음')
FROM employees;
--이래야 자료형이 일치한다.--3)NULLIF(,) : 첫번째 인자값과 두번째 인자값이 같으면 null을 반환, 다르면 첫번째 인자값을 반환
SELECT NULLIF(10, 10) --null
,NULLIF('hello', 'hi')--hello
FROM dual;--4) NULL 관련 비교연산자 IS NULL, IS NOT NULL; = <> 이런걸로 비교하면 안됨
SELECT employee_id, department_id
FROM employees
WHERE department_id = NULL; --0건, 비교행의 결과가 없음. null과는 비교연산 하면 안됨SELECT employee_id, department_id
FROM employees
WHERE department_id IS NULL; --1건
SELECT employee_id, department_id
FROM employees
WHERE department_id <> NULL; --0건
SELECT employee_id, department_id
FROM employees
WHERE department_id IS NOT NULL; --106건--6. 일반함수 :
--DECODE() : 아래사진 처럼 가변길이 함수처럼 인자의 길이 계속 늘릴 수 있음 오라클에 내장되어있는 함수
SELECT NVL(commission_pct, 0)
,DECODE(commission_pct, null, commission_pct, 0)
FROM employees;
--DECODE 1번째 인자값이 2번째 인자값과 같은가 비교, 같으면 4번째 인자값 처리, 다르면 3번째 인자값 반환)
--NVL과 값 동일하게 나옴SELECT NVL2(commission_pct, '수당있음', '수당없음')
,DECODE(commission_pct, null, '수당없음', '수당있음')
FROM employees;--수당없으면 '수당없음'을 출력하고
--수당이 0.1인 경우 'B등급',
--그외의 경우 'A등급'을 출력하시오
SELECT employee_id, commission_pct
,DECODE(commission_pct, null, '수당없음', 0.1, 'B등급', 'A등급')
FROM employees;
--CASE절
CASE 컬럼 WHEN 값1 THEN 출력
WHEN 값2 THEN 출력
ELSE
END
CASE WHEN 조건식 THEN
WHEN 조건식 THEN
ELSE
END
--수당없으면 '수당없음'을 출력하고
--수당이 0.1인 경우 'B등급',
--그외의 경우 'A등급'을 출력하시오
SELECT employee_id, commission_pct
,CASE commission_pct WHEN 0.1 then 'B등급'
ELSE 'A등급'
END
FROM employees;--수당없으면 '수당없음'을 출력하고
--수당이 0.1~0.19까지는 'F등급',
-- 0.2~0.29까지는 'E'
-- 0.3~0.39까지는 'D'
-- 0.4~0.49까지는 'C'
-- 0.5~0.59까지는 'B'
-- 0.6 이상은 'A'
SELECT employee_id, commission_pct
,CASE WHEN commission_pct>= 0.6 THEN 'A'
WHEN commission_pct>= 0.5 THEN 'B'
WHEN commission_pct>= 0.4 THEN 'C'
WHEN commission_pct>= 0.3 THEN 'D'
WHEN commission_pct>= 0.2 THEN 'E'
WHEN commission_pct>= 0.1 THEN 'F'
--ELSE "수당없음'
WHEN commission_pct IS NULL THEN '수당없음'
END
FROM employees;decode나 case절을 써서 else if문처럼 사용
'데이터베이스 > Oracle' 카테고리의 다른 글
| JOIN (0) | 2023.08.21 |
|---|---|
| 내장함수(다중행 함수, 그룹화, 정렬하기) (1) | 2023.08.21 |
| 방화벽 오라클만 풀기 (0) | 2023.08.21 |
| SQL 기본 예제 1 (0) | 2023.08.21 |
| SQL Developer 시작하기 (0) | 2023.08.21 |
