oracle 함수

2017. 7. 2. 22:55

oracle 함수 C. Oracle2017. 7. 2. 22:55

함수 - single row function

- multiple row function

-----------------------------------------------------------------------------
single row function

-----------------------------------------------------------------------------

LOWER

=> LOWER(컬럼명) 컬럼data를 소문자로 바뀜

select dname, lower(dname), upper(dname), initcap (dname) from dept;

UPPER

=> UPPER(컬럼명)컬럼data를 대문자로 바뀜

select dname, lower(dname), upper(dname), initcap (dname) from dept;

INITCAP

=> UPPER(컬럼명): 컬럼data를 띄어쓰기순으로 첫번째글자만 대문자로 바뀜
(ps. 특수문자[_,%..] 는 띄어쓰기로 간주한다.

select dname, lower(dname), upper(dname), initcap (dname) from dept;

CONCAT

=> CONCAT(컬럼명1,컬럼명2) 합치기 컬럼명1과 컬럼명2를 붙여서 보여준다.

SELECT CONCAT('oracle','9i' ) from dual; -> dual이라는 빈테이블에 oracle9i를 붙여서 보여준다.

SELECT CONCAT(ename,job) from emp; -> ename컬럼data와 job컬럼data를 붙여서 보여준다.

SUBSTR

=> SUBSTR(컬럼명,숫자1,숫자2) 컬럼의 데이타에서 숫자1자리부터 시작해서 숫자2자리까지 출력

SUBSTR(컬럼명,숫자1) 컬럼의 데이타에서 숫자1자리부터 시작해서 끝까지 출력

select substr(ename,2,4) from emp;
-> ename컬럼data를 2번째부터 4번재까지 출력 만약 scott면 cot 출력

select substr(ename,2) from emp;
-> ename컬럼data를 2번째부터 끝까지 출력 만약 scott면 cott 출력

select substr('abcdef',4) from emp;
-> 'abcdef'에서 4번째부터 끝까지 출력 def

INSTR

=> INSTR(컬럼명, '문자')
컬럼의 데이타에서 '문자'의 인자값을 출력

INSTR(컬럼명, '문자',숫자)
컬럼의 데이타에서 숫자부터 시작해서 처음 나타나는 '문자'의 인자값을 출력

INSTR(컬럼명, '문자',숫자1,숫자2)
컬럼의 데이타에서 숫자1부터 시작해서 숫자2에 나타나는'문자'의 인자값을 출력

INSTR(컬럼명, '문자',-숫자1,숫자2)
컬럼의 데이타에서 오른쪽 숫자1부터 시작해서 숫자2에 나타나는'문자'의 인자값을 출력

ps. 여기서 '문자'는 대소문자 구분한다.

select insrt('araoraddd','r') from dual;

컬럼의 데이타 혹은 '문자 araoraddd ' 에서 최초의 '문자r'을 가진 인자값 출력
2 (2번째에 r문자 존재)

select insrt('araoraddd','r',3) from dual;

컬럼의 데이타 혹은 '문자 araoraddd ' 중 3번째부터 시작해서 처음 나타나는 '문자r'을 가진 인자값 출력
5 (5번째에 r문자 존재)

select insrt('araoraddrd','r',3,2) from dual;

컬럼의 데이타 혹은 '문자 araoraddd ' 중 3번째부터 시작해서 숫자2번째로 나타나는 r을 가진 인자값
출력 9 (9번째에 r문자 존재)

select insrt('araoradddGG','r',-3,1) from dual;

컬럼의 데이타 혹은 '문자 araoraddd ' 중 오른쪽 3번째부터 시작해서 숫자1번째로 나타나는 r을 가진
인자값 출력 5 (5번째에 r문자 존재) 여기서 인자값 계산은 왼쪽에서부터 한다.

LENGTH

=> LENGTH(컬럼명) 컬럼의 길이값을 나타낸다.

select length(ename) from emp;

select length('가나') from dual;

LENGTHB

=> LENGTHB(컬럼명) 컬럼의 바이트값을 나타낸다.

select lengthb(ename) from emp;

select lengthb('가나') from dual;

LPAD

=> LPAD(컬럼명,숫자,'문자')
컬럼을 숫자만큼의 공간으로 만들고 빈공간은 '문자'로 채우는데 lpad는 왼쪽부터 채운다.

RPAD

=> RPAD(컬럼명,숫자,'문자')
컬럼을 숫자만큼의 공간으로 만들고 빈공간은 '문자'로 채우는데 rpad는 오른쪽부터 채운다.

select lpad(ename,10,'$'), rpad(ename,10,'#')from emp;

TRIM

=> 1. 공백문자 제거

ex) select trim( ' ot tdt ')from dual;

2.지우고싶은 문자 제거

ex) select trim( 'S' from ename) from emp where ename='SMITH'
-> ename데이타 SMITH중 'S'를 제거한 나머지를 보여준다.

ex) select trim( 'o' from 'oraoraora') from dual; -> 양쪽의 값(o)만 제거한다.

LTRIM

=> 왼쪽공백 제거

ex) select ' abc ' , ltrim(' abc ') , rtrim(' abc ') from dual;

RTRIM

= > 오른족공백 제거

ex) select ' abc ' , ltrim(' abc ') , rtrim(' abc ') from dual;

REPLACE

=> 대체

ex) select replace ('나는 왕', '나', 'son') from dual; -> 나는 왕에서 '나'문자를 'son'으로 바꿔라

