Oracle DB #4

OracleSQL 함수



<문자열 함수>

select substr(문자열, 시작위치, 길이) from dual; 
-문자열 추출

select substrb(문자열, 시작위치, 길이) from dual;
-byte 단위로 문자열 추출

select concat('홍', '길동') from dual;
-문자열 더함

select ltrim('  hello') from dual;
select rtrim('hello   ') from dual;
select trim('  hello  ') from dual;
-공백 제거

select lower(문자열) from dual;
select upper(문자열) from dual;
-소문자 변경, 대문자 변경 
(대소문자를 가리지 않는 검색의 용도로 사용)
사용 예)
select * from 테이블명 where upper(속성명)='영문찾을값';

select replace(문자열, 찾는문자열, 대치할문자열) from dual;
-문자열 대치
select translate(문자열, 찾는문자열, 대치할문자열) from dual;
-문자열 전체에서 찾는 문자열들을 찾아 대치할 문자열로 1:1대치(char기준)

select lpad('hello', 10, '0') from dual;
-문자열 패딩(문자열 포함 왼쪽까지 10개 공간을 만들고 공백은 0으로 채움)
//00000hello  문자열 공간은 Byte 기준으로 계산한다
select rpad('hello', 10, '0') from dual;
-문자열 패딩(문자열 포함 오른쪽까지 10개 공간을 만들고 공백은 0으로 채움
//hello00000  문자열 공간은 Byte 기준으로 계산한다

select initcap(문자열) from dual;
-모든 단어의 첫글자를 대문자로 대치

select instr(문자열, 검색문자열, 찾기시작위치, 찾을 수) from dual;
-찾는 문자열의 위치를 숫자로 반환

select length(문자열) from dual;
-문자열의 길이

select length(replace('010-1234-5678','-','')) from dual;
-함수 중첩

select ascii('A') from dual;
-코드값 반환

select chr(65) from dual;
-코드값으로 문자 반환

전화번호 중간자리 찾기
select substr(phone, 5, instr(phone, '-', 1, 2)-instr(phone,'-',1,1)-1) from members;



<숫자 함수>

select abs(-65) from dual;
-절대값 출력

select sign(35), sign(-35), sign(0) from dual;
-음수/양수를 알려주는 함수 (양수는 1, 음수는 -1, 0은 0을 출력)

select round(34.45678) from dual;
-반올림 함수
select round(34.45678, 소수점위치) from dual;
-소수점 위치까지 반올림하여 출력

select trunc(17/5) 몫, mod(17, 5) 나머지 from dual;
-몫과 나머지를 구하는 함수
-trunc의 경우 소수점을 날리고 싶을 때 사용 가능

select power(5, 2), sqrt(25) from dual;
-제곱과 제곱근을 구하는 함수



<날짜 함수>

select sysdate, current_date, systimestamp, current_timestamp from dual;
sysdate 
-Db서버 기반 연월일시분초
current_date 
-사용자 세션 설정에 따른 연월일시분초
systimestamp 
-Db서버 기반 밀리세컨드 단위로 정교하게 구할 때 사용
current_timestamp 
-사용자 세션 설정에 따른 밀리세컨드 단위로 정교하게 구할 때 사용
alter session set time_zone = '09:00'; 
-타임존 설정 09:00 한국
alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
-데이터 포멧 설정 

extract(year/month/day/hour/minute/second)
select extract(year from sysdate) from dual;
-날짜 추출 함수(위는 연단위를 추출)

select add_months(날짜, 정수) from dual;
-날짜 누적 함수

select months_between(날짜, 날짜) from dual;
-날짜 차이 함수

select next_day(현재날짜, 다음요일) from dual;
-다음 요일을 알려주는 함수

select last_day(날짜) from dual;
-월의 마지막 일자를 알려주는 함수

select round(날짜, 포멧), trunc(날짜, 포멧) from dual;
-지정된 범위에서 날짜를 반올림/자르는 함수
포맷 : 'CC', 'YEAR', 'Q', 'MONTH', 'W', 'DAY', 'D', 'HH', 'MI'



<변환 함수>

숫자 => to_char() => 문자열 => to_date() => 날짜
날짜 => to_char() => 문자열 => to_number() => 숫자

 

=NUMBER 형식을 문자열(VARCHAR2)로 변환=
select to_char(number) from dual;
to_char(number, 포맷문자) 
-원하는 형식으로 변환하고 싶을때 포맷문자를 넣어준다
포맷 : 
9 숫자  
0 빈자리를 채우는 문자  
$ 앞에 $ 표시 (달러를 표시해주고 싶다면, 우리나라면 보통 뒤에 || '원'을 붙인다)
, 천 단위 구분자 표시
. 소수점 표시
포맷문자는 숫자 길이보다 길어야함
사용예)
select to_char(1234567.8, '09,999,9.90') || '원' from dual;

 

