오라클 함수, SQL Functions

1. 9i 함수 메뉴얼

* http://docs.oracle.com/cd/B10501_01/server.920/a96540/functions2a.htm#80856

 

2. 10g 함수 메뉴얼
* http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions001.htm#sthref912

# 가공 --> 연산 - 산술
                - 연결
                - 논리
                - ...
       --> 함수 - 단일행 함수 : lower ... : 3장
                - 복수행 함수 : avg   ... : 4장
                - 사용자 정의 함수        : PL/SQL

 

# 3-3 : argument? 아규먼트, 인자, 인수, 매개변수, 파라미터, ...

  col ip format a20
  select  user, sysdate, userenv('sid'), SYS_CONTEXT ('USERENV', 'IP_ADDRESS') as ip          from dual;
  select ename, lower(ename), instr(ename, 'E'), lower(substr(ename, 1, 2)), substr(ename, 3) from emp;

----------------------
 Character
----------------------

# 3-11

 - substr

   select ename, substr(ename, 2, 3), substr(ename, 2), substr(ename, -2, 1) from emp;

   select ename, lpad(ename, 10), ename                               from emp;
   select ename, substr(lpad(ename, 10), 10, 1), substr(ename, -1, 1) from emp;

 - length

   select ename, length(ename) from emp;

   select * from emp where length(ename) = 5;         --> 이름이 5자인 사원
   select * from emp where ename like '_____';        --> 이름이 5자인 사원

 - instr

   select ename, instr(ename, 'A') from emp;

   select * from emp where instr(ename, 'A') > 0;     --> 이름에 A가 포함된 사원
   select * from emp where ename like '%A%';          --> 이름에 A가 포함된 사원

 - rpad, lpad

   col stars format a60

   select empno, ename, sal, sal from emp;
   select empno, ename, sal, round(sal/100) from emp;
   select empno, ename, sal, '*', round(sal/100) from emp;
   select empno, ename, sal, rpad('*', round(sal/100)) as stars from emp;
   select empno, ename, sal, rpad('*', round(sal/100), '*') as stars from emp;

   col ename2 justify r
   col ename2 format a10
   select empno, ename, lpad(ename, 10) as ename2, sal from emp;

 - ltrim, rtrim vs trim

   select ltrim('SOS', 'S')            from dual;
   select trim(leading 'S' from 'SOS') from dual;

   select rtrim('SOS', 'S')             from dual;
   select trim(trailing 'S' from 'SOS') from dual;

   select rtrim(ltrim('SOS', 'S'), 'S') from dual;
   select trim(both 'S' from 'SOS')     from dual;

         --------

   select ltrim('ABBACAB', 'AB')            from dual;    --> http://docs.oracle.com/cd/B10501_01/server.920/a96540/functions68a.htm#77877
   select trim(leading 'AB' from 'ABBACAB') from dual;    --> ORA-30001: trim set should have only one character

 - reverse

   select ename, reverse(ename) from emp;

문제. 숫자로만 이루어진 행을 찾으세요.

  drop table t1 purge;
  create table t1 (col1 varchar2(30));
  insert into t1 values ('23FA2');
  insert into t1 values ('564766');
  insert into t1 values ('40953');
  insert into t1 values ('P3312');
  insert into t1 values ('48756');
  insert into t1 values ('8876M');
  insert into t1 values ('...');

  commit;

  grant select on t1 to public;

  select * from t1;

  해답 1.

    select upper(col1), lower(col1) from t1;
    select upper(col1), lower(col1) from t1 where upper(col1) = lower(col1);

  해답 2.

    select col1, ltrim(col1, 0123456789)   from t1; 
    select col1, ltrim(col1, 1234567890)   from t1;

    select col1, ltrim(col1, '0123456789') from t1;
    select col1, ltrim(col1, '0123456789') from t1 where ltrim(col1, '0123456789') is null;

