DB Oracle

[Oracle] 날짜/기타자료형, SELECT

아잠만_ 2024. 3. 8. 17:45

날짜자료형

  • 년, 월, 일, 시, 분, 초의 값을 보관 (년 월 일 / 시 분 초, 따로 저장) 
  • 덧셈과 뺄셈의 대상이 될 수 있음
  • 기본제공 함수로 SYSDATE, SYSTIMESTAMP 가 제공됨
  • DATE, TIMESTAMP(더 정교한 시간)

DATE

기본 날짜형

컬럼명 DATE

사용 예시

CREATE TABLE TEMP06 (
	COL1 DATE,
	COL2 DATE );

INSERT INTO TEMP06 VALUES(SYSDATE, SYSDATE-10);

SELECT * FROM TEMP06;

시 분 초는 출력은 안되지만 저장은 되어있음

형변환 함수(TO_CHAR)를 통해 출력

 SELECT TO_CHAR(COL1,'YYYY-MM-DD HH24:MI:SS')
    FROM TEMP06;

더보기

   * 지정된 날짜 저장하는 법 - ' YYYYMMDD ' 

                                                       형식 지정 문자열

INSERT INTO TEMP06 VALUES('20240308',20240308);
오류 보고 -
SQL 오류: ORA-00932: inconsistent datatypes: expected DATE got NUMBER 00932. 00000 - "inconsistent datatypes: expected %s got %s"                                                                                           숫자→날짜 저장x
*Cause:
*Action:
INSERT INTO TEMP06 VALUES('20240308',SYSDATE+10);

우선 순위 : 숫자 → 날짜 → 문자열

 

   * 지정된 날짜 저장하는 법 - TO_DATE ('날짜시간', 'YYYYMMDDHH24MISS')

INSERT INTO TEMP06 VALUES('20240308',TO_DATE('20200308151650','YYYYMMDDHH24MISS'));

TIMESTAMP

컬럼명 TIMESTAMP                                                   시간대 정보 저장하지 않음
컬럼명 TIMESTAMP WITH LOCAL TIME ZONE       클라이언트가 위치한 시간대정보(TIMEZONE 타입과 동일)
컬럼명 TIMESTAMP WITH TIME ZONE                    서버가 위치한 지역의 시간대 정보 
  • 10억분의 1초 단위의 정교한 시각 정보를 년월일 정보와 함께 저장
  • 시간대(TIMEZONE)정보 저장
  • TIMESTAMP 타입에 저장하는 날짜함수는 SYSTIMESTAMP임

사용 예시

CREATE TABLE TEMP07 (
	COL1 TIMESTAMP,
	COL2 TIMESTAMP WITH LOCAL TIME ZONE,
	COL3 TIMESTAMP WITH TIME ZONE );
    
INSERT INTO TEMP07 VALUES (SYSDATE,SYSDATE,SYSDATE);
INSERT INTO TEMP07 VALUES (SYSTIMESTAMP,SYSTIMESTAMP,SYSTIMESTAMP);
 
SELECT * FROM TEMP07;

기타 자료형

  • 2진 데이터를 저장하기 위한 자료형
  • BLOB, BFILE, RAW, LONG RAW가 지원
  • 2진자료를 저장하면 DBMS는 해당 2진 자료를 변환하거나 해석하지 않음

RAW

컬럼명 RAW (크기)
  • 상대적으로 작은 양의 이진자료 저장
  • 인덱스 처리 가능
  • 최대 2000 byte 저장 가능
  • 16진수(4bit)와 2진수 형태로 저장 

사용예시

CREATE TABLE TEMP08 (
        COL1 RAW(1000),
        COL2 RAW(50) );

INSERT INTO TEMP08 VALUES (HEXTORAW('7DA5'),'0111110110100101');

SELECT * FROM TEMP08;

해석되거나 변환되어 저장되어지지 않음

BFILE

컬럼명 BFILE
  • 이진자료 저장
  • 최대 4GB까지 저장
  • 원본자료를 데이터베이스 밖에 저장하고 데이터베이스에는 파일명과 경로명만 저장
  • 자주 업데이트(변경)되는 이진자료 저장에 유리함

사용 예시

CREATE TABLE TEMP09 (
	COL1 BFILE) ;

   ** BFILE 형태로 그림을 저장하는 절차

   1. 원본파일 (그림파일) 저장

simple.jpg

   2.디렉토리객체생성

CREATE DIRECTORY 절대경로 AS 별칭
CREATE DIRECTORY TEST_DIR AS 'D:\A_TeachingMaterial\02_Oracle\work';

 3. 자료 삽입

INSERT INTO TEMP09 VALUES(BFILENAME('TEST_DIR','sample.jpg'));
SELECT * FROM TEMP09;

BLOB

컬럼명 BLOB
  • 이진자료 저장
  • 최대 4GB까지 저장
  • 원본자료를 데이터베이스 내부에 저장
  • 변경이 일어나지 않은 이진자료 저장에 유리함
  • 데이터의 저장과 변환은 PL/SQL의 문법을 사용해야함

