Oracle SQL

[SQL] 테이블의 생성과 관리

YoonJuHan 2023. 5. 31. 20:09

데이터베이스 객체

  1. 데이터베이스에서 사용되는 서로 다른 유형의 데이터 구조
  2. 객체의 정의를 생성, 수정, 삭제하기 위해 사용하는 SQL
    1. CREATE, ALTER, DROP 등의 DDL
  3. 객체의 정의 및 생성
    1. 데이터베이스 설계 시 정의
    2. 데이터베이스 개발의 구축 단계에 생성
  4. 데이터베이스 운영단계에서 필요한 객체가 추가되거나 객체의 정의 변경 가능

 

데이터베이스 기본 객체

  1. 테이블(Table) : 데이터를 저장하는 기본 객체
  2. 뷰(View) : 하나 이상의 테이블에 있는 데이터의 부분 집합
  3. 시퀀스(Sequence) : 일련번호 생성기
  4. 인덱스(Index) : 쿼리 성능 향상을 위한 객체
  5. 동의어(Synonym) : 객체에 대한 다른 이름

 

데이터베이스의 두 가지 테이블

  1. 사용자 테이블
    1. y_emp, y_dept와 같이 사용자가 필요로 하는 데이터 저장을 위해 생성하는 테이블
  2. 데이터 딕셔너리
    1. 데이터베이스 자체에 관한 정보를 포함하는 테이블의 모음
    2. 데이터베이스 생성 시 자동으로 생성되어 오라클 서버에 의해 자동으로 유지 관리

 

데이터 딕셔너리의 구조

  1. 기본테이블(Base Table)과 데이터 딕셔너리 뷰로 구성
  2. 기본 테이블
    1. 데이터베이스에 관한 정보가 암호화 되어있고 복잡한 구조
  3. 데이터 딕셔너리 뷰
    1. 데이터베이스 생성과정에서 기본 테이블의 정보를 해독하고 간소화한 뷰 생성
  4. 사용자들은 데이터베이스에 관한 정보를 얻기 위해 데이터 딕셔너리 뷰를 액세스

 

데이터딕셔너리

  1. 관리자 계정인 SYS 사용자 소유
  2. 데이터 딕셔너리에 저장된 정보
    1. 데이터베이스 계정(사용자) 정보
    2. 사용자에게 부여된 권한
    3. 데이터베이스에 포함된 객체 관련 정보
    4. 테이블 제약 조건
    5. 감사 정보 등

 

데이터딕셔너리 뷰

  1. 사용 목적 또는 정보를 제공하는 단위에 따라 접두어를 다르게 사용
    1. USER_ : 현재 사용자가 소유하는 객체에 관한 정보
    2. ALL_ : 현재 사용자가 액세스 할 수 있는 모든 객체에 관한 정보
    3. DBA_ : 데이터베이스의 모든 객체에 관한 정보
    4. V$ : 데이터베이스 서버 성능, 메모리 및 LOCK 등에 대한 동적 성능 정보

 

  • insa 접속에서 테이블관련 데이터 딕셔너리를 조회
SELECT COUNT(*) FROM user_tables;
SELECT COUNT(*) FROM all_tables;
SELECT COUNT(*) FROM dba_tables; -- > 관리자 권한 필요
  • 관리자 접속에서 테이블관련 데이터 딕셔너리를 조회 (더 많은 테이블들이 검색된다.)
SELECT COUNT(*) FROM user_tables;
SELECT COUNT(*) FROM all_tables;
SELECT COUNT(*) FROM dba_tables;
  • 데이터베이스에 있는 모든 데이터 딕셔너리 뷰의 목록과 동적 성능 뷰의 목록을 알 수 있다.
SELECT table_name FROM dictionary;
SELECT name FROM v$fixed_table;

 

테이블 생성

  1. 사용자가 필요한 데이터를 저장하기 위해 생성
    1. CREATE TABLE 문을 실행
    2. 사용자에게 CREATE TABLE 권한이 필요
    3. 데이터가 저장될 저장 영역 필요
    4. 사용자 테이블의 생성 방법
      1. 직접 테이블의 이름과 구조를 정의하는 방법
      2. 기존의 테이블 구조를 복사하는 방법

 

직접 정의하는 테이블

  1. 사용자가 다음을 정의
    1. 테이블의 이름
    2. 저장할 데이터에 대한 열 이름 및 데이터 타입의 정의
    3. 필수 사양 : 테이블 이름과 열 이름, 열의 데이터 유형 및 길이
  2. 구문
CREATE TABLE [schema.]table_이름 (
column1 datatype [DEFAULT 표현식][제약조건],
column2 datatype [DEFAULT 표현식][제약조건],
...
)

 

