7월22일 - SQL활용 3 : 함수

SQL 함수

 

1. 오라클 함수

 

  1) ASCII 문자 변환

select chr(65) from dual;   //A select chr(97) from dual;   //a

 

  2) || 결합연산자
A || B -> A와 B를 연결시킴

select ename || '의 직급은 ' || job || '입니다' from emp;

 

  3) concat(칼럼명, '문자열') -> 칼럼에 해당하는 문자열을 붙임

select concat('로미오와 ','줄리엣') from dual;

 

select concat(ename, '의 직급은 '), job from emp;

 

select concat(concat(ename, '의 직급은 '), job) from emp;

 

  4) sysdate -> 시스템의 현재 날짜/시간을 리턴하는 함수 

select sysdate from dual;           //19-07-22    select sysdate+100 from dual;    //19-10-30 select sysdate-100 from dual;     //19-04-13

 

  5) rownum : 행(레코드) 번호

      rowid    : 행(레코드)의 주소값

     의사칼럼(모조칼럼)

select empno,select empno, ename from emp;

 

select rownum, empno, ename from emp;

 

select rowid, empno, ename from emp;

 

select rowid, rownum, empno, ename from emp;

 

  6) 두 날짜 사이의 개월 수 계산 함수

select months_between('2019-09-01','2019-07-01') from dual;

 

  7) to_date('날짜문자열') -> 문자열을 날짜형으로 변환 

select to_date('2019-05-20') from dual;

select to_date('2019-05-20')-to_date('2019-05-05') from dual;

 

  8) to_char(날짜, '출력형식') -> 날짜자료형의 데이터를 문자변환하는 함수 

select to_char(sysdate,'yyyy-mm-dd am hh:mi:ss day') from dual;

select to_char(sysdate,'yyyy-mm-dd am hh24:mi:ss day') from dual; --24시간 기준

 

  9) to_number('숫자형태의 문자열') -> 숫자변환 함수, 내부적으로 to_number()가 호출됨

select 100+10, 100-10, 100*10, 100/10 from dual;

select '100'+1 from dual;                     //100 select to_number('100')+1 from dual;     //100

 

  10) 숫자함수 

    - trunc(숫자1, 자리수) -> 버림, 숫자1을 소숫점 자리수에서 절사

    - round(숫자1, 자리수) -> 숫자1을 소숫점 자리수에서 반올림

    - ceil(숫자1) -> 올림

 

  11) 급여평균, 급여합계, 최고급여, 최저급여, 갯수

select avg(sal)        ,sum(sal)        ,max(sal)        ,min(sal)        ,count(sal) --null은 카운트 안함 from emp; select round(avg(sal),2) --소수둘째자리에서 반올림        ,ceil(avg(sal))   --올림        ,trunc(avg(sal),1)--소수첫째자리에서 절사  from emp;

 

문제) 각 직원들에 대해 직원의 이름과 근속연수를 구하시오. 단, 근속연수는 연단위를 버림하여 나타내시오.

--오늘날짜-입사일 select ename, sysdate-hiredate from emp; select ename, trunc((sysdate-hiredate)/365) as 근속연수 from emp;

 

  12) decode함수 -> decode(A, B, A==B일 때의 값, A<>B일 때의 값)

     - A<>B일 때의 값을 생략하면 null로 처리됨. 

     - decode함수의 매개변수의 갯수는 다중조건에 의해 늘어날 수 있음 

 

예) 주민번호를 이용해서 성별 출력

  ※ SQL문은 인덱스가 1부터 시작

select substr('8912303',7,1) from dual; select decode(substr('8912303',7,1),3,'남자','여자') from dual;

 

  13) lpad 함수 -> lpad(칼럼명, 자리수, '빈칸채우기')

     - 해당칼럼의 자릿수에 맞게 빈칸을 채우기

 

예) 각 직원의 이름, 직급, 급여를 나타내는데 단, 급여는 5자리로 나타내며 부족한 자리수는 '*'로 표시한다. 

월급이 300이상인 직원만 나타내시오.

select ename, job, lpad(sal,5,'*') from emp where sal>=300;

 

문제) 각 직원들에 대해서 직원의 이름과 근무개월수를 나타내는데 근무개월수가 100개월 이상인 직원만 나타내시오. 

근무개월수는 소수 부분을 제외한 정수부분만 나타내시오

select ename, trunc((sysdate-hiredate)/12) as 근무개월수 from emp where trunc((sysdate-hiredate)/12)>=100;

 

문제) 전체 직원에 대하여 직원의 이름과 직급, 총 근무주(week)수를 구하시오

(단, 근무주수가 많은 직원부터 나타내고, 근무주수가 같으면 이름에 대하여 오름차순 정렬하시오)

select ename, job, trunc((sysdate-hiredate)/12/4) as 근무week from emp order by trunc((sysdate-hiredate)/12/4), ename;