사용 예시

CREATE TABLE TEMP10 (
	COL1 BLOB);

   ** 자료 삽입 - 익명 BLOCK 사용

DECLARE
        L_DIR   VARCHAR2(20):='TEST_DIR';	-- 초기값
        L_FILE  VARCHAR2(30):='sample.jpg';	-- 초기값
        L_BFILE BFILE;
        L_BLOB  BLOB;
BEGIN
        INSERT INTO TEMP10 (COL1) VALUES(EMPTY_BLOB()) RETURN COL1 INTO L_BLOB;
        
        L_BFILE := BFILENAME(L_DIR, L_FILE);
        DBMS_LOB.FILEOPEN(L_BFILE, DBMS_LOB.FILE_READONLY);
        
        DBMS_LOB.LOADFROMFILE(L_BLOB,L_BFILE,DBMS_LOB.GETLENGTH(L_BFILE));
        DBMS_LOB.FILECLOSE(L_BFILE);
        
	COMMIT;
END;

:= : (연산자) 오른쪽에있는 변수를 왼쪽 변수에 저장

SELECT

SELECT문으로 검색 기능 수행

SELECT *|[DISTINCT][컬럼명 [AS 별칭],
         컬럼명 [AS 별칭],
              :
         컬럼명 [AS 별칭]
     FROM 테이블명
   [WHERE 조건]
   [GROUP BY 컬럼명[,컬럼명,...]]
 [HAVING 조건]
  [ORDER BY 컬럼명|컬럼인덱스 [ASC|DESC][,컬럼명|컬럼인덱스 [ASC|DESC],...]]
  • SELECT문은 SELECT절, FROM절, WHERE절,GROUP BY절, HAVING절, ORDER BY절로 구성
  • 필수 절은 SELECT, FROM 절
  • 수행순서 : FROM -> WHERE -> SELECT 

SELECT 절

추출한 컬럼 정의 담당
  • ' * ' :모든 컬럼 조회
  • DISTINCT : 중복행 제거 (대표로 1개)
  • 컬럼명 [AS 별칭] : 해당 컬럼에 별칭을 부여 (컬럼의 이름_참조하기 위해 사용)

      - 별칭에 특수문자(공백 등)나 예약어(명령어 등)를 사용할 때는 반드시 " "로 묶어야함

      - AS는 생략 가능

      - 한 컬럼의 끝은 반드시 ' , '를 사용해야하며 마지막 컬럼 정의에는 ' , ' 생략

FROM 절

사용할 테이블(또는 뷰) 기술
  • QUERY 실행에 테이블이 불필요한 경우 'DUAL' (가상 테이블명)을 사용 (ex. 날짜 SYSDATE를 불러올때)

ORDER BY 절

출력 데이터의 정렬 지정
  • 기술된 '컬럼명'이나 '컬럼의 인덱스(SELECT 절에 기술된 컬럼의 순번)'를 기준으로 오름차순(ASC)이나 내림차순(DESC)으로 정렬 (기본값 : 오름차순)
  • 두 번째 기술되는 컬럼명은 첫 번째 컬럼명으로 정렬을 할 수 없을 때(같은 값) 적용된다.

사용 예시

1) 회원테이블 (MEMBER)에서 회원번호 (MEM_ID), 회원명(MEM_NAME), 직업(MEM_JOB),
    보유 마일리지(MEM_MILEAGE)를 조회하시오.

더보기

조건이 없으므로 WHERE절 x

 SELECT MEM_ID AS 회원번호,
        MEM_NAME AS 회원명,
        MEM_JOB AS 직업,
        MEM_MILEAGE AS "보유 마일리지"
        FROM MEMBER;

공백은 " "

 2) 상품 분류 테이블(LPROD)의 모든 자료를 조회하시오.

더보기 
SELECT * 
	FROM LPROD;

3) 상품테이블(PROD)에서 상품번호(PROD_ID), 상품명(PROD_NAME), 
    매입단가(PROD_COST), 매출단가(PROD_PRICE), 할인판매단가(PROD_SALE)를 조회하시오.

더보기 
 SELECT PROD_ID AS 상품번호,
        PROD_NAME AS 상품명,
        PROD_COST AS 매입단가,
        PROD_PRICE AS 매출단가,
        PROD_SALE AS 할인판매단가
    FROM PROD;

4) 사원테이블(HR.EMPLOYEES)에서 사용하고 있는 부서번호(DEPARTMENT_ID)를 모두 조회하되 
    중복하지 않게 조회하며 순서대로 출력하시오 (작은값 -> 큰값)

더보기 
 SELECT DISTINCT DEPARTMENT_ID AS 부서번호
    FROM HR.EMPLOYEES
    ORDER BY DEPARTMENT_ID DESC;

5) HR계정의 부서테이블(HR.DEPARTMENTS)의 모든 자료를 조회하시오.

더보기 
 SELECT *
    FROM HR.DEPARTMENTS;