내장함수의 이해-2009/02/18

select employee_id, first_name, last_name
from employees
where employee_id = :emp;

create table score(
score number);

insert into score values(100);
insert into score values(0);
insert into score values(null);

select avg(score), count(*) from score;
select avg(nvl(score, 0)), count(*) from score;

select department_id, sum(salary) from employees
group by department_id
order by department_id;

--------------------------------------------------
--scott
-------------------------------------------------
--built-in function (오라클 내장함수)
---------------------------문자함수
--ascii 코드값에 해당하는 문자
select chr(75) from dual;

-- 문자열 접합
select concat(concat(ename, ' is a '), job) from emp;

-- 첫글자들을 대문자로
select initcap('i am a boy') from dual;

-- 소문자로
select lower('Mr, Smith') from dual;

--대문자로
select upper('Mr, Smith') from dual;

--왼쪽채우기 (전체 15자리 왼쪽 나머지 공간은 *)
select lpad('Page 1', 15, '*') from dual;

--오른쪽채우기 (전체 15자리 오른쪽 나머지 공간은 *)
select rpad('Page 1', 15, '*') from dual;

--왼쪽 지우기 (왼쪽에 x나 y가 나오면 제거-안나올때까지)
select ltrim('xyxXxyLast Word', 'xy') from dual;

--문자열 대체
select replace ('JACK and JUE', 'J', 'BL') from dual;

--부분 문자열 추출 (인덱스 3에서 2개-인덱스는 1부터 시작)
select substr('abcdefg',3,2) from dual;
select substr('abcdefg',3) from dual;
select substr('abcdefg',-3,2) from dual;

--문자의 유니코드값
select ascii('뷁') from dual;

--첫번째인자문자열에서 두번째 문자열이
--세번째인자문자열에서 네번째인자번째나온 곳의 인덱스
select instr('CORPORATE FLOOR','OR',3,2) from dual;
select instr('CORPORATE FLOOR','OR',-3,2) from dual;

--문자열의 길이
select length('안녕하세요!') from dual;
select lengthb('안녕하세요!') from dual;

--가장큰것(각각의 문자를 비교하여 각각 큰거 & 작은거 비교)
select greatest('HARRY', 'HARIOT', 'HALORD') from dual;

--가장 작은것
select least('HARRY', 'HARIOT', 'HALORD') from dual;

-- null 대체
--nvl : 첫번째 인자가 null이면 두번째 인자, 아니면 첫번째 인자.
select nvl(sal,0), nvl(ename,'*'), nvl(hiredate, '02/11/11') from emp;

----------------------------------시스템 함수
select sysdate from dual;
select systimestamp from dual;
select user from dual;

----------------------수학함수
--절대값
select abs(-15) from dual;

--올림값
select ceil(15.7) from dual;

--내림값
select floor (15.7) from dual;

--버림값
select trunc(15.7) from dual;

--cosine
select cos(180*3.14/180) from dual;

--e의 몇 승
select exp(4) from dual;

--log
select log(10, 100) from dual;

--나머지
select mod(11,4) from dual;

--승
select power(3,2) from dual;

--반올림
select round(15.193, 1) from dual;
select round (15.193, -1) from dual;

-------------------------------날짜함수
--월더하기
select hiredate, add_months(hiredate, 1)
from emp where empno=7782;

--해당 월의 마지막날
select hiredate, last_day(hiredate) from emp;

--시간대 변경
select hiredate, new_time(hiredate, 'GMT', 'PDT') from emp;

--개월의 차
select hiredate, months_between(sysdate, hiredate) from emp;

------------------------------변환 함수

--MM:월
--MON:세글자 영문 월
--MONTH:전체글자 영문 월
--D:그주의 일 수(1:일요일)
--DD:날짜
--DY:요일을 세글자 영문
--DAY:요일의 전체 이름
--YYYY:네자리 표현한 연도
--YY:두자리로 표현한 연도
--HH:12시간으로 표시, HH24:24시간으로 표시
--MI:분
--SS:초
select sysdate, to_char(sysdate, 'D') from dual;
--2009/02/18 12:10
select sysdate, to_char(sysdate, 'YYYY/MM/DD HH24:MI') from dual;
--02일 ==> 2일 (앞에 0제거)
select ename, to_char(hiredate, 'fmDD-MM-YY') from emp;

