Oracle sqlplus select Function(단일행 함수)

select 실행 순서

1. from 테이블명

2. where 조건절

3. group by 칼럼명

4. having 조건절

5. select 칼럼명1, 칼럼명2, ...

6. order by 칼럼명 [asc/desc] > 오름차순 혹은 내림차순

 

※ 계정 파일은 oraclexe\app\oracle\product\10.2.0\server\RDBMS\ADMIN 폴더안에 있는 scott.sql 파일을 활용함.

예제 table

테이블 column 설명

- empno 사원번호

- ename 사원이름

- job 직책

- mgr 매니저의 사원번호

- hiredate 입사일

- sal 봉급

- comm 성과급

- deptno 부서번호

 

 

함수 (Function)

- 어떠한 일을 수행하는 기능으로써 주어진 인수를 재료로 처리를 하여 그 결과를 반환하는 일을 수행한다.

 

함수의 종류

1) 단일행 함수

- 하나의 row 당 하나의 결과값을 반환하는 함수

 

2) 복수행 함수

- 여러개의 row 당 하나의 결과값을 반환하는 함수

 

 

1. 단일행 함수 → 문자함수

 

1-1 CHR (아스키 코드)

- select chr(65) from dual;

 

1-2 CONCAT (칼럼명, '붙일문자') → 문자열 연결함수

- select concat(ename, '님') name from emp;

 

1-3 INITCAP ('문자열') → 시작문자를 대문자로 바꿔준다.

- select initcap('hello world") from dual;

 

1-4 LOWER ('문자열') → 문자열을 소문자로 바꿔준다.

- select lower('HELLO!') from dual;

 

1-5 UPPER ('문자열') → 문자열을 대문자로 바꿔준다.

- select upper('hello!') from dual;

 

1-6 LPAD ('문자열', 전체 자리수, '남는자리를 채울 문자') → 왼쪽에 채운다.

- select LPAD('hi', 10, '*') from dual;

 

1-7 RPAD ('문자열', 전체 자리수, '남는자리를 채울 문자') → 오른쪽에 채운다.

- select rpad('hello', 15 , '^') from dual;

 

1-8 LTRIM ('문자열', '제거할문자')

- select ltrim('abcd', 'a') from dual;

 

1-9 RTRIM ('문자열', '제거할문자')

- select rtrim('acacbcd', 'cd') from dual;

 

1-10 REPLACE('문자열1', '문자열2', '문자열3') → 문자열 1에 있는 문자열 중 문자열2를 찾아서 문자열3으로 바꾼다.

- select replace ('hello mimi', 'mimi', 'mama') from dual;

 

1-11 SUBSTR ('문자열', N1, N2) → 문자열의 N1 번째 위치에서 N2개 만큼 문자열 빼오기

- select substr('ABCDEFGHIJ' , 3, 5) from dual;

- EMP 테이블에서 ename(사원이름) 의 두번째 문자가 'A'인 사원의 이름을 출력해보시오

- select ename from emp where substr(ename, 2, 1) = 'A';

 

1-12 ASCII('문자') → 문자에 해당하는 ASCII 코드값을 반환한다.

- select ascii('A') from dual;

 

1-13 LENGTH ('문자열') → 문자열의 길이를 반환한다.

- select length('ABCDE') from dual;

- EMP 테이블에서 사원이름이 5자 이상인 사원들의 사번과 이름을 출력하기.

- select empno, ename from emp where length(ename) >= 5;

 

1-14 LEAST ('문자열1', '문자열2', '문자열3') → 문자열 중에서 가장 앞의 값을 리턴한다.

- select least('ab', 'abc', 'd') from dual;

 

1-15 NVL (칼럼명, 값) → 해당 칼럼이 null 인 경우 정해진 값을 반환한다.

- select ename, NVL(comm, 0) from emp;

 

2. 단일행 함수 → 숫자함수

 

2-1 ABS (숫자) → 숫자의 절대값을 반환한다.

- select abs(-10) from dual;

 

2-2 CEIL (소수점이 있는 수) → 파라미터 값보다 같거나 가장 큰 정수를 반환(올림)

- select ceil(3.1234) from dual;

 

2-3 FLOOR (소수점이 있는 수) → 파라미터 값보다 같거나 가장 작은 정수반환(내림)

- select floor(3.2241) from dual;

 

2-4 ROUND (숫자, 자리수) → 숫자를 자리수 +1 번째 위치에서 반올림한다.

- select round(3.2254, 2) from dual;

 

2-5 MOD (숫자1, 숫자2) → 숫자1을 숫자2로 나눈 나머지를 리턴한다.

- select mod(10,3) from dual;

 

2-6 TRUNC (숫자1, 자리수) → 숫자1의 값을 소수점이하 자리수까지만 나타낸다. (나머지는 잘라낸다)

- select trunc(12.23423423 , 2) from dual;

 

 

3. 단일행 함수 → 날짜함수

 

3-1 SYSDATE → 현재 시간을 리턴한다.

- select sysdate from dual;

 

3-2 ADD_MONTHS (날짜, 더해질월)

- select add_months(sysdate, 10) from dual;

 

3-3 LAST_DAY (날짜) → 해당날짜에 해당하는 달의 마지막 날짜를 반환한다.

- select last_day(sysdate) from dual;

 

3-4 MONTHS_BETWEEN (날짜1, 날짜2) → 두 날짜 사이의 월의 수

- select empno, months_between (sysdate, hiredate) 근무개월 from emp;

 

 

4. 단일행 함수 → 문자 변환함수

 

4-1 TO_CHAR → DATE를 문자로 변환해준다.

- select to_char(sysdate, 'yyyy-mm-dd') from dual;

 

 

5. 단일행 함수 → 숫자변환함수, 날짜 변환함수

 

5-1 TO_NUMBER('숫자에 대응되는 문자');

- select to_number('999') +1 from dual;

 

5-2 TO_DATE ('날짜에 대응되는 문자')

- select to_date('2012-12-12') from dual;

 

5-3 TD_DATE (문자열, 형식)

- create table message (num number primary key, msg varchar2(20), rdgdate date);

- 데이터 저장하기

- insert into message (num, msg, regdate) values (1, 'hi', sysdate);

- insert into message (num, msg, regdate) values (2, 'hello', '2016/10/20');

- insert into message (num, msg, regdate) values (3, 'hi', 'TO_DATE('20161020123020', 'YYYYMMDDHHMISS'));