<이공계전문기술연수> 2. DB 단일 행 함수

함수(Function)

하나의 큰 프로그램에서 반복적으로 사용되는 부분들을 분리하여 작성해 놓은 작은 서브프로그램
호출하며 값을 전달하면 수행결과를 리턴하는 방식으로 사용된다.

 

함수의유형

 

단일 행 함수 :
각 행마다 반복적으로 적용되어 입력받은 행의 갯수만큼 결과를 반환
여러개 값 전달 및 호출 -> 작업 수행 -> 여러개의 결과값 리턴

 

그룹 함수 : 
특정한 행들의 집합으로 그룹이 형성되어 적용 -> 그룹당 1개의 결과를 변환
여러개 값 전달 및 호출 -> 그룹 작업 수행 -> 한개의 결과값 리턴

 

단일 행 함수

문자처리함수

LENGTH
-> 주어진 컴럼 값 / 문자열의 길이(문자갯수)를 반환
-> 문자타입을 입력 -> NUMBER

LENGTHB
->주어진 칼럼 값/문자열의 길이(BYTE)를 반환

INSTR
찾는 문자가 지정한 위치부터 지정한 횟수만큼 나타난 위치 반환

LPAD / RPAD
주어진 컬럼 문자열에 임의의 문자열을 왼쪽/또는 오른쪽에 덧붙여
길이 N의 문자열을 반환하는 함수

LTRIM / RTRIM
주어진 컬럼이나 문자열의 왼쪽 혹은 오른쪽에서 지정한 STR포함된 모든 문자를 제거
STR포함된 모든 문자를 제거한 나머지를 반환

TRIM
주어진 컬럼이나 문자열의 앞/뒤/양쪽 있는 지정한 문자를 제거한 나머지 반환

SUBSTR
컬럼이나 문자열에서 지정한 위치부터 지정한 갯수의 문자열을 잘라내어 리턴

LOWER / UPPER / INITCAP
대소문자 변환

REPLACE 
수정

ABS 

절대값

ROUND

반올림
인자로 전달받은 숫자 혹은 컬럼에서 지정한 위치 이후값을 반올림하여 리턴

FLOOR 

소수점자리수를 버림

 

--------실습--------
--1.EMPLOYEE 테이블에서 이름,연봉, 총수령액(보너스포함), 실수령액(총 수령액-(월급*세금 3%))가 출력되도록 하시오
select * from employee;
select emp_name as 이름 , salary*12 as 연봉, (salary+(salary*bonus))*12 as 총수령액 ,
((salary+(salary*bonus))*12-(salary*0.03*12)) as 실수령액 from employee;

--2.EMPLOYEE 테이블에서 이름, 근무 일수를 출력해보시오 (SASDATE 를 사용하면 현재 시간 출력)
select * from employee;
select emp_name as 이름, floor(sysdate - hire_date) as 근무일수 from employee; 

--3. EMPLOYEE 테이블에서 20년이상 근속자의 이름, 월급, 보너스율을 출력하시오.

select * from employee;
select emp_name, salary, bonus from employee where (sysdate - hire_date)/365 >= 20;


--DISTINCT : 칼럼에 포함된 중복 값을 한번씩만 표시할때 사용
select distinct job_code from employee;


--급여가 350만원보다 많이받고 600만원보다 적게받는 직원 이름과 급여조회
--이상/이하 -> 입력한 값 포함

select emp_name, salary from employee where salary between 3500000 and 6000000;

-- employee 테이블에서 고용일이 90/01/01 ~ 01/01/01인 사원의 전체정보 출력
select hire_date from employee;
select * from employee where hire_date between '90/01/01' and '01/01/01'; 

--LIKE
--비교하려는 값이 지정한 특정 패턴을 만족시키면 TRUE
--'%' , '_' 와일드카드 : 아무거나 대체해서 사용 할 수 있는 것
--성이 '전'씨 인 직원의 모든정보 조회
--% : 모든것, _ : 한문자
select * from employee where emp_name='전'; -- 완전히 일치하는것을 찾는것
select * from employee where emp_name like '전__'; --언더바 2개 이름이 3글자라서
select * from employee where emp_name like '전%'; --%는 '전'으로 시작하는 모든것

