과정 목표

함수는 기본적인 질의 블록을 보다 강력하게 만들며 데이터 값을 조작하기 위해 사용됩니다. 이곳은 함수에 대한 두 개의 과정 중 첫 번째 과정입니다. 문자 데이터를 숫자 데이터로 변환하는 것같은, 어떤 데이터 형을 다른 데이터형으로 변환하는 함수 뿐만 아니라 단일 행 문자, 숫자 그리고 날짜 함수에 초점을 맞출 것입니다.

 

SQL 함수

함수는 SQL의 아주 강력한 특징이며 다음을 위해서 사용할 수 있습니다:

SQL 함수는 인수를 받고 결과 값을 내줍니다.

SQL 함수 (계속)

함수에는 두 가지의 다른 유형이 있습니다:

단일 행 함수

이러한 함수는 오직 단일 행에서만 적용가능하고 행별로 하나의 결과를 리턴합니다. 단일 행 함수에는 여러 유형들이 있습니다. 본 과정은 아래에 나열된 것들을 다룹니다:

다중 행 함수

이러한 함수는 복수의 행을 조작하여 행의 그룹당 하나의 결과를 리턴합니다.

이용 가능한 함수와 구문형식의 완전한 목록에 대해 보다 자세한 정보를 알고자 한다면  
- Oracle Server SQL Reference, Release 7.3,
- Oracle Server SQL Reference, Release 8.0 을 참조하십시오.

 

단일 행 함수

단일 행 함수는 데이터 값을 조작하는데 사용됩니다. 하나 이상의 인수(argument)를 받고 질의에 의해 리턴될 각각의 행에 대해 하나의 값을 리턴합니다. 인수(argument)는 다음 중의 하나가 될 수 있습니다:

단일 행 함수의 특징

구문형식에서:

        function_name       함수 명 입니다.
        column                  데이터베이스 열 이름 입니다.
        expression             어떤 문자 스트링이거나 계산된 표현식입니다.
        arg1, arg2              함수에 의해 사용될 수 있는 인수입니다.

 

단일 행 함수 (계속)

본 과정은 다음의 단일 행 함수를 다룹니다:

 

대소문자 변환 함수

LOWER, UPPER, 그리고 INITCAP 은 대소문자 변환 함수입니다.

SQL>    SELECT  'The job title for '||INITCAP(ename)||' is '
  2             ||LOWER(job) AS "EMPLOYEE DETAILS"
  3     FROM    emp;


        EMPLOYEE DETAILS
        -----------------------------------------
        The job title for King is president
        The job title for Blake is manager
        The job title for Clark is manager
        ...
        14 rows selected.

 

대소문자 변환 함수 (계속)

위의 예는 종업원 BLAKE의 종업원 번호, 이름 그리고 부서번호를 디스플레이 합니다.

첫 번째 SQL 문장의 WHERE 절은 종업원 이름을 ‘blake’로 명시합니다. EMP 테이블의 모든 데이터는 대문자로 저장되어 있으므로, 'blake' 는 EMP 테이블에서 찾을 수 없어서 “no rows selected”를 리턴합니다.

두 번째 SQL 문장의 WHERE 절은 EMP 테이블의 종업원 이름을 소문자로 변환한 다음에 blake 와 비교하기를 명시합니다. 이제 양쪽의 이름이 소문자이므로 일치되는 하나의 행이 선택됩니다. WHERE 절은 똑같은 결과를 얻기 위해서 다음의 방법으로 다시 작성할 수 있습니다 :
 

… WHERE        ename = 'BLAKE'

결과에서 이름은 데이터베이스에 저장된 대로 나타납니다. 이름에서 첫 번째 문자를 대문자로 디스플레이 하기 위해서 SELECT 문장에서 INITCAP 함수를 사용합니다.

SQL> SELECT     empno, INITCAP(ename), deptno
  2  FROM       emp
  3  WHERE      LOWER(ename) = 'blake';

 

문자 조작 함수

CONCAT, SUBSTR, LENGTH, INSTR, LPAD 는 본 과정에서 다루는 다섯 가지의 문자 조작 함수입니다.

