[오라클 기본 쿼리 및 함수] (ORACLE QUERY) 기본 쿼리, 기본쿼리 함수

Notice

개발을 여행하는, 여행을 개발하는 블로그입니다⋯

Recent Posts

Recent Comments

Link

« 2025/01 »
일	월	화	수	목	금	토
			1	2	3	4
5	6	7	8	9	10	11
12	13	14	15	16	17	18
19	20	21	22	23	24	25
26	27	28	29	30	31

Tags more

Archives

Today

Total

관리 메뉴

DEVELOPER NOMAD

[오라클 기본 쿼리 및 함수] (ORACLE QUERY) 기본 쿼리, 기본쿼리 함수 본문

ORACLE(오라클)/ORACLE(오라클) 실습

[오라클 기본 쿼리 및 함수] (ORACLE QUERY) 기본 쿼리, 기본쿼리 함수

DEVELOPER NOMAD 2019. 1. 8. 15:28

오라클 기본 쿼리 실습 예제들과 기본쿼리 예제, 함수 모음 입니다.

//*****************************************

-- 오라클 데이터베이스 및 SID 확인(SYS 계정)

-- SID 란?

-- System Identifier 의 약자로 데이터베이스를 식별함에 있어 고유한 아이디

-- 데이타베이스가 하나만으로 구성 되어 있다면 데이타베이스명이 SID가 된다. 하지만 RAC 로 구성하여 데이타베이스 두개가 동시 가동되는 경우라면 SID 가 서로 다를 수 있기 때문에 중복 확인해야 한다.

-- DB연동을 위하여 필요한 naming 이다.

DBMS 서버를 기동하기 위해서는 DB서버가 기동하는 서버의 IP 그리고 DB서버가 접속을 받아들이기 위한 프로토콜에 대한 정의가 필요하다. 오라클의 경우 인스턴스가 서버 역할을 하는 DBMS 프로세스인데, 인스턴스가 기동할때 SID를 필요로 한다. 즉 SID는 인스턴스의 이름이다.

그리고 SID정보는 환경변수와, LISTENER.ORA라는 파일에서 정의 된다. listener.ora 파일을 열어보면

(SID_NAME = xe)

라고 기술된 부분이 있는데 이것이 SID이다.

-- 오라클 데이타베이스명을 확인(XE)

SQL>SELECT NAME, DB_UNIQUE_NAME FROM v$database;

-- 오라클 SID를 확인(xe)

SQL>SELECT instance FROM v$thread;

//*****************************************

-- SQLPLUS 실행(일반사용자)

CMD>sqlplus 사용자명/"암호"

-- 테이블 목록 확인

SQL>SELECT * FROM TAB;

-- 테이블 구조 확인

SQL>DESC[RIBE] 테이블명;

SQL>SELECT * FROM COL WHERE tname='테이블명';

-- 테이블명은 반드시 대문자로

//*****************************************

-- iSQL*Plus 실행(일반 사용자)

http://컴퓨터명:5560/isqlplus/

-- em 실행(관리자 영역)

http://컴퓨터명:1158/em

-- tnsnames.ora 에 정의된 connect_identifier 으로 접속

cmd>sqlplus 사용자명/"암호"@orcl

-- orcl : tnsnames.ora 에 정의된 connect_identifier

//*****************************************

-- sql 에서 cmd 창으로 잠시 빠져 나가야 하는 경우

sql>HOST

cmd>EXIT -- SQL 로 복귀

//*****************************************

-- sqlplus 에서 *.sql 파일 실행

-- 파일이 존재하는 경로에서 sqlplus를 실행하고,

-- "@파일명" 명령을 실행하면 된다.

sql>@ex.sql

-- 경로를 지정하여 실행

sql>@d:\sql\ex.sql

-- sqlplus 에서 sql 작업 내용 자동 저장하기

sql>spool d:\sql\test.txt

sql>SELECT * FROM insa; -- d:\sql\test.txt 파일 생성

sql>spool off -- 스풀 정지

//*****************************************

-- 오라클 쿼리 실행 시간 출력

실행시간 시작 : SET TIMING ON

실행시간 종료 : SET TIMING OFF

sql>SET TIMING ON

sql>SELECT * FROM tab;

//*****************************************

