1) 내장함수 : SQL 내장 함수
▶ NULL 값 처리
· NULL 값을 찾을 때는 '=' 연산자가 아닌 'IS NULL'을 사용
· NULL이 아닌 값을 찾을 때는 '< >' 연산자가 아닌 'IS NOT NULL'을 사용함
▶ ROWNUM 함수 : 자동으로 순번 매기기
SLELCT ROWNUM, A.P_name, A.price
FROM product A
▶ ROW_NUMBER() 함수 : 정렬한 후 자동으로 순번 매기기
SELECT ROWNUM() OVER(Order By A.price) AS NUM, A.p_name, A.price
FROM product A
· SQL에서는 함수의 개념을 사용하는데 수학의 함수와 마찬가지로 특정 값이나 열의 값을 입력 받아 그 값을 계산하여 결과 값을 돌려줌
· SQL의 함수는 DBMS가 제공하는 내장 함수(built-in function)와 사용자가 필요에 따라 직접 만드는 사용자 정의 함수(user-defined function)로 나뉨
· SQL 내장 함수는 상수나 속성 이름을 입력 값으로 받아 단일 값을 결과로 반환함. 모든 내장 함수는 최초에 선언될 때 유효한 입력 값을 받아야 함
▼ 오라클에서 제공하는 주요 내장 함수
▼ 숫자 함수
① 수학 함수
⑴ ABS 함수 : 절댓값을 구하는 함수
▶ -78과 +78의 절댓값을 구하시오
SELECT ABS(-78), ABS(+78);
FROM Dual; ← 존재하지 않는 가상의 테이블 (결과도 테이블로 출력됨)
⑵ ROUND 함수 : 반올림한 값을 구하는 함수
▶ 4.875를 소수 첫째 자리까지 반올림한 값을 구하시오
SELECT ROUND(4.875, 1); ←결과를 반올림해서 소수 첫째자리까지 출력
FROM Dual;
⑶ 숫자 함수의 연산
▶ 고객별 평균 주문 금액을 백 원 단위로 반올림한 값을 구하시오
SELECT custid "고객번호", ROUND(SUM(saleprice)/COUNT(*), -2) "평균금액" ← 결과를 반올림해서 소수 첫째자리까지 출력
FROM Orders
GROUP BY custid;
② 문자 함수
⑴ REPLACE : 문자열을 치환하는 함수
▶ 도서제목에 야구가 포함된 도서를 농구로 변경한 후 도서 목록을 보이시오
SELECT bookid, REPLACE(bookname, '야구', '농구')bookname, publisher, price
FROM Book;
⑵ LENGTH : 글자의 수를 세어주는 함수 (단위가 바이트(Byte)가 아닌 문자 단위)
▶ 굿스포츠에서 출판한 도서의 제목과 제목의 글자 수를 확인하시오(한글은 2바이트 혹은 UNICODE 경우는 3바이트를 차지함)
SELECT bookname "제목", LENGTH(bookname) "글자수", LENGTH(bookname) "바이트수"
FROM Book
WHERE publisher = '굿스포츠';
⑶ SUBSTR : 지정한 길이만큼의 문자열을 반환하는 함수
▶ 마당서점의 고객 중에서 같은 성을 가진 사람이 몇 명이나 되는지 성별 인원수를 구하시오
SELECT SUBSTR(name, 1, 1) "성", COUNT(*) "인원"
FROM Customer
GROUP BY SUBSTR(name, 1, 1);
③ 날짜, 시간 함수
▶마당서점은 주문일로부터 10일 후 매출을 확정한다. 각 주문의 확정일자를 구하시오
SELECT orderid "주문번호", orderdate "주문일", orderdate+10 "확정" ← 날짜형 데이터를 이용하면 +, -를 이용할 수 있다.
FROM Orders;
⑴ TO_DATE : 문자형으로 저장된 날짜를 날짜형으로 변환하는 함수
⑵ TO_CHAR : 날짜형을 문자형으로 변환하는 함수
▶ 마당서점이 2014년 7월 7일에 주문받은 도서의 주문번호, 주문일, 고객번호, 도서번호를 모두 보이시오. 단 주문일은 'yyyy-mm-dd 요일' 형태로 표시한다
SELECT orderid "주문번호", TO_CHAR(orderdate, 'yyyy-mm-dd dy') "주문일", custid "고객번호", bookid "도서번호"
FROM Orders
WHRER orderdate = TO_DATE('20140707', 'yyyymmdd');
⑶ SYSDATE : 오라클의 현재 날짜와 시간을 반환하는 함수
⑷ SYSTIMESTAMP : 현재 날짜, 시간과 함께 초 이하의 시간과 서버의 TIMEZONE까지 출력함
▶ DBMS 서버에 설정된 현재 시간과 오늘 날짜를 확인하시오
SELECT SYSDATE, TO_CHAR(SYSDATE, 'yyyy/mm/dddy hh24:mi:ss') "SYSDATE_1"
FROM Dual;
2) NULL 값 처리
① NULL 값이란?
· 아직 지정되지 않은 값
· NULL 값은 '0', '빈 문자', '공백' 등과 다른 특별한 값
· NULL 값은 비교 연산자로 비교가 불가능함
· NULL 값의 연산을 수행하면 결과 역시 NULL 값으로 반환됨
② 집계 함수를 사용할 때 주의할 점
· 'NULL+숫자' 연산의 결과는 NULL
· 집계 함수 계산 시 NULL이 포함된 행은 집계에서 빠짐
· 해당되는 행이 하나도 없을 경우 SUM, AVG 함수의 결과는 NULL이 되며, COUNT 함수의 결과는 0
③ NULL 값을 확인 하는 방법 : IS NULL, IS NOT NULL
· NULL 값을 찾을 때는 '=' 연산자가 아닌 'IS NULL'을 사용
· NULL이 아닌 값을 찾을 때는 '< >' 연산자가 아닌 'IS NOT NULL'을 사용함
ex)
SELECT *
FROM Mybook
WHERE price IS NULL;
④ NVL : NULL값을 다른 값으로 대치하여 연산하거나 다른 값으로 대치하여 연산하거나 다른 값으로 출력 (속성 값이 NULL이면 '값'으로 대치한다)
· NVL(속성, 값)
▶ 이름, 전화번호가 포함된 고객 목록을 보이시오. 단, 전화번호가 없는 고객은 '연락처 없음'으로 표시한다
SELECT name "이름", NVL(phone, '연락처없음') "전화번호"
FROM Customer;
3) ROWNUM : 내장 함수는 아니지만 자주 사용되는 문법임(자동 번호 매김)
· 오라클에서 내부적으로 생성되는 가상 컬럼으로 SQL 조회 결과의 순번을 나타냄
· 자료를 일부분만 확인하여 처리할 때 유용함
▶ 고객 목록에서 고객번호, 이름, 전화번호를 앞의 두 명만 보이시오
SELECT ROWNUM "순번", custid, name, phone
FROM Customer
WHERE ROWNUM <= 2;
