Chapter 10: Transactions

참조 : The Programmer’s Guide to SQL

트랜잭션의 특징 ACID
    – Atomic
    – Consistency
    – Isolation
    – Durable

오라클은 기본으로 Auto Transaction 상태.
    – 첫 번째 SQL문을 실행할 때, 트랜잭션이 자동으로 시작 됨.
    – 따라서 START TRANSACTION(SQL-99 표준) 을 사용하지 않아도 됨.
    – 그러나 10g에서는 기본으로 오토커밋 상태임.

사용자 삽입 이미지
오토커밋
    – 이 상태에서는 모든 SQL문을 개별 트랜잭션으로 처리함.
    – SET AUTOCOMMIT ON/OFF 오라클에서 오토커밋 상태로 전환 하는 방법.

롤백하기
    – ROLLBACK [세이브포인트 이름]
    – SAVEPOINT 이름 생략하면, 트랜잭션 시작 이전 상태로 돌아감.

세이브포인트 만들기
    – SAVEPOINT [이름];

커밋하기
    – COMMIT

트랜잭션 예제

BEGIN

   INSERT INTO Student (StudentID, Name) VALUES (101, ‘Dave’);
   INSERT INTO Student (StudentID, Name) VALUES (102, ‘Claire’);

   SAVEPOINT BeforeAddingAnne;
   INSERT INTO Student (StudentID, Name) VALUES (103, ‘Anne’);
   ROLLBACK TO BeforeAddingAnne;

   COMMIT;

EXCEPTION
   WHEN OTHERS
      THEN ROLLBACK;
END;
/

오라클의 Isolation level
    – 기본은 Read Commited
    – Read Uncommited와 Repeatable Read는 지원하지 않음.
    – Isolaction Level 변경하는 방법
    SET TRANSACTION
    { { READ ONLY | READ WRITE }
      | ISOLATION LEVEL
        { READ COMMITTED
        | SERIALIZABLE } };

동기화 테스트 하려면, 오토커밋 상태가 아닌 상태에서 두 개의 창을 띄워 놓고 하면 됨.(READ COMMITED 상태)
    – 한 쪽 창에서 한 개의 레코드를 추가한다.
    – 추가 됐는지 SELECT 문으로 확인한다.
    – 다른 쪽 창에서 SELECT 해본다.
    – 다른 쪽 창에는 첫 번째 창에서 추가한 레코드가 보이지 않는다.
    – 첫 번째 창의 SQL이 아직 커밋되지 않았기 때문이다.
    – 첫 번째 창에서 COMMIT 을 실행한다.
    – 두 번째 창에서 SELECT로 확인한다. 이번에는 보인다.

Chapter 8: Hiding Complex SQL with Views

참조 : The Programmer’s Guide to SQL

뷰 사용하면 좋은 점.
    – 같은 쿼리를 매번 작성하지 않아도 됨.
    – 데이터를 좀 더 편한 상태로 포맷해서 볼 수 있다.
    – 데이터가 아닌 뷰에 접근하게 함으로써 보안.
    – 데이터의 변경을 별도의 작업 필요없이 뷰에 반영할 수 있다.

뷰 만들기
    CREATE VIEW ViewName AS Query [WITH CHECK OPTION]
뷰 수정하기(오라클)
    CREATE OR REPLACE VIEW ViewName AS NewQuery
뷰 삭제하기
    DROP VIEW ViewName

뷰를 통해서 데이터를 수정/삭제 하려면 다음의 조건을 만족해야 한다.
    1. 어떤 aggregate functions(COUNT나 MAX) 또는 GROUP BY 를 사용하지 않아야 한다. 서브쿼리에서 사용하는 것은 괜찮다.
    2. DISINT를 사용하지 않아야 한다.
    3. 계산 식을 사용한 컬럼을 사용하지 않아야 한다.
    4. 명시적으로 설정한 값이 아닌, 데이블에서 가져온 데이터야만 한다.

뷰 예제
CREATE VIEW ClassAttendees AS
SELECT Class.ClassID,
   SUBSTR(Professor.Name, INSTR(Professor.Name, ‘ ‘) + 1)
           || ‘, ‘
           || SUBSTR(Professor.Name, 1,
              INSTR(Professor.Name, ‘ ‘) – 1)
   AS Name, ‘Professor’ AS Role
FROM Professor
   INNER JOIN Class ON Professor.ProfessorID =
      Class.ProfessorID
