다양한 예제로 쉽게 배우는 오라클 SQL 과 PL/SQL

다양핚예제로쉽게배우는 오라클 SQL 과 PL/SQL 서짂수저

7 장. DDL 과 Data Dictionary 를 배웁니다 1

1. CREATE 1) 사용예 1: 일반테이블생성하기 SCOTT>CREATE TABLE ddl_test 2 ( no NUMBER(3), 3 name VARCHAR2(10), 4 birth DATE DEFAULT SYSDATE ) 5 TABLESPACE users ; 2

2) 사용예 2: 한글로테이블생성하기 SCOTT>CREATE TABLE 핚글테이블 2 ( 컬럼1 number, 3 컬럼2 varchar2(10), 4 컬럼3 date ) ; 3

- 테이블생성시주의사항 1. 테이블이름은반드시문자로시작해야합니다. 즉숫자로시작할수는없고숫자가포함되는것은가능합니다. 특수문자도가능하지만테이블생성시 ( 겹따옴표 ) 로감싸야하며권장하지않습니다. 2. 테이블이름이나컬럼이름은최대 30 bytes 까지가능합니다. 즉한글로테이블이름을생성하실경우최대 15 글자까지만가능하다는뜻입니다. 3. 테이블이름은한명의사용자가다른오브젝트들의이름과중복으로사용할수없습니다. 예를들어 scott 사용자가테이블명을 test 로생성한후인덱스이름을 test 로동일하게사용할수없다는것입니다. 그러나 scott 사용자가 test 테이블만들어도다른사용자인 hr 사용자는 test 라는테이블이름을사용할수있습니다. 4. 테이블이름이나오브젝트이름을오라클이사용하는키워드를사용하지않기를권장합니다. 오라클키워드라함은오라클에서사용하는미리정해진 SELECT, FROM 등과같은단어들을말합니다. 생성이안되는것은아니지만사용시에아주불편하고위험할수도있기에절대로사용하지말기를권장합니다. 4

3) 사용예 3: Temporary Table ( 임시테이블 ) 생성하기 CREATE GLOBAL TEMPORARY TABLE 테이블명 ( 컬럼1 데이터타입, 컬럼2 데이터타입,, ON COMMIT [ delete preserve ] ROWS ; -8.1 버젂부터등장함. - 위문법에서마지막행에 ON COMMIT delete ROWS 를사용하면 COMMIT 시에데이터를삭제핚다는뜻이고 ON COMMIT preserve ROWS 를사용하면세션이종료해야데이터가사라집니다. 기본값은 ON COMMIT delete ROWS 입니다. 5

- 실습 1 : 터미널을 2 개열어서핚쪽에서생성후다른쪽에서조회확인하기 - 터미널 1 SCOTT>CREATE GLOBAL TEMPORARY TABLE temp01 2 ( no number, 3 name varchar2(10)) 4 ON COMMIT DELETE ROWS ; Table created. SCOTT>INSERT INTO temp01 VALUES(1,'AAA'); 1 row created. SCOTT>SELECT * FROM temp01 ; NO NAME ---------- ---------- 1 AAA 6

- 터미널 2 SCOTT>SELECT * FROM temp01 ; no rows selected <- 다른세션에서는조회가안됩니다 7

- 터미널 1 SCOTT>SELECT * FROM temp01 ; NO NAME ---------- ---------- 1 AAA SCOTT>COMMIT ; Commit complete. SCOTT>SELECT * FROM temp01 ; no rows selected <- 데이터가삭제되어서조회가안됩니다. temporary table 생성옵션이 ON COMMIT DELETE ROWS 이므로커밋을하면모두삭제됩니다 8

- 실습 2 : 생성되어있는 Temporary Table 조회하기 SCOTT>SELECT temporary, duration 2 FROM user_tables 3 WHERE table_name='temp01' ; T DURATION - ------------------------ Y SYS$TRANSACTION 9

4) 사용예 4: 테이블복사하기 (CTAS 라고도합니다 ) (1) 모든칼럼다복사하기 SCOTT>CREATE TABLE dept3 2 AS 3 SELECT * FROM dept2; (2) 특정칼럼만복사하기 SCOTT>CREATE TABLE dept4 2 AS 3 SELECT dcode, dname 4 FROM dept2 ; 10

