오라클 DB 용어, 명령문 정리04 - 복수행 서브쿼리, 집합연산자, DML, TCL

2019. 05. 22 필기

<복수행 서브쿼리 MULTI-ROW SUBQUERY>

---------------------------------------------기본 개념---------------------------------------------

여러 개의 행이 리턴되는 서크쿼리 
※반드시 복수행 연산자를 사용해야 한다.(IN, ANY, ALL) 

-----------------------------------------------------------------------------------------------------

:: 복수행 연산자 ::

IN 연산자

서브쿼리의 결과 중에서 하나라도 메인쿼리의 비교조건과 일치하면 검색 가능
WHERE절에서 사용하는 일반 비교연산자와 동일하다.

 

※ 서브쿼리의 결과를 부등호 연산자(>, <)를 사용할 경우 서브쿼리 앞에 ANY 또는 ALL 연산자 이용

ALL 연산자

서브쿼리에서 리턴된 모든 결과값이 WHERE 절에서 모두 비교된다.
복수행 서브쿼리 결과가 메인쿼리 WHERE 절에서 부등호 조건으로 비교될 때 사용된다.

 

ANY 연산자

서브쿼리에서 리턴된 각각의 결과값이 WHERE 절에서 비교된다.
복수행 서브쿼리 결과가 메인 쿼리의 WHERE 절에서 부등호 조건으로 비교될 때 사용된다.

 

--- 사실상 복수행 연산자 대신, MIN 또는 MAX 함수를 이용한 단일행 서브쿼리를 이용 가능(오히려 속도 더 빠름)
--- 따라서 사실상 ALL, ANY 연산자를 사용할 일은 거의 없다.

 

복수행 연산자	단일행 연산자
비교값 ALL > (모든 반환값)	비교값 > (MAX 반환값)
비교값 ALL < (모든 반환값)	비교값 < (MIN반환값)
비교값 ANY > (어느 반환값)	비교값 > (MIN 반환값)
비교값 ANY > (어느 반환값)	비교값 < (MAX 반환값)

 

서브쿼리에 검색된 값이 여러개인 경우(MULTI-COLUMN SUBQUERY) 비교대상을 () 안에 나열하여 비교 가능

(예문)

SELECT EMPNO, ENAME, MGR, JOB, SAL

FROM EMP

WHERE (MGR, JOB)=(SELECT MGR, JOB FROM EMP WHERE ENAME='ALLEN')

           AND ENAME<>'ALLEN';

 

 

<집합 연산자 (SET 연산자)>

--------------------------------------------------------기본 개념--------------------------------------------------------

두 개의 SELECT 명령 반환값을 이용하여 집합 결과를 제공하는 연산자

즉, 여러 개의 쿼리문을 하나의 쿼리문으로 결함하는 것

--- 합집합, 교집합, 차집합 등 

---------------------------------------------------------------------------------------------------------------------------

UNION 연산자 -- 합집합

>> [형식] SELECT 명령문1 UNION SELECT 명령문2;

: 두 개의 SELECT 명령으로 검색된 결과 중에 중복 행을 제거하고 출력

 두 개의 SELECT 명령에 대한 검색대상의 개수나 자료형이 다르면 에러 발생

--- 검색대상의 개수가 다른 경우, 임의값 또는 null을 이용하면 검색 가능

--- 검색대상의 자료형이 다른 경우에는 변환함수를 이용하여 검색대상의 자료형을 동일하게 검색

                                                    ---TO_CHAR() 등

 

UNION ALL 연산자

: 두 개의 SELECT 명령으로 검색된 모든 결과 출력, 중복 허용

 

INTERSECT 연산자 -- 교집합

: 두 개의 SELECT 명령을 모두 만족하는 결과 중에서 중복행을 제거하고 출력

 

MINUS 연산자 -- 차집합

: 첫번째 SELECT 명령으로 검색된 행에서 두번째 SELECT 명령으로 검색된 행을 제거한 결과 제공

 

 

 

--------------------------------------------- 여기까지는 DQL(DATA QUERY LANGUAGE) 였다. 이제 다른 언어도 알아보자.

---

 

<데이터 조작어 DML>

--------------------------------------------------------기본 개념--------------------------------------------------------

DML(DATA MANIPULATION LANGUAGE) : 데이터 조작어

테이블에 행을 삽입(저장), 삭제, 변경하기 위한 SQL 명령

DML 명령 실행 후, COMMIT 명령(DML 명령 적용) 또는 ROLLBACK 명령(DML 명령 취소) 실행

----------------------------------------------------------------------------------------------------------------------------

 

 

INSERT 문장

: 테이블에 새로운 행을 삽입(저장)하는 명령

 

[형식1] INSERT INTO 테이블명 VALUES(컬럼값, 컬럼값, ...);

