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?
