[Oracle] DDL, DML

이전 포스팅에선 Oracle의 기본적인 지식과 사용법을 알았다면

이번에는 데이터베이스 조작에 사용되는 명령어를 공부해보려 한다.

 

다른 RDBMS를 사용하고 있던 개발자들은 Oracle의 문법을

이번 기회를 통해 비교하고 이해할 수 있길 바란다.

 

DDL (Data Definition Language)

DDL은 데이터베이스 객체를 조작하는 데 사용되는 SQL 명령어를 가리킨다.

데이터베이스 객체라 하면 테이블, 뷰, 인덱스, 시퀀스 등이 이에 속한다.

 

CREATE

CREATE문은 객체를 생성하는 명령어다.

테이블, 뷰, 인덱스 별로 생성하는 방법이 각기 다르니 이 점에 유의해야 한다.

 

OR REPLACE는 해당 이름을 가진 객체가 존재 시, 현재 작성한 것으로 대체하겠다는 명령어다.

생략이 가능하므로, 덮어쓸 때에 선택적으로 활용하면 된다.

--테이블 생성
CREATE [OR REPLACE] TABLE STUDENT(
	S_ID NUMBER PRIMARY KEY,
	FIRST_NM VARCHAR2(20) NOT NULL, 
	LAST_NM VARCHAR2(20) NOT NULL,
	ADMISSION_DT DATE
);

--뷰 생성
CREATE VIEW STDT_VIEW AS
SELECT S_ID, FIRST_NM||' '||LAST_NM AS NAME, ADMISSION_DT
FROM STUDENT
WHERE ADMISSION_DT IS NOT NULL
	AND ADMISSION_DT BETWEEN TO_DATE('20240302', 'YYYYMMDD') AND SYSDATE;
	
--인덱스 생성
CREATE INDEX STDT_IX_AD_DT ON STUDENT(ADMISSION_DT);

 

ALTER

ALTER문은 기존 객체의 구조를 변경하는 명령어다.

테이블의 속성을 생성, 수정 및 삭제할 수 있다.

 

뷰 같은 경우엔 ALTER VIEW 명령문이 존재하지 않는다.

뷰에서 조회하는 SELECT문을 변경하고 싶다면 CREATE OR REPLACE로 수행한다.

 

--테이블 컬럼 변경
ALTER TABLE STUDENT ADD PHONE VARCHAR2(20) NOT NULL;
ALTER TABLE STUDENT MODIFY FIRST_NM VARCHAR2(30);
ALTER TABLE STUDENT DROP COLUMN PHONE;

--뷰 변경
CREATE OR REPLACE VIEW STDT_VIEW AS
SELECT S_ID, FIRST_NM||'('||LAST_NM||')' AS NAME, ADMISSION_DT
FROM STUDENT
WHERE ADMISSION_DT IS NOT NULL
	AND ADMISSION_DT BETWEEN TO_DATE('20240302', 'YYYYMMDD') AND SYSDATE;

--인덱스 재생성
ALTER INDEX STDT_IX_AD_DT REBUILD;

 

DROP

DROP문은 객체를 삭제하는 명령어다.

--테이블 삭제
DROP TABLE STUDENT;

--뷰 삭제
DROP VIEW STDT_VIEW;

--인덱스 삭제
DROP INDEX STDT_IX_AD_DT;

 

TRUNCATE

TRUNCATE문은 테이블 구조를 유지하되, 테이블 내의 모든 데이터들을 일괄적으로 삭제하는 명령어다.

 

객체가 아닌 객체 내 데이터들을 삭제하는 작업이기에 DML에 해당된다고 착각할 수 있으나, TRUNCATE는 DDL 명령으로 분류가 된다.

그 이유는 TRUNCATE는 단순히 각각의 행들을 DELETE하는 것이 아니라, 테이블을 DROP하고 다시 CREATE하는 작업이기 때문이다.

