[데이터베이스] SQL - 2

📂 SQL 뷰(View)

뷰(View)는 하나 또는 둘 이상의 기본 테이블로부터 유도되어 만들어지는 가상 테이블(Virtual table)을 말한다. 

기본 테이블은 디스크에 공간이 할당되어 데이터를 저장한다. 하지만, 뷰(View)는 데이터 딕셔너리 테이블에 뷰에 대한 정의(SQL문)만 저장되어 디스크 저장 공간 할당이 이루어지지 않는다. 

또한, 전체 데이터 중에서 일부만 접근할 수 있도록 하고, 뷰에 대한 수정 결과는 뷰를 정의한 기본 테이블에 적용된다. 

뷰를 정의한 기본 테이블에서 정의된 무결성 제약조건은 그대로 유지된다.

뷰는 독자적인 인덱스를 가질 수 없다.

뷰는 논리적 독립성을 제공한다.

---

📄 뷰 생성

질의 : 학생 테이블의 '컴퓨터'과 학생들로 구성된 학생뷰를 생성하라.

CREATE VIEW student_view(sno, sname, stel)
       AS SELECT sno, sname, stel
          FROM student
          WHERE sdept = '컴퓨터'
          WITH CHECK OPTION;

뷰의 생성은 "CREATE VIEW 뷰이름 AS SELECT 문" 의 형태로 만들어진다.

위의 WITH CHECK OPTION 절은 이 뷰에 대한 갱신이나 삽입 연산 시 뷰 정의 조건인 'sdept=컴퓨터'를 위반하면 실행이 거절된다는 것을 기술하는 것이다. 

 

이 그림은 또 다른 뷰의 생성과 사용의 예를 보여주고 있다. 기존의 title 테이블로부터 다음과 같은 뷰 생성 SQL 문을 사용하여 TitleView를 만들었다.

CREATE VIEW TitleView
       AS SELECT title, author
          FROM title;

이와 같이 뷰를 생성하고 나면 이후부터 사용자는 복잡한 title 테이블과는 상관없이 보기에 간단한 TitleView만 참조하면 된다. 

 

🏷 집계 함수 사용 예

CREATE VIEW 학과별통계(sdept, 학생수)
       AS SELECT sdept, COUNT(*)
          FROM student
          GROUP BY sdpet;

뷰 정의에서 기술되는 SELECT 문에는 GROUP BY 절을 포함할 수 있다. 위의 '학과별통계' 뷰는 관련된 행들을 그룹화하여 각 그룹에 대해 그룹 내의 데이터가 요약된 질의 결과를 생성한다. 

주의할 점은, '학과별통계' 뷰 정의문에서 두 번째 열은 집계 함수로부터 유도되기 때문에 열의 이름을 상속받을 수 없어 "학생수"로 명시하였다. 

🏷 두 개 이상의 테이블을 조인해서 뷰 정의

학생과 수강 테이블을 조인하여 점수가 90점 이상인 결과를 보여주는 '우수학생' 뷰

CREATE VIEW 우수학생(sname, sdept, score)
       AS SELECT student.sname, student.sdept, enroll.score
          FROM student S, enroll E
          WHERE S.sno=E.sno AND
                E.score >= 90;

이와 같이 테이블을 조인해서 뷰를 생성할 수도 있다.

 

조인된 뷰의 다른 사용 예를 보면 다음 그림과 같다.

이 BirthdayView를 만들어 두면, 매번 질의 때마다 조인을 할 필요 없이 다음 질의문부터는 간단히 BirthdayView만 살펴보면 된다. 

---

📄 뷰 제거

뷰를 제거할 때는 다음과 같이 DROP 문을 사용한다. 

DROP VIEW View_name { RESTRICT | CASCADE };

RESTRICT는 이 뷰에 종속적인 뷰가 정의되지 않았을 떄만 뷰를 삭제하도록 지시한다.

CASCADE는 이 뷰뿐만 아니라 이 뷰에 종속적인 다른 모든 뷰나 제약 조건이 함께 제거된다. 

 

예를 들어 다음과 같은 SQL 문을 보자.

DROP VIEW 학과별통계 CASCADE;

이 경우에는 '학과별통계' 뷰가 제거될 뿐만 아니라 이 뷰를 기반으로 종속된 다른 뷰들도 자동적으로 제거된다. 

---

📄 뷰의 장단점

🏷 뷰의 장점

• 관련된 데이터만 이용 가능하다.
• 중요하고 적합한 데이터만으로 구성 가능하다.
• 민감한 데이터에 대한 접근 금지(보안)가 가능하다.
• 데이터베이스의 복잡성을 해소할 수 있다.
• 복잡한 데이터베이스의 구조를 숨길 수 있다. 
• 복잡한 질의를 단순화 할 수 있다.
• 권한 부여를 단순화 할 수 있다. 

🏷 뷰의 단점