--이름에 전이 포함되는 직원의 정보 출력
select *from employee where emp_name like '%전%';
--'이'씨 성이 아닌 직원의 이름, 이메일 조회
select emp_name, email from employee where emp_name not like '이%';
--이메일중 _ 앞자리가 3자리인 직원 조회(이름, 이메일)
--???? _, 와일드카드 인지 구분이 안됨
--그래서 밑에것 처럼 쓰면 됨! ESCAPE!!!
select emp_name, email from employee where email like '___#_%' ESCAPE '#';

--1. EMPLOYEE 테이블에서 이름 끝이 연으로 끝나는 사원의 이름을 출력하시오.
select emp_name from employee where emp_name like '%연';

--2. EMPLOYEE 테이블에서 전화번호 처음 3자리가 010이 아닌 사원의 이름, 전화번호를 출력하셈
select * from employee;
select emp_name, phone from employee where phone not like '010%';

--3. employee 테이블에서 메일주소의's'가 들어가면서, DEPT_CODE가 D9 또는 D6이고
-- 고용일이 90/01/01 ~ 00/12/01 이면서, 월급이 270만원이상인 사원의 전체정보를 출력하시오

select * from employee where (email like '%s%') and (DEPT_CODE = 'D9' or DEPT_CODE ='D6') and
(hire_date between '90/01/01' and '00/12/01') and (salary >= 2700000);

select emp_name, salary||'원' from employee;            -- -> ||'원' 같은 컬럼안에 데이터가 붙음 // 연결연산자




-- IS NULL / IS NOT NULL
-- NULL 여부를 비교하는 연산자

select * from employee;

-- 보너스가 없는 직원의 이름 조회
select emp_name from employee where bonus is null; 
-- 보너스가 있는 직원의 이름 조회
select emp_name from employee where bonus is not null; 
select emp_name from employee where not bonous is null; --null 인 애들을 찾아 그리고 뒤집는것


--IN
--비교하려는 값 목록에 일치하는 값이 있으면 TRUE

--DEPT_CODE가 D6이거나 D9인 직원의 이름 조회
select emp_name from employee where DEPT_CODE = 'D6' or DEPT_CODE = 'D9';

select emp_name from employee where DEPT_CODE in ('D6' , 'D9');
select emp_name from employee where not DEPT_CODE in ('D6' , 'D9');


--부서원 중 직급코드가 J7 또는 J2이고, 급여가 2000000원 초과인 사람의 이름, 급여, 직급코드(job_code)출력

select emp_name, salary, job_code from employee
where (job_code in('J7' , 'J2')) and salary > 2000000;

---------------
select * from employee order by emp_name asc; -- 없어도됨
select * from employee order by emp_name desc;
select * from employee order by 2 ;  -- 컬럼 순서 .

select * from employee order by dept_code, job_code asc; --> dept_code 중에 job_code 도 배열 정리

 

 

 

 
------------------------------------------------------------------------
-- 문제1. 
-- 입사일이 5년 이상, 10년 이하인 직원의 이름,주민번호,급여,입사일을 검색하여라
select * from employee;
select emp_name, emp_no, hire_date from employee where (sysdate-hire_date)/365>=5 and (sysdate-hire_date)/365<=10;
--where (sysdate-hire_date)/365 between 5 and 10;

-- 문제2.
-- 재직중이 아닌 직원의 이름,부서코드를 검색하여라 (퇴사 여부 : ENT_YN) 
select emp_name, dept_code from employee where ENT_YN != 'N';


-- 문제3.
-- 근속년수가 10년 이상인 직원들을 검색하여
-- 출력 결과는 이름,급여,근속년수를 근속년수가 오름차순으로 정렬하여 출력하여라
-- 단, 급여는 50% 인상된 급여로 출력되도록 하여라.
select emp_name, salary*1.5, sysdate - hire_date from employee 
where (sysdate - hire_date)/365 >= 10 order by (sysdate - hire_date) asc; --order by 3 이라고 해도됨