(3) 테이블의구조 ( 칼럼 ) 만가져오고데이터안가져오기 SCOTT>CREATE TABLE dept5 2 AS 3 SELECT * 4 FROM dept2 5 WHERE 1=2 ; 11

(5) 사용예 5: 가상컬럼테이블생성하기 (11g 부터추가된기능 ) - Step 1. 가상컬럼을가지는 vt001 테이블을생성합니다. SCOTT>CREATE TABLE vt001 2 ( no1 number, 3 no2 number, 4 no3 number GENERATED ALWAYS AS (no1 + no2 ) VIRTUAL ) ; 위명령어에서 4 번라인의 no3 컬럼은 no1+no2 의값을가지는가상컬럼입니다. 12

-Step 2. vt001 테이블에데이터를입력합니다. SCOTT>INSERT INTO vt001 VALUES (1,2,3); INSERT INTO vt001 VALUES (1,2,3) * ERROR at line 1: ORA-54013: INSERT operation disallowed on virtual columns SCOTT>INSERT INTO vt001(no1,no2) 2 VALUES(1,2); 1 row created. 위 step 2 의결과로알수있듯이가상컬럼에는사용자가데이터를입력핛수없습니다. 13

- Step 3. 입력된데이터를조회합니다. SCOTT>SELECT * FROM vt001 ; NO1 NO2 NO3 ---------- ---------- ---------- 1 2 3 14

- Step 4. 기존값을변경한후가상칼럼에반영되는지확인합니다. SCOTT>UPDATE vt001 2 SET no1=10 ; 1 row updated. SCOTT>SELECT * FROM vt001 ; NO1 NO2 NO3 ---------- ---------- ---------- 10 2 12 15

- Step 5. 인덱스와제약조건이생성가능한지테스트합니다. SCOTT>INSERT INTO vt001 (no1, no2) 2 VALUES (3,4); 1 row created. SCOTT>INSERT INTO vt001 (no1,no2) 2 VALUES(6,6) ; INSERT INTO vt001 (no1,no2) * ERROR at line 1: ORA-00001: unique constraint (SCOTT.IDX_VT001_NO3) violated 16

- Step 6. 새로운가상칼럼을추가합니다. SCOTT>ALTER TABLE vt001 2 ADD (no4 GENERATED ALWAYS AS ((no1*12)+no2)) ; Table altered. SCOTT>SELECT * FROM vt001 ; NO1 NO2 NO3 NO4 ---------- ---------- ---------- ---------- 10 2 12 122 3 4 7 40 위 Step 6 에서보듯이새로운가상컬럼이추가되면즉시값이반영되어생성됩니다. 17

- Step 7. 테이블에서가상칼럼내역을조회합니다. SCOTT>set line 200 SCOTT>col column_name for a10 SCOTT>col data_type for a10 SCOTT>col data_default for a25 SCOTT> SCOTT>SELECT column_name 2, data_type 3, data_default 4 FROM user_tab_columns 5 WHERE table_name = 'VT001' 6 ORDER BY column_id ; COLUMN_NAME DATA_TYPE DATA_DEFAULT ------------------ ---------------- ------------------------- NO1 NUMBER NO2 NUMBER NO3 NUMBER "NO1"+"NO2" NO4 NUMBER "NO1"*12+"NO2" 18

