SQL 함수

 

 

내장 함수 

단일행 함수 : 행 별로 처리되는 함수

 숫자형 함수 : MOD ( ) 나머지 값을 구해주는 함수, 

 NULL 함수 : NVL( ) 

 문자형 함수 : LENGTH( )

 날짜형 함수 : MONTHS_BETWEEN( ), SYSDATE

 형변환 함수 : TO_CHAR( ) 숫자를 문자로 

※ 숫자 지정 형식

지정 형식	설명	예	결과
9	9로 출력 자릿수 지정	TO_CHAR(salary, '99999999')	24000
0	자릿수만큼 0을 출력	TO_CHAR(salary, '09999999')	00024000
$	달러 기호	TO_CHAR(salary, '$9999999')	$24,000
L	지역 화폐 기호(원)	TO_CHAR(salary, 'L9999999')	\24,000
.	명시한 위치에 소수점	TO_CHAR(salary, '999999.99')	24000.00
,	명시한 위치에 쉼표	TO_CHAR(salary, '9,9999,999')	24,000

 

SELECT employee_id, MOD(employee_id,2)
FROM employees;

 

SELECT employee_id, salary, commission_pct, salary+salary*NVL(commission_pct, 0.0) "실급여"

FROM employees; 

commission_pct 값이 Null이므로 NVL함수 사용 필요.
null인 경우 0.0리턴, 아닌 경우 그대로 리턴

 

SELECT employee_id, first_name, LENGTH(first_name) 
FROM employees;

 

SELECT employee_id, hire_date, SYSDATE, MONTHS_BETWEEN(SYSDATE, hire_date) 근무개월수
FROM employees;

SELECT employee_id, salary, commission_pct,

               TO_CHAR (salary+salary*NVL(commission_pct, 0.0), 'L9,999,999.0' ) "실급여"

FROM employees; 

1 ) 숫자형함수

ROUND( ) 반올림 된 값 반환
SELECT ROUND (45.923) --46 

              ,ROUND(45.923, 0) --46     : 소수점이하 0자리(1의자리)까지 표현

              ,ROUND(45.923, 1) --45.9  : 소수점이하 1자리까지 표현

              ,ROUND(45.923, 2) --45.92

              ,ROUND(45.923, -1)  --50 : 소수점이하 -1(10의자리)까지 표현
FROM dual; 

 

TRUNC( ) 버림된 값 반환
SELECT TRUNC (45.923) --45

              ,TRUNC(45.923, 0) --45     : 소수점이하 0자리(1의자리)까지 표현

              ,TRUNC(45.923, 1) --45.9  : 소수점이하 1자리까지 표현

              ,TRUNC(45.923, 2) --45.92

              ,TRUNC(45.923, -1)  --40 : 소수점이하 -1(10의자리)까지 표현
FROM dual; 

 

2 ) 문자형함수

대문자로 변환: UPPER( )

소문자로 변환: LOWER( )

첫글자만 대문자로 변환 : INITCAP( )

 

SELECT UPPER ('heLLo'), LOWER('heLLo'), INITCAP('heLLo')

FROM daul;

 

특정문자의 위치반환 : INSTR( )

SELECT INSTR ( 'hellojava', 'a' )  --7 a가 발생하는 최초위치

              ,INSTR ( 'hellojava', 'a', 8 ) --9

              ,INSTR ( 'hellojava', 'b') --0

              ,INSTR ( 'hellojava', 'a', -1) --0

FROM dual;

 

부문 문자열 반환 :SUBSTR( )
SELECT SUBSTR('hellojava', 2, 3) --ell

               ,SUBSTR('hellojava', 2) --ellojava

FROM dual;

 

문자열 제거 : TRIM( )

SELECT TRIM( LEADING 'a' FROM 'aaABCaDEFaHa') --가장 왼쪽에 있는 문자열 a 제거 (ABCaDEFaHa)
              ,TRIM( TRAILING 'a' FROM 'aaABCaDEFaHa') -- 가장 오른쪽에 있는 문자열 a 제거(aaABCaDEFaH)
              ,TRIM( BOTH 'a' FROM 'aaABCaDEFaHa') --양쪽에 있는 문자열 a제거 (ABCaDEFaH)
FROM dual; 

 

 

문자열 늘이기 : LPAD( ), RPAD( )
SELECT LPAD( 'abc' , 5, ' * ') --**abc

              ,RPAD( 'abc' , 5, ' * ') --abc**

              ,RPAD( LPAD( 'abc' , 5, ' * ') , 7, ' * ') --**abc**

FROM dual;

 

