2. 함수

오라클 내장 함수

오라클이 자체적으로 제공하는 함수

오라클 함수의 분류

단일 행 함수
- 하나의 행 값이 함수에 적용되어 하나의 행으로 리턴된다.
- 함수를 적용할 때나 안할 때나 반환되는 ROW 수는 같다
- SUBSTR 단일 문자행 함수
GROUP 함수
- 하나 이상의 행 값이 적용되어 하나의 값이 리턴된다.
- WHERE 조건절이 없어도 실제 존재하는 ROW 보다 결과로 나온 ROW 수가 적을 수도 있다.

공통점은 결과로 반환하는 ROW 는 하나다.

단일 행 함수 - 1. 문자 함수#

LOWER(CHAR)#

지정된 문자를 소문자로 변환하여 리턴

UPPER(CHAR)#

지정된 문자를 대문자로 변환하여 리턴

INITCAP(CAHR)#

지정된 문자 스트링의 단어 첫 자는 대문자,
나머지는 소문자로 바꾸어 리턴

LENGTH(컬럼or문자열)#

주어진 문자열의 길이를 돌려준다.

LENGTHB 가 존재한다.

문자열 내에 존재하는 한글이 몇 글자인지 검색

SELECT LENGTHB('ABC좋은나라DEF웃긴나라')
    - LENGTH('ABC좋은나라DEF웃긴나라')
FROM DUAL;

LPAD(CHAR1, n[,CHAR2])#

CHAR1 좌측에 CHAR2 문자를 채워서 n 자리로 만들어 리턴

문자열(컬럼)이 n 크기가 되도록 문자열의 왼쪽에 지정한 문자 CHAR2 을 채워준다.

SELECT LPAD(DEPT_NAME, 10, '1234456789')
FROM TDEPT;

