내장 함수는 SQL 작성을 위해서 사용할 수 있는 유용한 기능이 제공되는 함수를 의미한다.
DBMS 제품마다 조금씩 차이가 있다.
Oracle의 함수의 경우 객체에 종속이 되어있지 않기 때문에 그냥 사용이 가능하다.
빌트인 된 함수라고 생각하면 된다.
그럼 함수와 메소드는 어떤 차이가 있을 까?
함수 차이점
객체에 종속되어 있지 않아 그냥 사용할 수 있다.
메소드 차이점
객체에 종속되어 있어, 객체를 생성하고, 참조변수로 이용가능하다.
공통점
이름이 있는 코드 블록이다.
함수내에 메소드가 들어가 있고, 메소드는 무조건 객체의 구성요소라는 점이 다르다.
함수의 종류에는 2가지가 있다.
함수의 종류
단일행 함수
조회된 행마다 하나의 결과를 반환한다.
단일행 함수는 중첩해서 사용할 수 있다.
종류
문자함수
문자를 입력값으로 받아서 계산한 결과를 반환한다.
대/ 소문자의 변환
LOWER(테이블 명 ) : 소문자로 변환한다.
UPPER(테이블 명 ) : 대문자로 변환한다.
문자열 잘라내기
SUBSTR(컬럼명, 시작위치 ) : 시작위치부터 끝까지 잘라낸다.
SUBSTR(컬럼명, 시작위치, 길이 ) : 시작위치부터 지정된 길이만큼 잘라낸다.
Java의 index는 0부터 시작이다. Oracle의 index는 1부터 시작이다. SUBSTR(JOB_ID, 4) 4번째부터 끝까지 잘라내라는 의미이다.
문자열 길이 조회하기
LENGTH(컬럼명) : 해당 컬럼에 저장된 값의 길이를 반환한다.
SELECT JOB_ID, LENGTH(JOB_ID) FROM EMPLOYEES ORDER BY LENGTH(JOB_ID) DESC ; --컬럼명을 지정하지 않아서 2번연산을 진행하게 되었다.
SELECT JOB_ID, LENGTH(JOB_ID) JOB_ID_LENGTH--컬럼명을 지정한다. FROM EMPLOYEES ORDER BY JOB_ID_LENGTH DESC; -- 컬럼명을 지정하여 연산을 1번만 실행하였다.
1번보다는 2번이 한번의 연산으로 정렬을 할 수 있어 이득이다.
SELECT JOB_ID, LENGTH(JOB_ID) JOB_ID_LENGTH FROM EMPLOYEES ORDER BY 2 DECS; -- 2개의 열을 오름차순으로 정렬하라고 작성하였다 더 깔끔해짐
문자열 채우기
LPAD(컬럼명, 전체길이, '패딩문자')
문자열의 길이가 전체길이만큼 되도록 왼쪽에 패딩문자로 채운다.
RPAD(컬럼명, 전체길이, '패딩문자')
문자열의 길이가 전체길이만큼 되도록 오른쪽에 패딩문자로 채운다.
SELECT FIRST_NAME, LPAD(FIRST_NAME, 15, '*'), RPAD(FIRST_NAME, 15,'*') FROM EMPLOYEES;
LPAD는 왼쪽으로, RPAD는 오른쪽으로 글자가 15글자가 안될 경우 나머지는 '*'로 표시한다.
SELECT LPAD('1234',10,'0'), LPAD('12',10,'0'), LPAD(123456,10,'0') FROM FUAL;
왼쪽으로 10글자에서 1234로 채우고 나머지는 0으로 채워라
왼쪽으로 10글자에서 12로 채우고 나머지는 0으로 채워라
왼쪽으로 10글자에서 123456으로 채우고 나머지는 0으로 채워라
DUAL
DUAL은 오라클에 내장된 1행 1열짜리 테이블이다.
DUMMY 라는 테이블 이름을 가지고 있고, 간단한 연산의 결과를 조회할 경우 시스템 테이블에 있는 것 중에서 접근이 허용된 DUMMY TABLE이다.
SELECT는 데이터가 들어있는 테이블을 적으면 조회한 행의 갯수만큼의 전체값이 출력하게 된다. 그렇기 때문에 오라클에서 제공해주는 가상의 1행 1열짜리의 행이다.
SELECT * FROM DUAL; (*)의 의미는 전체라는 의미다 . TOTAL
문자열 연결하기
CONCENT(컬럼명1, 컬럼명2) : 지정된 컬럼의 값들을 서로 이어붙인다.
딱 2개만 이어붙일 수 있다.
컬럼명1 || 컬럼명 2 || 컬럼명3 || ... || 컬럼명N : N개의 컬럼의 값들을 서로 이어 붙인다.
여러개를 이어붙여서 사용할 수 있다. 그렇기 때문에 이방법을 추천한다.
SELECT FIRST_NAME, LAST_NAME, CONCAT(FRIST_NAME, LAST_NAEM) FIRST_NAME || ' ' || LAST_NAME FROM EMPLOYEES WHERE DEPARTMENT_ID = 60;
불 필요한 공백 없애기
trim(컬럼명) : 지정된 컬럼의 값 왼쪽, 오른쪽에 위치한 무의미한 공백을 제거한다.
SELECT trim(' abc abc abc ') FROM DUAL;
문자와 문자사이의 공백은 없앨 수 없다.
문자열 바꾸기
REPLACE(컬럼명, 찾는 문자열, 대체할 문자열) : 지정한 컬럼에서 특정 문자열을 찾아서 다른 문자열로 대체한다.
SELECT ROUND(123.45), ROUND(123.55), TRUNC(123.45), TRUNC(123.55) FROM DUAL;
ROUND는 반올림으로 소수점을 없애지만, TRUNC는 증가없이 소수점값을 버린다.
천장값과 바닥값 구하기
CEIL : 자신보다 큰 정수 중에서 가장 작은 수를 반환한다.
FLOOR : 자신보다 작은 정수 중에서 가장 큰 정수를 반환한다.
SELECT ROUND(2.6), TRUNC(2.6), CEIL(2.6) ,FLOOR(2.5) FROM DUAL;
반올림 값이기 때문에 ROUND는 3이 출력, TRUNC는 그냥 잘라내기로 2가 출력, CEIL은 2.6보다 가장 큰 정수값에서 가장 작은 수로 3이 반환 FLOOR은 2.5에서 자신보다 작은 정수중에서 가장 큰 정수를 반환함으로 2가 반환된다.
나머지 구하기
MODE(숫자1, 숫자 2) : 첫번째 숫자를 두번째 숫자로 나눗셈을 수행하고 나머지를 반환한다.
SELECT MOD(10, 3) , MOD(5, 3) FROM DUAL;
Java에서는 % 연산자와 동일하다. 이런 연산자를 MODULAR연산이라고 한다.
날짜함수
Date 타입의 값에 대한 처리를 수행한다.
괄호가 존재하지 않는 함수가 존재한다.
그런 함수를 인자가 없는 함수라고 한다. 따로 입력할 것이 존재하지 않기 때문에 입력하지 않는다.
인자를 전달하는 경우에만 () 이 존재한다.
자바는 매개변수가 없어도 () 가 있지만, SQL은 다르게 따로 ()를 작성하지 않는다.
종류
SYSDATE : 시스템의 현재 날짜와 시간정보를 반환하는 함수
JAVA에서 NEW DATE를 넣을 수 있지만, SYSDATE로 획득하여 시간정보를 획득할 수 있다.
SELECT SYSDATE FROM DAUL;
날짜 반올림 및 시간 정보 지우기
ROUND(날짜)
해당 날짜의 시간이 정오를 넘었을 경우 다음날 0시 0분 0초가 반환된다.
TRUNC(날짜)
해당 날자의 모든 시간정보를 0으로 바꾼 값이 반환된다.
SELECT SYSDATE, ROUND(SYSDATE) TRUNC(SYSDATE) FROM DAUL;
MONTHS_BETWEEN(날짜1, 날짜2) : 두 날짜사이의 개월수를 반환한다.
SELECT FIRST_NAME, HIRE_DATE, MONTHS_BETWEEN(SYSDATEM, HIRE_DATE) FROM EMPLOYEES WHERE DEPARTMENT_ID = 60;
소수점 자리를 단일행 함수 중첩으로 CEIL값으로 이용하여 깨끗하게 정리해줄 수 있다.
SELECT FIRST_NAME, HIRE_DATE, CEIL(MONTHS_BETWEEN(SYSDATEM, HIRE_DATE)) FROM EMPLOYEES WHERE DEPARTMENT_ID = 60;
ADD MONTHS(날짜, 개월 수 ) : 날짜에서 지정된 개월수 만큼 변경된 날짜를 반환한다.
SELECT SYSDATE, ADD_MONTHS(SYSDATE,-3), ADD_MONTHS FROM DUAL;
-3을 넣으면 지금시점의 개월수, +3을 넣게 되면 지금으로부터 3개월 후를 반환한다.
LAST_DAY(날짜) : 해당 날짜가 속한 달의 맨 마지막 날짜를 반환한다.
SELECT SYSDATE, LAST_DAY(SYSDATE) FROM DUAL;
그 달의 마지막날을 출력한다.
시작날짜의 경우 1일부터 시작하지만 맨 마지막 날짜는 각 달마다 다르다.
날짜연산
날짜와 숫자로 연산 : 일수 단위로 나눌 수 있게됨
날짜 + 숫자 : 날짜에서 지정된 숫자만큼 경과된 날짜를 반환한다.
날짜 - 숫자 : 날짜에서 지정된 숫자만큼 이전 날짜를 반환한다.
날짜 - 날짜 : 두 날짜 사이의 일수를 반환한다.
날짜 + 날짜 : 오류
숫자/24 : 시간단위로 나눌 수 있게 됨
날짜 + 숫자/ 24 : 날짜에서 지정된 시간만큼 경과된 날짜를 반환한다.
연산에서 숫자란 하루를 의미한다.
숫자/24으로 나눌 경우 시간으로 들어가게 된다.
날짜 - 숫자/24 : 날짜에서 지정된 시간만큼 이전 날짜를 반환한다.
시간의 경우 예금조회, 결제 날짜 조회등으로 많은 사용을 한다.
기억을 하고 있는 편이 좋다.
SELECT SYSDATE SYSDATE + 1/24 AS "1시간 이후 날짜", SYSDATE + 6/24 AS "6시간 이후 날짜", SYSDATE + 1 AS "하루 증가된 날짜", TRUNC(SYSDATE) - 3 AS "3일전 날짜 " -- 시간은 00 : 00 : 00으로 출력된다. TRUNC(SYSDATE) - 7 AS "1주일전 날짜" -- 시간은 00 : 00 : 00으로 출력된다. FROM DUAL;
시스템의 날짜에서 계산된 날짜가 출력된다.
SELECT FIRST_NAME, HIRE_DATE, TRUNC(SYSDATE) - HIRE_DATE AS "총 근무일수" FROM EMPLOYEES WHERE DEPARTMENT_ID = 60;
오늘 날짜까지의 총 근무일수를 구할 수 있다.
기타함수
NVL, CASE, DECODE등의 함수가 있다.
NVL(컬럼명, NULL일 때 대체할 값)
지정된 컬럼값이 NULL일 때 대체값이 반환된다. NULL이 아니면 컬럼값이 반환된다.
컬럼값과 대체할 값은 데이터타입이 같은 값이다.
NULL값을 포함하는 사칙연산의 최종연산결과는 항상 NULL이다.
사칙연산식에 NULL값을 포함하는 컬럼이 포함되어 있다면 NVL함수를 사용해서 NULL값을 적절한 값으로 대체한 후 연산해야 된다.
SELECT NVL(10, 0 ) NVL(NULL, 0) AS NULL FROM DUAL;
앞의 값이 10이면 원래의 값이 출력, NULL로 지정시 0으로 출력된다.
언제사용될까?
모든 직원들의 이름, 급여, 커미션, 연봉을 조회할 때
NULL값이 포함된 사칙연산에서 사용을 한다. NULL값이 포함될 경우 값이 이상하게 조회되기 때문이다.
SELECT FIRST_NAME, SALARY, COMMISSION_PCT, SALARY*12+SALARY*COMMISSION_PCT*12 FROM EMPLOYEES; -> 평소의 입력이다.
그러나 어떤 값이던지 NULL + X = NULL, NULL - X = NULL, NULL * X = NULL, NULL / X = NULL 이다. 그렇기 때문에 전체 연산의 결과가 NULL이 된다.
SELECT FIRST_NAME, SALARY, COMMISSION_PCT, SALARY*12+SALARY*NVL(COMMISSION_PCT, 0)*12 FROM EMPLOYEES; NULL값을 포함하고 있는 것을 NVL로 감싸서 대체할 값으로 입력하고 작성한다면 출력이 가능하다. 현재의 NVL의 경우 COMMISSIN의 NULL값일 경우 0으로 대체했다
NVL2(컬럼명, 값1, 값2) : 지정된 컬럼의 값이 NULL이 아니면 값1, NULL이면 값2가 반환된다. 값1과 값2는 데이터 타입이 같은 값이다.
커미션의 수령여부를 'YES/NO'값으로 조회하기
SELECT FIRST_NAME, COMMISSION_PCT, NVL2(COMMISSION_PCT, 'YES', 'NO') FROM EMPLOYEES;
CASE ~ WHEN ~ THEN ~ ELSE
CASE ~ WHEN 용법 1(IF 문과 유사 )
CASE WHEN 조건식1 THEN 표현식1 -- 조건식 1이 참이면 표현식 1이 최종결과가 된다. WHEN 조건식2 THEN 표현식2 -- 조건식 2이 참이면 표현식 2이 최종결과가 된다. WHEN 조건식3 THEN 표현식3 -- 조건식 3이 참이면 표현식 3이 최종결과가 된다. ELSE 표현식4 --조건식1, 조건식2, 조건식3이 모두 FALSE로 판정되면 표현식4의 최종결과가 된다. END AS 별칭
별칭을 입력시에는 END 다음에 작성을 해야한다. CASE와 END까지가 하나의 표현식이다.
대소관계를 비교할 경우 사용을 한다.
CASE ~ WHEN 용법 2 (SWITCH문과 유사)
CASE WHEN 값1 THEN 표현식1 --값이 값1과 일치하면 표현식 1이 최종결과가 된다. . WHEN값2 THEN 표현식2-- 값이 값2과 일치하면 표현식 2이 최종결과가 된다. WHEN값3 THEN 표현식3-- 값이 값3과 일치하면 표현식 3이 최종결과가 된다. ELSE 표현식4 --값1, 값2, 값3 이 모두 일치하지 않으면 표현식4의 최종결과가 된다. END
SWITCH 문은 EQCUALS 비교만 가능하다. 같다는 비교만 가능하다.
SWITCH문과 유사한 형태를 가진다면 이 후에 등장하는 DECODE를 많이 사용한다.
DECODE
사용방법 1
DECODE(컬럼명, 값1, 표현식1, -- 컬럼의 값이 값1과 일치하면 표현식1이 최종결과가 된다. 값2, 표현식2, -- 컬럼의 값이 값2과 일치하면 표현식2이 최종결과가 된다. 값3, 표현식3) -- 컬럼의 값이 값3과 일치하면 표현식3이 최종결과가 된다. 컬럼의 값이 값1, 값2, 값3 모두와 일치하지 않으면 NULL이 최종결과가 된다.
사용방법 2
DECODE(컬럼명, 값1, 표현식1, -- 컬럼의 값이 값1과 일치하면 표현식1이 최종결과가 된다. 값2, 표현식2, -- 컬럼의 값이 값2과 일치하면 표현식2이 최종결과가 된다. 값3, 표현식3, -- 컬럼의 값이 값3과 일치하면 표현식3이 최종결과가 된다. 표현식4) -- 컬럼의 값이 값1, 값2, 값3 모두와 일치하지 않으면 표현식4가 최종결과가 된다.
SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID = '100';
문자 '2007/01/01'이 HIRE_DATE의 타입과 같은 타입으로 변환
SELECT * FROM EMPLOYEES WHERE HIRE_DATE >= '2007/01/01';
2007-01-01
2007.01.01도 변환가능하다.
명시적 형변환
포맷팅된 문자 -> 숫자 : TO_NUMBER('포맷팅된 문자' , '패턴')
SELECT TO_NUMBER('1,000','9,999') * 10 FROM DUAL;
문자가 지정된 패턴의 숫자 형식일 때 숫자로 변환한다.
숫자의 위치가 동일해야지 가능하다.
숫자 ->포맷팅된 문자 :TO_CHAR(숫자, '포맷팅패턴')
패턴 문자 : 0 숫자 하나와 대응, 정수부에서 해당 자리에 숫자가 없어도 0이 출력된다. 패턴 문자 : 9 숫자 하나와 대응, 정수부에서 해당 자리에 숫자가 없으면 출력되지 않는다. 패턴 문자 : , 자리수 패턴 문자 : . 소수점
SELECT TO_CHAR(1000000000,'9,999,999,999'), TO_CHAR(10000,'9,999,999'),TO_CHAR(1000,'0,000,000') FROM DUAL; 9의 형태와 숫자가 변환할 갯수만큼 있어야한다. 9로 작성해도 되고 0으로 작성해도 되지만, 0으로 작성하면 남은 자리에 0을 입력한다. 소수점을 이용할 때, 정밀도를 위해서 0을 작성하고 정수를 다룰 때는 9를 입력하는 것이 좋다.
JAVA - Decimalformat df = new DecimalFormat("##.###");
으로 사용하지만, 오라클의 경우 그냥 문자열은 자동 형변환(묵시적 형변환) 이 가능하고, 포맷팅된 형변환의 경우 필요시에 명시적 형변환을 진행해야한다. DB에서 값을 변환하는 것보다는
나중에 APP 대체하는 JSP를 이용하여 보여줄 때 변환을 하는것이 좋다.
SELECT는 그냥 원본을 출력할 수 있게 만든다.
명시적 형변환 날짜
명시적 형변환 날짜 -> 포맷팅된 문자열 : 원하는 포맷형태로 바꿀 수 있다. 패턴 문자 : yyyy혹은 YYYY 년 패턴 문자 : mm혹은 MM 월 패턴 문자 : DD혹은 dd 일 패턴 문자 : am혹은 AM 오전/오후 패턴 문자 : HH혹은 hh 시간(1~12) 패턴 문자 : hh24혹은 HH24 시간(0~23) 패턴 문자 : MI혹은 mi 분 패턴 문자 : SS혹은 ss 초
날짜 -> 포맷팅된 문자
SELECT SYSDATE, TO_CHAR(SYSDATE,'yyYY/MM/DD'), TO_CHAR(SYSDATE, 'HH24:MI:SS'),TO_CHAR(SYSDATE,'AM HH:MI:SS') FROM DUAL;
대/소문자를 구별하지 않는다. 무조건 HH:MM:SS으로 2를 적어줘야한다.
AM은 오전/오후를 구별하는 패턴문자이다.
포맷팅된 문자 -> 날짜
오늘날짜부터 TO_DATE의 설정날짜를 뺄 수 있다.
SELECT SYSDATE - TO_DATE('2021/10/01') FROM DUAL;
특정구간의 날짜를 구할 때 사용된다.
SELECT * FROM EMPLOYEES WHERE HIRE_DATE >= '2007/01/01' AND HIRE_DATE < TO_DATE('2007/03/31') + 1;
SELECT * FROM EMPLOYEES WHERE HIRE_DATE >= '2007/01/01' AND HIRE_DATE < '2007/07/01';
다중행 함수
조회된 행을 그룹으로 묶고 행 그룹당 하나의 결과를 반환한다.
Group by절을 사용해서 조회된 행을 그룹으로 묶고 다중행 함수로 각 그룹당 하나의 결과 (합계, 평균, 분산(분산되어있는 값이 얼만큼 있는 지 조회) 표준편차, 최고값, 최저값 등을 계산해 낸다.
행 하나를 선별하여 전체가 실행되고 그것을 그룹으로 묶을 수 있으며, 여러가지르 더해서 출력이 가능ㅎ다.
함수는 빌트인 된 함수이다.
다중행 함수 SUM(컬럼명) :해당컬럼값의 합계를 반환한다. NULL인 행은 제외한다. AVG(컬럼명) :해당컬럼의 평균값을 반환한다. NULL인 행은 제외한다. MAX(컬럼명) :해당컬럼의 최대값을 반환한다. NULL인 행은 제외한다. MIN(컬럼명) :해당컬럼의 최소값을 반환한다. NULL인 행은 제외한다. COUNT(* 혹은 컬럼명) : 행의 갯수를 반환한다. *인 경우 해당 컬럼의 값이 NULL이어도 포함시킨다. 컬럼명을 적으면 해당 컬럼이 NULL이면 제외된다.
GROUP BY와 함께 사용하면 강력한 힘을 보인다.
60번 부서에 소속된 직원들의 총급여를 조회하기
SELECT SUM(SALARY) FROM EMPLOYEES WHERE DEPARTMENT_ID = 60;
60번 부서에 소속된 직원들의 평균급여를 조회하기
SELECT AVG(SALARY) FROM EMPLOYEES WHERE DEPARTMENT_ID = 60;
60번 부서에 소속된 직원들의 최대급여,최소급여를 조회하기
SELECT MAX(SALARY), MIN(SALARY) FROM EMPLOYEES WHERE DEPARTMENT_ID = 60;
전체 사원의 수를 조회하기
SELECT COUNT(*) FROM EMPLOYEES;
전체행을 조회한다.
커미션을 받은 사원의 수를 조회하기
SELECT COUNT(COMMISSION_PCT) FROM EMPLOYEES;
NULL이 아닌 COMMISSION의 행을 COUNT한다.
커미션을 받은 사원의 수를 조회하기
SELECT COUNT(*) FROM EMPLOYEES WHERE COMMISSION_PCT IS NOT NULL;
NULL이 아닌 행을 먼저 찾고, 그 다음의 수를 COUNT한다.
COUNT는 결과로는 한 개의 행이 출력된다.
단일 그룹과 중첩해서 사용할 경우 다중행 함수는 행그룹당 한 행만 결과가 나오기 때문에 다중행 함수가 아닌것과 같이 사용할 수 없다.
서버쿼리(SUB_QUERY)
SELECT FIRST_NAME, SALARY FROM EMPLOYEES WHERE SALARY = (SELECT MAX(SALARY) FROM EMPLOYEES);
먼저 (SELECT)문을 통해 SALARY MAX의 값을 찾고, 그 찾은 값에서 이름과 급여를 출력한다.
