Oracle 함수 (숫자 함수, 문자 함수) 및 명령어

 

select first_name, to_char(hire_date, 'yyyy-mm-dd') "HIRED_AFTER_2000" from employees

to_char를 "HIRED_AFTER_2000"로 나타냄

FIRST_NAME | HIRED_AFTER_2000

Sundita | 2000-04-21

" " 쌍따옴표를 쓰지않고, 작성하면 무조건 대문자로 작성되고, AS를 사용해서 쌍따옴표를 생략할 수 있다. 쌍따옴표로 대소문자 섞을 수 있음.

order by salary desc last_name asc;

​

숫자 함수 : mod(m,n), round(m,n), trunc(m,n), width_bucket(expr, min, max, step)

문자 함수 : lower(char), upper(char), substr(str, m, n) :m~ n이 아니라 m부터 시작해서 +n

replace(str 바뀔_문자, 바꿀_문자), ltrim(expr) and rtrim(expr) : 좌측과 우측에서 빈자리를 절삭

initcap(expr) : 첫번째 대문자, length(expr) : 문자열의 갯수, 빈칸도 셈을 함

instr(expr, char) : 해당 expr(문자열)에서 첫번째로 찾은 char 문자의 위치,

대소문자 구별(char를 'o' 한다면, Korea 에서 하나를 찾고, 'O'를 한다면 없다고 나옴

lpad(expr, n, #) and rpad(expr, n, -) : 문자열이 지정된 n 보다 적을 때 빈 공간을 좌측에 #, 우측에 -로 채움

left pad 왼쪽을 채워라 right pad 오른쪽을 채워라.

concat(열_명, char) : 지정된 테이블에서 문자열을 합침 (concat() 함수 내에 또 .concat() 함수를 넣어서 다양하게 표현할 수 있다.)

날짜 함수 :

​

to_char(date_name,'yyyy')

 

// to_char(date_name,'yyyy-mm') / to_char(date_name,'yyyy-mm-dd') / to_char(date_name,'yyyy-dd') 다 가능
select first_name, to _char(hire_date,'yyyy-mm-dd') "HIRED_AFTER_2000" from employees
where to_char(hire_date,'yyyy') >= '2000'
order by hire_date desc;

FIRST_NAME | HIRED_AFTER_2000
Sundita    | 2000-04-21

select to_char(hire_date, 'yyyy'), round(avg(salary),0) from employees
group by to_char(hire_date, 'yyyy')
order by round(avg(salary),0) desc;
// 연도별 평균 급여, 평균 급여를 내림차순으로.

​

group by

 

group by 사용할 때, where 대신 having을 사용한다. where을 사용하려면, group by 전에 써야한다.
select job_id, count(employee_id) from employees
group by job_id;
// 부서별 인원

select DISTINCT department_id, round(avg(salary),0) "AVG_SAL_BY_DEPT" from employees
group by department_id order by "AVG_SAL_BY_DEPT";
select job_id, max(salary), min(salary) from employees
group by job_id
having job_id LIKE 'SA%';

​

trunc

 

select trunc(avg(salary),2) from employees where salary >= 12000;
//trunc 절삭하다. 함수안에 함수 사용 가능, trunc(avg(salary),2) 소수점 2번째 자리까지 표시

select trunc(avg(salary),-2) from employees where salary >= 12000;
// 값이 14944 였다면, -2를 사용해서 14900를 출력한다. 정수 앞자리 2개를 날림.

​

DISTINCT

 

select distinct job_id, count(*) from employees
group by job_id
having count(*) >= 5
order by count(*) desc;
// 5명 이상인 그룹, 중복 제거, 인원을 내림차순

select DISTINCT department_id from employees; // 중복 제거, 부서 개수 확인

​

count()

 

// 함수 괄호 안에는 PK, FK 등 유일한 조건만 넣어주는 것이 좋음 중복이 될 수 있음

select count(employee_id) from employees;
// 총 인원

select job_id, count(*) from employees
group by job_id;
// 부서별 인원

select job_id, sum(salary) from employees
where job_id NOT LIKE '%MGR' and job_id NOT LIKE '%MAN'
// where job_id NOT IN ('%MGR','%MAN')

select distinc job_id, count(*) from employees
group by job_id
having count(*) >= 5
order by count(*) desc;
// 5인 이상인 그룹을 보여라, 중복은 제거, 인원은 내림차순.

​

sum()

 

select sum(salary) from employees
// employees 의 salary sum 값을 구하라

​

avg()

 

select avg(salary) from employees where salary >= 12000;
// 평균 급여, 12000불 이상 salary 중

select salary from employees where salary > (select avg(salary) from employees);
// 평균 급여보다 많이 받는 사람 나왓 !

​

round()

// 소수점 n+1 자리에서 반올림하여 표시

// n의 값이 음수라면, 정수에서 n 절대값 만큼 정수자리를 찾아 반올림을 함.

round(avg(salary),2) 소수점 3번째 자리에서 반올림

​

min() max()

 

order by 로 정렬 후, rownum으로 max, min을 추출할 수 있다.

select min(salary), max(salary) from employees
where department_id = 80

​

IN ('A','B')

 

select job_id, salary from employees where job_id IN('SA_MAN','SA_REP')

select job_id, max(salary), min(salary) from employees
where job_id in ('SA_MAN') group by job_id;

select department_id, max(salary) from employees
group by department_id
having department_id in (10,20);
// 10번, 20번 부서에서 각 최대 급여자 보이시오.

select job_id, sum(salary) from employees
where (job_id NOT LIKE '%MGR' and job_id NOT LIKE '%MAN') // 괄호 안쳐도됨
// where job_id NOT IN ('%MGR','%MAN')
group by job_id
having sum(salary) >= 10000
order by sum(salary)

​

dual

// 하나의 table, 따로 table을 생성할 필요없이 간편하게 return 값을 받을 수 있다.

// 함수에 대한 쓰임을 알고 싶을 때, 사용

반지름이 5인 원의 넓이를 구하시오,

select 3.14*5*5 "AREA_R_5" from dual;

select trunc(123.4567,0) "TRUNC" from dual;

TRUNC

123

​

mod(m,n) // m을 n으로 나누었을 때, 나머지를 반환. m%n

​

width_bucket(expr,max,min,step)

 

// min~max 까지 step 구간으로 해서 expression이 어느 구간에 속하는지 보임
// 난 71점 92점이 최대점수 47점이 최소점수 8등급으로 나누었을 때, 내가 몇등급?

select width_bucket(71,92,47,8) from dual;

// 국영수 시험이 89,82,76, 우리반 최저 평균 49 최고 평균 83일 때 8등급으로 나누면 나의 평균은?

select width_bucket((89+82+76)/3,83,49,8) from dual;
select width_bucket(avg(89+82+76),83,49,8) from dual;
** width_bucket() 안에 avg() 사용 못함. 계산을 잡지 못함 **
// department_id = 50 직원들의 급여를 10등급으로 나누어 등급별로 나누시오

select employee_id, department_id, salary, width(salary,8200,2100,10) from employees
where department_id = 50 and rownum <= 10
order by width_bucket(salary,8200,2100,10) asc

​

lower(char) upper()

 

// char를 모두 소문자로 처리한다.
select lower('KorEA') "LOW" from dual;
korea 소문자로 출력

select last_name, employee_id from employees
where lower(last_name) = 'king';
// lower() 함수 사용하여, King 값을 소문자로 작성하여 불러옴

select last_name, employee_id from employees
where upper(last_name) = 'KING';
// upper() 함수 사용하여 King 값을 대문자로 작성하여 불러옴

​

SUBSTR(str,start_pos,n)

 

// 주어진 str에서 시작점부터 n까지 데이터 추출

select substr('Korea Economy',2,8) "SUBSTR" from dual;
// 2번째 자리부터 띄어쓰기까지 인식하여 8자리를 출력 / orea Eco

select substr('Korea Economy',-4,2) "SUBSTR" from dual;
// 음수는 뒤에서 셈을 시작해 뒤에서 4번째 자리인 n부터 두번째 자리인 o까지, no를 출력

​

REPLACE(str,대상,교체문)

 

// 대상 문자를 교체 문자로 치환(바꿔낀다)한다.

select replace('Korea Economy','Korea','Japan') from dual;
REPLACE('KOREAECONOMY','KOREA','JAPAN')
// Japan Economy 출력

select replace('Korea Economy','korea','') from dual;
REPLACE('KOREAECONOMY','KOREA','')
// Economy 출력

​

concat()

 

SQL> select concat(employee_id, last_name) from employees
2 where employee_id = '100';
CONCAT(EMPLOYEE_ID,LAST_NAME)
-----------------------------------------------------------------

100King
SQL> select employee_id || ' ' || last_name from employees
2 where lower(last_name) = 'king';
EMPLOYEE_ID||''||LAST_NAME

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

100 King
156 King
SQL> select concat(employee_id, last_name), concat(employee_id, concat(':', last_name)) from employees
2 where employee_id = 100;
CONCAT(EMPLOYEE_ID,LAST_NAME)

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

CONCAT(EMPLOYEE_ID,CONCAT(':',LAST_NAME))

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

100King
100:King