문제1) emp 테이블에서
현재 날짜, 입사일,
scott 사원이 입사한 날부터 현재까지 근무한 날수를 출력
SQL> SELECT SYSDATE, HIREDATE, SYSDATE-HIREDATE
2 FROM EMP * DUAL은 조건절을 사용할수 없다.??? 확인요망.
3 WHERE ENAME='SCOTT'
4 ;
SYSDATE HIREDATE SYSDATE-HIREDATE
-------- -------- ----------------
07/03/28 87/04/19 7283.60706
SQL>SELECT SYSDATE, HIREDATE, MONTHS_BETWEEN(SYSDATE,HIREDATE)
2 FROM EMP
3 WHERE ENAME='SCOTT'
4 ;
SYSDATE HIREDATE MONTHS_BETWEEN(SYSDATE,HIREDATE)
-------- -------- --------------------------------
07/03/28 87/04/19 239.309944
문제2) 시간을 보고 싶다.
SQL> ALTER SESSION SET NLS_DATE_FORMAT='YY/MM/DD HH:MI:SS'
2 ;
세션이 변경되었습니다.
SQL> SELECT SYSDATE FROM DUAL;
SYSDATE
-----------------
07/03/28 02:42:17
문제3) 시간을 24시간으로 보고 싶다.
SQL> ALTER SESSION SET NLS_DATE_FORMAT='YYYY/MM/DD HH24:MI:SS'
2 ;
세션이 변경되었습니다.
SQL> SELECT SYSDATE FROM DUAL;
SYSDATE
-------------------
2007/03/28 14:44:35
31PAGE
예제1) 오전, 오후를 표기하는 방법
SQL> ALTER SESSION SET NLS_DATE_FORMAT='YY/MM/DD HH:MI:SS AM';
세션이 변경되었습니다.
SQL> SELECT SYSDATE FROM DUAL;
SYSDATE
----------------------
07/03/28 03:00:10 오후
예제2) 달과 서기 추가
SQL> ALTER SESSION SET NLS_DATE_FORMAT='YY/MON/DD HH:MI:SS BC';
세션이 변경되었습니다.
SQL> SELECT SYSDATE FROM DUAL;
SYSDATE
-------------------------
07/MAR/28 03:01:30 서기
예제3)
SQL> ALTER SESSION SET NLS_DATE_FORMAT='YY/MONTH/DD HH:MI:SS BC';
세션이 변경되었습니다.
SQL> SELECT SYSDATE FROM DUAL;
SYSDATE
-------------------------------
07/MARCH /28 03:03:15 서기
* 일시적이다. 다른 창을 불러 왔을 때 적용 안된다.
예제1) ROUND(날짜,'DD') / ROUND(날짜,'MM' /ROUND(날짜,'YY')을 이용
SQL> SELECT SYSDATE, ROUND(SYSDATE,'DD') FROM DUAL;
SYSDATE ROUND(SY
-------- --------
07/03/28 07/03/29
* 시간을 기준으로 바뀐다. 현제가 오후이기 때문에 다음날로 바뀐다.
SQL> SELECT SYSDATE, ROUND(SYSDATE,'DD'), ROUND(SYSDATE,'MM'), ROUND(SYSDATE,'YY') FROM DUAL;
SYSDATE ROUND(SY ROUND(SY ROUND(SY
-------- -------- -------- --------
07/03/28 07/03/29 07/04/01 07/01/01
SQL>
* ROUND : 15일 이상이면 다음달 1일
* ROUND : 6월 이상이면 다음년 1일
* TRUNC :날-> 그날자정 / 월 -> 그달 1일 / 년 -> 금년 1월1일
예제1) TRUNC 사용
SQL> SELECT TRUNC(SYSDATE,'DD'), TRUNC(SYSDATE,'MM'), TRUNC(SYSDATE,'YY') FROM DUAL;
TRUNC(SY TRUNC(SY TRUNC(SY
-------- -------- --------
07/03/28 07/03/01 07/01/01
* ROUND,TRUNC 보험 가입한 날짜 4시간이후 적용됩니다. 일괄적으로 절삭이나 반올림 할 때 사용
데이터 타입의 변환
명시적 데이터 타입의 변환
* 일시적으로 날짜 타입을 문자 형태로 변형 ->변환함수
TO_CHAR(날짜,'~~~;)
예제1)
SQL> SELECT SYSDATE, TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS')
2 FROM DUAL
3 ;
SYSDATE TO_CHAR(SYSDATE,'YY
-------- -------------------
07/03/28 2007/03/28 15:25:22
예제2)
SQL>SELECT SYSDATE, TO_CHAR(ROUND(SYSDATE),'YYYY/MM/DD HH24:MI:SS'), TO_CHAR(TRUNC(SYSDATE), 'YYYY/MM/DD HH24:MI:SS')
2* FROM DUAL
3 ;
SYSDATE TO_CHAR(ROUND(SYSDA TO_CHAR(TRUNC(SYSDA
-------- ------------------- -------------------
07/03/28 2007/03/29 00:00:00 2007/03/28 00:00:00
SQL>
* 함수 안에 함수가 존재하면 가장 안에 있는 함수 부터 적용된다.
문제1) SCOOT 사원의 입사일을
반올림하여 출력하시오
SQL>
SELECT HIREDATE, ROUND(HIREDATE,'MM')
2 FROM EMP
3* WHERE ENAME='SCOTT'
4 ;
HIREDATE ROUND(HI
-------- --------
87/04/19 87/05/01
예제1)
SQL> SELECT HIREDATE, ROUND(HIREDATE,'MM'), TO_CHAR(ROUND(HIREDATE,'MM'),'YYYY/MM/DD HH24:MI:SS CC')
2 FROM EMP
3* WHERE ENAME='SCOTT'
4 ;
HIREDATE ROUND(HI TO_CHAR(ROUND(HIREDATE
-------- -------- ----------------------
87/04/19 87/05/01 1987/05/01 00:00:00 20
묵시적인 데이터 타입 변환
문법)
WHERE A=B
A컬럼의 데이터 다입이 문자=숫자 - > 문자 타입이 NUMBER 형변환
유형부터 확인해야 한다.
문제1) WHERE NUMBER 타입의 칼럼 = 문자 타입의 상수값
10 번 부서의 * 조건
사원이름, 부서 번호 출력 * 출력
* 묵시적 방법
SQL> SELECT ENAME, DEPTNO
2 FROM EMP
3 WHERE DEPTNO='10' * 숫자 이기 때문에 '' 가 없어야 되지만 묵시적으로 변형이 된것이다.
4 ;
ENAME DEPTNO
---------- ----------
CLARK 10
KING 10
MILLER 10
* 명시적 방법
SQL> SELECT ENAME, DEPTNO
2 FROM EMP
3 WHERE DEPTNO=TO_NUMBER('10')
4 ;
ENAME DEPTNO
---------- ----------
CLARK 10
KING 10
MILLER 10
*형 변환 함수들
NUMBER CHAR DATE
<- TO_NUMBER
-> TO_DATE
->TO_CHAR
<- TO_CHAR
문제2) 사원명, 업무, 급여, 급여 *12 연봉도 출력
급여을 $와 , 를 이용하여 변환
SQL>SELECT ENAME, JOB, SAL,TO_CHAR(SAL,'$99,999'), SAL*12 "연봉", TO_CHAR(SAL*12,'$99,999')
2* FROM EMP
3 ;
ENAME JOB SAL TO_CHAR( 연봉 TO_CHAR(
---------- --------- ---------- -------- ---------- --------
SMITH CLERK 800 $800 9600 $9,600
ALLEN SALESMAN 1600 $1,600 19200 $19,200
WARD SALESMAN 1250 $1,250 15000 $15,000
JONES MANAGER 2975 $2,975 35700 $35,700
MARTIN SALESMAN 1250 $1,250 15000 $15,000
BLAKE MANAGER 2850 $2,850 34200 $34,200
CLARK MANAGER 2450 $2,450 29400 $29,400
SCOTT ANALYST 3000 $3,000 36000 $36,000
KING PRESIDENT 5000 $5,000 60000 $60,000
TURNER SALESMAN 1500 $1,500 18000 $18,000
ADAMS CLERK 1100 $1,100 13200 $13,200
JAMES CLERK 950 $950 11400 $11,400
FORD ANALYST 3000 $3,000 36000 $36,000
MILLER CLERK 1300 $1,300 15600 $15,600
14 개의 행이 선택되었습니다.
* # 표시는 데이터 값이 표시 값보다 많을때 표시 된다.
TO_DATE : 문자를 날짜 타입으로 변환(문자열을 DATE 타입의 컬럼에 값을 입력)
예제1)
SQL> SELECT TO_DATE('21-JUL-07','DD-MM-YY') FROM DUAL;
TO_DATE(
--------
07/07/21
NVL함수
문제1) 사원명, 급여, 커미션, 커미션 NULL이면 0
SQL> SELECT ENAME, SAL, COMM, NVL(COMM,0)
2 FROM EMP
3 WHERE DEPTNO=10
4 ;
ENAME SAL COMM NVL(COMM,0)
---------- ---------- ---------- -----------
CLARK 2450 0
KING 5000 0
MILLER 1300 0
NVL2 함수
문법)
NVL2(표현식1, 표현식2, 표현식3)
첫번째 인수값이 NULL 아니면 두번째 인수값이 출력
NULL 이면 세번째 인수값이 출력
문제1) 사원명, 업무, 급여, 커미션
커미션이 널이 아니면 SAL+COMM
널이면 SAL 출력
SQL> SELECT ENAME, JOB, SAL, COMM, NVL2(COMM, SAL+COMM, SAL)
2 FROM EMP
3 ;
ENAME JOB SAL COMM NVL2(COMM,SAL+COMM,SAL)
---------- --------- ---------- ---------- -----------------------
SMITH CLERK 800 800
ALLEN SALESMAN 1600 300 1900
WARD SALESMAN 1250 500 1750
JONES MANAGER 2975 2975
MARTIN SALESMAN 1250 1400 2650
BLAKE MANAGER 2850 2850
CLARK MANAGER 2450 2450
SCOTT ANALYST 3000 3000
KING PRESIDENT 5000 5000
TURNER SALESMAN 1500 0 1500
ADAMS CLERK 1100 1100
JAMES CLERK 950 950
FORD ANALYST 3000 3000
MILLER CLERK 1300 1300
14 개의 행이 선택되었습니다.
-- 한줄 주석
/* 한줄 이상의 내용 */
COALESCE(표현식1,표현식2,.......표현식N)
첫번째 인수가 NULL이 아니면 첫번째 인수값을
NULL이면 두번째 인수값을
두번째 인수도 NULL이면 세번째 인수값을
세번째 인수도 NULL이면 네번재 인수값을
... NULL이 아닐때 까지 뒤의 값을 인식 하며 NULL 이면 N번째 인수값을 출력
첫번째 와 두번째가 NULL이면 N번째 인수값을 출력
문제1) 사원면, 커미션, 급여
커미션이 NULL이 아니면 커미션을
NULL이면 급여를 출력
SQL> SELECT ENAME, COMM, SAL, COALESCE(COMM,SAL)
2 FROM EMP
3 ;
ENAME COMM SAL COALESCE(COMM,SAL)
---------- ---------- ---------- ------------------
SMITH 800 800
ALLEN 300 1600 300
WARD 500 1250 500
JONES 2975 2975
MARTIN 1400 1250 1400
BLAKE 2850 2850
CLARK 2450 2450
SCOTT 3000 3000
KING 5000 5000
TURNER 0 1500 0
ADAMS 1100 1100
JAMES 950 950
FORD 3000 3000
MILLER 1300 1300
14 개의 행이 선택되었습니다.
예제1) DATE 입력
INSERT INTO EMP(EMPNO, ENAME, COMM, SAL)
VALUES(777,'김진철',NULL,NULL); * DATE 입력
SAL가 NULL 사원을 검색
SQL> SELECT EMPNO, ENAME, COMM, SAL
2 FROM EMP
3 WHERE SAL IS NULL;
EMPNO ENAME COMM SAL
---------- ---------- ---------- ----------
777 김진철
문제3) 커미션이 확정되었으면 커미션을
미확정되었으면 급여를
급여도 미확정 되었으면 0을 출력하여
사원면, 커미션, 급여와 더불어 출력하시오
SQL> SELECT ENAME, COMM, SAL, COALESCE(COMM,SAL,0)
2 FROM EMP
3 ;
ENAME COMM SAL COALESCE(COMM,SAL,0)
---------- ---------- ---------- --------------------
SMITH 800 800
ALLEN 300 1600 300
WARD 500 1250 500
JONES 2975 2975
MARTIN 1400 1250 1400
BLAKE 2850 2850
CLARK 2450 2450
SCOTT 3000 3000
KING 5000 5000
TURNER 0 1500 0
ADAMS 1100 1100
JAMES 950 950
FORD 3000 3000
MILLER 1300 1300
김진철 0
15 개의 행이 선택되었습니다.
NULLIF(표현식1, 표현식2)
두개의 표현식값이 동일하면 NULL,
불일치 하면 첫번째 인수값이 출력
문제1) 사원번호, 사원명, 커미션 출력
커미션이 0 이면 NULL로 표현
SQL> SELECT EMPNO, ENAME, COMM, NULLIF(COMM,0)
2 FROM EMP
3 ;
EMPNO ENAME COMM NULLIF(COMM,0)
---------- ---------- ---------- --------------
7369 SMITH
7499 ALLEN 300 300
7521 WARD 500 500
7566 JONES
7654 MARTIN 1400 1400
7698 BLAKE
7782 CLARK
7788 SCOTT
7839 KING
7844 TURNER 0
7876 ADAMS
7900 JAMES
7902 FORD
7934 MILLER
14 개의 행이 선택되었습니다.
* 오늘 가장 중요한 함수 변환 함수.묵시적이 아닌 명시적 변환 중요
날짜 출력 형식의 종류 참고, 숫자를 문자 형식으로 변환 참고