-- 문제4.
-- 입사일이 99/01/01 ~ 10/01/01 인 사람 중에서 급여가 2000000 원 이하인 사람의
-- 이름,주민번호,이메일,폰번호,급여를 검색 하시오

select emp_name, email, phone, emp_no, salary from employee where hire_date between '99/01/01' and '10/10/01' and salary <= 2000000;

-- 문제5.
-- 급여가 2000000원 ~ 3000000원 인 여직원 중에서 4월 생일자를 검색하여 
-- 이름,주민번호,급여,부서코드를 주민번호 순으로(내림차순) 출력하여라
select * from employee;
select emp_name, salary, dept_code from employee 
where salary between 2000000 and 3000000 and emp_no like '__04%__-2%'
order by emp_no desc; 

--문제 6.
--남자 사원 중 보너스가 없는 사원의 오늘까지 근무일을 측정하여 급여의 10% 보너스를 계산하여 이름, 특별보너스(계산금액) 결과를 출력하여라.
--단, 이름 순으로 오름 차순 정렬하여 출력하여라
--특별보너스 = 근무일수 X 급여 X 0.1

select * from employee;
select emp_name,floor(sysdate-hire_date)*salary*0.1 ||'원' as "특별 보너스" from employee
where emp_no like ('______-1%') and bonus is null 
order by emp_name;

 

 

 

 

LENGTH / LENGTHB / INSTR / LPAD / RPAD / LTRIM / RTRIM / TRIM 실습

--#LENGTH
--> 주어진 컴럼 값 / 문자열의 길이(문자갯수)를 반환
--> 문자타입을 입력 -> NUMBER
---LENGTH
select emp_name, length(emp_name), email, length(email) from employee;

--#LENGTHB
-->주어진 칼럼 값/문자열의 길이(BYTE)를 반환
--LENGTHB

select emp_name, lengthb(emp_name), email, lengthb(email) from employee; 



--#INSTR
--찾는 문자가 지정한 위치부터 지정한 횟수만큼 나타난 위치 반환
select instr('Hello World Hi High' , 'H' , 1,1) from dual;--1 --첫번째부터 나오는 자리
select instr('Hello World Hi High' , 'H' , 1,2) from dual;--13 -- -1은 뒤에서 부터!
select instr('Hello World Hi High' , 'H' , 1,3) from dual;--16
select instr('Hello World Hi High' , 'H' , 2,1) from dual; --13 2번째부터 시작을 하는데 나오는값은 13
select instr('Hello World Hi High' , 'H' , 2,2) from dual;
select instr('Hello World Hi High' , 'H' , 2,1) from dual;
select instr('Hello World Hi High' , 'H' , -1,1) from dual;
select instr('Hello World Hi High' , 'H' , -1,3) from dual;
select instr('Hello World Hi High' , 'Hi' , 1,2) from dual;

--Email,email컬럼의 @위치 출력
select * from employee;
select email, instr(email,'@',1,1) 위치 from employee;


--LPAD / RPAD (left right)
--주어진 컬럼 문자열에 임의의 문자열을 왼쪽/또는 오른쪽에 덧붙여
--길이 N의 문자열을 반환하는 함수
select LPAD(email,20,'#') from employee;
select RPAD(email,20,'#') from employee;
select RPAD(email,10,'#') from employee; -- 모자르면 자른다!

--LTRIM / RTRIM
--주어진 컬럼이나 문자열의 왼쪽 혹은 오른쪽에서 지정한 STR포함된 모든 문자를 제거
--STR포함된 모든 문자를 제거한 나머지를 반환

select ltrim('000123456','0') from dual;
select ltrim('123123KH','123') from dual;
select ltrim('123123KH123','123') from dual;
select ltrim('ACABACCKH' , 'ABC') from dual;
select ltrim('12345678945612KH' , '0123456789') from dual;

