12. 단일 행 함수 : 문자함수
입력 값 하나 하나에 대해서 각각 결과를 제공하는 단일 행 함수는 응용의 개발을 줄이고 좀 더 효율적인 SQL문을 만드는 데 조움이 됨. 비록 오라클이 제공하는 단일 행 함수가 모든 응용을 대신 할 수 없지만, 많은 경우 단일 행 함수를 기억하지 못한다면 개발자는 매우 길고 복잡한 응용을 직접 개발 해야 함. 단일 행 함수의 이름이나 사용 방법은 다른 언어(C, BASIC, PHP, JAVA.. 등)에서 제공하는 함수들과 비슷함으로 이들 언어를 사용해본 경험이 있다면 쉽게 오라클의 단일 행 함수를 사용 할 수 있음
- 단일 행 함수의 종류
> 단일 행 함수는 입력 값의 형태나 출력 값의 형태에 따라 구별되며 그 구분은 아래와 같음
- 문자 함수
- 숫자 함수
- 날짜 함수
- 변환 함수
- 일반 함수
- 단일 행 함수의 이용
단일 행 함수를 이용하면 검색된 데이터에 대해서 각 행별로 연산된 결과 값을 얻을 수 있음.
- 함수의 결과 값은 입력 마다 하나씩 리턴
- 중첩이 가능
- 응용에서 처리 할 여러 조작을 RDBMS가 대신 해 줌으로 응용 오류를 줄이고 개발 시간을 단축
- 인수로는 컬럼 명, 수식, 변수, 상수 등을 사용 가능]
예) 함수명(인수1, 인수2, ...)
- 단일 행 함수는 SELECT 절이나 WHERE절 또는 HAVING 절 등 값이 필요한 거의 모든 구문에 사용 가능
> 문자 함수
문자형 단일 행 함수는 문자를 매개 인자로 하는 함수를 의미
대소문자 변환 함수
SQL>SELECT loc ERP_부서_지역
2 FROM dept
3 WHERE LOWER(dname)='erp';
dname(부서명) 컬럼에 뚜렷한 입력 원칙이 정해지지 않는다면 입력된 값에는 대소문자가 섞여 있을 가능성이 높음. 예약어가 아닌 경우 모든 문자에 대해서 대소문자가 구별됨으로 ERP라는 문자열을 찾기 위해 WHERE 절은 'WHERE dname = 'ERP' OR dname = 'erp' 와 같이 써야 함. 그러나 이렇게 쓸 경우 Erp와 같은 문자는 검색 불가, 이렇게 입력된 문자가 대, 소문자가 섞여있는 경우 문자열을 검색하기 위해 컬럼의 값을 모두 대문자나 소문자로 바꿔주는 단일항 함수를 사용하는 것 이외는 다른 방법이 없음. 이와 같이 WHERE 절에 기술된 컬럼에 단일 행 함수를 사용하면 전체적인 쿼리문의 수행 성능이 저하됨으로 가능한 입력 작업에 적당한 원칙을 적용해서 대소문자를 구별하고 지정된 문자 입력을 강제해서 단일 행 함수가 불필요하도록 하는 것이 좋음
문자 연산 함수
-예제3. 부서의 명과 위치를 하나의 컬럼으로 검색
SQL>SELECT CONCAT(dname, ' ' || loc) CONCAT
2 FROM dept;
문자 치환 함수
<문제>
1. 직원의 연봉을 10자리로 검색, 단 공백은 임의의 채움 문자로 채워 넣음
SQL>SELECT RPAD(sal*12+nvl(comm,0), 10, '*')
2 FROM emp;
2. 학생의 이름을 검색, 단 성이 '맹'인 학생은 성을 '사마'로 바꾸어 검색
(맹이 없어 공인 학생을 사마로 수정)
SQL>SELECT REPLACE(sname, '공', '사마') replace
2 FROM student;
13. 단일 행 함수 : 숫자, 날짜 함수
> 숫자 함수
숫자 함수는 숫자를 인자로 사용하고 결과 값도 숫자를 반환하는 함수
> 날짜 함수와 날짜 연산의 예
날짜 데이터 연산
- 날짜 데이터는 문자처럼 표기 하지만 숫자처럼 계산되는 데이터로 오라클은 6byte 크기로 날짜를 저장. 날짜를 출력할 때는 출력 터미널의 환경에 따라 출력 양식이 결정 되지만 저장 내용은 모두 동일한 '년/월/일:시:분:초'('YYYY/MM/DD:HH24:MI:SS') 형식을 갖음 이에 따라 만일 출력된 날짜 형식이 '1999/12/23' 인 경우에도 눈에 보이지 않지만 시 분 초에 해당하는 데이터가 존재. 업무를 위해 날짜 데이터를 연산 할 때는 이렇게 보이지 않는 부분을 고려 해야 함
-예제 4. 오늘 날짜, 입사 일자, 입사일로부터 오늘까지 기간, 입사일 이후 100일째 되는 날 등을 검색하고 날짜 연산의 결과를 확인
SQL>SELECT sysdate, hdate, TRUNC(sysdate-hdate+1), hdate+99
2 FROM emp;
날짜에 관련된 연산을 정리하면 다음과 같음
날짜 함수
-예제5. 숫자와 날짜를 반올림하거나 잘라내고 출력
SQL>SELECT sysdate,
2 ROUND(sysdate, 'YY') 년,
3 ROUND(sysdate, 'MM') 월,
4 ROUND(sysdate, 'DD') 일
5 FROM dual;
SQL>SELECT sysdate,
2 TRUNC(sysdate, 'YY') 년,
3 TRUNC(sysdate, 'MM') 월,
4 TRUNC(sysdate, 'DD') 일
5 FROM dual;
TRUNC 함수는 버림을 하며 일 단위 일 때는 ROUND 함수와 마찬가지로 절삭함
<문제>
1. 사원들의 급여가 현재 급여와 동일하다면 입사한 달에 급여는 얼마나 지급되었을 지 검색
SQL>SELECT eno, ename, hdate,
2 (LAST_DAY(TRUNC(hdate)) - TRUNC(hdate,'MM')) 근무일 ,
3 round(sal/(LAST_DAY(TRUNC(hdate)) - TRUNC(hdate,'MM'))*
4 (LAST_DAY(TRUNC(hdate)) - TRUNC(hdate))) 급여
5 FROM emp ;
2. 사원들의 오늘까지 근무 기간이 몇 년 몇 개월 몇 일인지 검색
SQL>SELECT eno, ename,
2 TRUNC(TRUNC(MONTHS_BETWEEN(sysdate,hdate))/12) "현재까지 근무한 년 수",
3 TRUNC(MOD(TRUNC(MONTHS_BETWEEN(sysdate,hdate)),12)) "개월 수",
4 TRUNC(sysdate - ADD_MONTHS(hdate,
5 TRUNC(MONTHS_BETWEEN(sysdate,hdate))))"일 수"
6 from emp;
14. 단일 행 함수 : 변환 함수
변환 함수는 앞에서 배운 다른 함수들에 비해서 응용 프로그램으로 대처하기 매우 어려운 함수들임. 여러분이 문자 함수나 숫자 함수를 잊었다면 간단히 응용 프로그램으로 대체해도 문제가 되지 않지만 변환 함수들은 그렇게 처리하기 어렵다는 것임. 오라클은 상식적인 수준의 형 변환을 자동으로 구현하고 있음. 예를 들어 문자 '1'은 연산자를 사용 할 경우 숫자 1로 자동으로 치환. 그러나 특별한 출력 형태를 유지하거나 사용자의 입력을 DBMS가 해석해야 되는 경우에는 반드시 변환 함수를 사용해야 함
단일 행 함수에서 사용하는 날짜 출력 형식
단일 행 함수에서 사용하는 숫자 출력 형식
변환 함수
TO_CHAR나 TO_DATE 함수의 기능은 데이터를 문자나 날짜로 바꾸는 것이지만 이들의 목적을 단지 데이터의 형을 변환하는 것은 아님. 이들은 특정한 목적을 위해 쓰임. TO_CHAR의 가장 일반적인 목적은 데이터의 출력 형식을 정의하는 것. 검색된 데이터의 출력 형식이 미리 지정한 규칙에 따르지 않는 경우 프로그램 개발 과정에서 심각한 오류를 만날 수 있음. 이런 문제를 해결하기 위해 대부분의 응용 프로그램에 사용되는 쿼리문은 출력 형식을 지정하는 데 이 때 TO_CHAR를 사용. 만일 TO_CHAR를 이용하지 않는 경우에 검색된 정보를 응용 프로그램이 다시 가공해야 함으로 프로그램의 질이 떨어 질 수 있음. 그리고 TO_DATE는 DBMS에게 데이터를 전달할 때 정확히 날짜를 해석하도록 추가 정보를 제공하는 역할을 담당. 예를 들어 '09/08/07'라는 데이터가 2009년 8월 7일인지 2007년 8월 9일 인지 데이터만을 보고는 명확히 해석하기 어려움. 이런 경우 오라클이 이를 명확히 해석하도록 하기 위해서 TO_DATE를 이용해서 정보를 표현
-예제1. 현재 날짜를 다양한 형식으로 출력
SQL>SELECT TO_CHAR(sysdate, 'YYYY/MM/DD'),
2 TO_CHAR(sysdate, 'YYYY/MM/DD:HH24:MI:SS'),
3 TO_CHAR(sysdate, 'YY/MM/DD:HH:MI:SS:AM')
4 FROM dual;
변환 함수인 TO_CHAR 용도는 예제에 보는 바와 같이 출력되는 데이터의 형식을 지정하는 용도로 이용됨. 최종 사용자가 출력 포맷을 고정하고 싶다면 반드시 TO_CHAR 함수를 이용해야 함, 오라클에 연동된 프로그램을 작성하는 경우 이렇게 지정된 출력 양식은 출력된 행을 변수로 패치할 때 매우 유용함
<문제>
1. 화학과 1학년 학생들의 4.5 환산 평점을 다음 형식에 따라 소수점 이하 두 자리 까지 검색
SQL>SELECT major || '학과의 ' || syear || '학년의 ' || sname ||
2 '학생의 평균 학점 ' || TO_CHAR(avr*(1.125), '99.99') 메롱
3 FROM student
4 WHERE major in ('화학') and syear IN ('1');
15. 단일 행 함수 : CASE 와 DECODE
일반 프로그래밍언어는 조건문과 반복문을 근간으로 응용 프로그램을 만드는 것이지만 SQL은 이와 달리 단문으로 구성되어 한 번의 독립된 명령으로 작업을 처리하도록 되어 있음. 그러나 SQL 사용자들로부터 SQL을 이용한 프로그래밍에 대한 요구가 높아지면서 각 벤더들은 각기 PL/SQL 같은 확장된 규격을 통해 프로그래밍언어에서 제공하는 여러 기능을 제공하고 있음. 그리고 이와는 별개로 SQL 자체에 일부 프로그래밍 기능을 부여하도록 하고 있는데 그것이 CASE와 DECODE다 보통 조건문으로 구별되는 IF-THEN-ELSE 기능은 기본 SQL에는 제공되지 않지만 CASE와 DECODE를 통해 구현 될 수 있음.
> IF, CASE 표현식, DECODE 함수
CASE는 대부분의 RDBMS가 지원하지만 DECODE는 오라클만 지원. DECODE 함수가 좀 더 간단한 표기가 되지만 부등호의 경우 DECODE는 직접적인 표현할 수 없음으로 여러가지 부가적인 표현이 필요. 또한 응용 개발 시에 기반 RDBMS가 반드시 오라클이라는 확신이 없는 경우 DECODE보다 CASE를 쓰는 것이 좋음. DECODE의 경우 IF절과의 비교를 위해 조건이라 기술했으나 실제는 값을 기술하는 것으로 값이 일치하는 결과를 반환하는 기능을 수행. 이와 달리 CASE는 다양한 형태의 조건을 직접 기술하는 것이 가능해서 좀 더 다양한 형태의 조건 기술이 가능
> CASE
CASE 컬럼 WHEN 조건1 THEN 결과1
[WHEN 조건2 THEN 결과2
WHEN 조건3 THEN 결과3
.............................
ELSE 결과]
END
* 컬럼의 값이 WHEN절의 조건의 일치하는 결과를 검색
* 일치하는 조건이 없는 경우 ELSE 절의 결과를 검색. 그러나 ELSE 절이 없는 경우 널을 반환
* 조건은 일치하는 값 뿐 아니라 여러 가지 조건식을 사용 할 수 있음
* CASE는 표현식
>DECODE
DECODE(컬럼, 값1, 결과1, [값2, 결과2, ....] [기본 값])
* 컬럼의 값이 각 '값n'에 일치하는 경우 뒤의 결과를 반환함
* 일치하는 값이 없을 경우 기본 값을 반환하지만 기본 값이 없을 경우 널을 반환
* DECODE는 함수
-예제1. 각 업무별로 인상된 급여를 검색
* 모델링, 분석: 20% 인상
* 이외 부서 : 10% 인상
SQL>SELECT job, eno, ename, sal,
2 CASE job WHEN '모델링' THEN sal*1.2
3 WHEN '분석' THEN sal*1.2
4 ELSE sal*1.1
5 END 인상급여
6 FROM emp;
SQL>SELECT job, eno, ename, sal,
2 DECODE(job, '모델링', sal*1.2,
3 '분석' , sal*1.2 ,
4 sal*1.1 )인상급여
5 FROM emp;
각 조건에 따라 서로 다른 결과를 검색하는 경우 상당이 장황한 응용 프로그램을 이용해야 했으나, 이와 같이 CASE 표현식을 이용하거나 DECODE 함수를 이용한다면 매우 간단히 검색할 수 있음. CASE나 DECODE를 이용하는 경우 가독성을 위해 위와 같이 들여쓰기를 적극 이용.
<문제>
1.현재 급여를 이용 인상될 급여를 검색하는 SQL문을 작성
SELECT job, eno, ename, sal, CASE
WHEN sal <= 1000 THEN sal*1.15
WHEN sal*1.1 between 1100 and 1149 THEN 1150
WHEN sal between 1001 and 2000 THEN sal*1.1
WHEN sal*1.05 between 2100 and 2199 THEN 2200
WHEN sal between 2001 and 3000 THEN sal*1.05
WHEN sal*1.02 between 3060 and 3149 THEN 3150
WHEN sal between 3001 and 4000 THEN sal*1.02
WHEN sal between 4001 and 4079 THEN 4080
ELSE sal
END 인상급여
FROM emp;
댓글 없음:
댓글 쓰기