• 정의를 변경할 수 없다.
• 삽입, 삭제, 갱신 연산에 제한이 많다.

---

📂 삽입 SQL

지금까지는 터미널에서 대화식 질의어로 사용되는 SQL 문들을 살펴보았다. 하지만 실제로는 응용 시스템을 개발할 때 프로그램 속에 SQL이 삽입된 형태로 더 많이 사용된다. 즉, Java, C, C++, Python... 등과 같은 범용 프로그래밍 언어로 된 응용 프로그램 속에 삽입된 형태로 많이 사용된다. 우리가 사용한 SQL 문은 응용 프로그램에서도 그대로 사용할 수 있는 이중 모드(Dual mode) 특성을 가지고 있다. 

📄 삽입 SQL을 포함하는 응용 프로그램의 특징

여기서는 C 프로그램을 기준으로 살펴보자. 기본적인 원리는 다른 프로그램 언어들과 비슷하다. 

• 범용 프로그래밍 언어로 작성 중인 프로그램에 삽입된 SQL 문들은 반드시 문장의 앞부분에 EXEC SQL을 붙여 다른 호스트 언어 명령문과 쉽게 구별할 수 있도록 한다. 
• SQL 문은 입력값과 데이터 출력을 위해 C언어의 변수들을 포함할 수 있다. 이를 '호스트 변수(Host Variable)'라 부른다. 호스트 변수는 삽입 SQL의 선언부에서 BEGIN DECLARE SECTION과END DECLARE SECTION문을 사용하여 선언된다. 
  이러한 호스트 변수가 사용될 때는 변수 앞에 콜론(:)을 붙여 동일한 이름을 가진 애트리뷰트 이름들을 쉽게 구별할 수 있도록 한다. 따라서, 호스트 변수와 DB의 애트리뷰트 이름은 같아도 무방하다. 
• 호스트 변수와 이에 대응하는 SQL 열의 데이터 타입은 일치해야 한다. 호스트 언어에서 제공하는 데이터 타입과 DBMS가 제공하는 데이터 타입이 불일치하는 문제를 Impedance Mismatch라 한다. 
• SQL 명령문을 수행한 후 상태 정보를 저장하는 호스트 변수 SQLSTATE를 포함한다. SQL 문이 실행되면 실행 상태(성공, 실패, 오류) 표시가 이 SQLSTATE를 통해 프로그램에 전달된다. 

🏷 응용 프로그램에 SQL 삽입 예시(단일 레코드 검색)

앞서 설명했듯이, SQL 문들은 문장의 앞부분에 EXEC SQL을 붙이고, 호스트 변수는 사용하기 전에 반드시 SQL 선언부인 BEGIN DECLARE SECTION 속에 선언되어야 한다. 

EXEC SQL BEGIN DECLARE SECTION;
     int sno;
     char sname[10];
     char sdept[20];
     char SQLSTATE[6];
EXEC SQL END DECLARE SECTION;

sno = 20181234;

EXEC SQL  SELECT sno, sname, sdept
          INTO :sno, :sname, :sdept
          FROM student
          WHERE sno = :sno;
          
if(SQLSTATE = '0000') // 실행 상태
...
else
...

printf("Student no : %d, name : %s, dept : %s", sno, sname, sdpet);

위의 결과는 간단하게 한 레코드만 검색된다. 이와 같이 WHERE 절을 만족하는 레코드가 오직 하나만 있을 때를 단일 레코드 검색(singleton SELECT)이라 한다. 

🏷 복수 레코드 검색(Cursor)

만약 여러 개의 레코드가 검색되는 경우에는 이에 대한 처리가 필요하다. 

SQL 문과 프로그래밍의 가장 큰 차이점은 DBMS에서 SQL은 결과를 한 번에 여러 레코드를 한 집합처럼 처리할 수 있지만, 프로그래밍에서는 한 줄씩 반복해서 루프를 돌며 반복처리되어야 하는 것이다. 

따라서, SQL 결과 집합 레코드를 하나씩 반복 접근하기 위해서 커서(Cursor)를 사용해야 한다. 

커서(Cursor)

• 커서를 정의하려면 DECLARE CURSOR 문을 사용한다. DECLARE CURSOR 문은 뒤에 나오는 SELECT 문을 연결한다. SELECT 문은 입력 매개변수들을 위해서 호스트 변수들을 포함할 수 있다. 
• 커서는 튜플들의 집합에 속하는 각 튜플을 가리키는 포인터로 생각할 수 있다. 커서 선언 후, CURSOR가 OPEN 될 때 SELECT 문이 실행되고, 질의 수행 결과의 첫 번째 튜플 이전을 커서가 가리키게 된다. 이것이 커서의 현재 튜플이 된다. 
• FETCH 문은 커서를 다음 튜플로 이동하고, 그 튜플의 애트리뷰트 값들을 FETCH 문에 명시된 호스트 변수들에 복사한다. 
• DO ... END 문 내에서 루프(Loop)를 수행하고, 더 이상 레코드가 없으면 루프가 종료되면 CLOSE 문에서 커서를 닫는다. 이후부터는 질의 수행 결과를 더이상 접근할 수 없다. 

