서울대학교 이상구 교수님의 SNUON 강의 데이타베이스: 빅데이터 시대의 필수 정보관리 개론와 Database System Concepts 7th Edition의 내용을 바탕으로 정리한 내용입니다.

강의에서는 다루지 않는 내용으로, Database System Concepts의 Chapter 5를 번역 및 정리해서 포스팅합니다.

2. Functions and Procedures

여기서는 어떻게 개발자들이 함수와 프로시저를 만들고, 그것들을 DB에 저장하고, SQL문에서 호출할 수 있는지에 대해 알아본다. 함수는 이미지나 기하학적 객체와 같은, 특수한 데이터 타입들을 다룰 때 특히 유용하다. 예를 들어, 지도 DB에서 쓰이는 선분 데이터 타입은 두 선분이 서로 교차하는지를 확인하는 함수와 연관되어 있을 수 있고, 이미지 데이터 타입은 두 이미지의 유사도를 비교하는 함수와 연관되어 있을 수 있다.

프로시저와 함수는 비즈니스 로직을 DB에 저장하고, SQL문을 통해 실행될 수 있도록 한다. 이러한 비즈니스 로직들은 DB 밖의 프로그래밍 언어 프로시저로 인코딩되어 저장될 수도 있지만, 이것들을 DB에 프로시저로 저장하는 데에는 많은 이점들이 있다. 예를 들어 이는 많은 애플리케이션들이 해당 프로시저에 접근할 수 있게 하고, 비즈니스 룰이 바뀌는 경우, 애플리케이션의 다른 부분들을 변경이 필요없는, 단일 변화 지점을 제공한다. 그러면 애플리케이션 코드는 직접 DB 릴레이션들을 업데이트하지 않고도 저장된 프로시저를 호출하기만 하면 된다.

SQL을 통해 함수, 프로시저, 메서드를 정의할 수 있다. 이것들은 SQL의 절차적 컴포넌트(procedural component)를 통해서도 정의될 수 있고, 다른 외부 프로그래밍 언어를 통해서도 정의될 수 있다. 우선은 SQL을 이용한 정의를 보고 나서, 다른 언어들을 이용한 정의를 살펴보도록 한다.

여기서 쓰이는 문법이 SQL 표준으로 정의되어있기는 하지만, 대부분의 데이터베이스는 이 문법의 비표준적 버전들을 구현한다. 예를 들어 Oracle, TransactSQL, PostgreASQL가 지원하는 절차적 언어는 모두 여기서 쓰이는 표준 문법과는 다르다. 각 시스템에 대한 더 세부적인 내용을 알고 싶다면 해다 시스템의 매뉴얼을 참고하도록 하자. 비록 이런 시스템들이 이 문법을 표준적으로 지원하지 않기도 하지만, 여기서 배울 개념들은 구현 방식이나 문법 차이와 별개로 적용 가능하다.

2-1. Declaring and Invoking SQL Functions and Procedures

Functions

학부의 이름이 주어졌을 때, 해당 학부 내에 있는 강사들의 수를 반환하는 함수를 만들고 싶다고 하자. 이 함수는 다음과 같이 정의할 수 있다.

create function dept_count(dept_name varchar(20))
	returns integer
	begin
	declare d count integer;
		select count(*) into d count
		from instructor
		where instructor.dept_name= dept_name
	return d count;
	end

위 함수는 다음과 같이 다른 쿼리에서 사용될 수 있다.

select dept_name, budget
from department
where dept_count(dept_name) > 12;

다만, 많은 DB 시스템에서 복잡한 사용자 정의 함수를 쿼리에서 호출할 때, 성능 문제가 나타나왔다. 그러므로 사용자 정의 시스템을 쿼리에 쓸지 말지를 결정할 때에는 반드시 성능을 고려해야 한다.

SQL 표준은 테이블을 결과로 반환하는 함수를 지원하며, 이러한 함수를 가리켜 테이블 함수(table function)라 부른다. 아래와 같은 함수를 살펴보자. 이 함수는 특정 학부의 모든 강사들을 포함하는 테이블을 반환한다. 함수의 파라미터는 함수명.파라미터의 방식으로 참조할 수 있다는 것을 염두에 두자 (맨 아랫줄의 instructor_of.dept_name).

create function instructor_of (dept_name varchar(20))
	returns table (
		ID varchar (5),
		name varchar (20),
		dept_name varchar (20),
		salary numeric (8,2))
	return table
		(select ID, name, dept_name, salary
		from instructor
		where instructor.dept_name = instructor of.dept_name);

