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) 이라고도 한다.
1. INSERT
테이블에 데이터를 입력. 숫자 값 이외에는 '(홑따옴표)로 감싸야 한다.
(1) INSERT 를 사용하여 단일 행 입력하기
INSERT INTO dept2 (dcode, dname, pdept, area)
VALUES (9000, '특판1팀', '영업부', '임시지역') ;
모든 컬럼에 데이터를 넣을 경우에는 테이블 이름 뒤 컬럼 이름 생략가능
INSERT INTO dept2
VALUES (9000, '특판1팀', '영업부', '임시지역') ;
특정 컬럼에만 데이터를 입력할 경우 반드시 컬럼명 입력
INSERT INTO dept2 (dcode, dname, pdept)
VALUES (9000, '특판1팀', '영업부') ;
INSERT INTO professor (profno, name, id, position, pay, hiredate)
VALUES (5001, '김설희', 'Love_me', '정교수', 510, '2013-11-11' ) ;
날짜 형식은 유닉스용 오라클(DD-MON-YY)과 윈도용 오라클(YYYY-MM-DD)이 다르다.
유닉스(리눅스)에서는 날짜 형식을 미리 변경 후 입력하던지 날짜부분에 TO_DATE 함수를 사용하여
TO_DATE('2013-11-11', 'YYYY-MM-DD') 형식으로 입력해야 한다.
날짜 형식 변경 방법
ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD:HH24:MI:SS' ;
데이터를 입력할 때 컬럼에 값을 안주면 자동으로 NULL 이 들어간다. NULL을 적어 주어도 NULL이 입력된다.
(2) INSERT 를 사용하여 여러 행 입력하기
실습을 위해 테이블 생성
CREATE TABLE professor2
AS SELECT * FROM professor ;
INSERT INTO professor2
SELECT * FROM professor ;
(3) INSERT ALL 을 이용하여 여러 테이블에 여러 행 입력하기
다른 테이블에 한꺼번에 데이터 입력하기
INSERT ALL
INTO p_01 (no, name)
VALUES (1, 'AAA')
INTO p_02 (no, name)
VALUES (2, 'BBB')
SELECT * FROM dual ;
다른 테이블의 데이터를 가져와서 입력하기
INSERT ALL
WHEN profno BETWEEN 1000 AND 1999
THEN
INTO p_01 VALUES (profno, name)
WHEN profno BETWEEN 2000 AND 2999
THEN
INTO p_02 VALUES (profno, name)
SELECT profno, name
FROM professor ;
다른 테이블에 동시에 같은 데이터 입력하기
INSERT ALL
INTO p_01 VALUES (profno, name)
INTO p_02 VALUES (profno, name)
SELECT profno, name
FROM professor
WHERE profno BETWEEN 3000 AND 3999 ;
2. UPDATE
기존 데이터를 다른 데이터로 변경, WHERE 절 누락 시키지 않도록 주의해야 함.
UPDATE professor
SET bonus = 100
WHERE position = '조교수' ;
UPDATE professor
SET pay = pay * 1.15
WHERE position = (
SELECT position
FROM profossor
WHERE name = '차범철'
)
AND pay < 250 ;
3. DELETE
데이터를 삭제하는 구문
DELETE
FROM dept2
WHERE dcode BETWEEN 9000 AND 9100 ;
DELETE문은 해당 데이터가 사용하고 있던 파일의 저장공간(extent)은 반납하지 않고 데이터만 삭제하는 구문.
데이터는 삭제되지 않고 해당 블록에 그대로 남아 있으며 특별한 툴(BBED 등) 을 이용하면 DELETE된 데이터도 전부 복구 할 수 있다.
여기서 중요한 건 데이터가 DELETE 되더라도 저장공간을 반납하지 않기 때문에 용량이 줄어들지 않는다는 것이다.
테스트
CONN / AS SYSDBA ;
CREATE TABLE scott.test01 ( no NUMBER, name, VARCHAR2(20), addr VARCHAR2(20) ) ;
BEGIN
FOR i IN 1..500000 LOOP
INSERT INTO scott.test01
VALUES (i, DBMS_RANDOM.STRING ('A', 19),
DBMS_RANDOM.STRING ('Q', 19) ;
END LOOP ;
COMMIT ;
END ;
/
SELECT COUNT(*)
FROM scott.test01 ;
ANALYZE TABLE scott.test01 COMPUTE STATISTICS ;
SELECT SUM(BYTES)/1024/1024 MB
FROM dba_segments
WHERE owner = 'scott'
and segment_name = 'test01'
SELECT table_name, num_rows, blocks, empty_blocks
FROM dba_tables
WHERE owner = 'scott'
AND table_name = 'test01' ;
위 결과 값 num_rows는 데이터 건 수이고 blocks 는 사용 중인 block의 개수며 empty_blocks는 빈 블록 개수이다.
SELECT COUNT (
DISTINCT DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid) ||
DBMS_ROWID.ROWID_RELATIVE_FNO(rowid)
) "실사용 블록수"
FROM scott.test01 ;
DELETE
FROM scott.test01 ;
COMMIT ;
SELECT COUNT(*)
FROM scott.test01 ;
SELECT SUM(BYTES)/1024/1024 MB
FROM DBA_SEGMENTS
WHERE OWNER = 'scott'
AND SEGMENT_NAME = 'test01' ;
SELECT COUNT (
DISTINCT DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid) ||
DBMS_ROWID.ROWID_RELATIVE_FNO(rowid)
) "실사용 블록수"
FROM scott.test01 ;
위 테스트에서 본 것처럼 DELETE는 데이터만 지우고 용량은 줄이지 못한다.
그래서 만약 모든 데이터가 지워졌는데 용량까지 줄이고 싶다면 REORG 작업을 별도로 해주어야 한다.
1. 위에서 생성했던 scott.test01 테이블에 데이터를 추가한다.
BEGIN
FOR i IN 1..1000 LOOP
INSERT INTO scott.test01
VALUES (i, DBMS_RANDOM.STRING ('A', 19),
DBMS_RANDOM.STRING ('B', 19) ;
END LOOP ;
COMMIT ;
END ;
2. 데이터 건 수 와 테이블 용량을 측정한다.
SELECT COUNT(*)
FROM scott.test01 ;
SELECT SUM(BYTES)/1024/1024 MB
FROM DBA_SEGMENTS
WHERE OWNER = 'scott'
AND SEGMENT_NAME = 'test01' ;
3. 1000건 중 300건만 삭제한다.
DELETE
FROM scott.test01
WHERE no BETWEEN 1 AND 300 ;
SELECT COUNT(*)
FROM scott.test01 ;
SELECT SUM(BYTES)/1024/1024 MB
FROM DBA_SEGMENTS
WHERE OWNER = 'scott'
AND SEGMENT_NAME = 'test01' ;
4. 테이블 REORG 작업을 한다.
이 작업은 여러가지 방법이 있지만 여기서는 테이블 스페이스를 이동시키는 방법을 이용한다.
SELECT TABLE_NAME, TABLESPACE_NAME
FROM DBA_TABLES
WHERE TABLE_NAME = 'test01' ;
ALTER TABLE scott.test01 MOVE TABLESPACE USERS ;
SELECT SUM(BYTES)/1024/1024 MB
FROM DBA_SEGMENTS
WHERE OWNER = 'scott'
AND SEGMENT_NAME = 'test01' ;
용량이 현저하게 준 것을 확인할 수 있다.
SELECT COUNT(*)
FROM scott.test01 ;
해당 테이블에 인덱스나 뷰 등이 생성되어 있다면 부가적인 작업이 더 있으니 자세한 사항은 운영 매뉴얼을 참고바람.
여기서 강조하는 것은 'DELETE를 수행해도 테이블 용량은 줄어들지 않는다' 라는 것
4. MERGE
MERGE란 테이블의 데이터를 합치는 병합을 의미
MERGE INTO table1
USING table2
ON ( 병합 조건절 )
WHEN MATCHED THEN
UPDATE SET 업데이트 내용
DELETE WHERE 조건
WHEN NOT MATCHED THEN
INSERT VALUES (컬럼 이름) ;
위 문법은 table1과 table2의 내용을 합쳐서 table1에 모으는 것이다. 이 때 기준은 3번 라인의 조건이 된다.
MERGE구문이 수행될 때 집계 테이블(table1)의 데이터와 신규테이블(table2)의 내용을 비교해서 확인하기 때문에
집계 테이블에 데이터가 많아질수록 MERGE작업의 수행 속도가 늦어진다.
MERGE작업을 조금이라도 빨리 수행하려면 3번 라인의 조건절에 인덱스가 잘 만들어져 있어야 한다.
서브 쿼리를 쓸 때 바인드 변수를 사용하는 경우가 있는데 MERGE구문에서 서브 쿼리를 사용할 때 바인드 변수의 값을
사용하는 것은 지원이 되지 않는다.
SELECT * FROM pt_01 ;
SELECT * FROM pt_02 ;
SELECT * FROM p_TOTAL ;
MERGE INTO p_total total
USING pt_01 p01
ON (total.판매번호 = p01.판매번호)
WHEN MATCHED THEN
UPDATE SET total.제품번호 = p01.제품번호
WHEN NOT MATCHED THEN
INSERT VALUES (p01.판매번호, p01.제품번호, p01.수량, p01.금액 ) ;
MERGE INTO p_total total
USING pt_02 p02
ON (total.판매번호 = p02.판매번호)
WHEN MATCHED THEN
UPDATE SET total.제품번호 = p02.제품번호
WHEN NOT MATCHED THEN
INSERT VALUES (p01.판매번호, p02.제품번호, p02.수량, p02.금액 ) ;
5. TRANSACTION 관리하기
TRANSACTION이란 여러가지 DML 작업들을 하나의 단위로 묶어 둔 것을 의미한다.
해당 트랜잭션 내에 있는 모든 DML이 성공해야 해당 트랜잭션이 셩공하는 것이고 만약 1개의 DML이라도 실패하면 전체가 실패하게 된다.
모든 트랜잭션은 크기가 다를 수 있다는 것을 기억해야 하며 트랜잭션의 시작은 DML이고 완료하려면 TCL, DCL, DDL 이 입력되면 된다.
트랜잭션 내의 작업 결과를 확정하는 명령어로 COMMIT이 있으며 트랜잭션 내의 모든 명령어들을 취소하는 ROLLBACK 명령어가 있다.
6. DML ERROR LOGGING 하기
오라클 10g R2 버전부터 DML 작업 시 장애가 날 경우 해당 장애 내용을 별도의 테이블에 기록해주는 기능이 제공된다.
이 기능은 DBMS_ERRLOG 패키지를 통해 구현하게 되며 이 패키지를 실행하면 DML 에러를 저장하는 로그 테이블을 생성하게 된다.
로그 테이블 명은 ERR$SUBSTR(table_name,1,25)로 설정되며 사용자가 해당 패키지를 실행할 때 다른 이름으로 지정할 수 있다.
1. DBMS_ERRLOG 패키지를 수행해서 에러 로깅 테이블 DML_ERRORS 생성한다.
SELECT * FROM dml_err_test ;
이 테이블은 no컬럼에 pk가 설정되어 있다. no컬럼에 중복되는 값을 입력하게 되면 에러가 발생한다.
그 내용을 로깅하는 것을 테스트하는 것이다. 에러내용을 로깅하기 위해 dmbs_errorlog 패키지를 아래와 같이 실행한다.
BEGIN
DBMS_ERRLOG.CREATE_ERROR_LOG (
dml_table_name => 'DML_ERR_TEST',
err_log_table_name => 'DML_ERRORS' ) ;
END ;
/
DESC dml_errors ;
2. DML_ERR_TEST 테이블에 에러를 발생하는 DML 을 수행한다.
INSERT INTO dml_err_test
VALUES (1, 'CCC')
LOG ERRORS INTO dml_errors('INSERT..RL=UNLIMITES')
REJECT LIMIT UNLIMITED ;
3. 에러를 확인한다.
이 에러 저장 테이블에 있는 내용을 보기 위해서 는 토마스 카이트가 작성한 print_table이란 프로시저를 사용해야 한다.
내용은 아래와 같다.
create or replace
procedure print_table( p_query in varchar2 )
AUTHID CURRENT_USER
is
l_theCursor integer default dbms_sql.open_cursor;
l_columnValue varchar2(4000);
l_status integer;
l_descTbl dbms_sql.desc_tab;
l_colCnt number;
begin
execute immediate
'alter session set
nls_date_format=''yyyy-mm-dd:hh24:mi:ss'' ';
dbms_sql.parse( l_theCursor, p_query, dbms_sql.native );
dbms_sql.describe_columns
( l_theCursor, l_colCnt, l_descTbl );
for i in 1 .. l_colCnt loop
dbms_sql.define_column
(l_theCursor, i, l_columnValue, 4000);
end loop;
l_status := dbms_sql.execute(l_theCursor);
while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
for i in 1 .. l_colCnt loop
dbms_sql.column_value
( l_theCursor, i, l_columnValue );
dbms_output.put_line
( rpad( l_descTbl(i).col_name, 30 )
|| ': ' ||
l_columnValue );
end loop;
dbms_output.put_line( '-----------------' );
end loop;
execute immediate
'alter session set nls_date_format=''yyyy-mm-dd'' ';
exception
when others then
execute immediate
'alter session set nls_date_format=''yyyy-mm-dd'' ';
raise;
end;
아래와 같은 방법으로 로깅 테이블 내용을 확인할 수 있다.
EXEC print_table ('SELECT * FROM dml_errors') ;
위의 예와 같이 DML 문장을 수행할 때 추가적인 옵션을 주어서 DML 문장 수행 시 에러가 발생할 경우 해당 에러를 별도의 테이블에 기록할 수 있다.
그러나 몇가지 제한사항이 있다.
1. CONSTRAINT 중에 DEFFERD CONSTRAINT를 위반한 사항은 로깅되지 않는다.
즉 IMMEDIATE로 위반 사항은 모두 기록되지만 COMMIT 수행시에 일괄적으로 체크되는 경우는 기록되지 않는다.
2. DIRECT PATH 방식으로 데이터를 입력하는 INSERT나 MERGE일 경우에 UNIQUE 제약조건 위반이나 INDEX 조건 위반일 경우 기록되지 않는다.
3. 모든 UPDATE나 MERGE일 경우 UNIQUE 제약조건 위반이나 INDEX 조건 위반일 경우 기록되지 않는다.
이 기능은 PL/SQL 에서도 동일하게 사용가능하지만 DML작업에 부하를 주게 되어 전체적인 DML작업의 성능이 저하된다는 단점이 있으니
성능이 중요한 DML작업 등이나 실시간 변경량이 많은 테이블 등에는 충분한 테스트를 거친 후 사용 여부를 결정하기 바란다.
[출처] 다양한 예제로 쉽게 배우는 오라클 SQL과 PL/SQL 서진수 저