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

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

6 장. DML 을배웁니다 1

- SQL 명령어들 DML (Data Manipulation Language) : INSERT( 입력 ), UPDATE( 변경 ), DELETE( 삭제 ), MERGE( 병합 ) DDL (Data Definition Language) : CREATE ( 생성 ), ALTER ( 수정 ), TRUNCATE ( 잘라내기 ),DROP ( 삭제 ) * DCL (Data Control Language) : GRANT ( 권한주기 ), REVOKE ( 권한뺏기 ) * TCL (Transaction Control Language): COMMIT ( 확정 ), ROLLBACK ( 취소 ) * SELECT : 어떤분류에서는 DQL (Data Query Language) 라고하기도합니다. 2

1. INSERT : 데이터입력명령어 1) INSERT 를사용하여단일행입력하기 INSERT INTO table [(column1, column2, )] VALUES (value 1, value 2,.) ; - 사용예 1: Dept2 테이블에아래와같은내용으로새로운부서정보를입력하세요. * 부서번호 : 9000 * 부서명 : 특판 1 팀 * 상위부서 : 영업부 * 지역 : 임시지역 SCOTT>INSERT INTO dept2(dcode, dname, pdept,area ) 2 VALUES (9000, ' 특판 1 팀 ',' 영업부 ',' 임시지역 ') ; SCOTT>INSERT INTO dept2 2 VALUES(9001, ' 특판 2 팀 ',' 영업부 ',' 임시지역 ') ; 3

- 사용예 2: 특정칼럼만입력하기 부서번호와부서명, 상위부서값만아래의값으로입력하세요. * 부서번호 : 9002 * 부서명 : 특판 3 팀 * 상위부서 : 영업부 SCOTT>INSERT INTO dept2(dcode,dname,pdept) 2 VALUES(9002, ' 특판 3 팀 ', ' 영업부 ') ; 4

- 사용예 3: 날짜데이터입력하기 아래정보를 professor 테이블에입력하세요. * 교수번호 : 5001 * 교수이름 : 김설희 * ID : Love_me * POSITION : 정교수 * PAY : 510 * 입사일 : 2011 년 11 월 14 일 <- 이부분을주의깊게보세요. SCOTT>INSERT INTO professor (profno, name, id, position, pay, hiredate) 2 VALUES (5001,' 김설희 ','Love_me',' 정교수 ',510,'2011-11-14'); - 윈도용과유닉스용은날짜포맷이다르므로주의해야함. 5

- 사용예 4: Null 값입력하기 * 자동 NULL 값입력하기데이터를입력할때칼럼에값을안주면자동으로 NULL 값이들어갑니다. * 수동 NULL 값입력하기데이터부분에 NULL 값을적어주면됩니다. 6

2) INSERT 를사용하여여러행입력하기 SCOTT>CREATE TABLE professor2 2 AS SELECT * FROM professor ; 실습을위해 professor2 테이블을생성합니다. SQL> INSERT INTO professor2 2 SELECT * FROM professor ; 이방식은이미생성되어있는테이블에서대량의데이터를복사해올 때아주많이사용하는방법입니다. ITAS 라고부르기도합니다. 7

3) INSERT ALL 을이용한여러테이블에여러행입력하기 - 사용예 1 : 다른테이블에한꺼번에데이터입력하기 SCOTT> INSERT ALL 2 INTO p_01 (no, name) 3 VALUES (1,'AAA') 4 INTO p_02 (no, name) 5 VALUES(2,'BBB') 6 SELECT * FROM dual ; 이예제는 p_01 테이블과 P_02 테이블에각각서로다른데이터를동시에입력하는방법을보여주고있습니다. 8

