IT/Oracle

오라클 - DDL명령과 딕셔너리

노마드오브 2018. 8. 17. 00:47

-- DDL 명령과 딕셔너리


-- CREATE

create table new_table

(no number(3),

name varchar2(10),

birth date);


create table tt02

(no number(3,1) default 0,

name varchar2(10) default 'NO Name',

hiredate date default sysdate);


insert into tt02 (no) values (1);


-- 한글 이름 테이블 생성

create table 한글테이블

(컬럼1 number, 

컬럼2 varchar2(10), 

컬럼3 date);


-- Global Tempory Table(임시테이블) 생성하기

create global temporary table temp01

(no number,

name varchar2(10))

on commit delete rows; -- commit 시에 데이터 삭제


insert into temp01 values (1, 'AAAAA');

select * from temp01; -- 조회되어 나옴

commit;

select * from temp01;  -- commit을 했으므로 임시테이블 데이터 삭제됨


-- 생성되어있는 Temporary Table 조회

select temporary, duration

from user_tables

where table_name='TEMP01';


-- 테이블 복사하기(CTAS)

table table dept3

as

    select * from dept2;


create table dept4

as

    select dcode, dname

    from dept2;


-- 테이블의 구조만 가져오고 데이터는 가져오지않기

create table dept5

    as select * from dept2

    where 1=2;


-- alter 명령어를 통한 컬럼 추가

create table dept6

as

    select dcode, dname

    from dept2

    where dcode in (1000,1001,1002);


select * from dept6;


ALTER TABLE dept6 

ADD ( LOC varchar2(10) );


select * from dept6;


-- 컬럼추가시 기본값 설정

ALTER TABLE dept6 

ADD ( LOC2 varchar2(10) default 'seoul' );


select * from dept6;


--  테이블 컬럼 이름 변경하기

alter table dept6 

rename column loc2 to loc3;


-- 테이블 이름 변경

rename dept6 to dept7;

desc dept7; -- 테이블 구조 확인


-- 컬럼의 데이터 크기 변경

alter table dept7

modify (LOC varchar2(20));


-- 컬럼 삭제

alter table dept7 drop column LOC;


-- TRUNCATE, 테이블의 데이터 전부 삭제, 사용하던 공간 반납

truncate table dept7;


-- drop 명령

drop table dept7;



-- 읽기전용 테이블로 변경하기 (11g부터 추가된 기능)

create table t_readonly

(no number, 

name varchar2(10));


insert into t_readonly

values (1, 'AAA');


commit;


select * from t_readonly;


alter table t_readonly read only; -- 읽기전용으로 변경


-- 읽기전용 테이블에 데이터 입력 시도이므로 오류발생

insert into t_readonly

values (2, 'BBB');


-- 읽기전용 테이블에 컬럼 추가 시도이므로 오류발생

alter table t_readonly

add (tel number default 111);


-- 읽기 전용 테이블을 읽기/쓰기 모드로 변경

alter table t_readonly read write;


-- 읽기 전용 테이블이라고 하더라고 drop 명령어로 테이블 삭제는 가능함

drop table t_readonly


-- 가상 컬럼 테이블 사용 (11g부터 추가된 기능)

create table vt1

(col1 number,

col2 number,

col3 number generated always as (col1 + col2));


insert into vt1 values (1,2,3); -- 오류

insert into vt1 (col1, col2) values (1,2); 


select * from vt1;


update vt1

set col1 = 5;


select * from vt1;


alter table vt1

add (col4 generated always as ((col1*12)+col2));


select * from vt1;


-- 테이블에서 가상 컬럼 내역 조회

select column_name,

        data_type,

        data_default

from user_tab_columns

where table_name = 'VT1'

order by column_id;


-- 조건절을 활용한 가상 컬럼 생성하기

create table sales10

(no number,

pcode char(4),

pdate char(8),

pqty number,

pbungi number(1)

generated always as

(

CASE

    WHEN SUBSTR(pdate,5,2) IN ('01','02','03') THEN 1

    WHEN SUBSTR(pdate,5,2) IN ('04','05','06') THEN 2

    WHEN SUBSTR(pdate,5,2) IN ('07','08','09') THEN 3

    ELSE 4

END ) virtual );


insert into sales10 (no, pcode, pdate, pqty) 

values (1, '100', '20110112', 10);

insert into sales10 (no, pcode, pdate, pqty) 

values (2, '200', '20110505', 20);

insert into sales10 (no, pcode, pdate, pqty) 

values (3, '300', '20110812', 30);

insert into sales10 (no, pcode, pdate, pqty) 

values (4, '400', '20111024', 40);

commit;


select * from sales10;


-- Data Dictionary(데이터 딕셔너리)

create table st_table

(no number);


begin

    for i in 1..1000 loop

        insert into st_table values (i);

    end loop;

    commit;

end;

/

 

select count(*) from st_table;


-- 결과 안나옴

select num_rows, blocks

from user_tables

where table_name = 'ST_TABLE';


-- 딕셔너리를 관리자가 수동으로 업데이트

analyze table st_table compute statistics;


select num_rows, blocks

from user_tables

where table_name = 'ST_TABLE';