[Oracle] 관리 문법 정리

 

 

 

DML 

 

Data Manipulation Language의 약자로 데이터 조작 언어로 해석된다.

테이블에 있는 행을 삭제, 수정, 추가할 때 사용된다.

 

insert into tbl (a, b, c)
	values (1, 2, 3);

insert into tbl (a, b)
	select a, b from tbl2 WHERE c = "asdf"

 

tbl 부분에 테이블, 서브쿼리를 작성하고 데이터를 추가한다.

테이블에 columns를 정의하지 않으면 모든 값을 입력해 줘야 한다.

 

위의 예시에서 서브쿼리를 사용했는데, c가 asdf인 행을 tb1에 삽입했다.

한 번에 여러 데이터를 삽입할 수 있다.

 

insert ALL
	into tbl1 (a, b) values (a, b)
    into tbl2 (a, b) values (a, b)
    select * from tbl3;

 

ALL 문법을 사용해 한 번에 여러 테이블에 데이터를 넣을 수 있다.

 

insert FIRST
	WHEN s >= 2000 THEN into tbl1
    WHEN s >= 3000 THEN into tbl2
    ELSE into tbl3
select a, b, c from tbl4;

 

ALL과 FIRST 문법을 사용해 조건부 INSERT 문법을 구현할 수 있다.

ALL은 조건을 만족하는 모든 테이블에 결과를 넣어주고, FIRST는 조건을 만족하는 첫 번째 테이블에 결과를 넣어준다.

 

 

update tbl1 SET a = 1, b = 2 WHERE c = 123
delete from tbl where a = 1

 

UPDATE 문법을 사용해 테이블의 기존 행을 갱신할 수 있다.

DELETE 문법을 사용해 테이블의 행을 삭제할 수 있다.

 

서브쿼리를 사용할 수도 있다. 

 

값에 직접적인 영향을 끼치는 문법이기에 실수를 줄이기 위해 수행하기 전 SELECT문법으로 확인하고 사용하는 편이 합리적이다.

 

MERGE
	into t1
    USING t2
    ON t1.a = t2.a
    WHEN MATCHED THEN
    	UPDATE
    		set t1.a = 10;
    WHEN NOT MATCHED THEN
    	INSERT (a) VALUES (1)

 

 

MERGE 문법으로 테이블에 행을 삽입하거나 수정할 수 있다.

INTO : 값을 조작할 테이블을 지정한다.

USING : 값을 조작할 테이블과 조인할 테이블을 지정한다.

ON : 조인 조건을 지정한다.

MERGE UPDATE : 조건을 만족하면 수행된다

MERGE INSERT : 조건을 만족하지 못하면 수행된다.

 

DML 문을 조작할 때 한 문장에서 하나의 작업이라도 실패하게 되면 변경 사항이 모두 롤백되는데, 에러 로깅 기능을 사용하면 롤백을 방지할 수 있으니 참고하자.

 

 

TCS

 

Transaction Control Statement의 약자로 트랜잭션 제어문을 의미한다.

트랜잭션은 하나로 묶이는 작업의 논리적인 단위이고, DML문이나 SET TRANSACTION 문법이 실행되면 시작되고 COMMIT이나 ROLLBACK 문법이 실행되면 트랜잭션이 종료된다.

 

오라클에서는 v$transaction 뷰에서 현재 수행 중인 트랜잭션에 대한 정보를 조회할 수 있다.

 

COMMIT 문법을 실행하면 현재 트랜잭션의 변경 내용을 데이터베이스에 저장하고 트랜잭션을 종료한다.

ROLLBACK 문법을 실행하면 현재 트랜잭션의 변경 내용을 모두 취소하고 트랜잭션을 종료한다.

 

내부적으로 undo segment에 저장된 변경 이전 데이터를 통해 데이터를 복구한다.

 

SAVEPOINT 문법은 롤백할 수 있는 저장점을 생성한다.

 

SAVEPOINT s1;

ROLLBACK TO SAVEPOINT s1;

 

ROLLBACK과 SAVEPOINT를 함께 사용해 원하는 위치로 롤백할 수 있다.

특정 SAVEPOINT로 롤백하면 해당 SAVEPOINT 이후에 있는 모든 SAVEPOINT가 사라지니 조심하자.

 

오라클은 DML 문이 수행될 때 마다 내부적으로 SAVEPOINT를 생성하고 쿼리 실행 중 오류가 발생하면 직전의 SAVEPOINT로 롤백하는 방식으로 동작한다.

 

 

여러 사용자가 하나의 데이터에 동시에 접근하는 작업을 데이터 동시성이라고 부른다.

오라클은 자원의 사용을 직렬화하기 위해 락을 사용한다. (latch와 mutex를 사용하기도 한다)

 

DML 문법은 TM (테이블 락) 과 TX (트랜잭션 락) 을 사용한다.  

락을 획득한 사용자만 해당 데이터를 다룰 수 있고, 데이터베이스를 모두 조작했으면 락을 반납하는 방식으로 동작한다.

 

데이터베이스 엔진마다 트랜잭션의 격리 수준이 다르니 특징을 적절히 활용하자. 

 

 

DDL

 

Data Definition Language 의 약자로 데이터베이스 객체를 생성하거나 변경할 떄 사용한다.

 

CREATE TABLE tbl (
	column exp1,
    column exp2,
) as subquery;

 

 

서브쿼리를 함께 작성하면 테이블을 만듦과 동시에 서브쿼리의 내용을 테이블에 삽입할 수 있다.

 

테이블은 테이블스페이스에 세그먼트로 저장된다. (지정하지 않으면 기본 테이블스페이스에 저장된다)

 

