Chap 4) 관계 대수와 SQL

  • 관계 데이터 모델에서 지원되는 두 가지 정형적인 언어
    • 관계 해석(relational calculus)
      • 원하는 데이터만 명시하고 질의를 어떻게 수행할 것인가는 명시하지 않는 선언적인 언어
    • 관계 대수(relational algebra)
      • 어떻게 질의를 수행할 것인가를 명시하는 절차적 인어
      • 관계 대수는 상용 관계 DBMS들에서 널리 사용되는 SQL의 이론적인 기초
      • 관계 대수는 SQL을 구현하고 최적화하기 위해 DBMS의 내부 언어로서도 사용됨
  • SQL
    • 상용 관계 DBMS들의 사실상의 표준 질의어인 SQL을 이해하고 사용할 수 있는 능력은 매우 중요함
    • 사용자는 SQL을 사용하여 관계 데이터베이스에 릴레이션을 정의하고, 관계 데이터베이스에서 정보를 검색하고, 관계 데이터베이스를 갱신하며, 여러 가지 무결성 제약조건들을 명시할 수 있음


1. 관계 대수

  • 관계 대수
    • 기존의 릴레이션들로부터 새로운 릴레이션을 생성함
    • 릴레이션이나 관계 대수식(이것의 결과도 릴레이션임)에 연산자들을 적용하여 보다 복잡한 관계 대수식을 점차적으로 만들 수 있음
    • 기본적인 연산자들의 집합으로 이루어짐
    • 산술 연산자와 유사하게 단일 릴레이션이나 두 개의 릴레이션을 입력으로 받아 하나의 결과 릴레이션을 생성함
    • 결과 릴레이션은 또 다른 관계 연산자의 입력으로 사용될 수 있음

operator

operator