주: RPAD 문자 조작 함수는 문자 값을 좌측부터 채웁니다.

 

문자 조작 함수 (계속)

위의 예는, 업무가 sales인 모든 종업원에 대해서 함께 합성된 종업원의 이름과 업무, 종업원 이름의 길이 그리고 종업원 이름에서 문자 A의 숫자적인 위치를 디스플레이 합니다.

예

이름이 N으로 끝나는 종업원에 대해서 데이터를 디스플레이 하기 위해서 위의 SQL 문장을 수정합니다.

SQL> SELECT     ename, CONCAT(ename, job), LENGTH(ename), INSTR(ename, 'A')
  2  FROM       emp
  3  WHERE      SUBSTR(ename, -1, 1) = 'N';


  ENAME    CONCAT(ENAME,JOB)   LENGTH(ENAME) INSTR(ENAME,'A')
  -------- ------------------- ------------- ----------------
  MARTIN   MARTINSALESMAN                  6                2
  ALLEN    ALLENSALESMAN                   5                1

 

숫자 함수

숫자 함수는 숫자 입력을 받고 숫자 값을 리턴합니다.

함  수

목  적

ROUND(column |expression, n)

열, 표현식 또는 값을 소수점 n 자리까지 반올림
합니다. n이 생략되면 소수점이 없어집니다. n이 음수
이면 소수점의 왼쪽 자리 수만큼 반올림 됩니다.

TRUNC(column |expression, n)

열, 표현식 또는 값을 소수점 n 자리까지 절삭합니다.
n이 생략되면 소수점이 없어집니다. n이 음수이면,
소수점의 왼쪽 자리 수만큼 절삭됩니다.

MOD(m, n)

m을 n으로 나눈 나머지를 리턴합니다.

주: 이 목록은 이용 가능한 숫자 함수의 일부분입니다.

숫자 함수에 대해 보다 자세한 정보를 알고자 하다면 다음을 참조하십시오.
- Oracle Server SQL Reference, Release 7.3 또는 8.0, “Number Functions.”

 

ROUND 함수

ROUND 함수는 열, 표현식 또는 값을 소수점 n 자리로 반올림 합니다. 두 번째 인자가 Ø이거나 생략되면, 값은 소수점 위치가 Ø으로 반올림 됩니다. 두 번째 인자가 2이면, 값은 소수점 아래 두 번째 위치로 반올림 됩니다. 반대로, 두 번째 인자가 -2이면, 값은 소수점 좌측의 두 번째 위치로 반올림 됩니다.

ROUND 함수는 날짜 함수에 함께 사용될 수도 있습니다. 본 과정의 뒤에서 예제를 볼 수 있습니다.

DUAL 은 더미 테이블입니다. 이것에 대한 보다 자세한 것은 뒤에서 다루도록 합니다.

 

TRUNC 함수

TRUNC 함수는 열, 표현식 또는 값을 소수점 n 자리로 절삭합니다.

TRUNC 함수는 ROUND 함수와 유사한 인수로 수행합니다. 두 번째 인자가 Ø이거나 생략되면, 값은 소수점 위치를 Ø으로 절삭합니다. 두 번째 인수가 2이면, 값은 두 개의 소수점 아래 위치로 절삭합니다. 반대로 두 번째 인자가 -2 이면, 값은 소수점 좌측부터 두 자리 까지로 절삭합니다.

ROUND 함수처럼 TRUNC 함수도 날짜 함수와 함께 사용될 수 있습니다.

 

MOD 함수

MOD 함수는 value1을 value2로 나눈 나머지를 찾습니다. 위의 예는 업무가 salesman인 모든 종업원에 대해서 보너스에 대한 급여 비율의 나머지를 계산합니다.

 

오라클 날짜 형식

오라클은 세기, 년, 월, 일, 시간, 분, 초의 내부 숫자 형식으로 날짜를 저장합니다.
날짜에 대한 디폴트 디스플레이와 입력 형식은 ‘DD-MON-YY’ 입니다. 적절한 오라클 날짜의 범위는 January 1, 4712 B.C. 와 December 31, 9999 A.D. 사이 입니다.

