본문 바로가기
SQL

단일 행에 대한 SQL 함수

박은주 2008. 4. 29. 00:30

제 4 장 단일 행에 대한 SQL 함수

◈ 학습목표 :
1) SQL에서 이용가능한 여러 가지의 함수에 대해 학습한다.
2) 함수들을 사용하여 기본적인 개념을 살펴본다.
3) SELECT문장에 여러 가지의 문자,숫자, 데이터 함수를 포함시킨다.
4) 전환함수를 이해하고 어떻게 사용될 수 있는가 이해한다.

제공되는 함수들은 기본적인 Quey문을 더욱 강력하게 해주고 데이터 값을 조작하는데 사용된다. 여러분은 단일행 문자, 숫자 그리고 날짜함수에 대해 집중적으로 살펴볼 뿐만아니라 하나의 Datatype에서 다른 하나의 Datatype,예를 들자면 문자 데이터 형태에서 숫자 데이터 형태로 전환하는 함수들에 대해서도 살펴본다.


♣ SQL 함수의 개요

    함수는 SQL의 매우 강력한 기능으로 아래와 같은 일을 할 경우에 사용한다.
    ◈데이터에 대해 계산을 수행할 경우
    ◈각각의 데이터 항목을 변경할 경우
    ◈그룹의 결과 출력
    ◈출력할 날짜형식을 변경할 경우
    ◈column datatype을 변경할 경우
     

♣ ２가지의 SQL함수

    ◈단일행 함수 : 이 함수는 단일행에 대해서만 적용 가능하고 행 당 한 개의 결과를 돌려준다.
          －문자, 숫자, 날짜, 변환

    ◈복수행 함수 : 이 함수는 복수의 행을 조작하여 복수의 행에 대해 하나의 결과를 돌려준다.


1. 단일 행 함수

    ◈단일 행 함수는 데이터 항목을 조작하기 위해 사용된다.
    ◈데이터에 관한 계산을 수행할 경우나 데이터 항목을 조작하기 위해 사용.
    ◈한 개 이상의 인수(Argument)를 받아들여 한 개의 행 당 하나의 값을 Return한다.
    ◈인수 - 상수, 가변적인 값, 변수, 표현식
    ◈참조시 사용한 datatype과 다른 datatype의 데이터 값을 돌려줄 수 있다.
    ◈함수를 중첩하여 사용할 수 있다.
    ◈SELECT, WHERE 및 ORDER BY절에도 함수를 사용할 수 있다.
    Syntax : Function_name (column|expression, [arg1,arg2,…])

    항목

    설명

    function_name

    함수의 이름

    Column

    데이터베이스에 존재하는 column명

    Expression

    어떤 문자스트링 또는 계산된 표현식

    Arg1, arg2

    함수에 의해 사용될 인수(들)


2. 문자 함수

    ◈단일 행 문자 함수들은 문자데이터를 입력으로 받아들여 문자나 숫자 결과 값을 돌려 준다.

    함수

    목 적

    LOWER(column\expression)

    알파벳 문자를 소문자로 바꿈.
    예> LOWER(‘SQL Course’)→sql course

    UPPER(column\expression)

    알파벳 문자를 대문자로 바꿈
    예> UPPER(‘SQL Course’)→SQL COURSE

    INITCAP(column\expression)

    알파벳 각 단어의 첫자는 대문자로, 나머지는 소문자로 바꿈
    예> INITCAP(‘SQL Course’)→Sql Course

    CONCAT(column1\expression1,
    Column2\expression2)

    첫번째의 문자값과 두번째의 문자값을 연결한다.('||'와 동일)
    예> CONCAT(‘Good’,’String’)→GoodString

    SUBSTR(column\expression,m,[,n])

    문자값 중에서 m위치에서 n문자길이의 문자를 돌려줌.
    예>SUBSTR(‘STRING’,1,3)→STR

    LENGTH

    문자갯수를 값으로 돌려 줌.
    예>LENGTH(‘’) →6

    NVL(column1\expression1,
    olumn2\expression2)

    첫번째 값이 NULL 이면 두번째 값으로 바꾸어 준다.
    예> SELECT ename,NVL(to_char(bonus),’NO BONUS’)FROM emp;


    ♠실습 : 성이 Patel인 모든 사원의 성과 이름을 출력 하시오.

    SQL> SELECT first_name, last_name FROM s_emp
             WHERE UPPER(last_name) = 'PATEL'


    ♠실습 : 성명은 소문자로, USERID의 첫 자만 대문자로 그리고 직급은 대문자로 출력

    SQL> SELECT LOWER(first_name||' '||last_name) VP,
             INITCAP(userid) USER_ID, UPPER(title) TITLE
             FROM s_emp   WHERE title LIKE 'VP%';


    ♠실습 : 신용도가 “GOOD”인 모든 고객의 이름과 Country를 합성시켜 출력 하시오.

    SQL> SELECT CONCAT(name,country) CUSTOMER
             FROM s_customer
             WHERE credit_rating = 'GOOD';