--TRIM
--주어진 컬럼이나 문자열의 앞/뒤/양쪽 있는 지정한 문자를 제거한 나머지 반환
select trim('Z' from 'ZZZKHZZZ') from dual; -- 양쪽에서 z가 다 지워짐
select trim(leading 'Z' from 'ZZZKHZZZ') from dual; -- 앞에만사라짐
select trim(trailing 'Z' from 'ZZZKHZZZ') from dual; --뒷쪽제거
select trim(both 'Z' from 'ZZZKHZZZ') from dual; --둘다제거 (생략가능)

 

 

--실습문제1
--Hello KH Java 문자열을 Hello KH 가 출력되게 하여라.
select rtrim('Hello KH Java', 'Java') from dual;

--실습문제2
--Hello KH Java 문자열을 KH Java 가 출력되게 하여라.
select ltrim('Hello KH Java', 'Hello') from dual;

--실습문제3(TRIM으로 해보세요)
--DEPARTMENT 테이블에서 DEPT_TITLE을 출력하여라
--이때, 마지막 부 글자를 빼고 출력되도록 하여라
--ex> 회계관리부 -> 회계관리
select dept_title from department;
select trim('부' from dept_title) from department;


--십습문제4
--다음문자열에서 앞뒤 모든 숫자를 제거하세요.
--'982341678934509hello89798739273402'
select ltrim(rtrim('982341678934509hello89798739273402','0123456789'),'0123456789') from dual;

--SUDSTR
--컬럼이나 문자열에서 지정한 위치부터 지정한 갯수의 문자열을 잘라내어 리턴


select substr('SHOWMETHEMONEY',5,2) from dual;
select substr('SHOWMETHEMONEY',5,2) from dual;
select substr('SHOWMETHEMONEY',5,2) from dual;
select substr('SHOWMETHEMONEY',5,2) from dual;

--실습문제1
--사원명에서 성만 중복없이 사전순으로 출력하세요.
select distinct(substr(emp_name , 1,1)) from employee order by 1;

--실습문제2
--employee 테이블에서 남자만 사원번호, 사원명, 주민번호, 연봉을 나타내세요.
--주민번호 뒷자리는 *처리하세요!!
select * from employee;
select emp_id, emp_name, rpad(substr(emp_no,1,8),14,'*'), salary*12 
from employee where emp_no like '%-1%' or emp_no like '%-3%';

 

 

LOWER / UPPER / INITCAP / CONCAT / REPLACE / ABS / MOD / ROUND / CEIL 실습

--LOWER / UPPER / INITCAP
--대소문자 변환
select lower('Welcome To MY World') from dual;
select upper('Welcome To MY World') from dual;
select initcap('welcome to my world') from dual;

--concat
--문자열 두개를 합쳐서 리턴하는 함수||와 같은기능

select concat('가나다라' , 'abcd') from dual;
select '가나다라' || 'abcd' from dual;

--Replace
--수정

select replace('Hello World' , 'World' ,'Java') from dual; --> 변경될것, 변경할것


--실습문제
--EMPLOYEE 테이블의 모든 직원의 이름, 주민번호, Email을 출력하시오
--단, 출력시 Email은 kh.or.kr 에서 iei.or.kr 로 변경하여 출력되게 하시오
--ex) sun_di@kh.or.kr -> sun_di@iei.or.kr

select emp_name, emp_no, replace(email,'kh','iei') from employee;

select replace(emp_no, substr(emp_no,9),'******') from employee; --주민번호 *처리


--ABS -> 절대값

select abs(-10) from dual;

--MOD -> 나머지값

select mod(10,3) from dual;
select mod(10,2) from dual;
select mod(10,4) from dual;


--Round -> 반올림
--인자로 전달받은 숫자 혹은 컬럼에서 지정한 위치 이후값을 반올림하여 리턴
select round(126.456 , 3) from dual;
select round(126.456 , 2) from dual;
select round(126.456 , 1) from dual;
select round(126.456 , 0) from dual;
select round(126.456 , -1) from dual;
select round(126.456 , -2) from dual;

