오라클 각종 함수

|| - 둘 이상의 문자열을 연결하는 결합 연산자

 

concat - 두 문자열을 연결하여 반환, 두 개만 가능

select concat('My name is ', mem_name) 연결 from member;

 

chr(숫자) - 숫자값(아스키 코드값)을 문자로

select chr(65) || chr(97) || chr(48) from dual;

ascii('문자') - 문자를 아스키 코드값으로

select ascii('a') from dual;

 

lower(' ') - 문자나 문자열을 소문자로

upper() - 문자나 문자열을 대문자로

initcap(' ') - 첫 글자를 대문자로 나머지는 소문자로

select lower('Data manipulation Language') "lower",
      upper('Data manipulation Language') "upper",
      initcap('Data manipulation Language') "initcap" from dual;

 

lpad(c1, n, c2) - 지정된 길이 n(표시할 문자길이)에서 c1을 채우고 남은 공간을 c2로 채움

c2값을 주지 않으면 공백으로 채움

select lpad('Java' , 10, '*'),
        rpad('Flex', 12, '^') from dual;

 

ltrim() - 좌측 공백문자 제거

rtrim(c1, c2) - 인자값 주면 c1에서 c2를 제거, 오른쪽부터 c2가 안나올때까지 제거(붙어 있을 경우에만)

select ltrim('aaabbbccaaa','a') from dual; -> bbbbccaaa
select rtrim('aaabbbccaaa','a') from dual; -> aaabbbcc

 

trim() - 좌우 공백문자 제거

SELECT trim('   aaa   '),  -> aaa
      trim(leading 'a' from 'aaAaBaAaa'),  -> AaBaAaa
      trim(trailing 'a' from 'aaAaBaAaa'),  -> aaAaBaA
      trim('a' from 'aaAaBaAaa') from dual;  -> AaBaA

 

substr(c m, n) - c문자열의 m부터 n의 길이로 문자 리턴, m이 0또는 1이면 첫 글자부터, m이 음수면 뒤부터

select substr('I love Malja', 1, 6), -> I love(처음부터 6개의 문자)
      substr('I love Malja', 8), -> Malja(8번째 부터 끝까지, n이 지정 안되있으므로 맨끝까지)
      substr('I love Malja', -5, 3) -> Mal(뒤에서 5번째 부터 3개의 문자)
from dual ;

 

replace(c1, c2, c3) - c1에 포함된 c2의 문자를 c3값으로 치환, c3이 없으면 찾은 문자 제거

 

translate(c1, c2, c3) - c1의 문자가 c2에 대응하면 대응하는 c2의 인덱스값으로 c3의 문자를 반환

select translate('2009-02-28','0123456789-','ABCDEFGHIJK') -> CAAJKACKCI
from dual;

 

instr(c1, c2, m, n) - c1에서 c2문자가 n번째 나타나는 위치를 리턴, m은 시작 위치, n은 생략 가능

select instr('hello heidi', 'he', 1,2) from dual; -> 7(못찾으면 0반환)

 

width_bucket(c, min, max, b) - min~max의 범위로 설정하고 b개의 구간으로 나누어 c가 속하는 구간을 리턴

 

sysdate - 시스템에서 제공하는 현재 날짜와 시간 값

 

extract(year from sysdate) - 날짜에서 포맷형식 부분만 추출

select mem_name, mem_bir
from member
where extract(month from mem_bir) = 3; -> 3월에 태어난 사람만 조회

 

cast() - 명시적 형 변환

ex) select cast('Hello' as char(30)) from dual;

      select cast('1997/12/25' as date) + 3 from dual;

 

to_char() - 숫자, 문자, 날짜를 지정한 형식의 문자열로 반환

ex) select to_char(sysdate,'AD YYY CC"세기"') from dual;
      select to_char(sysdate,'yyy.mm.dd.hh24:mi:ss') from dual;

      select to_char(-1234.6, 'L9999.00pr') from dual; (pr은 음수인 경우 "<>"로 묶음)

      select to_char(12345678, '999,999,999.00') from dual;

to_number()

select to_number('￦1,200', 'L999,999') from dual;

to_date() - 문자열을 Date형식으로 변환

select to_date('200803101234', 'yyyymmddhh24mi') from dual;

 

round(숫자열, 나타낼 소수점 개수) - 지정된 자리수 밑에서 반올림

round(avg(prod_sale),2) - xxx . xx

 

trunc(숫자열, 나타낼 소수점 개수) - 지정된 자리수 밑에서 버림

trunc(avg(prod_sale),-1) - xx0

 

nvl(c,r) - c가 null이 아니면 c값으로, null이면 r반환

nvl(c, r1, r2) - c가 null이 아니면 r1값으로, null이면 r2반환

nullif(c, d) - c와 d를 비교하여 같으면 null 다르면 c값 반환

 

decode() - if문과 같은 기능

decode(값1, 값2,'a', 값3,'b',...'e') - 값1과 같은 값의 문자열 반환, 같은 것이 없으면 마지막 문자열 반환

 

mod(c, n) - c를 n으로 나눈 나머지

 

max(col) - 조회 범위 내 해당 컬럼 들 중 최대값

select max(prod_sale) 최고판매가, min(prod_sale) 최저판매가

from prod

 

in - 질의 탐색을 위해 사용될 둘 이상의 표현식을 지정(not과 함께 사용 가능, not은 in앞에 사용)

where prod_sale in(150000, 170000, 330000)

 

select prod_id, prod_name, prod_lgu
from prod

where prod_lgu in (
        select lprod_gu from lprod where lprod_nm = '피혁잡화'
 );

 

exists - 부 질의를 매개변수로 받아서, 만약 이 부질의가 하나 이상의 행들을 돌려주면 true반환

             exists는 다수의 테이블과 연관관계에 있어서 in에 비해 용이하고 속도도 빠르다.

select prod_id, prod_name, prod_lgu
from prod
where  exists(
      select lprod_gu from lprod where lprod_gu = prod.prod_lgu and lprod_gu = 'P301'
);

 

sign(파라미터 or 식) - 파라미터 또는 식이 0보다 크면 1 반환, 0이면 0반환, 0보다 작으면 -1반환


출처 : http://www.cyworld.com/khyu81/229243