4. 고급 SQL

1. SQL 데이터 타입과 스키마

1.1 Data Type

Scale과 Precision의 예

 

데이터베이스에서 Scale과 Precision은 주로 숫자 데이터 타입에서 사용됩니다.

- Precision(정밀도): 소수점을 포함한 전체 자릿수를 나타냅니다. 예를 들어, 숫자 1234.56에서 전체 자릿수는 6이 됩니다.
- Scale(스케일): 소수점 아래의 자릿수를 나타냅니다. 예를 들어, 숫자 1234.56에서 소수점 아래의 자릿수는 2가 됩니다.

예를 들어, NUMBER(8,2)라는 데이터 타입은 전체 8자리의 숫자를 표현하며, 소수점 아래에 2자리까지 표현할 수 있습니다. 따라서 1234.56과 같은 숫자를 저장할 수 있습니다.

또 다른 예로, DATE 데이터 타입은 연도, 월, 일, 시, 분, 초까지의 정보를 저장합니다. 이 경우 Precision은 연도, 월, 일, 시, 분, 초를 모두 포함한 전체 자릿수를 나타내며, Scale은 초까지의 자릿수를 나타냅니다.

 

1.2 Large-Object Types

사진이나 동영상 등의 대용량 데이터를 저장

• CLOB(Character Large-Object)
• BLOB(Binary Large-Object)

CLOB은 긴 문자 데이터를 저장한다.(예시: 소설)

BLOB은 바이너리 데이터를 저장한다. (예시: 이미지, 동영상 파일)

 

(Oracle에서는 4000byte가 넘어가는 데이터는 외부에 저장하고, 파일 위치 locator를 리턴하는 방식을 쓴다고 함)

 

1.3 Schemas, Catalogs, and Environments

Three-level hierarchy 

Catalogs -> Schemas -> Relations/Views

 

카탈로그는 Database개념이다. (여러개의 스키마로 구성된다)

스키마는 여러개의 relation과 view로 구성된다.

(Oracle에서는 카탈로그 개념을 지원하지 않는 2단계 네이밍을 이용한다. 스키마는 사용자ID(ex: SCOTT)를 의미한다.)

(sys.dual.sysdate 는 sys계정(스키마)-dual(릴레이션)을 나타냄)

 

---

2. 무결성 조건(Integrity Constraints)

2.1. 개체 무결성(Entity Integrity): 기본 키

개체 무결성은 기본 키(primary key)가 null이거나 중복되지 않도록 보장하는 제약 조건입니다. 즉, 각 행은 유일한 식별자(primary key)를 가져야 하며, 이 값은 null일 수 없습니다.

2. 도메인 무결성(CHECK문)

: 도메인 무결성은 각 열(column)이 특정한 유형의 값을 가지도록 보장합니다. CHECK 제약 조건을 사용하여 열에 입력할 수 있는 값의 범위나 조건을 정의할 수 있습니다.

CREATE TABLE student(
	SID	Number(4) NOT NULL PRIMARY_KEY,
	SNAME VARCHAR(10) CHECK (Sname = initcap(Sname))
    				CONSTRAINT ST_Sname,
   	...
);

여기서 SNAME은 initcap(Sname)인지 체크하는 조건을 가지고 있으며, 제약조건의 이름을 ST_Sname으로 정의하고 있다.(CONSTRAINT {제약조건명}) 만약 제약조건명을 지정하지 않으면 시스템이 작명한다.

 

이렇게 제약조건을 선언할 수 있게 됐는데, 만약 제약조건을 비활성화하고싶다면?

Alter Table Student Disable Constraint ST_Sname
Alter Table Student Enable Constraint ST_Sname

"ALTER TABLE {테이블명} Disable/Enable Constraint {제약조건명}" 으로 비활성화/활성화할 수 있다.

Column/Table Constraints

: Column Constraints는 열에 대한 제약 조건을 설정합니다. 예를 들어, NOT NULL 제약 조건은 특정 열에 null 값을 허용하지 않습니다.

CREATE TABLE STUDENT(
	SID Number(4) NOT NULL PRIMARY_KEY,
    SNAME VARCHAR(10) CHECK(Sname = initcap(Sname))
);
-- 열에 대한 제약조건

Table Constraints는 전체 테이블에 적용되는 제약 조건을 설정합니다.