-- FLOOR --> 소수점 자리수 버림
select floor(100.123) from dual; 


-- ceil -> 소수점 자리수 올림
select ceil(100.123) from dual;



--to_char처리함수
--sysdate - > 시스템의 현재시간 반환
select sysdate,current_date,localtimestamp, current_timestamp from dual;

--months_between
--to_char 두개를 전달받아 개월수차이를 숫자형태로 리턴

select emp_name, floor(months_between(sysdate,hire_date))
from employee;


--add_months
--인자로 전달받은 to_char에 인자로 전달받은 개월수를 더하여 리턴
select sysdate, add_months(sysdate,4) from dual;

 

--실습문제
--오늘부로 홍길동이 군대에 또 끌려갑니다.
--군복무 기간이 1년 6개월을 한다라고 가정하면 첫번째, 제대일자를 구하시고,
--두번째, 제대일자까지 먹어야할 짬밥의 그릇수를 구합니다.
--(단, 1일3끼를 먹는다고 한다.)
--테이블은 dual로 하세요.

select add_months(sysdate,18) from dual;
select (add_months(sysdate,18)-sysdate)*3 as "밥" from dual;

 

 

 

NEXT_DAY / LAST_DAY / EXTRACT 실습

--NEXT_DAY
--인자로 전달받은 to_char에 인자로 전달받은 요일 중 가장 가까운 다음요일
select next_day(sysdate,'수요일') from dual;
--숫자사용가능 1=일요일 ... 7=토요일


--LAST_DAY
--인자로 전달받은 to_char가 속한 달의 마지막 to_char를 구하여 리턴
select last_day(sysdate) from dual;
select hire_Date, last_day(hire_Date) from employee;

-- EXTRACT
--년,월,일 정보를 추출하여 리턴
select extract(year from sysdate) from dual;
select extract(month from sysdate) from dual;
select extract(day from sysdate) from dual;

 

-- 실습문제1
-- EMPLOYEE 테이블에서 사원의 이름, 입사일, 년차를 출력하여라.
-- 단, 입사일은 YYYY년M월D일로 출력하도록 하여라.
-- 년차 출력은 소수점 일 경우 올림으로 하여 출력하여라.(28.144 ->29년차)
--출력시 정렬은 입사일 기준으로 오름차순
select * from employee;
select emp_name, EXTRACT(year from hire_date)||'년'
||EXTRACT(month from hire_date)||'월'
||EXTRACT(day from hire_date)||'일',
ceil(months_between(sysdate,hire_date)/12)년차 
from employee order by 2;

-- 실습문제2
-- 특별보너스를 지급하기 위하여 자료가 필요하다(기준달은 입사 다음달의 6개월 후 시점)
-- 입사일을 기점으로 다음달 1일부터 6개월을 계산하여 출력하시오.
--(이름, 입사일, 기준일, 기준일 +6, 기준달(월))
--ex)90년2월6일 입사 -> 90년 3월1일 부터 계산
--   90년2월26일 입사 -> 90년3월1일부터 계산
--   97년12월1일 입사 -> 98년 1월1일 부터 계산
-- 출력시 입사일 기준으로 정렬하시오!

select * from employee;
select emp_name, hire_date, last_day(hire_date)+1, add_months(last_day(hire_date)+1,6),
extract(month from add_months(last_day(hire_date)+1,6))||'월' from employee order by 2;

 

 

형 변환 함수 실습

--형변환 함수
--TO_CHAR -> to_char, 숫자데이터를 문자형 데이터로 변환하여 리턴
--TO_CHAR(data, 포멧)
--to_char
--YYYY : 년도표현(4자리), YY : 년도표현(2자리), 
--MONTH(월표시), MM : 월을 숫자로 표시, MON : 월을 알파벳으로
--DD : to_char표현, D : 요일을 숫자로 표현(1:일요일 ....)
--DAY : 요일표현, DY : 요일을 약어로 표현