문자열 반환 : REPLACE( ), TRANSLATE( )

SELECT REPLACE( 'BCCARD' , 'BC', 'KB' )  -- KBCARD

             ,TRANSLATE( 'BCCARD' , 'BC', 'KB' ) -- KBBARD

             ,REPLACE( 'JACK AND JUE', 'J', 'BL') -- BLACK AND BLUE

             ,TRANSLATE( 'JACK AND JUE', 'J', 'BL') -- BACK AND BLUE (J를 B로만)

FROM dual;

 

 

이름에 'E' 또는 'e'를 포함한 사원의 사번, 이름을 출력하시오

SELECT employee_id 사번,  first_name 이름
FROM employees
WHERE INSTR(UPPER(first_name), 'E') > 0 ;
           -- INSTR(LOWER(first_name), 'E') > 0 ;

 

이름에 'an' 를 포함한 사원의 사번, 이름을 출력하시오
SELECT employee_id,  first_name 

FROM employees

WHERE INSTR(first_name), 'an') > 0 ;

 

실급여(급여+급여*수당률)가 10000보다 많은 사원들을 사번, 급여, 수당률, 실급여 출력하시오

단,실급여는 일의 자리에서 반올림하고 실급여를 많이 받는 사원부터 출력한다

SELECT employee_id,  salary, commission_pct, 
               ROUND (salary + salary * NVL(commission_pct, 0.0), -1) "실급여"
FROM employees
WHERE  ROUND (salary + salary * NVL(commission_pct, 0.0), -1) > 10000
ORDER BY salary DESC;

 

 

 

3) 날짜형함수

날짜용 연산자 : 날짜+숫자,    날짜-숫자   날짜값반환, 날짜-날짜 일수를 반환
SYSDATE 현재 날짜시간반환

MONTHS_BETWEEN( )개월수를 반환

ADD_MONTHS( ) 개월을 더해 날짜 반환
NEXT_DAY( ) 특정일 기준으로 요일에 해당하는 날짜 반환

 

 

SELECT SYSDATE, SYSDATE + 1, SYSDATE-1,  SYSDATE-15
FROM dual;

 

SELECT SYSDATE - TO_DATE ( '23/05/15' )
FROM dual;

 

SELECT SYSDATE,

               MONTHS_BETWEEN( SYSDATE, '23/05/15' )

               ADD_MONTHS(SYSDATE, 5)

               NEXT_DAY( SYSDATE, '월' )

FROM dual;

 

4) 형변환함수

문자형 -> 숫자형 : TO_NUMBER( )
문자형 -> 날짜형 : TO_DATE( )

 

숫자형->문자형 : TO_CHAR( )

날짜형->문자 : TO_CHAR( )

 

자동형변환 
SELECT '1' || 2 FROM dual; --'12' 숫자형2가 문자형으로 자동형변환됨

SELECT  '1' + 2 FROM dual; -- 3 문자형1이 숫자형으로 자동형변환됨

 

SELECT employee_id, hire_date
FROM employees
WHERE hire_date >= ' 08/01/01 '; -- 문자형08/01/01이 날짜형으로 자동형변환됨

 

날짜형 -> 문자형
SELECT SYSDATE, TO_CHAR (SYSDATE, 'YYYYMMDD HH24:MI:SS' )

FROM dual;

 

 

숫자형 -> 문자형

SELECT 12345.678,

                                TO_CHAR(12345.678, '9,999,999.0000')

                                TO_CHAR(12345.678, '9,999,999.0')

                                TO_CHAR(12345.678, '00000000.0')
FROM dual;

 

 

문자형 ->숫자형
SELECT ' 1,234.5 ', TO_NUMBER( '1,234.5', '9,999.0' ) + 10

FROM dual;

 

문자형 ->날짜형
SELECT '2023-06.14',
               SYSDATE - TO_DATE('2023-06-14 오후 02:30', 'YYYY-MM-DD am hh:MI')
FROM dual;

 

날짜형 주의점: 시분초정보 포함

SELECT employee_id, hire_date

FROM employees

WHERE hire_date > ' 08/01/01 ' ; -- 2008년 1월2일부터? (X)

                                                      --2008년 1월1일 0시 0분 0초 보다 큰시간(o)

 

2008년 1월 2일이후(부터) 입사한 입사자를 출력하시오

SELECT employee_id, hire_date
FROM employees
WHERE TO_DATE(hire_date, 'YY/MM/DD') > TO_DATE('08/01/01'); 

 

5) NULL 관련 함수
NULL인 경우 값 변환: NVL( )
NULL이 아닌경우, NULL인 경우 값 변환 : NVL2( )

 

 