- 사용예 2 : 다른테이블의데이터를가져와서입력하기 Professor 테이블에서교수번호가 1000 번에서 1999 번까지인교수의번호 와교수이름은 p_01 테이블에입력하고교수번호가 2000 번에서 2999 번까 지인교수의번호와이름은 p_02 테이블에입력하세요. SCOTT>INSERT ALL 2 WHEN profno BETWEEN 1000 AND 1999 THEN 3 INTO p_01 VALUES(profno,name) 4 WHEN profno BETWEEN 2000 AND 2999 THEN 5 INTO p_02 VALUES(profno,name) 6 SELECT profno,name 7 FROM professor ; 9

- 사용예 2 번결과화면 10

- 사용예 3 : 다른테이블에동시에같은데이터입력하기 Professor 테이블에서교수번호가 3000 번에서 3999 번인교수들의교수번호와 이름을 p_01 테이블과 p_02 테이블에동시에입력하세요. SCOTT>INSERT ALL 2 INTO p_01 VALUES (profno,name) 3 INTO p_02 VALUES (profno,name) 4 SELECT profno,name 5 FROM professor 6 WHERE profno BETWEEN 3000 AND 3999 ; 11

2. UPDATE ( 데이터변경하기 ) UPDATE table SET column = value WHERE 조건 ; - 사용예 1: Professor 테이블에서직급이조교수인교수들의 BONUS 를 100 만원으로인상하세요. SCOTT>UPDATE professor 2 SET bonus = 100 3 WHERE position =' 조교수 '; 12

- 사용예 2: Professor 테이블에서차범철교수의직급과동일한직급을가진교수들중현재 급여가 250 만원이안되는교수들의급여를 15% 인상하세요. SCOTT>UPDATE professor 2 SET pay = pay * 1.15 3 WHERE position = ( SELECT position 4 FROM professor 5 WHERE name = ' 차범철 ') 6 AND pay < 250 ; 13

3. DELETE ( 데이터삭제하기 ) DELETE FROM table WHERE 조건 ; - 사용예 : Dept2 테이블에서부서번호 (DCODE) 가 9000 번에서 9100 번사이인매장들을삭제하세요. SCOTT>DELETE FROM dept2 2 WHERE dcode between 9000 and 9100 ; DELETE 는데이터는삭제되나용량은변함이없다는것!!! 14

-Table Reorg 하기 ( DELETE 후용량줄이기 ) SCOTT>CONN / AS SYSDBA ; SYS> CREATE TABLE scott.test01 ( 2 no NUMBER, name VARCHAR2(20), addr VARCHAR2(20)); SYS>BEGIN 2 FOR i IN 1..500000 LOOP 3 INSERT INTO scott.test01 4 VALUES ( i, DBMS_RANDOM.STRING('A',19), 5 DBMS_RANDOM.STRING('Q',19) ); 6 END LOOP; 7 COMMIT; 8 END; 9 / PL/SQL procedure successfully completed. 15

- 테이블크기확인 SYS>SELECT COUNT(*) FROM SCOTT.TEST01; COUNT(*) -------------- 500000 <- 50 만건의데이터가확인됩니다. SYS>ANALYZE TABLE scott.test01 COMPUTE STATISTICS ; Table analyzed. SYS>SELECT SUM(BYTES)/1024/1024 MB 2 FROM DBA_SEGMENTS 3 WHERE OWNER='SCOTT' 4 AND SEGMENT_NAME='TEST01'; MB --------------- 28 <- 테이블크기가 28 MB 로확인됩니다. 16

SYS>SELECT table_name, num_rows, blocks, empty_blocks 2 FROM dba_tables 3 WHERE owner= SCOTT 4 AND table_name='test01'; TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS ------------------ -------------------- ----------- ------------------- TEST01 500000 3520 64 SYS> SELECT COUNT(DISTINCT DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid) 2 DBMS_ROWID.ROWID_RELATIVE_FNO(rowid)) " 실사용블록수 " 3 FROM scott.test01 ; 실사용블록수 ---------------- 3447 실제사용하고있는블록개수입니다. 17

SYS>DELETE FROM SCOTT.TEST01; 500000 rows deleted. SYS>COMMIT; Commit complete. SYS>SELECT COUNT(*) FROM SCOTT.TEST01; COUNT(*) ------------- 0 <- 모든데이터가전부삭제됨이확인됩니다. 18

