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 별칭)