[ DB ] Chapter 5. Advanced SQL

해당 게시물은 건국대학교 김욱희 교수님의 데이터베이스 강의와

DATABASE SYSTEM CONCEPTS 7th 원서를 참고하여 작성하였습니다. 

---

1. Accessing SQL from Programming Language

 

SQL만으로는 범용언어를 완전히 표현하기 힘들기 때문에 모든 쿼리를 SQL만으로 표현하기는 힘들다. 또, print와 같이 쿼리를 GUI로 보내는 작업들은 SQL에서 수행할 수 없다. 따라서 우리는 범용 프로그래밍 언어에 액세스 할 수 있어야한다.

이를 위해 데이터베이스 서버와 연결하고 쿼리를 수행하는 표준방식은 Dynamic SQL과 Embedded SQL가 있다.

• Dynamic SQL

일련의 함수 또는 메서드를 사용하면 데이터베이스 서버에 연결하고 통신하는 프로그램을 작성할 수 있다. Dynamic SQL은 프로그램이 실행하는 동안 문자열로 SQL쿼리를 작성하고 제출하여 결과를 변수에 저장하는 기능을 제공한다.

Java 언어를 위한 응용 프로그램 프로그래밍 인터페이스 인 JDBC와 C 언어를 위해 개발된 응용 프로그램 프로그래밍 인터페이스 인 ODBC가 있다. 이들은 서버에 접속하기 위해 다음과같은 3단계를 거친다

1. Connect DB server
2. Send SQL to DB server
3. Fetch tuples of result into program variables

• Embedded SQL

이는 프로그래밍 언어 내에 SQL 문장을 삽입하여 데이터베이스와 상호 작용할 수 있는 방법이다. 내장 SQL을 사용하면, SQL 문장을 컴파일러에 의해 분석 및 처리된 후 프로그램과 함께 컴파일되며, 런타임 시에 SQL 문장을 데이터베이스 서버에 보내 실행한다. 이는 동적 SQL에 비해 성능이 좋고 컴파일 시간에 오류를 잡을 수 있다는 장접이 있다. 하지만 이는 코드 가독성이 떨어지고 DB제품마다 다른 내장 SQL구현 방법을 가지고 있어 이식성이 떨어진다.

 

JDBC

JDBC는 JAVA를 사용할대 DB서버에 접속할 수 있도록 해주는 API이다. 이는 데이터를 업데이트하고 쿼리결과를 검색할 수도 있게한다. 또, 데이터베이스에서 관계 및 속성 이름과 유형과 같은 메타데이터 검색을 지원한다. DB와 연결하는 model은 다음과 같은 과정을 거친다.

1. Open connection
2. “Statement” 객체 만들기
3. "Statement" 객체를 사용하여 쿼리를 보내고 결과를 가져와서 쿼리를 실행
4. 오류를 처리하기 위한 예외 메커니즘 사용

JDBC를 사용할 때 주의해야할 점도 있다 입력으로 받은 문자열을 연결하여 쿼리를 생성하면 SQL Injection에 노출될 위험이 있다. SQL injection은 해커가 악의적인 SQL 문을 입력하여 데이터베이스 시스템에서 의도하지 않은 작업을 수행하도록 하는 공격이다. 항상 참인 문장을 삽입하여 데이터를 빼낸다. 예를 들어 X’ or ’Y’=’Y와 같은 코드를 삽입하면 "X’ or ’Y’=’Y"으로 삽입되어 항상 맞는 문장이 된다.

JDBC에서는 Metadata Feature를 제공한다. Metadata란 데이터에 대한 정보를 말합니다. 따라서 Metadata Features는 데이터베이스에서 데이터에 대한 정보를 검색할 수 있는 기능을 말한다.

JDBC에서는 기본적으로 자동으로 commit과 rollback을 제공한다. 이를 직접 제어하고싶으면 자동commit기능을 끄고 직접 제어할 수 있다.

SQLJ는 Java 언어에서 SQL을 사용할 수 있도록 지원하는 내장SQL이다. 이는 SQL 코드를 Java 코드에 직접 내장하여 사용하므로, 보다 직관적이고 유지보수가 용이하다는 장점이 있다. 또한 SQLJ에서는 동적 SQL을 사용할 수 있으며, 컴파일 단계에서 SQL 문법의 오류를 검출할 수 있다는 점도 JDBC와 차이가 있다

 

ODBC

ODBS는 응용 프로그램이 데이터베이스와 연결을 열고 쿼리 및 업데이트를 보내고 결과를 반환하는 데 사용할 수 있는 API를 정의한다. 이는 Excel, 스프레드시트, C 등 대부분의 언어에서 사용가능하다.

ODBC를 지원하는 각 데이터베이스 시스템은 클라이언트 프로그램과 링크할 "드라이버" 라이브러리를 제공한다. ODBC 프로그램에서는 먼저 SQL 환경을 할당한 다음 데이터베이스 연결 핸들을 할당하여 데이터베이스 연결을 연다. SQLExecDirect를 사용하여 데이터베이스에 SQL 명령을 보내고 결과 튜플은 SQLFetch()를 사용하여 가져온다. SQLBindCol()를 사용하면 C 언어 변수를 쿼리 결과의 속성에 바인딩할 수 있다.

ODBC는Prepared Statement를 제공한다. Prepared Statement란 SQL 구문이 미리 컴파일되고 데이터베이스에 준비된 상태를 유지하는 객체를 말한다. 그렇기 때문에 매번 컴파일될 필요가 없어 반복적으로 사용해야하는 SQL를 더 빠르게 이용할 수 있다. 이는 placeholder를 가지고 있어 SQL 를작성할 때 특정 값을 직접 입력하는 대신, 나중에 값들을 동적으로 설정할 수 있다. 이는?로 표시된다. 이를 활용하면 사용자 입력을 직접 SQL문으로 만드는 대신, Prepared Statement를 사용하여 파라미터를 바인딩하는 방식으로 사용자 입력 값을 처리할 수 있어 SQL injection을 방지할 수 있다.