=DATE 형식을 문자열(VARCHAR2)로 변환=
select to_char(datetime) from dual;
select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') from dual;
포맷 :
YYYY/RRRR/YY/YEAR 년도표시:4자리/Y2K/2자리/영문
MM/MON/MONTH 월표시:2자리/영문3자리/영문전체
DD/DAY/DDTH 일표시:2자리/영문/2자리ST
AM/PM 오전/오후표시
HH/HH24 시간표시:12시간/24시간
MI 분표시:0~59분
SS 초표시:0~59초

 

=문자열을 DATE로 변환=
select to_date('2014-03-31 12:23:03', 'YYYY-MM-DD HH:MI:SS') from dual;
-기존 리터럴 형식을 사용하지 않을 경우 두번째 인자에 현재 사용하는 포맷형식을 넣어줘야한다

 

=문자열을 숫자로 변환=
select to_number('2') + 3 from dual;



<NULL 관련 함수>

NVL(NULL, 대체값)
-속성값이 null일 경우 대체값으로 반환

NVL2(입력값, NOTNULL대체값, NULL대체값)
-속성값이 null이 아닐 경우 NOTNULL대체값을, null일 경우 NULL대체값을 반환

NULLIF(값1, 값2)
-두 값이 같을 경우 NULL 반환, 그렇지 않을 경우 첫 번째 값 반환

DECODE(기준값, 비교값, 출력값, 비교값, 출력값)
-조건에 따른 값 선택
사용 예)
select decode(substr(phone, 1, 3), '011', 'sk', '016', 'kt', '기타') from members;



<SELECT문의 구절>

SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY
-좌에서 우로 순서가 바뀌어서는 안됨. 실질적 절차는 아래에 있다.
FROM               격자형 데이터 준비
WHERE            필터링
GROUP BY      집계
HAVING           집계자료 필터링(집계 이후 사용이기에 WHERE 사용 불가)
ORDER BY      정렬

ORDER BY 옵션
ASC 오름차, DESC 내림차
ex) select * FROM 테이블 WHERE 속성 like '박%' ORDER BY 속성1 asc, 속성2 asc;
-속성1로 오름차 정렬 후 같은 값이 있을 경우 속성2로 2차 정렬

집계 함수
SUM, MIN, MAX, COUNT, AVG

select count(id) from notice;
-NULL 값이 들어간 컬럼을 제외하고 갯수를 반환
select sum(hit) from notice;
-값 더하기
select avg(hit) from notice
-값들의 평균
MIN, MAX
-최소값 최대값

select writer_id, count(id) COUNT
from notice 
GROUP BY writer_id
ORDER BY COUNT DESC;
-갯수를 내림차순으로 정렬해서 작성자를 기준으로 작성자 아이디와 작성자별 레코드 갯수를 반환

=SELECT문 구절의 실제 절차=
FROM -> CONNECT BY -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY
좌에서 우로 진행되며 하위에서 진행된 별칭은 상위에서 실행될 수 없다
때문에 FROM 절에서 부여된 별칭은 이후 모든 절에서 사용 가능

ex) 회원별 게시글 수를 조회하시오. 단 게시글이 2미만인 레코드만 출력하시오

select writer_id, count(N.id) from notice N
where count(N.id) < 2
group by writer_id;
-에러! 집계함수(count)는 where절에 사용이 불가능하고, group by 이후에만 사용이 가능하다

select writer_id, count(N.id) from notice N
group by writer_id
HAVING count(N.id) < 2;
-WHERE 절을 빼고 GROUP BY절 다음에 HAVING 절을 사용함으로써 사용이 가능해진다