UNION
SELECT Enrollment.ClassID,
   SUBSTR(Student.Name, INSTR(Student.Name, ‘ ‘) + 1)
           || ‘, ‘
           || SUBSTR(Student.Name, 1,
              INSTR(Student.Name, ‘ ‘) – 1)
   AS Name, ‘Student’
FROM Student
   INNER JOIN Enrollment ON Student.StudentID = Enrollment.StudentID;
– 문자열 연산을 사용했다.
– 명시적으로 값을 사용했다.
– 따라서 위의 뷰를 통해서 테이블의 데이터를 수정/삭제/추가 할 수 없다.

Chapter 7: Querying Multiple Tables

참조 : The Programmer’s Guide to SQL

조인은 두 집합에 대한 곱셈연산인 데카르트 곱이다. 데카르트 곱이란 예를 들어, 집합 {a, b, c} 와 집합 {a, b}가 있을 때 그 결과는 {(a, a), (a, b), (b, a), (b, b), (c, a), (c, b)}다.

기본 문법(데카르트 곱)
    SELECT Class.ClassID, Class.Time, Room.RoomID
    FROM Room, Class;

크로스 조인
    – 기본 문법과 동일한 결과를 보여주지만, 다음과 같이 사용할 수 있다.
    – SQL-92 표준이다.
    SELECT Table1.Column1, Table1.Column2, Table2.Column3
    FROM Table1 CROSS JOIN Table2
    – 대부분 데카르트 곱을 결과로 원하지는 않을 것이다. Table1의 어떤 레코드가 Table2의 어떤 레코드와 조인되는지 명시할 필요가 있다. 이 때 보통 테이블의 관계를 이용한다.

Equi 조인
    – 다음과 같이 WHERE 절을 사용하여 컬럼 값을 동일성으로 레코드를 연결할 수 있다.
    SELECT Table1.Column1, Table1.Column2, Table2.Column3 FROM Table1, Table2
    WHERE Table1.Column1 = Table2.Column2
    – 좀 더 명시적으로 JOIN ON을(보다 명시적으로는 INNER JOIN ON) 사용할 수 있다.
    SELECT Table1.Column1, Table1.Column2, Table2.Column3 FROM Table1 JOIN Table2
    ON Table1.Column1 = Table2.Column2
    – 오라클에서 NATUAL JOIN을 사용할 수 있다.
    SELECT Class.ClassID,
        Class.CourseID,
        Class.Time,
        Room.Comments AS RoomName
    FROM Class NATURAL JOIN Room
    ORDER BY ClassID;
    – 오라클에서 USING을 사용할 수 있다.
    SELECT Room.Comments, Class.Time
    FROM Class
       JOIN Room
       USING (RoomID);

Non-Equi 조인
    – 두 개 이상의 테이블을 꼭 동일성으로 연결하지 않아도 된다.
    SELECT * FROM Table1, Table2
    WHERE Table1.Column1 < Table2.Column2
    – 이것도 물론 JOIN ON 문법을 사용할 수 있다.
    SELECT Room.RoomID, Class.Time
    FROM Room JOIN Class ON Room.RoomID <> Class.RoomID;

INNER 조인
    – 두 개 이상의 테이블에 모두 나타나는 결과만을 보여준다.

Outer 조인
    LEFT OUTER 조인
       – 관계에서 왼쪽에 위치한 테이블의 모든 레코드를 보여주며, 그에 대응하는 값이 없을 때는 NULL을 표시한다.
    RIGHT OUTER 조인
       – 관계에서 오른쪽에 위치한 테이블의 모든 레코드를 보여주며, 그에 대응하는 값이 없을 때는 NULL을 표시한다.
    FULL OUTER 조인
       – 관계를 맺는 테이블의 모든 레코드를 보여주며, 그에 대응하는 값이 없을 때는 NULL을 표시한다.

Chapter 6: Combining SQL Queries

참조 : The Programmer’s Guide to SQL

두 개 이상의 테이블의 데이터가 필요할 때 서브쿼리나 조인을 사용할 수 있다. 이번 챕터는 서브쿼리에 대해 공부한다.

서브쿼리에는 두종류가 있다.

  • 독립적인 서브쿼리 non-correlated subquery : 포함된 쿼리가 한 번만 수행되며, 그 결과가 외부 쿼리에 전달된다.
  • 연관된 서브쿼리 correlated : 외부 쿼리의 데이터를 필요로 하며, 외부 쿼리의 결과 레코드 마다 한 번씩 수행하게 된다.

