![[DBMS/Oracle] 함수](http://t1.daumcdn.net/tistory_admin/static/images/xBoxReplace_250.png)
함수
- 특정 결과를 반환하는 모듈울 미리 작성하고 컴파일하여 실행 가능한 상태로 구성된 반환값이 있는 서브프로그램
- 컬럼의 값이나 데이터 타입을 변경
- 숫자, 날짜 자료의 출력 형식 변경
- 특정 값을 기준으로 데이터를 그룹화하고 각 그룹에서 집계를 수행
- 단일행 함수/복수행 함수
- 문자열 함수/숫자함수/날짜함수/변환함수/집계함수/분석함수 등으로 구분
1. 문자열 함수
1) '||' - ★★★★
. 문자열 결합연산자
. 자바의 문자열 연산자 '+'와 동일 기능 수행
사용예) 회원테이블에서 대전에 거주하는 회원정보를 조회하시오.
Alias는 회원번호,회원명,주민번호,주소이며 주민번호의 출력은'XXXXXX-XXXXXXX'형태로,
주소는 기본 주소와 상세주소를 공백으로 연결하여 출력하시오.
SELECT MEM_ID AS 회원번호,
MEM_NAME AS 회원명,
MEM_REGNO1||'-'||MEM_REGNO2 AS 주민번호,
MEM_ADD1||' '||MEM_ADD2 AS 주소
FROM MEMBER
WHERE MEM_ADD1 LIKE '대전%'
2) CONCAT(c1, c2) - ★★
. 문자열 c1과 c2를 결합하여 새로운 문자열로 반환
. '||' 연산자와 동일 기능 제공
사용예) 회원테이블에서 대전에 거주하는 회원정보를 조회하시오.
Alias는 회원번호,회원명,주민번호,주소이며 주민번호의 출력은'XXXXXX-XXXXXXX'형태로,
주소는 기본 주소와 상세주소를 공백으로 연결하여 출력하시오.
SELECT MEM_ID AS 회원번호,
MEM_NAME AS 회원명,
CONCAT(CONCAT(MEM_REGNO1,'-'),MEM_REGNO2) AS 주민번호1,
CONCAT(MEM_REGNO1,CONCAT('-',MEM_REGNO2)) AS 주민번호2,
CONCAT(CONCAT(MEM_ADD1,' '),MEM_ADD2) AS 주소
FROM MEMBER
WHERE MEM_ADD1 LIKE '대전%'
3) CHR(n), ASCII(c) - ★★
. CHR : 주어진 n에 해당하는 ASCII 코드의 문자 출력
. ASCII : 주어진 문자열 c의 첫 글자에 대한 ASCII 코드값(정수) 출력
사용예)
SELECT CHR(90), ASCII('KOREA'), ASCII('대한민국'),CHR(ASCII('KOREA')),CHR(ASCII('대한민국'))
FROM DUAL;
4) LOWER(c), UPPER(c), INITCAP(c)
. LOWER : 주어진 문자열 c에 포함된 모든 문자를 소문자로 변환
. UPPER : 주어진 문자열 c에 포함된 모든 문자를 대문자로 변환
. INITCAP : 단어의 첫 글자만 대문자로 변환
SELECT FIRST_NAME,
LAST_NAME,
LOWER(EMP_NAME),
INITCAP(LOWER(EMP_NAME))
FROM HR.EMPLOYEES;
5) LPAD(c1, n [,c2]), RPAD(c1, n [,c2]) - ★★★
. LPAD : n크기의 기억공간에 C1문자열을 저장시키고 남는 왼쪽 기억공간에 c2문자열을 채움.
c2가 생략되면 공백이 확장되어 채워짐
수표보호문자로 사용되었음
. RPAD : n크기의 기억공간에 C1문자열을 저장시키고 남는 오른쪽 기억공간에 c2문자열을 채움.
c2가 생략되면 공백이 확장되어 채워짐
사용예) 상품테이블에서 다음 자료를 주어진 조건에 맞추어 출력하시오
Alias는 상품코드, 상품명, 매입가격
매입가격은 10자리에 출력하되 왼쪽 빈공간에 '*'를 확장하여 출력하고
상품명은 25자리크기에 공간에 오른쪽정렬 하여 출력하시오
SELECT PROD_ID AS 상품코드,
LPAD(TRIM(PROD_NAME),30) AS 상품명,
LPAD(PROD_COST,10,'*') AS 매입가격
FROM PROD;
6) LTRIM(c1 [,c2]), LTRIM(c1 [,c2]) - ★★★
. LTRIM : 주어진문자열 c1의 왼쪽 첫 글자부터 c2 문자열과 일치하면 해당 문자열을 삭제
. RTRIM : 주어진문자열 c1의 오른쪽 첫 글자부터 c2 문자열과 일치하면 해당 문자열을 삭제
. C2가 생략되면 공백이 제거됨
사용예) HR계정의 사원테이블에서 사원들의 이름(EMP_NAME)의 데이터 타입을 VARCHAR2(50)에서
CHAR(50)으로 변경하시오.
ALTER TABLE HR.EMPLOYEES MODIFY(EMP_NAME CHAR(50));
SELECT EMPLOYEE_ID, EMP_NAME
FROM HR.EMPLOYEES;
** 사원명 'Steven King' 사원의 사원번호,사원명,입사일,직무코드를 조회
SELECT EMPLOYEE_ID AS 사원번호,
RTRIM(EMP_NAME) AS 사원명,
HIRE_DATE AS 입사일,
JOB_ID AS 직무코드
FROM HR.EMPLOYEES
WHERE EMP_NAME='Steven King'
SELECT LTRIM('APAPAPPLE APESIMMON BANANA', 'AP'),-- LE APESIMMON BANANA
LTRIM('APAPAPPLE APESIMMON BANANA', 'APP')-- LE APESIMMON BANANA
FROM DUAL;
7) TRIM(c1) - ★★★
. 주어진 문자열 c1의 앞, 뒤에 존재하는 공백을 제거
. 문자열 내부의 공백은 제거하지 못함
사용예) HR계정의 사원테이블의 이름(EMP_NAME)컬럼의 자료형을 VARCHAR2(50)으로 복귀시키시오.
ALTER TABLE HR.EMPLOYEES MODIFY(EMP_NAME VARCHAR2(50));
UPDATE HR.EMPLOYEES
SET EMP_NAME=TRIM(EMP_NAME);
COMMIT;
SELECT EMPLOYEE_ID, EMP_NAME, LENGTHB(EMP_NAME)
FROM HR.EMPLOYEES;
8) SUBSTR(c, sidx [,cnt]) - ★★★★★
. 주어진 문자열 c에서 sidx위치부터 cnt갯수만큼의 부분 문자열을 추출하여 반환
. c의 문자수보다 큰 값의 cnt가 사용되거나 cnt가 생략되면 sidx이후 모든 문자열을 반환
. sidx가 음수이면 오른쪽부터 처리함
사용예)
SELECT SUBSTR('대전시 중구 계룡로 846',3,5) AS COL1,
SUBSTR('대전시 중구 계룡로 846',3) AS COL2,
SUBSTR('대전시 중구 계룡로 846',3,35) AS COL3,
SUBSTR('대전시 중구 계룡로 846',-10,5) AS COL4
FROM DUAL;
**표현식 : CASE WHEN THEN
- 자바의 다중분기와 비슷한 기능 제공
**표현식 : CASE WHEN THEN
(사용형식-1)
CASE WHEN 조건1 THEN
값1
WHEN 조건2 THEN
명령2
:
ELSE
명령n
END
(사용형식-2)
CASE 조건 WHEN 값1 THEN
명령1
WHEN 값2 THEN
명령2
:
ELSE
명령n
END . SELECT문의 SELECT절에서 사용
. '조건1'이 참이면 명령1을 반환하고 END다음 명령 수행
. '조건1'이 거짓이면 그 다음 조건들을 비교하며 모든 조건들이 거짓이면 ELSE
다음의 명령n을 반환
문제] 회원테이블에서 주민등록번호를 이용하여 나이를 계산하여 20대 회원만 조회하시오
회원번호,회원명,주민번호,나이,마일리지
SELECT MEM_ID AS 회원번호,
MEM_NAME AS 회원명,
MEM_REGNO1||'-'||MEM_REGNO2 AS 주민번호,
EXTRACT(YEAR FROM SYSDATE) -
(CASE WHEN SUBSTR(MEM_REGNO2,1,1) IN ('1','2') THEN
1900+TO_NUMBER(SUBSTR(MEM_REGNO1,1,2))
ELSE
2000+TO_NUMBER(SUBSTR(MEM_REGNO1,1,2))
END) AS 나이,
MEM_MILEAGE AS 마일리지
FROM MEMBER
WHERE (EXTRACT(YEAR FROM SYSDATE) -
(CASE WHEN SUBSTR(MEM_REGNO2,1,1) IN ('1','2') THEN
1900+TO_NUMBER(SUBSTR(MEM_REGNO1,1,2))
ELSE
2000+TO_NUMBER(SUBSTR(MEM_REGNO1,1,2))
END)) BETWEEN 20 AND 29;
9) REPLACE(c1, c2 [,c3]) - ★★★
. 주어진 문자열 c1에서 c2문자열을 찾아 c3 문자열로 대치
. c3가 생략되면 찾은 c3를 삭제함
. 문자열 내부 공백을 제거할 수 있음
사용예)
SELECT REPLACE('대우서비스센터','대우','APPLE'),
REPLACE('APPLE PERSSIOM','A','T'),
REPLACE('ORACLE MYSQL MSSOQ',' ')
FROM DUAL;
10) INSTR(c1, c2 [,m [,n]]) - ★★
. c1문자열에서 c2문자열이 처음 나온 위치(INDEX)를 반환
. m은 검색 시작 위치 지정 값
. n은 c2의 n번째 출현 위치를 반환 받을 때 사용
사용예)
SELECT INSTR('APPLEPERSSIBANANAOMORANGE','L'),
INSTR('APPLEPERSSIBANANAOMORANGE','L',3),
INSTR('APPLEPERSSIBANANAOMORANGE','A',10,2)
FROM DUAL;
11) LENGTH(c), LENGTHB(c) -★★★
. LENGTH : 주어진 문자열 c에 포함된 글자수 반환
. LENGTH : 주어진 문자열 c의 길이(BYTE 수) 반환
2. 숫자함수
1) 수학적 함수
- ABS, SIGN, SQRT, POWER 등 제공
. ABS(n) : n의 절대값 반환
. SIGN(n) : n의 부호에 따라 양수이면 1, 음수이면 크기에 관계없이 -1, 0이면 0을 반환
. SQRT(n) : n의 평반근 값 반환
. POWER(e,n) : e의 n 승값 반환
- 이 밖에도 수학적으로 사용되는 많은 함수가 존재(EXP, LOG 등등)
2) GREATEST(n1, n2,...), LEAST(n1, n2,...)
- GREATEST : 주어진 숫자자료 n1, n2,... 중 가장 큰 값 반환
- LEAST : 주어진 숫자자료 n1, n2,... 중 가장 작은 값 반환
- MAX, MIN 함수와의 차이점은 GREATEST와 LEAST는 한행에서 여러 열 값 중 최대 최소값을
반환하는 함수이고, MAX, MIN 함수는 하나의 열 내부에서 최대 최소값을 반환하는 함수
(ex 50명의 국어, 영어, 수학, 과학 시험점수가 저장된 테이블에서
각 학생의 4과목 중 최고/최저 점수 과목 검색은 GREATEST와 LEAST
각 과목의 최고점 또는 최저점을 기록한 학생의 검색은 MAX, MIN 함수
시용예)
SELECT GREATEST(120,50,70), LEAST(120,50,70),
GREATEST('A','홍길동',256), LEAST('송아지', '망아지', '강아지'),
GREATEST('홍길동','홍길순','홍길남'), LEAST('홍길동','홍길순','홍길남')
FROM DUAL;
사용예) 회원테이블에서 마일리지가 1000미만인 회원의 마일리지를 1000으로 부여하여 조회하시오
1000이상인 회원은 그대로 출력
Alias는 회원번호,회원명,원본마일리지,변환마일리지
SELECT MEM_ID AS 회원번호,
MEM_NAME AS 회원명,
MEM_MILEAGE AS 원본마일리지,
-- 두 값을 비교해서 더 큰 값을 출력
GREATEST(MEM_MILEAGE, 1000) AS 변환마일리지
FROM MEMBER;
3) ROUND(n [,loc]), TRUNC(n [,loc]) - ★★★★
- 제시된 수 n에서 소숫점 이하 loc+1번째 자리에서 반올림(ROUND) 또는
자리버림(TRUNC)하여 loc자리까지 반환
- loc가 생략되면 0으로 간주됨
- loc가 음수이면 양수쪽 loc 위치에서 반올림 또는 자리버림
사용예)
SELECT ROUND(12345.6789,2),
TRUNC(12345.6789,2),
ROUND(12345.6789),
TRUNC(12345.6789),
ROUND(12345.6789,-2),
TRUNC(12345.6789,-2)
FROM DUAL;
사용예) 회원테이블에서 연령대별 평균마일리지,회원수를 구하시오.
SELECT TRUNC(EXTRACT(YEAR FROM SYSDATE)-EXTRACT(YEAR FROM MEM_BIR),-1) AS 연령대,
AVG(MEM_MILEAGE) AS 평균마일리지,
COUNT(*) AS 회원수
FROM MEMBER
GROUP BY TRUNC(EXTRACT(YEAR FROM SYSDATE)-EXTRACT(YEAR FROM MEM_BIR),-1)
ORDER BY 1;
4) FLOOR(n), CEIL(n) - ★★
- FLOOR : n과 같거나 작은쪽에서 가장 큰 정수(왼쪽에서 가장 가까운 정수)
- CEIL(n) : n과 같거나 큰쪽에서 가장 작은 정수(오른쪽에서 가장 가까운 정수)
- 금액과 관련된 계산에 주로 사용
사용예)
SELECT FLOOR(20), FLOOR(20.567), FLOOR(-20.257),
CEIL(20), CEIL(20.567), CEIL(-20.257)
FROM DUAL;
5) MOD(n,c) - ★★★, REMAINDER(n,c) - ★
- 나머지를 반환
- 내부에 적용하는 함수가 MOD와 REMAINDER는 다름
ex) a를 b로 나눈 나머지
MOD : a - b * FLOOR(a/b)
REMAINDER : a - b * ROUND(a/b)
ex)
MOD(27, 4) : 27 - 4 * FLOOR(27/4)
27 - 4 * FLOOR(6.75)
27 - 4 * 6
27 - 24
1(나머지)
REMAINDER(27, 5) : 27 - 4 * ROUND(27/4)
27 - 4 * ROUND(6.75)
27 - 4 * 7
-1(나머지)
MOD(27, 5) : 27 - 5 * FLOOR(27/5)
27 - 5 * FLOOR(6.75)
27 - 5 * 5
27 - 24
2(나머지)
REMAINDER(27, 5) : 27 - 5 * ROUND(27/5)
27 - 5 * ROUND(5.4)
27 - 5 * 5
2(나머지)
사용예) 키보드로 년도를 입력받아 그 해가 윤년인지 평년인지 판별하시오
윤년 : 4의 베수이면서 100의 배수가 아닌 해이거나 400의 배수가 되는 해
ACCEPT P_YEAR PROMPT '년도입력(4자리수) : '
DECLARE
L_YEAR NUMBER := '&P_YEAR';
L_RES VARCHAR2(200);
BEGIN
IF (MOD(L_YEAR,4)=0 AND MOD(L_YEAR,100)!=0) OR (MOD(L_YEAR,400)=0) THEN
L_RES:=L_YEAR||'년은 윤년입니다';
ELSE
L_RES:=L_YEAR||'년은 평년입니다';
END IF;
DBMS_OUTPUT.PUT_LINE(L_RES);
END;
6) WIDTH_BUCKET(n, min_val, max_val, b)
- min_val에서 max_val 까지를 b개의 구간으로 나눌때 n이 어느 구간에 속하는지 속한 구간의 인덱스를 반환
- 구간의 상한 값은 포함되지 않음 (n이 포함된 구간값은 max_val<= n < max_val)
- 사용하는 구간의 수는 b+2개임 (하한값 보다 작은 구간, b개의 구간, 상한값보다 크거나 같은 구간)
사용예) 1000-8000를 3개의 구간으로 구분하고 회원들의 마일리지가 어느 구간에 속하는지 나타내시오.
-- 3개로 나누었지만 0(하한값보다 작은 값), 1(1000), 2, 3, 4(8000, 상한값보다 큰 값) 구간이 생김
SELECT MEM_ID AS 회원번호,
MEM_NAME AS 회원명,
MEM_MILEAGE AS 마일리지,
WIDTH_BUCKET(MEM_MILEAGE,1000,8000,3) AS 구간값
FROM MEMBER;
-- 역순으로 출력, 고정값에서 변동값을 빼줌
SELECT MEM_ID AS 회원번호,
MEM_NAME AS 회원명,
MEM_MILEAGE AS 마일리지,
5 - WIDTH_BUCKET(MEM_MILEAGE,1000,8000,3) AS 구간값
FROM MEMBER;
SELECT MEM_ID AS 회원번호,
MEM_NAME AS 회원명,
MEM_MILEAGE AS 마일리지,
5- WIDTH_BUCKET(MEM_MILEAGE,1000,8000,3) AS 구간값,
CASE WHEN 5 - WIDTH_BUCKET(MEM_MILEAGE,1000,8000,3) IN(1,2) THEN
'우수회원'
WHEN 5 - WIDTH_BUCKET(MEM_MILEAGE,1000,8000,3) IN(3,4) THEN
'보통회원'
ELSE
'초심회원'
END AS 비고
FROM MEMBER;
3. 날짜 함수
1) SYSDATE - ★★★★, SYSTIMESTAMP - ★★★
- 현재의 날짜와 시간을 DATE 와 TIMESTAMP 타입으로 반환
사용예)
SELECT SYSDATE, SYSTIMESTAMP FROM DUAL;
SELECT SYSDATE+3, SYSDATE-19 FROM DUAL;
2) ADD_MONTHS(d, n) - ★★
- 주어진 날짜 d에 n개월을 더한 날짜를 반환
- 반환되는 데이터 타입은 날짜타입
사용예) HR 계정의 사원테이블에서 입사일을 10년을 더한 날짜로 변경하시오.
-- 현재 내용 보관
CREATE TABLE T_EMP AS
SELECT * FROM HR.EMPLOYEES;
-- 사원테이블 내용 변경
UPDATE HR.EMPLOYEES
SET HIRE_DATE = ADD_MONTHS(HIRE_DATE,120) ;
COMMIT;
SELECT EMP_NAME, HIRE_DATE
FROM HR.EMPLOYEES
3) MONTHS_BETWEEN(d1, d2) - ★★
- 두 날짜 자료 d1과 d2 사이의 개월수를 반환
- d1 > d2 인 경우 양수의 개월수 반환
- d1 < d2 인 경우 음수의 개월수 반환
사용예)
SELECT MONTHS_BETWEEN(SYSDATE, TO_DATE('20010415')),
ROUND(MONTHS_BETWEEN(SYSDATE, TO_DATE('20230101'))) --소숫점 제거
FROM DUAL;
사용예) 사원테이블에서 입사일을 활용하여 각 사원의 근속년수를 정확하게 XX년 XX월
형식으로 조회하시오
Alias는 사원번호,사원명,입사일,근속기간
SELECT EMPLOYEE_ID AS 사원번호,
EMP_NAME AS 사원명,
HIRE_DATE AS 입사일,
ROUND(MONTHS_BETWEEN(SYSDATE, HIRE_DATE)) AS 개월수,
-- 년도 && 개월수
-- TRUND 반올림 하지 않고 개월수 버림(현재 날짜,입사일)/12 = 년
-- MOD(ROUND 반올림(현재 날짜,입사일) = 개월수),12) = 개월수
TRUNC(ROUND(MONTHS_BETWEEN(SYSDATE, HIRE_DATE)/12))||'년'||
MOD(ROUND(MONTHS_BETWEEN(SYSDATE, HIRE_DATE)),12)||'개월' AS 근속기간
FROM HR.EMPLOYEES
4) NEXT_DAY(d, 'fmt') - ★
- 기준일 d일 이후 처음 만나는 'fmt'요일에 해당하는 날짜 반환
- 'fmt';는 날짜표현언어에 따라 달라짐
. 날짜표현언어가 영어인 경우 : SUNDAY, SUN..,
. 날짜표현언어가 한글인 경우 : 일요일,일,월,화요일
. 요일의 INDEX값(1:일요일, 2:월요일,..7:토요일)
사용예)
SELECT NEXT_DAY(SYSDATE, '수요일'),
NEXT_DAY(SYSDATE,4)
FROM DUAL;
5) LAST_DAY(d) - ★★★
- 주어진 날자 d에 표현된 월의 마지막 일자를 반환
- 주로 임의년도의 2월의 마지막일이 필요한 경우 또는 임의의 월을 입력받아 해당월의 마지막일을 구할 때 사용
사용예) 매입테이블에서 2020년 2월 제품별 매입수량과 매입금액 합계를 조회하시오.
SELECT BUY_PROD AS 상품코드,
SUM(BUY_QTY) AS 매입수량,
SUM(BUY_QTY*BUY_COST) AS 매입금액
FROM BUYPROD
WHERE BUY_DATE BETWEEN TO_DATE('20200201') AND LAST_DAY(TO_DATE('20200201'))
GROUP BY BUY_PROD;
사용예) 키보드로 년도와 월을 6자리 문자로 입력 받아 해당 기간동안 발생된 매입건수를 출력하시오.
ACCEPT P_PERIOD PROMPT '기간 입력(YYYYMM) : '
DECLARE
L_SDATE DATE := TO_DATE('&P_PERIOD'||'01');
L_EDATE DATE := LAST_DAY(L_SDATE);
L_COUNT NUMBER := 0;
BEGIN
SELECT COUNT(*) INTO L_COUNT
FROM BUYPROD
WHERE BUY_DATE BETWEEN L_SDATE AND L_EDATE;
DBMS_OUTPUT.PUT_LINE(SUBSTR('&P_PERIOD',1,4)||' 년 '||SUBSTR('&P_PERIOD',5)||
'월 매입 건수 : '||L_COUNT);
END;
4. 변환 함수
- 데이터 타입을 변환하는 함수
- TO_CHAR, TO_NUMBER, TO_DATE, CAST 등이 제공됨
1) CAST(expr AS 타입명)
- 'expr'에 저장된 데이터를 AS 다음에 기술한 '타입'으로 변환
사용예)
SELECT BUY_DATE AS COL1,
CAST(BUY_DATE AS CHAR(20)) AS COL2
FROM BUYPROD
WHERE BUY_DATE BETWEEN TO_DATE('20200101') AND TO_DATE('20200115');
사용예)
SELECT CAST(1234.56 AS VARCHAR2(7)) FROM DUAL;
-- 소수점이 있는 수를 문자로 바꿀 때 크기를 지정해야함 숫자 + .(소수점)의 개수
2) TO_CHAR(data [, 'fmt']) - ★★★★★
- 주어진 데이터('data')를 형식지정문자열('fmt')에 맞추어 문자로 변환
- 'data'로 사용될 수 있는 자료의 타입은
. 문자열(CHAR, CLOB) >> VARCHAR2로 변환
. 숫자타입=>문자열 타입으로 변환
. 날짜타입=>문자열 타입으로 변환
- 날짜 형식지정 문자열
--1) FORMAT : BC, AD / 설명 : 서기(기원전)
SELECT TO_CHAR(SYSDATE, 'BC') FROM DUAL;
--2) FORMAT : CC / 설명 : 세기
SELECT TO_CHAR(SYSDATE, 'BC CC') FROM DUAL;
--3) FORMAT : YYYY,YYY,YY,Y / 설명 : 년도
SELECT TO_CHAR(SYSDATE, 'YYY YYY YY Y') FROM DUAL;
--4) FORMAT : RM,MM,MONTH,MON / 설명 : 월
SELECT TO_CHAR(SYSDATE,'YYYY-MM'),
TO_CHAR(SYSDATE,'YYYY-RM'),
TO_CHAR(SYSDATE,'YYYY-MONTH'),
TO_CHAR(SYSDATE,'YYYY-MON')
FROM DUAL;
--5) FORMAT : RM,MM,MONTH,MON / 설명 : 월
--6) FORMAT : DD,DDD,D / 설명 : 일
--7) FORMAT : DAY,DY / 설명 : 요일
--8) FORMAT : WW, W / 설명 : 주
SELECT TO_CHAR(SYSDATE,'YYYY-MM-D DAY'),
TO_CHAR(SYSDATE,'YYYY-MM-DD DY'),
TO_CHAR(SYSDATE,'YYYY-MM-DDD W'),
TO_CHAR(SYSDATE,'YYYY-MM-DDD WW')
FROM DUAL;
--9) FORMAT : AM,PM,A.M.,P.M. / 설명 : 오전/오후
SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD DAY AM'),
TO_CHAR(SYSDATE,'YYYY-MM-DD DY PM')
FROM DUAL;
--10) FORMAT : HH.HH12,HH24 / 설명 : 시간
SELECT TO_CHAR(TO_DATE('20210213133127','YYYYMMDDHH24MISS'),'YYYY/MM/DD HH:MI:SS:SSSSS')
FROM DUAL;
--11) FORMAT : MI / 설명 : 분
--12) FORMAT : SS,SSSSS /설명 : 초
--13) FORMAT : "문자열" / 설명 : 사용자 지정 문자열
SELECT TO_CHAR(SYSDATE, 'YYYY"년" MM"월" DD"일"') FROM DUAL;
- 숫자 형식지정 문자열
--1) FORMAT : 9 / 설명 : 출력자릿수 결정 밒 유효숫자와 대응하여 유효슛자 출력. 무효의 0은 공백처리
--2) FORMAT : 0 / 설명 : 출력자릿수 결정 밒 유효숫자와 대응하여 유효슛자 출력. 무효의 0은 '0'을 출력
SELECT TO_CHAR(12345.789,'99,999.9'),
TO_CHAR(12345.789,'999,999.99'),
TO_CHAR(123.789,'999,999'),
TO_CHAR(123.789,'000,000.0'),
TO_CHAR(12345.789,'000,000.09'),
TO_CHAR(12345.789,'000,000')
FROM DUAL;
--3) FORMAT : $, L / 설명 : 화폐기호를 숫자 왼쪽에 출력
SELECT TO_CHAR(1234567,'$9,999,999'),
TO_CHAR(1234567,'L9,999,999')
FROM DUAL;
--4) FORMAT : MI / 설명 : '-'부호를 숫자 오른쪽에 출력
SELECT TO_CHAR(-1234567,'$9,999,999MI'),
TO_CHAR(-1234567,'L9,999,999MI')
FROM DUAL;
--5) FORMAT : PR / 설명 : 음수 자료는 '< >'안에 출력
SELECT TO_CHAR(-1234567,'$9,999,999PR'),
TO_CHAR(-1234567,'L9,999,999PR')
FROM DUAL;
--6) FORMAT : ,(comma) / 설명 : 3자리 마다 자리점 출력, 무효의 ','는 공백출력(9 mode)
--7) FORMAT : .(dot) / 설명 : 소숫점 출력
3) TO_DATE(expr [, 'fmt']) - ★★★★★★
- 날짜로 변환 가능한 'expr'(문자열 또는 숫자)를 날짜 타입으로 변환
- 날짜로 변환할 수 없는 문자열이 포함된 경우 해당 문자열이 출력되기 위하여 필요한 형식 지정 기술해야 함
- 'fmt'는 TO_CHAR에 사용되는 날짜 형식 지정 문자열과 동일
사용예)
SELECT TO_DATE('20221217'),
TO_DATE(20221217),
TO_DATE('2022-12-17 14:27:45','YYYY-MM-DD HH24:MI:SS'),
TO_DATE('2022.12.17', 'YYYY.MM.DD')
FROM DUAL;
사용예) 장바구니 테이블에서 2020년 6월 1일부터 6월 15일까지 발생된 매출자료를 조회하시오.
Alias는 일자,상품코드,판매수량이다.
SELECT TO_DATE(SUBSTR(CART_NO,1,8)) AS 일자,
CART_PROD AS 상품코드,
CART_QTY AS 판매수량
FROM CART
WHERE SUBSTR(CART_NO,1,8) BETWEEN '20200601' AND '20200615';
4) TO_NUMBER(expr [, 'fmt']) - ★★★
- 숫자로 변환 가능한 'expr'(문자열)을 숫자타입으로 변환
- 숫자로 변환 할 수 없는 문자열이 포함된 경우 해당 문자열이 출력되기 위하여 필요한 형식 지정 문자열을 기술해야 함
- 'fmt'는 TO_CHAR에 사용되는 숫자형식 지정 문자열과 동일
사용예)
SELECT TO_NUMBER('12345'),
TO_NUMBER('12345.9'),
TO_NUMBER('12,345','99,999'),
TO_NUMBER('₩12,345','L00,000'),
TO_NUMBER('<12345>','99999PR')
FROM DUAL;사용예) 오늘이 2020년 7월 1일이라고 가정하고 새로운 장바구니 번호를 생성하는 SQL을 작성하시오.
SELECT TO_CHAR(SYSDATE,'YYYYMMDD')||
TRIM(TO_CHAR(TO_NUMBER(SUBSTR(MAX(CART_NO),9))+1,'00000')),
MAX(CART_NO)+1
FROM CART
WHERE CART_NO LIKE '20200701%';
5. NULL처리 함수
- 오라클의 기본 초기화 값은 기억공간의 데이터 타입에 관계 없이 NULL임
- NULL타입의 자료와 어떤 타입의 자료가 연산(사칙,비교)되면 결과가 NULL임
- NULL 처리를 위한 함수와 연산자 : IS [NOT] NULL, NVL, NVL2, NULLIF등이 제공
1) IS [NOT] NULL
. 특정 컬럼이나 식의 값이 NULL인지 판별하여 true or false를 반환
. 동등연산자 '='으로는 NULL을 반별할 수 없음
사용예) 사용테이블에서 색상정보(PROD_COLOR)가 없는 상품의 상품번호,상품명,크기,색상을 조회하시오.
SELECT PROD_ID AS 상품번호,
PROD_NAME AS 상품명,
PROD_SIZE AS 크기,
PROD_COLOR AS 색상
FROM PROD
WHERE PROD_COLOR IS NOT NULL;
2) NVL(expr,val)
- 'expr' 값이 NULL이면 'val'을 출력하고, NULL이 아니면 expr값을 출력
- 'expr'과 'val'은 같은 데이터 타입이어야 함
사용예) 사원테이블에서 영업실적이 NULL이면 '영업실적없음'을, 영업실적이 있으면 영업실적을 비고난에 출력하시오.
Alias는 사원번호,사원명,부서코드,직무코드,비고
SELECT EMPLOYEE_ID AS 사원번호,
EMP_NAME AS 사원명,
DEPARTMENT_ID AS 부서코드,
JOB_ID AS 직무코드,
-- COMMISSION_PCT : 숫자, '영업실적없음' : 문자열
NVL(TO_CHAR(COMMISSION_PCT,'0.00'),'영업실적없음') AS 비고
FROM HR.EMPLOYEES;
**매입테이블에서 매입된 상품의 분류코드의 종류 및 매입수량합계
SELECT LPROD_GU AS 분류코드,
SUM(A.BUY_QTY) AS 매입수량합계
FROM BUYPROD A, LPROD B, PROD C
WHERE A.BUY_PROD=C.PROD_ID
AND C.PROD_LGU=B.LPROD_GU
GROUP BY B.LPROD_GU
ORDER BY 1;
사용예) 모든 분류코드별 평균매입가를 구하시오.
SELECT B.LPROD_GU AS 분류코드,
NVL(ROUND(AVG(A.PROD_COST)),0) AS 평균매입가
FROM PROD A, LPROD B
WHERE A.PROD_LGU(+)=B.LPROD_GU
GROUP BY B.LPROD_GU
ORDER BY 1;
** 상품테이블에 사용하는 분류코드의 종류
SELECT DISTINCT PROD_LGU
FROM PROD;
3) NVL2(expr,val1,val2)
- 'expr'값이 NULL 이면 val2를 반환라고, NULL이 아니면 val1을 반환함
- val1과 val2는 반드시 같은 데이터 타입이어야 함
- NVL2는 NVL을 포함할 수 있음
** 상품테이블에서 분류코드가 P301에 속한 상품들의 판매가를 매입가로 변경하시오.
UPDATE PROD
SET PROD_PRICE=PROD_COST
WHERE UPPER(PROD_LGU)='P301';
--ROLLBACK;
--COMMIT;사용예) 상품테이블에서 상품의 크기(PROD_SIZE)정보를 조회하여 크기정보가 없으면
'크기 정보 없음'을 크기정보가 있으면 '크기 : '문자열과 크기정보를 비고난에 출력하시오
Alias는 상품코드,상품명,크기,비고
SELECT PROD_ID AS 상품코드,
PROD_NAME AS 상품명,
NVL2(PROD_SIZE, '크기: '||PROD_SIZE,'SIZE 정보 없음') AS 비고
FROM PROD;
사용예) 사원테이블에서 영업실적(COMMISSION_PCT)을 조회하여 영업실적이 없으면 비고에 '실적없음'을 출력하고
영업실적이 있으면 해당 부서코드(DEPARTMENT_ID)를 출력하시오.
Alias는 사원번호,사원명,입사일,비고
SELECT EMPLOYEE_ID AS 사원번호,
EMP_NAME AS 사원명,
HIRE_DATE AS 입사일,
-- 영없실적에 데이터가 있으면 -> DEPARTMENT_ID && '실적없음'과 비교
NVL2(COMMISSION_PCT, TO_CHAR(DEPARTMENT_ID),'실적없음') AS 비고1,
NVL2(TO_CHAR(COMMISSION_PCT,'999999'), TO_CHAR(DEPARTMENT_ID),'실적없음') AS 비고2
FROM HR.EMPLOYEES
4) NULLIF(col1, col2)
- col1과 col2를 비교하여 같은 값이면 NULL을 반환하고 서로 다른 값이면 col1을 반환함
사용예) 상품테이블에서 매출가와 매입가가 동일하면 수입난에 '단종예정상품'을 서로 다르면
판매가에서 매입가를 뺀 이익금을 출력하시오
Alias는 상품코드,상품명,수익
SELECT PROD_ID AS 상품코드,
PROD_NAME AS 상품명,
NVL2(NULLIF(PROD_COST,PROD_PRICE),TO_CHAR(PROD_PRICE-PROD_COST,'9,999,999'),'단종예정상품') AS 수익1,
CASE WHEN NULLIF(PROD_COST,PROD_PRICE) IS NULL THEN
'단종예정상품'
ELSE
TO_CHAR(PROD_PRICE-NULLIF(PROD_COST,PROD_PRICE),'9,999,999')
END AS 수익2
FROM PROD;
'DBMS' 카테고리의 다른 글
| [DBMS/Oracle] ROLLUP과 CUBE함수 (0) | 2023.03.15 |
|---|---|
| [DBMS/Oracle] 그룹함수 (0) | 2023.03.15 |
| [DBMS/Oracle] 연산자 (0) | 2023.03.15 |
| [DBMS/Oracle] 데이터 검색 명령(SELECT) (0) | 2023.03.14 |
| [DBMS/Oracle] 데이터 타입 (0) | 2023.03.14 |