문제. 연속된 숫자 5개가 포함된 행 찾기

  drop table t2 purge;
  create table t2 (col1 varchar2(40));

  insert into t2 values('이것은 12321이다');
  insert into t2 values('중요한 것은 12가 123보다 크지 않다는 것이다');
  insert into t2 values('3 곱하기 2는 6이다. 6 나누기 2는 3이다.');
  insert into t2 values('전체의 합은 76871이다');
  ...

  commit;

  grant select on t2 to public;

  select * from t2;

  해답.

    select col1, translate(col1, '0123456789', '----------') from t2;
    select col1 from t2 where translate(col1, '0123456789', '----------') like '%-----%';

    cf.http://docs.oracle.com/cd/B10501_01/server.920/a96540/functions150a.htm#79574

----------------------
 Number
----------------------

# 3-13

  select ceil(2.4), floor(2.4) 
  from dual;

  select round(45.995, 2),     -- 46 or 46.00
         round(45.995, -2),    -- 0
         round(55.995, -2),   
         round(78345.995, -2)  
  from dual;

  select to_char(round(45.995, 2), '999,999.99') as ret
  from dual;

  select dbms_random.value(1000, 2000) as ret from dual connect by level <= 100;
  select round(dbms_random.value(1000, 2000), -2) as ret from dual connect by level <= 100;

  select ret, count(*)
  from (select round(dbms_random.value(1000, 2000), -2) as ret from dual connect by level <= 100)
  group by ret
  order by ret;

  select trunc(45.995, 2),
         trunc(45.995, -2),
         trunc(55.995, -2),
         trunc(78345.995, -2)
  from dual;

  select empno,
         mod(empno, 2),
         mod(empno, 3),
         mod(empno, 5)
  from emp;

  select * from emp where mod(empno, 2) = 0;
  select * from emp where mod(empno, 3) = 0;
  select * from emp where mod(empno, 5) = 0;

# 3-14 : Dual

  @desc dual
  select * from dual;

  col dummy format a10
  select dummy from sys.dual;

  select empno, ename, 37*1.5+23545 from emp;
  select 37*1.5+23545 from emp;
  select 37*1.5+23545 from dept;

  select 37*1.5+23545 from dual;

----------------------
 Date
----------------------

# 3-17

  alter session set nls_date_format = 'DD-MON-RR';
  select sysdate from dual;
  select * from emp;
 
  alter session set nls_date_format = 'YYYY/MM/DD HH24:MI:SS';
  select sysdate from dual;
  select * from emp;

# 3-20

  select sysdate, sysdate + 1            from dual;
  select sysdate, sysdate - 3            from dual;
  select sysdate, sysdate + 5/24         from dual;
  select sysdate, sysdate + 5/(24*60)    from dual;
  select sysdate, sysdate + 5/(24*60*60) from dual;

  alter session set nls_date_format = 'DD-MON-RR';
  select empno, ename, sysdate, hiredate, ceil((sysdate - hiredate)/7) as weeks from emp;

  select sysdate, sysdate + 100, sysdate + 1000, sysdate + 10000 from dual;

  select sysdate - to_date('01-JAN-93') from dual;

  select sysdate * 3 from dual;              --> 에러 : ORA-00932: inconsistent datatypes: expected NUMBER got DATE
  select sysdate + (sysdate + 1) from dual;  --> 에러 : ORA-00975: date + date not allowed

# 3-22

  select empno, ename, sysdate, hiredate,
         months_between(sysdate, hiredate) as Month_Between1,
         ceil(months_between(sysdate, hiredate)) as Month_Between2,
         add_months(hiredate, 5)
  from emp;

  select next_day(sysdate, 'FRI') from dual;
  select next_day(sysdate, 'Mon') from dual;

  select last_day('13-FEB-11') from dual; 
  select last_day('17-FEB-12') from dual; 

# 3-24

  Day   :  1 ~ 15 : 탈락
          16 ~ 31 : 반올림

  Month :  1 ~  6 : 탈락
           7 ~ 12 : 반올림

  select empno, ename, hiredate, trunc(hiredate, 'month'), trunc(hiredate, 'year') from emp order by hiredate;
 
  select empno, ename, hiredate, trunc(hiredate, 'month'), trunc(hiredate, 'year')   --> 81년 2월에 입사한 사원
  from emp
  where trunc(hiredate, 'month') = '01-FEB-81';

  select empno, ename, hiredate, trunc(hiredate, 'month'), trunc(hiredate, 'year')   --> 82년에 입사한 사원
  from emp
  where trunc(hiredate, 'year') = '01-JAN-82';

  --> Hashing?