SELECT문 함수
1.DUAL 테이블과 SQL 함수 분류
함수를 배우기 전에 한 행으로 결과를 출력하기 위한 테이블인 DUAL 테이블에 대해 살펴본다.
1)DAUL 테이블
SELECT 컬럼명, 컬럼명
FROM 테이블명
WHERE 조건식
ORDER BY 컬럼(별칭 OR 순번 작성 가능) ASC/DESC
SELECT 연산식 FROM 테이블명; (SELECT를 쓸려면 FROM 필요. 테이블이 가지고 있는 레코드 수 만큼 출력)
SELECT 연산식 FROM DUAL; 연산식 함수 확인하기 위한 테이블(한번만 나옴. DUAL: 임시 테이블, 산술 연산이나 가상 칼럼 등의 값을 하나의 레코드로 출력, DUMMY라는 한 개의 칼럼으로 구성)
테이블을 만들면 테이블을 만들때 소유주가 있어서 테이블 공통이 아님.(계정 체크 중요 HR(샘플용으로 제공)이면 HR에서만 사용 가능, 다른 사용자가 접근 못함. 권한을 주면 말이 다름. 테이블을 만들 때 사용자 체크)
2) 단일 행 함수와 그룹 함수로 SQL 함수 분류
함수를 이용하여 복잡한 질의를 간결하게 표현(함수를 주면 함수를 포함한게 컬럼이름=> 별칭을 줌)
SELECT DEPARTMENT_ID, LOWER(FIRST_NAME), SALARY --LOWER(FIRST_NAME)이 칼럼 이름이 된다.
FROM EMPLOYEES
WHERE DEPARTMENT_ID=30;
단일행 함수(행 마다 함수가 적용되어 결과를 반환)
그룹함수(하나 이상의 행을 그룹으로 묶어 연산)
2.문자함수
| LOWER | 소문자로 변환 |
| UPPER | 대문자로 변환 |
| INITCAP | 첫 글자만 대문자로 나며지 글자는 소문자로 변환 |
| CONCAT | 문자의 값을 연결 |
| SUBSTR | 문자를 잘라 추출(한글 1BYTE) |
| SUBSTRB | 문자를 잘라 추출(한글 2BYTE) |
| LENGTH | 문자의 길이를 반환 (한글 1BYTE) |
| LENTRHB | 문자의 길이를 반환(한글 1BYTE) |
| INSTR | 특정 문자의 위치값을 반환(한글 1BYTE) |
| INSTRB | 특정 문자의 위치 값을 반환(한글 2BYTE) |
| LPAD, RPAD | 입력 받은 문자열과 기호를 정렬하여 특정 길이의 문자열로 반환 |
| TRIM | 잘라내고 남은 문자를 표시 |
| CONVERT | CHAR SET을 변환 |
| CHR | ASCII코드 값으로 변화 |
| ASCII | ASCII코드 값을 문자로 변화 |
| REPLACE | 문자열에서 특정 문자를 변경 |
1) 소문자로 변환: LOWER
WHERE LOWER(JOB_ID)='it_prog';
2) 대문자로 변환: UPPER
WHERE EMAIL=UPPER('jking');
3) 첫 글자만 대문자 나머지 소문자: INICAP
SELECT EMPLOYEE_ID, FIRST_NAME, INITCAP(EMAIL)
4)두 문자를 연결: CONCAT
SELECT CONCAT(CONCAT(FIRST_NAME, ' '),LAST_NAME) "사원 이름"
--중복 사용 가능
5)문자 길이를 구함: LENGTH/LENGTHB
WHERE LENGTH(FIRST_NAME)=4;
6)문자열 일부만 추출: SUBSTR/SUBSTRB //DB시작은 1부터
- 시작위치 인자 값: 음수
뒤 쪽에서부터 세어서 시작위치를 잡음
WHERE SUBSTR(HIRE_DATE,1,2)=03;
WHERE SUBSTR(FIRST_NAME, -1,1)='k';
7)특정 문자의 위치를 구하는 INSTR/INSTRB
INSTR(FIRST_NAME, 'e')
8)특정 기호로 채우는 LPAD/RPAD =>자릿수를 맞출때 사용
SELECT LPAD('DATABASE',10, ' ') FROM DUAL;
SELECT CONCAT(RPAD(FIRST_NAME, 10,' '), CONCAT('($',CONCAT(SALARY,')'))) AS "사원 정보"
9)특정 문자를 잘라내는 TRIM 함수=>공백제거, 방향설정 가능, 앞뒤 공백 제거하고 데이터만 검색
SELECT TRIM(LEADING FROM ' ABCD '), LENGTH(TRIM(LEADING FROM ' ABCD ')) LT_LEN,
TRIM(TRAILING FROM ' ABCD ') RT, LENGTH(TRIM(TRAILING FROM ' ABCD ')) RT_LEN,
TRIM(BOTH FROM ' ABCD ') BOTH1, LENGTH(TRIM(BOTH FROM ' ABCD ')) BOTH1,
TRIM(' ABCD ') BOTH2, LENGTH(TRIM(' ABCD ')) BOTHLEN2
FROM DUAL;
3. 숫자함수
숫자형 데이터를 조작하여 변환된 숫자 값을 반환
| ABS | 절대값을 반환 |
| COS | COSINE값을 반환 |
| CEIL | 무조건 소수점 아래를 올린다.(절상) |
| FLOOR | 무조건 소수점 아래를 잘라낸다.(절사) |
| LOG | LOG값을 반환 |
| POWER | POWER(m,n) m의 n승을 반환 |
| SIGN | SIGN(n) n<0이면 -1, n=0이면 0, n>0이면 1을 반환 |
| SIN | SINE값을 반환 |
| TAN | TANGENT값을 반환 |
| ROUND | 특정 자릿수에서 반올림 |
| TRUNC | 특정 자릿수에서 잘라낸다.(버림) |
| MOD | 입력 받은 수를 나눈 나머지 값을 반환 |
1)ABS 함수/CEIL 함수/FLOOR 함수
ABS 함수는 절대값을 반환
CEIL(n) 무조건 소수점 아래를 올림, FLOOR(n) 함수 무조건 소수점 아래를 잘라냄
2) 특정 자릿수에서 반올림: ROUND 함수
ROUND(대상, 표시할 자릿수) //소수점 첫째자리가 1 기준
3)특정 자릿수에서 잘라내는 TRUNC 함수
지정한 자릿수 이하를 버린 것
4)나머지 값을 반환: MOD
4.날짜 함수
DATE형에 사용하는 함수 결과 값으로 날짜 또는 기간
날짜+숫자=>date
날짜-숫자=>date
날짜-날짜=>number(일수)
| SYSDATE | 시스템 저장된 현재 날짜를 반환 |
| MONTHS_BETWEEN | 두 날짜 사이가 몇 개월인지 반환 |
| ADD_MONTHS | 특정 날짜에 개원 수를 더한다. |
| NEXT_DAY | 특정 날짜에서 최초로 도래하는 인자로 받은 요일의 날짜를 반환 |
| LAST_DAY | 해당 달의 마지막 날짜를 반환 |
| ROUND | 인자로 받은 날짜를 특정 기준으로 반올림 |
| TRUNC | 인자로 받은 날짜를 특정 기준으로 버림 |
1)현재 날짜를 반환:SYSDATE
-날짜형 데이터는 더하기나 빼기 연산 가능(일자)
2)두 날짜 사이 간격을 계산: MONTHS_BETWEEN
-날짜와 날짜 사이의 개월 수를 구함.
TO_DATE: 문자를 날짜 데이터 타입으로 변화해 주는 함수
3)특정 날짜에 개월수를 더함: ADD_MONTHS
4) 해당 요일의 가까운 날짜: NEXT_DAY
5) 해당 달의 마지막 날짜를 반환:LAST_DAY
6)ROUND 함수의 다양한 적용
7)TRUNC 함수의 다양한 적용
5. 변환 함수
자료형을 변환시키고자 할때 사용
TO_CHAR: 날짜형 혹은 숫자형을 문자형으로 변환
TO_DATE: 문자형을 날짜형으로 변환
TO_NUMBER: 문자형을 숫자형으로 변환
1)TO_CHAR
DDD: 01~365일
DY: 요일의 약어(월, 화,..)
DL: 현재 일을 요일까지 표시
AM,PM / A.M, P.M / HH, HH12 (시간1~12)/ HH24 (24시간)
숫자형을 문자형으로 변환
TO_CHAR(숫자, '출력형식')
| 0 | 자릿수를 나타내며 자릿수가 맞지 않을 경우 0으로 채움 |
| 9 | 자릿수를 나타내며 자릿수가 맞지 않아도 채우지 않는다. |
| L | 각 지역별 통화 기호를 앞에 표시 |
--숫자형을 문자형으로 변환하기
SELECT TO_CHAR(123467, 'FM999,999') --123,467
, TO_CHAR(123467890, 'FM999,999,999') --123,467,890
, TO_CHAR(123467, 'FML999,999') --￦123,467
FROM DUAL;TO_DATE: 날짜형 변환 함수
6.일반 함수
| NVL | 첫 번째 인자로 받은 값이 NULL과 같으면 두 번째 인자 값으로 변경 |
| DECODE | 첫 번째 인자로 받은 값을 조건에 맞춰 변경(if와 유사) |
| CASE | 조건에 맞는 문장을 수행한다.(switch와 유사) |
1)NULL을 다른 값으로 변환: NVL, NVL2
NVL(컬럼 또는 표현식, 대체값)
SELECT FIRST_NAME, SALARY, COMMISSION_PCT, SALARY*NVL(COMMISSION_PCT,0) AS COMMISSION,
SALARY+(SALARY*NVL(COMMISSION_PCT,0)) AS TOTAL, JOB_ID
FROM EMPLOYEES
ORDER BY JOB_ID;
NVL2 함수
NVL2(컬럼 또는 표현식, NULL값이 아니면 처리할 구문, NULL 값이면 처리할 구문)
SELECT FIRST_NAME, SALARY, COMMISSION_PCT,
NVL2(COMMISSION_PCT, SALARY+(SALARY*COMMISSION_PCT), SALARY)TOTAL_SAL
FROM EMPLOYEES;--<문제> 모든 직원은 자신의 상관(MANAGER_ID)이 있자.
--하지만 EMPLOYEES 테이블에 우일하게 상관이 없는 로우가 있는데 그 사원의 MANAGER_ID 칼럼 값이 NULL이다.
--상관이 없는 대표이사만 출력하되 MANAGER_ID 칼럼 값 NULL 대신 CEO로 출력
---01722: 수치가 부적합합니다
SELECT EMPLOYEE_ID, FIRST_NAME, NVL(MANAGER_ID, 'CEO')--MANAGER_ID=숫자타입
FROM EMPLOYEES
WHERE MANAGER_ID IS NULL;
SELECT EMPLOYEE_ID, FIRST_NAME, NVL(TO_CHAR(MANAGER_ID),'CEO')
FROM EMPLOYEES
WHERE MANAGER_ID IS NULL;2) 선택을 위한 DECODE 함수
SWITCH CASE 문과 같이 여러가지 경우에 대해서 선택할 수 있는 함수
DECODE(표현식, 조건1, 결과1, 조건2, 결과2, 조건3, 결과3, ... 조건절외 나머지 결과n )
3)조건에 따라 서로 다른 처리가 가능한 case 함수
CASE WHEN 조건1 THEN 결과1
WHEN 조건2 THEN 결과2
WHEN 조건3 THEN 결과3
ELSE 결과n
END( AS 별칭)