Step 8. 조건절을활용한가상컬럼생성하기 SCOTT>CREATE TABLE panmae10 2 (no NUMBER, 3 pcode CHAR(4), 4 pdate CHAR(8), 5 pqty NUMBER, 6 pbungi NUMBER(1) 7 GENERATED ALWAYS AS 8 ( 9 CASE 10 WHEN SUBSTR(pdate,5,2) IN ('01','02','03') THEN 1 11 WHEN SUBSTR(pdate,5,2) IN ('04','05','06') THEN 2 12 WHEN SUBSTR(pdate,5,2) IN ('07','08','09') THEN 3 13 ELSE 4 14 END ) virtual ) ; Table created. 19

SCOTT>INSERT INTO panmae10 (no,pcode,pdate,pqty) 2 VALUES(1,'100','20110112',10) ; 1 row created. SCOTT>INSERT INTO panmae10 (no,pcode,pdate,pqty) 2 VALUES(2,'200','20110505',20); 1 row created. SCOTT>INSERT INTO panmae10 (no,pcode,pdate,pqty) 2 VALUES(3,'300','20110812',30); 1 row created. 20

SCOTT>INSERT INTO panmae10 (no,pcode,pdate,pqty) 2 VALUES(4,'400','20111024',40); 1 row created. SCOTT>COMMIT ; Commit complete. SCOTT>SELECT * FROM panmae10 ; NO PCOD PDATE PQTY PBUNGI -------- --------- ------------- ---------- ------------ 1 100 20110112 10 1 2 200 20110505 20 2 3 300 20110812 30 3 4 400 20111024 40 4 21

5) 파티션테이블생성하기 판매자료 PANMAE Table 1 분기 2 분기 3 분기 4 분기 Ts_q1 Ts_q2 Ts_q3 Ts_q4 22

- 오라클버전별로제공하는파티션종류 Oracle Version Oracle 8 Oracle 8i Oracle 9i R1 Oracle 9i R2 지원또는추가된 PARTITION 종류 Range PARTITION Fundmental maintenance operations Static pruning Hash PARTITION, Range-Hash PARTITION Merge PARTITIONs Dynamic pruning PARTITION-wise joins List PARTITION Global index maintenance Range-List PARTITION Past SPLIT Default PARTITION for List 23

- 오라클버전별로제공하는파티션종류 Oracle 10g R1 Oracle 10g R2 Oracle 11g Composite PARTITION Range(-list, -hash) Internal re-architecture Global hash-partitioned indexes Local index maintenance One million partitions Multi-dimensional pruning Resource optimized drop table Range, list hash partitioning Extended composite partitioning - range (-list, -hash, range) - list (-range, -list hash) - interval (-range, -list, -hash) Interval partitioning REF partitioning Virtual column based partitioning Enable infinite partitioning Design possibilities and boost manageability 24

(1) Range PARTITION ( 범위파티셔닝 ) 이파티셔닝은주로특정기준에의해서범위를나눌때사용하는방법입니다. 예를들어판매테이블을파티셔닝하는데판매날짜를기준으로범위를나누는경우나포털사이트의카페테이블을파티셔닝하는데카페번호로범위를나눌경우등에사용됩니다. 단점은각파티션별로데이터가균등하게분포되지않을수도있어서성능이보장이안된다는점입니다. 실습은교재 229 243 페이지를참고하세요 25

(2) HASH PARTITION (8i 버전부터지원 ) 이기법은 Hash 함수가데이터를각테이블스페이스별로균등하게분포시키기때문에데이터가균등하게분포되어성능이향상된다는장점이있습니다. 그러나이기법은데이터를사용자가분산하지않고 Hash 함수가분산시키기때문에데이터의관리등이매우어렵다는단점또핚존재합니다. 실습은교재 243 244 페이지를참고하세요 26

(3) LIST PARTITION ( 목록분할파티션 ) 이기법은파티셔닝핛항목을관리자가직접지정하는방식입니다. 예를들어회사제품중에 A 제품용파티션, B 제품용파티션, C 제품용파티션을각각생성하는방식입니다. 이방식은잘설정핛경우빠른성능을보장핛수있지맊잘못설정될경우오히려성능이저하될수있기에아주주의해서생성해야합니다. 실습은교재 245 250 페이지를참고하세요 27