오라클 명령은 대소문자를 구분하지 않는다. 하지만 리터널(상수)는 대소문자를 구분한다.

//*****************************************

-- 산술연산자

+ : 덧셈 - : 뺄셈 * : 곱셈 / : 나눗셈 ( ) : 괄호

-- 비교연산자

= : 같다. > : 크다. >= : 크거나 같다.

< : 적다. <= : 작거나 같다. <> : 같지 않다. != : 같지 않다.

-- 논리 연산자

AND : 논리 곱 OR : 논리 합 NOT : 부정

-- SQL 연산자

IN(값, 값, ...) : 피연산자가 식 목록 중 하나와 동일한 경우 TRUE

BETWEEN ~ AND: 피연산자가 범위 안에 있는 경우 TRUE

LIKE : 피연산자가 패턴과 일치하는 경우 TRUE

-- 문자열 연결 연산자

|| : 문자열 연결

' 를 출력할 경우에는 '을 두번 쓴다.

SELECT 'test''s' FROM insa;

SELECT name || '''' FROM insa;

//*****************************************

-- 자료 검색

-- 형식 : SELECT 필드명, 필드명 ..... FROM 테이블

- 실행 순서

FROM 절 -> WHERE 절 -> ROWNUM 할당 -> GROUP BY 절 -> HAVING -> SELECT 절 -> ORDER BY 절

-- 필드명 변경(AS는 생략가능. 별명에서 띄어쓰기등을 하는 경우에는 "" 안에 기술)

형식 : SELECT 필드명 AS 별명, 필드명 AS 별명 FROM 테이블

//*****************************************

-- 조건 검색

형식 : SELECT 필드명, 필드명 ..., 필드명 FROM 테이블 WHERE 조건

//*****************************************

-- 테이블 구조 확인

DESC 테이블명;

SELECT * FROM col WHERE tname='테이블명';

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

-- 테이블 목록 확인

SELECT * FROM tab;

-- 테이블 구조 확인

DESC insa;

SELECT * FROM col WHERE tname='INSA';

-- 전체출력

SELECT * FROM insa;

-- name, tel, buseo 컬럼

SELECT name, tel, buseo

FROM insa;

-- 컬럼명 변경

SELECT name AS 이름, tel AS 전화, buseo AS 부서명

FROM insa;

-- AS 생략가능

SELECT name 이름, tel 전화, buseo 부서명

FROM insa;

SELECT name 이름, tel 전화, buseo "부서 명"

FROM insa;

-- 연산

SELECT name, basicpay FROM insa;

SELECT name||'님', basicpay/10000 FROM insa;

SELECT name||'님' AS name, basicpay/10000 pay FROM insa;

-- 조건검색

-- city가 서울인 사람의 name, city, basicpay 출력

SELECT name, city, basicpay

FROM insa

WHERE city='서울';

-- basicpay+sudang 가 250만원 이상인 name, basicpay, sudang, basicpay+sudang 출력

SELECT name, basicpay, sudang, basicpay+sudang pay

FROM insa

WHERE basicpay+sudang>=2500000;

SELECT name, basicpay, sudang, basicpay+sudang pay

FROM insa

WHERE pay>=2500000; -- (X)

-- basicpay+sudang 가 200만원 이상이면서 city가 서울인 name, basicpay, sudang, basicpay+sudang, city 출력

SELECT name, basicpay, sudang, basicpay+sudang pay, city

FROM insa

WHERE basicpay+sudang>=2000000 AND city='서울';

//*****************************************

-- DUAL

- DUAL 테이블은 데이터 딕셔너리와 함께 Oracle에 의해 자동으로 생성되는 테이블로 사용자 SYS의 스키마에 존재하며, 모든 사용자가 사용 가능하다.

SELECT * FROM dual;

//*****************************************

-- 단일행(single row, 스칼라) 함수

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

-- 문자함수

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

-- SUBSTR (char, m [,n]) : 문자열 추출. 인덱스는 1부터 시작한다.

SELECT SUBSTR('seoul korea', 7, 3) FROM insa;

SELECT SUBSTR('seoul korea', 7, 3) FROM dual; -- kor

SELECT SUBSTR('seoul korea', -5, 3) FROM dual; -- kor

SELECT SUBSTR('seoul korea', 7) FROM dual; -- korea

-- insa 테이블중 name, city, ssn 을 출력하되 city가 서울이면서 주민번호(ssn이용)이용하여 남자인 사람만 출력

SELECT name, city, ssn

FROM insa

WHERE city='서울' AND (SUBSTR(ssn, 8, 1) = '1' OR SUBSTR(ssn, 8, 1) = '3');

SELECT name, city, ssn

FROM insa

WHERE city='서울' AND (SUBSTR(ssn, 8, 1) = '1' OR '3'); -- (X)

SELECT name, city, ssn

FROM insa

WHERE city='서울' AND SUBSTR(ssn, 8, 1) IN ('1', '3', '5', '7');

SELECT name, city, basicpay

FROM insa

WHERE city='서울' OR city='부산' OR city='경기';

SELECT name, city, basicpay

FROM insa

WHERE city IN ('서울', '부산', '경기');

-- name, city, ssn 컬럼출력. 단, 70년대 사람만(ssn 이용)

SELECT name, city, ssn

FROM insa

WHERE SUBSTR(ssn, 1, 2)>=70 AND SUBSTR(ssn, 1, 2)<=79;

SELECT name, city, ssn

FROM insa

WHERE SUBSTR(ssn, 1, 1)=7;

-- name, city, ssn 컬럼출력. 단, 김씨중 서울사람만 출력

SELECT name, city, ssn

FROM insa

WHERE city='서울' AND SUBSTR(name, 1, 1)='김';

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

-- LENGTH (column | expression) : 문자열 길이

LENGTHB (column | expression) : 문자열 바이트수

-- 11g에서는 한글은 UTF-8로 처리(한글 1자는 3byte)

SELECT LENGTH('대한민국') FROM dual; -- 4

SELECT LENGTHB('대한민국') FROM dual; -- 12

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

-- INSTR (column | expression, 'string' [,m] [,n])

문자의 위치를 반환한다. m값은 시작위치고, n값은 발생 횟수이며 m과 n의 기본 값은 1이다.

문자가 없으면 0을 반환한다.

SELECT INSTR('seoul korea', 'e') FROM dual; -- 2

SELECT INSTR('seoul korea', 'e', 7) FROM dual; -- 10

SELECT INSTR('seoul korea', 'e', 1, 2) FROM dual; -- 10

SELECT INSTR('seoul korea', 'abc') FROM dual; -- 0

-- name, city 출력 : 성씨가 김인사람만 출력. INSTR 이용

SELECT name, city

FROM insa

WHERE INSTR(name, '김') = 1;

-- name, city 출력 : 이름(성씨포함)에 이 가 포함된 사람만 출력. INSTR 이용

SELECT name, city

FROM insa

WHERE INSTR(name, '이') >= 1;

-- 자바에서 분리하는게 효율적

SELECT name, tel FROM insa;

SELECT name, tel, SUBSTR(tel, 1, INSTR(tel, '-')-1) 서비스번호,

SUBSTR(tel, INSTR(tel, '-')+1,

INSTR(tel, '-', 1, 2)-INSTR(tel, '-')-1) 국번,

SUBSTR(tel, INSTR(tel, '-', 1, 2)+1) 번호

FROM insa;

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

-- LPAD (column | expression, n, ['string']), RPAD (column | expression, n, ['string'])

expression의 문자열을 제외한 공간에 문자열을 왼쪽(오른쪽)에 채운다.

SELECT LPAD('korea', 10, '*') FROM dual; -- *****korea

SELECT LPAD('korea', 3, '*') FROM dual; -- kor

SELECT LPAD('*', 0, '*') FROM dual; -- NULL

SELECT LPAD('대한', 6, '*') FROM dual; -- **대한

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

-- REPLACE (text, search_string [, replacement_string])

문자열 치환

SELECT REPLACE('seoul korea', 'seoul', 'busan') FROM dual;

SELECT REPLACE('12365845852588', '5') FROM dual; -- 5제거

SELECT name, buseo FROM insa;

SELECT name, REPLACE(buseo, '부') FROM insa;

SELECT name, REPLACE(buseo, '부', '팀') FROM insa; -- 문제가 있을 수 있음

SELECT name, SUBSTR(buseo, 1, LENGTH(buseo)-1) || '팀' FROM insa;

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

-- TRANSLATE ('char','from_string','to_string')

char 내에 포함된 문자중 from_string에 지정한 모든 각각의 문자를 to_string 문자로 각각 변경한다.

SELECT TRANSLATE('ababccc', 'c', 'd') FROM dual; -- ababddd

SELECT TRANSLATE('2dk5erw6', '0123456789abcdefghijklmnopqrstuvwxyz',

'9999999999**************************') FROM dual; -- 9**9***9

SELECT TRANSLATE('2dk5erw6', '0123456789abcdefghijklmnopqrstuvwxyz',

'0123456789') FROM dual; -- 256

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

-- RTRIM(char [,set]), LTRIM(char [,set])

TRIM (leading | trailing | both trim_character FROM trim_source)

공백을 제거하거나 오른쪽(왼쪽)의 문자열 제거

SELECT ':' || ' 우 리 ' || ':' FROM dual;

SELECT ':' || TRIM(' 우 리 ') || ':' FROM dual;

SELECT ':' || LTRIM(' 우 리 ') || ':' FROM dual;

SELECT ':' || RTRIM(' 우 리 ') || ':' FROM dual;

SELECT RTRIM('우리나라대한', '대한') FROM dual;

SELECT name, RTRIM(buseo, '부') || '팀' FROM insa;

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

-- UPPER(char) : 영문자 문자열을 모두 대문자로 변환

-- LOWER(char) : 영문자 문자열을 모두 소문자로 변환

-- ASCII(char) : 주어진 char의 첫 글자의 아스키 값을 반환

-- CHR(n) : 입력된 수의 바이너리 코드에 해당하는 문자를 반환

-- INITCAP(char) : 입력 문자열 중에서 각 단어의 첫 문자를 대문자로 나머지 문자는 소문자로 변환

SELECT CHR(75)||CHR(79)||CHR(82)||CHR(69)||CHR(65) FROM DUAL; -- korea

//*****************************************

-- 숫자함수

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

-- MOD(m, n) : m을 n으로 나눈 나머지

SELECT MOD(13, 2) FROM dual; -- 1

SELECT name, ssn FROM insa WHERE MOD(SUBSTR(ssn, 8, 1), 2) = 0; -- 여자

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

-- ROUND(n [,m]) : 반올림

SELECT ROUND(15.193, 1) FROM dual; -- 15.2

SELECT ROUND(15.193, 2) FROM dual; -- 15.19

SELECT ROUND(15.193, 0) FROM dual; -- 15

SELECT ROUND(15.193) FROM dual; -- 15

SELECT ROUND(15.193, -1) FROM dual; -- 20

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

-- TRUNC(n [,m]) : 절삭

SELECT TRUNC(15.193, 1) FROM dual; -- 15.1

SELECT TRUNC(15.193, -1) FROM dual; -- 10

-- name, basicpay, basicpay의 5만원권개수, basicpay 1만원권개수, 나머지 금액

-- name basicpay 5만원권개수 1만원권개수 나머지 금액

SELECT name, basicpay, TRUNC(basicpay/50000) "5만원권개수"

,TRUNC(MOD(basicpay, 50000) / 10000) "1만원권개수"

,MOD(basicpay, 10000) "나머지 금액"

FROM insa;

-- name, basicpay, 그래프(basicpay 10만원당 *하나)

SELECT LPAD('korea', 10, '*') FROM dual; -- *****korea

SELECT name, basicpay, LPAD('*', TRUNC(basicpay/100000), '*') 그래프

FROM insa;

//*****************************************

-- 암시적 형 변환

값을 할당할 때 오라클 서버는 다음과 같이 자동으로 값을 변환할 수 있다.

VARCHAR2, CHAR → NUMBER

VARCHAR2, CHAR → DATE

NUMBER → VARCHAR2

DATE → VARCHAR2

//*****************************************

-- 변환함수

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

-- TO_CHAR(label [, fmt]) : MLSLABEL datatype을 VARCHAR2 타입으로 변환

TO_CHAR(n [, fmt [, 'nlsparams'] ]) : 숫자를 문자로 변환(VARCHAR2 타입)

TO_CHAR(d [, fmt [, 'nlsparams'] ]) : 날짜를 문자로 변환(VARCHAR2 타입)

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

YYYY : 4자리 연도로 표시

YY : 끝의 2자리 연도로 표시(무조건 시스템 상의 년도)

RR :

- 현재년도가 0~49사이에 있고 표현할 연도가 0~49이면 현재 세기를 적용

- 현재년도가 0~49사이에 있고 표현할 연도가 50~99이면 (현재세기-1)를 적용

- 현재년도가 50~99사이에 있고 표현할 연도가 0~49이면 (현재세기+1)를 적용

- 현재년도가 50~99사이에 있고 표현할 연도가 50~99이면 현재 세기를 적용

- 현재 : 2015년, 연도 : 70 -> 1970년

- 현재 : 2015년, 연도 : 08 -> 2008년

YEAR : 연도를 알파벳으로 표시

MM : 달을 숫자로 표시

MON : 달을 알파벳 약어 로표시

MONTH : 달을 알파벳으로 표시

DD : 일자를 숫자로 표시

DAY : 일에 해당하는 요일

DY : 일에 해당하는 요일의 약어

D : 일에 해당하는 요일을 숫자로 표시(1~7)

HH : 12시간으로 표시(1-12)

HH24 : 24시간으로 표시(0-23)

MI : 분을 표시

SS : 초를 표시

AM(또는 PM) : 오전인지 오후인지를 표시

SELECT SYSDATE FROM dual;

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') FROM dual;

SELECT TO_CHAR(SYSDATE, 'YYYY"년" MM"월" DD"일"') FROM dual;

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD DAY HH24:MI:SS') FROM dual;

SELECT name, ibsadate FROM insa;

SELECT name, ibsadate FROM insa WHERE TO_CHAR(ibsadate, 'YYYY') = 2000;

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

9 : 숫자의 출력 자릿수를 지정. 값이 지정한 자릿수보다 작으면 공백으로 채워 표시. '999'는 숫자를 3자리로 출력

만약 자릿수가 부족하면 #으로 출력

0 : 자릿수가 비면 0을 채움. 123을 '0999'로 출력하면 0123출력

$ : 숫자앞에 달러를 붙인다.

L : 국가별 화페를 출력.

. : 소수점(한 위치에 마침표 표기)

TO_CHAR(1234,'99999.99') -> 1234.00

, : 천단위로 ,을 출력(명시한 위치에 컴마 표기).

TO_CHAR(1234,'99,999') -> 1,234

PR : 음수를 괄호(<>)로 묶음

MI : 음수인 경우 뒤에 '-' 기호를 표시하며 양수인 경우 뒤에 공백

V : 10을 V 뒤의 수만큼 곱함

EEEE : 지수 표기법으로 표시

SELECT name, basicpay FROM insa;

SELECT name, TO_CHAR(basicpay, '9,999,999') FROM insa;

SELECT name, TO_CHAR(basicpay, 'L9,999,999') FROM insa;

SELECT name, TO_CHAR(basicpay, '999,999') FROM insa; -- 부족하면 #######

SELECT TO_CHAR(12345, '0,999,999') FROM dual; -- 0,012,345

SELECT TO_CHAR(12345, '999') FROM dual; -- ###

SELECT TO_CHAR(123.45, '990.0') FROM dual; -- 123.5

SELECT TO_CHAR(123.45, '999.9') FROM dual; -- 123.5

SELECT TO_CHAR(123, '999.9') FROM dual; -- 123.0

SELECT TO_CHAR(1234, '9999MI') FROM dual; -- 1234

SELECT TO_CHAR(-1234, '9999MI') FROM dual; -- 1234-

SELECT TO_CHAR(-1234, '9999PR') FROM dual; -- <1234>

SELECT TO_CHAR(1234.345, '9.999EEEE') FROM dual; -- 1.234E+03

SELECT TO_CHAR(1234, '99999V9999') FROM dual; -- 123450000

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

-- 통화기호, 날짜 등 출력 형식 변경

SELECT parameter, value FROM NLS_SESSION_PARAMETERS; -- 확인

ALTER SESSION SET NLS_LANGUAGE = 'KOREAN';

ALTER SESSION SET NLS_CURRENCY = '\';

ALTER SESSION SET NLS_DATE_LANGUAGE = 'KOREAN';

-- 날짜형식 변경(기본:RR/MM/DD)

ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD';

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

-- TO_NUMBER(char [,fmt [, 'nlsparams'] ]) : 문자를 숫자로 변환

SELECT '12'+20 FROM dual; -- 32

SELECT '1,200'+20 FROM dual; -- 에러

SELECT TO_NUMBER('1,200', '9,999')+20 FROM dual;

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

-- TO_DATE(char [, fmt [, 'nlsparams'] ]) : 문자를 날짜로 변환

SELECT TO_CHAR(SYSDATE, 'DD-MON-RR') FROM dual; -- 02-1월-19

SELECT TO_DATE('01-JUL-08', 'DD-MON-RR') FROM dual; -- 날짜 형식이 미국식인 경우 가능

SELECT TO_DATE('01-10-90', 'MM-DD-RR') FROM dual; -- 90/01/10

SELECT name, ssn FROM insa;

SELECT name, SUBSTR(ssn, 1, 6) birth FROM insa;

SELECT name, TO_DATE(SUBSTR(ssn, 1, 6), 'RRMMDD') birth FROM insa;

SELECT name, TO_DATE(SUBSTR(ssn, 1, 6)) birth FROM insa; -- 가능

SELECT name, TO_CHAR(TO_DATE(SUBSTR(ssn, 1, 6)), 'YYYY-MM-DD') birth FROM insa;

SELECT TO_CHAR(SYSDATE, 'MON DD DAY') FROM dual;

SELECT TO_CHAR(SYSDATE, 'MON DD DAY', 'NLS_DATE_LANGUAGE=American') FROM dual;

SELECT TO_CHAR(SYSDATE, 'MON DD DAY', 'NLS_DATE_LANGUAGE=Korean') FROM dual;

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

-- ASCIISTR(string) : string의 아스키 문자로 반환. non-ASCII 문자는 UTF-16 code로 반환.

SELECT ASCIISTR('kor') FROM dual; -- kor

SELECT ASCIISTR('대한') FROM dual; -- \B300\AD6D

//*****************************************

-- 날짜 및 날짜함수

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

-- 날짜에 산술 연산을 사용하는 경우

날짜 + 숫자 → 날짜 : 날짜에 일수를 더하여 날짜 계산

날짜 - 숫자 → 날짜 : 날짜에 일수를 감하여 날짜 계산

날짜 + 숫자/24 → 날짜 : 날짜에 시간을 더하여 날짜 계산

날짜 - 날짜 → 일수 : 날짜에 날짜를 감하여 일수 계산

-- 살아온날수

SELECT TRUNC(SYSDATE - TO_DATE('20001010', 'YYYYMMDD')) 날수 FROM dual;

-- 재민씨가 2018-10-10일에 여자친구를 사귀기 시작했다. 100일후는 ??

SELECT TO_DATE('2018-10-10', 'YYYY-MM-DD') + 100 FROM dual;

-- 2019-05-16 까지 몇일 ?

SELECT TRUNC(TO_DATE('2019-05-16', 'YYYY-MM-DD') - SYSDATE) FROM dual;

-- 현재 시간 - 1시간

SELECT TO_CHAR(SYSDATE - 1/24, 'YYYYMMDD HH24:MI:SS') FROM dual;

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

-- SYSDATE : 시스템에 저장된 현재 날짜를 반환

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

-- CURRENT_DATE : 현재 session의 날짜 정보를 반환

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

-- ADD_MONTHS(d, n) : 해당 날짜에 n만큼의 달수를 더한다.

기준이 되는 날짜가 '말일'일 경우는, 계산된 값도 해당 월의 말일을 반환한다.

SELECT ADD_MONTHS(TO_DATE('2019-03-31', 'YYYY-MM-DD'), 1) FROM dual;

-- 2019-04-30

SELECT CURRENT_DATE "today", ADD_MONTHS(SYSDATE, 1) "next month" FROM dual;

2014-03-30, 2014-03-31 ==> 6개월 후 ==> 2014-09-30, 2014-09-30

SELECT ADD_MONTHS(TO_DATE('2014-03-30', 'YYYY-MM-DD'), 6) FROM dual;

SELECT ADD_MONTHS(SYSDATE, -1) 지난달 FROM dual;

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

-- LAST_DAY(d) : 정의된 날짜의 달에서 마지막일이 몇일인지 돌려준다.

SELECT LAST_DAY(SYSDATE) FROM dual;

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

-- MONTHS_BETWEEN(d1, d2) : 정의된 두 날짜사이의 차이(d1 - d2)를 월로 돌려준다.

SELECT MONTHS_BETWEEN(SYSDATE, TO_DATE('180708')) FROM dual;

-- SYSDATE : 19-01-03 ==> 5.xxx

-- name, ssn, birth, age

SELECT name, ssn, TO_DATE(SUBSTR(ssn, 1, 6), 'RRMMDD') birth,

TRUNC(MONTHS_BETWEEN(SYSDATE, TO_DATE(SUBSTR(ssn, 1, 6)))/12) age

FROM insa;

-- name, ssn, birth, age, ibsadate, 근속년수

SELECT name, ssn, TO_DATE(SUBSTR(ssn, 1, 6), 'RRMMDD') birth,

TRUNC(MONTHS_BETWEEN(SYSDATE, TO_DATE(SUBSTR(ssn, 1, 6)))/12) age,

ibsadate, TRUNC(MONTHS_BETWEEN(SYSDATE, ibsadate)/12) 근속년수

FROM insa;

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

-- ROUND(d [,fmt]) : 정해진 날짜를 fmt를 기준으로 반올림

-- 년도 기준은 7월 1일부터 반올림

SELECT ROUND(TO_DATE('070710'), 'YEAR') FROM dual; -- 2008-01-01

SELECT ROUND(TO_DATE('070630'), 'YEAR') FROM dual; -- 2007-01-01

-- 월의 기준은 16일 기준

SELECT ROUND(TO_DATE('070720'), 'MONTH') FROM dual; -- 2007-08-01

SELECT ROUND(TO_DATE('070715'), 'MONTH') FROM dual; -- 2007-07-01

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

-- TRUNC(d,[fmt]) : 정해진 날짜를 fmt를 기준으로 반 내림

SELECT TRUNC(TO_DATE('070710'), 'YEAR') FROM dual; -- 2007-01-01

SELECT name, ibsadate, TRUNC(ibsadate, 'MONTH') FROM insa;

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

-- NEXT_DAY(d, char)

명시된 요일(char)이 돌아오는 날짜를 계산한다. 요일은 숫자로 표현 가능(SUNDAY : 1, MONDAY : 2, ...)

SELECT SYSDATE, NEXT_DAY(SYSDATE, '월요일') FROM dual;

SELECT SYSDATE, NEXT_DAY(SYSDATE, 2) FROM dual;

-- 시스템 날짜를 기준으로 주의 시작, 오늘날짜, 주의 마지막 날짜 출력

SELECT CASE

WHEN TO_CHAR(SYSDATE, 'D') = 1 THEN SYSDATE -- 일요일이면 1

ELSE NEXT_DAY(SYSDATE, 1)-7

END 주시작,

SYSDATE,

CASE

WHEN TO_CHAR(SYSDATE, 'D') = 7 THEN SYSDATE

ELSE NEXT_DAY(SYSDATE, 7)

END 주끝

FROM dual;

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

-- EXTRACT ({year|month|day|hour|minute|second|

timezone_hour|timezone_minute|

timezone_region|timezone_abbr}

FROM {datetime_value_expr|interval_value_rxpr})

특정 날짜/시간 값이나 날짜 값을 가진 표현식으로 부터 원하는 날짜 영역을 추출하여 출력한다.

SELECT EXTRACT(YEAR FROM SYSDATE) FROM dual;

SELECT TO_CHAR(SYSDATE, 'YYYY') FROM dual;

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

-- 오늘 0시0분0초

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM dual;

SELECT TO_CHAR(TRUNC(SYSDATE, 'DD'), 'YYYY-MM-DD HH24:MI:SS') FROM dual;

-- 내일 0시 0분 0초

SELECT TO_CHAR(TRUNC(SYSDATE, 'DD')+1, 'YYYY-MM-DD HH24:MI:SS') FROM dual;

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

-- TIMESTAMP형

초단위 이하의 밀리세컨드를 처리하기 위해 사용

DATE형은 초까지만 처리

- 밀리세컨드를 3자리로 표현할 경우는 FF3, 4자리로 표현할 경우는 FF4로 나타냄.

- 현재시간을 입력 할 경우 DATE형은 SYSDATE, TIMESTAMP는 SYSTIMESTAMP를 사용

SELECT TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS.FF4') FROM dual;

SELECT TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS.FF3') FROM dual;

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

SELECT SYSDATE + 1 FROM dual;

SELECT SYSDATE + (INTERVAL '1' YEAR) FROM dual;

SELECT SYSDATE + (INTERVAL '1' MONTH) FROM dual;

SELECT SYSDATE + (INTERVAL '1' DAY) FROM dual;

SELECT TO_CHAR(SYSDATE + (INTERVAL '1' HOUR), 'YYYY-MM-DD HH24:MI:SS') FROM dual;

SELECT TO_CHAR(SYSDATE + (INTERVAL '02:10' HOUR TO MINUTE), 'YYYY-MM-DD HH24:MI:SS') FROM dual;

SELECT TO_CHAR(SYSDATE + (INTERVAL '01:30' MINUTE TO SECOND), 'YYYY-MM-DD HH24:MI:SS') FROM dual;

SELECT SYSDATE - (INTERVAL '3' YEAR) FROM dual;

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

'2010-10-10 10:10:10' '2010-10-02 09:09:09'

-- 두날짜 사이의 간격을 초로 환산하여 출력

SELECT (TO_DATE('2010-10-10 10:10:10', 'YYYY-MM-DD HH24:MI:SS') -

TO_DATE('2010-10-02 09:09:09', 'YYYY-MM-DD HH24:MI:SS')) * 24 * 60 * 60

FROM dual;

//*****************************************

-- NULL : 값이 존재하지 않은 상태. 오라클에서는 ''(문자길이가 0인경우) 도 널이다

IS NULL : NULL 이면 참

IS NOT NULL : NULL 이 아니면 참

SELECT name, tel FROM insa;

SELECT name, tel FROM insa WHERE tel = NULL; -- (X)

SELECT name, tel FROM insa WHERE tel IS NULL;

SELECT name, tel FROM insa WHERE tel IS NOT NULL;

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

-- NULL 관련 함수

NVL(expr1, expr2) : expr1이 Null 이면 expr2 반환하고 Null이 아니면 expr1 반환

NVL2(expr1, expr2, expr3) : expr1이 null이 아니면 expr2를 반환하고, null이면 expr3를 반환

NULLIF(expr1, expr2) : 두 표현식을 비교해서 같으면 null 반환하고, 같지 않으면 expr1을 반환

COALESCE(expr1, expr2, ……, exprn) : 표현식 목록에서 첫 번째로 null이 아닌 것을 반환한다. 즉, expr1이 널이 아니면 expr1을 반환하며, expr1이 널이고 expr2가 널이 아니면 expr2를 반환한다.

LNNVL(조건) : 조건이 거짓이거나 값이 존재하지 않으면 TRUE를 리턴하며, 조건이 거짓이면 FALSE를 리턴 한다. SELECT 리스트에서는 사용할 수 없으며 WHERE 절에서만 사용 가능 하다.

SELECT name, tel FROM insa ;

SELECT name, NVL(tel, '없다') tel FROM insa ;

SELECT name, NVL2(tel, '있다', '없다') tel FROM insa ;

SELECT name, NVL2(tel, tel, '없다') tel FROM insa ;

SELECT NULLIF(1, 1) FROM dual;

SELECT NULLIF(1, 0) FROM dual;

SELECT COALESCE(null, 3, null, 5) FROM dual;

SELECT 10+NULL FROM dual; --> null

SELECT 10+NVL(null, 0) FROM dual; --> 10

//*****************************************

-- ORDER BY : 정렬

- ASC : 오름차순, DESC : 내림차순

- 첫번째 행 부터 NULL 값 정렬

ORDER BY 컬럼명 NULLS FIRST;

- NULL 값을 마지막 행으로 정렬

ORDER BY 컬럼명 NULLS LAST;

- ORDER BY 구문 활용시 단순 칼럼명으로 정렬하는것이 아니라 특정칼럼의 특정값을 먼저 오게 하는것처럼 정렬순서를 변경하고 싶은경우 ORDER BY 구문과 DECODE 혹은 CASE WHEN 구문을 함께 활용하면 된다.