관계 데이터베이스 릴레이션을 정의, 관계 데이터베이스에서 정보를 검색, 관계 데이터베이스 갱신, 여러 가지 무결성 제약조건 명시
4.1. 관계 대수
기존의 릴레이션들로부터 새로운 릴레이션을 생성
릴레이션이나 관계 대수식(이것의 결과도 릴레이션)에 연산자 적용 -> 복잡한 관계 대수식을 점차적으로 생성
기본적인 연산자들의 집합으로 구성
산술 연산자와 유사하게 단일 릴레이션이나 두 개의 릴레이션을 입력으로 받아 -> 하나의 결과 릴레이션을 생성
결과 릴레이션은 또 다른 관계 연산자의 입력으로 사용될 수 있음
Selection (형식: σ<selection condition>(relation))
한 realtion에서 selection condition을 만족하는 tuple들의 부분집합을 생성
단항 연산자
결과 relation의 차수 = 입력 relation의 차수와 같음
결과 relation의 카디날리티 <= 입력 relation의 카디날리티
selection condition을 predicate라고도 함
selection condition은 일반적으로 relation의 임의의 attribute와 constant, =, <>(!=), <=, <, >=, > 등의 비교 연산자, AND, OR, NOT 등의 bool 연산자를 포함할 수 있음
Projection 연산자 (형식: ∏<attribute list>(relation))
한 relation의 attributes의 부분 집합을 구함
결과로 생성되는 relation은 <attributes list>에 명시된 attributes만 가짐
selection의 결과 relation에는 중복 tuple이 존재할 수 없지만, projection 연산의 결과 relation에는 중복된 tuple이 존재할 수 있음
relation은 모든 튜플이 고유해야 하는데, attribute 범위가 좁아지면 고유함이 깨져서 realtion이 될 수 없음 -> 기존의 관계 데이터베이스는 중복 제거
최근의 (확장된) 관계 데이터베이스에서는 디폴트로 중복 제거되지 않은 형태를 제공
why? -> 중복을 제거하려면 많은 시간과 비용 발생 -> 대규모의 데이터는 메모리가 아닌 디스크에 저장 -> 디스크에 있는 데이터를 다루려면, 똑같은 시간복잡도를 가져도 메모리의 데이터를 다루는 것보다 시간이 훨씬 오래 걸린다 -> 중복을 제거하는 과정에서 필요한 정렬 연산의 비용이 많이 발생 -> 꼭 필요하지 않으면 일단은 중복 제거는 하지 않은 형태로 제공한다.
집합 연산자
relation이 tuple의 집합이기 때문에 기존의 집합 연산이 relation에 적용됨
합집합, 교집합, 차집합 연산자
집합 연산자의 입력으로 사용되는 두 개의 relation은 합집합 호환(union compatible)이어야 함
이항 연산자
합집합 호환
두 relation R1(A1, A2, ..., An)과 R2(B1, B2, ..., Bm)이 합집합 호환일 필요 충분 조건 -> n = m, 모든 1 <= i <= n에 대해 domain(A_i) = domain(B_i)
두 릴레이션의 스키마가 서로 호환돼야 한다.
두 릴레이션에 있던 스키마가 하나의 릴레이션으로 들어감 -> 차수가 같아야 하며, 대응되는 attribute의 도메인이 일치해야 한다.(string과 int는 호환 x!) -> domain을 고려할 때 data type, 값 범위, 제한 조건 등이 있는데, 여기서 data type 정도만 고려하면 된다. -> 어차피 새로운 relation이 만들어지기 때문
합집합 연산자 (형식: relation1 ∪ relation2)
결과 relation에서 중복된 tuples는 제외
교집합 연산자
차집합 연산자 (형식: relation1 - relation2)
카티션 곱 연산자 (형식: R x S)
카디날리티가 n인 relation R(A1, A2, ..., An)과 카디날리티가 m인 relation S(B1, B2, ..., Bm)의 카티션 곱 R x S는 차수가 n+m이고, 카디날리티가 n*m이며, attribute가 (A1, A2, ..., An, B1, B2, ...,Bm)이며, R과 S의 tuples의 모든 가능한 조합으로 이루어진 relation
관계 대수의 완전성
selection, projection, 합집합, 차집합, 카티션 곱은 관계 대수의 필수적인 연산자
다른 관계 연산자들은 필수적인 관계 연산자들 두 개 이상 조합하여 표현 가능
임의의 질의어가 적어도 필수적인 관계 대수 연산자들만큼의 표현력을 갖고 있으면 관계적으로 완전(relationally complete)하다고 말함
조인 연산자
두 개의 relation으로부터 연관된 tuples를 결합하는 연산자
관계 데이터베이스에서 두 개 이상의 relations의 관계를 다루는데 매우 중요한 연산자
세타 조인(theta join), 동등 조인(equijoin), 자연 조인(natural join), 외부 조인(outer join), 세미 조인(semi join) 등
세타(Θ) 조인 (형식: R⋈<join condition>S)
조인 조건은 R.A_i Θ R.B_j의 형태로 주어지며 Θ는 {=, <>, <=, <, >=, >} 중의 하나
세타 조인 결과는 두 relation의 카티션 곱에 join condition을 적용한 결과와 동일
동등 조인
세타 조인의 비교 연산자가 =인 조인
자연 조인 (형식: R * S)
두 relation의 공통된 attribute에 대해 동등 조인을 수행하고, 중복되는 attribute 중 하나를 제외한 조인
join 연산자 중 가장 자주 사용됨
디비전 연산자 (형식: R ÷ S)
차수가 n+m인 relation R(A1, A2, .., An, B1, B2, ..., Bm)과 차수가 m인 relation S(B1, B2, ..., Bm)의 디비전 R ÷ S -> 차수가 n, S에 속하는 모든 tuple u에 대해서 tuple tu가 R에 존재하는 tuple t들의 집합
모든 ...에 대해 ~하는
관계 대수의 한계
관계 대수는 산술 연산을 할 수 없음
집단 함수(aggregate function)를 지원하지 않음 -> 여러 개의 값을 받아서 하나의 통계 값을 출력
정렬을 나타낼 수 없음
데이터베이스를 수정할 수 없음
프로젝션 연산 결과에 중복된 tuple을 나타내는 것이 필요할 때가 있는데, 이를 명시하지 못함
-> 이런 한계점들을 보완한 것이 확장 관계대수
추가된 관계 대수 연산자
집단(aggregation) 함수: AVG, SUM, MIN, MAX, COUNT
그룹화: 각 그룹에 대해 집단 함수를 적용
외부 조인
두 relation에서 대응되는 tuple들을 결합하면서, 대응되는 tuple을 갖지 않는 tuple과 조인 attribute에 널값을 갖는 tuple도 결과에 포함시킴
SQL은 현재 DBMS 시장에서 관계 DBMS가 압도적인 우위를 차지하는 중요한 요인 중 하나
SQL은 IBM 연구소에서 1974년에 System R이라는 관계 DBMS 시제품을 연구할 때 관계 대수와 관계 해석을 기반으로 집단 함수, 그룹화, 갱신 연산 등을 추가하여 개발된 언어
1986년에 ANSI(미국 표준 기구)에서 SQL 표준을 채택함으로써 SQL이 널리 사용되는데에 기여
본 책에서는 SQL2를 따름
관계 데이터 모델은 집합을 기반으로 두고 있어 테이블 내에 동일판 tuple을 허용하지 않지만, SQL은 이를 허용
SQL은 비절차적 언어(선언적 언어)이므로 사용자는 자신이 원하는 바(what)만 명시하며, 원하는 것을 처리하는 방법(how)은 명시할 수 없음
관계 DBMS: 사용자가 입력한 SQL문을 번역 -> 사용자가 요구한 데이터 탐색에 필요한 모든 과정을 담당
자연어에 가까운 구문을 사용하여 질의를 표현
두 가지 인터페이스 -> 대화식 SQL(interactive SQL), 내포된 SQL(embedded SQL)
오라클 SQL의 구성요소
데이터 검색
데이터 조작어
데이터 정의어
트랜잭션 제어
데이터 제어어
4.3. 데이터 정의어와 무결성 제약조건
데이터 정의어
스키마의 생성과 제거
SQL2에서는 동일한 데이터베이스 응용에 속하는 realtion, domain, constraint(제약조건), view, grant(권한) 등을 그룹화하기 위해서 스키마 개념을 지원
릴레이션 정의
DEPARTMENT relation과 EMPLOYEE relation의 생성
Empty string은 NULL과 다르다 -> 현재 SQL 표준과 맞지 않음 -> VARCHAR2 사용 권장
릴레이션 제거
DROP TABLE DEPARTMENT;
ALTER TABLE - 스키마 변경
ALTER TABLE EMPLOYEE ADD PHONE CHAR(13);
인덱스 생성
하나의 테이블 안에서만 만들 수 있음
CREATE INDEX EMPDNO_IDX ON EMPLOYEE(DNO);
EMP(first name, last name) -> EMP(first name, last name) -> 두 개의 attribute로 인덱스를 만들 수 있음 (attribute list)
-> 이름을 설정할 수도, 설정하지 않을 수도 있다 -> 설정하지 않는 경우에는 삭제가 불가능함 -> 카탈로그에 접근해서 삭제할 수 있긴 하나, 정상적이 루트는 아님
참조 무결성 제약조건 유지
-> ON DELETE가 없으면 Default값(NO ACTION) 설정
무결성 제약조건의 추가 및 삭제
-> STUDENT_PK: constraint 이름 -> 제약조건 변경 시 사용
4.4. SELECT문
Data Manipulationo Language -> 데이터 조작어
관계 데이터베이스에서 정보를 검색하는 SQL문
관계 대수의 SELECTION, PROJECTION, JOIN, CARTESIAN PRODUCT 등을 결합한 것
관계 데이터베이스에서 가장 자주 사용
아래 예시에서 계속 사용할 예정
기본적인 SQL 질의
SELECT절과 FROM절만 필수, 나머지는 선택
-> DISTINCT: 하나의 attribute 뿐만 아니라 tuple을 고유하게
별칭(alias)
서로 다른 relation에 동일한 이름을 가진 attribute가 속해있을 때, attributes의 이름을 구분하는 방법
Relation의 모든 Attributes나 일부 Attributes를 검색
SELECT * FROM DEPARTMENT; -> 모든 attributes
SELECT DEPTNO, DEPTNAME FROM DEPARTMENT; -> projection
상이한 값들을 검색
SELECT DISTINCT TITLE FROM EMPLOYEE; -> 중복 제거한 projection
SELECT DISTINCT TITLE, DNO FROM EMPLOYEE -> DISTINCT 뒤에 오는 모든 컬럼에 대해 하나의 행으로 인식해 중복 제거
https://bio-info.tistory.com/110
특정한 Tuples의 검색
SELECT * FROM EMPLOYEE WHERE DNO = 2; -> selection
문자열 비교
와일드 문자 %: 임의 길이의 문자열과 매칭 -> 이% -> 이수민, 이성래, 이정재, 이수, 이카루스.. 길이 상관 없이 뭐든 될 수 있음
와일드 문자 _: 길이 제한 -> 이_ -> 이수, 이상, but 이수민, 이카루스는 안 됨
다수의 검색 조건
SELECT FLOOR FROM DEPARTMENT WHERE DEPTENAME = '영업' AND DEPTNAME = '개발';
그러나, 위 예제는 잘못됨 -> 부서 이름이 '영업'이면서 '개발'인 경우는 의미적으로 잘못 작성됨 -> 이런 경우는 DBMS에서 자체적으로 체크하지 못함
연산자 우선순위: 비교 연산자 > NOT > AND > OR
부정 검색 조건
<> -> !=
범위를 사용한 검색
SELECT EMPNAME, TITLE, SALARY
FROM EMPLOYEE
WHERE SALARY BETWEEN 3000000 AND 4500000;
SELECT EMPNAME, TITLE, SALARY
FROM EMPLOYEE
WHERE SALARY >= 3000000 AND SALARY <= 4500000;
-> between은 inclusive 포함 -> between으로는 등호가 들어가지 않은 부등식 표현 불가능
리스트를 사용한 검색
SELECT *
FROM EMPLOYEE
WHERE DNO IN (1, 3);
-> DNO = 1 OR DNO = 3과 같음
SELECT절에서 산술 연산자(+, -, *, /) 사용
SELECT EMPNAME, SALARY, SALARY * 1.1 AS NEWSALARY
FROM EMPLOYEE
WHERE TITLE = '과장';
-> AS문을 안 쓰면 attribute 이름이 그냥 SALARY * 1.1로 표시됨
널값
널값을 포함한 산술식의 결과 = NULL
COUNT(*)를 제외한 집단 함수들은 널값을 무시
어떤 attribute에 들어 있는 값이 NULL인가 비교하기 위해서 'DNO = NULL'처럼 나타내면 안 됨 -> IS NULL이 맞는 표현
SELECT EMPNO, EMPNAME
FROM EMPLOYEE
WHERE DNO IS NULL;
사용자가 SELECT문에서 질의 결과의 순서를 명시하지 않으면 사용자에게 제시되는 순서가 정해져 있지 않음
디폴트 정렬 순서는 오름차순(ASC)
NULL값은 오름차순에서 가장 마지막에 나타나고, 내림차순에서는 가장 앞에 나타남
SELECT절에 명시한 attributes를 사용해서 정렬해야 했으나, 최근 SQL에서는 SELECT 절에 나오지 않은 attributes도 허용
SELECT SALARY, TITLE, EMPNAME
FROM EMPLOYEE
WHERE DNO = 2
ORDER BY SALARY;
SELECT SALARY, TITLE, EMPNAME
FROM EMPLOYEE
WHERE DNO = 2
ORDER BY EMPNAME ASC, AGE DESC;
-> 오름차순이 default
-> ORDER BY에 list가 오는 경우 -> 사원 이름을 기준으로 오름차순 출력 -> 사원 이름이 같으면 나이를 기준으로 내림차순
집단 함수(집계 함수)
데이터베이스에서 검색된 여러 tuple들의 집단에 적용되는 함수
한 개의 attribute 혹은 표현식에 적용되어 단일 값을 반환
SELECT절과 HAVING절에만 나타날 수 있음
COUNT(*)를 제외하고는 널값을 제거한 후 남아있는 값들에 대해서 집단 함수의 값을 구함 -> COUNT(attribute)는 널값을 제외한 값들의 개수를 구함
DISTINCT가 집단 함수 앞에 사용되면, 집단 함수가 적용되기 전에 먼저 중복 제거
SELECT AVG(SALARY) AS AVGSAL, MAX(SALARY) AS MAXSAL
FROM EMPLOYEE;
그룹화
GROUP BY 절에 사용된 attribute 혹은 표현식에 동일한 값을 갖는 tuple들이 각각 하나의 그룹으로 묶임
그룹화에 사용된 attribute를 그룹화 애트리뷰트(grouping attribute)라고 한다.
각 그룹에 대해 결과 relation에 하나의 tuple이 생성됨
SELECT절에는 집단 함수, 그룹화 애트리뷰트들만 나타날 수 있음
SELECT EMPNO, AVG(SALARY)
FROM EMPLOYEE;
-> 그룹화를 하지 않은 채 EMPLOYEE relation의 모든 tuple에 대해서 사원번호와 모든 사원들의 평균 급여를 검색하므로 잘못됨
SELECT DNO, AVG(SALARY) AS AVGSAL, MAX(SALARY) AS MAXSAL
FROM EMPLOYEE
GROUP BY DNO;
γ(Grouping operation) 1. A, B에 대해서 grouping하고, 해당 Group에서 Max(C) 구한 후, MAX(C)를 attribute로하는 relation 반환 1. B에 대해서 grouping하고, 해당 Group에서 SUM(C) 구한 후, SUM(C)를 attribute로하는 relation 반환 1. A에 대해서 grouping하고, 해당 Group에서 Min(B) 구한 후, Min(B)를 attribute로하는 relation 반환
HAVING절
어떤 조건을 만족하는 그룹들에 대해서만 집단 함수를 적용
각 그룹마다 하나의 값을 갖는 attribute를 사용하여 각 그룹이 만족해야 하는 조건을 명시
그룹화 attribute에 같은 값을 갖는 tuple들의 그룹에 대한 조건을 나타내고, 이 조건을 만족하는 그룹들만 질의 결과에 나타남
HAVIN절에 나타나는 attribute는 반드시 GROUP BY절에 나타나거나 집단 함수에 포함되어야 함
SELECT DNO, AVG(SALARY) AS AVGSAL, MAX(SALARY) AS MAXSAL
FROM EMPLOYEE
GROUP BY DNO
HAVING AVG(SALARY) >= 2500000;
-> 평균 급여가 2500000 이상인 부서 -> 절대 WHERE절로 표현 X -> where절은 개별 tuple에 조건을 주는 것, having절은 그룹(집단)에 대해 조건을 주는 것
집합 연산
두 relation이 합집합 호환성을 가져야 함
UNION, EXCEPT, INTERSECT -> 중복 허용 합집합, 차집합, 교집합
UNION ALL, EXCEPT ALL, INTERSECT ALL -> 중복 허용 합집합, 차집합, 교집합
(SELECT DNO
FROM EMPLOYEE
WHERE EMPNAME = '김창섭')
UNION
(SELECT DEPTNO
FROM DEPATMENT
WHERE DEPTNAME = '개발');
조인
조인 조건을 생략, 조인 조건을 틀리게 표현 -> 카티션 곱 생성
SELECT EMPNAME, DEPTNAME
FROM EMPLOYEE AS E, DEPARTMENT AS D
WHERE E.DNO = D.DEPTNO;
-> 모든 combination 생성하여 찾아내는 방식은 비효율적 -> Search structure -> 왼쪽 DNO를 일종의 키값으로 생각해서 일치하는 value 빠르게 탐색
자체 조인(self join)
한 relation에 속하는 tuple을 동일한 relation에 속하는 tuple들과 조인
하나의 릴레이션을 사용하지만, 해당 릴레이션에 두 개의 별칭을 붙여야 함
SELECT E.EMPNAME, M.EMPNAME
FROM EMPLOYEE E, EMPLOYEE M
WHERE E.MANAGER = M.EMPNO;
SELECT DEPTNAME, EMPNAME, TITLE, SALARY
FROM EMPLOYEE E, DEPARTMENT D
WHERE E.DNO = D.DEPTNO
ORDER BY DEPTNAME, SALARY DESC;
중첩 질의(nested query)
질의의 WHERE 혹은 FROM 절에 다시 '(SELECT ... FROM ... WHERE ...)'형태로 포함된 SELECT문
부질의(subquery)
중첩 질의를 포함하는 질의를 외부 질의라고 부름
INSERT, DELETE, UPDATE문에도 사용될 수 있음
줄첩 질의의 결과는 다음 세 가지 경우
한 개의 스칼라값(단일 값)
스칼라(scala): 컬럼 값으로 사용될 수 있는 원자값
WHERE 절에서 상수 도는 애트리뷰트가 사용될 위치에 나타날 수 있음
SELECT empname, title
FROM employees
WHERE title = (
SELECT title
FROM employees
WHERE empname = '박영권'
)
한 개의 attribute로 이루어진 realtion
중첩 질의의 결과로 한 개의 애트리뷰트로 이루어진 다수의 tuple들이 반환뒬 수 있음
외부 질의의 WHERE절에서 IN, ANY(SOME), ALL, EXISTS와 같은 연산자를 사용
IN: 한 애트리뷰트가 값들의 집합에 속하는가 테스트
ANY: 한 애트리뷰트가 값들의 집합에 속하는 하나 이상의 값들과 어떤 관계를 갖는가 테스트
ALL: 한 애트리뷰트가 값들의 집합에 속하는 모든 값들과 어떤 관계를 갖는가 테스트
EXISTS: 중첩 질의의 결과가 빈 릴레이션인지 여부 검사 -> 빈 릴레이션이면 True, 아니면 False
SELECT empname
FROM emloyees
WHERE department_id IN (
SELECT department_id
FROM departments
WHERE deptname = '영업' OR deptname = '개발'
)
SELECT e.empname
FROM employees e, departments d
WHERE e.department_id = d.department_id
AND (d.deptname = '영업' OR d.deptname = '개발')
SELECT last_name
FROM employees
WHERE EXISTS (
SELECT *
FROM employees e, departments d
WHERE e.department_id = d.department_id
AND (d.department_name = '영업' OR d.department_name = '개발')
)
여러 attributes로 이루어진 realtion
Tuple의 리스트
SELECT last_name
FROM employees
WHERE salary < 1500000
AND (department_id, title) IN (
SELECT department_id, title
FROM employees
WHERE salary >= 1500000
)
상관 중첩 질의(correlated nested query)
외부 질의를 만족하는 각 tuple이 구해진 후에, 중첩 질의가 수행됨
부질의의 결과는 tuple이 바뀔 때마다 결과가 달라지고 where절을 튜플에 대해서 매번 계산해야 함 -> 계산 시간이 오래 걸림
SELECT employee_name, department_id, salary
FROM employees e
WHERE salary > (
SELECT AVG(salary)
FROM employees
WHERE department_id = e.department_id
)
-> 외부 쿼리가 매 내부 쿼리에 대해서 연산됨
FROM 절에 사용된 중첩 질의
중첩 질의는 테이블 이름이 없으므로 alias를 사용하여 이름 부여
SELECT employee_name, department_name
FROM employees e, (
SELECT departmnet_id, department_name
FROM departments
) d
WHERE e.department_id = d.department_id
4.5. INSERT, DELETE, UPDATE문
INSERT문
기존 릴레이션에 Tuple 삽입
참조되는 relation에 tuple 삽입 -> 참조 무결성 제약조건의 위배가 발생 X
참조하는 relation에 tuple 삽입 -> 참조 무결성 제약조건의 위배 발생할 수 있음
한 번에 한 Tuple씩 삽입하는 INSERT문
한 번에 여러 개의 Tuples 삽입하는 INSERT문
INSERT INTO department
VALUES (5, '연구', ''); --oracle에서는 empty string을 NULL로 간주
-> attribute 순서는 deptno, deptname, floor로 가정
INSERT INTO high_salary(last_name, title, salary)
SELECT empname, title, salary
FROM employee
WHERE salary >= 3000000;
DELETE문
한 릴레이션으로부터 한 개 이상의 Tuples 삭제
참조되는 릴레이션의 삭제 연산의 결과로 참조 무결성 제약조건이 위배될 수 있으나, 참조하는 릴레이션에서 Tuple을 삭제하면 참조 무결성 제약조건을 위배하지 않음
DELETE FROM department
WHERE department_id = 4;
UPDATE문
한 릴레이션에 들어 있는 Tuples의 attribute 값 수정
기본 키나 외래 키에 속하는 attribute의 값이 수정되면 참조 무결성 제약조건을 위배할 수 있음
UPDATE employee
SET department_id = 3, salary = salary * 1.05
WHERE employee_id = 2106;
4.6. 트리거와 주장
트리거
명시된 이벤트(데이터베이스의 갱신)가 발생할 때마다 DBMS가 자동적으로 수행하는, 사용자가 정의하는 문(프로시저)
데이터베이스의 무결성을 유지하기 위한 일반적이고 강력한 도구
테이블 정의시 표현할 수 없는 기업의 비즈니스 규칙들을 시행하는 역할
트리거 표현 요소
트리거를 활성화시키는 사건인 이벤트(event)
트리거가 활성화되었을 때 수행되는 테스트인 조건(condition)
트리거가 활성화되고 조건이 참일 때 수행되는 문(프로시저)인 동작(action)
트리거를 event - condition - action(ECA) 규칙이라고도 부름
SQL3 표준에 포함되었으며, 대부분의 상용관계 DBMS에서 제공됨
SQL3에서 트리거의 형식
CREATE TRIGGER <트리거 이름>
AFTER <트리거를 유발하는 이벤트들이 OR로 연결된 리스트> ON <relation> -- 이벤트
[WHEN <조건>] -- 조건
BEGIN <SQL문(들)> END -- 동작
이벤트의 가능한 예로는 테이블에 Tuple 삽입, 테이블로부터 Tuple 삭제, 테이블의 Tuple 수정 등
조건은 임의의 형태의 프레디키트
동작은 데이터베이스에 대한 임의의 갱신
어떤 이벤트 발생 -> 조건 참 -> 트리거와 연관된 동작 수행, 조건 거짓 -> 수행 x
삽입, 삭제, 수정 등이 일어나기 전(before)에 동작하는 트리거와 일어난 후(adter)에 동작하는 트리거로 구분
CREATE TRIGGER Raise_Salary
AFTER INSERT ON employee -- 이벤트
REFERENCING NEW AS newEmployee
FOR EACH ROW
WHEN (newEmployee.salary < 1500000) -- 조건
UPDATE employee -액션
SET newEmployee.salary = salary * 1.1
WHERE employee_id = newEmployee.employee_id;
연쇄적으로 활성화되는 트리거
하나의 트리거가 활성화 -> 이 트리거 내의 한 SQL문 수행 -> 그 결과로 다른 트리거 활성화 -> 트리거 내의 SQL문 수행
주장(ASSERTION)
SQL3에 포함되어 있으나, 대부분의 상용 관계 DBMS가 아직 지원하고 있지 않음
트리거: 제약조건을 위반했을 때 수행할 동작을 명시, 주장: 제약조건을 위반하는 연산이 수행되지 않도록 함
주장의 구문
CREATE ASSERTION <이름>
CHECK <조건>;
트리거보다 일반적인 무결성 제약조건
DBMS는 주장의 프레디키트 검사 -> 참이면 > 주장을 위배하지 않는 경우 -> 데이터베이스 수정 허용
일반적으로 두 개 이상의 테이블에 영향을 미치는 제약조건을 명시하기 위해 사용됨
대부분의 주장은 NOT EXISTS를 포함
주장에는 "모든 x가 F를 만족한다."를 이와 동치인 ~F를 만족하는 x가 존재하지 않는다."로 표시
CREATE ASSERTION EnrollStudentIntegrity
CHECK (NOT EXISTS
(SELECT *
FROM enroll
WHERE stno NOT IN (SELECT stno FROM student)));
4.7. 내포된 SQL(embedded SQL)
SQL이 호스트 언어의 완전한 표현력을 갖고 있지 않기 때문에 모든 질의를 SQL로 표현할 수는 없음
SQL은 호스트 언어가 갖고 있는 조건문(IF문), 반복문(WHILE문), 입출력 등과 같은 동작, 사용자와의 상호 작용, 질의 결과를 GUI로 보내는 등의 기능 없음
C, C++, 코볼, 자바 등의 언어로 작성하는 프로그램에 SQL문을 삽입하여, 데이터베이스를 접근하는 부분을 SQL이 맡고 SQL에 없는 기능은 호스트 언어로 작성하는 것이 필요
호스트 언어에 포함되는 SQL문을 내포된 SQL이라 부름
데이터 구조가 불일치하는 문제(impedance mismatch 문제)
Pro*C
오라클에서 C 프로그램에 SQL문을 내포시키는 방법
내포된 SQL문이 포함된 소스파일의 확장자는 .pc -> Pro*C를 통해 전컴파일 -> .c인 소스 프로그램 생성됨
호스트 변수
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 프로그램의 변수들을 포함할 수 있음
EXEC SQL SELECT title INTO :title --attribute title을 호스트 변수 title(:title)에 전송
FROM employee
WHERE employee_id = :no;
-> 주어진 사원 번호를 갖는 사원의 직급을 검색
- 입력(호스트 언어 -> SQL)
- 출력(SQL -> 호스트 언어)
동적인 SQL문
불완전한 SQL문으로서 일부 또는 전부를 질의가 수행될 때 입력 가능
응용을 개발할 때 완전한 SQL문의 구조를 미리 알고 있지 않아도 됨
문자열 형 변수에 담아서 표현함으로써 동적으로 변경 가능
컴파일 시점에 SQL문을 알지 못함
strcpy(hostVarStmtDyn, "UPDATE staff SET salary = salary + 1000 WHERE dept = :v");
//Prepare 이후 execute -> 질의 최적화 -> 어떤 방법으로 수행하는 것이 빠른지, best execution plan 생성
//찾아내는 데에 시간이 오래 걸림 -> 생성하는 과정 사용 최대한 자제 -> preapre와 execute 분리
EXEC SQL PREPARE StmtDyn FROM :hostVarStmtDyn;
EXEC SQL EXECUTE StmtDyn USING :dept;
//EXEC SQL EXECUTE IMMEDIATE :hostVarStmtDyn USING :dept; //prepare와 분리되지 않고 바로 실행되는 경우
#include <stdio.h>
EXEC SQL BEGIN DECLARE SECTION;
int no;
varchar title[10];
EXEC SQL END DECLARE SECTION;
EXEC SQL INCLUDE SQLCA.J; //SQL 통신영역
void main() {
char *uid = "KIM/kim@DBSERVER"; //사용자가 DBMS에 접속하기 위한 접속 정보
EXEC SQL WHENEVER SQLERROR GOTO errexit;
EXEC SQL CONNECT :uid; //DBSERVER 데이터베이스 사용자 KIM으로 접속
printf("Enter employee number: ");
scanf("%d", &no);
EXEC SQL SELECT title INTO :title
FROM employee WHERE employee_id = :no;
printf("\nAuthor's title is %s.\n", title);
EXEC SQL COMMIT WORK; //영구적으로 반영
exit(0);
errexit: //에러가 발생한 경우
EXEC SQL ROLLBACK WORK; //지금까지 수행한 것을 되돌리기
exit(1)
}
불일치 문제와 커서
호스트 언어는 단일 변수/레코드 위주의 처리(Tuple 위주의 방식)를 지원하는 반면, SQL은 데이터 레코드들의 처리(집합 위주의 방식)를 지원하기 때문에 불일치 문제 발생
불일치 문제를 해결하기 위해서 커서(cursor)가 사용됨
두 개 이상의 tuples를 검색하는 SQL문에 대해서 반드시 커서를 선언하고 사용해야 함
커서는 한 번에 한 tuple씩 가져오는 수단
커서
DECLARE CURSOR문을 사용하여 커서 정의
OPEN cursor문은 질의 수행, 질의 수행 결과의 첫 번째 투플 이전을 커서가 가리키도록 한다. 이것이 커서의 현재 tuple
그 다음에 FETCH문은 커서를 다음 tuple로 이동 -> 그 tuple의 attribute값들을 FETCH문에 명시된 호스트 변수들에 복사