--HH, HH12 : 시간표현(12시간) , HH24 : 시간표현(24시간)
--MI : 분 , SS: 초, AM,PM : 오전, 오후표기, FM : 월,일,시,분,초 앞의 0 제거

select TO_CHAR(sysdate, 'YYYY-MM-DD') from dual;
select TO_CHAR(sysdate, 'YYYY/MM/DD') from dual;
select TO_CHAR(sysdate, 'YYYY/MM/DD/DAY') from dual;
select TO_CHAR(sysdate, 'YYYY/Month/DD') from dual;
select TO_CHAR(sysdate, 'YYYY/MM/DD/PMHH12"시"MI"분"ss"초"') from dual;
select TO_CHAR(sysdate, 'YYYY/MM/DD/HH24"시"MI"분"ss"초"') from dual;
select TO_CHAR(sysdate, 'FMYYYY/MM/DD/HH24"시"MI"분"ss"초"') from dual;


--숫자 포맷
-- ,       9,999 콤마형식으로 변환
-- .       99.99 소수점 형식으로 변환
-- $       $9999 $통화로 표시
-- L       \9999 로컬통화로 표시
-- XXXX    16진수로 표시

select emp_name, to_char(salary, 'L999,999,999'),to_char(salary,'000,000,000') from employee;

select emp_name, to_char(salary, 'L99,999') from employee; --안나옴, 길이를 여유있게 해야됨

select 500000, to_char(500000,'999,999,999'),to_char(500000,'9,999'),to_char(500000,'000,000,000'),
to_char(500000,'L999,999,999'),to_char(500000,'999,999,999.999') from dual;

--To_DATE
--숫자 혹은 문자형 데이터를 날짜형 데이터로 변환
select to_date(20000101,'YYYYMMDD') from dual;
select to_date('20190821','yyyymmdd') from dual;
select to_char(to_date('20190821','yyyymmdd'),'yyyy/mm/dd hh24:mi:ss') from dual;

--EMPLOYEE 테이블에서 2000년도 이후에 입사한 사원의 이름, 입사일 조회

select emp_name, hire_date from employee where hire_date > to_date(20000101, 'yyyymmdd');

--TO_NUMBER
--문자형 데이터를 숫자형 데이터로 변환하여 리턴

select to_number('1,000,000', '9,999,999') - to_number('500,000','9,999,999') from dual;

select '1000' + '100' from dual;
select '1000' || '100' from dual;

--NULL 처리 함수 - NVL
-- NULL 로 되어있는 컬럼의 값을 지정한 숫자 혹은 문자로 변경하여 리턴

select salary ,NVL(bonus,0), salary*NVL(bonus,0) 보너스 from employee;

select NVL(DEPT_CODE, '없음') from employee;

 

DECODE / CASE 실습

--선택함수 DECODE, CASE
--DECODE : 여러가지 경우에 선택을 할 수 있는 기능을 제공(일치하는 값)
--DECODE(표현식,조건1,결과1,조건2,결과2,조건3,결과3,..........)

--주민등록번호를 기준으로 남/여 출력
select emp_name, decode(substr(emp_no,8,1),'1','남','2','여') from employee;
select emp_name, decode(substr(emp_no,8,1),'1','남','여') from employee; -- 이것두됨

--case : decode 와 거의 유사하지만 범위값 사용 가능
--case when 조건1 then 결과1 when 조건2 then 조건2 ......... else 결과N end

select emp_name , case when substr(emp_no,8,1) = '1' then '남' else '여' end 성별 from employee;

--60년대생들 중 60 ~ 64년생까지는 '60년생 초반'
--                  65 ~ 69년생까지는 '60년생 후반 , 이름 출력
select emp_name,
case when substr(emp_no,1,2) < 65 then '60년생 초반'
when substr(emp_no,1,2) >= 65 then '60년생 후반' end
from employee where substr(emp_no,1,1) = '6';