● 과제 09/04/24

** 함수 :좀더 강력한 질의문을 작성하기 위해서

1)오라클제공 내장함수

 - 단일행함수
    - 문자형함수:lower,upper,initcap,substr,instr,
            lpad,rpad,ltrim,rtrim등
   - 숫자형함수 :round,trunc,mod등
   - 날짜형함수 :add_months,months_between,
             next_day,last_day,sysdate등
   - 형변환함수 : to_char,to_number,to_date등
   - 기타 함수 :nvl,decode,case등
- 그룹행함수 :sum,count,max,min,avg등

2)사용자 함수 : pl/sql로 작성

** 단일행함수의 사용예

1.select문장에서
select round(72.15,1),round(72.15,-1),round(72.15,0)
from dual;   ==>함수를 사용법,결과를 테스트하고자 할때

1. select ename,sal,round(sal,-1)
   from emp;

1. select ename,sal,round(sal,-2),hiredate ==>표현에 관계되는 절
   from emp
   where round(sal,-2)>=1000;   ==>행을 제한하는 조건절

2.그외문장에서

1. update emp
   set sal=round(sal,-1)
   where ename='SCOTT';

문제1) EMP 테이블에서 scott의 사원번호,성명,담당업무(소문자로),부서번호를 출력하여라.
select empno,ename,lower(job),deptno
from emp
where lower(ename)='scott';

문제2) DEPT 테이블에서 모든 부서의 부서명,위치를 첫 글자들만 대문자로 변환하여 출력하여라.
select initcap(dname),initcap(loc)
from dept;

문제3) EMP 테이블에서 이름의 첫글자가 ‘K’ 보다 크고 ‘Y’보다 작은 사원의 사원번호, 이름, 업무, 급여, 부서번호를 출력하여라. 단 이름순으로 정렬하여라.
select ename,substr(ename,1,1),substr(ename,1),substr(ename,2,2)
from emp;

select empno,ename,job,sal,deptno
from emp
where substr(ename,1,1)>'K' and substr(ename,1,1)<'Y'
order by ename;

문제4) EMP 테이블에서 부서가 20번인 사원의 사원번호, 이름, 이름의 자릿수, 급여, 급여의 자릿수를 출력하여라.
select empno,ename,length(ename),sal,length(sal)
from emp
where deptno=20;

문제5) EMP 테이블에서 급여를 30으로 나눈 나머지를 구하여 출력하여라.
select ename,sal,mod(sal,30)
from emp;

** 오라클 날짜타입:date

- 반드시 값이 세기,년,월,일,시,분,초 7개의 값이 저장된다.

select * from nls_session_parameters;   ==>현재 세션에 설정된 환경설정값들을 확인

alter session set nls_date_format='rrrr/mm/dd:hh24:mi:ss';  ==>현재 세션의 날짜표현방법을 이런패턴으로 바꿔라~
select * from emp;
alter session set nls_date_format='dd/mm/rr';

select * from emp
where hiredate >'81/01/01';  ==>?(에러)

alter session set nls_date_format='rr/mm/dd';

select * from emp
where hiredate >'81/01/01';  ==>?

- 오라클 날짜값은 표현하는 범위가 BC xxxx~ AD 9999까지 표현된다.

- 날짜 기본연산이 가능

select sysdate,sysdate-to_date('2009/03/16','rrrr/mm/dd'),sysdate+20/24
from dual;

문제6) EMP 테이블에서 현재까지의 근무일수가 몇 주 몇 일인가를 출력하여라. 단 근무 일수가 많은 사람순으로 출력하여라.
select ename,hiredate,trunc(sysdate-hiredate) "총근무일수",trunc(trunc(sysdate-hiredate)/7) "총근무주수",trunc(mod((sysdate-hiredate),7)) "나머지일수"
from emp
order by 3 desc;

문제7) EMP 테이블에서 10번 부서원의 현재까지의 근무 월수를 계산하여 출력하여라.
select ename,hiredate,trunc(months_between(sysdate,hiredate))
from emp
where deptno=10

문제8) EMP 테이블에서 10번 부서원의 입사 일자로부터 5개월이 지난 후 날짜를 계산하여 출력하여라.
select ename,hiredate,add_months(hiredate,5)
from emp
where deptno=10;

문제9) EMP 테이블에서 10번 부서원의 입사 일자로부터 돌아오는 금요일을 계산하여 출력하여라.
select ename,hiredate,next_day(hiredate,6)
from emp
where deptno=10;

문제10) EMP 테이블에서 입사한 달의 근무 일수를 계산하여 출력하여라. 단 토요일과 일요일도 근무 일수에 포함한다.
select ename,hiredate,last_day(hiredate),last_day(hiredate)-hiredate
from emp;

** 형변환 :데이터타입이 변환

- 자동형변환 :오라클이 자동으로 변환
- 수동형변환 :개발자가 함수를 사용해서 변환

** 자동형변환의 예