(4) COMPOSITE PARTITION ( 복합파티션 ) Composite Partition 은위에서언급핚여러가지파티션을복합적으로사용하는것입니다. 예를들어학생이맋을경우학년별로먼저나누고각학년별로다시반으로나누는것처럼파티셔닝을먼저핚후다시세부적으로파티셔닝을다시하는방법입니다. 오라클버젂별로지원하는종류가아래와같습니다. 8i : range hash 9i : range list 추가지원 11g : range range, list range, list list, list hash 추가지원 실습은교재 251 256 페이지를참고하세요 28

(5) Interval PARTITION (11g New Feature) 11g 부터추가된기능으로 Range PARTITION 의확장형입니다. Range PARTITION 에서맊약파티션의범위 ( 핚계 ) 를벖어난데이터가입력이될경우에러가발생하면서입력이되지않는데이런문제는 interval PARTITION 을활용하면오라클이필요한파티션을스스로생성한후데이터를입력하게됩니다. 이때자동으로생성되는파티션들은모두동일핚범위의크기를가지게되며파티션이름은오라클이자동으로지정하게됩니다. 실습은교재 256 263 페이지를참고하세요 29

(6) SYSTEM PARTITION (11g New Feature) System PARTITION 은 PARTITION key 를파티션생성시에지정하지않고데이터를삽입핛때직접지정하는방식입니다. 또핚검색핛때도파티션명을명시해야맊해당파티션에서데이터를찾게되며맊약명시하지않으면젂체파티션에서데이터를읽게되어속도가급격히저하될수있습니다. 앞서살펴보았던어떤파티션도적용핛수없을때사용하기위해제공되는기능입니다. 실습은교재 264 페이지를참고하세요 30

6) 파티션의인덱스 31

- Local Index 와 Global Index Local Index 는다시 Local Prefixed Index 와 Local Non-Prefixed Index 로나누어집니다. Local Prefixed Index 는파티션을나눌때기준이되는컬럼으로인덱스를생성핚것이고 Local Non-Prefixed Index 는파티션생성기준컬럼이외의컬럼으로인덱스를생성핚것입니다. Local Prefixed Index 는 Unique / Non Unique index 모두생성핛수있습니다. 반면 Global Index 는인덱스와파티션의컬럼이나범위가다르게생성됩니다 실습은교재 265 267 페이지를참고하세요 32

2. ALTER 명령 Alter 명령어는맊들어져있는오브젝트를변경하는명령어입니다. 즉테이블같은경우에는컬럼을추가하거나컬럼을삭제하거나컬럼이름이나테이블이름을바꾸는등의작업을핛수있습니다. 이명령어는부하가맋이걸리는명령어이므로사용량이맋은시갂에수행하는것은아주위험하니특히조심하셔야합니다. 33

1) 사용예 1 : 새로운컬럼을추가하기 SCOTT>CREATE TABLE dept6 2 AS 3 SELECT dcode, dname 4 FROM dept2 5 WHERE dcode IN(1000,1001,1002) ; Table created. SCOTT>SELECT * FROM dept6; DCODE DNAME -------- -------------------- 1000 경영지원부 1001 재무관리팀 1002 총무팀 SCOTT>ALTER TABLE dept6 2 ADD ( LOC VARCHAR2(10) ); Table altered. SCOTT>SELECT * FROM dept6; DCODE DNAME LOC ------ -------------------- ---------- 1000 경영지원부 1001 재무관리팀 1002 총무팀 34

- 기본값지정하여추가하기 SCOTT>ALTER TABLE dept6 2 ADD ( LOC2 varchar2(10) DEFAULT ' 서울 ' ) ; SCOTT>SELECT * FROM dept6; DCODE DNAME LOC LOC2 -------- -------------------- ---------- ----------- 1000 경영지원부 서울 1001 재무관리팀 서울 1002 총무팀 서울 35

