DB Oracle
[Oracle] 함수
아잠만_
2024. 3. 13. 17:30
함수 (FUNCTION)
1. 문자열 함수
CONCAT (c1, c2)
- 주어진 문자열 c1과 c2를 결합하여 새로운 문자열 반환 (2개만 가능)
- 결합 연산자 '||'로 대치가능
회원테이블에서 주민등록번호의 표현을 'XXXXXX-XXXXXXX'으로 출력하시오
Alias는 회원번호, 회원명, 주민등록번호
SELECT MEM_ID AS 회원번호,
MEM_NAME AS 회원명,
CONCAT(CONCAT(MEM_REGNO1,'-'),MEM_REGNO2) AS 주민등록번호
-- MEM_REGNO1||'-'||MEM_REGNO2 AS 주민등록번호
FROM MEMBER;거래처테이블(BUYER)에서 거래처 정보를 조회하시오.
단 주소는 기본주소와 상세주소 사이에 공백 2개를 삽입하여 출력하시오
Alias는 거래처번호, 거래처명, 주소, 담당자
SELECT BUYER_COMTEL AS 거래처번호,
CONCAT(CONCAT(BUYER_ADD1,' '),BUYER_ADD2) AS 주소,
-- BUYER_ADD1 || ' ' || BUYER_ADD2 AS 주소,
BUYER_BANKNAME AS 담당자
FROM BUYER;LOWER(c1) , UPPER(c1) , INITCAP(c1)
- 주어진 문자열 c1의 철자를 대문자(UPPER), 소문자(LOWER), 첫 글자만 대문자(INITCAP)로 바꾸는 함수
LPAD(c1, n [,c2]) , RPAD(c1, n [,c2])
* LPAD(c1, n [ ,c2] )
- 주어진 문자열 c1을 n 자리만큼 확보된 기억공간에서 오른쪽부터 저장하고 남는 왼쪽(LPAD) 문자열 'c2'를 모두 채움
- 문자열 'c2'가 생략되면 공백으로 채움
* RPAD(c1, n [ ,c2] )
- 주어진 문자열 c1을 n 자리만큼 확보된 기억공간에서 왼쪽부터 저장하고 남는 오른쪽(RPAD) 문자열 'c2'를 모두 채움
- 문자열 'c2'가 생략되면 공백으로 채움
LTRIM(c1 [,c2]), RTRIM(c1 [,c2])
- 주어진 문자열 c1에서 우측 (LTRIM) 또는 좌측(RTRIM)에서 c2를 찾아 제거함
- c2가 생략되면 공백을 제거함
- 문자열 내부의 공백은 제거하지 못함
TRIM(c1)
c1 문자열의 왼쪽과 오른쪽에 존재하는 무효의 공백을 제거함
★ SUBSTR(c1, m [,n])
- 주어진 문자열 c1에서 m(시작열)번째 문자부터 n(갯수)개의 문자를 추출함
- n이 생략되거나 기술한 문자의 갯수보다 큰 값이면 m부터 나머지 모든 문자가 추출
- n이 음수이면 오른쪽부터 기산하여 처리함
REPLACE(c1, c2 [,c3])
- 문자나 문자열을 치환하기 위한 함수
- c1 문자열에서 c2를 찾아 c3로 치환
- c3가 생략되면 찾은 c2를 제거함(c2가 공백이면 공백을 제거)
- 문자열 안에 유효공백을 없애는데에 사용
INSTR(c1,c2[,n ,m])
- c1 문자열에서 c2를 찾아 값을 반환
- n번째 부터 찾을 수 있다 (1은 앞에서부터 -1은 뒤에서부터)
- 해당 문자를 한번 찾으면 더이상 검색하지 않는다
- 그렇기 때문에 뒤에 같은 문자열을 검색하기 위해서 m번째로 지정해줘야 함.
상품테이블에서 'p301'분류에 속한 상품정보를 조회하시오
Alias는 상품코드, 상품명, 분류코드, 매출단가
SELECT PROD_ID AS 상품코드,
PROD_NAME AS 상품명,
PROD_LGU AS 분류코드,
PROD_PRICE AS 매출단가
FROM PROD
WHERE LOWER(PROD_LGU) = 'p301';키보드로 회원번호를 입력 받아 해당 회원정보를 조회
Alias는 회원번호, 회원명, 주소,마일리지
ACCEPT P_PID PROMPT '회원번호 : '
DECLARE
L_NAME MEMBER.MEM_NAME%TYPE;
L_ADDR VARCHAR2 (200);
L_MILEAGE NUMBER:=0;
BEGIN
SELECT MEM_NAME, MEM_ADD1 || ' ' || MEM_ADD2, MEM_MILEAGE
INTO L_NAME, L_ADDR, L_MILEAGE
FROM MEMBER
WHERE MEM_ID = LOWER(RTRIM('&P_PID'));
DBMS_OUTPUT.PUT_LINE('--------------------------------------');
DBMS_OUTPUT.PUT_LINE('회원번호 : ' || '&P_PID');
DBMS_OUTPUT.PUT_LINE('회원명 : ' || L_NAME);
DBMS_OUTPUT.PUT_LINE('주소 : ' || L_ADDR);
DBMS_OUTPUT.PUT_LINE('마일리지 : ' || L_MILEAGE);
DBMS_OUTPUT.PUT_LINE('--------------------------------------');
END;상품테이블에서 분류코드 'P102'에 속한 상품 정보를 조회하시오
Alias는 상품코드, 상품명, 매입단가, 매출단가이며
매입/매출 단가는 10자리(byte)/15자리에 오른쪽 정렬 후 왼쪽에 '*'문자열을 삽입하시오
SELECT PROD_ID AS 상품코드,
PROD_NAME AS 상품명,
LPAD(PROD_COST,10,'*') AS 매입단가,
LPAD(LTRIM(TO_CHAR(PROD_PRICE,'9,999,999')),15,'*') AS 매출단가
FROM PROD
WHERE UPPER (PROD_LGU) = 'P102';CHAR로 FORMAT의 형태를 자릿수에 넣음
CHAR형태는 빈공간도 포함하기 때문에 공백을 지우므로써 위와 같은 값을 산출
회원테이블에서 충남에 거주하는 회원정보를 조회하시오
Alias는 회원번호, 회원명, 주민등록번호, 주소이며
두번째 주민등록번호 중 첫 자리를 제외한 나머지 6글자는 모두 '*'로 출력하시오.
SELECT MEM_ID AS 회원번호,
MEM_NAME AS 회원명,
MEM_REGNO1 || '-' || SUBSTR(MEM_REGNO2,1,1) || '******' AS 주민등록번호,
MEM_ADD1 || ' ' || MEM_ADD2 AS 주소
FROM MEMBER
WHERE SUBSTR(MEM_ADD1,1,2) = '충남';
--WHERE MEM_ADD1 LIKE '충남%';오늘이 2020년 5월 7일 이라고 간주하고 장바구니테이블에 사용할 장바구니 번호를 생성하시오.
SELECT TO_CHAR (SYSDATE,'YYYYMMDD') ||
TRIM(TO_CHAR (TO_NUMBER (SUBSTR(MAX(CART_NO),9))+1,'00000'))
FROM CART
WHERE SUBSTR(CART_NO,1,8) = TO_CHAR(SYSDATE,'YYYYMMDD');SELECT MAX (CART_NO)+1
FROM CART
WHERE SUBSTR(CART_NO,1,8) = TO_CHAR(SYSDATE,'YYYYMMDD');상품테이블에서 상품명 중 '대우'를 찾아 'DW'로 치환하고(변경상품명1),
또 상품명에 포함된 모든 공백을 삭제(변경상품명2)하여 출력하시오.
Alias는 상품코드, 상품명, 변경상품명1, 변경상품명2
SELECT PROD_ID AS 상품코드,
PROD_NAME AS 상품명,
REPLACE(PROD_NAME,'대우','DW') AS 변경상품명1,
REPLACE(PROD_NAME,' ') AS 변경상품명2
FROM PROD2. 숫자함수
수학적함수
ABS(n1)
- 주어진 숫자자료 n1의 절대값 반환
SIGN(n1)
- 주어진 숫자자료 n1의 음수이면 -1, 0이면 0, 양수이면 1을 반환
POWER(n1, n2)
- n1을 n2번 거듭 곱한 결과(n1의 n2승)
SQRT(n1)
- n1의 평방근(루트) 반환
SELECT ABS(-10), ABS(200),
SIGN(-100000), SIGN(0.00001), SIGN(0),
POWER(2,10), POWER(10,10),
SQRT(3.3)
FROM DUAL;
GREATEST(n1,n2, ... , n), LEAST(n1,n2, ... , n)
- 주어진 데이터 n1 ~ n 값중 가장 큰 값(GREATEST) 또는 가장 작은 값(LEAST)으로 반환
- GREATEST와 LEAST는 한 행에서 큰 값 | 작은 값을 구할 때 사용하고
MAX(컬럼명), MIN(컬럼명)은 한 컬럼에서 최대(최소) 값을 구할 때 사용한다 - 문자열도 적용할 수 있음
SELECT GREATEST (100, 50, 900),
ASCII('홍'),
GREATEST ('홍길동', 23000, '대한민국')
FROM DUAL;SELECT LEAST(PROD_COST, PROD_PRICE, PROD_SALE)
FROM PROD;회원테이블에서 회원들의 마일리지를 조회하여 1000미만인 회원의 마일리지를 1000으로,
1000이상인 회원마일리지는 해당회원의 마일리지는 그대로 출력하시오.
Alias는 회원번호, 회원명, 보유마일리지, 변경마일리지
SELECT MEM_ID AS 회원번호,
MEM_NAME AS 회원명,
MEM_MILEAGE AS 보유마일리지,
GREATEST(MEM_MILEAGE,'1000') AS 변경마일리지
FROM MEMBER;ROUND(n1, loc), TRUNC(n1, loc)
- 주어진 수 n1에서 소숫점 이하 loc+1번째 자리에서 반올림(ROUND) 또는 자리버림(TRUNC)을 하여 loc자리까지 반환
- loc가 생략되면 0으로 간주되어 소숫점 이하를 잘라버림
- loc가 음수이면 정수부분의 loc자리에서 반올림(ROUND)/자리버림(TRUNC)을 수행함
HR계정의 사원테이블에서 각 부서별 평균 임금을 구하시오
Alias는 부서번호, 부서명, 평균임금
평균임금은 소숫점 1자리까지 출력
SELECT A.DEPARTMENT_ID AS 부서번호,
B.DEPARTMENT_NAME AS 부서명,
ROUND(AVG(A.SALARY),1) AS 평균임금
FROM HR.EMPLOYEES A, HR.DEPARTMENTS B -- JOIN
WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID
GROUP BY A.DEPARTMENT_ID, B.DEPARTMENT_NAME
ORDER BY 1;GROUP BY는 같은 값을 가진 행끼리 하나의 그룹으로 뭉쳐줌
FLOOR(n1), CEIL(n1)
FLOOR (바닥)
- 주어진 수 n1과 같거나(n1이 정수) n1보다 작은 가장 큰 정수
CEIL (천장)
- 주어진 수 n1과 같거나(n1이 정수) n1보다 큰 가장 작은 정수
123 ←→ 123.4567 ←→ 124
(FLOOR) (CEIL)
SELECT FLOOR(123.5678), FLOOR(23), FLOOR(-123.4567),
CEIL(123.5678), CEIL(23), CEIL(-123.4567)
FROM DUAL;
MOD(n1, n2)
- 주어진 수 n1을 n2로 나눔 나머지 반환
- 자바의 '%'연산자와 동일
- 내부 연산방식 : MOD(n1,n2)=n1 - n2 * FLOOR(n1/n2)
SELECT MOD(21,5), MOD(21,8) FROM DUAL;
키보드로 년도를 입력받아 윤년과 평년을 구별하시오
윤년 : ( (4의 배수이면서) (100의 배수가 아니거나)) 또는 (400의 배수)가 되는 해
ACCEPT P_YEAR PROMPT '년도입력 : '
DECLARE
L_YEAR NUMBER := TO_NUMBER('&P_YEAR');
-- 크기를 지정하지 않으면 시스템이 알아서 지정 > NUMBER(4)
L_RESULT VARCHAR2(500);
L_FLAG BOOLEAN := FALSE;
BEGIN
L_FLAG:=(MOD(L_YEAR,4)=0 AND MOD(L_YEAR,100)!=0 ) OR MOD(L_YEAR,400)=0;
IF L_FLAG=FALSE THEN
L_RESULT:=L_YEAR||'년은 평년입니다.';
ELSE
L_RESULT:=L_YEAR||'년은 윤년입니다.';
END IF;
DBMS_OUTPUT.PUT_LINE(L_RESULT);
END;IF구문을 쓸때 꼭 FALSE일때 조건을 세울필요 없이 첨엔 TRUE값 ELSE뒤엔 FALSE값이 반환된다
IF L_FLAG THEN
L_RESULT:=L_YEAR||'년은 윤년입니다.';
ELSE
L_RESULT:=L_YEAR||'년은 평년입니다.';WIDTH_BUCKET(n1, min_val, max_val, block_count)
- min_val에서 max_val 까지의 구간을 block_count 개수의 그룹으로 나누었을 때 주어진 값 n1이 그 중 어느 구간에 포함되었는지를 구간의 순번을 반환
- 각 구간의 범위는 '구간 하한값'<=구간<'구간 상한값' 으로 구간 상한값은 해당 구간에 포함되지 않고 다음 구간에 포함된다.
- 표현하는 구간의 수는 block_count+2개임 ('max_val'보다 큰 구간, 'min_val'미만 구간 표현)
회원테이블에서 회원들의 마일리지를 조회하여 회원 등급을 부여하시오
회원등급은 1000~8000 까지 7로 구분하고 등급명은 다음과 같다
1 이하 : 새싹회원
1-2 등급 : 평회원
3-5 등급 : 열심회원
6 이상 등급 : VIP회원
Alias는 회원번호, 회원명, 마일리지, 등급, 회원등급명이다
SELECT MEM_ID AS 회원번호,
MEM_NAME AS 회원명,
MEM_MILEAGE AS 마일리지,
WIDTH_BUCKET(MEM_MILEAGE,1000,8000,7) AS 등급,
CASE WHEN WIDTH_BUCKET(MEM_MILEAGE,1000,8000,7)<1 THEN '새싹회원'
WHEN WIDTH_BUCKET(MEM_MILEAGE,1000,8000,7)<3 THEN '평회원'
-- WHEN WIDTH_BUCKET(MEM_MILEAGE,1000,8000,7) IN(1,2) THEN '평회원'
-- WHEN WIDTH_BUCKET(MEM_MILEAGE,1000,8000,7) BETWEEN 1 AND 2 THEN '평회원'
WHEN WIDTH_BUCKET(MEM_MILEAGE,1000,8000,7)<5 THEN '열심회원'
ELSE 'VIP회원'
END AS 회원등급명
FROM MEMBER;회원테이블에서 회원들의 마일리지를 조회하여 회원 등급을 부여하시오
회원등급은 1000~8000까지 7로 구분하고 마일리지가 많은 회원부터 1 등급에서 등급값이 큰 등급을 순차적으로 부여하시오
Alias는 회원번호, 회원명, 마일리지, 등급이다
SELECT MEM_ID AS 회원번호,
MEM_NAME AS 회원명,
MEM_MILEAGE AS 마일리지,
9-WIDTH_BUCKET(MEM_MILEAGE,1000,8000,7) AS 등급
--WIDTH_BUCKET(MEM_MILEAGE,8000,1000,7)+1 AS 등급
FROM MEMBER;3. 날짜함수
★ SYSDATE
- 시스템의 날짜를 반환
- '+' 와 '-' 연산의 대상이 됨
- (SYSTIMESTAMP > 더 정확한 시간표현)
회원테이블의 생년월일 자료를 이용하여 매주 월요일에 조회할 때,
이번주에 생일인 회원들에게 문자메세지를 보내려고 한다. 해당 회원들을 조회하시오
Alias 회원번호, 회원명, 생년월일, 핸드폰번호
SELECT MEM_ID AS 회원번호,
MEM_NAME AS 회원명,
MEM_BIR AS 생년월일,
MEM_HP AS 핸드폰번호
FROM MEMBER
WHERE EXTRACT(MONTH FROM SYSDATE) = EXTRACT(MONTH FROM MEM_BIR)
AND EXTRACT(DAY FROM MEM_BIR) BETWEEN EXTRACT(DAY FROM SYSDATE)
AND EXTRACT(DAY FROM SYSDATE+7);ADD_MONTHS(d1,n)
- 주어진 날짜자료 d1에 n개월을 더한 날짜 반환
HR 계정의 사원테이블에서 입사일(HIRE_DATE)을 15년을 더한 날짜로 변경하시오
UPDATE HR.EMPLOYEES
SET HIRE_DATE=ADD_MONTHS(HIRE_DATE,180);SELECT EMP_NAME,HIRE_DATE
FROM HR.EMPLOYEES;NEXT_DAY(d1, fmt)
- 주어진 날짜 d1이후 처음 만나는 'fmt' 요일의 날짜를 반환
- fmt : '월요일', '월', '화요일', .... 등 의 요일명
SELECT NEXT_DAY(SYSDATE,'월요일') FROM DUAL;
LAST_DAY(d1)
- 주어진 날짜 d1에 포함된 월의 마지막 날짜
- 주로 2월의 마지막일자나 사용자가 실행 중 입력 받은 날짜 월의 마지막 일자를 구할 때 사용
매입테이블에서 2020년 2월 매입건수를 구하시오.
SELECT COUNT(*) AS 매입건수
FROM BUYPROD
WHERE BUY_DATE BETWEEN TO_DATE('20200201') AND LAST_DAY(TO_DATE('20200201'));키보드로 월을 입력 받아 해당 월의 전체 매출금액 합계를 구하시오.
ACCEPT P_MONTH PROMPT '월 입력 : '
DECLARE
L_SDAY DATE := TO_DATE('2020'||TRIM('&P_MONTH')||TRIM('01'));
L_EDAY DATE := LAST_DAY(L_SDAY);
L_AMT NUMBER := 0;
BEGIN
SELECT SUM(A.CART_QTY*B.PROD_PRICE) INTO L_AMT
FROM CART A, PROD B
WHERE A.CART_PROD=B.PROD_ID
AND TO_DATE(SUBSTR(A.CART_NO,1,8)) BETWEEN L_SDAY AND L_EDAY;
DBMS_OUTPUT.PUT_LINE(EXTRACT(MONTH FROM L_SDAY)||'의 매출 합계 : '|| L_AMT);
END;MONTHS_BETWEEN(d1,d2)
- 두 날짜자료 d1과 d2 사이의 달수를 반환
사원테이블에서 근속년수를 구하시오. 근속년수는 월까지 고려하여 구하시오.
SELECT EMPLOYEE_ID AS 사원번호,
EMP_NAME AS 사원명,
ROUND(MONTHS_BETWEEN(SYSDATE,HIRE_DATE)) AS 근속월수,
TRUNC(MONTHS_BETWEEN(SYSDATE,HIRE_DATE)/12)||'년 '||
TRUNC(MOD(MONTHS_BETWEEN(SYSDATE,HIRE_DATE),12))||'개월' AS 근속년수
FROM HR.EMPLOYEES;★ EXTRACT(fmt FROM d1)
- 주어진 날짜자료 d1에서 fmt를 반환
- fmt는 YEAR, MONTH, DAY, HOUR, MINUTE, SECOND 이며 반환되는 값의 타입은 숫자형이다.
사원테이블에서 각 월별 입사인원수를 조회하시오.
SELECT EXTRACT(MONTH FROM HIRE_DATE) AS 월,
COUNT(*) AS 사원수
FROM HR.EMPLOYEES
GROUP BY EXTRACT(MONTH FROM HIRE_DATE)
ORDER BY 1;매입테이블에서 2020년 상반기 월별 매입집계를 조회하시오.
SELECT EXTRACT(MONTH FROM BUY_DATE)||'월' AS 월,
SUM(A.BUY_QTY) AS 매입수량합계,
SUM(A.BUY_QTY*B.PROD_PRICE) AS 매입금액합계
FROM BUYPROD A, PROD B
WHERE A.BUY_PROD=B.PROD_ID
AND EXTRACT(YEAR FROM A.BUY_DATE)=2020
AND EXTRACT(MONTH FROM A.BUY_DATE) BETWEEN 1 AND 6
-- AND A.BUY_DATE BETWEEN TO_DATE('20200101') AND LAST_DAY(TO_DATE('20200601'))
GROUP BY EXTRACT(MONTH FROM A.BUY_DATE)
ORDER BY 1;ROUND(d1), TRUNC(d1)
- 주어진 날짜자료 d1을 시간 단위에서 반올림 또는 자리버림 수행
YEAR(d1) MONTH(d1) DAY(d1)
날짜의 각각 년/월/일이 반환
4. 변환함수
CAST(expr AS type명)
- 'expr'(컬럼이나 수식)을 'type'으로 형변환
- 'type'은 오라클의 모든 타입 사용 가능
- 형식을 지정하지 못해 사용빈도는 낮음
SELECT CAST (PROD_COST AS CHAR(20)),
CAST (SUBSTR(PROD_ID,2) AS NUMBER(30))
FROM PROD
WHERE PROD_LGU='P201';TO_CHAR() (문자타입으로 변환)
- 주어진 자료(숫자, 날짜, 문자열)를 'fmt' 형식의 문자열 자료로 변환
- date가 문자열인 경우 CHAR, CLOB 타입의 데이터만 혀용
- 'fmt'날짜형과 숫자형이 있으며 생략되면 단순 문자열로 형 변환
『날짜형 형식지정 문자열』
| 형식 문자열 | 의미 | 사용 예 | 결과(24/03/18 오후4시기준) |
| AD, BD |
기원 전(BD) 기원 후(AD) |
SELECT TO_CHAR(SYSDATE, 'BC'), TO_CHAR(SYSDATE, 'AD') FROM DUAL; |
서기 서기 |
| CC | 세기 | SELECT TO_CHAR(SYSDATE, 'CC') FROM DUAL; |
21 |
| YYYY, YYY, YY, Y |
년도 | SELECT TO_CHAR(SYSDATE, 'YYYY'), TO_CHAR(SYSDATE, 'YYY'), TO_CHAR(SYSDATE, 'YY'), TO_CHAR(SYSDATE, 'Y') FROM DUAL; |
2024 024 24 4 |
| Q | 분기 | SELECT TO_CHAR(SYSDATE, 'Q') FROM DUAL; |
1 |
| MM, RM |
월(로마자 월) | SELECT TO_CHAR(SYSDATE, 'MM') TO_CHAR(SYSDATE, 'RM') FROM DUAL; |
03 III |
| MONTH, MON | '월' 글자 추가 출력 | SELECT TO_CHAR(SYSDATE, 'MONTH') FROM DUAL; |
3월 |
| W, WW, IW |
주차 | SELECT TO_CHAR(SYSDATE, 'W') TO_CHAR(SYSDATE, 'WW') FROM DUAL; |
3 12 |
| DD, DDD, J |
일 | SELECT TO_CHAR(SYSDATE, 'DD') TO_CHAR(SYSDATE, 'DDD') TO_CHAR(SYSDATE, 'J') FROM DUAL; |
18 078 2460388 |
| D, DAY, DY |
요일 | SELECT TO_CHAR(SYSDATE, 'D') TO_CHAR(SYSDATE, 'DAY') TO_CHAR(SYSDATE, 'DY') FROM DUAL; |
2 월요일 월 |
| AM, A.M. | 오전 | SELECT TO_CHAR(SYSDATE, 'AM') FROM DUAL; |
오후 |
| PM, P.M. | 오후 | SELECT TO_CHAR(SYSDATE, 'PM') FROM DUAL; |
오후 |
| HH, HH12, HH24 |
시간 | SELECT TO_CHAR(SYSDATE, 'HH') TO_CHAR(SYSDATE, 'HH24') FROM DUAL; |
04 16 |
| MI | 분 | SELECT TO_CHAR(SYSDATE, 'MI') FROM DUAL; |
44 |
| SS, SSSSS (하루의 전체 초) |
초 | SELECT TO_CHAR(SYSDATE, 'SS') TO_CHAR(SYSDATE, 'SSSSS') FROM DUAL; |
57 61737 |
『숫자형 형식지정 문자열』
| 형식 문자열 | 의미 | |
| 9 | 대응되는 자료가 유효숫자이면 해당되는 자료 출력, 0이면 공백 출력 (단, 소숫점 이하 경우에는 '0' 출력) |
|
| 사용 예 | SELECT TO_CHAR(234, '9,999'), TO_CHAR(234.267, '9,999.99'), TO_CHAR(234, '999.99') FROM DUAL; |
234 234.27 234.00 |
| 0 | 대응되는 자료가 유효숫자이면 해단되는 자료 출력, 0이면 0 출력 소숫점 이하인 경우에도 '0' 출력) |
|
| 사용 예 | SELECT TO_CHAR(234, '0,000'), TO_CHAR(234.267, '0,000.00'), TO_CHAR(234, '000.99') FROM DUAL; |
0,234 0,234.27 234.00 |
| PR | 주어진 숫자가 음수인 경우 '<>' 안에 출력 | |
| 사용 예 | SELECT TO_CHAR(234, '9,999PR'), TO_CHAR(-234.267, '9,999.99PR'), TO_CHAR(-234, '999.99PR') FROM DUAL; |
234 <234.27> <234.00> |
| L($) | 데이터 왼쪽에 화폐기호 출력(L은 지역화폐) | |
| 사용 예 | SELECT TO_CHAR(6782, 'L999,999'), TO_CHAR(234.267, '$9,999.99'), TO_CHAR(234000, 'L999999.99') FROM DUAL; |
￦6,782 $234.27 ￦234000.00 |
| , (COMMA) | 데이터 3자리마다 ',' (자리점) 출력 | |
| . (DOT) | 소숫점 표현 | |
TO_DATE(data [,fmt]) (날짜타입으로 변환)
- 날짜 형식의 문자열 데이터와 숫자 데이터를 기본 날짜형으로 변환
- fmt는 date가 기본 날짜형으로 자동 변환될 수 없는 편집된 자료인 경우
- 해당 data가 출력(편집)에 사용된 형식 문자열을 기술
- 날짜 타입 형식 문자열은 TO_CHAR과 동일
SELECT TO_DATE('20200201'),
-- TO_DATE('20200201 오후 1:23:35'), 불가능
-- TO_DATE('2020년03월19일'), 불가능
TO_DATE('2020/02/01'),
TO_DATE('2020-02-01'),
TO_DATE('2020 02 01')
FROM DUAL;→ 기본 날짜형으로 자동변환할 수 없는 자료인 경우 fmt로 형식 문자열 기술
SELECT TO_DATE('20200201 오후 3:11:11', 'YYYYMMDD PM HH:MI:SS'),
TO_DATE('2020년03월19일','YYYY"년"MM"월"DD"일"')
FROM DUAL;TO_NUMBER(data [,fmt])
- 숫자 형식의 문자열 데이터를 기본 숫자형으로 변환
- fmt는 date가 기본 숫자형으로 자동 변환될 수 없는 편집된 자료인 경우
- 해당 data가 출력(편집)에 사용된 형식 문자열을 기술
- 숫자 타입 형식 문자열은 TO_CHAR과 동일
SELECT TO_NUMBER('230.89'),
TO_NUMBER('-1234')
FROM DUAL;SELECT TO_NUMBER('<12,567>','99,999PR'),
TO_NUMBER('123,567.00', '999,999.99'),
TO_NUMBER('￦2,300','L9,999')
FROM DUAL;5. NULL처리 함수
IS NULL, IS NOT NULL
- NULL인지 여부 판단
NVL(col,value)
- 'col' 값이 NULL이면 'value'를 반환하고 NULL이 아니면 'col'값을 반환함
- 'col'과 'value'는 반드시 같은 타입이어야 함
더보기
회원테이블에서 마일리지가 1000미만인 회원의 마일리지를 NULL로 변경하시오.
UPDATE MEMBER --테이블명
SET MEM_MILEAGE=NULL --컬럼명= 변경할 값
WHERE MEM_MILEAGE<1000; --변경할 행 선택더보기
상품테이블에서 각 상품의 마일리지(PROD_MILEAGE)를 다음 조건에 맞게 계산하여 입력하시요
상품의 마일리지(PROD_MILEAGE)=상품의 이익(판매가-매입가)의 1%
단, 상품의 마일리지가 100 미만이면 NULL처리하시오.
CREATE OR REPLACE PROCEDURE PROC_PROD_MILEAGE(
P_PID IN PROD.PROD_ID%TYPE)
IS
L_MILEAGE NUMBER:=0;
CURSOR CUR_MILE IS
SELECT (PROD_PRICE - PROD_COST) * 0.01 AS PMILE, PROD_ID
FROM PROD;
BEGIN
FOR REC IN CUR_MILE LOOP
IF REC.PMILE <100 THEN
L_MILEAGE:=NULL;
ELSE
L_MILEAGE:=REC.PMILE;
END IF;
UPDATE PROD
SET PROD_MILEAGE=L_MILEAGE
WHERE PROD_ID=REC.PROD_ID;
COMMIT;
END LOOP;
END; DECLARE
CURSOR AA IS
SELECT PROD_ID
FROM PROD;
BEGIN
FOR REC IN AA LOOP
PROC_PROD_MILEAGE(REC.PROD_ID);
END LOOP;
END;사원테이블에서 50-90 부서에 속한 사원들의 보너스를 계산하여 출력 하시오
보너스=급여*영업실적코드이며 보너스가 없으면 '보너스 없음'을 출력하시오
Alisa는 사원번호, 부서번호, 급여, 영업실적코드,보너스 / 부서번호 순으로 출력
SELECT EMPLOYEE_ID AS 사원번호,
DEPARTMENT_ID AS 부서번호,
SALARY AS 급여,
COMMISSION_PCT AS 영업실적코드,
NVL(TO_CHAR(SALARY * COMMISSION_PCT,'99,999'),'보너스 없음') AS 보너스
-- 타입이 달라서 실행이되지 않음 '보너스 없음'은 CHAR
FROM HR.EMPLOYEES
WHERE DEPARTMENT_ID BETWEEN 50 AND 90
ORDER BY 2;회원 테이블에서 마일리지가 NULL인 회원을 찾아 비고란에 '휴면회원'을,
NULL이 아닌 회원은 해당 마일리지를 출력하시오
Alias는 회원번호, 회원명, 비고
SELECT MEM_ID AS 회원번호,
MEM_NAME AS 회원명,
NVL(TO_CHAR(MEM_MILEAGE,'99,999'),'휴면회원') AS 비고
FROM MEMBER;다음은 2020년 5월 회원별 구매실적에 따른 마일리지를 계산한 쿼리이다.
마일리지가 없는 상품에는 마일리지는 0으로 계산할 것
잘못된 명령을 고쳐 정상 수행되게 하시오.
SELECT A.MEM_ID AS 회원번호,
A.MEM_NAME AS 회원명,
NVL(A.MEM_MILEAGE,0) AS 원본마일리지,
NVL(D.MSUM,0) AS 추가마일리지,
NVL(A.MEM_MILEAGE,0)+NVL(D.MSUM,0) AS 변경마일리지
FROM MEMBER A, (SELECT B.CART_MEMBER AS CID,
SUM(B.CART_QTY*C.PROD_MILEAGE) AS MSUM
FROM CART B, PROD C
WHERE B.CART_PROD=C.PROD_ID
AND B.CART_NO LIKE '202005%'
GROUP BY B.CART_MEMBER) D
WHERE A.MEM_ID=D.CID(+);
-- OUTER JOIN숫자 + NULL = NULL값이 나오므로
0으로 반환시켜 계산해야함
NVL2(col, value1, value2)
- 'col'값이 NULL이면 'value2'를 반환하고, NULL이 아니면 'value1'을 반환
- 'value1'과 'value2'의 데이터 타입은 일치해야 함 (col타입과는 상관X)
- NVL의 기능을 포함 NVL2(col,col,value)
상품테이블에서 생상정보(PROD_COLOR)를 조회하여
NULL이면 ' 색상 없는 상품', NULL이 아니면 '색상 보유 상품'을 출력
Alias는 상품코드, 상품명, 비고
SELECT PROD_ID AS 상품코드,
PROD_NAME AS 상품명,
NVL2(PROD_COLOR,'색상 보유 상품','색상 없는 상품') AS 비고
FROM PROD;NULLIF(col1, col2)
- 'col1'과 'col2'를 비교하여 같은 값이면 NULL을 반환하고 다른 값이면 'col1'의 값을 반환 함
더보기
상품테이블에서 분류코드 'P301' 에 속한 상품의 매출가격을 매입 가격으로 조정하시오.
UPDATE PROD
SET PROD_PRICE=PROD_COST
WHERE PROD_LGU='P301';
COMMIT;상품테이블에서 매입가격과 매출가격이 동일한 품목은 매출가에 '단종예정상품'을 출력하시오
Alias는 상품코드, 상품명, 매입가, 매출가
SELECT PROD_ID AS 상품코드,
PROD_NAME AS 상품명,
PROD_COST AS 매입가,
LPAD(NVL(TO_CHAR(NULLIF(PROD_PRICE,PROD_COST)),'단종예정상품'),LENGTHB('단종예정상품')) AS 매출가
FROM PROD6. 집계함수
SELECT절에서 사용 시, GROUP BY절을 무조건 사용해야함
SUM() AVG() COUNT() MAX() MIN()
- 특정 컬럼을 기준으로 같은 값을 갖는 행들을 그룹으로 묶고 각 그룹마다 합, 평균, 갯수 등을 구하는 함수
- 집계함수는 다른 집계함수를 포함할 수 없다. ex.
AVG ( SUM () )→ 서브쿼리 사용
SELECT 컬럼1[, ... 컬럼n],
SUM(컬럼명) | AVG (컬럼명) | COUNT(*|컬럼명) | MAX(컬럼명) | MIN(컬럼명)
FROM 테이블명
[WHERE 조건]
GROUP BY 컬럼1[, ... 컬럼n];
[HAVING 조건]
[ORDER BY 컬럼명|컬럼인덱스 [ASC|DESC], ...]- SELECT 절에 집계함수만 사용되면 GROUP BY절 생략
- GROUP BY절에는 SELECT절에 사용된 일반컬럼을 모두 다 기술해야함
- GROUP BY절에 하나 이상의 컬럼이 기술되면 가장 왼쪽에 기술된 컬럼을 기준으로 그룹을 묶고(대분류), 다음에 기술된 컬럼으로 각 그룹 내에서 다시 그룹을(중분류) 묶는다
- HAVING 조건은 집계함수에 대한 조건을 부여할 때 반드시 사용
- WHERE에는 집계함수를 사용할 수 없음 HAVING에 써야함
- SELECT절에 기술되어진 것뿐만 아닌 테이블 내에있는 것을 조건에 사용할 수 있다.
- 기술순서 : WHERE → GROUP BY → HAVING → ORDER BY
상품테이블에서 모든 상품의 수를 조회하시오
SELECT COUNT(*) AS 상품수
FROM PROD;회원테이블에서 회원수를 조회하시오
SELECT COUNT(*) AS 회원수
FROM MEMBER;사원테이블에서 모든 사원수를 조회하시오
SELECT COUNT(*) AS 사원수
-- ROUND(AVG(SALARY)) AS 평균급여,
-- SUM(SALARY) AS 급여합계,
-- MAX(SALARY) AS "최고 급여액",
-- MIN(SALARY) AS "최저 급여액"
FROM HR.EMPLOYEES;사원테이블에서 부서별 사원수, 평균급여를 조회하시오.
SELECT DEPARTMENT_ID AS 부서코드,
COUNT(*) AS 사원수,
ROUND(AVG(SALARY)) AS 평균급여
FROM HR.EMPLOYEES
GROUP BY DEPARTMENT_ID
ORDER BY 1;상품테이블에서 분류별 상품의 수를 조회하시오.
SELECT PROD_LGU AS 분류코드,
COUNT(PROD_LGU) AS "상품의 수"
FROM PROD
--WHERE PROD_PRICE>=100000
GROUP BY PROD_LGU
ORDER BY 1;상품테이블에서 각 분류별 최대판매가격과 최소판매가격을 조회하시오
(+ 최대판매 물품과 최소판매 물품을 함께 조회하기)
SELECT PROD_LGU AS 분류코드,
MAX(PROD_PRICE) AS 최대판매가격,
MIN(PROD_PRICE) AS 최소판매가격
FROM PROD
GROUP BY PROD_LGU
ORDER BY 1;서브쿼리와 JOIN을 써야 최대 판매가격 상품과 최소 판매가격 상품을 조회할 수 있다
더보기
SELECT AAID AS 분류코드,
TBLA.AANAME AS "가장 비싼 상품",
TBLA.AAPRICE 가격,
TBLB.BBNAME AS "가방 싼 상품",
TBLB.BBPRICE AS 가격
FROM ( SELECT A.PROD_LGU AS AAID,
A.PROD_NAME AS AANAME,
B.BPRICE AS AAPRICE
FROM ( SELECT PROD_LGU AS BID,
MAX(PROD_PRICE) AS BPRICE
FROM PROD
GROUP BY PROD_LGU) B, PROD A
WHERE B.BPRICE=A.PROD_PRICE
AND BID=A.PROD_LGU ) TBLA,
( SELECT A.PROD_LGU AS BBID,
A.PROD_NAME AS BBNAME,
B.BPRICE AS BBPRICE
FROM (SELECT PROD_LGU AS BID,
MIN(PROD_PRICE) AS BPRICE
FROM PROD
GROUP BY PROD_LGU) B, PROD A
WHERE B.BPRICE=A.PROD_PRICE
AND BID=A.PROD_LGU) TBLB
WHERE TBLA.AAID=TBLB.BBID
ORDER BY 1;매입테이블에서 2020년 월별 매입수량합계를 조회하시오.
SELECT EXTRACT(MONTH FROM BUY_DATE) AS 월,
SUM(BUY_QTY) AS 매입수량합계
FROM BUYPROD
WHERE BUY_DATE BETWEEN TO_DATE('20200101') AND TO_DATE('20201231')
GROUP BY EXTRACT(MONTH FROM BUY_DATE)
ORDER BY 1;매입테이블에서 2020년 제품별 매입수량합계를 조회하시오.
+매입수량합계가 100개 이상인 상품만 조회하시오
SELECT BUY_PROD AS 제품,
SUM(BUY_QTY) AS 매입수량합계
FROM BUYPROD
WHERE EXTRACT(YEAR FROM BUY_DATE) = 2020
GROUP BY BUY_PROD
HAVING SUM(BUY_QTY)>=100
ORDER BY 1;장바구니테이블에서 2020년 5월 회원별 구매금액을 구하되 구매금액이 많은 5명을 조회하시오.
Alias는 회원번호, 회원명, 구매금액
SELECT AID AS 회원번호,
ANAME AS 회원명,
APRICE AS 구매금액
FROM ( SELECT B.MEM_ID AS AID,
B.MEM_NAME AS ANAME,
SUM(A.CART_QTY*C.PROD_PRICE) AS APRICE
FROM CART A, MEMBER B, PROD C
WHERE SUBSTR(A.CART_NO,1,6)='202005'
AND A.CART_MEMBER=B.MEM_ID
AND A.CART_PROD=C.PROD_ID
GROUP BY B.MEM_ID, B.MEM_NAME
ORDER BY 3 DESC)
WHERE ROWNUM <=5;FROM -> WHERE ->GROUP BY -> ORDER BY 순서로 실행되므로 내림차순한 결과의 금액으로 나오지 않으므로 서브쿼리를 통해 출력해야한다.
ROLLUP & CUBE
GROUP BY 절에서만 사용되어 다양한 집계 결과를 반환한다
ROLLUP
GROUP BY ROLLUP (컬럼명1 [, 컬럼명2, ... 컬럼명n]) [, 컬럼명, ... ]
- 사용된 컬럼명n부터 컬럼명 1이 모두 적용된 집계를 반환한 후 컬럼명n부터 하나씩 제거한 집계를 반환함
- 마지막은 모든 컬럼이 적용되지 않는 집계(전체집계)를 반환
- ROLLUP절에 기술된 컬럼의 수가 n개일 때 n+1개의 집계반환
- ROLLUP절 전, 후에 컬럼이 올 수가 있으며 이를 '부분 ROLLUP'이라 함
CUBE
GROUP BY CUBE (컬럼명1 [, 컬럼명2, ... 컬럼명n]) [, 컬럼명, ... ]
- 사용된 컬럼들로 조합 가능한 모든 경우의 집계를 반환함
- 마지막은 모든 컬럼이 적용되지 않는 집계(전체집계)를 반환
- CUBE절에 기술된 컬럼의 수가 n개일 때 2^n개의 집계반환
- CUBE절 전, 후에 컬럼이 올 수가 있으며 이를 '부분 CUBE'이라 함
2020년 월별, 회원별, 제품별 구매수량 합계를 조회하시오
Alias는 월,회원번호,상품번호,구매수량합계
SELECT SUBSTR(CART_NO,5,2) AS 월,
CART_MEMBER AS 회원번호,
CART_PROD AS 상품번호,
SUM(CART_QTY) AS 구매수량합계
FROM CART
WHERE CART_NO LIKE '2020%'
GROUP BY SUBSTR(CART_NO,5,2), CART_MEMBER, CART_PROD
ORDER BY 1, 2, 3;ROLLUP절 사용
SELECT SUBSTR(CART_NO,5,2) AS 월,
CART_MEMBER AS 회원번호,
CART_PROD AS 상품번호,
SUM(CART_QTY) AS 구매수량합계
FROM CART
WHERE CART_NO LIKE '2020%'
GROUP BY ROLLUP(SUBSTR(CART_NO,5,2), CART_MEMBER, CART_PROD)
--GROUP BY SUBSTR(CART_NO,5,2), ROLLUP(CART_MEMBER, CART_PROD)
--일경우 월은 고정
ORDER BY 1, 2, 3;CUBE절 사용
SELECT SUBSTR(CART_NO,5,2) AS 월,
CART_MEMBER AS 회원번호,
CART_PROD AS 상품번호,
SUM(CART_QTY) AS 구매수량합계
FROM CART
WHERE CART_NO LIKE '2020%'
GROUP BY CUBE(SUBSTR(CART_NO,5,2), CART_MEMBER, CART_PROD)
ORDER BY 1, 2, 3;7. 순위 함수
- 오라클에서 성적, 급여, 매출 등의 자료에 대한 순위를 구할 때 사용
- 순위를 부여하는 방법에 따라 RANK, DENSE_RANK, ROW_NUMBER로 구분
- SELECT절에서 사용
RANK() | DENSE_RANK() | ROW_NUMBER() OVER (ORDER BY 컬럼명 [DESC | ASC] [,
컬럼명 [DESC | ASC] ,...] ) AS 컬럼 별칭
RANK()
- 일반적 순위 부여 방식
- 같은 값이면 같은 등수부여하고 다음 등수는 '현재등수+동점자수'이다
| 90 | 80 | 80 | 80 | 70 | 60 |
| 1 | 2 | 2 | 2 | 5 | 6 |
DENSE_RANK()
- 같은 값이면 같은 등수 부여하고 다음 등수는 '현재등수' 다음 등수이다.
| 90 | 80 | 80 | 80 | 70 | 60 |
| 1 | 2 | 2 | 2 | 3 | 4 |
ROW_NUMBER()
- 같은 값이라도 다음 등수 부여(단, 값은 정렬되어있음)
| 90 | 80 | 80 | 80 | 70 | 60 |
| 1 | 2 | 3 | 4 | 5 | 6 |
사원테이블에서 사원들의 급여에 따라 등수를 부여하시오
등수는 많은 급여부터 차례대로 등수 부여
Alias는 사원번호, 사원명, 부서번호, 직무코드,급여,순위
SELECT EMPLOYEE_ID AS 사원번호,
EMP_NAME AS 사원명,
DEPARTMENT_ID AS 부서번호,
JOB_ID AS 직무코드,
SALARY AS 급여,
RANK() OVER(ORDER BY SALARY DESC) AS "순위(RANK)",
DENSE_RANK() OVER(ORDER BY SALARY DESC) AS "순위(DENSE_RANK)",
ROW_NUMBER() OVER(ORDER BY SALARY DESC) AS "순위(ROW_NUMBER)"
FROM HR.EMPLOYEES;사원테이블에서 사원들의 급여에 따라 등수를 부여하시오
단, 등수는 많은 급여부터 차례대로 등수를 부여하되 같은 급여인 경우 입사일이 빠른 사원부터 등수를 부여하시오
SELECT EMPLOYEE_ID AS 사원번호,
EMP_NAME AS 사원명,
HIRE_DATE AS 입사일,
JOB_ID AS 직무코드,
SALARY AS 급여,
RANK() OVER(ORDER BY SALARY DESC, HIRE_DATE) AS 순위
FROM HR.EMPLOYEES;그룹별 순위 구하기
그룹별로 순위가 필요한 경우 사용
RANK () | DENSE_RANK() | ROW_NUMBER() OVER(PARTITION BY 컬럼명 [, 컬럼명, ...]
ORDER BY 컬럼명 [DESC | ASC] [,컬럼명 [DESC | ASC] , ...] ) AS 컬럼명
사원테이블에서 각 부서별 급여에 따른 순위를 부여
SELECT DEPARTMENT_ID AS 부서번호,
EMP_NAME AS 사원명,
SALARY AS 급여,
RANK() OVER(PARTITION BY DEPARTMENT_ID
ORDER BY SALARY DESC ) AS 순위
FROM HR.EMPLOYEES;
2020년 4월 회원별 구매금액합계를 구하고 금액이 많은 회원부터 순위를 각 성별로 부여하시오
SELECT CASE WHEN SUBSTR(D.MEM_REGNO2,1,1) IN ('1','3') THEN '남성'
ELSE '여성' END AS 성별,
D.MEM_NAME AS 회원명,
TA.CSUM AS 구매금액합계,
RANK() OVER(PARTITION BY CASE WHEN SUBSTR(D.MEM_REGNO2,1,1) IN ('1','3') THEN '남성'
ELSE '여성' END ORDER BY TA.CSUM DESC) AS 순위
FROM MEMBER D, -- 2020년 4월 구매금액 합계에 대한 서브쿼리
(SELECT CART_MEMBER,
SUM(B.CART_QTY*C.PROD_PRICE) AS CSUM
FROM CART B, PROD C
WHERE B.CART_PROD=C.PROD_ID
AND SUBSTR(B.CART_NO,1,6)='202004'
GROUP BY CART_MEMBER ) TA
WHERE TA.CART_MEMBER=D.MEM_ID;8. 계층구조함수(계층형 쿼리 함수)
(부서를 결정할 때 사용)
- STATE WITH ~
- CONNECTED BY ~