ODBC는 메타데이터 기능을 제공한다. 이는 DB의 모든 관계를 찾거나, 쿼리 결과나 관계의 열 이름 및 유형을 찾는 데 사용된다.

또한, ODBC에서는 각 SQL 문을 자동으로 커밋하는 기능이 있기 때문에 수동으로 하고싶다면 기능을 비활성화해야한다.

Embedded SQL

SQL 표준은 C, Java, Cobol 등 다양한 프로그래밍 언어에서 SQL을 내장하는 방법을 정의하고 있다. SQL 쿼리가 내장되는 언어를 host language라고 하며, 호스트 언어에서 허용되는 SQL 구조는 내장 SQL로 구성된다.

EXEC SQL문은 EXEC SQL <내장 SQL 문> END_EXEC 의 형태를 가지고 있어 전처리기에서 식별하기 좋다.

 

---

 

 

2.  Functions and Procrdures

 

이번에는 함수와 프로시저를 작성하고 데이터베이스에 저장한 다음 SQL 문에서 호출하는 방법을 알아볼 것이다.

프로시저와 함수는 "비즈니스 로직"을 데이터베이스에 저장하고 SQL 문에서 실행할 수 있도록한다. 비지니스 로직은 프로그래밍언어로 인코딩 될 수 있지만, DB내에서 정의하는 것이 이점이 많다.

Function

SQL의 function은 SQL 기본적으로 제공하는 함수 외에도 사용자가 직접 정의하여 사용할 수 있는 함수이다. 함수는 데이터베이스에 저장되며 SQL 문에서 호출하여 사용할 수 있다.

SQL의 함수는 일반 함수와 비슷하게 입력값을 받아서 일정한 처리를 하고, 결과값을 반환한다.

 

함수는 create function구문을 이용하여 생성한다. 뒤에는 함수 이름이 나오고 괄호 안에는 인자를 선언해준다.

returns는 반환될 값의 자료형을 표시한다.

begin를 선언한 뒤 함수의 본문이 시작되고 return으로 값을 반환하고 함수의 본문이 끝나면 end라고 명시해준다.

DELIMITER는 구분자를 ;에서 다른 문자로 변경해준다. 저 구문에서는 //로 바꿔주었고 end뒤에 //를 명시하며 하여 함수가 끝났음을 알린다. 그 후 다시 구분자를 ;로 바꿔주었다.

함수는 보통 select문의 where에서 사용이되거나 다른 함수 안에서 사용된다. 주 목적은 데이터를 수정,가공하는 것이 아니라 원하는 결과를 반환하는 것이다.

 

return값을 table로 설정할 수도 있다.

테이블에서 특절 열을 기준으로 값을 나누고 싶을 때 유용하게 사용할 수 있다.

Procedure

procedure의 가장 큰 특징은 return이 없다는 것이다. 있을 수도 있지만 대부분 사용하지 않고 결과는 보통 DB에 저장하거나 매개변수를 통해 반환한다. 또, 이를 호출하기 위해서는 CALL문을 사용한다.

생성과정은 함수와 매우 비슷하다. 프로시저는creat procedure 문를 통해 만들어진다. 리턴값이 없기때문에 지정해주지 않고 begin과 end사이에 수행할 명령을 넣는다.

프로시저는 함수와 달리 단순 반환뿐만 아니라 일련의 작업도 수행한다.

Function과 Procedure의 차이

	Function	Procedure
return	O	X
용도	특정 값 반환	작업
호출	where문 내부	call

 

Language Constructs for Procedures and Functions

SQL내부에는 함수화 프로시저를 유용하게 쓸 수 있기위해 사용할 언어가 있다.

• IF-THEN문 : ELSEIF는 붙여서 쓰고 END IF는 띄어서 쓰는 것 조심

IF<condition> then<statements>

ELSEIF<condition> then<statements>

ELSE<statements>

END IF

• CASE문

CASE<expression>

WHEN<value> then<statements>

WHEN<value> then<statements>

…

ELSE<statements>

END CASE;

• REPEAT문

label1: REPEAT

…

UNTIL<condition>

END REPEAT label1;

• WHILE문

WHILE <condition> DO

<statements>

END WHILE

 

---

3. Triggers

 

Trigger는 데이터베이스에서 데이터의 삽입, 수정, 삭제 등의 이벤트가 발생했을 때 자동으로 동작하는 특수한 종류의 저장 프로시저를 의미한다.

트리거는 특정 테이블의 특정 이벤트가 발생하면 자동으로 실행되며, 이를 이용해 데이터베이스의 일관성 유지 및 무결성을 보장할 수 있다. 또한, 특정 조건에 해당하는 데이터의 삭제나 갱신을 막을 수 있는 등의 데이터베이스 보안에 이용된다.

트리거는 create trigger문을 이용하여 만들 수 있다. trigger는 특정 이벤트를 기준으로 언제 동작할건지에 대한 정보를 BEFORE, AFTER로 표현하고, INSERT, DELETE, UPDATE 중 어떤 이벤트에 반응할건지에 대한 정보를 작성해야한다. 그리고 수행할 동작은 begin과 end사이에 기입되면 된다.

이는 trigger의 예시이다. instructor라는 table에 새로운 사람이 들어오면 department의 그 사람 부서 예산을 update해준다. 여기서 new는 새로들어온 튜플을 참조한다.