SYSDATE

SYSDATE 는 현재의 날짜와 시간을 리턴하는 함수입니다. 다른 열 이름을 사용하듯이 SYSDATE 를 사용할 수 있습니다. 예를 들면, 테이블로부터 SYSDATE 를 선택하여 현재 날짜를 디스플레이 할 수 있습니다. DUAL 이라는 더미 테이블로부터 SYSDATE를 선택하는 것이 관례입니다.

DUAL

DUAL 테이블은 SYS 에 의해 소유되며 모든 사용자가 액세스 할 수 있습니다. DUMMY 라는 하나의 열과 X 값을 가지는 하나의 행을 포함합니다. DUAL 테이블은 오직 한번만 값을 리턴하고자 할 때 유용합니다. 가령 사용자 데이터를 가진 테이블에서 파생되지 않은 상수, 의사열 또는 표현식의 값의 경우입니다.

Example

DUAL 테이블을 사용하여 현재 날짜를 디스플레이 합니다.

SQL> SELECT     SYSDATE
  2  FROM       DUAL;

 

날짜 연산

데이터베이스는 날짜를 숫자로 저장하므로, 더하기와 빼기 같은 산술 연산자를 사용하여 계산을 수행할 수 있습니다. 날짜 뿐 만 아니라 숫자 상수를 더하거나 뺄 수도 있습니다.

다음의 작업을 수행할 수 있습니다:

작업 

결과    

설명

날짜 + 숫자  

날짜

일 수를 날짜에 더합니다.

날짜 - 숫자 

날짜

날짜에서 일 수를 뺍니다.

날짜 - 날짜

일수

어떤 날짜에서 다른 날짜를 뺍니다.

날짜 + 숫자/24

날짜

시간을 날짜에 더합니다.

 

날짜 연산 (계속)

위의 예는 부서 10에 속하는 모든 종업원에 대해서 이름과 근무한 주의 합계를 디스플레이 합니다. 현재 날짜(SYSDATE)에서 종업원이 입사한 날짜를 빼고 근무한 주의 합계를 구하기 위해서 7로 나눕니다.

주: SYSDATE 는 현재 날짜와 시간을 리턴하는 SQL 함수입니다. 결과는 예제와 다를 수 있습니다.

 

날짜 함수

날짜 함수는 오라클 날짜에 대해 연산을 합니다. 모든 날짜 함수는 숫자 값을 리턴하는 MONTHS_BETWEEN 을 제외하고는 DATE 데이터형의 값을 리턴합니다.

이 목록은 이용 가능한 날짜 함수의 일부분입니다. 포맷 모델(fmt)은 이 장의 뒤에서 다루도록 합니다. 포맷 모델(fmt)의 예는 월 또는 년입니다.

 

변환 함수

Oracle8 데이터베이스 테이블의 열은 오라클 데이터형은 물론이고 ANSI, DB2 그리고  SQL/DS  데이터형을 사용하여 정의될 수 있습니다. 그러나, 오라클 서버는 내부적으로 그러한 데이터형을 Oracle8 데이터형으로 변환합니다.

오라클 서버는 어떤 일정한 데이터형의 데이터를 사용해야 하는 곳에, 그것과 다른  데이터형의 데이터를 사용할 수 있게 합니다. 이것은 오라클 서버가 자동적으로 데이터형을 변환할 수 있을 때 허용됩니다. 이 데이터형 변환은 오라클 서버에 의해서 암시적 으로 행해지거나 또는 사용자에 의해서 명시적으로 행해질 수 있습니다.

암시적인 데이터형 변환은 다음의 두 슬라이드에 설명된 규칙에 따라서 수행됩니다.

명시적인 데이터형 변환은 변환 함수를 사용하여 수행됩니다. 변환 함수는 어떤 데이터형의 값을 다른 데이터형의 값으로 변환합니다.

주: 비록 암시적 데이터형 변환을 이용할 수 있더라도, SQL 문장의 안정성을 위해서 명시적 데이터형 변환을 할 것을 권장합니다.

 