operator

  • 실렉션 연산자 (형식: σ <실렉션 조건>
    • 한 릴레이션에서 실렉션 조건(selection condition)을 만족하는 투플들의 부분 집합을 생성함
    • 단항 연산자
    • 결과 릴레이션의 차수는 입력 릴레이션의 차수와 같음
    • 결과 릴레이션의 카디날리티는 항상 원래 릴레이션의 카디날리티보다 작거나 같음
    • 실렉션 조건을 프레디키트(predicate)라고도 함
    • 실렉션 조건은 일반적으로 릴레이션의 임의의 애트리뷰트와 상수, = , <>, <=, <, >=, > 등의 비교 연산자, AND, OR, NOT 등의 부울 연산자를 포함할수 있음

selection

  • 프로젝션 연산자 (형식: π <애트리뷰트 리스트> (릴레이션))
    • 한 릴레이션의 애트리뷰트들의 부분 집합을 구함
    • 결과로 생성되는 릴레이션은 <애트리뷰트 리스트="">에 명시된 애트리뷰트들만 가짐
    • 실렉션의 결과 릴레이션에는 중복 투플이 존재할 수 없지만, 프로젝션 연산의 결과 릴레이션에는 중복된 투플들이 존재할 수 있음
      • 기본키 외의 키들은 중복이 가능하기 때문
  • 집합 연산자
    • 릴레이션이 투플들의 집합이기 때문에 기존의 집합 연산이 릴레이션에 적용됨
    • 이항 연산자
    • 세 가지 집합 연산자: 합집합, 교집합, 차집합 연산자
    • 집합 연산자의 입력으로 사용되는 두 개의 릴레이션은 합집합 호환(union compatible) 이어야 함
      • 합집합 호환: 두 릴레이션 R1(A1, A2, …, An)과 R2(B1, B2, …, Bm)이 합집합 호환일 필요 충분 조건은 n=m이고, 모든 1<=i<=n에 대해 domain(Ai)=domain(Bi)
        • 애트리뷰트 수가 다르면 합집합 호완이 되지 않음
    • 합집합 연산자 (형식: 릴레이션1 ∪ 릴레이션2)
      • 두 릴레이션 R과 S의 합집합 R ∪ S는 R 또는 S에 있거나 R과 S 모두에 속한 투플들로 이루어진 릴레이션
      • 결과 릴레이션에서 중복된 투플들은 제외됨
      • 결과 릴레이션의 차수는 R 또는 S의 차수와 같으며, 결과 릴레이션의 애트리뷰트 이름들은 R의 애트리뷰트들의 이름과 같거나 S의 애트리뷰트들의 이름과 같음
      • 만약 결과가 동일한 속성의 다른 이름이면, 이름 하나만 선택해야 함
    • 교집합 연산자 (형식: 릴레이션1 ∩ 릴레이션2)
      • 두 릴레이션 R과 S의 교집합 R ∩ S는 R과 S 모두에 속한 투플들로 이루어진 릴레이션
      • 결과 릴레이션의 차수는 R 또는 S의 차수와 같으며, 결과 릴레이션의 애트리뷰트 이름들은 R의 애트리뷰트들의 이름과 같거나 S의 애트리뷰트들의 이름과 같음
    • 차집합 연산자 (형식: 릴레이션1 - 릴레이션2)
      • 두 릴레이션 R과 S의 차집합 R - S는 R에는 속하지만 S에는 속하지 않은 투플들로 이루어진 릴레이션
      • 결과 릴레이션의 차수는 R 또는 S의 차수와 같으며, 결과 릴레이션의 애트리뷰트 이름들은 R의 애트리뷰트들의 이름과 같거나 S의 애트리뷰트들의 이름과 같음
  • 카티션 곱 연산자 (형식: R × S)
    • 카디날리티가 i인 릴레이션 R(A1, A2, …, An)과 카디날리티가 j인 릴레이션 S(B1, B2, …, Bm)의 카티션 곱 R × S는 차수가 n+m이고, 카디날리티가 i*j이고, 애트리뷰트가 (A1, A2, …, An, B1, B2, …, Bm)이며, R과 S의 투플들의 모든 가능한 조합으로 이루어진 릴레이션
    • 카티션 곱의 결과 릴레이션의 크기가 매우 클 수 있으며, 사용자가 실제로 원하는 것은 카티션 곱의 결과 릴레이션의 일부인 경우가 대부분이므로 카티션 곱 자체는 유용한 연산자가 아님
  • 관계 대수의 완전성
    • 실렉션, 프로젝션, 합집합, 차집합, 카티션 곱은 관계 대수의 필수적인 연산자
    • 다른 관계 연산자들은 필수적인 관계 연산자를 두 개 이상 조합하여 표현할 수 있음
    • 임의의 질의어가 적어도 필수적인 관계 대수 연산자들만큼의 표현력을 갖고 있으면 관계적으로 완전(relationally complete)하다 고 말함


  • 조인 연산자
    • 두 개의 릴레이션으로부터 연관된 투플들을 결합하는 연산자
    • 관계 데이터베이스에서 두 개 이상의 릴레이션들의 관계를 다루는데 매우 중요한 연산자
    • 종류[http://ojc.asia/bbs/board.php?bo_table=LecSQLnPlSql&wr_id=15]
      • 세타조인, 동등조인, 자연조인, 외부조인, 세미조인
  • 디비전 연산자 (형식: R ÷ S)
    • 차수가 n+m인 릴레이션 R(A1, A2, …, An, B1, B2, …, Bm)과 차수가 m인 릴레이션 S(B1, B2, …, Bm)의 디비전 R ÷ S는 차수가 n이고, S에 속하는 모든 투플 u에 대하여 투플 tu(투플 t와 투플 u을 결합한 것)가 R에 존재하는 투플 t들의 집합
    • 릴레이션 S의 모든(ALL) 투플 값과 쌍을 이루는 릴레이션 R의 A1, A2, …,An 값
    • “모든 …에 대해 ~하는“ 형태의 질의에 사용될 수 있음
    • SQL로 표현할 때 동치를 활용: ~하지 않는 …가 없다
  • 관계 대수의 한계
    • 관계 대수는 산술 연산을 할 수 없음
    • 집단 함수(aggregate function)를 지원하지 않음
    • 정렬을 나타낼 수 없음
    • 데이터베이스를 수정할 수 없음
    • 프로젝션 연산의 결과에 중복된 투플을 나타내는 것이 필요할 때가 있는데 이를 명시하지 못함
  • 한계 보완을 위해 추가된 관계 대수 연산자
    • 집단(aggregation) 함수: AVG, SUM, MIN, MAX, COUNT
    • 그룹화: 각 그룹에 대해 집단 함수를 적용
    • 외부 조인
      • 상대 릴레이션에서 대응되는 투플을 갖지 못하는 투플이나 조인 애트리뷰트에 널값이 들어 있는 투플들을 다루기 위해서 조인 연산을 확장한 조인
      • 두 릴레이션에서 대응되는 투플들을 결합하면서, 대응되는 투플을 갖지 않는 투플과 조인 애트리뷰트에 널값을 갖는 투플도 결과에 포함시킴
      • 종류
        • 왼쪽 외부 조인(left outer join) (형식: R⟕S) : 릴레이션 R과 S의 왼쪽 외부 조인 연산은 R의 모든 투플들을 결과에 포함시키고, 만일 릴레이션 S에 관련된 투플이 없으면 결과 릴레이션에서 릴레이션 S의 애트리뷰트들은 널값으로 채움
        • 오른쪽 외부 조인(right outer join) (형식: R⟖S) : 릴레이션 R와 S의 오른쪽 외부 조인 연산은 S의 모든 투플들을 결과에 포함시키고, 만일 릴레이션 R에 관련된 투플이 없으면 결과 릴레이션에서 릴레이션 R의 애트리뷰트들은 널값으로 채움
        • 완전 외부 조인(full outer join) (형식: R⟗S) : 릴레이션 R와 S의 완전 외부 조인 연산은 R과 S의 모든 투플들을 결과에 포함시키고, 만일 상대 릴레이션에 관련된 투플이 없으면 결과 릴레이션에서 상대 릴레이션의 애트리뷰트들은 널값으로 채움


2. SQL, 3. 데이터 정의어와 무결성 제약조건

  • SQL
    • SQL은 현재 DBMS 시장에서 관계 DBMS가 압도적인 우위를 차지하는데 중요한 요인의 하나
    • SQL은 IBM 연구소에서 1974년에 System R이라는 관계 DBMS 시제품을 연구할 때 관계 대수와 관계 해석을 기반으로, 집단 함수, 그룹화, 갱신 연산 등을 추가하여 개발된 언어 1986년에 ANSI(미국 표준 기구)에서 SQL 표준을 채택함으로써 SQL이 널리 사용되는데 기여
    • 다양한 상용 관계 DBMS마다 지원하는 SQL 기능에 다소 차이가 있음
    • 본 책에서는 SQL2를 따름
    • 관계 데이터 모델은 집합을 기반을 두고 있어 테이블 내에 동일한 튜플을 허용하지 않지만 SQL은 이를 허용함
    • SQL은 비절차적 언어(선언적 언어)이므로 사용자는 자신이 원하는 바(what)만 명시하며, 원하는 것을 처리하는 방법(how)은 명시할 수 없음
    • 관계 DBMS는 사용자가 입력한 SQL문을 번역하여 사용자가 요구한 데이터를 찾는데 필요한 모든 과정을 담당
    • 자연어에 가까운 구문을 사용하여 질의를 표현할 수 있음
    • 두 가지 인터페이스
      • 대화식 SQL(interactive SQL)
      • 내포된 SQL(embedded SQL)

역사

  • 오라클 SQL의 구성요소
    • 데이터 검색 : SELECT
    • 데이터 조작어: UPDATE, DELETE, INSERT
    • 데이터 정의어

정의어 - 스키마의 생성과 제거: SQL2에서는 동일한 데이터베이스 응용에 속하는 릴레이션, 도메인, 제약조건, 뷰, 권한 등을 그룹화하기 위해서 스키마 개념을 지원

``` CREATE SCHEMA MY_DB AUTHORIZATION kim; DROP SCHEMA MY_DB RESTRICT; DROP SCHEMA MY_DB CASCADE; ```

- 릴레이션 정의

CREATE TABLE EMPLOYEE
  (EMPNO  NUMBER  NOT NULL,
    EMPNAME CHAR(10),
    TITLE CHAR(10),
    MANAGER NUMBER,
    SALARY NUMBER,
    DNO   NUMBER,
    PRIMARY KEY(EMPNO),
    FOREIGN KEY(MANAGER)  REFERENCES EMPLOYEE(EMPNO),
    FOREIGN KEY(DNO)  REFERENCES EMPLOYEE(DENPNO));

  - data type
    - 가변길이 문자열은 내부 처리로 성능 저하될 수 있음
    - 키나 인덱스 컬럼 등은 고정길이로 공간이 크게 낭비되지 않으면 고정길이 문자열 사용

정의어

- 릴레이션 제거

DROP TABLE DEPARTMENT;

- ALTER TABLE

ALTER TABLE EMPLOYEE ADD PHONE CHAR(13);

- 인덱스 생성

CREATE INDEX EMPDNO_IDX ON EMPLOYEE(DNO);

- 제약조건

CREATE TABLE EMPLOYEE
  (EMPNO  NUMBER  NOT NULL,
  EMPNAME CHAR(10) UNIQUE, --중복불가
  TITLE CHAR(10) DEFAULT '사원',
  MANAGER NUMBER,
  SALARY NUMBER CHECK (SALARY <6000000),
  DNO   NUMBER CHECK (DNO IN (1,2,3,4,5,6)) DEFAULT 1,
PRIMARY KEY(EMPNO),
FOREIGN KEY(MANAGER)  REFERENCES EMPLOYEE(EMPNO),
FOREIGN KEY(DNO)  REFERENCES EMPLOYEE(DENPNO))
      ON DELETE CASCADE; -- 자동적으로 삭제됨

CREATE TABLE EMPLOYEE (
  ID NUMBER,
  NAME CHAR(10),
  SALARY NUMBER,
  MANAGER_SALARY NUMBER,
  CHECK (MANAGER_SALARY > SALARY));

- 참조 무결성 제약조건 유지

ON DELETE NO ACTION
ON DELETE CASCADE
ON DELETE SET NULL
ON DELETE SET DEFAULT
ON UPDATE NO ACTION

- 무결성 제약조건의 추가 및 삭제

ALTER TABLE STUDENT ADD CONSTRAINT STUDENT_PK
PRIMARY KEY (STNO);
ALTER TABLE STUDENT DROP CONSTRAINT STUDENT_PK;
  • 트랜잭션 제어
  • 데이터 제어어

원래 SQL에서는 데이터 조작어, 정의어, 제어어만 존재

sql


4. SELECT 문

  • SELECT문
    • 관계 데이터베이스에서 정보를 검색하는 SQL문
    • 관계 대수의 실렉션과 의미가 완전히 다름
    • 관계 대수의 실렉션, 프로젝션, 조인, 카티션 곱 등을 결합한 것
    • 관계 데이터베이스에서 가장 자주 사용됨
    • 여러 가지 질의들의 결과를 보이기 위해서 그림 4.8의 관계 데이터베이스 상태를 사용함

4_8

  • 기본적인 SQL 질의
    • SELECT절과 FROM절만 필수적인 절이고, 나머지는 선택 사항
SELECT  [DISTINCT] 애트리뷰트들
FROM    릴레이션들
[WHERE    조건
          [중첩 질의]]
[GROUP BY   애트리뷰트들]
[HAVING     조건]
[ORDER BY 애트리뷰트들 [ASC|DESC]];
  • 별칭(alias)
    • 서로 다른 릴레이션에 동일한 이름을 가진 애트리뷰트가 속해 있을 때 애트리뷰트의 이름을 구분하는 방법
EMPLOYEE.DNO
FROM EMPLOYEE AS E, DEPARTMENT AS D
  • 릴레이션의 모든 애트리뷰트나 일부 애트리뷰트들을 검색
SELECT *
FROM DEPARTMENT;
WHERE DNO = 2; -- 검색조건
  • 문자열 비교
  SELECT *
  FROM DEPARTMENT;
  WHERE EMPNAME LIKE '이%'; -- 검색조건
  • bool 연산자 사용
  SELECT EMPNAME, SALARY
  FROM EMPLOYEE;
  WHERE TITLE = '과장' AND DNO <> 2; -- 검색조건
  • 범위를 사용
  • 리스트를 사용

  • SELECT 절에서 산술 연산자 사용: +, -, * , /

  • 널값
    • 널값을 포함한 다른 값과 널값을 +, - 등을 사용하여 연산하면 결과는 널
    • COUNT( * )를 제외한 집단 함수들은 널값을 무시함
    • 어떤 애트리뷰트에 들어 있는 값이 널인가 비교하기 위해서 ‘DNO=NULL’처럼 나타내면 안됨: ‘DNO is NULL’
  • 세 가지 값의 논리 (Three valued logic)
    • true/false/unknown

연산

  • true = 1, false = 0, unknown = 0.5
  • C1 AND C2 = min(C1, C2)
  • C1 OR C2 = max(C1, C2)

  • NOT(C1) = 1 - C1

  • ORDER BY절
    • 사용자가 SELECT문에서 질의 결과의 순서를 명시하지 않으면 릴레이션에 투플들이 삽입된 순서대로 사용자에게 제시됨
    • ORDER BY절에서 하나 이상의 애트리뷰트를 사용하여 검색 결과를 정렬할 수 있음
    • SELECT문에서 가장 마지막에 사용되는 절
    • 디폴트 정렬 순서는 오름차순(ASC)
    • DESC를 지정하여 정렬 순서를 내림차순으로 지정할 수 있음
    • 널값은 오름차순에서는 가장 마지막에 나타나고, 내림차순에서는 가장 앞에 나타남
    • SELECT절에 명시한 애트리뷰트들을 사용해서 정렬해야 함
  • 집단 함수
    • 데이터베이스에서 검색된 여러 투플들의 집단에 적용되는 함수: COUNT, SUM, AVG, MAX, MIN
    • 한 릴레이션의 한 개의 애트리뷰트에 적용되어 단일 값을 반환함
    • SELECT절과 HAVING절에만 나타날 수 있음
    • COUNT( * )를 제외하고는 널값을 제거한 후 남아 있는 값들에 대해서 집단 함수의 값을 구함
    • COUNT( * )는 결과 릴레이션의 모든 행들의 총 개수를 구하는 반면에 COUNT(애트리뷰트)는 해당 애트리뷰트에서 널값이 아닌 값들의 개수를 구함
    • 키워드 DISTINCT가 집단 함수 앞에 사용되면 집단 함수가 적용되기 전에 먼저 중복을 제거함
  • 그룹화
    • GROUP BY절에 사용된 애트리뷰트에 동일한 값을 갖는 투플들이 각각 하나의 그룹으로 묶임
    • 이 애트리뷰트를 그룹화 애트리뷰트(grouping attribute)라고 함
    • 각 그룹에 대하여 결과 릴레이션에 하나의 투플이 생성됨
    • SELECT절에는 집단 함수, 그룹화 애트리뷰트들만 나타날 수 있음
    • 다음 질의는 그룹화를 하지 않은 채 EMPLOYEE 릴레이션의 모든 투플에 대해서 사원번호와 모든 사원들의 평균 급여를 검색하므로 잘못됨
SELECT EMPNO, AVG(SALARY)
FROM EMPLOYEE;
  • HAVING절
    • 어떤 조건을 만족하는 그룹들에 대해서만 집단 함수를 적용할 수 있음
    • 각 그룹마다 하나의 값을 갖는 애트리뷰트를 사용하여 각 그룹이 만족해야 하는 조건을 명시함
    • 그룹화 애트리뷰트에 같은 값을 갖는 투플들의 그룹에 대한 조건을 나타내고, 이 조건을 만족하는 그룹들만 질의 결과에 나타남
    • HAVING절에 나타나는 애트리뷰트는 반드시 GROUP BY절에 나타나거나 집단 함수에 포함되어야 함
  • 집합 연산
    • 집합 연산을 적용하려면 두 릴레이션이 합집합 호환성 을 가져야 함
    • UNION(합집합), EXCEPT(차집합), INTERSECT(교집합), UNION ALL(합집합), EXCEPT ALL(차집합), INTERSECT ALL(교집합)
  • 조인
    • 두 개 이상의 릴레이션으로부터 연관된 투플들을 결합
    • 일반적인 형식은 아래의 SELECT문과 같이 FROM절에 두 개 이상의 릴레이션들이 열거되고, 두 릴레이션에 속하는 애트리뷰트들을 비교하는 조인 조건이 WHERE절에 포함됨
    • 조인 조건은 두 릴레이션 사이에 속하는 애트리뷰트 값들을 비교 연산자로 연결한 것
    • 가장 흔히 사용되는 비교 연산자는 =
    • 조인 조건을 생략했을 때와 조인 조건을 틀리게 표현했을 때는 카티션 곱이 생성됨
    • 조인 질의가 수행되는 과정을 개념적으로 살펴보면 먼저 조인 조건을 만족하는 투플들을 찾고, 이 투플들로부터 SELECT절에 명시된 애트리뷰트들만 프로젝트하고, 필요하다면 중복을 배제하는 순서로 진행됨
    • 조인 조건이 명확해지도록 애트리뷰트 이름 앞에 릴레이션 이름이나 투플 변수를 사용하는 것이 바람직

    • 두 릴레이션의 조인 애트리뷰트 이름이 동일하다면 반드시 애트리뷰트 이름 앞에 릴레이션 이름이나 투플 변수를 사용해야 함

    • 자체 조인(self join)
      • 한 릴레이션에 속하는 투플을 동일한 릴레이션에 속하는 투플들과 조인하는 것
      • 실제로는 한 릴레이션이 접근되지만 FROM절에 두 릴레이션이 참조되는 것처럼 나타내기 위해서 그 릴레이션에 대한 별칭을 두 개 지정해야 함
  • 중첩 질의(nested query)
    • 질의의 WHERE 또는 FROM절에 다시 ‘(SELECT … FROM … WHERE …)’ 형태로 포함된 SELECT문 (괄호로 묶어서 표시함)
    • 부질의(subquery)라고 함
    • 중첩 질의를 포함하는 질의를 외부 질의라고 부름
    • INSERT, DELETE, UPDATE문에도 사용될 수 있음
    • 중첩 질의의 결과는 세 가지 경우가 있음
      • 한 개의 스칼라값(단일 값)
      • 한 개의 애트리뷰트로 이루어진 릴레이션
      • 여러 애트리뷰트로 이루어진 릴레이션
  • 한 개의 스칼라값이 반환되는 경우
    • 스칼라(scala): 컬럼 값으로 사용될 수 있는 원자 값 (atomic value)
    • WHERE 절에서 상수 또는 애트리뷰트가 사용될 위치에 나타날 수 있음
  • 한 개의 애트리뷰트로 이루어진 릴레이션이 반환되는 경우
  • 중첩 질의의 결과로 한 개의 애트리뷰트로 이루어진 다수의 투플들이 반환될 수 있음
  • 외부 질의의 WHERE절에서 IN, ANY(SOME), ALL, EXISTS와 같은 연산자를 사용해야 함
    • IN: 한 애트리뷰트가 값들의 집합에 속하는가를 테스트
    • ANY: 한 애트리뷰트가 값들의 집합에 속하는 하나 이상의 값들과 어떤 관계를 갖는가를 테스트
    • ALL: 한 애트리뷰트가 값들의 집합에 속하는 모든 값들과 어떤 관계를 갖는가를 테스트
    • EXISTS: 중첩 질의의 결과가 빈 릴레이션인지 여부를 검사함
      • 중첩 질의의 결과가 빈 릴레이션이 아니면 참이 되고, 그렇지 않으면 거짓
  • 여러 애트리뷰트들로 이루어진 릴레이션이 반환되는 경우
    • 단일 애트리뷰트들로 이루어진 릴레이션이 반환되는 경우와 마찬가지로 IN, ANY, ALL, EXISTS 중에 하나를 사용하여 프레디킷을 만들어 사용할 수 있음
    • IN, ANY, ALL을 사용하는 경우에는 결과 릴레이션과 호환되는 투플 구조를 갖는 투플을 사용해서 비교해야 함
  SELECT EMPNAME
  FROM EMPLOYEE E
  WHERE SALARY =< 1500000 AND (E.TITLE, E.DNO) IN
  (SELECT TITLE, DNO
  FROM EMPLOYEE
  WHERE SALARY > 1500000
  );
  • 상관 중첩 질의(correlated nested query)
    • 중첩 질의의 WHERE절에 있는 프레디키트에서 외부 질의에 선언된 릴레이션의 일부 애트리뷰트를 참조하는 질의
    • 중첩 질의의 수행 결과가 단일 값이든, 하나 이상의 애트리뷰트로 이루어진 릴레이션이든 외부 질의로 한 번만 결과를 반환하면 상관 중첩 질의가 아님
    • 상관 중첩 질의에서는 외부 질의를 만족하는 각 투플이 구해진 후에 중첩 질의가 수행됨
    • 상관 중첩 질의는 외부 질의를 만족하는 투플 수만큼 여러 번 수행될 수 있음
  • FROM 절에 사용된 중첩 질의
    • FROM 절에 저장된 일반 테이블과 함께 중첩 질의를 사용할 수 있음
    • 중첩 질의는 테이블 이름이 없으므로 alias를 사용하여 이름 부여
  SELECT EMPNAME, DEPTNAME
  FROM EMPLOYEE E, (SELECT DEPTNO, DEPTNAME FROM DEPARTMENT) D
  WHERE E.DNO = D.DEPTNO AND TITLE = ‘과장’;


5. INSERT, DELETE, UPDATE

  • INSERT문
    • 기존의 릴레이션에 투플을 삽입
    • 참조되는 릴레이션에 투플이 삽입되는 경우에는 참조 무결성 제약조건의 위배가 발생하지 않으나 참조하는 릴레이션에 투플이 삽입되는 경우에는 참조 무결성 제약조건을 위배할 수 있음
    • 릴레이션에 한 번에 한 투플씩 삽입하는 것과 한 번에 여러 개의 투플들을 삽입할 수 있는 것으로 구분
    • 릴레이션에 한 번에 한 투플씩 삽입하는 INSERT문
    INSERT
INTO 릴레이션 (애트리뷰트 1, ..., 애트리뷰트 n)
VALUES (값1, ...,값n);

예시

  • 릴레이션에 한 번에 여러 개의 투플들을 삽입하는 INSERT문
INSERT
INTO 릴레이션 ( 애트리뷰트1, ..., 애트리뷰트n)
SELECT ... FROM ... WHERE ...;
  • DELETE문
    • 한 릴레이션으로부터 한 개 이상의 투플들을 삭제함
    • 참조되는 릴레이션의 삭제 연산의 결과로 참조 무결성 제약조건이 위배될 수 있으나, 참조하는 릴레이션에서 투플을 삭제하면 참조 무결성 제약조건을 위배하지 않음
    • DELETE문의 구문
    DELETE
FROM 릴레이션
WHERE 조건;
  • UPDATE문
    • 한 릴레이션에 들어 있는 투플들의 애트리뷰트 값들을 수정
    • 기본 키나 외래 키에 속하는 애트리뷰트의 값이 수정되면 참조 무결성 제약조건을 위배할 수 있음
    • UPDATE문의 구문
    UPDATE 릴레이션
SET 애트리뷰트
WHERE 조건;


6. trigger와 assertion

  • 트리거
    • 명시된 이벤트(데이터베이스의 갱신)가 발생할 때마다 DBMS가 자동적으로 수행하는, 사용자가 정의하는 문(프로시저)
    • 데이터베이스의 무결성을 유지하기 위한 일반적이고 강력한 도구
    • 테이블 정의시 표현할 수 없는 기업의 비즈니스 규칙들을 시행하는 역할
    • 트리거 표현 요소
      • 트리거를 활성화시키는 사건인 이벤트 (event)
      • 트리거가 활성화되었을 때 수행되는 테스트인 조건 (condition)
      • 트리거가 활성화되고 조건이 참일 때 수행되는 문(프로시저)인 동작 (action)
    • 트리거를 이벤트-조건-동작(ECA) 규칙이라고도 부름 E는 Event, C는 Condition, A는 Action 을 의미
    • SQL3 표준에 포함되었으며 대부분의 상용 관계 DBMS에서 제공됨
    • SQL3에서 트리거의 형식
    CREATE TRIGGER <트리거이름>
AFTER <트리거를 유발하는 이벤트들이 OR로 연결된 리스트> ON <릴레이션>
[WHEN <조건>]
BEGIN <SQL문> END
    • 이벤트의 가능한 예로는 테이블에 투플 삽입, 테이블로부터 투플 삭제, 테이블의 투플 수정 등이 있음
    • 조건은 임의의 형태의 프레디키트
    • 동작은 데이터베이스에 대한 임의의 갱신
    • 어떤 이벤트가 발생했을 때 조건이 참이 되면 트리거와 연관된 동작이 수행되고, 그렇지 않으면 아무 동작도 수행되지 않음
    • 삽입, 삭제, 수정 등이 일어나기 전(before)에 동작하는 트리거와 일어난 후(after)에 동작하는 트리거로 구분
    • 연쇄적으로 활성화되는 트리거
      • 하나의 트리거가 활성화되어 이 트리거 내의 한 SQL문이 수행되고, 그 결과로 다른 트리거를 활성화하여 그 트리거 내의 SQL문이 수행될 수 있음

예시

  • 주장 (ASSERTION)
    • SQL3에 포함되어 있으나 대부분의 상용 관계 DBMS가 아직 지원하고 있지 않음
    • 트리거는 제약조건을 위반했을 때 수행할 동작을 명시하는 것이고, 주장은 제약조건을 위반하는 연산이 수행되지 않도록 함
    • 주장의 구문
    CREATE ASSERTION
CHECK 조건;
    • 트리거보다 좀더 일반적인 무결성 제약조건
    • DBMS는 주장의 프레디키트를 검사하여 만일 참이면 주장을 위배하지 않는 경우이므로 데이터베이스 수정이 허용됨
    • 일반적으로 두 개 이상의 테이블에 영향을 미치는 제약조건을 명시하기 위해 사용됨
    • 대부분의 주장은 NOT EXISTS를 포함
    • 주장에는 “모든 x가 F를 만족한다”를 이와 동치인 “ㄱF를 만족하는 x가 존재하지 않는다＂로 표시


7. 내포된 SQL

  • 내포된 SQL(embedded SQL)
    • SQL이 호스트 언어의 완전한 표현력을 갖고 있지 않기 때문에 모든 질의를 SQL로 표현할 수는 없음
    • SQL은 호스트 언어가 갖고 있는 조건문(IF문), 반복문(WHILE문), 입출력 등과 같은 동작, 사용자와의 상호 작용, 질의 결과를 GUI로 보내는 등의 기능을 갖고 있지 않음
    • C, C++, 코볼, 자바 등의 언어로 작성하는 프로그램에 SQL문을 삽입하여, 데이터베이스를 접근하는 부분을 SQL이 맡고 SQL에 없는 기능은 호스트 언어로 작성하는 것이 필요
    • 호스트 언어에 포함되는 SQL문을 내포된 SQL이라 부름
  • 데이터 구조가 불일치하는 문제 (impedance mismatch 문제)

SQL

  • Pro*C
    • 오라클에서 C 프로그램에 SQL 문을 내포시키는 방법
    • 내포된 SQL 문이 포함된 소스파일의 확장자는 .pc
    • .pc 파일을 Pro*C를 통하여 전컴파일하면 .c인 소스 프로그램이 생성됨
    • 윈도우7 환경에서 Pro*C를 실습하려면 비주얼 스튜디오 6.0 등의 통합 개발 환경이 필요
  • 호스트 변수 (host variable)
    • SQL문에 포함된 C 프로그램의 변수
    • 호스트 언어와 SQL 문 사이에 통신을 위해 사용
    • SQL 문에 사용될 데이터 값을 입력하거나
    • SQL 문의 결과를 출력
    • 호스트 변수를 SQL 문에서 사용할 때 콜론(:)을 붙여서 사용
    • DECLARE SECTION을 이용해서 선언
    • Oracle은 DECLARE SECTION을 사용하지 않는 것도 지원 (표준은 아님)
EXEC SQL BEGIN DECLARE SECTION;
  int   no;
  varchar title[10];
EXEC SQL END DECLARE SECTION;
  • 정적인 SQL문
    • C 프로그램에 내포된 완전한 SQL문
    • 입력값과 출력 데이터를 위해서 C 프로그램의 변수들을 포함할 수 있음
  • 동적인 SQL문
    • 불완전한 SQL문으로서 일부 또는 전부를 질의가 수행될 때 입력 가능
    • 응용을 개발할 때 완전한 SQL문의 구조를 미리 알고 있지 않아도 됨
    • 문자열 형 변수에 담아서 표현함으로써 동적으로 변경 가능
    • 컴파일 시점에 SQL 문을 알지 못함

key

  • 불일치 문제와 커서
    • 호스트 언어는 단일 변수/레코드 위주의 처리(투플 위주의 방식)를 지원하는 반면에 SQL은 데이터 레코드들의 처리(집합 위주의 방식)를 지원하기 때문에 불일치 문제가 발생함
    • 불일치 문제를 해결하기 위해서 커서(cursor)가 사용됨
    • 두 개 이상의 투플들을 검색하는 SQL문에 대해서는 반드시 커서를 선언하고 사용해야 함
    • 커서는 한 번에 한 투플씩 가져오는 수단
  • 커서
    • DECLARE CURSOR문을 사용하여 커서를 정의함
    • OPEN cursor문은 질의를 수행하고, 질의 수행 결과의 첫 번째 투플 이전을 커서가 가리키도록 한다. 이 것이 커서의 현재 투플
    • 그 다음에 FETCH문은 커서를 다음 투플로 이동하고, 그 투플의 애트리뷰트 값들을 FETCH문에 명시된 호스트 변수들에 복사함
    • CLOSE cursor는 커서를 닫음
  • 여러 투플을 읽어 오기 위해서는 루프 내의 FETCH문 사용
  EXEC SQL WHENEVER NOT FOUND GOTO NotFoundLabel;
  for (;;) {
  …
  EXEC SQL FETCH …;
  …
  }
  …
  NotFoundLabel:
  …
  • 루프 종료 조건: 결과 집합이 비었거나 더 이상의 가져올 투플이 없으면 FETCH문은 ‘no data found’ 에러를 발생시키고, WHENEVER NOT FOUND가 이를 인지함

  • SQLCODE를 검사하는 방법을 사용할 수도 있음

  • WHENEVER
    • 자동적인 에러 검사와 에러 처리를 위한 구문
    • 구문: WHENEVER <조건> <동작>
      • <조건>
        • NOT FOUND: WHERE절을 만족하는 투플이 없거나, SELECT INTO 혹은 FETCH가 row를 리턴하지 않음
        • SQLERROR: 에러가 발생한 경우
        • SQLWARNING: 경고가 발생한 경우
      • <동작>: CONTINUE, GOTO, STOP, DO
        • CONTINUE: WHENEVER 문을 사용하지 않는 것과 같은 효과
        • DO {function call CONTINUE BREAK}: 프로그램 제어를 이동
        • STOP: 프로그램 종료. COMMIT되지 않은 WORK은 ROLLBACK
    • CURSOR를 이용한 UPDATE
      • 커서의 현재 투플을 업데이트하려면 CURRENT OF 절을 사용하면 됨
      • CURSOR를 선언할 때 FOR UPDATE OF 키워드를 선택적으로 추가
        • 개발자가 추가하지 않아도, UPDATE 또는 DELETE 문에 CURRENT OF 절이 나오면 전처리기가 필요 시 추가함
      • 제약:
        • organized 테이블에는 사용금지. 한 테이블의 애트리뷰트들만 수정 가능
      EXEC SQL DECLARE title_cursor CURSOR FOR
SELECT title FROM EMPLOYEEWHERE empname = :name
FOR UPDATE OF title;
…
EXEC SQL FETCH title_cursor INTO :title;
EXEC SQL UPDATE EMPLOYEE SET title = ‘상무’
WHERE CURRENT OF title_cursor;
  • SQL 통신 영역(SQLCA: SQL Communications Area)
    • C 프로그램에 내포된 SQL문에 발생하는 에러들을 사용자에게 알려줌
    • 사용자는 SQLCA 데이터 구조(SQLCA.H)의 에러 필드와 상태 표시자를 검사하여 내포된 SQL문이 성공적으로 수행되었는가 또는 비정상적으로 수행되었는가를 파악할 수 있음
    • SQLCA 데이터 구조 중에서 가장 중요하고 널리 사용되는 필드는 sqlcode 멤버 변수
    • sqlcode의 값이 0이면 마지막에 내포된 SQL문이 성공적으로 끝났음을 의미
    • SQLCA를 사용하기 위해서는 아래와 같은 문장을 포함해야 함
    EXEC SQL INCLUDE SQLCA.H
; 또는
#include <sqlca.h>
  • 오라클 통신 영역(ORACA: Oracle Communications Area)
    • SQLCA라는 SQL 표준을 오라클에서 확장한 구조체
    • sqlca에서 얻을 수 있는 정보 외에 추가로 필요한 정보를 호스트 프로그램에게 제공하기 위한 구조체

sqlcode

  • 에러 메시지: sqlglm() 함수 사용
    • 에러가 난 것을 먼저 확인하고 사용해야 함
    • WHENEVER 사용 또는 SQLCODE (or sqlca.sqlcode) 값이 0이 아닐 때
EXEC SQL WHENEVER SQLERROR DO sql_error();
...
-- other statements
…
Sql_error()
{
char msg[200];
size_t buf_len, msg_len;
buf_len = sizeof (msg);
sqlglm(msg, &buf_len, &msg_len); -- note use of pointers
printf("%.*s\n\n", msg_len, msg);
exit(1);
}
  • SQLSTATE 상태 변수
    • SQL92 표준에서는 Embedded SQL의 에러 처리를 위해 SQLSTATE 변수를 도입
      • 이전에는 SQLCODE를 사용함 (SQL92에서 deprecated 됨)
      • Oracle의 전처리기 옵션에서 mode=ANSI 로 하여 사용 가능
        • mode=ORACLE이면 SQLCA가 사용됨
    • 예외의 유형을 구분하는 CLASS CODE (2자리 문자)와 구체적인 예외를 나타내는 SUBCLASS CODE (3자리 문자)의 총 5자리 문자의 값을 가짐
      • 각 자리는 0..9 또는 A..Z
    • 선언(declaration) 필요: char SQLSTATE[6];
    • 참조: https://docs.oracle.com/cd/B10501_01/appdev.920/a97269/pc_09err.htm
  • 지시 변수 (indicator variables)
    • NULL 값 여부 등 호스트 변수에 대한 추가적인 정보 제공
    • 2바이트 정수로 표현
    • 호스트 변수 바로 다음에 지시 변수를 선택적으로 추가
      • 지시 변수도 앞에 콜론(:) 붙임
      • 호스트 변수와 지시 변수 사이에 선택적으로 INDICATOR 키워드 사용 가능
      short indicator_var;
EXEC SQL SELECT xyz INTO :host_var:indicator_var FROM ...;
EXEC SQL INSERT INTO R VALUES(:host_var INDICATOR :indicator_var, ...);
지시 변수 해석: SELECT INTO 문 INSERT/UPDATE
-1 (호스트 변수 값이) NULL -1 (호스트 변수 값이) NULL
0 intact (온전한) value >=0 normal value
>0 truncated value, 원래 길이 저장  
-2 truncated value, 원래 길이 모름