내장 함수

SQL 내장함수

SQL 내장 함수는 상수나 속성 이름을 입력 값으로 받아 단일 값을 결과로 반환한다.

모든 내장 함수는 유효한 입력값을 받아야한다. 유효하지 않은 값이 입력되면 에러메시지를 출력한다

SQL내장 함수는 SELECT, WHERE, UPDATE SET 절 등에 모두 사용가능하다.

 

숫자함수

숫자함수의 종류

함수	설명	사용 예
ABS	숫자의 절대값을 계산	ABS(-4.5) = 4.5
CEIL	숫자보다 크거나 같은 최소의 정수	CEIL(4.1) = 5
FLOOR	숫자보다 작거나 같은 최소의 정수	FLOOR(4.1) = 4
ROUND	숫자의 반올림, 두 번째 인수는 반올림 기준 자릿수	ROUND(5.36,1) = 5.40
LOG	숫자의 자연로그 값을 반환	LOG(10) = 2.30259
POWER	숫자의 n제곱 값을 계산	POWER(2, 3) = 8
SQRT	숫자의 제곱근 값을 계산	SQRT(9.0) = 3.0
SIGN	숫자가 음수면 -1, 0이면 0, 양수면 1	SIGN(3.45) = 1

 

Q. 고객별 평균 주문 금액을 백원 단위로 반올림한 값을 구하시오

SELECT custid "고객번호", ROUND(SUM(saleprice)/COUNT(*), -2) "평균금액" FROM  Orders GROUP BY custid;

 

문자함수

함수	설명	사용 예
CHR	정수 아스키 코드를 문자로 반환	CHR(68) = 'D'
CONCAT	두 문자열을 연결	CONCAT('마당','서점') = '마당서점'
INITCAP	문자열의 첫 번째 알파벳을 대문자로 변환	INITCAP('the soap') = 'The soap'
LOWER	대상 문자열을 모두 소문자로 변환	LOWER(MR.SCOTT) = 'mr.scott'
LPAD	대상 문자열의 왼쪽부터 지정한 자리 수까지 지정한 문자로 채움	LPAD('Page 1',10,'*') = '****Page 1'
LTRIM	대상 문자열의 왼쪽부터 지정한 문자들을 제거	LTRIM('<==>BROWNING<==>', '<>=') = 'BROWNING<==>'
REPLACE	대상 문자열의 지정한 문자를 원하는 문자로 변경	REPLACE('JACK and JUE', 'J', 'BL') = 'BLACK and BLUE'
RPAD	대상 문자열의 왼쪽부터 지정한 자리 수까지 지정한 문자로 채움	RPAD('AbC',5,'*') = 'AbC**'
RTRIM	대상 문자열의 오른쪽부터 지정한 문자들을 제거	RTRIM('<==>BROWNING<==>', '<>=') = '<==>BROWNING'
SUBSTR	대상 문자열의 지정된 자리에서부터 지정된 길이만큼 잘라서 반환	SUBSTR('ABCDEFG',3,4) = 'CDEF'
TRIM	대상 문자열의 양쪽에서 지정된 문자를 삭제	TRIM('=' FROM '==>BROWNING<==') = '>BROWNING<'
UPPER	대상 문자열을 모두 대문자로 변환	UPPER('mr. scott') = 'MR. SCOTT'
ASCII	대상 알파벳 문자의 아스키 코드 값을 반환	ASCII('D') = 68
INSTR	문자열 중 3번째 인수 번째 문자부터 시작하여 찾고자 하는 문자열 2번째 인수가 4번째 인수 번째로 나타나는 문자열 위치 반환	INSTR('CORPORATE FLOOR', 'OR', 3, 2) = 14
LENGTH	대상 문자열의 글자 수를 반환	LENGTH('CANDIDE') = 7

 

날짜, 시간 함수

함수	설명	사용 예
TO_DATE	문자열 데이터를 날짜로 변환	TO_DATE('2014-02-14','yyyy-mm-dd') = 2014-02-14
TO_CHAR	날짜 데이터를 문자열으로 변환	TO_CHAR(TO_DATE('2014-02-14','yyyy-mm-dd'), 'yyyymmdd') = '20140214'
ADD_MONTH	DATE형의 날짜에서 지정한 달만큼 더함	ADD_MONTH(TO_DATE('2014-02-14','yyyy-mm-dd'), 12) 2015-02-14
LAST_DAY	DATE형의 날짜에서 달의 마지막 날을 반환	LAST_DAY(TO_DATE('2014-02-14','yyyy-mm-dd')) = 2014-02-28
SYSDATE	DBMS 시스템상의 오늘 날짜를 반환	SYSDATE = 17/07/02

 

 

NULL 값 처리

NULL 값은 아직 지정되지 않은 값을 말한다.

NULL 값은 비교 연산자로 비교가 불가능하다.

NULL 값의 연산을 수행하면 결과는 NULL 값으로 반환된다.

 

NULL 값에 대한 연산과 집계 함수

NULL + 숫자 연산의 결과는 NULL 이다.

집계 함수 계산 시 NULL이 포함된 행은 집계에서 빠진다.

 

NULL 값을 확인하는 방법

NULL 값을 찾을 때는 '=' 연산자가 아닌 IS NULL 을 사용하고, NULL이 아닌 값을 찾을 때는 IS NOT NULL 을 사용한다.

 

NVL 함수

NVL은 NULL 값을 다른 값으로 대치하여 연산하거나 다른 값으로 출력하는 함수이다.

Q. 이름, 전화번호가 포함된 고객목록을 보이시오, 단, 전화번호가 없은 고객은 '연락처없음'으로 표시하시오

SELECT name="이름", NVL(phone, '연락처없음') "전화번호" FROM Customer;

 

ROWNUM

ROWNUM은 오라클 내부적으로 생성되는 가상 컬럼으로 SQL 조회 결과의 순번을 나타낸다.

고객 목록에서 고객번호, 이름, 전화번호를 앞의 두명만 보이시오

SELECT ROWNUM "순번", custid, name, phone FROM Customer WHERE ROWNUM<=2;