3. 숫자 함수

    ◈숫자 함수는 숫자값을 받아들여 숫자값을 되돌려 준다.

    함수

    목적

    ROUND(columm/expression,n)

    Column.expression의 값을 소수점 n자리까지 반올림 한다. 만약 n이 없다면 소숫점은 없어진다. 또한 n값이 음수라면 소수점의 왼쪽 자리수만큼 반올림된다.

    TRUNC(columm/expression,n)

    Column.expression의 값을 소수점 n자리까지 절삭한다.
    만약 n이 없다면 소숫점은 나타나지 않는다. 또한 n값이 음수라면 소숫점의 왼쪽 자리수만큼 절삭한다

    MOD (m,n)

    m값을 n값으로 나누고 남은 나머지를 Return

    ♠실습 : 45.923을 소수점 2자리, 0, -1자리까지 반올림한 값을 출력 하시오.

    SQL> SELECT  ROUND(45.923,2), ROUND(45.923,0), ROUND(45.923,-1)
             FROM    dual;


    ♠실습 : 45.923을 소수점 2자리, 0, -1자리까지 절삭한 값을 출력 하시오.

    SQL> SELECT  TRUNC(45.923,2), TRUNC(45.923), TRUNC(45.923,-1)
             FROM    dual;


    ♠실습 : 월급여가 1400보다 큰 모든 사원에 대해 월급여를 상여금으로 나누고 난 나머지를 계산하라.

    SQL> SELECT last_name, MOD(salary,commission_pct)
             FROM s_emp
             WHERE salary > 1400;


4. 날짜 산술 연산

    ◈결과로 날짜 값이 나오게 날짜에 숫자를 더하거나 날짜로부터 숫자를 뺀다.
    ◈두 개의 날짜 사이의 일수를 알기 위해 날짜에서 날짜를 뺀다.
    ◈시간을 날짜에 더한다.
     

♣ 날짜에 산술 연산자 사용

    ◈데이터베이스는 날짜를 숫자로 저장하기 때문에 덧셈, 뺄셈 같은 산술연산자를 사용하여 계산을 할 수 있다.
    ◈날짜뿐만 아니라 숫자상수를 더하거나 뺄 수 있다.

    연산

    결과

    설명

    Date + number

    Date

    날수를 날짜에 더한다.

    Date - number

    Date

    날짜에서 날수를 뺀다.

    Date ? date

    Number of days

    다른 하나의 날짜에서 하나의 날짜를 뺀다.

    Date + number/24

    date

    시간을 날짜에 더한다.


    ♠실습 : 부서번호 43에 속하는 사원의 성과 입사일부터 지금까지 근무한 근무주 수를 출력하라.

    SQL> SELECT last_name, (SYSDATE - start_date) / 7 WEEKS
             FROM s_emp
            WHERE dept_id = 43;


♣ SYSDATE

    ◈SYSDATE는 현재의 날짜와 시각을 돌려주는 날짜 함수이다.(소수점 이하는 시간이다.)
    ◈예를 들어 Dummy테이블인 DUAL에서 SYSDATE를 선택할 수 있다.
     

♣ DUAL 테이블

    ◈DUAL은 SYSDATE를 보기 위해 사용하는 Dummy 테이블이다.
    ◈DUAL 테이블은 사용중인 테이블에서 유래하지 않는 상수값, Pseudo column, 표현식등의 값을
       단 한번만 돌려 받기를 원할 경우에 유용하다,
    ♠실습 : 현재 날짜를 출력 하시오.

    SQL> SELECT SYSDATE      FROM DUAL;