-- LPAD(DEPT_NAME, 10, '12
-- -----------------------
-- 12경영지원
-- 123456재무

RPAD(CHAR1, n[, CHAR2])#

CHAR2 우측에 CHAR2 문자를 채워서 n 자리로 만들어 리턴

SUBSTR(CHAR, m[, n])#

CHAR 의 m 번째 문자에서부터 n 개의 문자를 리턴

INSTRB 와 마찬가지로 SUBSTRB 가 존재한다.

SUBSTR(컬럼or문자열, 숫자1, 숫자2) - 두번째 숫자가 생략되면 마지막 문자까지 리턴

SELECT SUBSTR('ABC좋은나라DEF웃긴나라', 2, 10)
FROM DUAL;

-- SUBSTR('ABC좋은
-- ---------------
-- BC좋은나라DEF웃

첫번째 인자가 음수 → 뒤에서 부터 자리수를 센다

SELECT SUBSTR('ABC좋은나라DEF웃긴나라', -4, 2)
FROM DUAL;

-- SUBS
-- -------
-- 웃긴

INSTR(CHAR, CHAR2)#

INSTR(*컬럼or문자열, 문자*) - CHAR1 에서 CHAR2 가 최초로 나타나는 위치

SELECT  INSTR('ABC좋은나라DEF웃긴나라', 'D'),
        INSTR('ABC좋은나라DEF웃긴나라', '나')
FROM DUAL;

-- INSTR('ABC   INSTR('ABC
-- -----------------------
--   8           6

INSTRB - BYTE 단위로 값 처리

SELECT  INSTRB('ABC좋은나라DEF웃긴나라', 'D'),
        INSTRB('ABC좋은나라DEF웃긴나라', '나')
FROM DUAL;

-- INSTR('ABC   INSTR('ABC
-- -----------------------
--   12           8

INSTR(CHAR1, CHAR2, n, m) - 문자열 n 번째 자리부터 검색, m번째 나타나는 CHAR2 위치 검색

SELECT  INSTRB('ABC좋은나라DEF웃긴나라', '나', 7, 1)
FROM DUAL;

-- INSTR('ABC
-- ------------
--   13

찾는 문자가 없을 경우, 0 반환

SELECT  INSTRB('ABC좋은나라DEF웃긴나라', '다')
FROM DUAL;

-- INSTR('ABC
-- ------------
--   0

DUAL TABLE

SYS USER 가 소유하고 있는 단일 행에 단일 컬럼만을 가지는 테이블
일시적 산술 연산이나 날짜 연산을 위해 사용한다.

LTRIM(CHAR, [,SET])#

SET 이 아닌 최초의 문자가 나타날 때까지 죄측으로부터 문자를 삭제

RTRIM(CHAR, [, SET)#

SET 이 아는 최초의 문자가 나타날 때까지 지정된 값의 우측에서 문자를 삭제

SOUNDEX(CHAR)#

지정한 단어와 발음이 동일한 문자열을 리턴

주로 조건절에서 사용

TRANSLATE(CHAR, S1, S2)#

모든 S1 을 S2 로 치환 후 리턴

CHR(CHAR)#

지정된 수치와 일치하는 ASCII 코드를 리턴

SELECT CHAR(65) FROM DUAL;

-- C 
-- -
-- A

ASCII('CHAR')#

지정된 문자의 ASCII 값을 돌려준다.

SELECT ASCII('A') FROM DUAL;

-- ASCII('A')
-- ---------
-- 65

개행 문자의 ASCII 코드 이용

SELECT EMP_ID || CHR(10) || EMP_NAME EMP
FROM TEMP;

-- EMP
-- -----------
-- 19970101
-- 김길동
-- 19960101
-- 홍길동

REPLACE(CHAR, STR1, STR2)#

CHAR 에서 STR1을 모두 STR2 로 치환 후 리턴

STR2를 지정하지 않으면 STR1 을 모두 삭제

SELECT REPLACE(REPLACE('ABC좋은나라DEF웃긴나라', 'ABC', ''), 'DEF', '')
FROM DUAL;

단일 행 함수 - 2. 숫자 함수#

ROUND(n, [, m])#

n 을 소수점 아래 m 자리까지 반올림한 값을 리턴,

m 이 생략되면, 소수점 아래 값을 반올림한 정수 리턴

m 이 음수이면, 소수점 이상 지정된 자리까지 반올림 (m 이 -2 이면 10단위에서 반올림한 값 리턴)

SELECT  ROUND('1234,567', 1);   -- 1234.6
        ROUND('1234.567', -1);  -- 1230
        ROUND('1234.567')       -- 1235

TRUNC(n, [m])#

n 을 소수점 아래 m 자리까지 남기고 절사한다

m의 사용법은 ROUND 함수의 경우와 동일

CEIL(N)#

지정된 값 이상의 가장 작은 정수를 리턴

FLOOR(N)#

지정된 값 이하의 가장 큰 정수를 리턴

POWER(M, n)#

m 을 n 제곱한 값을 리턴

SQRT(n)#

지정된 값의 루트값(평방근)을 리턴

SIGN(n)#

지정된 값의 음수(-1), 양수(1), 0(0) 여부를 리턴

NULL 이 들어가면 NULL 리턴

ABS(n)#

지정된 값의 절대값 리턴

MOD(m, n)#

n 을 m으로 나눈 나머지를 리턴

n이 0인 경우 m 리턴

CREATE TABLE AS SELECT...

CREATE TABLE table_name
AS SELECT ....SELECT
FROM ....
....;

SELECT 한 결과를 이용하여 바로 테이블을 CREATE 시키고자 할 때
DDL 이다
DDL 문장이 실행되면 자동 COMMIT 이 일어나게 된다.

자동 COMMIT 되는 경우

DDL 이나 DCL 문장을 만났을 때.
SQL *PLUS 에서 exit 을 이용하여 정상 종료할 때
Set AutoCommit On 으로 환경이 지정되어 있을 때

단일 행 함수 - 3. 날짜 함수#

날짜와 날짜의 가산은 할 수 없다.

SELECT SYSDATE + BIRTH_DATE

날짜와 날짜의 감산 → 그 사이의 일 수가 숫자로 나온다.

SELECT  SYSDATE - BIRTH_DATE
FROM    TEMP
WHERE   EMP_NAME = '홍길동';

-- SYSDATE-BI
-- -----------
-- 10262.4922   # 출생일부터 현재일자까지 10262 만큼의 일수가 경과하였다.

SYSDATE

시스템의 일자를 DATE 형으로 보관 하고 있는 가상 컬럼

SYSDATE 를 이용하면 현재일자를 초 단위까지 알아낼 수 있다.

날짜에 숫자를 더하거나 빼면 날짜가 나온다

SELECT  BIRTH_DATE, BIRTH_DATE + 1, BIRTH_DATE - 1
FROM    TEMP
WHERE   EMP_NAME = '홍길동';
 
-- BIRTH_DATE   BIRTH_DATE + 1   BIRTH_DATE + 1
-- ---------------------------------------------
-- 73/03/22     73/03/23         73/03/21

DATE 형에 시간 더하고 빼기

보통 DATE 형을 SELECT 하면 일자 까지만 나온다.
시, 분, 초를 보기 위해서 TO_CHAR 함수를 사용할 수 있다.

SELECT  TO_CHAR(BIRTH_DATE, 'HH24:MI:SS')    BIRTH_TIME,
        TO_CHAR(BIRTH_DATE + 14/24, 'HH24:MI:SS') AS TIME,
        TO_CHAR(BIRTH_DATE + 30/(24*60), 'HH24:MI:SS') AS MIN,
        TO_CHAR(BIRTH_DATE + 50/(24*60*60), 'HH24:MI:SS') AS SEC
FROM TEMP
WHERE EMP_NAME = '홍길동';

-- BIRTH_TIME       TIME        MIN     SEC
-- -----------  ----------  --------- ----------
-- 00:00:00     14:00:00    00:30:00   00:00:50

ADD_MONTHS(DATE, n)#

지정된 월 만큼을 날짜에 더한 결과

SELECT  BIRTH_DATE,
        ADD_MONTHS(BIRTH_DATE, 1),      -- 한달 더하기
        ADD_MONTHS(BIRTH_DATE, -13)     -- 13개월 빼기
FROM    TEMP
WHERE   EMP_NAME = '홍길동';

MONTHS_BETWEEN(DATE1, DATE2)#

두 개의 일자 간격이 몇 개월인지를 보여준다.

SELECT  MONTHS_BETWEEN(SYSDATE, BIRTH_DATE) MON_TERM 
FROM    TEMP
WHERE   EMP_NAME = '홍길동';

NEXT_DAY(DATE, 요일(1-7))#

두 번째 인수에 1(일요일) 부터 7(토요일) 까지 가능

이 경우 지정한 일자 다음에 나타나는 지정 요일의 날짜 반환

LAST_DAY(DATE)#

지정한 일자가 포함된 월의 말일

ROUND(DATE, format)#

format 에 지정된 형식으로 반올림

TRUNC(DATE, format)#

format 에 지정된 형식으로 잘림

TO_CHAR(NUMBER or Date)#

Date 형이나, Number 형을 문자로

TO_NUMBER(CHAR)#

문자를 숫자로

TO_DATE(DATE)#

문자를 Date 형으로

단일 행 함수 - 4. 자료형 무관#

DECODE#

IF 문의 기능

NVL#

Null 의 치환

GREATEST#

지정된 값들 중 최대 값

LEAST#

지정된 값들 중 최소 값

VSIZE#

지정된 값의 bytes

데이터형 변환함수#

오라클은 문자 ↔ 숫자, 문자 ↔ 날짜 간의 형 변환을 위해 필요한 함수를 제공한다.

DATE 를 문자로 TO_CHAR(date, format)#

format	설명
YYYY 또는 SYYY	년도, S 를 지정하면 기원전 년도
YYY YY 또는 Y	년의 아래 3자리, 2자리, 1자리
SYEAR 또는 YEAR	년을 철자로
Q	분기
MM	월
MONTH 또는 MON	월의 명칭 또는 3문자의 단축형
DDD DD 또는 D	년, 월, 주의 몇번째 날인가를 보여줌
DAY 또는 DY	요일 또는 3문자의 단축형
AM 또는 PM	오전과 오후표시
A.M 또는 P.M	오전과 오후 표시
HH 또는 HH12	시각 (1-12)
HH24	시각 (0-23)
MI	분
SS	초

접미사	설명
TH	서수(DDPH 라고 지정하면 31TH와 같이 나옴)
SP	숫자를 철자로 보여준다. (DDSP => FOUR)
SSTH 또는 THSP	서수를 철자로 보여준다. (DDSPTH => FOURTH)

SELECT  TO_CHAR(SYSDATE, 'YY/MM/DD') DATE1,         -- 01/04/26
        TO_CAHR(SYSDATE, 'YYYY.MM.DD') DATE2,       -- 2001.04.26
        TO_CHAR(SYSDATE, 'Month DD.YYYY') DATE3,    -- 4월 26.2001
        TO_CHAR(SYSDATE, 'DY DD MON YY') DATE4,     -- 목 26 APR 01
        TO_CHAR(SYSDATE, 'Day Mon DD')  DATE5       -- 목요일 Apr 26
FROM DUAL;

SELECT EMP_ID,
       TO_CHAR(BIRTH_DATE, 'YYYY "년" MM "월" DD "일" DAY') BIRTH
FROM TEMP;

숫자를 문자로 TO_CHAR(number, format)#

format	설명
9	숫자의 출력
0	숫자열 앞에 0 표시
.(소수점)	소수점 자리 표시
,(Comma)	지정한 위치에 콤마 표시
$	달러 표시
L	국가별 화폐단위 표시
MI	오른쪽에 마이너스 부호
EEEE	과학적 표기

SELECT  TO_CHAR(1234, '09,999')         NUMBER1,    -- 01,234
        TO_CHAR(1234.56, '99,999.99')   NUMBER2,    -- 1,234.56
        TO_CHAR(1234, '$99,999')        NUMBER3,    -- $1,234
        TO_CHAR(1234, 'L99,999')        NUMBER4,    -- ₩1,234
        TO_CHAR(-1234, '99,999MI')      NUMBER5,    -- 1,234-
        TO_CHAR(-1234, '99,999EEEE')    NUMBER6     -- -IE+03
FROM    DUAL;

숫자로 변환 가능한 문자열을 숫자로 변환 TO_NUMBER#

SELECT  TO_NUMBER('123456')             TONUM1,     -- 123456
        TO_NUMBER('123,456', '999,999') TONUM2,     -- 123456
FROM    DUAL,

DATE 와 문자열의 연산

날짜를 표현하는 문자열을 TO_DATE 함수를 써서 변환 후 연산해야 한다.

1970년 01월 01일부터 현재일짜까지 며칠이 되는지 계산

SELECT  SYSDATE - TO_DATE('19700101', 'YYYYMMDD')   -- 11438.7064   
FROM    DUAL;

1970년 01년 01일 00시00분00초에서 부터 946075441초가 지난 날짜를 DATE 형으로 변환

SELECT  T0_DATE('19700101000000', 'YYYYMMDDHH25MISS') + 946075441 / (24 * 60 * 60)
FROM    DUAL;   -- 99/12/24

그룹함수#

행들의 집합에 대해 연산을 한 결과를

하나의 ROW 로 반환
집합 수 만큼 ROW 반환

COUNT(COLUMN or *)#

그룹지어진 행들의 수

어떤 형의 컬럼도 올 수 있다
* 로우수를 센다
- NULL 값이 들어갈 가능성이 없다면 * 를 넣는 것과 결과가 동일하다.

강제로 어떤 숫자나 문자를 넣어도 결과는 같다

SELECT  COUNT(*), COUNT(8)  -- 10 10
FROM    TEMP;

같은 테이블에 있는 컬럼이지만 두개의 값이 다르다.

SELECT  COUNT(EMP_ID) CNT1,     -- 10
        COUNT(HOBBY)  CNT2      -- 4   NULL 은 결과에 포함되지 않았다. 
FROM    TEMP;

그룹함수 사용시의 장점

GROUP 함수를 사용하면 0 rows selected. 의 결과는 나오지 않는다.

PROCEDURE, FUNCTION 등 PL/SQL 블록을 이용한 프로그램을 만들 때

0 rows selected. 라는 결과가 나올 가능성이 있는 QUERY 는 항상 EXCEPTION 처리를 해야한다.

EXCEPTION 처리를 하고 계속 진행하고자 한다면 PL/SQL 블록으로 감싸줘야 한다.

그룹 함수를 사용하면 그럴 필요가 없기 때문에 일부러 그룹함수를 사용하는 경우가 있다.

SELECT COUNT(*)         -- 0 (1 row selected.)
FROM TEMP
WHERE HOBBY = '공부';

TEMP 에서 LEV 의 종류를 세어보자

SELECT  COUNT(DISTINCT LEV)
FROM    TEMP;

MAX#

그룹지어진 행들의 지정된 컬럼에서 최대값

숫자/문자/날짜 어떤 형의 자료도 쓸 수 있다.

MIN#

그룹지어진 행들의 지정된 컬럼에서 최소값

숫자/문자/날짜 어떤 형의 자료도 쓸 수 있다.

SUM#

그룹지어진 행들의 지정된 컬럼에 대한 합계

숫자형의 자료만 쓸 수 있다.

AVG#

그룹지어진 행들의 지정된 컬럼에 대한 평균

숫자형의 자료만 쓸 수 있다.

STDDEV#

그룹지어진 행들의 지정된 컬럼에서 표준편차

VARIANCE#

그룹지어진 행들의 지정된 컬럼에서 분산

평균에서의 NULL

0 이 있는 경우와 NULL 이 있는 경우는 완전히 다르다.

평균은 SUM/COUNT 로 구해지는데, NULL 은 COUNT 에서 제외가 된다.