201016 Oracle

숫자함수 

 

메모리구조가 스택(버켓)구조로 되어있음 

 

서로떨어져있는걸 마치 한 배열에 있는것처럼..제한된 선형구조.. LINKED LIST (꽁무니에 다음뒤에 따라올놈의 주소를갖고다녀)

 

4. WIDTH_BUCKET

- 수치를 일정한 구간으로 구분하여 주어진 자료가 어느 구간에 포함되어있는지를 판별하는 함수

- 사용자가 제시한 수치자료가 사용자가 정의한 구간(상한,하한구간의갯수정의)중 어느곳에 속하는지 구간의 INDEX를 되돌려줌

 

*사용형식

WIDTH_BUCKET(col,min,max,b)

- col:원본자료 또는 그 값을 저장하고 있는 컬럼명

- min:하한값

- max:상한값

- b : 구간의 갯수

 

EX

회원테이블에서 마일리지를 0에서 9000사이의 범위를

9개의 구간으로 나누고 각 회원이 보유한 마일리지가 어느 구간에 속하는지 조회하시오

 

Alias는 회원번호, 회원명, 마일리지, 구간값

 

그냥 MIN을 0으로 MAX는 9로 하면 적은 마일리지가 1등급이 됨

그걸 반대로 입력해주면 가장 많은 마일리지를 가진사람이 1등급이됨

(MAX를0 MIN을 9000으로)

즉 뒤바꾸면 DESC의 효과를 얻을 수 있다.

SELECT  MEM_ID 회원번호, 
        MEM_NAME 회원명, 
        MEM_MILEAGE 마일리지, 
        WIDTH_BUCKET(MEM_MILEAGE,9000,0,9)||'등급' 구간값
        FROM MEMBER;

날짜함수

1. SYSDATE (매개변수가없다.)

 - 시스템이 제공하는 날짜 및 시각정보를 반환

 - 반환타입이 날짜임 (DATE타입)

 - +와 - 연산의 대상이 됨 (반환값도 날짜임. 일수로 계산하는것. 윤년평년같이계산되는거임)

 

2. ADD_MONTHS

 - 월이 더해짐

 - 마지막 로그인 후 3개월이 지난 사람 가려낼때 ... 활용

 

*사용형식

ADD_MONTHS( d, n)

- 날짜데이터 'd'에 'n'개월을 더한 날짜 반환

SELECT ADD_MONTHS(SYSDATE,8)-14 AS 수료날
FROM DUAL;

-14가 왜 가능?? ADD_MONTHS의 결과값은 DATE타입이니까

 

3. NEXT_DAY

 - 정의된 날짜 이후 가장 먼저 만나는 제시된 요일의 날짜반환

 

*사용형식

NEXT_DAY(d, c)

- d : 날짜 자료나 날짜 정보를 저장하고 있는 컬럼명

- c : 요일 ('일요일','월요일',....)

 

EX

2010년 10월 8일 이후에 처음만나는 화요일의 날짜와 오늘날짜(201016)부터 가장 가짜운 금요일

SELECT 	NEXT_DAY(TO_DATE('20101008'),'화요일'),
 		NEXT_DAY(SYSDATE,'금')
FROM 	DUAL;

4. EXTRACT

 - 날짜에서 필요한 요소 (년, 월, 일 등)을 추출하여 반환

 

*사용형식

EXTRACT(fmt FROM d)

 

- 'fmt' : YEAR, MONTH, DAY, HOUR, MINUTE, SECOND

- 반환 값은 숫자타입

 

EX

사원테이블에서 80번 부서에 속한 사원들의 근속년수를 조회하시오

근속년수가 많은 사원부터 출력 

 

HIRE_DATE가 DATE 타입이어야함

SELECT  EMPLOYEE_ID 사원번호,
        EMP_NAME 사원명,
        HIRE_DATE 입사일,
        DEPARTMENT_ID 부서번호,
        EXTRACT(YEAR FROM SYSDATE)-EXTRACT(YEAR FROM HIRE_DATE)근속년수
FROM    EMPLOYEES
WHERE   DEPARTMENT_ID=80
ORDER BY 4 DESC;

ORDER BY 할때 너무 기니까 SELECT문의 컬럼 순서를 써줘도됨

 

EX 회원테입르에서 50대 여성회원정보를 조회하시오 (마일리지순)

Alias는 회원번호 이름 주민번호 나이 마일리지

SELECT  MEM_ID 회원번호,
        MEM_NAME 이름, 
        MEM_REGNO1||'-'||MEM_REGNO2 주민번호, 
        EXTRACT(YEAR FROM SYSDATE)-SUBSTR(MEM_BIR,1,4) 나이,
        MEM_MILEAGE 마일리지
FROM    MEMBER
WHERE   (SUBSTR(MEM_REGNO2,1,1)=2 OR SUBSTR(MEM_REGNO2,1,1)=4) AND 
        EXTRACT(YEAR FROM SYSDATE)-SUBSTR(MEM_BIR,1,4) BETWEEN 50 AND 59
ORDER BY MEM_MILEAGE DESC;

 

-50대는 비트윈으로 짧게 만들어주기

-EXTRACT의 FROM 다음에 나오는 데이터타입은 DATE여야한다!!! 내 오답은 DATE 타입이 아닌 REGNO1를 쓴거였음

 

 

사원테이블에서 부서번호 50번 부서에 속한 사원 중 급여를 가장 많이 받는 상위 5명을 조회하시오

사원번호, 사원명, 입사일, 급여

 

내림차순해서, 5명을 어케봅느냐?

ROWNUM 의사컬럼 PSEUDO 사용

여기보면 맨 왼쪽에 컬럼번호에 행번호가 붙을걸 볼수있따.

이게 ROWNUM

 

PSEUDO는 가상의 컬럼이라는 뜻 가상으로 부여된 번호

실행되어지는 순서대로 작성된 코드를 수도익스트렉션이라고 함 (EX ",프린트의 매개변수 헬로우월드..이런식으로")

 

 

 

정렬하고 5개를 자른게 아니고 5자르고 정렬을 해서 값이 제대로 안나왔음

SELECT  EMPLOYEE_ID 사원번호, 
        EMP_NAME 사원명, 
        HIRE_DATE 입사일, 
        SALARY 급여
FROM    EMPLOYEES
WHERE   DEPARTMENT_ID=50 
        AND ROWNUM<=5
        ORDER BY SALARY DESC;

ORDERBY 보다 WHERE절이 먼저 실행되니까 이런 오류가 생기는 거임

더 높은애들도 있는데...

해결방법? 없음 그냥 서브쿼리만들어서 해결해야함

 

SELECT  A.SID 사원번호, 
        A.SNAME 사원명, 
        A.SDATE 입사일, 
        A.SSAL 급여
FROM    (SELECT EMPLOYEE_ID SID,
                EMP_NAME SNAME,
                HIRE_DATE SDATE,
                SALARY SSAL
        FROM EMPLOYEES
        WHERE DEPARTMENT_ID=50
        ORDER BY SALARY DESC) A
WHERE   ROWNUM <=5;

50번 부서에 속한 사원들의 급여많이 받는 순으로가 FROM에서 서브쿼리로 처리를 해. (FROM절이 1빠따)

이걸 뷰라고 한다. 이 뷰의 이름은 A

 

FROM절엔 테이블만 올수있다고 했음. 즉 VIEW는 테이블이다! (가상의 테이블인거임)