NULL
- 연산(+, -, *, /)에 NULL 포함되면 결과도 NULL
- 많은 함수들은 NULL 값을 제외하고 함수 처리
- 처리함수인
NVL,NVL2를 이용해 NULL 값을 다른 값으로 바꿔 사용할 수 있음
NULL 처리함수
- NVL(표현식, 표현식의 결과가 NULL인 경우에 사용할 값)
- NVL2(표현식, (표현식의 결과가 NULL이 아닌 경우에 사용할 값, 표현식의 결과가 NULL인 경우에 사용할 값))
/* 사원 테이블에서 모든 사원들의 실제 커미션 조회 커미션 = 연봉*커미션퍼센트 커미션을 받지 않는 경우 0으로 조회 */ SELECT EMPLOYEE_ID, NVL(SALARY*COMMISSION_PCT, 0) AS COMMISSION FROM EMPLOYEES; --SALARY * NVL(COMMISSION_PCT,0)으로 해도 됨
- NULL 값은 인덱스(INDEX)를 사용할 수 없지만 처리함수를 이용해서 NULL 값을 다른 값으로 바꾸면 사용 가능해짐
연산자
BETWEEN A AND B: A와 B 사이에 속하는 모든 값(A와 B를 포함함)NOT BETWEEN A AND B: A와 B 사이에 속하지 않는 모든 값IN(A, B, C): A, B, C 중 하나임NOT IN(A, B, C): A, B, C 모두 아님LIKE 패턴: 패턴과 일부 일치함NOT LIKE 패턴: 패턴과 일치하는 부분이 없음A || B: A와 B를 연결
와일드카드
%: 모든 문자를 의미, 글자 수에 제한이 없음_ (밑줄): 모든 문자를 의미, 글자 수가 1글자로 제한됨
패턴
김%: '김'으로 시작하는 모든 패턴%김: '김'으로 끝나는 모든 패턴%김%:'김'을 포함하는 모든 패턴
함수
- 모든 함수에서 NULL 값은 제외
통계함수
SUM: 합계AVG: 평균MAX: 최댓값MIN: 최솟값COUNT: 데이터 개수COUNT(*)전체 행의 개수STDDEV: 표준편차VARIANCE: 분산
-- 사원 테이블에서 전체 사원의 커미션 퍼센트 평균 조회 -- 커미션이 없는 사원은 제외하고 조회 (직접 제외 안해도 모든 함수들은 NULL값을 제외함) SELECT AVG(COMMISSION_PCT) AS 커미션퍼센트평균 FROM EMPLOYEES; -- 사원 테이블에서 전체 사원의 최대 커미션 조회 -- 커미션 = 연봉 * 커미션퍼센트 SELECT MAX(SALARY*COMMISSION_PCT) AS 최대커미션 FROM EMPLOYEES; -- 사원 테이블에서 전체 사원 중 가장 나중에 입사한 사원의 입사일 조회 SELECT MAX(HIRE_DATE) AS 최근고용일 FROM EMPLOYEES; -- 가장 나중 날짜 -> 큰날짜 구하기 = MAX -- (날짜=숫자, 타임스탬프 이용하는데 그게 시간 지날수록 커지게 설계되어 있음)
수학함수
POWER(A, B): A의 B제곱SQRT(A): A의 제곱근(루트 A)ABS(A): A의 절대값MOD(A, B): A를 B로 나눈 나머지SIGN(A): A가 양수이면 1, 음수이면 -1, 0이면 0을 반환CEIL(A): 실수 A를 정수로 올림FLOOR(A): 실수 A를 정수로 내림TRUNC(A, [DIGIT]): 실수 A를 DIGIT 자릿수로 절사, DIGIT 생략하면 정수로 절사ROUND(A, [DIGIT]): 실수 A를 DIGIT 자릿수로 반올림, DIGIT 생략하면 정수로 반올림
-- 원하는 자릿수로 반올림 SELECT ROUND(123.456) --123 (정수로 반올림) , ROUND(123.456,1) --123.5 (소수 1자리로 반올림) , ROUND(123.456,2) --123.46 (소수 2자리로 반올림) , ROUND(123.456,-1) --120 (일의 자리에서 반올림) , ROUND(123.456,-2) --100 (십의 자리에서 반올림) -- 원하는 자릿수로 절사 SELECT TRUNC(123.456) --123 (정수로 절사) , TRUNC(123.456, 1) --123.4 (소수 1자리로 절사) , TRUNC(123.456, 2) --123.45 (소수 2자리로 절사) , TRUNC(123.456, -1) --120 (일의 자리에서 절사) , TRUNC(123.456, -2) --100 (십의 자리에서 절사) FROM DUAL;
날짜함수
SYSDATE: 오라클이 설치된서버의 현재 날짜와 시간 (DATE타입)SYSTIMESTAMP: 오라클이 설치된서버의 현재 날짜와 시간 (TIMESTAMP타입)CURRENT_DATE:SESSIONTIMEZONE의 현재 날짜와 시간 (DATE타입)CURRENT_TIMESTAMP:SESSIONTIMEZONE의 현재 날짜와 시간 (TIMESTAMP타입)
SELECT SESSIONTIMEZONE --Asia/Seoul , CURRENT_DATE --DATE 형식 , CURRENT_TIMESTAMP -- TIMESTAMP 형식 FROM DUAL;
-- DATE 형식의 날짜 연산 -- 1) 1일을 숫자 1로 처리 -- 2) 1=1일, 1/24=1시간, 1/24/60=1분, 1/24/60/60 = 1초 SELECT TO_CHAR(SYSDATE +1, 'YYYY-MM-DD AM HH:MI:SS'), --1일 후 TO_CHAR(SYSDATE+1/24, 'YYYY-MM-DD AM HH:MI:SS'), --1시간 후 TO_CHAR(SYSDATE+1/24/60, 'YYYY-MM-DD AM HH:MI:SS'), --1분 후 TO_CHAR(SYSDATE+1/24/60/60, 'YYYY-MM-DD AM HH:MI:SS') --1초 후 FROM DUAL; -- 특정 시점에서 경과한 일수 구하기: 비밀번호 변경 안내창 등에 활용 SELECT SYSDATE - TO_DATE('23/07/01', 'YY/MM/DD') -- 23/07/01에서 현재까지 며칠 지났는지 , TRUNC(SYSDATE - TO_DATE('23/07/01', 'YY/MM/DD')) -- 소수점 없애기 (경과한 일수) FROM DUAL; -- TIMESTAMP 형식의 날짜 연산 -- 1) INTERVAL 키워드 이용 -- 2) YEAR, MONTH, DAY, HOUR, MINUTE, SECOND 단위 사용 SELECT SYSTIMESTAMP + INTERVAL '1' YEAR -- 1년 후 , SYSTIMESTAMP + INTERVAL '1' MONTH -- 1개월 후 FROM DUAL; -- 경과한 일수 구하기 SELECT SYSTIMESTAMP - TO_TIMESTAMP('23/07/01', 'YY/MM/DD') , EXTRACT(DAY FROM SYSTIMESTAMP - TO_TIMESTAMP('23/07/01', 'YY/MM/DD')) -- 경과한 기간에서 일수 추출 FROM DUAL; -- TIMESTAMP에서는 TRUNC 쓰면 오류 (경과한 기간이 숫자가 아닌 TIMESTAMP 형식으로 반환되기 때문)
EXTRACT ( { YEAR | MONTH | DAY | HOUR | MINUTE | SECOND } FROM DATE ):
지정된 DATE에서 필요한 정보 추출NEXT_DAY(DATE, { 일 | 월 | 화 | 수 | 목 | 금 | 토 }): 지정된 DATE의 다음 WEEKDAY(일~월) 반환LAST_DAY(DATE): 지정된 DATE의 해당 월 말일 반환ADD_MONTHS(DATE, N): 지정된 DATE의 N개월 후 날짜MONTHS_BETWEEN(DATE1, DATE2): 두 날짜(최근 DATE1, 이전 DATE2) 사이에 경과한 개월 수
-- 필요한 단위 추출 SELECT EXTRACT(YEAR FROM SYSDATE) , TO_CHAR(SYSDATE, 'YYYY') -- TO_CHAR 함수를 추출용도로 사용 , EXTRACT(MONTH FROM SYSDATE) , EXTRACT(DAY FROM SYSDATE) , EXTRACT(HOUR FROM SYSTIMESTAMP) -- UTC(표준시) 기준 (SYSDATE 기본값은 년원일만 나타내주기 때문에 HOUR, MINUTE, SECOND는 타임스탬프 써야 함) , EXTRACT(HOUR FROM SYSTIMESTAMP)+9 -- Asia/Seoul 기준 , EXTRACT(MINUTE FROM SYSTIMESTAMP) , EXTRACT(SECOND FROM SYSTIMESTAMP) , TRUNC(EXTRACT(MINUTE FROM SYSTIMESTAMP)) FROM DUAL; -- 요일을 기준으로 특정 날짜 구하기 SELECT NEXT_DAY(SYSDATE, '수') -- 다음 수요일 구하기 , NEXT_DAY(SYSDATE-8, '수') -- 저번주 수요일 구하기, -7하면 답 안나올 수도 있으니 -8 FROM DUAL; -- N개월 전후 날짜 구하기 SELECT ADD_MONTHS(SYSDATE, 1) -- 1개월 후 , ADD_MONTHS(SYSDATE, -1) -- 1개월 전 , ADD_MONTHS(SYSDATE, 5*12) -- 5년 후 FROM DUAL; -- 일수는 더하기빼기 월수년수는 함수(ADD_MONTHS) -- 경과한 개월 수 구하기 SELECT MONTHS_BETWEEN(SYSDATE, TO_DATE('23/01/01', 'YY/MM/DD')) FROM DUAL;
타입 변환 함수
TO_NUMBER(STRING)문자 -> 숫자TO_CHAR(NUMBER|DATE, [FORMAT])숫자, 날짜 -> 문자TO_DATE(STRING, [FORMAT])문자 -> 날짜(DATE)TO_TIMESTAMP(STRING, [FORMAT])문자 -> 날짜(TIMESTAMP)
-- 문자로 된 날짜/시간을 실제 날짜/시간 타임으로 변환 SELECT TO_DATE('23/07/04'), --년월일 형식으로 해석 (생략가능) TO_DATE('23/07/04', 'DD/MM/YY') --일월년 형식으로 해석 FROM DUAL;
-- 예제 데이터 DT1에 SYSDATE 값, DT2에 TIMESTAMP 값이 들어있음 DROP TABLE EXAMPLE_T; CREATE TABLE EXAMPLE_T( DT1 DATE, DT2 TIMESTAMP ); INSERT INTO EXAMPLE_T(DT1, DT2) VALUES(SYSDATE, SYSTIMESTAMP); COMMIT; --DT1이 '23/07/04'인 데이터를 조회(안되는게 정상) SELECT * FROM EXAMPLE_T WHERE DT1 = '23/07/04'; --DT1이 '23/07/04'인 데이터를 조회(안되는게 정상) SELECT * FROM EXAMPLE_T WHERE DT1 = TO_DATE('23/07/04', 'YY/MM/DD'); --DT1이 '23/07/04'인 데이터를 조회(됨) 둘 다 TO_DATE해야 됨 SELECT * FROM EXAMPLE_T WHERE TO_DATE(DT1, 'YY/MM/DD') = TO_DATE('23/07/04', 'YY/MM/DD');
- 날짜 변환 헷갈리면 TO_DATE를 다 넣어보자
문자열 함수
UPPER(STRING)STRING을 모두 대문자로 변환LOWER(STRING)STRING을 모두 소문자로 변환INITCAP(STRING)STRING의 첫 글자는 대문자 나머지 글자는 소문자로 변환LENGTH(STRING)STRING의 글자 수CONCAT(STRING1, STRING2)STRING1, STRING2를 연결SUBSTR(STRING, BEGIN, LENGTH)STRING의 BEGIN 위치부터 LENGTH만큼 가져옴INSTR(STRING, FIND)STRING에서 FIND의 위치를 가져옴LPAD(STRING, WIDTH, CHAR)WIDTH에 맞춰 STRING의 왼쪽에 CHAR를 채움RPAD(STRING, WIDTH, CHAR)WIDTH에 맞춰 STRING의 오른쪽에 CHAR를 채움LTRIM(STRING, [CHAR])STRING의 왼쪽 CHAR 제거, CHAR 생략 시 공백 제거RTRIM(STRING, [CHAR])STRING의 오른쪽 CHAR 제거, CHAR 생략 시 공백 제거TRIM(STRING)STRING의 양쪽 공백 제거
-- 연결하기 -- 1) || 연산자 (오라클 전용, 다른 DB에서는 오류) -- 2) CONCAT 함수 -- CONCAT(A, B) : 인수를 2개만 전달할 수 있음 (MYSQL은 3개도 됨) -- CONCAT(CONCAT(A,B),C) : 인수 3개 이상은 CONCAT 함수 여러 개로 해결 SELECT * FROM EMPLOYEES WHERE PHONE_NUMBER LIKE CONCAT('515', '%'); -- CONCAT('515', '%') = '515' || '%' -- SELECT * FROM EMPLOYEES WHERE EMAIL LIKE CONCAT(CONCAT('%','A'),'%'); -- A를 포함('%' || 'A' || '%') -- 일부만 반환하기 SELECT SUBSTR(PHONE_NUMBER,1,3) -- 전화번호 1번째 글자부터 3글자 반환 , SUBSTR(PHONE_NUMBER,5) -- 전화번호 5번째 글자부터 끝까지 반환 FROM EMPLOYEES; -- 8. 채우기 -- 1) LPAD(표현식, 전체폭, 채울문자) -- 2) RPAD(표현식, 전체폭, 채울문자) SELECT DEPARTMENT_ID , EMAIL , RPAD(SUBSTR(EMAIL,1,2),5,'*') --이메일 첫 두글자 사용, 다섯자리로 만들어줘야하고 모자란 자리는 오른쪽 *표 (개인정보 마스킹 등에 활용) FROM EMPLOYEES; -- 9. 공백 제거 SELECT '[' || LTRIM(' HELLO WORLD ') || ']'--왼쪽 공백 제거 [' || || ']'는 공백 잘 보이라고 해준 것 , '[' || RTRIM(' HELLO WORLD ') || ']'--오른쪽 공백 제거 , '[' || TRIM(' HELLO WORLD ') || ']'--양쪽 공백 제거 FROM DUAL; -- 중간 공백 제거는 없음
기타 함수
RANK() OVER(ORDER BY칼럼 ASC) : 순위 함수, 낮은 값이 1등RANK() OVER(ORDER BY칼럼 DESC) : 순위 함수, 높은 값이 1등
SELECT EMPLOYEE_ID , RANK() OVER(ORDER BY SALARY DESC) AS 연봉순위 -- 연봉 내림차순 정렬 후 순위 매기기(동점자는 같은 순위) , HIRE_DATE FROM EMPLOYEES;
ROW_NUMBER() OVER(ORDER BY칼럼 ASC/DESC) : 행번호 함수, 동점자 처리 방식 없음, RANK보다 많이 사용됨- 암호화 함수
SELECT STANDARD_HASH('1111', 'SHA1') --암호화 알고리즘 SHA1 , STANDARD_HASH('1111', 'SHA256') --암호화 알고리즘 SHA256 , STANDARD_HASH('1111', 'SHA384') --암호화 알고리즘 SHA384 , STANDARD_HASH('1111', 'SHA512') --암호화 알고리즘 SHA512 , STANDARD_HASH('1111', 'MD5') --암호화 알고리즘 MD5 FROM DUAL;
- 분기 처리 함수
- 조인 줄일 수 있어서 많이 사용됨
SELECT EMPLOYEE_ID , DEPARTMENT_ID , DECODE(DEPARTMENT_ID , 10, 'Administration' , 20, 'Marketing' , 30, 'Purchasing' , 40, 'Human Resources' , 50, 'Shipping' , 60, 'IT') AS DEPARTMENT_NAME FROM EMPLOYEES;
- 분기 처리 표현식
SELECT EMPLOYEE_ID , DEPARTMENT_ID , CASE WHEN DEPARTMENT_ID = 10 THEN 'Administration' WHEN DEPARTMENT_ID = 20 THEN 'Marketing' WHEN DEPARTMENT_ID = 30 THEN 'Purchasing' WHEN DEPARTMENT_ID = 40 THEN 'Human Resources' WHEN DEPARTMENT_ID = 50 THEN 'Shipping' WHEN DEPARTMENT_ID = 60 THEN 'IT' ELSE 'Unknown' END AS DEPARTMENT_NAME FROM EMPLOYEES;
묵시적 타입변환
: 오라클에서 데이터 타입을 내부적으로 변환시켜주는 것
- 정확한 연산을 위해 데이터 타입을 오라클 내부에서 스스로 바꾸는 것
포맷
숫자 format
9: 숫자 한 자리- (1234, '999999') = 1234 : 빈칸으로 빈자리 채움
0: 숫자 한 자리- (1234, '000000') = 001234 : 0으로 빈자리 채움
SELECT TO_CHAR(1234) --'1234' , TO_CHAR(1234, '999999') -- 999999: 9개수=자리수, 여섯자리로 나타내라는 뜻 ' 1234' , TO_CHAR(1234, '000000') -- 빈자리 0으로 채우기 '001234' , TO_CHAR(1234, '9,999') --'1,234' , TO_CHAR(12345, '9,999') --'######' 숫자는 5자리인데 형식은 4자리만 지정되어 있어서 오류 , TO_CHAR(12345, '99,999') --'12,345' FROM DUAL;
날짜 format
YY: 년도 2자리YYYY: 년도 4자리MM: 월 2자리(01~12)DD: 일 2자리(01~31)AM: 오전 / 오후HH: 12시각(01~12)HH24: 24시각(00~23)MI: 분(00~59)SS: 초(00~59)
🟢 구디아카데미후기 IT국비지원 민경태 강사님 수업 6일차! 🟢
함수가 너무 많아서 어떻게 공부해야할지 고민됐는데 하다보니 자연스레 머리에 생각이 나서 신기했다 ㅎㅎ 강사님이 가르쳐주시다가 함수가 없으면 일일이 구현해야 하니 더 복잡했을 거라는 요지의 말을 지나가면서 하셨는데 그렇게 생각을 해보니 함수가 있어서 다행이라는 생각이 들었다.
오늘은 처음으로 평가도 치뤘다. 지금까지 배운 것 정도는 검색없이도 작성할 수 있게 된 것 같아서 뿌듯했고 앞으로도 계속 열심히 하자고 다짐ㅎㅎ
