[Oracle] 단일 행 함수

단일 행 함수

• 각 행렬별로 연산된 결과 값을 얻을 수 있으며 중첩이 가능하다.
• 오류를 줄이고 개발 시간을 단축 할 수 있다.
• 인수로는 컬럼 명, 수식, 변수, 상수 등을 사용할 수 있다. (함수명(인수1,2...)
• 값이 필요한 거의 모든 구문에 사용이 가능하다.(SELECT, WHERE, HAVING) 등

문자 함수

함수명	기능	함수명	기능
LOWER	문자열을 소문자로 변환 LOWER(문자열)	INSTR	문자열 내 문자의 위치 반환 INSTR(문자열, 검색할문자,시작위치,횟수)
	LOWER('APPLE') -> apple		INSTR('oracle','a')-> 3
UPPER	문자열을 대문자로 변환 LOWER(문자열)	TRIM	접두(접미) 글자를 잘라낸다 TRIM([leding | trailing | both] 제외문자            FROM 문자열)
	LOWER('pear')-> PEAR		TRIM('o' FROM 'oracle') ->racle
INITCAP	첫 번째 문자를 대문자로, 나머지는 소문자 INITCAP(문자열)	LENGTH LENGTHB	문자열의 길이 반환한다. LENGTH(문자열) 문자열의 BYTE를 반환한다. LENGTHB(문자열)
	INITCAP( 'apple') -> Apple		LENGTH('디비') -> 2 / LENGTHB('디비') -> 4
SUBSTR	문자열 내 지정된 문자열 검색 SUBSTR(문자열,start,count)	LPAD RPAD	지정된 문자열의 길이만큼 빈부분에 문자를 채움 L(R)PAD(문자열,출력폭, 채울문자)
	SUBSTR('pear',1,2) -> pe		LPAD('30',3,'*') -> *30 / RPAD('30',3,'*') -> 30*
REPLACE	문자열 단위로 치환된 값 반환 REPLACE(문자열,검색문자열,치환문자열)	TRANSLATE	문자 단위로 치환된 값 반환 TRANSLATE(문자열,검색문자,치환문자)
	REPLACE('oracle','or','##') -> ##acle		TRANSLATE('oracle','a','#')-> or#cle

 - 자바와 달리 인덱스가 1부터 시작함 (SUBSTR, INSTR)

SELECT INSTR('database','a'),   
          INSTR('database','a',3),
          INSTR('database','a',1,3)
FROM dual; -- 임시테이블

결과
INSTR('DATABASE','A') INSTR('DATABASE','A',3) INSTR('DATABASE','A',1,3)
--------------------- ----------------------- -------------------------
                    2                       4                         6
                    
SELECT ename, SUBSTR(ename,2),          
                    SUBSTR(ename,-2),
                    SUBSTR(ename,1,2),
                    SUBSTR(ename,-2,2)
FROM emp;

결과
ENAME     SUBSTR(ENAME,2)    SUBSTR(ENAME,-2)  SUBSTR(ENAME,1,2) SUBSTR(ENAME,-2,2)
-----     ----------------   ----------------  ----------------  ----------------
안영희         영희               영희               안영                영희

SELECT TRANSLATE('World of Warcraft', 'Wo', '‐‐') Translate,
       REPLACE('World of Warcraft',  'Wo', '‐‐') Replace  
FROM dual;

결과
TRANSLATE                          REPLACE
---------------------------------- ----------------------------------
??rld ?f ?arcraft                  ??rld of Warcraft

‐ TRANSLATE는 각각의 글자를 지정된 문자로 변환

1. '강' 씨 성을 가진 학생의 이름을 검색한다.
SELECT sno, SUBSTR(sname,2);
FROM student
WHERE SUBSTR(sname,1,1)='강';

2. 교수의 지위를 한글자로 검색한다.(ex. 조교수 → 조)
SELECT pno, pname, SUBSTR(orders,1,1)
FROM professor;

3. 모든 일반 과목 명칭을 기초 과목으로 변경해서 검색한다.
(ex. 일반화학 → 기초화학)
SELECT cno, REPLACE(cname,'일반','기초')
FROM course
WHERE cname LIKE '일반%';

4. 만일 입력 실수로 student 테이블의 sname 컬럼에 데이터가 입력될 때 문자열 마지막에
공백이 추가 되었다면 검색할 때 이를 제외하고 검색하는 SELECT문을 작성한다.
SELECT sno, TRIM(' ' FROM sname)
FROM student;

5. 직원의 년봉을 10자리로 검색한다. 단 공백은 임의의 채움 문자로 채워 넣는다.
SELECT eno, ename, RPAD((sal*12)+NVL(comm,0),10,'0') 연봉
FROM emp;


6. 학생의 이름을 검색한다. 단 성이 '심'인 학생은 성을 '사마' 로 바꾸어 검색한다.
SELECT sno, sname
FROM student;
MINUS
SELECT sno, sname
FROM studnet;
WHERE sname LIKE '심%'
UNION
SELECT sno, REPLACE(sname,'심','사마')
FROM student;
WHERE sname LIKE '심%';

숫자 함수

함수명	기능	함수명	기능
ROUND	반올림해서 소수 이하 n 자리까지 출력 ROUND(m, n)	CEIL	m 보다 큰 가장 작은 정수 CEIL(m)
	ROUND(123.456,1) -> 123.5		CEIL(2.34) -> 3
TRUNC	소수 이하 n 자리 미만 절삭 TRUNC(m, n)	FLOOR	m 보다 작은 가장 큰 정수 FLOOR(m)
	TRUNC( 123.456,1) -> 123.4		FLOOR(2.34) -> 2
MOD	m을 n으로 나눈 나머지 MOD(m, n)	SQRT	제곱근을 검색 SQRT(m)
	MOD(10,4) -> 2		SQRT(9) -> 3
POWER	m의 n승 POWER(m, n)	SIGN	음수 : -1, 양수 : 1, 0 이면 0을 검색 SIGN(m)
	POWER(2, 4) -> 16		SIGN(-3) -> -1

날짜 연산

날짜 + m : m일 이후 날짜 (결과 = 날짜)
날짜 - m  : m일 이전 날짜 (결과 = 날짜)
날짜 - 날짜 : 두 날짜 간에 기간 (결과 = 숫자)

날짜 포맷

SQL> SELECT sysdate FROM dual;
SQL> ALTER SESSION SET nls_date_format = 'YYYY/MM/DD';
SQL> SELECT sysdate FROM dual;
SQL> ALTER SESSION SET nls_date_format = 'YYYY/MM/DD:HH24:MI:SS';
SQL> SELECT sysdate FROM dual

•  Windows 기본 날짜 출력 형식 : 'YY/MM/DD'
• UNIX 기본 날짜 출력 형식 : 'DD-MON-YY'
• nls_date_format은 세션 레벨에서 설정한다.
•  glogin.sql 파일을 이용한다.

 ‐ 응용 프로그램에서 날짜 관련 검색은 반드시 TO_DATE나 TO_CHAR를 이용한다

// 오늘 날짜, 입사 일자, 입사일로부터 오늘까지 기간, 입사일 이후 100째 되는날 등을
검색하고 날짜 연산의 결과를 살펴보자.
SELECT eno, ename, sysdate 오늘, hdate 입사일,
       TRUNC(sysdate)‐TRUNC(hdate)+1 근무일, hdate+99 "100일"
FROM emp;
- TRUNC는 시분초를 버린다.
‐ 날짜 연산은 ROUND하는 경우가 거의 없고 TRUNC하는 것이 대부분이다.

//날짜를 다양한 단위에서 반올림하거나 절삭해 본다
ALTER SESSION SESSION SET nls_date_format = 'YYYY/MM/DD:HH24:MI:SS';
SELECT sysdate,
       ROUND(sysdate,'YYYY') 년,
       ROUND(sysdate,'MM') 월,
       ROUND(sysdate,'DD') 일
FROM dual;

SELECT sysdate,
       TRUNC(sysdate,'YY') 년,
       TRUNC(sysdate,'MM') 월,
       TRUNC(sysdate,'DD') 일
FROM dual;

// 문시현이 오늘까지 일한 일수를 검색한다. 
SELECT ename, TRUNC(sysdate)‐TRUNC(hdate)+1 DAY
FROM emp
WHERE ename='문시현';

// 20번 부서 직원들이 현재까지 근무한 개월 수를 검색한다.
SELECT eno, ename, TRUNC(MONTHS_BETWEEN(sysdate,hdate)) 근무_개월
FROM emp
WHERE dno = '20';

// 20번 부서원들이 입사한 이후 첫 번째 일요일을 검색한다.
SELECT eno, ename, hdate, NEXT_DAY(hdate,'일요일') Sunday
FROM emp
 WHERE dno = '20';

// 20번 부서원들이 입사 100일째 되는 날과 10년째 되는 날을 검색한다.
SELECT eno, ename, hdate 입사일,
          hdate+99 "100일", ADD_MONTHS(hdate,120) "10년"
FROM emp
WHERE dno = '20';

// 20번 부서원들의 입사한 달의 마지막 날짜와 입사한 달에 근무 일수를 검색한다.
SELECT eno, ename, hdate, LAST_DAY(hdate) 마지막날,
         LAST_DAY(TRUNC(hdate))‐TRUNC(hdate)+1 "마지막달 근무일수"
FROM emp
WHERE dno = '20';

 

날짜 연산 예제

1. 교수들이 부임한 달에 근무한 일수는 몇 일인지 검색한다. 
SELECT pno, pname, TRUNC(LAST_DAY(hiredate)-hiredate)+1 "부임 달 근무일수"
FROM professor;

2. 교수들의 오늘까지 근무한 주가 몇 주인지 검색한다.
SELECT pno,pname,TRUNC(TRUNC(sysdate-hiredate)/7,0)||'주' "총 근무 주"
FROM professor;

3. 1991년에서 1995년 사이에 부임한 교수를 검색한다. 
SELECT pno, pname, hiredate
FROM professor
WHERE TRUNC(hiredate,'YYYY') BETWEEN '1991/01/01'AND '1995/01/01';

4. 사원들이 일한 날짜에 대해서만 급여를 받는다면 급여가 현재와 동일하다는 조건에서 입
사한 달에 급여는 얼마나 지급되었을지 검색한다.
SELECT eno, ename, hdate,TRUNC(LAST_DAY(hdate)-hdate)+1 "입사달 근무일수",TRUNC(sal/12,1) 월급,
       TRUNC(((sal/12)/(TRUNC(LAST_DAY(hdate))-TRUNC(hdate,'MM')+1))*(TRUNC(LAST_DAY(hdate)-hdate)+1),1) "입사 달 월급"     
FROM emp;

변환 함수

	형식	의미		형식	의미
년	YYYY	네 자리 년도 : 2021	시	HH24	00~24, 24시간 표현
	YY	두 자리 년도 : 99-2099
	RR	두 자리 년도 : 99-1999		HH	01 ~ 12, 12시간 표현
월	MM	숫자 : 01,02
	MONTH	문자 : MARCH, 3월	분	MI	##, 분
	MON	약자 : MAR, 3월	초	SS	##, 초
일	DD	숫자 : 01,02
	DAY	요일 : SUNDAY, 일요일		SSSSS	하루를 초로 환산 (0 ~ 86399)
	DY	약자 요일 : SUN, 일	오전 오후	AM/ PM	오전, 오후 형식으로 의미 없음

숫자 형식

형식	의미
9	숫자의 출력 폭을 지정
0	선생 0 을 표기
$	화폐 표기(달러)
L	화폐 표기(각 국가의 언어 셋 설정에 따라 바뀜)
,	쉼표 위치 지정(보통 세자리마다 지정)
.	소수점 위치를 지정
MI	음수의 - 기호 표시를 오른쪽에
EEEE	실수 표현기법

변환 함수

함수명	기능
TO_CHAR	날짜나 숫자를 지정된 형식의 문자로 변환 - 출력 형식 지정 TO_CHAR(날짜, 형식), TO_CHAR(숫자,형식)
TO_DATE	문자를 지정된 형식의 날짜로 해석한다. TO_DATE(문자, 형식)
TO_NUMBER	문자를 숫자로 해석한다. 대부분의 경우 오라클의 자동 형변환에 의해 SQL 구문 작성시에는 사용빈도가 매우 낮다

- TO_CHAR : 데이터를 출력하거나 전달할 때 서식에 맞추어 가공하는 기능을 제공한다.
                      이를 통해 응용 프로그램의 부담을 줄이고 가독성과 생산성을 높일 수 있다.

- TO_DATE : 사용자가 입력하거나 전달 받은 문자열을 형식에 따라 날짜로 해석하는 함수, 날짜 데이터에 반드시 사용

//숫자를 다양한 형식으로 검색한다
SELECT TO_CHAR(12345.678, '999,999.99999') num FROM dual; => 12,345.6780
SELECT TO_CHAR(12345.678, '099,999.999') num FROM dual; => 012,3455.678
SELECT TO_CHAR(12345.678, '99,999.9') num FROM dual; => 12,345,7
SELECT TO_CHAR(12345.678, '9,999.9') num FROM dual; => ########
SELECT TO_CHAR(1234, '$999,999') num FROM dual; =>  $1,234
SELECT TO_CHAR(1234, 'L999,999') num FROM dual; =>  ￦1,234
SELECT TO_CHAR(123456789, '9.999EEEE') num FROM dual; => 1.235E+08
SELECT TO_CHAR('-1234', '999,999MI') num FROM dual; =>  1,234-

// 1992년 이전에 입사한 사원의 정보를 검색한다.
SELECT eno 사번, ename 이름, hdate 입사일
FROM emp
WHERE hdate < TO_DATE('19920101','YYYYMMDD');

SELECT eno 사번, ename 이름, hdate 입사일
FROM emp
WHERE hdate <= TO_DATE('19911231:235959','YYYYMMDD:HH24MISS');

 

변환 함수 예제

1. 학생의 평균 평점을 다음 형식에 따라 소수점 이하 두 자리까지 검색한다.
‐ 'XXX 학생의 평균 평점은 x.xx입니다.'
SELECT sno, sname||' 학생의 평균 평점은'||TO_CHAR(ROUND(avr,2),90.09)||'입니다.' 평점
FROM student;

2. 교수의 부임 일을 다음 형식으로 검색한다.
‐ 'XXX 교수의 부임일은 YYYY년 MM월 DD일입니다.'
SELECT pno, pname||'교수의 부임일은 '||TO_CHAR(hiredate,'YYYY"년 " MM"월 " DD"일 입니다."') 입사일
FROM professor;

3. 교수 중에 3월에 부임한 교수의 명단을 검색한다.
SELECT pno, pname, hiredate
FROM professor
WHERE TO_CHAR(hiredate,'MM') = 03;

4. 화학과 1학년 학생들의 4.5 환산 평점을 다음 형식에 따라 소수점 이하 두 자리까지 검
색한다. 
‐ 'XXX 학생의 4.5 환산 평점은 x.xx입니다.'
SELECT sno, sname||' 학생의 4.5 환산 평점은'||TO_CHAR(ROUND(avr*1.125,2),90.09)||'입니다.' 평점
FROM student;