이 함수도 다음과 같이 쿼리에서 사용될 수 있다.

select *
from table(instructor of ('Finance'));

이렇게 간단한 경우에서는 테이블 함수를 사용하지 않고 쿼리를 사용하는 것도 좋겠지만, 일반적으로 테이블 함수는 파라미터를 이용해 보통의 뷰 개념을 일반화한, 매개화된 뷰(parameterized view)로 생각될 수 있다.

Procedures

SQL은 프로시저도 지원한다. 위에서의 dept_count 함수는 다음과 같은 프로시저로 바꿀 수도 있다.

create procedulre dept_count_proc(in dept_name varchar(20),
											 out d_count integer)
	begin
		select count(*) into d_count
		from instructor
		where instructor.dept_name = dept_count_proc.dept_name
	end

in, out 키워드는 프로시저의 입출력 파라미터들을 가리키는 데 쓰인다.

프로시저는 call문을 통해 SQL 프로시저나, 임베디드 SQL에서 호출될 수 있다.

delcare d_count integer
call dept_count_proc('Physics', d_count);

프로시저나 함수는 JDBC에서 본 것처럼 동적 SQL에서도 호출될 수 있다

SQL의 프로시저들은 서로 같은 이름을 사용해도 되는데, 다만 이때, 각 프로시저의 인자의 수는 서로 달라야 한다. 이 이름은 프로시저를 식별하는 데 쓰인다. 함수의 경우의 마찬가지로, 인자의 수가 다르다면 같은 이름을 허용한다. 함수는 또한 적어도 한 인자의 데이터 타입이 다르다면, 인자의 수가 같더라도 같은 이름을 사용할 수 있다.

2.2 Language Constructs for Procedures and Functions

SQL은 Persistent Storage Module(PSM)이라는 구문을 제공하며, 이는 범용 프로그래밍 언어와 같은 기능을 구현하는 데 쓰인다.

변수는 declare 문을 통해 선언되며, 이 변수에는 어떠한 SQL 데이터 타입이든 담을 수 있다. 할당은 set문을 통해 이루어진다.

구문의 합성은 begin ... end의 형식으로 이루어지며, 이 안에는 여러 개의 SQL문을 넣을 수 있다. 지역 변수 또한 이 안에서 선언될 수 있다. begin atomic ... end문은 그 안의 모든 SQL문이 하나의 트랜잭션으로 실행됨을 보장한다.

while문과 repeat문도 있으며, 다음과 같이 사용될 수 있다.

while boolean expression do
	sequence of statements;
end while

repeat
	sequence of statements;
	until boolean expression
end repeat

쿼리 결과를 순회하기 위한 for 반복문도 있다. 이 프로그램은 for 반복문 변수(r)에 쿼리 결과를 한 번에 한 행 씩 가져온다.

declare n integer default 0;
for r as
	select budget from department
	where dept name = ‘Music‘
do
	set n = n− r.budget
end for

leave문은 루프를 빠져나오기 위해 사용하며, iterate는 다음 튜플로 넘어갈 때 사용한다. 자바나 C/C++의 break, continue와 같다고 생각하면 된다.

SQL의 조건문은 if-then-else문들을 통해 지원되며, C/C++의 case문과 유사한 case문도 있다.

if boolean expression
	then statement or compound statement
elseif boolean expression
	then statement or compound statement
else statement or compound statement
end if

SQL의 절차적 언어는 예외 조건(exception conditions) 시그널링과, 해당 예외에 대한 핸들러(handler) 선언도 지원한다.

declare out_of_classroom_seats condition
declare exit handler for out_of_classroom_seats
begin
sequence of statements
end

위 SQL의 begin, end 사이의 구문은 out_of_classroom_seats 예외가 발생했을 때 취할 행동들이다. 예외가 발생했을 때 취할 수 있는 다른 액션에는, 예외를 발생한 구문 뒤의 구문을 계속해서 실행하는 continue도 있다. 이렇게 직접 정의한 예외 조건 외에도, 선-정의되어 있는 조건으로 sqlexception, sqlwarning, not found등이 있다.

2.3. External Language Routines