5. 날짜 함수

    ◈날짜 함수는 오라클의 날짜에 대해 연산을 한다.
    ◈모든 날짜 함수는 DATE 자료형의 값을 돌려주는데 MONTHS_BETWEEN은 예외적으로 숫자값을
       돌려준다.

    함수

    목적

    MONTHS_BETWEEN(date1,date2)

    두 날짜 사이의 달 수.
    결과의 소수점부분은월이며, 음수 값을 가질 수 있다.
    예> MONTHS_BETWEEN(‘01-SEP-98’,’11-JAN-97’)→16.6774194

    ADD_MONTHS(date,n)

    날짜에 n달 수를 더한다.
    n은 정수이어야 하며 음수가 가능하다.
    예> ADD_MONTHS(’11-JAN-98’,6)→ ‘11-JUL-98’

    NEXT_DAY(date,’char’)

    명시한 날짜이후의 첫번째 해당요일(‘char’) 일자.
    예>NEXT_DAY(‘04-FEB-98’,’FRIDAY’) →’06-FEB-98’

    LAST_DAY(date)

    date를 포함하고 있는 달의 마지막 날을 돌려 준다.
    DAY(‘04-FEB-98’) → ‘28-FEB-98’

    ROUND(date[,’fmt’])

    정오를 기준으로 날짜 반올림.
    예> ROUND(‘25-MAY-98’,’MONTH’) → ‘01-JUN-98’
    예> ROUND(‘25-MAY-98’) →‘25-MAY-98’

    TRUNC(date[,’fmt’])

    날짜 절삭(날짜에서 시간부분을 제거).
    예> TRUNC(‘25-MAY-98’,’YEAR’)→’

    ※ Note : 위는 이용 가능한 날짜 함수 중 일부분이다. 　형식 모델은 이 장 나중에 다루어진다.
                 형식 모델의 예는 달 또는 년이다.
    ♠실습 : 근무경력이 48개월 미만인 사원들의 사번,입사일,근무월수 및 입사 6개월되는 날짜를 출력 하시오.

    SQL> SELECT  id,start_date,  MONTHS_BETWEEN(SYSDATE,start_date) TENURE,
                      ADD_MONTHS(start_date,6) REVIEW
             FROM    s_emp  
             WHERE   MONTHS_BETWEEN(SYSDATE,start_date) < 80;


★ 전환 함수

    ◈전환 함수는 많은 요소들로 구성된 형식 모델(FORMAT)을 사용할 수 있다.

    함수

    목적

    TO_CHAR(number\date, [‘fmt’])

    숫자 및 날짜 스트링을 문자스트링으로 바꾸어 준다.
    예> TO_CHAR(sysdate,’YYYY/MM/DD’)→1998/2/4

    TO_NUMBER(char)

    숫자를 포함하고 있는 문자스트링을 숫자로 바꾸어 준다.

    TO_DATE(char[,‘fmt’])

    날짜의 문자스트링을 날짜값으로 바꾸어 준다.
    예> TO_DATE(’04-FEB-1998’,’YYYY/MM/DD’)


6. TO_CHAR 함수

♣ Syntax

    TO_CHAR(date, 'fmt')

    ◈단일 인용부호(single quotation)로 에워싸야 하며 대소문자 구분이 있다.
    ◈어떤 유효한 날짜 형식 요소도 포함가능하다.
    ◈덧붙여진 공백이나 선행제로를 없애기 위한 fm 형식을 갖고 있다.
    ◈콤마에 의해 날짜값과 구분한다.
     

♣ 날짜형식 사용시 TO_CHAR함수(특정 형식으로 날짜 Display 하기)

    ◈이전에는 모든 오라클의 날짜값은 DD-MON-YY 형식으로 보여졌다.
    ◈TO_CHAR 함수는 이런 기본 형식의 날짜값을 명시한 형식으로 전환해 준다.
    ◈형식 모델은 단일 인용부호로 에워싸야 하고 대소문자 구분이 있다.
    ◈형식 모델은 어떠한 유효한 날짜 형식 요소를 포함할 수 있고, 날짜값은 콤마에 의해 구분한다.
    ◈결과에 일,월의 이름은 자동적으로 공백으로 덧붙여진다.
    ◈덧붙여지는 공백 및 선행 제로를 없애기 위해서는 자릿수채움 모드 "fm"을 사용하시오.
    ◈COLUMN명령을 사용하여 결과로 나오는 문자필드의 Display폭을 재결정할 수 있다.
    ◈결과로 나오는 column의 폭은 기본적으로 80자이다.
    ♠실습 : 영업사원 11에 의해 받은 모든 주문에 대해 주문번호, 주문날짜를 출력 하시오.
                (날짜형식 "08'92")

    SQL> SELECT  id,TO_CHAR(date_ordered,'MM/YY') ORDERED
             FROM    s_ord
             WHERE   sales_rep_id = 11;


    ◈ 실제 날짜Type형태는거의 사용하지 않고 있다.(시스템 날짜만 사용하고 있음)

7. 숫자 사용시 TO_CHAR 함수

♣ Syntax

    TO_CHAR(number, 'fmt')

    number의 값에 있어 앞에 0이 있으면 9을 제거하고 출력한다.
     

♣ 숫자 사용할 때 TO_CHAR함수

    문자 스트링 같은 숫자값으로 일을 할 때 그런 숫자를 TO_CHAR함수를 사용하여 문자 자료형으로 전환해야 하는데 TO_CHAR함수는 NUMBER 자료형을 VARCHAR2자료형의 값으로 전환해준다. 이런 기법은 특히 합성시(concatenation) 유용하다.
     