예시를 확인해보자.

EXEC SQL DECLARE cur CURSOR FOR
         SELECT sno, sname, sdept
         FROM student
         WHERE sdept = :sdept;
         
EXEC SQL OPEN cur;

DO
    EXEC SQL FETCH cur INTO :sno, :sname, :sdept;
    ...
END

EXEC SQL CLOSE cur;

 

만약, 갱신할 튜플에 대해서 커서를 정의할 때에는 커서 선언부에 FOR UPDATE [속성명] 절을 반드시 포함시킨다. 그 후, UPDATE 구문의 WHERE 절에서 "CURRENT OF 커서이름"의 형태로 갱신하고자 하는 튜플의 위치를 지정해준다. 

 

변경(UPDATE)

EXEC SQL UPDATE student
         SET sdept = :sdept
         WHERE CURRENT OF cur;

삭제(DELETE)

EXEC SQL DELETE 
         FROM student
         WHERE CURRENT OF cur;

---

📂 동적 SQL

온라인 애플리케이션은 온라인 터미널로 데이터베이스 접근을 지원하는 실시간 응용 프로그램을 말한다. 수행 과정은 일반적으로 다음과 같다. 

1. 온라인 터미널로부터 명령문을 접수한다.
2. 입력된 명령문을 분석한다.
3. 데이터베이스에 대한 적절한 SQL 문으로 지시한다.
4. 터미널에 메시지나 결과를 반환한다.

이때 입력 명령문은 상황에 따라 다를 수가 있으므로 SQL 문을 미리 만들어 두는 것은 불가능하다. 따라서 SQL 문을 동적으로 작성하여 이를 실시간으로 처리하는 것이 바람직하다. 

이를 지원하기 위한 기능을 동적 SQL(Dynamic SQL)이라 한다. 이 동적 SQL을 위한 가장 중요한 기본적인 명령문이 PREPARE와 EXECUTE이다. 

 

예제를 하나 살펴보자.

varchar staticSQL[256];
staticSQL = "SELECT * FROM enroll WHERE code='CO123' AND score>=90";

EXEC SQL PREPARE stSQL FROM :staticSQL;
EXEC SQL EXECUTE stSQL;

이 예제에서 staticSQL은 변수로써 SELECT 문을 저장한다. 반면에 stSQL은 호스트 변수가 아니라 SQL 변수로써 staticSQL에 저장된 SQL 문의 목적 코드를 저장하는데 사용된다. 

PREPARE 문은 주어진 SQL 문을 예비 컴파일하여 목적 코드를 생성하고 stSQL에 저장시킨다.

EXECUTE 문은 저장되어 있는 stSQL의 SQL 목적 코드를 실행한다. 

 

여기서 과목코드와 점수가 고정되어 있다. 만약 고정된 값이 아닌 동적으로 변경가능한 과목코드와 점수를 대입하려고 하면 이 부분에 호스트 변수를 넣어야 한다. 위의 staticSQL 변수는 string으로 표현되기 때문에 직접 호스트 변수를 사용하지 못하고, 물음표(?)로 표현되는 매개변수를 사용해야 한다. 

 

다음과 같은 표현이 가능하다.

varchar dynamicSQL[256];
dynamicSQL = "SELECT * FROM enroll WHERE code=? AND score>=?";

EXEC SQL PREPARE dySQL FROM :dynamicSQL;
         code = 'CO123';
         score = 90;
EXEC SQL EXECUTE dySQL USING :code, :score;

위의 dynamicSQL에는 2개의 물음표 매개변수가 포함되어 있다. 물음표를 포함한 SQL 문을 실행하기 위해서는 USING 절을 사용한다. USING 절을 가진 EXECUTE 문에 물음표 인자 값들을 설정한다. 

 

지금까지는 C를 기반으로 한 삽입 SQL을 설명했지만, 실 응용에서 가장 많이 사용되는 Java에서의 SQL programming을 살짝만 살펴보자.

public static void main(String[] args){
    Connection conn = null;
    String url = "jdbc:mysql://localhost/accdemicDB";
    String id = "root"
    String pass = "root"
    
    Statement stmt = null;
    ResultSet rs = null;
    
    String query = "select * from student";
    
    try{
        conn = DriverManager.getConnection(url, id, pass);
        stmt = conn.createStatement();
        rs = stmt.executeQuery(query);
        
        while(rs.next()){
            System.out.println(rs.getInt(1) + ", " + rs.getString(2));
        }
    }
    
    ...
    rs.close();
    stmt.close();
    conn.close();
}