출처 : http://naver.kaist.ac.kr/oracle_sql/s04.htm
♣ 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) | 알파벳 문자를 소문자로 바꿈. |
| UPPER(column\expression) | 알파벳 문자를 대문자로 바꿈 |
| INITCAP(column\expression) | 알파벳 각 단어의 첫자는 대문자로, 나머지는 소문자로 바꿈 |
| CONCAT(column1\expression1, | 첫번째의 문자값과 두번째의 문자값을 연결한다.('||'와 동일) |
| SUBSTR(column\expression,m,[,n]) | 문자값 중에서 m위치에서 n문자길이의 문자를 돌려줌. |
| LENGTH | 문자갯수를 값으로 돌려 줌. |
| NVL(column1\expression1, | 첫번째 값이 NULL 이면 두번째 값으로 바꾸어 준다. |
♠실습 : 성이 Patel인 모든 사원의 성과 이름을 출력 하시오.
| SQL> SELECT first_name, last_name FROM s_emp |
♠실습 : 성명은 소문자로, USERID의 첫 자만 대문자로 그리고 직급은 대문자로 출력
| SQL> SELECT LOWER(first_name||' '||last_name) VP, |
♠실습 : 신용도가 “GOOD”인 모든 고객의 이름과 Country를 합성시켜 출력 하시오.
| SQL> SELECT CONCAT(name,country) CUSTOMER |
3. 숫자 함수
◈숫자 함수는 숫자값을 받아들여 숫자값을 되돌려 준다.
| 함수 | 목적 |
| ROUND(columm/expression,n) | Column.expression의 값을 소수점 n자리까지 반올림 한다. 만약 n이 없다면 소숫점은 없어진다. 또한 n값이 음수라면 소수점의 왼쪽 자리수만큼 반올림된다. |
| TRUNC(columm/expression,n) | Column.expression의 값을 소수점 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) |
♠실습 : 45.923을 소수점 2자리, 0, -1자리까지 절삭한 값을 출력 하시오.
| SQL> SELECT TRUNC(45.923,2), TRUNC(45.923), TRUNC(45.923,-1) |
♠실습 : 월급여가 1400보다 큰 모든 사원에 대해 월급여를 상여금으로 나누고 난 나머지를 계산하라.
| SQL> SELECT last_name, MOD(salary,commission_pct) |
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 |
♣ 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) | 두 날짜 사이의 달 수. |
| ADD_MONTHS(date,n) | 날짜에 n달 수를 더한다. |
| NEXT_DAY(date,’char’) | 명시한 날짜이후의 첫번째 해당요일(‘char’) 일자. |
| LAST_DAY(date) | date를 포함하고 있는 달의 마지막 날을 돌려 준다. |
| ROUND(date[,’fmt’]) | 정오를 기준으로 날짜 반올림. |
| TRUNC(date[,’fmt’]) | 날짜 절삭(날짜에서 시간부분을 제거). |
※ Note : 위는 이용 가능한 날짜 함수 중 일부분이다. 형식 모델은 이 장 나중에 다루어진다.
형식 모델의 예는 달 또는 년이다.
♠실습 : 근무경력이 48개월 미만인 사원들의 사번,입사일,근무월수 및 입사 6개월되는 날짜를 출력 하시오.
| SQL> SELECT id,start_date, MONTHS_BETWEEN(SYSDATE,start_date) TENURE, |
★ 전환 함수
◈전환 함수는 많은 요소들로 구성된 형식 모델(FORMAT)을 사용할 수 있다.
| 함수 | 목적 |
| TO_CHAR(number\date, [‘fmt’]) | 숫자 및 날짜 스트링을 문자스트링으로 바꾸어 준다. |
| TO_NUMBER(char) | 숫자를 포함하고 있는 문자스트링을 숫자로 바꾸어 준다. |
| TO_DATE(char[,‘fmt’]) | 날짜의 문자스트링을 날짜값으로 바꾸어 준다. |
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 |
◈ 실제 날짜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)||' 선적 완료 합계 ' |
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 |
9. 단일행 함수의 중첩
◈단일행 함수들은 여러 레벨에 걸쳐 중첩이 가능하다.
◈중첩된 함수들은 가장 하위 레벨에서 가장 상위 레벨 순으로 진행된다. 
♠실습 : 직급이 부사장인 사원의 성을 부서명과 합성하여 출력한다. 이때 column heading은 Vice Presidents로 한다.
| SQL> SELECT CONCAT(UPPER(last_name),SUBSTR(title,3)) "부사장" |
♠실습 : 자기위에 관리자가 없는 회사의 사장을 출력하라.
| SQL> SELECT last_name, NVL(TO_CHAR(manager_id),'관리자 없음') |
♠실습 : 주문일로부터 6개월이 지난 달의 다음 금요일의 날짜를 출력하라.
결과로 나올 날짜는 "Friday, March 12th, 1993"와 같은 형태로 보여져야 한다.
| SQL> SELECT |
◈내부 함수를 수행한다.: Result1=ADD_MONTHS(date_ordered,6)
◈그 다음 함수를 평가한다. : Result2=NEXT_DAY(Result1, 'FRIDAY')
◈외부 함수를 평가한다. : Result3=TO_CHAR(Result2, 'fm Day, Month ddth, YYYY')