--- 한 번에 하나의 행만 입력 가능

--- 테이블 구조(컬럼 순서, 자료형, 개수)에 맞게 차례대로 컬럼값을 나열하여 삽입

--- 컬럼 순서대로 자료형에 맞는 값들을 생략없이 전달하여 저장

--- 컬럼값의 개수 또는 자료형이 다르거나 컬럼값의 크기가 컬럼크기보다 큰 경우 에러 발생

--- 컬럼에 부여된 제약조건을 위반하는 값이 전달될 경우 에러 발생

 

[형식2] INSERT INTO 테이블명(컬럼명,컬럼명,...) VALUES(컬럼값, 컬럼값, ...);

--- 원래 테이블 구조의 컬럼순서와 상관 없이, 선언해 준 컬럼명 순서대로 값을 삽입

--- 왜 쓰느냐? 컬럼을 생략할 수 있기 때문! 형식1은 생략이 안 되지만 형식2는 생략 가능

 

DESC 테이블명; -- 테이블 구조를 확인하는 명령어

>> [결과]

/ 이름 --- 컬럼명

/ 널? --- 널값이 허용되는지

       --- NOT NULL이라고 되어있으면, 반드시 어떤 값이 들어가야 한다는 뜻

/ 유형 --- 자료형

         --- NUMBER(n), VARCHAR2(n), DATE 등등

 

삽입 테이블의 컬럼 생략 가능

 : 생성된 컬럼에는 컬럼 기본값(COLUMN DEFAULT VALUES)에 자동 저장 
모든 컬럼에서 기본값이 설정되어 있으며, 기본값을 설정하지 않은 경우 자동으로 null이 설정되어 있음

 

INSERT 명령에서 SUBQUERY 사용

: 테이블에서 행을 검색하여 다른 테이블에 검색행 삽입 - 테이블 행 복사

[형식1] INSERT INTO 테이블명 SELECT 검색대상 FROM 테이블명 WHERE 조건식;

--- INSERT 명령의 SUBQUERY는 () 를 명시하지 않는다.

--- 삽입 테이블의 컬럼과 서브쿼리의 검색대상은 구조(컬럼개수, 자료형, 크기)가 동일해야 함 - 컬럼명 제외

 

 

UPDATE 문장

: 저장되어 있는 컬럼값을 변경하는 명령

 

[형식] UPDATE 테이블명 SET 컬럼명=변경값, 컬럼명=변경값, ... WHERE 조건식;

--- 조건식이 생략된 경우 테이블에 저장된 모든 행의 컬럼값이 동일한 값으로 변경되므로 주의

--- PK 제약조건이 부여된 컬럼의 값은 변경하지 않는 것을 권장

 

UPDATE 명령에서 SUBQUERY 사용

: 변경값 대신 서브쿼리를 사용하거나, 조건식의 비교값 대신 서브쿼리 사용

 

 

DELETE 문장

: 저장된 행을 삭제하는 명령

 

[형식] DELETE FROM 테이블명 WHERE 조건식;

--- 조건식이 생략된 경우 테이블에 저장된 모든 행의 컬럼값이 삭제되므로 주의

--- FK 제약조건에 의해 자식 테이블의 행에서 참조되는 부모테이블의 컬럼값을 가진 행을 삭제하려 할 경우 에러 발생

 

DELETE 명령에서 SUBQUERY 사용

: 비교값 대신 서브쿼리 사용

 

 

MERGE 문장

: 테이블의 행을 다른 테이블에 삽입하거나 행의 컬럼값을 변경하는 명령 - 테이블 동기화

 

[형식]

MERGE INTO 타겟테이블명 USING 소스테이블명 ON (조건식) 

WHEN MATCHED THEN UPDATE SET 타겟컬럼명1=소스컬럼값1, 타겟컬럼명2=소스컬럼값2, ...

WHEN NOT MATCHED THEN INSERT(타겟컬럼명, ...) VALUES(소스컬럼값, ...);

--- 데이터를 받을 테이블이 타겟, 가져 올 테이블이 소스

--- 조건식에 따라 소스테이블에 저장된 행의 컬럼값을 타겟테이블의 컬럼으로 삽입하거나 변경

--- DEPT 테이블(소스테이블)에 저장된 부서정보를 MERGE_DEPT 테이블(타겟테이블)에 삽입 또는 변경

--- DEPT 테이블의 부서코드가 MERGE_DEPT 테이블에 이미 저장된 경우 MERGE_DEPT 테이블의 부서정보를 변경하고 없는 경우에는 부서정보를 삽입

 

 

<트랜잭션 제어언어 TCL>

--------------------------------------------------------기본 개념--------------------------------------------------------