(참고: https://docs.oracle.com/cd/E17952_01/mysql-8.0-en/truncate-table.html)

 

--테이블 비우기
TRUNCATE TABLE STUDENT;

 

RENAME

RENAME문은 객체의 이름을 변경하는 명령어다.

--테이블 이름 변경
RENAME STUDENT TO STDT;

--뷰 이름 변경
RENAME STDT_VIEW TO STDT_VW

--인덱스 이름 변경
ALTER INDEX STDT_IX_AD_DT RENAME TO STDT_IDX_AD;

 

COMMENT

COMMENT문은 객체에 주석을 추가하는 명령어다.

해당 명령문은 Oracle에서만 사용하는 방법이므로 MySQL, MS-SQL과 같은 다른 RDBMS를 사용하는 개발자들은 해당 RDBMS의 방식을 따라야 한다.

 

뷰에 주석을 추가하고 싶을 때엔 COMMENT ON VIEW가 아닌 COMMENT ON TABLE을 사용한다.

또한, 인덱스에는 따로 주석을 작성할 수가 없다.

 

--테이블 주석 추가
COMMENT ON TABLE STUDENT IS '학생 테이블';

--테이블 속성 주석 추가
COMMENT ON COLUMN STUDENT.S_ID IS '학번';

--뷰 주석 추가
COMMENT ON TABLE STDT_VIEW IS '학생 정보 뷰';

 

DML(Data Manipulation Language)

 

DML은 객체(테이블, 뷰)  내에 삽입된 데이터를 조작하는 데 사용되는 SQL 명령어를 가리킨다.

데이터 CRUD를 진행하는 것이 바로 이 DML에 속한다.

 

SELECT

SELECT문은 객체 안의 데이터를 조회하는 명령어다.

테이블 뿐만 아니라 VIEW도 필요 조건을 붙여 SELECT문으로 데이터를 조회할 수 있다

SELECT S_ID, FIRST_NM||'('||LAST_NM||')' AS NAME, ADMISSION_DT --조회할 속성 선택
FROM STUDENT --조회할 테이블/뷰 선택
WHERE ADMISSION_DT IS NOT NULL --조회 조건 설정
	AND ADMISSION_DT BETWEEN TO_DATE('20240302', 'YYYYMMDD') AND SYSDATE
LIMIT 10; --조회 튜플 개수 설정

 

INSERT

INSERT문은 특정 테이블에 새로운 데이터를 추가하는 명령어다.

 

다중 행을 삽입하고 싶을 때 VALUES를 콤마로 구분짓는 것은 Oracle 문법에 어긋난다.

DUAL 테이블을 사용하여 삽입할 행에 대한 임시 테이블을 만들고 이를 VALUES에 정의해야 한다.

INSERT INTO STUDENT (S_ID, FIRST_NM, LAST_NM, ADMISSION_DT) --삽입할 테이블 속성
VALUES(1111, '길동', '홍', '20240302'); --삽입할 값

INSERT INTO STUDENT (S_ID, FIRST_NM, LAST_NM, ADMISSION_DT)
SELECT 1111, '길동', '홍', '20240302' FROM DUAL
UNION ALL
SELECT 2222, '철수', '김', '20240302' FROM DUAL;

 

UPDATE

UPDATE문은 테이블 안에 존재하는 기존의 데이터를 수정하는 명령어다.

UPDATE STUDENT --변경 테이블
SET FIRST_NM = '영희' --변경 속성
WHERE FIRST_NM = '철수'; --변경 조건

 

💡 UPSERT문 작성법

데이터를 관리하다보면 중복 데이터가 존재 시 UPDATE, 없으면 INSERT를 하는 갱신이 필요할 때가 있다.

이럴 때 Oracle에서는 MERGE INTO를 사용한다.

 

MERGE INTO문은 조건에 따른 결합을 수행하는 명령어로,

UPSERT문에 자주 쓰이지만 이외에도 해당 값 존재 시 INSERT문 무시 등 응용해서 사용할 수 있는 조건문이다.

MERGE INTO STUDENT S
USING DUAL ON (S.S_ID = 2222) --조건 설정
WHEN MATCHED THEN --조건이 TRUE일 시
	UPDATE SET FIRST_NM = '영희'
WHEN NOT MATCHED THEN --조건이 FALSE일 
	INSERT (S_ID, FIRST_NM, LAST_NM, ADMISSION_DT)
		VALUES (2222, '철수', '김', '20240302');

 

 

DELETE

DELETE문은 데이터를 삭제하는 명령어다.

DELETE FROM STUDENT --삭제 테이블
WHERE S_ID = 1111; --삭제 조건

 

💡 TRUNCATE와 DELETE의 차이점

TRUNCATE는 튜플 뿐만 아니라 테이블과 관련된 인덱스 등도 모두 한 번에 삭제가 된다. 위에서 언급했듯, 테이블을 DROP하고 다시 CREATE하는 과정이기 때문이다.

이 작업은 속도가 빠르지만, 로그가 남지 않아 ROLLBACK이 불가능하다.

 

반면 DELETE는 각 튜플 하나하나를 순차적으로 삭제하는 작업으로, TRUNCATE보다 속도가 느릴 수 있다.

그러나 각 작업에 대한 로그가 생성되므로 잘못된 작업에 대해 되돌릴 수가 있다.

---

 

DCL과 TCL은 이후 포스팅에서 다루도록 하겠다.