SQL에서 지원하는 절차적 언어가 유용하기는 하지만, 이것들이 여러 DB에서 표준 방식으로 지원되는 것은 아니다. 가장 기본적인 기능마저도 다른 문법과 의미를 가진다. 결과적으로 프로그래머들은 각 데이터베이스 제품들에 대해 새로운 언어를 배워야한다. 대체 방법으로는 명령형 프로그래밍 언어에서 프로시저를 정의하고, SQL 쿼리와 트리거 정의에서 호출하는 것이다.

SQL은 자바, C#, C, C++과 같은 프로그래밍 언어에서 함수를 정의할 수 있도록 한다. 이러한 방법으로 정의된 함수들은 SQL에서 정의된 함수들보다 더 효율적이며, SQL에서 수행될 수 없는 계산들도 이 함수들을 통해서는 수행될 수 있다.

외부 프로시저와 함수는 다음과 같은 방식으로 명시될 수 있다. 다만 정확한 문법은 사용하는 DB 시스템에 따라 달라질 수 있다.

create procedure dept count proc( in dept name varchar(20),
									out count integer)
language C
external name '/usr/avi/bin/dept count proc'

create function dept count (dept name varchar(20))
returns integer
language C
external name '/usr/avi/bin/dept count'

일반적으로 외부 언어로 작성된 프로시저는 파라미터와 반환값의 널 값들에 대한 처리를 해줘야 하고, 실패/성공 상태에 대한 통신과 예외에 대한 처리도 해줘야 한다. 이러한 정보는 추가적인 파라미터들을 이용해 전달될 수 있다. 여기에는 실패/성공 상태를 전달하기 위한 sqlstate 값, 함수의 반환값을 담기 위한 파라미터, 각 파라미터/함수의 결과의 값이 널인지를 가리키는 변수 등이 있다. 널 값을 처리하기 위해서는 다른 메커니즘도 쓸 수 있는데, 예를 들면 값 대신 포인터를 전달하는 방식이 있다. 정확한 메커니즘은 데이터베이스에 따라 달라진다. 하지만 만약 함수가 이러한 상황들을 처리해주지 않는 경우, 외부 프로시저나 함수가 널 값이나 예외를 처리하지 않고 주어진 인자만 취한다는 것을 가리키기 위해 parameter style general을 사용할 수도 있다.

데이터베이스 시스템 밖에서, 프로그래밍 언어로 정의된 함수들은 데이터베이스 시스템 코드를 통해 로드되고 실행된다. 하지만 이러한 방식에서는 프로그램 내의 버그가 데이터베이스의 내부 구조를 오염시킬 수 있으며, DB 시스템의 접근 제어 기능을 우회할 수도 있다. 보안보다 효율성에 더 집중하는 데이터베이스 시스템은 이러한 방식으로 프로시저를 실행할 것이고, 그 반대의 데이터베이스 시스템은 그 코드를 별개의 프로세스로 실행하고, IPC(interprocess communication)을 통해 파라미터 값을 받아오고 결과를 내보낼 것이다. 하지만 IPC의 시간적 오버헤드는 상당히 크다. 전형적인 CPU 아키텍처에서는, 하나의 IPC에 걸리는 시간동안 수만에서 수십만 개의 명령어를 실행할 수 있을 정도다.

만약 코드가 자바나 C#과 같이 "안전한" 언어로 쓰인다면, 다른 방법도 있다. 해당 코드를 데이터베이스 쿼리 실행 프로세스 내의 샌드박스(sandbox)에서 직접 실행하는 것이다. 샌드박스는 자바나 C# 코드가 그 자신의 메모리 영역에는 접근할 수 있게 하지만, 코드가 쿼리 실행 프로세스의 메모리를 읽고 쓰거나, 파일 시스템에 접근하는 것은 막는다. 이를 통해 IPC를 막음으로써 함수 호출의 오버헤드를 크게 줄일 수 있다.

오늘날의 많은 DB 시스템들은 외부 언어 루틴이 쿼리 실행 프로세스 내의 샌드박스에서 실행되도록 지원한다. 예를 들어 오라클과 IBM DB2는 자바 함수가 데이터베이스 프로세스에서 실행될 수 있도록 한다. Microsoft SQL Server는 CLR(Common Language Runtime)으로 컴파일된 프로시저를 데이터베이스 프로세스에서 실행될 수 있게 한다. 이 프로시저들은 C#, VB 등으로 쓰인 프로시저들이다. PostgreSQL은 Perl, Python, Tcl,과 같은, 여러 언어를 통한 함수 정의도 지원한다.