CREATE TABLE EMP(
	EMPNO Number(6) NOT NULL PRIMARY KEY,
    NAME VARCHAR(12) NOT NULL,
    SALARY Number(9,2),
    BONUS Number(9,2),
    TAX Number(9,2),
    
    CONSTRAINT SAL_CK CHECK (SALARY >= 10000),
    CONSTRAINT BONUS_CK CHECK (BONUS > TAX)
)

위 예시에서는 봉급이 10000이상이고, 보너스가 세금보다 커야한다는 제약조건을 달았다.

 

2.3 참조 무결성(Referential Integrity): 외래키

외래키를 쓸 때, 해당 값이 참조되는 테이블에 존재해야하고 유일해야한다.

 

만약 Student 테이블의 advisor 필드가 Professor테이블의 id를 갖는다고 하면(즉, advisor는 professor id 값을 갖는 외래키)

advisor 필드에 30이라는 숫자가 적히기 위해서는 professor테이블에 id가 30인 레코드가 존재해야한다(또한 유일해야함). 

 

참조 무결성을 체크해야하는 상황은 언제일까?

 

참조되는 테이블의 경우

insert: 상관 없다.

delete, update: 필요하다. 참조되고 있는데 삭제하거나 변경되면 안되니까.

 

외래키를 쓰는 테이블의 경우

insert, update: 필요하다

delete: 상관 없다.

 

참조 무결성 정의하는 방법

References 테이블명(필드명)

CREATE TABLE STUDENT(
	SID Number(4) NOT NULL PRIMARY_KEY,
    DeptNo Number(2) REPERENCES Department(DeptNo) ON DELETE CASCADE,
    Advisor Number(3) REFERENCES Professor(PID) CONSTRAINT Emp_Advisor
)

이 예시에서는 DeptNo, Advisor가 외래키로 사용된다.

DeptNo는 ON DELETE CASCADE 설정이 되어있는데, Department테이블에서 레코드가 삭제될 때, 해당 레코드를 참조하고있는 student레코드가 삭제된다는 것이다.

만약 삭제 말고 null값을 넣고싶다면 ON DELETE SET NULL을 쓰면 된다.

 

Advisor는 CONSTRAINT Emp_Advisor을 적어서 제약조건이름을 설정했는데,

추후에 이 이름을 사용해서 제약조건을 변경하거나 삭제할 수 있다.

2.4. Assertions

: Assertion은 SQL의 일부로, 특정 조건이 항상 참이어야 함을 보장하는 제약 조건입니다. 일반적으로 복잡한 비즈니스 규칙이나 제약 조건을 정의할 때 사용됩니다.

Assertion과 CHECK문의 비교

: Assertion과 CHECK문은 유사한 목적을 가지고 있습니다. 그러나 CHECK문은 열 수준 또는 테이블 수준에서 정의되며, 열의 값에 대한 조건을 검사합니다. 반면에 Assertion은 테이블 수준에서 정의되고 여러 열에 대한 복잡한 조건을 검사할 수 있습니다.

 