♣ 숫자 형식 요소(total 값이 1234인 경우)

    ◈문자를 숫자로 변환하기를 원한다면 아래와 같은 숫자형식 요소를 사용할 수 있다.

    요소

    기술

    예(값이 1234인 경우)

    결과

    9

    숫자(9는 Display 폭을 결정한다)

    TO_CHAR(total,'999999')

    1234

    0

    0을 출력 하시오.

    TO_CHAR(total,'099999')

    001234

    $

    달러 기호를 나타낸다.

    TO_CHAR(total,'$999999')

    $1234

    L

    지역 화폐 기호를 나나낸다.

    TO_CHAR(total,'L999999')

    \1234

    .

    명시한 위치에 소수점을 출력한다.

    TO_CHAR(total,'999999.99')

    1234.00

    ,

    명시한 위치에 코마를 표시한다.

    TO_CHAR(total,'999,999')

    1,234

    ◈오라클7 서버는 형식 모델에 의해 제공하는 자릿수를 넘는 숫자에 대해서는 파운드 기호(#) 스트링을 보여준다.

    ◈오라클7 서버는 저장된 소숫값을 형식 모델에서 제공 하는 소숫점 자리수로 반올림 시켜준다.

    ♠실습 : 1992년 9월 21일날 선적되어야 하는 주문이 완료되었다는 메시지를 출력하라.
    각 주문별 합계와 주문번호를 반드시 포함하라.

    SQL> SELECT '주문번호 '||TO_CHAR(id)||' 선적 완료 합계 '
                 ||TO_CHAR(total,'$9,999,999') NOTE
             FROM   s_ord   WHERE  date_shipped = '21-SEP-92';
     
    or
     
    SQL> SELECT '주문번호 '||TO_CHAR(id)||' 선적 완료 합계 '
                ||TO_CHAR(total,'fm$9,999,999') NOTE
         FROM   s_ord
       WHERE  date_shipped = '21-SEP-92';


8. TO_NUMBER와 TO_DATE 함수

    ◈문자 스트링을 TO_NUMBER와 TO_DATE 함수를 사용하여 숫자 형식으로 전환한다.
           TO_DATE(char[, 'fmt'])
    ◈문자 스트링을 TO_NUMBER와 TO_DATE 함수를 사용하여 날짜 형식으로 전환한다.

      - TO_DATE('10 September 1992', 'dd Month YYYY')
      - Use format elements
      TO_DATE(char[, 'fmt'])
       

♣ TO_NUMBER와 TO_DATE 함수

    ◈문자스트링을 숫자나 날짜형식으로 변환하기를 원할 수도 있다.
    ◈이러한 일을 수행하기 위해 TO_NUMBER 또는 TO_DATE 함수를 각각 사용할 수 있다.
       선택할 형식은 앞페이지에서 보여진 형식의 형태를 따른다.
    ♠실습 : 1992년 9월 7일날 받은 모든 주문을 보여라. 여기서 보고자 하는 날짜 스트링을 날짜 형식으로
                전환하라.

    SQL> SELECT id, total, date_ordered     FROM   s_ord
        WHERE date_ordered = TO_DATE ( 'September 7, 1992', 'Month dd, YYYY' );


9. 단일행 함수의 중첩

    ◈단일행 함수들은 여러 레벨에 걸쳐 중첩이 가능하다.
    ◈중첩된 함수들은 가장 하위 레벨에서 가장 상위 레벨 순으로 진행된다.

    ♠실습 : 직급이 부사장인 사원의 성을 부서명과 합성하여 출력한다. 이때 column heading은 Vice Presidents로 한다.

    SQL> SELECT CONCAT(UPPER(last_name),SUBSTR(title,3)) "부사장"    
             FROM s_emp
             WHERE title LIKE 'VP%'


    ♠실습 : 자기위에 관리자가 없는 회사의 사장을 출력하라.

    SQL> SELECT last_name, NVL(TO_CHAR(manager_id),'관리자 없음')  
         FROM   s_emp
         WHERE  manager_id IS NULL;


    ♠실습 : 주문일로부터 6개월이 지난 달의 다음 금요일의 날짜를 출력하라.
               결과로 나올 날짜는 "Friday, March 12th, 1993"와 같은 형태로 보여져야 한다.

    SQL> SELECT  
    TO_CHAR(NEXT_DAY(ADD_MONTHS(date_ordered,6),'FRIDAY'),
                  'fmDay, Month ddth, YYYY') "New 6 Month Review"
         FROM     s_ord
         ORDER BY date_ordered


    ◈내부 함수를 수행한다.: Result1=ADD_MONTHS(date_ordered,6)
    ◈그 다음 함수를 평가한다. : Result2=NEXT_DAY(Result1, 'FRIDAY')
    ◈외부 함수를 평가한다. : Result3=TO_CHAR(Result2, 'fm Day, Month ddth, YYYY')

    펌:네이버

티스토리툴바