SYS>SELECT SUM(BYTES)/1024/1024 MB 2 FROM DBA_SEGMENTS 3 WHERE OWNER='SCOTT' 4 AND SEGMENT_NAME='TEST01'; MB ---------- 28 <- 용량은변함없이그대로입니다. SYS>SELECT COUNT(DISTINCT DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid) 2 DBMS_ROWID.ROWID_RELATIVE_FNO(rowid)) " 실사용블록수 " 3 FROM scott.test01 ; 실사용블록수 --------------- 0 DELETE 가발생해서모든데이터가삭제되었지만테이블의용량은전혀변하지않음이확인되었습니다. 19

- DELETE 후용량까지줄이는 Reorg 작업을수행합니다. 1. 위에서생성했던 SCOTT.TEST01 테이블에데이터를추가합니다. SYS>BEGIN 2 FOR i IN 1..1000 LOOP 3 INSERT INTO scott.test01 4 VALUES (i, DBMS_RANDOM.STRING('A',19) 5, DBMS_RANDOM.STRING('B',19) ); 6 END LOOP; 7 COMMIT; 8 END; 9 / PL/SQL procedure successfully completed. 20

2. 데이터건수와테이블용량을측정합니다. SYS>SELECT COUNT(*) FROM SCOTT.TEST01; COUNT(*) ------------ 1000 <- 1000 건이입력되었습니다. SYS>SELECT SUM(BYTES)/1024/1024 MB 2 FROM DBA_SEGMENTS 3 WHERE OWNER='SCOTT' 4 AND SEGMENT_NAME='TEST01'; MB ------------ 28 <- 용량은여전히 28MB 입니다. 21

3. 1000 건의데이터중 300 건만삭제합니다. SYS>DELETE FROM SCOTT.TEST01 2 WHERE no BETWEEN 1 AND 300 ; 300 rows deleted. SYS>COMMIT; Commit complete. SYS>SELECT COUNT(*) FROM SCOTT.TEST01; COUNT(*) ------------ 700 22

SYS>SELECT SUM(BYTES)/1024/1024 MB 2 FROM DBA_SEGMENTS 3 WHERE OWNER='SCOTT' 4 AND SEGMENT_NAME='TEST01'; MB ------------- 28 <- 여전히예상대로 28 MB 입니다. 23

4. 테이블 REORG( 리오그 ) 작업을합니다. SYS>SELECT TABLE_NAME,TABLESPACE_NAME 2 FROM DBA_TABLES 3 WHERE TABLE_NAME='TEST01'; TABLE_NAME TABLESPACE_NAME ---------------- -------------------------- TEST01 USERS <- 현재 USERS 테이블스페이스입니다. SYS>ALTER TABLE SCOTT.TEST01 MOVE TABLESPACE USERS; Table altered. 24

SYS>SELECT SUM(BYTES)/1024/1024 MB 2 FROM DBA_SEGMENTS 3 WHERE OWNER='SCOTT' 4 AND SEGMENT_NAME='TEST01'; MB ------------.0625 <- 용량이현저하게줄어든것이확인됩니다. SYS>SELECT COUNT(*) FROM SCOTT.TEST01 ; COUNT(*) -------------- 700 <- 데이터는 700 건있습니다. 25

4. MERGE ( 테이블합치기 ) SQL> MERGE INTO Table1 2 USING Table2 3 ON ( 병합조건절 ) 4 WHEN MATCHED THEN 5 UPDATE SET 업데이트내용 6 DELETE WHERE 조건 7 WHEN NOT MATCHED THEN 8 INSERT VALUES( 컬럼이름 ) ; 26

- Merge 실습 27