암시적 데이터형 변환

값 할당(assignment) 시, 오라클 서버는 다음을 자동으로 변환할 수 있습니다:

오라클 서버가 값 할당(assignment) 문장에서 사용된 값의 데이터형을 목표(target) 값의 데이터형으로 변환할 수 있을 경우에 할당(assignment) 문장은 올바로 수행됩니다.

 

암시적 데이터형 변환

표현식 계산의 경우, 오라클 서버는 다음을 자동으로 변환할 수 있습니다:

일반적으로 오라클 서버는 할당(assignment) 시의 변환에 대한 규칙으로 데이터형 변환이 이루어질 수 없는 곳에서 데이터형 변환이 필요할 때 표현식에 대한 규칙을 사용합니다.

주: CHAR가 NUMBER로의 변환은 오직 문자 스트링이  적절한 숫자를 나타낼 때만 가능합니다. CHAR가 DATE로의 변환은 문자 스트링이 디폴트 형식 DD-MON-YY를 가질 경우에만 성공합니다.

 

명시적 데이터형 변환

SQL은 어떤 데이터형 값을 다른 데이터형 값으로 변환하기 위해 아래 세 개의 함수를 제공합니다.

함  수

목  적

TO_CHAR(number | date,['fmt'])

숫자나 문자 값을 포맷 모델 fmt를 사용하여 VARCHAR2 문자 스트링으로 변환합니다

TO_NUMBER(char)

숫자를 포함하는 문자 스트링을 숫자로 변환합니다.

TO_DATE(char,['fmt'])

날짜를 나타내는 문자 스트링을 명시된 fmt 에 따라서 날짜 값으로 변환합니다. (fmt 가 생략 되면, DD-MON-YY 형식입니다.)

주: 이 목록은 이용할 수 있는 변환 함수의 일부분입니다.

변환 함수에 대해 보다 많은 정보를 알고자 한다면 다음을 참조하십시오.                                                                                                                      
- Oracle Server SQL Reference, Release 7.3 또는 8.0, “Conversion Functions.”

 

특정 형식으로 날짜를 디스플레이

이전의 모든 오라클 날짜 값은 DD-MON-YY 형식으로 디스플레이 되었습니다.  TO_CHAR 함수는 이 디폴트 형식의 날짜를 여러분이 명시한 형식으로 변환합니다.

지침서

SQL> SELECT  empno, TO_CHAR(hiredate, 'MM/YY') Month_Hired
  2  FROM    emp
  3  WHERE   ename = 'BLAKE';

 

타당한 날짜 형식 요소의 예

요  소

설  명

SCC or CC

세기; BC 날짜에는 ?S를 붙입니다.

Years in dates YYYY or SYYYY

년; BC 날짜에는 ?S를 붙입니다.

YYY or YY or Y

년의 마지막 3, 2 또는 1자리 수

Y,YYY

콤마가 있는 년

IYYY, IYY, IY, I

ISO 표준에 바탕을 둔 4, 3, 2 또는 1자리 수

SYEAR or YEAR

문자로 표현된 년; BC 날짜에는 ?S를 붙입니다.

BC or AD

BC/AD 지시자

B.C. or A.D.

. 이 있는 BC/AD 지시자

Q

년의 4분의 1

MM

두자리 값의 월

MONTH

9자리를 위해 공백을 추가한 월 이름

MON

세 자리의 약어로 된 월 이름

RM

로마 숫자 월

WW or W

년이나 월의 주

DDD or DD or D

년, 월 또는 주의 일

DAY

9자리를 위해 공백을 추가한 요일 이름

DY

세 자리 약어로된 요일 이름

J

Julian day; BC 4713년 12월 31일 이후의 요일 수

 

시간 형식

시간 정보와 리터럴을 디스플레이 하고 숫자를 명시된 숫자로 변경하기 위해서 다음의 테이블에 나열된 형식을 사용합니다.

요  소

설  명

AM or PM

정오 지시자

A.M. or P.M.

. 이 있는 정오 지시자

HH or HH12 or HH24

하루 중 시간 또는 시간(1-12) 또는 시간(0-23)