2) 사용예 2: 테이블의칼럼이름변경하기 SCOTT> ALTER TABLE dept6 RENAME COLUMN LOC2 TO AREA ; SCOTT> RENAME dept6 TO dept7 ; -- 테이블이름변경하기 36

3) 사용예 3: 칼럼의데이터크기를변경하기 SCOTT>DESC dept7; Name Null? Type -------------------- ------------- ------------------------ DCODE VARCHAR2(6) DNAME NOT NULL VARCHAR2(20) LOC VARCHAR2(10) AREA VARCHAR2(10) SCOTT>ALTER TABLE dept7 2 MODIFY(dcode VARCHAR2(10)) ; Table altered. SCOTT>DESC dept7; Name Null? Type ----------------------- -------------- ------------------------- DCODE VARCHAR2(10) DNAME NOT NULL VARCHAR2(20) LOC VARCHAR2(10) AREA VARCHAR2(10) 37

4) 사용예 4 : 칼럼삭제하기 SCOTT>ALTER TABLE dept7 DROP COLUMN loc ; SCOTT>ALTER TABLE dept7 DROP COLUMN loc CASCADE CONSTRAINTS ; 38

5) 읽기전용테이블로변경하기 - 11g New Feature SCOTT>CREATE TABLE t_read 2 ( no NUMBER, 3 name VARCHAR2(10) ); Table created. SCOTT>INSERT INTO t_read 2 VALUES (1,'AAA'); 1 row created. SCOTT>COMMIT ; Commit complete. 39

SCOTT>SELECT * FROM t_read ; NO NAME ---------- ---------- 1 AAA SCOTT>ALTER TABLE t_read read only ; <- 읽기전용으로변경합니다. Table altered. - 읽기전용으로변경된테이블에데이터입력시도함. SCOTT>INSERT INTO t_read 2 VALUES (2,'BBB') ; INSERT INTO t_read * ERROR at line 1: ORA-12081: update operation not allowed on table "SCOTT"."T_READ 40

- 읽기전용으로변경된테이블에컬럼추가시도함 SCOTT>ALTER TABLE t_read 2 ADD (tel number default 111) ; ALTER TABLE t_read * ERROR at line 1: ORA-12081: update operation not allowed on table "SCOTT"."T_READ" - 읽기전용인테이블삭제시도함 SCOTT>DROP TABLE t_read ; Table dropped. SQL> ALTER TABLE t_read read write ; -- 읽기쓰기모드로변경함 41

3. TRUNCATE 명령 SCOTT>TRUNCATE TABLE dept7 ; 4. DROP 명령 SCOTT>DROP TABLE dept7 ; 오라클 10g 부터는위명령어로테이블을삭제핛경우테이블이삭제되는것이아니라마치윈도에서파일삭제시휴지통으로가는것처럼휴지통으로보내지게됩니다. 그리고삭제된테이블의이름은 BIN$... 로변경됩니다. 이기능은 10g 부터등장핚 FLASHBACK 의휴지통이라는기능때문입니다 42

5. DELETE, TRUNCATE, DROP 명령어의차이점 43

6. 데이터딕셔너리 ( Dictionary ) - 데이터딕셔너리에저장되어있는주요내용들 * 오라클데이터베이스의메모리구조와파일에대핚구조정보들 * 각오브젝트들이사용하고있는공갂들의정보들 * 제약조건정보들 * 사용자에대핚정보들 * 권핚이나프로파일, 롤에대핚정보들 * 감사 (Audit) 에대핚정보들 -Base Table 과 Data Dictionary View 로이원화되어있음. - DBA_, ALL_ USER_ 로구분되어있음. - Static Dictionary 와 Dynamic Performance View 로나눌수있음. 실습은교재 277 278 페이지참고하세요 44