이름 지정 규칙

  1. 오라클 데이터베이스 객체 이름 지정 표준 규칙
    1.  테이블 이름과 열 이름은 문자로 시작해야 하며 최대 30자까지 가능하다.
    2. 테이블 및 데이터베이스 객체에는 기술적인 이름을 사용하도록 권장한다.
    3. 이름에는 A-Z, a-z, 0-9, _ (밑줄), $, # 문자만 사용할 수 있다. 
    4. 이름은 동일한 오라클 서버 사용자가 소유한 다른 객체의 이름과 중복되지 않아야 한다.
    5. 객체이름과 열 이름은 오라클 예약어가 아니어야 한다. 
    6. 객체이름은 대소문자를 구분하지 않는다. 예를 들어, Y_EMP는 y_EMP 또는 Y_emP 와 동일한 이름으로 취급된다. 
    7. 객체와 열 이름은 데이터 딕셔너리에는 대문자로 저장된다.

 

  • CUSTOMERS 테이블을 생성
CREATE TABLE customers (
cust_id NUMBER(6),
cust_name VARCHAR2(40),
phone_no VARCHAR2(15),
email VARCHAR2(30),
birth_date DATE,
join_date DATE DEFAULT SYSDATE,
cust_level NUMBER(1) DEFAULT 9
);

 

  • DESC 명령으로 CUSTOMERS 테이블의 구조 확인
DESC customers;

 

테이블 복사

  1. 기존 테이블을 복사하여 새 테이블 생성
    1. CREATE TABLE 구문에 열을 정의하는 대신 AS subquery 절을 사용
    2. 해당 서브 쿼리를 기반으로 한 테이블 생성
    3. 서브 쿼리에 의해 반환되는 행 삽입 수행(행 복사 발생)
  2. 구문
CREATE TABLE table
[(column, column...)]
AS subquery;

 

테이블 복사 시 지침 사항

  1. CREATE TABLE 절에 지정된 열 수는 서브 쿼리의 SELECT 목록에 있는 열 수와 동일해야 한다.
  2. CREATE TABLE 절에 열 이름을 지정하지 않는 경우 해당 테이블의 열 이름은 서브 쿼리의 열 이름과 동일하다.
  3. 서브 쿼리의 열 이름에 사용한 별칭은 생성되는 테이블의 열 이름이 된다.
  4. 표현식과 같은 기본 테이블에 존재하지 않는 열은 반드시 별칭을 지정해야 한다.
  5. NOT NULL 이외의 무결성 규칙은 새로운 테이블에 전달되지 않으며 열 데이터 유형 정의만 전달된다.

 

  • 부서 200에서 일하는 사원들에 대한 정보를 포함하는 dept200 테이블을 생성, 이 테이블의 데이터는 y_emp 테이블로부터 복사
CREATE TABLE dept200
AS
SELECT emp_id, emp_name, salary*12 ANNSAL, hiredate
FROM y_emp
WHERE dept_id = 200;

DESC dept200;

SELECT * FROM dept200;

 

데이터 타입의 정의

  1. 숫자 데이터
    1. NUMBER 사용
    2. 다음과 같은 형식으로 정의
      1. NUMBER [(p, s)]
      2. 형식에서 십진 자릿수가 p이고 소수점 이하 자릿수가 s이다.
      3. 유효한 십진 자릿수의 범위는 1부터 38까지 이다.
  2. 문자 데이터 1
    1. CHAR [(size)]
      1. 고정길이 문자 데이터
      2. 최소 1바이트 최대 2000바이트까지 길이로 지정
      3. 괄호 안에 지정하는 size가 고정길이라서 저장되는 문자의 길이에 상관없이 지정된 size의 바이트를 모두 사용한다.
  3. 문자 데이터 2
    1. VARCHAR2 [(size)]
      1. 가변 길이 문자 데이터
      2. 최소 1바이트에서 최대 4000바이트까지 지정
      3. 괄호 안에 지정하는 size가 최대 길이
      4. 저장되는 문자는 최대 길이를 넘지 않는 범위에서 실제 문자의 길이에 해당하는 바이트만 저장공간으로 사용
  4. 날짜 데이터
    1. DATE
      1. 초 단위까지의 날짜와 시간 값을 저장할 수 있는 7바이트의 고정길이 데이터 타입
      2. 형 변환을 하지 않으면 기본적으로 연도, 월, 일 까지만 화면에 표시한다.
    2. 날짜 데이터 연산의 특징
      1. 날짜 데이터와 더하거나 빼기 연산을 하는 정수는 날 수로 해석된다.
      2. 시간, 분, 초 단위의 시간관련 계산을 수행하려면 분수를 사용해야 한다.
    3. 글로벌 환경 고려사항
      1. 클라이언트와 서버가 지리적으로 서로 떨어져 있는 경우 시차를 반영하여 날짜 정보를 조정해서 기록

 

  • y_emp 테이블에서 각 열의 데이터 타입을 확인
DESC y_emp;

 

추가 날짜 데이터

  1. DATETIME
    1. Oracle 9i에 도입
    2. 클라이언트와 서버가 서로 다른 시간대에 있는 경우 유용한 시간대 및 지역 시간대에 관한 기능이 향상
    3. 날짜와 시간 관련 계산을 쉽게 할 수 있는 기능 추가
    4. 종류
      1. TIMESTAMP
      2. TIMESTAMP WITH TIME ZONE
      3. TIMESTAMP WITH LOCAL TIME ZONE
      4. INTERVAL YEAR TO MONTH
      5. INTERVAL DAY TO SECOND

 