--문자열을 숫자로 변환
select to_number('100')+200 from dual;
select '100'+200 from dual;

--숫자를 형식에 맞게 문자로 변환
--숫자혀익요소 9(숫자하나), 0(자리가비면 0으로), $(달러).(소수점),(콤마)
select to_char(12506, '$9099,99') from dual;
select '\'||to_char(1234567, '90999,99') from dual;

--날짜의 형식을 변경
select sysdate, to_date('09/07/01', 'DD-MM-YY') from dual;

-- 9i버젼에서 추가된 함수
--nvl2(첫번째인자가 null이면 세번째 인자 null이 아니면 두번째 인자)
select empno, ename, nvl2(comm, comm*1.1, 0) from emp;
--nullif(같으면 null, 다르면 첫번째 인자)
select empno, ename, nullif(comm,0) from emp;
--coalesce(null이 아닌 첫번째 인자)
select coalesce(comm, sal) from emp;
select coalesce(null,null,null,3,null) from dual;
--trim (8i에 추가)
select ename, trim(LEADING 'A' FROM ename) as trim
from emp
where ename like 'A%';

select ename, trim(TRAILING 'N' FROM ename) as trim
from emp
where ename like '%N';

select ename, trim(BOTH 'A' FROM ename) as trim
from emp
where ename like '%';

--------------------------------------복수행 함수
select count(*) from emp;
select count(empno) from emp;
select sum(sal) from emp;

select avg(sal) from emp;
select avg(nvl(sal,0)) from emp;

select max(sal), min(sal) from emp;

--입사일이 가장 늦은 사람과 이른 사람의 일수 차
select max(hiredate)-min(hiredate) from emp;

--부서별 평균 급여
select avg(nvl(sal,0)), deptno
from emp
group by deptno;

--그룹에 조건을 줄때는 having
--그룹에 속하는 사원수가 2보다 크다라는 조건
select deptno, count(*), sum(sal)
from emp
group by deptno
having count(*)>2;

--decode
--decode(expr, a, r1,b,r2,default)
--expr을 평가해서 a이면 r1, b이면 r2, 나머지 모든 경우 default 반환
select job, sal, decode(job, 'ANALYST', sal*1.10,  'CLERK', sal*1.15,
  'MANAGER',sal*1.20, sal) "인상된 급여"
from emp;

--1월 부터 6월 까지 월별로 입사한 사원의 수와 1~6월 전체 입사한 사원의 수
select
count(decode(extract(month from hiredate), '1', 1)) "1월",
count(decode(to_char(hiredate, 'MM'), '02', 1)) "2월",
count(decode(to_char(hiredate, 'MM'), '03', 1)) "3월",
count(decode(to_char(hiredate, 'MM'), '04', 1)) "4월",
count(decode(to_char(hiredate, 'MM'), '05', 1)) "5월",
count(decode(to_char(hiredate, 'MM'), '06', 1)) "6월", count(*) "1월~6월"
from emp
where to_char(hiredate,'MM')>='01' and to_char(hiredate,'MM')<='06';

--case
select job, sal,
  case
    when job='ANALYST' then sal*.10
    when job='CLERK' then sal*1.15
    when job='MANAGER' then sal*1.20
    else sal
  end
from emp;

--사원들의 입사일의 요일을 한자로
select ename, hiredate,
  case
    when to_char(hiredate,'DY')='월' then '月'
    when to_char(hiredate,'DY')='화' then '火'
    when to_char(hiredate,'DY')='수' then '水'
    when to_char(hiredate,'DY')='목' then '木'
    when to_char(hiredate,'DY')='금' then '金'
    when to_char(hiredate,'DY')='토' then '土'
    when to_char(hiredate,'DY')='일' then '日'
  end
from emp;

20090218-lsj403.sql

20090218_home-lsj403.sql