|
3. 함수
함수는 SQL의 매우 강력한 기능으로 아래와 같은 일을 할 경우 사용합니다.
·데이터에 관한 계산을 수행할 경우
·각각의 데이터 항목을 변경할 경우
·그룹의 결과 출력
·디스플레이할 날짜형식을 변경할 경우
·Column datatype을 변경할 경우
(가) 단일행 함수
·문자
·숫자
·날짜
·변환
단일행 함수는 데이터 항목을 조작하기 위해 사용됩니다. 한 개 이상의 인수(argument)를 받아들여 한 개의 행 당 하나의 값을 돌려줍니다. 인수(argument)는 아래 중 하나가 될 수 있습니다.
·상수
·가변적인 값
·변수
·표현식
(나) 단일행 함수의 기능
·Query시 각 행에 대해 수행합니다.
·행 당 한 개의 결과를 돌려줄 수 있습니다.
·참조시 사용한 자료형과 다른 자료형의 데이터 값을 돌려줄 수 있습니다.
·한 개 이상의 인수(argument)를 필요로 합니다.
·함수를 중첩하여 사용할 수 있습니다.
·SELECT, WHERE 및 ORDER BYW절에도 함수를 사용 할 수 있습니다.
function_name (cloumn｜expression, [arg1, arg2,...])
※ function_name 함수의 이름입니다.
column 데이터베이스에 존재하는 column 명
expression 어떤 문자스트링 또는 계산된 표현식입니다.
arg1, arg2 함수에 의해 사용될 인수입니다.
(다) 복수행 함수
이 함수들은 복수의 행을 조작하여 복수의 행에 대해 하나의 결과를 돌려줍니다.
(1)문자함수
단일행 문자 함수들은 문자 데이타를 입력으로 받아들여 문자나 숫자결과 값을 돌려줍니다.
①LOWER(column＼expression) : 알파벳 문자를 소문자로 바꾸어 줍니다.
②UPPER (column＼expression) : 알파벳 문자를 대문자로 바꾸어 줍니다.
③INITCAP (column＼expression) :알파벳 문자 값을 각 단어의 첫 자는 대문자로, 모든 다른 문자는 소문자로 바꾸어 줍니다.
④CONCAT(column1＼expression1,column2＼expression 2) : 첫 번째의 문자값과 두 번째의 문자값을 연결합니다. 연결연산자(∥)와 동일함.
⑤SUBSTR (column＼expression,m[n]) : 문자 값 중에서 m 위치에서 n 문자길이에 해당되는 문자를 돌려줍니다.
⑥LENGTH(column＼expression) : 문자 갯수를 값으로 돌려줍니다.
⑦NVL(cloumn＼expression1, column＼expression2) : 첫 번째 값이 널이면 두 번째 값으로 바꾸어 줍니다.
name에 '님'을 추가 입력 후 code가 101이면 수영부, 102이면 테니스부,103이면 볼링부......등으로 출력.
SQL> select name||'님' "성명",
2 decode(code,101,'수영부',102,'테니스부',
3 103,'볼링부',104,'탁구부',105,'농구부') "종목부서"
4 from member;
성명 종목부서
------------ --------
이정석님 볼링부
강호식님 탁구부
..........................
김길수님 수영부
이석준님 테니스부
17 개의 행이 선택되었습니다.
회원의 addr column의 주소를 가지고 시별로 나누어 거주지항목을 만들어 출력하고 종목별로 code가 101이면 수영부, 102이면 테니스부,103이면 볼링부......등으로 출력.
SQL> select no "번호", name "회원이름", addr "주 소",
2 substr(addr,1,2)||'시' "거주지",
3 decode(code,101,'수영부',102,'테니스부',103,'볼링부',104,'탁구부',
4 105,'농구부') "종목부서" from member;
번호 회원이름 주 소 거주지 종목부서
-- ------------------------------------- ------ --------
1 이정석 서울 종로구 창신동 14-1 서울시 볼링부
2 강호식 서울 관악구 신림3동 45-2 서울시 탁구부
..........
15 이석준 성남시 분당구 내정동 4-5 성남시 테니스부
15 개의 행이 선택되었습니다.
(라). 숫자 함수
숫자 함수는 숫자 값을 받아들여 숫자 값을 되돌려주는데, 이러한 몇가지 숫자 함수들을 살펴봅니다.
①ROUND (column/expression, n) : Column, Expression의 값을 소수점 n자기까지 반올림한다. 만약 n이 없다면 소숫점은 없어진다. 또한 n값이 음수라면 소숫점의 왼쪽 자리 수만큼 반올림됩니다.
②TRUNC(column/expression, n) : Column, Expression의 값을 소수점 n자리까지 절삭한다. 만약 N이 없다면 소숫점은 나타나지 않는다. 또한 N값이 음수라면 소숫점의 왼쪽 자리 수만큼 반올림된다.
③MOD(m, n) ; m값을 n값으로 나누고 남은 나머지를 Return한다.
소숫점 2자리, 0, -1자리까지 반올림한 값을 Display하시오.
SQL> select round(95.735,2), round(75.736,0), round(36.534,-1)
2 from sys.dual;
ROUND(95.735,2) ROUND(75.736,0) ROUND(36.534,-1)
--------------- --------------- ----------------
95.74 76 40
점수가 70보다 큰 모든 회원에 대해 점수를 6으로 나누고 난 나머지를 계산하십시오.
SQL> select no, name, mod(jumsu,6),code
2 from member
3 where jumsu > 70;
NO NAME MOD(JUMSU,6) CODE
-------- ---------- ------------ ---------
1 이정석 2 103
3 이순민 5 101
7 김찬옥 0 105
.....................................
15 이석준 3 102
11 개의 행이 선택되었습니다.
(마). 날짜 함수
날짜 함수는 오라클의 날짜에 대해 연산을 합니다. 모든 날짜 함수는 DATE 자료형의 값을 돌려주는데 MONTHE_BETWEEN은 예외적으로 숫자 값을 돌려줍니다.
①MONTHS_BETWEEN(date1, date2) : date1과 date2 사이의 달수를 찾아줍니다. 결과는 양수 또는 음수가 가능합니다. date1이 date2보다 빠른 날짜면 결과는 양수지만 date2가 date1보다 빠른 날짜면 결과는 음수입니다. 결과의 비정 수 부분은 월의 부분을 나타냅니다.
②ADD_MONTHS(date, n) : 날짜에 n달을 추가합니다. n은 정수여야 하며 음수가 가능합니다.
③NEXT_DAY(date,'char') : date 다음의 해당주('cher')의 다음 요일를 찾아줍니다. char은 날 또는 문자 스크린을 나타내는 요일입니다.
④ROUND(date[, 'fmt']) : 어떤 형식 모델 (fmt) 명시되지 않았을 시 자정까지 시간 성정과 함께 날짜를 돌려줍니다. 다른 시간을 갖고 있는 날짜 비교시 유용합니다.
⑤TRUNC(date[,'fmt']) : 어떤 형식 모델(fmt)가 명시되지 않을시 date에 포함되어 있는 그날의 자정을 찾아줍니다. 만약 fmt=YEAR이면 DATE포함한 해의 첫 번째 날을 찾아줍니다. 이런 함수는 날짜의 시간 부분을 제거하기를 원할시 유용합니다.
(1)오라클의 날짜 저장
오라클은 다음을 나타내기 위해 날짜를 내부적인 숫자 형식으로 저장합니다.
·Century
·Year
·Month
·Day
·Hours
·Minutes
·Seconds
(2) SYSDATE
SYSDATE는 현재의 날짜와 시간을 돌려주는 날짜 함수입니다. 여러분은 column을 사용하는 것과 마찬가지로 SYSDATE를 사용할 수 있습니다. 예를 들자면 테이블에서 SYSDATE를 선택함으로 현재 날짜를 Display 할 수 있습니다. Dummy 테이블인 여미에서 SYSDATE를 선택하는게 일반적입니다.
(3) DUAL
DUAL 테이블은 SYS User가 소유하고 있으며 모든 사용자가 액세스할 수 있습니다. 이 테이블은 한 개의 column, DUMMY를 갖고 있으며 "X"라는 값을 가진 한 개의 행으로 되어 있습니다. DUAL 테이블은 사용자 데이타가 있는 테이블에서 유래하지 않은 상수값, 의사 열(pseudo-column), 표현식등의 값을 단 한번만 돌려 받기를 원할시 유용합니다.
DUAL 테이블 사용하여 현재 날짜를 Display 합니다.
SQL> select sysdate from sys.dual;
SYSDATE
---------
15-SEP-99
회원가입한지 5개월 미만인 모든 회원들의 번호, 가입일, 수업월수 및 입사 3개월 되는 날짜를 Display 하십시오
SQL> select no, name, gaip,
2 months_between (sysdate, gaip)tenure,
3 add_months(gaip, 3) review
4 from member
5 where months_between (sysdate,gaip)<5;
NO NAME GAIP TENURE REVIEW
--------- ---------- -------- --------- --------
1 이정석 99/09/01 .45840614 99/12/01
8 남호정 99/05/20 3.8455029 99/08/20
10 이수영 99/07/25 1.6842126 99/10/25
※ROUND와 TRUNC 함수들은 숫자 및 날짜 값에 대해서 사용할 수 있습니다. 이 함수들을 날짜에 대해 사용시 명시한 형식의 모델로 반올림하거나 절삭합니다. 따라서 여러분은 날짜를 가장 가까운 년 또는 월로 반올림 할수 있습니다.
1997년에 가입한 모든 회원들에 대한 가입일자를 ROUND 및 TRUNC 함수를 사용하여 번호, 이름, 전화번호, 가입일, 가입 월을 Display 하고 이를 비교해 보십시오.
SQL> select no, name, tel, gaip,
2 round(gaip,'month'),
3 trunc(gaip,'month')
4 from member
5 where gaip like '%97';
NO NAME TEL GAIP ROUND(GAI TRUNC(GAI
3 이순민 000-0000 30-APR-97 01-MAY-97 01-APR-97
4 황의수 391-9192 20-MAR-97 01-APR-97 01-MAR-97
6 이민순 888-1212 20-AUG-97 01-SEP-97 01-AUG-97
①TO_CHAR(number\date,['fmt']) : 숫자 또는 날짜값을 fmt 형식 모델을 사용하여 VARCHAR2 문다스트링으로 바꾸어 줍니다.
②TO_NUMBER(char) : 숫자를 포함하고 있는 문자 스트링을 숫자로 전환합니다.
③TO_DATE(char,['fmt']) : 날짜를 나타내는 문자스트링을 명시한 fmt에 따라 날짜값으로 바꾸어 줍니다. fmt를 생략시 형식은 DD-MON-YY가 됩니다.
|
