제 7장 함수를 사용한 데이터 검색
SQL에서는 질의를 효과적으로 하고, 데이터 값을 조작할 수 있도록 여러 가지 함수들을 제공한다. SQL 함수는 일반 함수와 같이 argument로 값을 전달받아 연산을 한 후에 결과 값을 돌려준다. 함수의 종류에 따라 argument가 없을 수도 있으나 일반적으로 하나 이상을 갖는다.
function( argument, argument, . . . )
함수 내의 argument로는 column명이나 문자, 숫자 또는 표현식이 올 수 있으며, argument로 함수를 사용하는 식으로 함수를 중첩해서 사용할 수도 있다. SQL 함수는 SELECT절을 포함하여 WHERE 및 ORDER BY 절에도 사용할 수 있다. SQL 함수는 단일행 함수와 복수행 함수로 구분된다. 단일행 함수는 각각의 단일 행에 대해 수행하여 행 당 하나의 결과를 돌려주고, 복수행 함수는 복수개의 행에 대해 수행하여 복수개의 행에 대한 하나의 결과를 돌려준다.
7.1 단일행 함수
단일행 함수는 하나의 값에 대해 계산을 하거나 조작하기 위해 사용한다. 단일행 함수는 숫자 값을 전달받아 계산을 하거나 조작을 하는 숫자 함수와 문자 값을 전달받아 조작하거나 문자의 수를 돌려주는 문자 함수, 날짜 값을 전달받아 계산을 하거나 조작을 하는 날짜 함수 그리고, 문자나 숫자, 날짜 값 등을 전달받아 데이터 형식을 바꾸어 주는 변환 함수로 구분한다.
7.1.1 숫자 함수
숫자 함수는 숫자 값을 받아들여 계산이나 조작을 하여 숫자 값을 되돌려 준다. 자주 사용되는 숫자 함수 정리.
* 함수 기능
- ABS(n) : 절대값 구함
- COS(n), SIN(n), TAN(n) : 삼각함수
- CEIL(n) : 가장 근접한 같거나 큰 정수를 돌려줌
- FLOOR(n) : 가장 근접하면서 같거나 작은 정수를 돌려줌
- EXP(n) : e= 2.71828183에 대한 멱승(power)을 돌려줌
- LN(n) : 자연대수(natural logarithm)값을 돌려줌
- LOG(m, n) : Logm n
- MOD(m, n) : m을 n으로 나눈 나머지를 돌려줌
- POWER(m, n) : mn을 돌려줌
- ROUND(m[, n]) : m을 소수점 이하가 n자리에서 반올림 또는 버림
- TRUNC(m[, n]) : n이 음수이면 소수점 이상으로 자리 수를 반영, n을 생략시 n=0으로 처리
- SIGN(n) : n=0이면 0을, n>0이면 1을, n<0이면 -1을 돌려줌
- SQRT(n) : root n
* ROUND()와 TRUNC() 함수
ROUND()와 TRUNC()는 전달되는 값을 반올림하거나 버리는 함수이다. 이 함수들은 두 개의 argument를 사용하는데 첫 번째 argument로는 대상이 되는 값이 전달되고, 두 번째 argument는 반올림이나 버림 위치를 지정하는 값이 입력된다. 두 번째 argument는 생략될 수 있으며, 생략되면 0로 간주된다. 만약, 두 번째 argument가 양수이면 소수점이하로 그 자리 수에서 반올림이나 버림을 수행하고, 음수이면 반올림이나 버리는 자리 수를 소수점 이상으로 반영한다. 그리고, 0이라면 소수점이 없는 형태가 되도록 반올림하거나 버린다.
* 문법 설명
- ROUND(m[, n]) : m을 소수점 이하 n자리에서 반올림
n이 음수이면 소수점 이상 n자리에서 반올림
n을 생략하면 n=0으로 처리
- TRUNC(m[, n]) : m을 소수점 이하 n자리 이후를 버림
n이 음수이면 소수점 이상 n자리 값까지 버림
n을 생략하면 n=0으로 처리
ROUND()와 TRUNC()의 적용 예를 들어보면 다음과 같다.
ROUND(17.145, 2)= 17.15 TRUNC(17.145, 2)= 17.14
ROUND(17.145, 0)= 17 TRUNC(17.145, 0)= 17
ROUND(17.145, -1)= 20 TRUNC(17.145, -1)= 10
* 실습예제: 위의 예제를 SQL 구문 실행시켜 결과를 비교해 보시오.
SQL> SELECT ROUND(17.145, 2), ROUND(17.145, 0), ROUND(17.145, -1) FROM DUAL;
SQL> SELECT TRUNC(17.145, 2), TRUNC(17.145, 0), TRUNC(17.145, -1) FROM DUAL;
SQL> SELECT ROUND(17.145, 2), ROUND(17.145, 0), 2 ROUND(17.145, -1) 3 FROM DUAL;
ROUND(17.145,2) ROUND(17.145,0) ROUND(17.145,-1)
--------------- --------------- ----------------
17.15 17 20
SQL> SELECT TRUNC(17.145, 2), TRUNC(17.145, 0), 2 TRUNC(17.145, -1) 3 FROM DUAL;
TRUNC(17.145,2) TRUNC(17.145,0) TRUNC(17.145,-1)
--------------- --------------- ----------------
17.14 17 10
* 실습문제: St_Schedule 테이블에서 학생의 과목별 점수(score)를 단 자리를 반올림하는 SQL 구문을 작성하시오.
☞Dummy 테이블 DUAL을 사용하는 경우
우리가 검색을 수행할 때는 반드시 대상 테이블을 지정해야 한다. 그러나 어느 경우에는 특정 테이블을 참조하지 않는 작업을 할 수도 있다. 이 때 사용할 수 있도록 Oracle에서 제공하는 임시 테이블이 DUAL 테이블이다.
SQL> SELECT ROUND(3.144, 1), SYSDATE FROM DUAL;
ROUND(3.144,1) SYSDATE
-------------- ----------------
3.1 99/07/29
여기에서 SYSDATE는 현재의 날짜와 시간을 돌려주는 날짜 함수이다.
함수의 argument로 산술식을 사용할 수 있다. 예를 들어 St_Schedule 테이블에서 학생의 과목별 점수(score)를 100점 만점 기준에서 30점 만점으로 환산하려고 한다면 다음과 같이 SQL문을 작성할 수 있다.
SQL> SELECT stid, score, ROUND(score*30/100) FROM st_schedule;
함수의 argument로 다른 함수를 사용할 수도 있다. 예를 들어 root 2에 대해서 소수점 이하 3자리를 반올림하는 SQL 문은 다음과 같다.
SQL> SELECT ROUND(SQRT(2), 3) FROM DUAL;
* CEIL()과 FLOOR() 함수
CEIL()과 FLOOR() 함수는 전달된 값에 가장 근접한 정수를 돌려주는 함수이다. CEIL()은 위로 가장 근접한 정수를 반환하고 FLOOR()은 아래로 가장 근접한 정수를 반환한다. 그래서, FLOOR(n)= TRUNC(n)이 성립된다.
* 문법 설명
CEIL(n) 가장 근접한 같거나 큰 정수를 돌려줌
CEIL(15.7)= 16, CEIL(15.1)= 16, CEIL(15.0)= 15
FLOOR(n) 가장 근접하면서 같거나 작은 정수를 돌려줌
FLOOR(n)은 TRUNC(n, 0)과 같은 효과를 갖는다.
FLOOR(15.7)= 15, FLOOR(15.1)= 15, FLOOR(15.0)= 15
CEIL()과 FLOOR()의 적용 예를 들어보면 다음과 같다.
CEIL(17.5)= 18 FLOOR(17.5)= 17
CEIL(17.1)= 18 FLOOR(17.1)= 17
CEIL(17.0)= 17 FLOOR(17.0)= 17
* 실습예제: 위의 예제를 SQL 구문 실행시켜 결과를 비교해 보시오.
SQL> SELECT CEIL(17.5), CEIL(17.1), CEIL(17.0) FROM DUAL;
SQL> SELECT FLOOR(17.5), FLOOR(17.1), FLOOR(17.0) FROM DUAL;
SQL> SELECT CEIL(17.5), CEIL(17.1), CEIL(17.0) 2 FROM DUAL;
CEIL(17.5) CEIL(17.1) CEIL(17.0)
---------- ---------- ----------
18 18 17
SQL> SELECT FLOOR(17.5), FLOOR(17.1), FLOOR(17.0) 2 FROM DUAL;
FLOOR(17.5) FLOOR(17.1) FLOOR(17.0)
----------- ----------- -----------
17 17 17
* 실습문제: St_Schedule 테이블에서 학생의 과목별 점수(score)를 100점 만점 기준에서 30점 만점으로 환산하려고 한다. 이 때 환산된 결과를 소수점이하를 무조건 올림 했을 경우하고, 무조건 버렸을 때하고 비교하는 SQL문을 작성하시오.
MOD()는 전달된 값을 지정한 값으로 나누고 남은 잉여 값을 반환해 주는 함수이다.
* 문법 설명
MOD(m, n) m을 n으로 나누고 남은 값은 반환
MOD(13, 3)= 1
* 실습예제: St-Schedule 테이블에서 학생 성적 중에 단 자리가 4거나 9이면 점수에 1을 더해주려고 한다. 이 문제를 해결하기 전에 원래 점수와 이 조건으로 점수를 올려 주었을 때의 점수를 비교할 수 있는 SQL문을 적어 보시오.
SQL> SELECT stid, score, FLOOR(score + MOD(score, 5)/4) FROM st_schedule
WHERE MOD(score, 5) = 4;
STID SCORE FLOOR(SCORE+MOD(SCORE,5)/4)
--------- --------- ---------------------------
980007 84 85
960001 94 95
7.1.2 문자 함수
문자 함수들은 표 7-2와 같이 문자 값을 입력으로 받아들여 문자나 숫자를 결과로 반환한다.
* 함수 기능
CONCAT(c1, c2) : c1과 c2 문자를 연결, || 연산자와 동일
INITCAP(c) : 각 단어의 알파벳 첫 문자만 대문자로 변경
LOWER(c) : 모든 문자의 알파벳을 소문자로 변경
UPPER(c) : 모든 문자의 알파벳을 대문자로 변경
LPAD(c1, n[, c2]) : c1을 n길이가 되도록 c2를 반복해서 왼쪽부터 붙임
RPAD(c1, n[, c2]) : c1을 n길이가 되도록 c2를 반복해서 오른쪽부터 붙임
LTRIM(c[, set]), RTRIM(c[, set]) : c의 가장 왼쪽부터(또는 오른쪽) set에 포함된 각 문자가 나타날 때까지 문자를 제거
REPLACE(c, s1[, s2]) : c에서 s1를 찾아서 s2로 모두 바꿈
SUBSTR(c, m[, n]) : c에서 m번째 위치부터 n개 문자를 돌려줌
LENGTH(c) : c의 문자 개수를 돌려줌
표 7-2 문자 함수
⼆ INITCAP(), LOWER(), UPPER()
INITCAP()는 연속되는 문자열 중에 맨 앞에 오는 알파벳 문자만 대문자로 변환해 주는 함수이고, LOWER()는 알파벳 문자들을 모두 소문자로 UPPER()는 모두 대문자로 변환하는 함수이다.
⼧실습예제: Department 테이블에서 영문 학과명(ename)을 INITCAP()와 LOWER(), UPPER() 함수를 사용하여 그 결과를 비교하시오.
SQL> SELECT LOWER(ename), INITCAP(ename), UPPER(ename) FROM department;
LOWER(ENAME) INITCAP(ENAME) UPPER
------------------------------- ------------------------------- -----
arts Arts ARTS
architectural engineering Architectural Engineering ARCHI
civil engineering Civil Engineering CIVIL
business management Business Management BUSIN
electronics Electronics ELECT
information and communications Information And Communications INFOR
food science and nutrition Food Science And Nutrition FOOD
preschool education Preschool Education PRESC
8 개의 행이 선택되었습니다.
◐실습문제: Subject 테이블의 영문 과목명에 대해 INITCAP()와 LOWER(), UPPER() 함수를 사용하여 그 결과를 비교하시오.
⼆ LPAD(), RPAD()
LPAD()나 RPAD()는 전달된 문자의 오른편이나 왼편에 지정한 문자를 반복해서 덧붙여서 지정한 길이가 되도록 하는 함수이다. 여기에서 argument로 입력하는 반복문자는 생략해도 되며 한 문자 이상의 문자 스트링도 가능하다. 만약 생략한다면 공백(space) 하나로 간주하여 처리한다.
문법 설명
LPAD(c1, n[, c2])
⼘c1을 n길이가 되도록 c2를 반복해서 왼쪽부터 붙임
⼘c2가 생략되면 c2는 공백 하나로 간주한다.
RPAD(c1, n[, c2])
⼘c1을 n길이가 되도록 c2를 반복해서 오른쪽부터 붙임
⼘c2가 생략되면 c2는 공백 하나로 간주한다.
⼧실습예제: Department 테이블에서 영문 학과명(ename)을 맨 우측에 나타나는 공백만큼 '-'문자로 모두 채워서 원래 영문 학과명과 비교하는 SQL 구문을 작성하시오.
SQL> SELECT ename, RPAD(ename, 40, '.-.') FROM department;
ENAME RPAD(ENAME,40,'.-.')
--------------------------------- ----------------------------------
Arts Arts.-..-..-..-..-..-..-..-..-.._.
Architectural Engineering Architectural Engineering.-..-..-.
Civil Engineering Civil Engineering.-..-..-..-..-..-
Business Management Business Management.-..-..-..-..-.
Electronics Electronics.-..-..-..-..-..-..-..-
Information and Communications Information and Communications.-..
Food Science and Nutrition Food Science and Nutrition.-..-..-
Preschool Education Preschool Education.-..-..-..-..-.
8 개의 행이 선택되었습니다.
⼆ LTRIM(), RTRIM()
LTRIM()이나 RTRIM()은 전달된 문자의 가장 오른편이나 왼편에서부터 지정한 문자가 나타나는 동안 모두 제거하는 함수이다.
* 문법 설명
LTRIM(c[, set])
⼘c의 가장 왼쪽부터 set에 포함된 각 문자가 나타날 때까지 문자를 제거한다.
⼘set을 생략하면 공백 하나로 간주된다.
⼘만약, set= 'xy'라면 c의 맨 좌측부터 연이어 나타나는 'x'나 'y'는 모두 제거한다.
RTRIM(c[, set])
⼘c의 가장 오른쪽부터 set에 포함된 각 문자가 나타날 때까지 문자를 제거한다.
예를 들어, RTRIM('abcdxyyxayxy', 'xy')라고 입력을 하면, 함수는 입력 문자 스트링 'abcdxyyxayxy'의 오른쪽부터 'x'나 'y'를 찾아가면서 제거하고, 만약 찾기에 실패한다면 실행을 멈추고 그때까지의 결과를 반환한다. 따라서, RTRIM() 함수는 'abcdxyyx'를 반환한다.
SQL> SELECT RTRIM('abcdxyyxayxy', 'xy') FROM DUAL;
RTRIM('AB
---------
abcdxyyxa
⼧실습예제: Department 테이블에서 영문 학과명(ename)의 맨 우측에 's'를 포함하고 있을 경우 's'를 제거하여 나타나도록 하는 SQL을 작성해 보시오.
SQL> SELECT ename, RTRIM(ename, 's')FROM department;
ENAME RTRIM(ENAME,'S')
-------------------------------------- -----------------------------
Arts Art
Architectural Engineering Architectural Engineering
Civil Engineering Civil Engineering
Business Management Business Management
Electronics Electronic
Information and Communications Information and Communication
Food Science and Nutrition Food Science and Nutrition
Preschool Education Preschool Education
8 개의 행이 선택되었습니다.
RTRIM()이나 LTRIM()은 지정한 문자를 오른편이나 왼편부터 제거하게되므로 만약에 RTRIM(' ')을 수행한다면 그 결과는 NULL이 된다.
Instructor 테이블에서 전자우편 주소가 NULL인 행은 아래와 같이 2개 행이다.
SQL> SELECT hname, email FROM instructor WHERE email IS NULL;
HNAME EMAIL
---------- ----------------------------------------
강성호
그러나, Instructor 테이블 모든 행을 검색해 보면 전자우편주소가 없는 행이 세 개행이 출력된다. 이것은 전자우편주소 중에 NULL이 아닌 공백이 들어가 있는 행이 한 행이 있다는 의미이다. 현재 Instructor 테이블의 행의 수가 적으니까 이 한 행을 찾는 것은 그리 어려운 일이 아닐 것이다. 그러나, 테이블 행의 수가 수천 또는 수만 건이라고 했을 때 어떻게 전자우편주소가 공백인 행들을 찾아낼 수 있을까? 찾기가 어려운 것은 행의 수일뿐만 아니라 공백의 길이가 각기 다 다를 수 있다는 점이 더 문제이다. 해결 방법은 있다. 바로 아래와 같이 WHERE절에 RTRIM()이나 LTRIM()을 사용하면 된다.
SQL> SELECT hname, email FROM instructor WHERE RTRIM(email) IS NULL;
HNAME EMAIL
---------- ----------------------------------------
강성호
⼆ REPLACE()
REPLACE()는 전달된 문자에 지정한 문자 스트링이 존재하면 지정한 문자 스트링으로 바꾸어 주는 함수이다. 대치가 될 문자 스트링은 생략할 수 있는데 만약 생략한다면 NULL로 간주하여 처리한다. 대치 문자가 NULL이라고 하는 것은 일치하는 문자를 제거한다는 의미와 같다.
* 문법 설명
REPLACE(c, s1[, s2])
⼘c에서 s1 스트링을 찾아서 s2로 바꾸어 준다.
⼘s2가 생략되면 s2는 NULL로 간주된다.
⼧실습예제: Instructor 테이블의 전자우편주소(email) 칼럼 값 중에 공백을 육안으로 볼 수 있도록 공백을 모두 'Z'로 대치하여 검색하는 SQL 구문을 작성해 보시오.
SQL> SELECT email, REPLACE(email, ' ', 'Z') FROM instructor;
EMAIL REPLACE(EMAIL,'','Z')
--------------------------------------- ----------------------
shhan@oooo.ac.kr shhan@oooo.ac.kr
1 개의 행이 선택되었습니다.
⼆ SUBSTR()
SUBSTR()은 전달된 문자에 지정한 위치에서부터 시작하여 지정한 개수만큼의 문자를 반환하는 함수이다.
반환 시작 위치 값은 정수 또는 실수 값이 올 수 있다. 만약, 실수 값을 입력한다면 TRUNC()를 적용한 것과 같이 무조건 소수점 밑에서 올림을 한 값을 사용한다. 그리고, 음수와 양수를 모두 사용할 수 있는데 양수를 사용하면 왼쪽부터 시작위치가 결정되고, 음수이면 오른쪽부터 시작위치가 결정되며, 0일 경우는 외쪽 첫 번째부터 시작한다.
반환할 문자 길이 값이 생략되면 n=∞로 간주하여 시작 위치부터 끝까지 반환하고, n<1 이면 NULL을 반환한다.
* 문법 설명
SUBSTR(c, m[, n])
⼘c의 m번째 문자에서부터 n개 문자를 반환한다.
⼘m=0이면 왼쪽에서 첫 번째부터 처리한다.
⼘m이 양수이면 왼쪽부터 m번째부터 처리한다.
⼘m이 음수이면 오른쪽부터 m번째부터 처리한다.
⼘n이 생략되면 그냥 n=∞로 간주한다.
⼘n<1 이면 NULL을 반환한다.
⼧실습예제: Department 테이블의 영문 학과명을 앞에서 네 번째까지만 출력되도록 하는 SQL 구문을 작성해 보시오.
SQL> SELECT deptno, SUBSTR(ename, 1.3, 4) FROM department;
DEPTNO SUBSTR(E
--------- --------
10 Arts
20 Arch
30 Civi
3 개의 행이 선택되었습니다.
⼆ LENGTH()
LENGTH()은 문자의 길이 값을 숫자 형태로 반환하는 함수이다.
만약에 입력 문자가 맨 뒤에 연속되는 공백을 포함하고 있다면 그 숫자만큼 반환되는 길이 값에 포함된다. 또 입력 값이 NULL이면 반환되는 값도 NULL이 된다. 문 법
LENGTH(c)
⼘c의 문자 길이를 반환한다.
⼘c의 맨 뒤에 나타날 수 있는 연속되는 공백이 있다면 그 길이도 포함된다.
⼘c가 NULL이면 NULL 값을 반환한다.
⼧실습예제: Instructor 테이블의 전자우편주소와 문자 길이를 출력하는 SQL 구문을 작성해 보시오.
SQL> SELECT email, LENGTH(email) FROM instructor;
EMAIL LENGTH(EMAIL)
---------------------------------------- -------------
답> SELECT SUBSTR(email, 1, LENGTH(email) - LENGTH('@hangil.bc.ac.kr')) FROM instructor;
7.1.3 날짜 함수
날짜 함수들은 표 7-3과 같이 날짜 형식의 값을 받아들여서 날짜 계산 결과를 반환하는 함수이다.
* 함수 기능
ADD_MONTHS(d, n) : d 날짜에 n 달수를 더한다.
LAST_DAY(d) : 달의 마지막 일자를 찾아준다.
MONTHS_BETWEEN (d1, d2) : d1과 d2의 차이나는 달수를 찾아준다.
NEXT_DAY(d, day) : d날 이후의 첫 번째 지정한 요일에 해당하는 날짜를 찾아준다.
ROUND(d[, fmt])
TRUNC(d[, fmt]) : 가장 가까운 시간이나 일, 월, 년 등으로 반올림하거나 버린다.
⼆ ADD_MONTHS()
ADD_MONTHS()는 입력된 날짜에 지정한 달수를 더한 날짜를 반환하는 함수이다. 이 함수는 달수를 더하여 12개월이 초과되면 연도도 함께 고려하여 계산한다. 그리고, 더하는 달 값을 음수로 입력하면 날짜를 거슬러서 계산하는 효과가 있다.
* 문법 설명
ADD_MONTHS(d, n)
⼘d 날짜에 n 달수를 더한다.
⼘n을 음수로 입력하면 빼기 계산 효과가 있다.
⼧실습예제: 현재 날짜로부터 6개월 이전의 날짜와 6개월 이후의 날짜를 출력하는 SQL 구문을 작성하시오.
SQL> SELECT SYSDATE, ADD_MONTHS(SYSDATE, -6), ADD_MONTHS(SYSDATE, 6) FROM DUAL;
SYSDATE ADD_MONTHS(SYSDA ADD_MONTHS(SYSDA
---------------- ---------------- ----------------
99/07/30 99/01/30 00/01/30
⼆ LAST_DAY()
LAST_DAY()는 입력된 날짜 달의 마지막 일을 반환하는 함수이다.
* 문법 설명
LAST_DAY(d)
⼘d 날짜 달의 마지막 일을 반환한다.
⼧실습예제: 이번 달이 몇 일 남았는지 알아보는 SQL 구문을 작성하시오.
SQL>
SELECT SYSDATE, LAST_DAY(SYSDATE), LAST_DAY(SYSDATE) - SYSDATE FROM DUAL;
SYSDATE LAST_DAY(SYSDATE LAST_DAY(SYSDATE)-SYSDATE
---------------- ---------------- -------------------------
99/07/30 99/07/31 1
⼆ MONTHS_BETWEEN()
MONTHS_BETWEEN()은 입력된 날짜 달의 마지막 일을 반환하는 함수이다.
달수 계산은 앞에 명시한 날짜에서 뒤에 명시한 날짜를 빼는 식으로 계산되므로 계산 결과는 음수가 나올 수 있다. 계산 결과에서 비정수 부분이 나올 수 있는데 이 값은 두 날짜 사이의 달수를 계산하고 남은 일수에 대한 환산된 달수이다.
* 문법 설명
MONTHS_BETWEEN
d1, d2)
⼘d1과 d2 사이의 달수를 반환한다.
⼘(d1 - d2)로 계산한다.
⼘반환된 값에서 소수점 이하 값은 잔여 일수에 대한 환산된 달수이다.
⼧실습예제: Student 테이블의 생년월일(birthday) 칼럼을 이용해 학생들이 지금까지 몇 달을 살아 왔는지 알아보는 SQL 구문을 작성하시오.
SQL> SELECT SYSDATE, birthday, MONTHS_BETWEEN(SYSDATE, birthday) FROM student;
SYSDATE BIRTHDAY MONTHS_BETWEEN(SYSDATE,BIRTHDAY)
---------------- ---------------- --------------------------------
99/07/31 78/07/16 252.49936
99/07/31 78/03/03 256.91872
99/07/31 77/12/12 259.62839
⼆ NEXT_DAY()
NEXT_DAY()는 지정한 날 이후의 첫 번째 지정한 요일에 해당하는 날짜를 반환하는 함수이다. 이 때 요일 지정 방법은 '일요일' 또는 'SUNDAY'와 같이 요일에 대한 완전한 문자식 표기방법과 '일' 또는 'SUN'과 같이 일반적인 약식 표기 방법, 그리고 1에서 7까지 숫자를 요일로 사용하는 세 가지 형태의 방법이 있다. 요일을 숫자로 표현할 때는 일요일(1)부터 월요일(2), 화요일(3), 수요일(4), 목요일(5), 금요일(6), 토요일(7) 순으로 대응시켜 사용한다.
* 문법 설명
NEXT_DAY(d, day)
⼘d 날 이후의 첫 번째 c요일에 해당하는 날짜를 찾아준다.
⼘day에 올 수 있는 요일표시 형식 (일요일 경우)
-완전형식 : '일요일' 또는 'SUNDAY'
-축약형식 : '일' 또는 'SUN'
-숫자형식 : 1
⼧실습예제: 돌아오는 토요일이 몇 일인지 알아보시오.
SQL> SELECT SYSDATE, NEXT_DAY(SYSDATE, '토요일'), NEXT_DAY(SYSDATE, '토'), NEXT_DAY(SYSDATE, 7) FROM DUAL;
SYSDATE NEXT_DAY(SYSDATE NEXT_DAY(SYSDATE NEXT_DAY(SYSDATE
---------------- ---------------- ---------------- ----------------
99/07/31 99/08/07 99/08/07 99/08/07
ROUND()와 TRUNC() 함수는 숫자뿐만 아니라 날짜 값에 대해서도 적용할 수 있다.
* 문법 설명
ROUND(d[, format])
TRUNC(d[, format])
⼘날짜를 format 기준으로 반올림을 하거나 버림을 수행하여 날짜 값을 반환한다.
날짜 값에 반올림이나 버림을 적용하려면은 날짜 값과 함께 반올림이나 버림의 기준이 되는 날짜 형식을 지정해 주어야 한다. 여러 가지 날짜 형식모델의 소개와 반올림 방법을 중심으로 표 7-4에 정리하였다.
7.1.4 변환 함수
변환 함수들은 표 7-5와 같이 입력 값의 형식을 지정한 형식으로 변환해준다.
* 함수 기능
TO_CHAR(n|d[, fmt]) : 숫자나 날짜 형식을 문자 형식으로 변환한다.
TO_DATE(c[, fmt]) : 문자로 표기된 날짜를 날짜 형식으로 변환한다.
TO_NUMBER(c) : 문자 형식을 숫자 형식으로 변환한다.
⼆ TO_CHAR()에서의 날짜형식 지정
TO_CHAR() 함수로 반환되는 날짜 형식 지정은 인용부호(' ') 안에 표 7-7에 있는 날짜 형식 요소들을 복합적으로 사용할 수 있고, -, /, <, (, ), : 등과 같은 특수문자도 함께 사용할 수 있다. 인용부호 내의 형식지정과 함께 특수문자가 아닌 일반 문자를 입력하려면 이중인용부호(" ")로 감싸서 써주면 된다. 또한, 형식변환 후에 생길 수 있는 공백을 없애주기 위해서 'FM'이라는 접두사를 형식지정 요소로 함께 사용할 수 있다. 형식 지정은 생략하면 기본 형식이 적용된다.
⼧실습예제: 현재 일시를 "yyyy년-mm월-dd일, x요일, hh24:mi:ss" 형식으로 보는 SQL 구문을 작성하시오.
SQL> SELECT SYSDATE, TO_CHAR(SYSDATE, 'YYYY"년"-fmMONTH-DD"일", (DAY), HH24:MI:SS')
FROM DUAL;
SYSDATE TO_CHAR(SYSDATE,'YYYY"년"-FMMONTH-DD"일",(DAY),HH24:
---------------- ----------------------------------------------------
99/07/31 1999년-7월-31일, (토요일), 17:16:59
⼆ TO_CHAR()에서의 숫자형식 지정
TO_CHAR() 함수로 반환되는 숫자 형식 지정은 인용부호(' ') 안에 표 7-8에 있는 숫자 형식 요소들을 복합적으로 사용할 수 있다. 형식변환 후에 생길 수 있는 공백을 없애주기 위해서 'FM'이라는 접두사를 형식지정 요소로 함께 사용할 수 있다. 형식 지정은 생략할 수 있으며, 생략하면 기본 형식이 적용된다.
Oracle DBMS에서는 지정한 형식보다 숫자 값이 초과되면 원래 값 대신에 # 스트링을 출력하고, 소숫점 이하 자리 값이 초과되면 형식 요소가 적용되는 부분까지 반올림이 적용시켜 반환한다.
SQL> SELECT score, TO_CHAR(score), TO_CHAR(score, '999') FROM st_schedule
WHERE stid= 980007;
SCORE TO_CHAR(SCORE) TO_C
--------- ---------------------------------------- ----
84 84 84
93 93 93
위 예제 결과를 보면 숫자형 값인 점수(score)는 오른쪽으로 정렬되어 출력되었고, 문자형으로 변환하여 출력한 것은 왼쪽으로 정렬되어 출력되었음을 알 수 있다. TO_CHAR(score)처럼 변환될 형식을 지정하지 않음으로서 기본(default) 형식이 적용되어 숫자의 최대 표현 길이만큼의 자리 수로 출력되었다. TO_CHAR(score, '999')처럼 출력 형식을 지정함으로서 지정한대로 부호를 포함한 네 자리만 출력되었음을 알 수 있다.
⼆ TO_DATE()
TO_DATE()는 문자형으로 표현된 날짜 값을 날짜 형식으로 변환하는 함수로서 여러 가지 형태로 표현될 수 있는 문자형 날짜 값에 대한 형식 지정 방법은 TO_CHAR() 함수에서의 날짜 형식 지정 방법과 동일하게 표 7-6의 날짜형 형식요소를 사용하면 된다.
Oracle DBMS에서는 날짜 값을 숫자 형식으로 저장을 하고, 하나의 날짜 값은 세기(century), 연도, 월, 일, 시, 분, 초 정보를 모두 가질 수 있다. 이와 같은 많은 정보들 중에 우리는 일반적으로 연도-월-일 정보를 주로 사용하게 된다. 이와 같이 날짜 값은 많은 정보를 가지고 있다보니까 표현 방법 또한 다양할 수밖에 없다. Oracle에서의 날짜 값에 대한 묵시적(default) 표현 형식은 DD-MON-YY 이지만, 한글판 Personnal Oracle 7 Ver7.3.3.0.0에서는 YY/MM/DD가 기본 형식으로 사용된다.
따라서, TO_DATE() 함수는 날짜 값에 대한 표현 방법이 다양하기 때문에 묵시적 표현 방식을 고려하지 않고 사용자 취향에 따라 날짜 값을 표현할 때 아주 유용하게 사용된다.
⼧실습예제: Student 테이블에서 1978년 이전에 태어난 학생의 생년월일을 출력하는 SQL 구문을 작성하시오.
SQL> SELECT hname, birthday FROM student WHERE birthday < TO_DATE('1978-01-01', 'YYYY-MM-DD');
HNAME BIRTHDAY
---------- ----------------
aaa 77/12/12
⼆ TO_NUMBER()
TO_NUMBER()는 문자형으로 표현된 숫자 값을 숫자 형식으로 변환하는 함수로서 문자형 숫자 값은 음, 양 부호인 +, -와 소수점(.)을 포함할 수 있다.
⼧실습예제: Student 테이블에서 입학년도(entrance)에 2년을 더한 값이 2000년이 되지 않는 학생의 성명과 입학년도를 출력하는 SQL 구문을 작성하시오.
SQL> SELECT hname, entrance FROM student WHERE TO_NUMBER(entrance)+2 < 2000;
HNAME ENTR
---------- ----
AAA 1995
7.2 집단 함수
우리는 그 동안 실습을 통해서 테이블에 저장된 각 행의 내용에 대해서만 검색을 하였다. 하지만 우리는 각 행에 대한 정보보다는 전체 행 또는 일부 조건이 일치하는 행들에 대해서만 합계라든가, 평균 값 등과 같은 통계정보도 손쉽게 얻기를 바라게 된다. 이와 같이 어느 조건에 맞는 그룹이나 전체에 대한 통계정보를 얻을 수 있는 함수가 집단함수(group function)이다.
집단함수는 단일행 함수와 달리 하나이상의 행 그룹당 한 개의 결과를 주기 위해 그룹의 행들에 대해 연산을 하는 함수이다. 집단함수의 연산 단위가 되는 행 그룹은 테이블의 전체 행 또는 하나 행 이상의 일부 행이 될 수 있다.
* 함수 기능
AVG([DISTINCT|ALL] column) : 평균값을 구한다.
COUNT([DISTINCT|ALL], [expr|*]) : 행의 수를 반환한다.
MAX([DISTINCT|ALL] expr) : 최대값을 구한다.
MIN([DISTINCT|ALL] expr) : 최소값을 구한다.
STDDEV([DISTINCT|ALL] column) : 표준편차를 구한다.
SUM([DISTINCT|ALL] column) : 합계를 구한다.
VARIANCE([DISTINCT|ALL] column) : 분산을 구한다.
표 7-8에 명시한 집단함수들의 argument 중에 DISTINCT는 집단함수로 연산할 행들 중에 중복되지 않는 값만 연산하도록 하는 선택적인 입력 값으로서 명시하지 않으면 ALL로 처리된다. expr은 숫자형을 비롯하여 문자, 날짜형 칼럼이나 표현식이 올 수 있다는 의미이다.
집단함수 연산시 column이나 expr로 명시된 값이 NULL이면 그룹 연산에서 제외되고 COUNT(*)와 같이 아스타리스크(*)를 사용하면 NULL 값을 갖는 행도 포함하여 연산한다.
집단함수를 사용하기 위한 SELECT 구문 작성 방법은 다음과 같다.
* 구문법
SELECT [group_column | group_function], ...
FROM table_name
[WHERE query_condition]
[GROUP BY group_by_expression]
[HAVING group_condition]
[ORDER BY [group_column | group_function] [ASC | DESC], ... ];
group_column
집단함수의 연산 결과와 함께 보고자 하는 칼럼명을 입력한다. GROUP BY 절에 명시한 칼럼에 한하여 볼 수 있다.
group_function
집단함수를 입력한다.
query_condition
행들을 그룹으로 나누기 전에 미리 조건에 맞지 않는 행들을 제외시킨다.
group_by_expression
집단함수가 연산할 동일 그룹 행에 대한 조건을 칼럼명 나열로 명시한다.
group_column, group_column, ...
명시한 칼럼 값이 모두 같을 때 같은 그룹으로 처리한다.
group_condition
집단함수로 연산한 결과 중에 반환되는 제한 조건을 명시한다.
ORDER BY
검색된 결과 행들이 보여지는 순서를 결정한다.
GROUP BY 절에 명시한 칼럼에 한하여 입력할 수 있다.
⼆ GROUP BY절 없이 집단함수 사용
집단함수를 사용할 때 GROUP BY절을 명시하지 않으면 집단함수는 WHERE절 조건에 일치하는 모든 행들에 대해 연산을 수행하므로 단지 한 행의 결과만을 반환하게 된다.
⼧실습예제: St_schedule 테이블에서 전체 학생에 대한 취득점수(score)의 최대값, 최소값, 행의 수, 평균, 표준편차를 구하는 SQL 구문을 작성하시오.
SQL> SELECT MAX(score), MIN(score), COUNT(score), AVG(score), STDDEV(score)
FROM st_schedule;
MAX(SCORE) MIN(SCORE) COUNT(SCORE) AVG(SCORE) STDDEV(SCORE)
---------- ---------- ------------ ---------- -------------
100 67 74 86 6.6928709
⼧실습예제: Student 테이블에서 전체 학생의 수와 성명이 가장 빠른 학생과 가장 늦은 학생의 이름을 출력하고, 생년월일이 가장 빠른 학생과 가장 늦은 학생의 생년월일을 출력하는 SQL 구문을 작성하시오.
SQL> SELECT COUNT(*), MIN(hname), MAX(hname), MIN(birthday), MAX(birthday)
FROM student;
COUNT(*) MIN(HNAME) MAX(HNAME) MIN(BIRTHDAY) MAX(BIRTHDAY)
--------- ---------- ---------- ---------------- ----------------
18 AAA BBB 76/11/21 81/07/06
⼆ GROUP BY절이 포함된 집단함수 사용
집단함수로 연산시 적용될 그룹 단위를 구분하기 위해서는 GROUP BY절에 같은 그룹이 될 조건을 칼럼으로 나열하여 명시하여야 한다. GROUP BY절에 명시된 칼럼의 값이 모두 같은 행들은 같은 그룹으로 간주되어 집단함수의 그룹 연산 단위가 되며, 집단함수는 그룹별로 한 행의 결과를 반환한다.
SELECT절에는 집단함수와 함께 칼럼명을 명시할 수 있는데, 이 칼럼은 반드시 GROUP BY절에 명시된 칼럼이어야 한다.
반환되는 행들은 ORDER BY절을 명시하지 않으면 GROUP BY절에 명시한 칼럼 순으로 정렬되어 출력된다.
⼧실습예제: St_schedule 테이블에서 각 학생별 수강과목 수와 총점, 평균, 표준편차를 학번과 함께 출력하는 SQL 구문을 작성하시오.
SQL> SELECT stid, COUNT(*), SUM(score), TO_CHAR(AVG(score), '999.0'), TO_CHAR(TRUNC(STDDEV(score), 2), '99.00') FROM st_schedule GROUP BY stid;
STID COUNT(*) SUM(SCORE) TO_CHA TO_CHA
--------- --------- ---------- ------ ------
960001 3 284 94.7 5.03
⼆ HAVING절이 포함된 집단함수 사용
우리는 집단함수로 연산된 결과들 중에 어떤 조건에 일치하는 결과만을 출력하고 싶을 때가 있다. 이 때 사용하는 절이 HAVING절로서 집단함수의 연산 결과에 대한 제한 조건을 명시하게 되므로 GROUP BY절과 함께 사용해야 의미가 있다. HAVING절이 WHERE절과 다른 점은 WHERE절이 집단함수로 연산하기 전에 행들을 제한하는 조건인 반면, HAVING는 집단함수로 연산한 결과들 중에 제한하는 조건이라는 점이다. 그러므로 HAVING절에는 집단함수를 사용하여 조건을 명시하게 된다.
⼧실습예제: St_schedule 테이블에서 평균점수가 90점 이상인 학생에 대한 취득점수(score)의 최대값, 최소값, 행의 수, 평균, 표준편차를 학번과 함께 출력하는 SQL 구문을 작성하시오.
SQL> SELECT stid, COUNT(*), SUM(score), TO_CHAR(AVG(score), '999.0'), TO_CHAR(TRUNC(STDDEV(score), 2), '99.00') FROM st_schedule WHERE AVG(score) >= 90 GROUP BY stid;
SQL> SELECT stid, COUNT(*), SUM(score),
2 TO_CHAR(AVG(score), '999.0'),
3 TO_CHAR(TRUNC(STDDEV(score), 2), '99.00')
4 FROM st_schedule
5 WHERE AVG(score) >= 90
6 GROUP BY stid;
WHERE AVG(score) >= 90
*
줄 5에 오류:
ORA-00934: group function is not allowed here
위 예제에서 집단함수를 WHERE절에 사용하여 착오가 발생하였다. 올바른 실행을 위해서는 다음과 같이 WHERE절에 있는 조건을 HAVING절에 사용하여야 한다.
SQL> SELECT stid, COUNT(*), SUM(score), TO_CHAR(AVG(score), '999.0'), TO_CHAR(TRUNC(STDDEV(score), 2), '99.00') FROM st_schedule GROUP BY stid HAVING AVG(score) >= 90;
STID COUNT(*) SUM(SCORE) TO_CHA TO_CHA
--------- --------- ---------- ------ ------
960001 3 284 94.7 5.03
집단함수를 사용하는 SELECT 구문에서 SELECT절에 반드시 집단함수가 포함될 필요는 없다. 다시 말해 집단함수를 HAVING절에만 사용하고 SELECT절에는 그룹 칼럼명만을 사용해도 관계없다는 말이다. SELECT절에 그룹 칼럼명만을 명시하는 예는 다음과 같다.
SQL> SELECT stid FROM st_schedule GROUP BY stid HAVING AVG(score) >= 90;
STID
---------
960001
댓글 없음:
댓글 쓰기