본문 바로가기

.주제별/SQL,DB

SQL)강의- 제 4 장 단일 행에 대한 SQL 함수

출처 : http://naver.kaist.ac.kr/oracle_sql/s04.htm

 

♣ SQL 함수의 개요

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

♣ 2가지의 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')