관리자가 없는 사원은 '관리자없음' 을, 관리자있는 사원은 관리자번호를 출력하시오
SELECT employee_id, manager_id, NVL(TO_CHAR(manager_id), '관리자없음') 
FROM employees; 
WHERE manager_id IS NULL;

SELECT employee_id, manager_id, NVL2(manager_id, ''||manager_id,  '관리자없음')
FROM employees; 



SELECT employee_id, manager_id, NVL2(manager_id, '관리자있음' , '관리자없음')
FROM employees; 

 

SELECT employee_id, salary, NULLIF(salary, 24000) -- 24000이면 null반환, 아니면 값 그대로 반환
FROM employees;

 

 

 

6) 조건함수 : DECODE (값이 정확히 같을 때)

관리자없는 사원은 '관리자 없음', 있는 사원은 '관리자 있음' 을 출력하시오 

SELECT employee_id, manager_id, DECODE(manager_id, NULL, '관리자없음', '관리자있음' )

 

부서번호가 80이면 '영업부', 60이면 'IT' , 그외의 부서번호는 '그외의 부서'를 출력하시오

SELECT employee_id, manager_id,

               DECODE(department_id, 80, '영업부', 60, 'IT부', '그외의 부서')

FROM employees;

 

여러행 함수 : 여러 행을 그룹화하여 처리하는 함수

 

7) CASE절 : = 연산이 아닌 크거나 작은 경우 사용하면 좋음

관리자없는 사원은 '관리자없음', 있는 사람은 '관리자있음'을 출력하시오

SELECT employee_id, manager_id,

CASE 

 WHEN manager_id IS NULL THEN '관리자없음'
 ELSE '관리자있음'
END

FROM employees;

 

부서번호가 80이면 '영업부', 60이면 'IT' , 그외의 부서번호는 '그외의 부서'를 출력하시오

SELECT employee_id, department_id,
CASE WHEN department_id =80 THEN '영업부'
           WHEN department_id =60 THEN 'IT부'
            ELSE '그외의 부서'
END
FROM employees;

 

 

SELECT employee_id, department_id,

CASE department_id

           WHEN  80 THEN '영업부'

           WHEN 60 THEN 'IT부'

           ELSE '그외의 부서'
END

FROM employees;

 

 

사원의 사번, 급여, 급여 등급을 출력하시오
등급은 급여가 15000이상이면 'A', 10000이상이면 'B' , 5000이상이면 'C', 5000미만이면 'D'

등급이 높은 순서대로, 급여가 높은 사원부터 출력하시오

 

SELECT employee_id 사번, salary 급여, 
CASE WHEN  salary>=15000 THEN 'A등급' 
           WHEN  salary>=10000 THEN 'B등급'
           WHEN  salary>=5000 THEN 'C등급' 
           ELSE 'D등급' 
END "급여등급"
FROM employees
ORDER BY 급여 DESC, 사번;

 

 

여러행 함수 : SUM( ), COUNT( ), AVG( ), MAX( ), MIN( )

SELECT SUM(salary) --총급여

              ,COUNT(department_id) --부서번호를 갖고있는 사원행수 (department_id가 null인 행은 제외)

              ,COUNT(*) --모든사원수 107

              ,AVG(salary) -- 평균급여 691416/107

              ,MAX(salary)  --최대급여

              ,MIN(salary) --최소급여

FROM employees; 

 

부서별 부서번호, 총급여, 사원수, 평균급여, 최대급여, 최소급여를 출력하시오

사원수가 적은 부서부터 출력한다

SELECT department_id 부서번호, sum(salary) 합계, count(*) 사원수, 
avg(salary) 평균, max(salary) 월급최대값, min(salary)월급최소값
FROM employees
GROUP BY department_id
ORder BY count(*); 

 

 

입사일자별 입사일자, 입사자수를 출력하시오

입사일자가 오래된 일자부터 출력한다

SELECT hire_date, COUNT(*)
FROM employees
GROUP BY hire_date

ORDER BY hire_date;

 

입사년도별 년도, 사원수를 출력하시오

입사일자가 오래된 년도부터 출력한다

SELECT TO_CHAR(hire_date, 'YYYY'), COUNT(*)
FROM employees
GROUP BY TO_CHAR (hire_date, 'YYYY')

ORDER BY 1;

 

 

SELECT SUBSTR (TO_CHAR(hire_date), 1, 2), COUNT(*)
FROM employees

GROUP BY SUBSTR (TO_CHAR(hire_date), 1, 2)

ORDER BY 1;