create assertion sum-constraint check
	(not exists(select * from branch B
    	where (select sum(amount( from loan L
        	where L.branch-name = B.branch-name)
            >=(select sum(balance) from account A
            where A.branch-name = B.branch-name)))

이 예시에서는 특정 브랜치의 예금의 합이 대출의 합보다 커야함을 제약조건으로 하고있다.

여기서 branch, loan, account 테이블의 열들을 참조하며 제약하고있다.(CHECK문으로는 테이블 내의 열만 참조가능)

 

Assertion을 표현하기 위해 Trigger를 이용할 수 있다.

 

2.5. Triggers

: 트리거는 데이터베이스에서 발생하는 특정 이벤트(INSERT, UPDATE, DELETE 등)에 자동으로 응답하는 일련의 작업을 정의합니다. 트리거를 사용하여 데이터의 무결성을 유지하거나 특정 작업을 수행할 수 있습니다.

 

Trigger 정의 요건

1. 발생하는 조건

2. 실행될 때 수행할 명령

 

Triggers in Oracle

- Trigger의 유형은 12가지이다.

(Before/After, Insert/Delete/Update, Row/Statement)

- PL/SQL을 이용하여 프로그래밍한다.

 

행 단위의 트리거:

referencing new row as nrow

for each row

 

문장 단위의 트리거:

referencing old table as DeletedFlights

referencing new table as NewFlights

for each statement

 

행 단위는 추가/삭제/변경되는 각 행에 대해 실행되고, 문장단위는 한번에 여러 레코드가 삽입/삭제/변경되더라도 단 한번 실행된다.

 

---

Embedded SQL

C,C++,Java등의 언어로 작성한 프로그램에 포함된 SQL

host language의 컴파일러에 의해 전처리된다.

 

CURSOR를 이용한 QUERY

-SQL결과를 변수로 저장하기엔, 결과값이 몇개일지 모르기 때문에 어려움. CURSOR를 사용.

CURSOR는 결과값의 첫번째 데이터를 가리킴.

Embedded SQL은 프로그래밍 언어(예: C, C++, Java) 안에 SQL 코드가 포함된 형태를 의미합니다. 이러한 SQL 코드는 호스트 언어의 컴파일러에 의해 전처리되어 실행됩니다.

CURSOR는 Embedded SQL에서 사용되는 중요한 개념 중 하나입니다. 이는 SQL 쿼리 결과를 변수에 저장하기 위해 사용됩니다. 왜냐하면 SQL 쿼리의 결과값이 몇 개인지 사전에 알 수 없기 때문에, CURSOR를 사용하여 결과값을 하나씩 처리할 수 있습니다.

CURSOR는 결과값 집합 중 첫 번째 데이터를 가리키는 포인터 역할을 합니다. 이후에는 CURSOR를 사용하여 순차적으로 결과값을 읽거나 처리할 수 있습니다. 이는 데이터베이스에서 여러 행을 반환하는 쿼리를 처리할 때 매우 유용합니다.

예를 들어, C 언어에서 CURSOR를 사용한 Embedded SQL 코드는 다음과 같이 될 수 있습니다:

```c
EXEC SQL DECLARE my_cursor CURSOR FOR
    SELECT column1, column2
    FROM my_table
    WHERE condition;

EXEC SQL OPEN my_cursor;

while (1) {
    EXEC SQL FETCH my_cursor INTO :variable1, :variable2;
    if (SQLCODE != 0) break; // 더 이상 데이터가 없을 때 루프 종료

    // 변수들을 이용하여 결과값 처리
    // ...
}

EXEC SQL CLOSE my_cursor;
```

위 코드에서 `DECLARE`, `OPEN`, `FETCH`, `CLOSE` 등은 Embedded SQL의 CURSOR를 다루는 명령어들입니다. `:variable1`, `:variable2` 등은 결과값을 저장할 변수들을 나타냅니다.

CURSOR를 사용하여 SQL 쿼리의 결과값을 처리할 때, 주의해야 할 점은 쿼리의 실행 결과가 비어있지 않은지를 항상 확인해야 한다는 것입니다. 위 코드에서 `SQLCODE`를 이용하여 에러 여부를 확인하고, 루프를 종료하는 부분이 그 예시입니다.

이러한 방식으로 Embedded SQL을 사용하여 프로그램에서 데이터베이스와 상호작용할 수 있습니다.

---

Dynamic SQL

Run-time에 SQL을 구성하고 질의를 요청하는 API로 구성.

(Embedded SQL은 컴파일 시점에 질의가 완성되지만 다이나믹SQL은 런타임에 SQL질의를 생성가능하다)

 

대표적인 Dynamic SQL표준은 ODBC, JDBC가 있다.

 

 

1. JDBC 드라이버 로딩

try {
    // JDBC 드라이버를 로드합니다.
    Class.forName("cohttp://m.mysql.cj.jdbc.Driver"); // 예시: MySQL 드라이버
} catch (ClassNotFoundException e) {
    e.printStackTrace();
}

2. 데이터베이스 연결

Connection connection = null;

try {
    // 데이터베이스 URL, 사용자명, 비밀번호를 이용하여 연결합니다.
    String url = "jdbc:mysql://localhost:3306/your_database";
    String username = "your_username";
    String password = "your_password";

    connection = DriverManager.getConnection(url, username, password);
} catch (SQLException e) {
    e.printStackTrace();
}

3. Statement 객체 생성

Statement statement = null;

try {
    // Statement 객체를 생성합니다.
    statement = connection.createStatement();
} catch (SQLException e) {
    e.printStackTrace();
}

4. 쿼리 실행

try {
    String query = "SELECT * FROM your_table";
    ResultSet resultSet = statement.executeQuery(query);

    while (resultSet.next()) {
        // 결과 처리
        int id = resultSet.getInt("id");
        String name = resultSet.getString("name");

        // 여기서 결과를 처리하면 됩니다.
    }

    // ResultSet과 Statement를 닫아줍니다.
    resultSet.close();
    statement.close();
} catch (SQLException e) {
    e.printStackTrace();
}