TCL(TRANSACTION CONTROL LANGUAGE) - 트랜잭션 제어언어

 

SQL 명령이 저장된 트렌젝션을 실제 데이타베이스에 적용하거나 --- COMMIT

적용하지 않고 제거하는 명령 --- ROLLBACK

제거할 명령 범위도 지정 가능--- SAVEPOINT

 

※ 트랜잭션(TRANSACTION) : 현재 세션에 생성되는 SQL 명령을 저장하기 위한 작업 단위 - SQL 명령 집합

※ 세션(SESSION) : DBMS에 접속하여 명령을 전달하여 실행할 수 있는 사용자 작업환경

---------------------------------------------------------------------------------------------------------------------------

 

- 현재 세션에서 SQL 명령(DDL, DCL, DML)을 전달하면 자동으로 트랜잭션이 생성된다.

- SQL 명령이 DBMS에 무조건 전달되는 것이 아니라 세션에 생성된 트랜잭션에 저장되는 것

-즉, 쉽게말하면 트랜잭션은 '임시저장'같은 기능이며,

--- SELECT 명령은 트렌젝션에 저장되지 않음 >> DBMS에 직접 전달되어 실행 후 결과 제공

--- DML 명령은 DBMS에 전달되어 실행되지 않고 트렌젝션에 저장 >> 실제 DB의 데이터가 변동되지는 않는다!

 

 

COMMIT;

: 트랜잭션에 저장되어 있던 모든 SQL 명령을 DBMS에 전달

 

ROLLBACK;

: 트랜잭션에 저장되어 있던 모든 SQL 명령을 제거

--- 트랜잭션에서 잘못된 명령을 실행했을 경우, DBMS에 전달되기 전 만회할 수 있다.

 

SAVEPOINT;

: 일종의 라벨을 지정해, 롤백TO라벨명 실행 시 라벨 이후부터의 명령만 롤백.

--- 롤백을 실행하면 한 순간에 모~든 트랜잭션 속 SQL명령이 날아가기 때문에, 세이브포인트를 생성해 단계별로 관리 

 

[형식] SAVEPOINT 라벨명;

[형식] ROLLBACK TO 라벨명;

 

 

<트랜젝션을 이용하는 이유>

- 데이터 일관성을 제공하기 위해

  --- 데이터 일관성 : 모든 데이터베이스 사용자에게 동일한 결과값을 제공하는 것

- 데이타 잠금 기능을 구현하기 위해
  --- 데이타 잠금 기능 : 현재 세션에서 작업 중인 행을 다른 세션에서 작업하지 못하도록 제한하는 기능

- SQL 명령에 대한 취소 기능을 제공하기 위해(ROLLBACK 이용)

  --- 잘못된 SQL 명령이 DBMS 직접 전달되어 실행될 경우 시스템에 심각한 문제 발생할 수 있기 때문

 

< 트랜잭션에 저장된 SQL 명령을 DBMS에 전달하여 실행하는 방법 >

1. 현재 세션이 정상적으로 종료된 경우 트랜잭션의 SQL 명령이 자동으로 DBMS에서 전달되어 실행

2. DDL 명령 또는 DCL 명령을 전달할 경우 트랜잭션에 저장된 SQL 명령이 자동으로 DBMS에서 전달되어 실행

3. COMMIT 명령을 이용하여 트랜잭션의 SQL 명령을 DBMS에서 전달되어 실행

>> 트랜잭션에 저장된 SQL 명령이 DBMS에 전달되어 실행된 후 트랜잭션은 자동으로 제거

 

 

< 트랜잭션을 제거하는 방법 >

1. 현재 세션이 비정상적으로 접속 종료된 경우 트랜잭션이 자동으로 제거됨

2. ROLLBACK 명령을 이용하여 트랜잭션 제거

 

 

<자주 뜨는 오라클 에러메시지 중 몇 가지 메모>

- "unique constraint" : 기존 컬럼값과 겹치는 값을 삽입하려고 할 때 발생하는 에러

- "column not allowed here" : 자료형이 다를 때

- "not enough values" : 전달되어야 하는 컬럼값 개수에서 누락된 게 있을 때

- integrity constraint (오류위치) violated - parent key not found
    : FK 제약조건을 위반하여 에러 발생 - FK 컬럼에 없는 데이터를 이용하려 함
     >> 다른 테이블의 컬럼값을 참조하여 처리

     >> EMP 테이블의 DEPTNO 컬럼에 FK 제약조건 부여되어 DEPT 테이블의 DEPTNO 컬럼값을 참조하여 저장

     >> 사원정보 중 부서코드에 잘못된 부서코드가 저장되는 것을 방지하기 위한 제약조건

- "table or view does not exist" : 존재하지 않는 테이블을 이용하려 함