- Merge 전테이블내용확인 SCOTT>SELECT * FROM pt_01 ; 판매번호제품번호 수량 금액 ----------- ------------ ---------- ---------- 12010101 1000 1 500 12010102 1001 1 400 12010103 1003 1 300 SCOTT>SELECT * FROM pt_02 ; 판매번호제품번호 수량 금액 ----------- ------------ ---------- ---------- 12010201 1004 1 600 12010202 1000 1 500 12010203 1005 1 700 SCOTT>SELECT * FROM p_total ; no rows selected 28

- MERGE 작업 QUERY 1 (pt_01 테이블과 p_total 테이블병합 ) SCOTT>MERGE INTO p_total total 2 USING pt_01 p01 3 ON (total. 판매번호 =p01. 판매번호 ) 4 WHEN MATCHED THEN 5 UPDATE SET total. 제품번호 = p01. 제품번호 6 WHEN NOT MATCHED THEN 7 INSERT VALUES(p01. 판매번호, p01. 제품번호, p01. 수량, p01. 금액 ) ; 29

- MERGE 작업 QUERY 2 (pt_02 테이블과 p_total 테이블병합 ) SCOTT>MERGE INTO p_total total 2 USING pt_02 p02 3 ON (total. 판매번호 =p02. 판매번호 ) 4 WHEN MATCHED THEN 5 UPDATE SET total. 제품번호 = p02. 제품번호 6 WHEN NOT MATCHED THEN 7 INSERT VALUES(p02. 판매번호, p02. 제품번호, p02. 수량, p02. 금액 ) ; 30

- Merge 작업완료후결과조회하기 31

5. TRANSACTION 관리하기 초등학교 1 학년 2 학년 3 학년 4 학년 5 학년 6 학년 중학교 1 학년 2 학년 3 학년 대학교 1 학년 2 학년 3 학년 4 학년 - Commit 트랜잭션확정하기 - Rollback 트랜잭션취소하기 32

6. DML ERROR LOGGING 하기 ( 10g R2 부터지원됨 ) 1. DBMS_ERRLOG 패키지를수행해서에러로깅테이블 DML_ERRORS 생성합니다. SCOTT>SELECT * FROM dml_err_test ; NO NAME ---------- ---------- 1 AAA 2 BBB 이테이블은 NO 컬럼에 Primary Key 가설정이되어있습니다. 33

- 에러내용을로깅하기위해 DBMS_ERROR 패키지를수행합니다. SCOTT>BEGIN 2 DBMS_ERRLOG.CREATE_ERROR_LOG ( 3 dml_table_name => 'DML_ERR_TEST', 4 err_log_table_name => 'DML_ERRORS' ); 5 END ; 6 / PL/SQL procedure successfully completed. 34

- 에러가기록되는 DML_ERRORS 테이블을살펴봅니다. SCOTT>DESC dml_errors ; Name Null? Type ------------------------------ --------- ---------------------------- ORA_ERR_NUMBER$ NUMBER ORA_ERR_MESG$ VARCHAR2(2000) ORA_ERR_ROWID$ ROWID ORA_ERR_OPTYP$ VARCHAR2(2) ORA_ERR_TAG$ VARCHAR2(2000) NO VARCHAR2(4000) NAME VARCHAR2(4000) 35

2. DML_ERR_TEST 테이블에에러를발생하는 DML 을수행합니다. SCOTT>INSERT INTO dml_err_test 2 VALUES (1,'CCC') 3 LOG ERRORS INTO dml_errors('insert..rl=unlimited') 4 REJECT LIMIT UNLIMITED ; 0 rows created. 36

3. 에러를확인합니다 ( 관련스크립트는교재를참조하세요 ) SCOTT>exec print_table('select * FROM dml_errors'); ORA_ERR_NUMBER$ : 1 ORA_ERR_MESG$ : ORA-00001: unique constraint (SCOTT.SYS_C0014256) violated ORA_ERR_ROWID$ : ORA_ERR_OPTYP$ : I ORA_ERR_TAG$ : INSERT..RL=UNLIMITED NO : 1 NAME : CCC ----------------- PL/SQL procedure successfully completed. 37