MI

분 (0-59)

SS

초 (0-59)

SSSSS

자정 이후의 초 (0-86399)

기타 형식

요  소

설  명

/  .  ,  

사용 문자가 결과에 다시 나타납니다.

"of the"

인용 부호내의 스트링이 결과에 다시 나타납니다.

숫자 디스플레이에 영향을 주는 접미사 명시

요  소

설  명

TH

서수 (예, DDTH for 4TH)

SP

명시한 수 (예, DDSP for FOUR)

SPTH or THSP

명시한 서수 (예, DDSPTH for  FOURTH)

 

날짜를 가진 TO_CHAR 함수

위의 SQL 문장은 모든 종업원에 대해서 이름과 입사일을 디스플레이 합니다. 입사일은 “17 November 1981”처럼 나타납니다.

예

날짜 형식이 “Seventh of February 1981 08:00:00 AM”처럼 나타나도록 위의 예를 수정합니다.

SQL> SELECT ename,
  2         TO_CHAR(hiredate, 'fmDdspth "of" Month YYYY fmHH:MI:SS AM')
  3         HIREDATE
  4  FROM   emp;


ENAME      HIREDATE
---------- ------------------------------------------------
KING       Seventeenth of November 1981 12:00:00 AM
BLAKE      First of May 1981 12:00:00 AM
...
14 rows selected.

월은 명시된 형식 모델(INITCAP)을 따름을 주목하십시오.

숫자를 가진 TO_CHAR 함수

문자 스트링 같은 숫자 값으로 작업할 때, 그러한 숫자를 TO_CHAR 함수를 사용하여 문자 데이터형으로 변환해야 합니다. TO_CHAR 함수는 NUMBER 데이터형의 값을 VARCHAR2로 전환합니다. 이 기법은 특히 연결(concatenation) 시에 유용합니다.

숫자 형식 요소

숫자를 문자 데이터형으로 변환한다면, 아래에 나열된 요소를 사용할 수 있습니다:

요  소

설  명

예

결과

9

숫자 위치(9의 수는 디스플레이 폭을 결정합니다.)

999999

1234

0

앞에 Ø을 디스플레이 합니다.

099999

001234

$

달러 기호

$999999

$1234

L

지역 화폐 기호

L999999

FF1234

.

명시한 위치에 소수점

999999.99

1234.00

,

명시한 위치에 콤마

999,999

1,234

MI

우측에 마이너스 기호(음수 값)

999999MI

1234-

PR

음수를 괄호로 묶습니다.

999999PR

<1234>

EEEE

과학적인 부호표기(형식은 4개의 E를 명시해야 합니다.)

99.999EEEE

1.234E+03

V

10을 n 번 곱합니다. (n = V 뒤의 9의 수)

9999V99

123400

B

Ø값을 Ø이 아닌 공백으로 디스플레이 합니다.

B9999.99

1234.00

 

지침서

 

TO_NUMBER 와 TO_DATE 함수

문자 스트링을 숫자나 날짜 형식으로 변환하기를 원할 수도 있습니다. 이런 일을 수행하기 위해 TO_NUMBER 나 TO_DATE 함수를 사용합니다. 여러분이 선택할 형식 모델은 앞의 형식 모델 요소를 바탕으로 합니다.

예

February 22, 1981에 입사한 모든 종업원의 이름과 입사일을 디스플레이 합니다.

SQL> SELECT ename, hiredate
  2  FROM   emp
  3  WHERE  hiredate = TO_DATE('February 22, 1981', 'Month dd,  YYYY');


ENAME      HIREDATE
---------- --------
WARD       22-FEB-81

 

NVL 함수

null 값을 실제 값으로 변환하기 위해서, NVL 함수를 사용합니다.

구문형식

NVL (expr1, expr2)

여기서: 
        expr1           null을 포함할 수 있는 소스 값이나 표현식입니다.
        expr2           null 변환을 위한 목표(target) 값 입니다.
데이터형을 변환하는데 NVL 함수를 사용할 수 있는데, 리턴 값은 항상 expr1의 데이터형과 같아야 합니다.