create table test(
id    number,
name    varchar2(10));
desc test
select * from test;
insert into test(id,name)
values(1,10);    ==>?(성공 : 숫자10을 자동으로 문자10으로 형변환시켜서 입력)
select * from test;
select ename+100 from emp;  ==>?(에러)
select id+10,name+100 from test;  ==>?(성공 : 문자 10을 자동으로 숫자10으로 변환시켜서 처리)

** 수동형변환

- to_char  함수 :날짜나 숫자를 문자로 변환하는 함수

날짜변환의 예

-현재의 세기,년,월,일,시,분,초정보및 요일정보를 확인

select sysdate,
to_char(sysdate,'rrrr/mm/dd:hh24:mi:ss:day')
from dual;

- rr과 yy의 차이점

rr : 년도가 0~49년도일 경우에는 현재세기로 인식하고, 50~99년도일 경우에는 이전세기로 인식
yy :무조건 현재세기

select * from nls_session_parameters;
==>현재 nls_date_format의 값이 rr/mm/dd로 설정되어 있음

select *
from emp
where hiredate >'81/01/01';   ==>결과1

alter session set nls_date_format='yy/mm/dd';

select *
from emp
where hiredate >'81/01/01';  ==>결과1과의 차이점

2)숫자변환의 예
emp테이블의 사원들의 급여를 돈의 단위처럼 표

- to_date함수 : 문자를 날짜로 변환시켜주는 함수

insert into emp(empno,ename,hiredate)
values(8000,'홍길동',to_date('10-12-2009:10','dd-mm-rrrr:hh24'));

select * from emp;

** DECODE/CASE함수

select ename,deptno,
decode(deptno,10,'10번부서',20,'20번부서',30,'30번부서','기타부서') "부서",
case when deptno=10 then '10번부서'
    when deptno=20 then '20번 부서'
    when  deptno=30 then '30번 부서'
    else  '기타부서'
end  "case문"
from emp;

SQL : RDB 조작하는 언어

1) SELECT : 조회

2) Create, Alter, Drop, Truncate ....

 : DDL(Data Definition Language) - 생성, 수정, 삭제

3) Insert, Update, delete

 : DML(Data Manipuation Language) - 데이터 입력, 수정, 삭제

4) Commit, Rollback, Savepoint

 : TCL (Transaction Control Language) - 트랜잭션 종료 및 취소

5) Grant, Revoke

 : DCL(Data Control Language) - 권한부여, 취소

Data dictionary table

 = System table

 = catalog table

ex) tab, nls_session_parameters, all_users, user_tab_columns

※ 왜(Why!!! Tell me Why! )!? 에 집중하라!!

SELECT * FROM dual;   dual 테이블은 함수 test를 위해서 오라클에서 제공하는 1행 1열의 테이블이다.

단일행함수

1. SELECT ROUND(72.15, 1), ROUND(72.15, -1), ROUND(72.15, 0) FROM dual;

1. SELECT ename, sal, round(sal, -1) from emp;

1. SELECT ename, sal, round(sal, -1), hiredate FROM emp WHERE ROUND(sal, -1) >= 2000;

문제 1) EMP 테이블에서 scott의 사원번호, 성명, 담당업무(소문자로), 부서번호를 출력하여라.

1. SELECT empno, ename, LOWER(job) "JOB" FROM emp WHERE ename = 'SCOTT';         //LOWER() 소문자 만들어줘라.
2. SELECT empno, ename, LOWER(job) "JOB" FROM emp WHERE LOWER(ename) = 'scott';  // 요로코롬 해주는 게 나을듯. 이름 대소문자 구분할 필요 없음.

문제 2) DEPT 테이블에서 모든 부서의 부서명, 위치를 첫 글자만 대문자로 변환하여 출력하여라.

1. SELECT dname, INITCAP(loc) FROM dept; // 두 개 항목 모두를 변경하는 것이군요.
2. SELECT INITCAP(dname), INITCAP(loc) FROM dept;

문제 3) EMP 테이블에서 이름의 첫글자가 'K'보다 크고 'Y'보다 작은 사원의 사원번호, 이름, 업무, 급여, 부서번호를 출력하여라. 단, 이름순으로 정렬하여라.

1. SELECT empno, ename, job, sal, deptno FROM emp WHERE ( SUBSTR(ename,1,1) > 'K' ) AND ( SUBSTR(ename,1,1) < 'Y' ) ORDER BY ename;

1. SELECT ename, substr(ename, 1), substr(ename, 1, 1),  substr(ename, 2, 2) FROM emp;

문제 4) EMP 테이블에서 부서가 20번인 사원의 사원번호, 이름, 이름의 자릿수, 급여, 급여의 자릿수를 출력하여라.

1. SELECT empno, ename, LENGTH(ename), sal, LENGTH(sal) FROM emp WHERE deptno = 20;

문제 5) emp 테이블에서 급여를 30으로 나눈 나머지를 구하여 출력하여라.

1. SELECT MOD(sal,30) FROM emp;

날짜 관련 변경

1. select * from nls_session_parameters;  
2. //현재 세션(현재창에서만)에 설정된 환경파라미터의 값들을 조회
3. alter session set nls_date_format='rrrr/mm/dd:hh24:mi:ss';
4. //날짜 관련 출력형식을 YYYY/MM/DD:24시:분:초 로 출력한다.
5. ALTER SESSION SET NLS_DATE_FORMAT='dd/mm/rr';
6. ALTER SESSION SET NLS_DATE_FORMAT='rr/mm/dd';

