[DB] Dual Table, 수학함수, 문자열함수, 변환함수, 조건함수, 집계함수

#HappyKeysDay 

 

*Dual Table

- 사용자가 입력하는 값으로 컬럼을 생성하여 단순 조회를 할 수 있도록 만든 가상테이블.

 

*함수(Function) [참고] 오라클 함수 리스트 및 설명 문서(10g기준, 2006년 백승민) 

 Oracle함수.zip

- 자주 사용될 코드를 미리 정의해두고 필요한 곳에서 호출하여 사용하기 위하여 만들어 놓은 것.

- 결과가 하나가 나오는 단일행 함수와 여러개가 나오는 여러개가 나오는 복수행 함수가 존재한다.

- 조회하는 컬럼, 조건절에서 사용할 수 있다.

- 수학함수, 문자열함수, 변환함수, 조건함수, 집계함수 둥이 존재한다,

- 사용법

select 컬럼명, 함수명(컬럼명,,,),,,

from

where 컬럼명, 함수명(컬럼명,,)

- 함수

 

*수학 함수

- abs, sin, cos, tan, round, ceil, floor, trunc

ex)

+ abs(값 or 컬럼명) : 절대값

select abs(-9) from dual; >> 9

------------------------------------------

+ sin, cos, tan :

sin(or cos, or tan)(값 or 컬럼명)

------------------------------------------

+ round(값 or 컬럼명, 반올림할 자릿수) : 반올림

- 소수부 : 다음 자리에서 반올림하여 해당 자리를 보여준다.(양수로 기술)

- 정수부 : 해당 자리에서 반올림된다.(음수로 기술)

ex) 789.789 -(index)-> -3(7),-2(8),-1(9),0(.),1(7),2(8),3(9)이다.

select round(777.777, 2) from dual; >> 777.78

select round(777.777, -1) from dual; >> 780

------------------------------------------

+ ceil(값 또는 컬럼명) : 소수 첫번째 자리에서 올림

select ceil(10.1) from dual; >> 11

------------------------------------------

+ floor(값 또는 컬럼명) : 소수 첫번째 자리에서 버림

select floor(10.9) from dual; >> 10

------------------------------------------

+ trunc(값 or 컬럼명, 자릿수) : 절삭

select trunc(777.777, -1) from dual; >> 770

------------------------------------------

----------------------------------------

-- 함수 사용

----------------------------------------

-- 수학함수

 

-- 절대값

select abs(9), abs(-9)from dual;

 

-- sin, cos, tan

select sin(10), cos(10), tan(10)from dual;

 

-- 반올림

select round(555.555, 2), round(555.555, 0), round(555.555, -1)from dual;

 

-- 버림

select floor(10.9) from dual;

 

-- 절삭

select trunc(555.555, 0), trunc(555.555, -1), trunc(555.555, 2) from dual;

 

-- |(^▽^(^^;(ㅎㅅㅎ)ㅍ_ㅍ)'ㅂ'*)/ My Little freaks

 

 

 

*문자열 함수

- length, upper, lower, substr, instr, trim, ltrim, rtrim, lpad, rpad, initcap, concat

ex)

+ length(컬럼명) : 길이

+ upper(컬럼명) : 대문자 변환

+ lower(컬럼명) : 소문자 변환

+ instr(컬럼명, 찾을 문자) : 문자열의 index를 얻을 때 사용한다.(Oracle은 index가 1번부터 시작한다. 따라서 찾는 문자가 없으면 0을 반환한다.)

+ concat(컬럼명1, 컬럼명2) : 문자열 합치기

+ substr(컬럼명, 시작 index, 자를 문자수)

+ trim(컬럼명) : 앞&뒤 공백 제거 / ㅣltrim(컬럼명) : 앞 공백 제거 / rtrim(컬럼명) : 뒷 공백 제거

+ lpad(컬럼명, 총 글자 수, 채울 문자) : 왼쪽에 글자를 채운다. ≒ rpad() : 오른쪽에 글자를 채운다.

lpad('ABC', 7, 0) >> 0000ABC

-- 문자열 함수

 

-------------------

-- 길이

select length('AbCdR'), length('안녕하세요') from dual;

 

-- 문) 사원명이 4자인 사원명을 조회하라

select ename

from emp

where length(ename) = 4;

 

 

-------------------

-- 대, 소문자 변환

select upper('sHinee'), lower('ShINEE') from dual;

 

-- 문) 사원명이 'scott'인 사원의 사원명, 연봉, 입사일을 조회하라(단, ENAME에는 데이터가 모두 대문자로 저장되어 있다.)

SELECT ename, SAL, HIREDATE

FROM EMP

WHERE lower(ename) = 'scott';

 

-- 문) 사원명을 모두 소문자로 출력하라

select lower(ename) ename from emp;

 

 

-------------------

-- 인덱스 얻기(1번부터 시작)

select instr('ABCDEF', 'D'), instr('ABCD', 'K'), instr('다운타운베이비', '다') from dual;

 

 

-------------------