성능은 당연히 독립적인 서브쿼리가 좋아보인다. 그렇다고 연관된 서브쿼리를 안 쓸 수도 없을테고 외부 쿼리Outter Query의 레코드 수를 줄이면 그나마 Inner Query 수행 횟수가 줄어 들테니 이 점을 유의해야겠다.

연관된 서브쿼리 예제1

SELECT CustomerID, CustomerName, (
   SELECT COUNT(*) FROM CreditCards
   WHERE CreditCards.CustomerID = Customers.CustomerID)
   AS NumberOfCreditCards
FROM Customers

– 고객ID, 고객이름, 고객의 신용카드 거래 합계를 보여준다.
– Customers 테이블의 레코드 갯 수 만큼 서브 쿼리가 실행된다.

연관된 서브쿼리 예제2

SELECT e1.StudentID, e1.ClassID, (
   SELECT COUNT(*) FROM Enrollment e2
   WHERE e1.ClassID = e2.ClassID)—1
   AS OtherStudentsInClass
FROM Enrollment e1
WHERE StudentID = 6;

– ID가 6인 학생이 등록한 수업에 참여하는 다른 학생들의 합계를 구한다.
– Enrollment 테이블에서 SudentID가 6인 레코드 갯수 만큼 서브쿼리를 수행한다.

독립적인 서브쿼리 예제 1

SELECT ExamID, SustainedOn FROM Exam
WHERE SustainedOn <= (
   SELECT SustainedOn FROM Exam WHERE ExamID = 5)
ORDER BY SustainedOn DESC;

– 시험 ID가 5인 시험이 치뤄지기 이전에 치뤄진 모든 시험 목록을 역순으로 보여준다.
– 내부 쿼리는 한 번만 수행되고 그 결과를 외부 쿼리에서 사용한다.

독립적인 서브쿼리 예제 2

SELECT StudentID, Name FROM Student WHERE StudentID IN
  (SELECT StudentID FROM Enrollment WHERE ClassID IN
        (SELECT ClassID FROM Class WHERE ProfessorID IN
          (SELECT ProfessorID FROM Professor
          WHERE Name LIKE ‘%Williams%’)));

– Williams 라는 이름이 들어가는 교수가 가르치는 학급에 등록한 학생들의 정보를 보여준다.
– 역시 전부 독립적인 서브쿼리로 여러개의 결과를 반환하기 때문에 IN을 사용했다.

EXISTS 연산자
– 외부 쿼리의 결과 레코드가 내부 쿼리 레코드 집합안에 포함되어 있다면, true.
– 따라서 연관된 쿼리로 작성해야 함.
– 따라서 a.커럼id = b.컬럼id 와 같은 공식이 내부 쿼리에 추가되어야 함.
– 그래야 외부 쿼리와 내부 쿼리가 연결 됨.

– 예제
SELECT StudentID, Name FROM Student s
WHERE EXISTS (
   SELECT StudentID FROM StudentExam e
   WHERE Mark < 40 AND e.StudentID = s.StudentID);
– Mark가 40점 이하인 학생 정보.
– AND e.StudentID = s.StudentID 없으면 그냥 Student 테이블의 모든 레코드 보여줌.

ALL 연산자
– 외부쿼리가 ALL 뒤에 있는 내부 쿼리의 결과를 모두 만족 시키는 경우 true.

-예제
SELECT StudentID, Grade FROM Enrollment e
WHERE Grade > ALL (
   SELECT Mark FROM StudentExam s
   WHERE s.StudentID = e.StudentID);
– 등록 정보 중에서 Grade가 그 학생이 치룬 모든 시험의 Mark보다 높은 학생 정보를 보여줌.

ANY 연산자
– 외부쿼리가 ANY 뒤에 있는 내부 쿼리의 결과에 하나라도 만족하는 경우 true.

– 예제
SELECT StudentID, Grade FROM Enrollment e
WHERE Grade < ANY (
   SELECT Mark/2 FROM StudentExam s
   WHERE s.StudentID = e.StudentID);
-등록 정보 중에서 Grade가 그 학생이 치룬 모든 시험의 Mark의 절반도 안되는 학생 정보를 보여줌.

쿼리 결과 데이터 조합하기
SELECT ColumnA, ColumnB FROM TableA
<Operator>
SELECT ColumnC, ColumnD FROM TableB;

Operator(오라클)

  • UNION : 합집합
  • UNION ALL : 합집합 + 교집합
  • INTERSECT : 교집합
  • MINUS : 차집합

흠.. EXISTS, ALL, ANY 어렵… -_-;;