다양한 데이터형에 대한 NVL 변환
 

데이터형

변환 예

NUMBER  

NVL(number_column, 9)

DATE

NVL(date_column, '01-JAN-95')

CHAR or VARCHAR2

NVL(character_column, 'Unavailable')

 

NVL 함수

모든 종업원의 연봉을 계산하기 위해서 월 급여에 12를 곱하고 거기에 보너스를 더합니다.

SQL> SELECT ename, sal, comm, (sal*12)+comm
  2  FROM   emp;


ENAME      JOB       (SAL*12)+COMM
---------- --------- -------------
KING       PRESIDENT
BLAKE      MANAGER
CLARK      MANAGER           
JONES      MANAGER
MARTIN     SALESMAN          16400
...
14 rows selected.

연봉은 보너스를 받는 종업원에 대해서만 계산됨을 주목하십시오. 표현식의 열 값이 null이면 결과는 null 입니다. 모든 종업원에 대한 값을 계산하기 위해서는 산술 연산자를 적용하기 전에 null 값을 숫자로 변환해야 합니다. 슬라이드의 예에서 NVL 함수는 null 값을 Ø으로 변환하는데 사용됩니다.

DECODE 함수

DECODE 함수는 여러 언어에서 사용하는 IF-THEN-ELSE 로직과 유사한 방법으로 표현식을 해독 합니다. DECODE 함수는 각각의 search에 대해 비교한 후에 expression을 해독합니다. 표현식이 search와 똑같으면 result 가 리턴됩니다.

디폴트 값이 생략되면 검색 값(search)이 결과 값(result)과 일치하지 않는 곳에 null 값이 리턴될 것입니다.

 

DECODE 함수 사용

위의 SQL 문장에서 JOB 의 값이 해독되었습니다. JOB 이 ANALYST 이면, 급여 증가는 10%이고 JOB 이 CLERK 이면, 급여 증가는 15% 입니다. JOB 이 MANAGER 라면, 급여 증가는20% 입니다. 다른 업무에 대해서는 급여 증가분이 없습니다.

IF-THEN-ELSE 문장으로 똑같이 작성될 수 있습니다:

IF job = 'ANALYST'    THEN  sal = sal*1.1
IF job = 'CLERK'      THEN  sal = sal*1.15
IF job = 'MANAGER'    THEN  sal = sal*1.20
ELSE sal = sal

 

중첩 함수

단일 행 함수는 어떤 레벨까지도 중첩될 수 있습니다. 중첩 함수는 가장 내부의 레벨로부터 가장 외부의 레벨 순으로 계산됩니다. 다음 페이지의 예를 보십시오.

중첩 함수 (계속)

위의 예는 관리자가 없는 회사의 대표자를 디스플레이 합니다. SQL 문장의 평가는 다음의 두 단계를 가집니다:
   1.  숫자 값을 문자 값으로 변환하기 위해서 내부 함수를 계산합니다.
      -Result1 = TO_CHAR(mgr)
   2. null 값을 텍스트 스트링으로 대체하기 위해서 외부 함수를 계산합니다.
      -NVL(Result1, 'No Manager')
모든 표현식은 주어진 열 별칭이 없으므로 열의 헤딩이 됩니다.

예

입사일로부터 6개월이 지난후의 다음 금요일의 날짜를 디스플레이 합니다. 결과 날짜는 “Friday, March 12th, 1982”처럼 나타나야 합니다. 결과 정렬은 입사일 순입니다.

SQL> SELECT    TO_CHAR(NEXT_DAY(ADD_MONTHS
  2            (hiredate, 6), 'FRIDAY'),
  3            'fmDay, Month ddth, YYYY')
  4            "Next 6 Month Review"
  5  FROM      emp
  6  ORDER BY  hiredate;

 123.4, 234231, 0.23421, 1.1 등의 값을 포함하는 datatype?
A. number(6,3)
B. varchar2(30)
C. char(30)
D. number
답

X 정답:D


X 정답:C


O


X 정답:BD


O


X 정답:BC


O


X 정답:E


X 정답:B


X 정답:C