ALTER TABLE tbl MOVE INCLUDING ROWS WHERE a < 10;

 

INCLUDING ROWS 문법으로 세그먼트를 재배치할 조건을 지정하고 MOVE 문법으로 세그먼트의 데이터를 재배치한다.

ALTER TABLE 문법은 테이블, 열, 제약 조건을 변경할 때 사용하고, TRUNCATE TABLE로 테이블을 초기화 할 수 있다.

 

ADD MODIFY RENAME 등으로 테이블의 COLUMN 등 테이블의 요소를 조작할 수 있다.

 

 

테이블에는 여러 종류가 있다.

 

힙 구조 테이블 : 오라클의 기본 테이블으로, 힙 자료구조 형식으로 데이터를 저장한다 (데이터는 임의의 위치에 저장된다)

인덱스 구조 테이블 : b-tree 인덱스 구조에 데이터를 저장한다.

익스터널 테이블 : 외부 데이터를 조회하거나 외부에 데이터를 저장하는 테이블이다.

클러스터 테이블 : 클러스터 키가 동일한 데이터를 같은 위치에 저장하는 테이블이다.

임시 테이블 : 트랜잭션이나 세션 레벨으로 관리되는 테이블이다. 중간 집계를 저장하는 용도로 쓰인다.

 

테이블에 포함될 column을 설정할 때는 데이터 무결성을 보장하기 위해 제약 조건을 설정할 수 있다.

 

NOT NULL : 지정한 열에 널 값이 존재하지 않음을 보장한다.

UNIQUE : 열이나 열의 조합에서 값이 중복되지 않음을 보장한다.

PK : Primary Key의 약자로 열이나 열의 조합으로 행을 고유하게 식별할 수 있음을 보장한다.

FK : 부모 테이블과 자식 테이블간의 참조 무결성을 보장한다. CASCADE NO ACTION 등으로 부모 테이블이 변경될 때 수행할 규칙을 지정할 수 있다.

CHECK : 열에 저장된 값이 특정 조건을 만족시킴을 보장한다.

 

인덱스에도 여러 종류가 있다.

 

인덱스는 특정 열을 기준으로 데이터가 정렬돼 저장되고 인덱스 열의 값을 키로 원본 테이블의 레코드와 매핑된다. 데이터를 빠르게 검색하기 위한 세그먼트로, 테이블에 종속된다. 일부 열에 대한 별도의 데이터 구조라고 생각하면 된다. 

인덱스 열의 값만 저장하기 때문에 인덱스를 사용해 값을 조회하는 작업이 원본 테이블에 영향을 주지는 않는다.

 

비트맵 인덱스 : 비트맵을 사용해서 인덱스 열을 저장한다. 

함수 기반 인덱스 : 인덱스 열의 일부에 함수느 표현식을 사용한 인덱스이다. 

내림차순 인덱스 : 인덱스 열의 일부를 내림차순으로 정렬한 인덱스이다. (기본적으로 오름차순이다)

 

PK 조건과 UNIQUE 조건을 사용할 때 인덱스를 사용하지 않으면 제약 조건을 검증할 때 마다 전체 테이블을 읽어야 하니 내부적으로 인덱스를 사용한다.

 

create or replace view v1 as select * from tbl where a = 1;

 

뷰는 SELECT 문을 데이터베이스에 저장한 오브젝트로 쿼리에서 테이블처럼 사용할 수 있다.

from 절에 서브쿼리를 사용해서 인라인 뷰를 만든다고 했는데, 그 때 사용한 용어인 뷰 로 생각하면 된다.

뷰를 통해 데이터 보안성과 독립성을 높일 수 있다.

 

WITH CHECK OPTION 문법은 서브쿼리의 WHERE 절에 해당하지 않은 데이터가 생성됨을 방지한다. 

뷰를 사용할 때 WHERE 절과 WITH CHECK OPTION 문법을 함께 사용해 의도하지 않은 결과를 막을 수 있다.

 

뷰도 테이블처럼 ALTER DROP 등 여러 문법을 적용할 수 있다.

 

 

SEQUENCE 는 정수의 순번을 생성할 때 사용한다.

테이블처럼 사용할 수도 있고, 테이블을 구성하는 열의 기본값으로도 사용할 수 있다.

 

IDENTITY 열은 시퀀스가 결합된 열으로, 시퀀스를 할당하는 여러 전략이 있으니 테이블의 PK를 설정할 때 사용하는 등 순차적으로 증가하는 값을 기록할 때 사용하자.

 

CREATE DATABASE LINK db1 USING 'orac';
CREATE DATABASE LINK db2 CONNECT TO db123 IDENTIFIED BY asdf USING 'orac';

 

 

원격 데이터베이스의 오브젝트에 엑세스할 때는 데이터베이스 링크를 사용한다.

접속할 때 필요한 사용자명과 패스워드를 지정하거나 현재 로그인한 정보를 그대로 사용할 수 있다. 

IDENTIFIED BY 문법으로 사용자의 패스워드를 지정한다.

 

분산 데이터베이스 환경에서 사용하자.

 

 

DCL

 

Data Control Language의 약자로 데이터의 권한을 부여하거나 취소할 때 사용된다.

 

사용자는 데이터베이스 서버에 로그인 할 수 있는 계정을 의미한다.

데이터베이스 작업을 수행할 수 있는 권한인 시스템 권한과 특정 오브젝트 (테이블) 에 접근할 수 있는 권한인 오브젝트 권한을 적절히 부여해서 데이터 보안성을 강화하자.

 

권한을 효과적으로 관리하기 위해서 롤을 사용한다.

롤은 권한과 롤의 모음으로, 권한 대신 롤을 사용해 사용자에게 권한을 쉽게 부여할 수 있다.