<순위 함수>

select ROW_NUMBER() OVER (ORDER BY 정렬기준컬럼), id, title, writer_id, regdate, hit 
from notice;
-ROWNUM을 출력한 뒤 ORDER BY로 정렬함으로써 생기는 ROWNUM 뒤섞임 문제를 해당 함수를 사용해 해결할 수 있다

select RANK() OVER (ORDER BY 정렬기준컬럼), id, title, writer_id, regdate, hit 
from notice;
-순위 매기기

select DENSE_RANK() OVER (ORDER BY 정렬기준컬럼), id, title, writer_id, regdate, hit
from notice;
-공동 등수 이후 다음 등수를 뛰어넘지 않도록 순위 매기기

select DENSE_RANK() OVER (PARTITION BY 집계컬럼 ORDER BY 정렬기준컬럼 DESC), id, title, writer_id, regdate, hit 
from notice;
-GROUP BY를 한 것처럼 집계를 추가해줄 수 있다



<서브 쿼리>
select * from ( select * from member order by regdate desc ) where rownum between 1 and 10;
-select 구절은 절차를 따라야 하므로 구절의 순서를 바꾸기 위해 서브쿼리를 사용하기도 한다
-from 이후 ( ) 로 묶인 구역이 서브쿼리 구절이다
-서브쿼리로 미리 정렬된 결과물로 다시 쿼리를 만들겠다는 의미이다

ex) 평균 나이 이상인 회원
select * from member
where age >= ( select avg(age) from member )



<조인(JOIN)>

INNER JOIN, OUTER JOIN, SELF JOIN, CROSS JOIN(Cartesian Product)

INNER JOIN
-좌우 테이블 중 서로 관계가 있는 것들로만 레코드를 만든다.
ex) select * from member INNER JOIN notice ON member.id = notice.writer_id;

(LEFT/RIGHT/FULL) OUTER JOIN
-사용하는 OUTER에 따라 관계가 없는 레코드 또한 포함시켜 레코드들을 만든다.

-빈값은 NULL 값이 들어간다.
-가장 자주 쓰임

SELF JOIN
-자기 자신을 JOIN, 별칭을 사용해 JOIN한다
-대댓글, 카테고리 내의 카테고리 등으로 쓰임

CROSS JOIN
-관계성 여부에 상관없이 두 테이블을 곱하기로 결과를 만든다
-테이블1: 레코드 2개, 테이블2: 레코드 3개라면 cross join할 경우 6개의 테이블이 만들어짐 
-자주 사용되지 않고, 의미있는 테이블을 만들어내지 않는다


<ANSI SQL (표준SQL) 과 ORACLE SQL의 차이점>

ASNI INNER JOIN
select N.id, N.title, M.name
from
member M
inner join notice N on M.id = N.writer_id
where m.id = 'newlec';

ORACLE INNER JOIN
select N.id, N.title, M.name
from member M, notice N
where M.id = N.writer_id
and M.id = 'newlec';

ASNI OUTER JOIN
select N.*, M.name writer_name
from notice N left outer join member M
on M.id = N.writer_id

ORACLE OUTER JOIN
select N.*, M.name writer_name
from notice N, member M
where N.writer_id = M.id(+)

-oracleSQL의 left outer는 주가되는 테이블 반대 테이블에 (+)를 붙임
-오라클SQL은 full outer join은 지원하지 않음
-오라클SQL join은 ANSISQL join과 다르게 단순하나 절차가 명확하지가 않다


<필드 이름의 충돌문제>

select NOTICE.id, NOTICE.name, MEMBER.name from
member
inner join notice on member.id = notice.writer_id
-명시적으로 지칭하는 컬럼을 알려줌으로써 해결한다(대문자 부분)

select N.ID, N.WERITE_ID, M.NAME
from member M inner join notice N
on M.ID = N.WRITE_ID
-테이블 별칭 사용으로 해결한다

ex) id, name 그리고 회원별 작성한 게시글 수를 조회하시오
select m.id, m.name, count(n.id) from
member m left outer join notice n
on m.id = n.writer_id
group by m.id, m.name;
-회원에 포함되나 게시글 수가 0인 회원 또한 포함해야하므로 outer join을 사용하는 게 범용적인 사용법이다