select replace (ename,'S','천재son') from emp

참고

=> select ASCII('a') from dual;

select CHR(97) from dual;

select translate ('ABCD','AC','ae') from dual; = > 'ABCD'에서 AB를 ae로 바꿔라

숫자함수

FLOOR

=> 내림

ex) select 3.161 , floor(3.161), ceil(3.161), round(3.161), round(3.161,자리수)from dual;

3 4 3 3.16 반올림해서 나타낼 자리수.2이면 2번째 자리까지 나타냄

CEIL

=> 올림

ex) select 3.161 , floor(3.161), ceil(3.161), round(3.161), round(3.161,자리수)from dual;

ROUND

=> 반올림

ex) select 3.161 , floor(3.161), ceil(3.161), round(3.161), round(3.161,자리수) from dual;

TRUNC

=> 소수점 자리내림

ex) select 3.164, truc(3.164), trunc(3.164,자리수) from dual;

3 3.16 내림해서 나타낼 자리수.2이면 2번째 자리까지 나타냄

MOD

=> 나눈 나머지

ex) select mod(10,3) from dual; = > 10을 3으로 나눈 나머지

ABS

=> 절대값

ex) select abs(-50) from dual;

SQRT

=> √ (제곱근)

ex) select sqrt(4) from dual;

POWER

=> 제곱승

ex) select power(3,2) from dual; = > 3의 2승 출력

날짜 함수 (날짜와 날짜는 뺄수는 있어도 더하지는 않는다. 의미가 없는것이어서 지원하지 않는다.)

SYSDATE

= > OS의 시간값을 읽어서 현재 시간값을 나타낸다

ex) select sysdate from dual;

ex) alter session set NLS_DATE_FORMAT='dd-mon-yy';
-> 현재 세션에서 날짜를 'dd-mon-yy'으로 바꾼다.

ex) alter session set NLS_DATE_FORMAT='dd-mon-yy:hh:mi:ss';
-> 현재 세션에서 날짜를 'dd-mon-yy:hh:mi:ss' 으로 바꾼다.

=> 날짜계산

ex) select sysdate + 7 from dual; => 현재 날짜에서 7일을 더한다. 7일후

ex) select sysdate + 7/24 from dual; => 현재 날짜에서 7시간을 더한다. 7시간후

ex) select sysdate - to_date('2005-01-01','yyyy-mm-dd') from dual;

-> 문자이기 때문에 형변환을 해서 시간값으로 바꾼후 연산한다.

ex) select ceil ( sysdate - to_date('2005-01-01','yyyy-mm-dd') ) from dual;

MONTHS_BETWEEN

=> 달의 차이수를 출력

ex) select months_between (sysdate,'2004-02-10') from dual;
-> 현재 날짜와 뒤의 날짜와의 달수 차이를 나타낸다.

-> 여기서 문자인데도 형변환을 안하는 이유는 months_between함수에서 이곳은 날짜가
들어간다고 규정되어 있기 때문에 Oracle에서 암시적 형변환을 해주기 때문이다.

ADD_MONTHS

=> 달을 더한다.

ex) select add_months( sysdate,12 ) from dual;
-> 현재의 날짜에서 12달을 더한다.

LAST_DAY

=> 마지막날을 보여준다.

ex) select last_day( sysdate ) from dual;

-> 현재달에서 가장 마지막날을 보여준다.

NEXT_DAY

=> 가까운 요일을 나타낸다.

ex) select next_day( sysdate,'일요일 ) from dual;

-> 오늘부터 시작해서 가장 가까운 일요일을 반환해서 나타낸다.

참고

=> ex) select round( sysdate, 'MONTH') from dual; => 달을 반올림

=> ex) select round( sysdate, 'DD') from dual; => 날짜를 반올림

=> ex) select trunc( sysdate, 'DAY') from dual; => 요일을 소수점 내림

일반함수

- NVL

=> NVL(컬럼명,값) 컬럼의 값이 null를 가질때 값을 가짐. NULL연산이 가능하도록 NVL()함수사용

ex) SELECT ename,sal,comm,nvl(comm,0)+sal FROM emp;

- NVL2

=> NVL2(컬럼명,값1,값2) 컬럼의 값이 null이면 값2, null이 아니면 값1를 가진다.

ex) SELECT ename,comm,nvl2(comm,1,0)l FROM emp;

- NULLIF

=> NULLIF(컬럼명,값) 컬럼의 DATA에서 값과 같은 DATA값을 NULL로 만든다.

ex) select ename,sal, NULLIF( sal,1250) from emp;
-> sal컬럼의 데이타중 1250에 해당하는 데이타는 null로 출력한다.

-COALESCE

=> 인자값중에서 NULL이 아닌 첫번째것의 값을 반환해서 나타낸다.

ex) select coalesce(null,100,200), coalesce(null,null,'ab') from dual;
-> 100,ab 가 나타난다.

-DECODE

=> ex) select ename, decode(Job, 'MANAGER',SAL*1.1, 'CLERK',SAL*1.05, SAL) "UPSAL", SAL,
JOB from emp;

- CASE

=> DECODE보다 CASE사용하는것이 더 좋다. 표준문임

ex) select ename,

case when deptno=10 then '관리부'

when deptno=20 then '인사부'

else '기타'

end

from emp;

-----------------------------------------------------------------------------

multiple row function 한번에 2번까지 중첩사용이 가능합니다.

-----------------------------------------------------------------------------

- count, count(*) => 개수