[Oracle] 2020.12.17. day_39 조건함수(decode & case문), 변환함수(문자변환 to_char(), 날짜변환 to_date(), 숫자변환 to_number())

 

○ 조건함수

함수명	기능	사용법
decode	비교값에 해당하면 반환값을 반환한다 PL/SQL에서 사용할 수 없다 - case문과 같은 동작 수행 (반관값에 대한 코드가 짧을때 : decode 길 때 : case) java의 else if 문(다중 if)과 비슷하게 이해할 수 있다	decode(컬럼명, 비교값, 반환값, 비교값, 반환값, 비교값, 반환값, ,,,,,,,,, 비교값이 없을 때 반환할 값) decode(1, 1, '하나', 2, '둘', 3, '셋', '너무크다') ==> 하나 decode(9, 1, '하나', 2, '둘', 3, '셋', '너무크다') ==> 너무크다
함수는 아니다 문법으로 이해 case	비교값에 해당하면 반환값을 반환한다 조회하는 컬럼에 사용 case문은 반환값에 해당하는 코드가 길 때 사용	case 컬럼명     when 비교값 then 실행코드     when 비교값 then 실행코드     when 비교값 then 실행코드     ,,,     else 비교값이 없을 때 실행코드 (없어도됨) end

○ 조건함수 연습해보기

함수	예시코드	실행
decode	select decode(1, 1, '하나', 2, '둘', 3, '셋', '너무크다')  숫자변환, decode(9, 1, '하나', 2, '둘', 3, '셋', '너무크다')  숫자변환 from dual;
부서 테이블에서 부서번호, 부서명, 한글부서명을 조회 단, 한글 부서명은 아래(하기)의 표와 같이 설정한다 10번 - 개발부, 20번 - 유지보수부, 30 - 영업부 , 40 - 탁구부
decode	select deptno, dname, decode(deptno, 10, '개발부', 20, '유지보수부', 30, '영업부', 40, '탁구부') kor_dname from dept;
사원 테이블에서 사원번호,사원명, 직무, 한글직무를 조회 단, 한글직무는 아래의 표와 같이 설정 CLERK - 일반사원,SALESMAN - 영업사원 , MANAGER - 관리사원 , ANALYST - 분석가, PRESIDENT - 사장
decode	select empno, ename, decode(job, 'CLERK' , '일반사원','SALESMAN' ,'영업사원' , 'MANAGER' ,'관리사원' , 'ANALYST' , '분석가', 'PRESIDENT','사장') kor_job  from   emp;
사원테이블에서 사원번호, 사원명, 연봉, 보너스, 부서번호를 조회  단, 보너스는 아래의 표와같이 부서별로 차등지금을 한다 10 - 연봉에 10% ,20 - 연봉에 15%,30 - 연봉에 20%, 그외 연봉 50%
decode trunc(소숫점자리 절사해주기)	select empno, ename, sal, trunc(decode(deptno, 10,(sal*0.1),20,(sal*0.15),30,(sal*0.2),(sal*0.5)), 0) bonus, deptno from   emp; decode를 사용하기에 코드가 중복되고, 길다면 case문과 같이 사용한다 ↓↓↓↓
case문 사용	select  case 'HTML'              when 'java' then '완벽한 OOP언어'              when 'HTML' then '마크업언어'              when 'javascript' then 'toy Language'              else '언어의 정보가 없습니다'         end  language from dual;
사원테이블에서 사원번호, 사원명, 부서번호, 부서별 보너스, 직무조회 단, 부서별 보너스는 10-100, 20-400, 30-250, 그 외 1000으로 출력
case문 사용	select empno, ename, deptno, case deptno when 10 then 100 when 20 then 400 when 30 then 250 else 1000 end dept_bonus, job from emp;

---

○ 변환함수 to_char()

함수	기능	사용법
◉ 문자변환
to_char()	문자가 아닌 것을 문자로 변경 (날짜 → 문자열, 숫자 → 문자열)	1. 날짜 to_char(날짜, 'pattern')  //날짜가 아닌 문자열 들어가면 Error letter-pattern-format format이 pattern보다 상위의 개념이라 이해
1. 날짜 → 문자열
pattern, letter 알아보기	dos, sqldeveloper는 년/월/일로, golden7은 시간까지 나온다	letter가 대소문자를 구분하지 않는다 ▷▷ pattern y-년 m-월 d-일 hh-시(12) hh12-시(12) hh24-시(24) mi-분 (java에서는 대문자M이지만 오라클은 대소문자 구분하지 않아서 이렇게 사용한다) s-초 - 월,일,시,분,초는 2개씩은 넣어야한다 am-오전,오후 dy-요일(월,화,,) day-요일(월요일,,) q-분기(1~4분기)

○ 변환함수 to_char()

1. 날짜-문자열 연습하기

함수	예시코드	실행
to_char() 1. 날짜-문자열	select  to_char(sysdate, 'YYYY"년"mm-dd am HH(hh24):mi:ss day(dy) q"분기') day from dual; 대소문자를 구분하지 않는다 letter가 아닌 문자를 사용할 때에는 "쌍따옴표"를 사용하면 된다
	pattern이 길면 Error 발생 select to_char(sysdate, 'yyyy " 년 " mm " 월 " dd " 일 " am hh " 시 " mi " 분 " ss " 초 "') from  dual;