1. SELECT '2000/01/01' +30 from dual;
2. // 오류 발생
3.  SELECT to_date('2000/01/01', 'rrrr/mm/dd') +30 from dual;
4. // '2000/01/01' 문자열을 'rrrr/mm/dd' 형식으로 변경하는 함수 to_date(,)

1.  SELECT to_date('2000/01/01', 'rrrr/mm/dd') +30, sysdate  from dual;

1. SELECT SYSDATE, SYSDATE-TO_DATE('2009/03/16', 'RRRR/MM/DD') from dual;

1. SELECT SYSDATE, SYSDATE-TO_DATE('2009/03/16', 'RRRR/MM/DD'), sysdate + 20/24  from dual;
2. 시간을 더하는 것은 + Hour / 24

문제 6) emp 테이블에서 현재까지의 근무일수가 몇 주 몇 일 인가를 출력하여라. 단, 근무 일수가 많은 사람순으로 출력하여라.

1. SELECT ename,   sysdate,   TRUNC(sysdate-hiredate,0) "총 근무일수",   TRUNC(  ( TRUNC(sysdate-hiredate,0) -  TRUNC(MOD( (sysdate-hiredate), 7)) ) / 7 , 0 ) "주",    TRUNC(MOD( (sysdate-hiredate), 7)) "일" FROM emp ORDER BY (sysdate-hiredate) DESC;
2. // 아래 것도 결과는 같다.
3. SELECT ename,   sysdate,   TRUNC(sysdate-hiredate,0) "총 근무일수",   TRUNC(TRUNC( (sysdate-hiredate)/ 7) , 0 ) "주",    TRUNC(MOD( (sysdate-hiredate), 7)) "일" FROM emp ORDER BY (sysdate-hiredate) DESC;

문제 7) emp 테이블에서 10번 부서원의 현재까지의 근무 월수를 계산하여 출력하여라.

1. SELECT ename, hiredate, TRUNC( MONTHS_BETWEEN( sysdate, hiredate) ) From emp WHERE deptno = 10;

문제 8) emp 테이블에서 10번 부서원의 입사 일자부터 5개월이 지난 후 날짜를 계산하여 출력하여라.

1. SELECT ename, hiredate, ADD_MONTHS(hiredate, 5) FROM emp WHERE deptno = 10;
2. // ADD_MONTH  사용6

문제 9) emp 테이블에서 10번 부서원의 입사 일자로부터 돌아오는 금요일을 계산하여 출력하여라.

1. SELECT ename, hiredate, NEXT_DAY(hiredate, 6) FROM emp WHERE deptno = 10;
2. SELECT ename, hiredate, NEXT_DAY(hiredate, '금') FROM emp WHERE deptno = 10;
3. //NEXT_DAY 사용

문제 10) emp 테이블에서 입사한 달의 근무 일수를 계산하여 출력하여라. 단, 일요일과 일요일도 근무 일수에 포함된다.

1. SELECT ename, hiredate, LAST_DAY(hiredate) - hiredate FROM emp;
2. // LAST_DAY 사용

1. create table test (
   id   number,
   name varchar2(10));

1. SELECT * FROM test;

1. INSERT INTO test(id, name) VALUES(1, 10);

1. SELECT ename+100 FROM emp;

1. SELECT id+10, name+100 FROM test;

현재의 세기, 년, 월, 일, 시, 분, 초 정보 및 요일 정보를 확인

1. SELECT sysdate, to_char(sysdate, 'rrrr/mm/dd:hh24:mi:ss:day') FROM dual;

1. SELECT * FROM emp WHERE hiredate > '81/01/01';

1. alter session set nls_date_format='yy/mm/dd';

1. SELECT * FROM emp WHERE hiredate > '81/01/01';  '81/01/01' = 2081/01/01  값 없음!!

1. SELECT ename, sal, to_char(sal, '$9,999.9') FROM emp;

1. SELECT ename, sal, to_char(sal, 'L9,999.9') FROM emp;
2. //L은 로컬

1. SELECT ename, sal, to_char(sal, 'L9,999.9'), TO_CHAR(sal, '999') FROM emp;

1. INSERT into emp(empno, ename, hiredate) VALUES(8000, '홍길동', '10-12-2009:10시');

1. INSERT into emp(empno, ename, hiredate) VALUES(8000, '홍길동', TO_DATE('10-12-2009:10', 'dd-mm-rrrr:hh24') );

1. SELECT ename, deptno, DECODE(deptno, 10, '10번 부서', 20, '20번 부서', 30, '30번 부서', '기타부서') "부서",
2. CASE WHEN deptno = 10  THEN '10번 부서'
3. WHEN deptno = 20  THEN '20번 부서'
4. WHEN deptno = 30  THEN '30번 부서'
5. ELSE '기타부서' end "case문"  FROM emp;

이 글은 스프링노트에서 작성되었습니다.