[Oracle] 함수

함수 (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 PROD

2. 숫자함수

수학적함수

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 PROD

6. 집계함수

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 ~