TIMESTAMP 관련 타입

  1. 사용자가 데이터를 요청하면 오라클에서 사용자의 지역세션 시간대에 맞게 해당 데이터를 변환하여 반환
    1. TIMESTAMP
    2. TIMESTAMP WITH TIME ZONE
    3. TIMESTAMP WITH LOCAL TIME ZONE
  2. 구문
    1. 정밀도(fractional_seconds_precision)는 선택 사항으로 초 단위의 소수점 이하 부분의 자릿수(0~9)를 지정
    2. WITH TIME ZONE은 시간대 정보를 저장
TIMESTAMP[(fractional_seconds_precision)][WITH TIME ZONE]

 

  • SYSTIMESTAMP 함수와 CURRENT_TIMESTAMP 함수를 사용하여 데이터베이스 서버와 클라이언트의 시간기록을 알아볼 수 있다. CURRENT_TIMESTAMP 함수는 클라이언트 지역의 현재 시간을 반환하는 시스템함수이다.
SELECT systimestamp, current_timestamp FROM dual;

 

  • TIME_ZONE 파라미터 변경을 통해 유저 세션의 시간대를 변경하고 데이터베이스와 클라이언트의 현재 시간 기록을 확인
ALTER SESSION SET time_zone = '-10:00';
SELECT systimestamp, current_timestamp FROM dual;
ALTER SESSION SET time_zone = '+9:00';
SELECT systimestamp, current_timestamp FROM dual;

 

  • TIME_TEST1 테이블 구조를 확인한 후 현재의 날짜를 입력
DESC time_test1;
INSERT INTO time_test1
VALUES(11, SYSDATE, SYSDATE, SYSDATE, SYSDATE);
COMMIT;
SELECT * FROM time_test1;

 

  • 다음과 같이 현재의 시간정보와 TIME ZONE을 이동한 후의 시간정보를 비교
ALTER SESSION SET TIME_ZONE = '-10:00';
SELECT * FROM time_test1;

 

INTERVAL YEAR TO MONTH

  1. MONTH datetime 필드를 사용하여 연도 수 및 개월 수에 해당하는 기간 저장
  2. 특정 datetime 값으로 부터의 연도와 월의 차이를 나타낼 때 사용
  3. 구문
    1. year_precision은 YEAR datetime 필드의 자릿수로서 생략하면 기본값은 2
INTERVAL YEAR [(year_precision)] TO MONTH

 

INTERVAL DAY TO SECOND

  1. 기간을 날짜 수, 시간 수, 분 수, 초 수로 저장
  2. 구문
    1. day_precision은 DAY datetime 필드의 자릿수이며 허용되는 값은 0부터 9까지로 생략하면 기본값은 2
    2. fractional_seconds_precision는 SECOND datetime 필드의 소수점 이하 부분의 자릿수로 허용되는 값은 0부터 9까지 이며 기본값은 6

 

  • time_test2 테이블의 구조를 살펴본다.
DESC time_test2

 

  • time_test2 테이블에 데이터를 입력하고 조회
INSERT INTO time_test2
VALUES(1, INTERVAL '10-6' YEAR TO MONTH, INTERVAL '7 12:00:00' DAY TO SECOND);
COMMIT;
SELECT * FROM time_test2;

 

  • time_test2 테이블을 이용하여 y_emp 테이블에서 사원의 입사일로부터 10년 6개월이 지난 후의 날짜를 알아본다.
SELECT emp_id, hiredate, hiredate+dur1 review_day
FROM y_emp, time_test2;

 

  • time_test2 테이블을 이용하여 현재로부터 7일 12시간 후의 날짜 및 시간을 알아본다.
SELECT SYSTIMESTAMP, SYSTIMESTAMP+dur2
FROM dual, time_test2
WHERE no = 1;

 

기타 데이터 타입

구분 데이터 타입 설명
- RAW 최대 2000 바이트인 원시 이진 데이터
LONG LONG RAW 최대 2GB의 가변길이 원시 이진 데이터
LONG 최대 2GB의 가변길이 문자 데이터
LOB BLOB 최대 4GB의 이진 데이터
CLOB 최대 4GB의 가변길이 문자 데이터
BFILE 최대 4GB의 외부 파일에 저장된 이진 데이터
- ROWID 테이블의 행의 고유주소를 나타내는 64진수

 

LONG 타입 고려사항

  1. 다음과 같은 제한사항으로 Oracle 8 릴리스 이상에서는 사용을 권장하지 않는다.
    1. 서브 쿼리를 사용하여 테이블을 생성한 경우 LONG 열은 복사되지 않는다.
    2. LONG 열은 GROUP BY 또는 ORDER BY 절에 포함시킬 수 없다.
    3. LONG 열은 테이블 당 하나만 사용할 수 있다.
    4. LONG 열에는 제약 조건을 정의할 수 없다.
저작자표시 (새창열림)