-- 문자열 자르기

select substr('Downtown Baby - SHINee', 10, 4) substr1,

substr('Downtown Baby - SHINee', 10) substr2

from dual;

 

 

-------------------

-- 앞, 뒤 공백 자르기

select '[' || trim('   O new   ') || ']' trim1,

'[' || ltrim('   O new   ') || ']' trim2,

'[' || rtrim('   O new   ') || ']' trim3,

'[' || trim('오' from '오 필승 코리아 오오오') || ']' trim4

from dual;

 

 

-------------------

-- 문자열 합치기

select

'이진기',

'이태민',

concat('이진기','이태민') as 이형제,

'이진기' || '이태민' as 이형제2

from dual;

 

 

-------------------

-- 문자열 채우기(한글은 글자수가 맞지 않으면 표현하지 않는다. lpad2 실행결과 >> 가가가ABC)

select lpad('ABC', 10, '&') lpad1,

lpad('ABC', 10, '가') lpad2,

rpad('ABC', 10, '#') rapd1

from dual;

-- 문) test@test.com의 이메일 주소만 잘라라

select trim('@' from substr('test@test.com', instr('test@test.com', '@'))) as 닷com,

substr('test@test.com', 1, instr('test@test.com', '@')-1) as id

from dual;

 

 

-------------------

-- initcap 첫글자를 대문자로 변환 : 띄어쓰기 이후의 첫글자도 적용

select initcap('java oracle') from dual;

 

 

 

*null 변환

- nvl : null 변환

ex)

nul(컬럼명, null일때 출력할 값(컬럼의 데이터형과 동일한 값이어야 한다.))

이때, 데이터형이 숫자일때 '0'와 같이 문자열이지만 그 값이 숫자일 경우에는 error가 없다.

ex) comm number형 nvl(comm, '0') -> 문제 없음

-------------------

-- null 변환

-- 문) 사원번호, 사원명, 입사일, 연봉, 보너스, 연봉합산액를 조회하라. 단, 보너스가 null이라면 0을 출력한다.

-- 연봉합산액은 연봉+보너스이며 보너스가 없는 경우 100원으로 일괄 지급하여 계산하라.

SELECT EMPNO, ENAME, HIREDATE, SAL, nvl(COMM, 100), sal+nvl(comm, 100)

FROM EMP;

 

-- 우편번호 테이블에서 동이름이 역삼1동인 동의 우편번호, 시도, 구군, 동, 번지를 조회하라

-- 단, 번지가 없다면 '번지 없음'을 출력하라

select ZIPCODE, SIDO, GUGUN, DONG, nvl(BUNJI, '번지 없음') BUNJI

FROM ZIPCODE

WHERE DONG like '역삼1동%';

 

 

 

*변환 함수

- to_char, to_date, to_number

 

 

*조건 함수

- decode(select의 case와 같이 볼 것)

ex)

+ decode(컬럼명, 비교값1, 수행코드1, 비교값2, 수행코드2, ... 모든 조건에 맞지 않을 때 수행코드) : 조건에 맞을 때 그리고 작성할 코드가 짧은 경우에 사용한다.

+ case 컬럼명 when 비교값1 then 수행코드1 ... others (비교값이 없을 때) 수행코드 end alias : 조회하는 컬럼의 값에 따라 다른 코드가 실행되어야 할 때 사용한다.

-------------------

-- 조건 함수

-------------------

-- decode

 

-- 성이 최면 민호를 출력하고, 성이 심이면 창민을 출력하고, 성이 정이면 윤호를 출력하라. 또 그 외라면 다타베를 출력하라.

select decode('최', '최', '민호', '심', '창민', '정', '윤호', '다타베') name

from dual;

 

-- 문) 사원번호, 사원명, 입사일, 부서번호, 부서명을 조회하라.

-- 단, 부서명은 아래의 표와 같이 출력한다.

-- 10:SI개발부, 20:SM유지보수, 30:QA품질보증, 40:유령부

SELECT EMPNO, ENAME, HIREDATE, DECODE(DEPTNO, 10, 'SI개발부', 20, 'SM유지보수', 30, 'QA품질보증', 40, '유령부') DNAME

FROM EMP;

 

 

-------------------

-- case

/*

case 컬럼명 when 비교값 then 코드

       when 비교값 then 코드

       when 비교값 then 코드

       .

       .

       .

       others 비교값 없을 때 코드

end alias;

*/

 

-- 사원번호, 사원명, 연봉, 보너스를 출력하라

-- 단, 보너스는 부서별로 아래표와 같이 차등 지급한다.

-- 10:연봉의 5%, 20:연봉의 3%, 30:연봉의 3.5%, 그 이외에는 2%로 계산한다.

select empno, ename, sal,

case deptno when 10 then sal*0.05

when 20 then sal*0.03

when 30 then sal*0.035

else sal*0.02

end comm

from emp;

 

 

 

*집계 함수(그룹 함수)

- sum, avg,

 count(row count), max, min

*날짜 함수

- add_months, months_between