위의 문제를 해결하려면 이렇게 하면 된다
to_char() 1. 날짜-문자열	select to_char(sysdate, 'yyyy " 년 " mm " 월 " dd " 일 "')  || to_char(sysdate, 'am hh " 시 " mi " 분 " ss " 초 "') long_date from  dual;
사원테이블에서 사원번호, 사원명, 입사일을 조회 단, 입사일은 월-일-년의 형식으로 출력
to_char() 1. 날짜-문자열	select empno, ename, to_char(hiredate, 'mm-dd-yyyy') "월-일-년" from emp;
사원테이블에서 입사년도가 1981년인 사원의 사원번호, 사원명, 연봉, 입사일, 부서번호 조회
to_char() 1. 날짜-문자열	select empno, ename, sal, hiredate, deptno from   emp where  to_char(hiredate, 'yyyy') = '1981';

---

○ 변환함수 to_char()

함수	기능	사용법
2. 숫자 → 문자열  java의 DecimalFormat과 비슷하다
to_char()	문자가 아닌 것을 문자로 변경 (날짜 → 문자열, 숫자 → 문자열)	to_char(숫자, 'pattern')    0-해당자리에 데이터가 없다면 0으로 출력    9-데이터가 존재하는 것만 출력한다 to_char(2020, '0,000,000') ==> 0,002,020 //0으로 채워져 있다면, 데이터를 초과하는 자리에는 0으로 채워진다 to_char(2020, '9,999,999') ==>2,020 //9로 채워져 있다면, 존재하는 것만 출력

○ 변환함수 to_char()

2. 숫자-문자열 연습하기

함수	예시코드	실행
to_char() 2. 숫자-문자열 변환	select to_char(2020, '0,000,000') "0pattern" from   dual;	앞자리 한칸에는 공백이 들어간다
	select to_char(20202, '0,000') "0pattern" from   dual; pattern보다 데이터가 길면 값이 나오지 않는다 (자동으로 인식해주는 java와는 다르다 따라서 충분히 길게 pattern을 만들어줘야한다)
	select to_char(2020, '9,999,999') "9pattern" from   dual;	앞자리 한칸에는 공백이 들어간다
사원테이블에서 사원번호, 사원명, 입사일, 연봉을 조회 단, 연봉은 3자리를 초과했을 때만 ','를 3자리마다 붙인다
to_char() 2. 숫자-문자열 변환	select empno, ename, hiredate, to_char(sal, '9,999,999') sal from emp;

---

○ 날짜변환

to_date()

함수	기능	사용법
to_date()	날짜형식의 문자열을 날짜로 변환	to_date('날짜형식의 문자열', 'pattern') pattern은 to_char의 형식과 같다 to_date('2020-12-17', 'yyyy-mm-dd') //날짜형식의 문자열 : 일,시,분,,등 정확히 알수없음 //원하는 형식대로 명시해준다

select  to_date('2020-12-17', 'yyyy-mm-dd'), '2020-12-17'  from    dual;
	to_char의 첫번째 매개변수에는 문자열이 아닌, 날짜형식의 날짜, 숫자만 가능하다
위의 ERROR 해결방법
select  to_char(to_date('2020-12-17', 'yyyy-mm-dd'), 'yyyy') from    dual; 문자열을 날짜형식으로 변환해주는 to_date를 사용하여 to_char를 사용할 수 있다

○ Oracle 의 자동형변환을 이용하여 to_char()로 날짜를 insert해서 넣어보기

sysdate로 추가 insert into student(num, name, input_date) values(6, '남경수', sysdate);	현재날짜가 sysdate패턴에 맞게 입력된다  년-월-일 오전오후 시:분:초
--1. 날짜형식의 문자열 (Oracle에서 문자열을 날짜로 자동형변환해준다) insert into student(num, name, input_date) values(7, '박종영', '2020-12-17');	Oracle에서 문자열을 날짜로 자동형변환해서 넣어준다
--2. to_date()를 사용하여 날짜형식의 문자열을 날짜로 변환하여 추가 insert into student(num, name, input_date) values(8, '신경주', to_date('2020-12-17', 'yyyy-mm-dd'));	pattern에 맞게 날짜로 변환하고, 날짜를 넣는다

---

○ 숫자형태 문자열을 숫자로 변환

함수	기능	사용법
to_number()	숫자형식의 문자열을 숫자로 변환	to_number('숫자형식의 문자열')
	문자열은 사칙연산이 되지 않는다 (숫자만 가능) 하지만, Oracle이 숫자형식 문자열을 알아서 형변환시켜서 연산에 참여시킨다	select '1', '2', '2'-'1' from dual;
	하지만, to_number를 통해 숫자로 변환해주는 것이 좋다	select to_number('2')-to_number('1') from  dual;
	우측 error를 보면, 첫번째쿼리문 :  숫자가 아닌 문자열을 넣어도 Oracle이  일단 숫자의 형태로 자동형변환하려고 했다는 것을 알 수 있다 두번째쿼리문 : 숫자형태가 아닌 문자열to_number사용 두개의 쿼리문이 같은 오류를 낸다

 

 

 

 

▼▼ 수치함수, null변환함수, 문자열함수도 배우기 ▼▼

2020/12/17 - [개발/Oracle] - [Oracle] 2020.12.17. day_39 Oracle에서 제공하는 함수(수치함수, null변환함수, 문자열함수), dual table

[Oracle] 2020.12.17. day_39 Oracle에서 제공하는 함수(수치함수, null변환함수, 문자열함수), dual table