통상적인 관계형 데이터베이스 작업은 DB와 Connection 을 맺고 SQL을 던져서 질의 결과를 반환받는 과정이다. Connection 이 성공적으로 맺어지고 SQL 구문 상의 오류가 없다면 RDB는 전달받은 SQL에 맞춘 알맞은 실행 계획을 세워 질의를 수행하고 결과를 만들어 반환해준다. 거의 모든 데이터베이스 작업은 이런 DML 작업으로 충분히 처리되지만 간혹 조금 더 자유도가 높은 처리가 필요한 때가 있다. 가령 Table의 Row 단위로 처리를 통제하거나 질의 내에서 Loop 를 돌리는 처리 등이 있겠다.
CURSOR
커서는 흔히 GUI 환경에서 마우스를 따라 움직이는 작은 이미지를 가리키는 명칭으로 쓰인다. 프로그래밍적으로 말하자면 커서는 마우스의 위치와 마우스 동작 이벤트의 정보를 저장하는 메모리 객체라고 이해할 수 있는데, RDB에서의 커서도 이와 크게 다르지 않은 맥락으로 이해할 수 있다. 위키피디아에 기술된 데이터베이스 CURSOR의 정의는 아래와 같다.
A mechanism that enables traversal over the records in a database
데이터베이스의 Record를 따라 순회할 수 있는 매카니즘이라고 한다. NOSQL 중에는 컬럼 기반의 저장 구조를 가진 데이터베이스들이 있지만 RDB에서 Record는 Row와 동일하다고 보면 되겠다. 그러니까 Cursor는 Row를 순회하면서 특정한 처리 동작을 하는 매커니즘 혹은 그러한 기능을 말한다. 언뜻 생각하기에 이건 RDB의 내부 질의 구조 상의 한 개념으로 이해될 수 있지만 그건 커다란 오해다. 일반적인 DML SQL 실행과 Cursor 실행은 아무런 관계도 없다. CURSOR는 그저 데이터베이스 Client 입장에서 사용 가능한 RDB의 기본 기능 같은 것이겠다.
앞서의 이해에 따라 DML SQL 실행과 CURSOR를 통한 실행을 간단히 비교해볼 수 있다. 여기에서 외부 서버에 접근하여 문서 파일을 복사해오는 작업을 예시로 들어볼 수 있다. DML SQL 실행은 DB와 Connection 을 맺고 SQL 구문을 보내고 결과값을 받는다. 이건 서버에 ssh 연결을 한 후 파일을 로컬 머신에 복사하는 과정과 유사하다. 말하자면 scp 작업을 하는 것! 반면 CURSOR 실행은 Connection을 맺은 후 문서 파일을 열어 글자 단위로 복사를 해 파일을 가져오는 과정이라 할 수 있겠다. 문서 파일을 열어 글자와 글자 사이를 오가며 추가, 삭제 작업을 할 수 있는 것이며 이때 내 키보드 동작에 맞추어 글자 사이를 오가는 막대기의 이름도 공교롭게 커서이다.
커서를 사용한 처리의 예시는 아래에 있다.
LOOP
굳이 Row를 따라서 하나씩 처리를 할 필요는 없지만 전체적인 동작 상에서 반복문을 사용해야하는 경우가 있다. 예를 들자면 DB 상에 A, B, C 테이블이 있고 이 중 특정한 이름의 컬럼을 가지고 있는 테이블의 갯수를 반환받고 싶을 때가 있다. 물론 Client가 반복적으로 SQL을 처리하고 그 결과값을 취합해 Client의 프로그램으로 최종값을 얻어내는 식으로 처리가 가능하다. 하지만 어떤 경우 데이터베이스에 질의를 날리는 것이 상당한 비용을 수반할 때가 있고 이땐 자연히 하나의 쿼리문을 통해 원하는 최종결과값을 얻는 것이 훨씬 효율적이게 된다. 이럴 때 사용할 수 있는 것이 LOOP 이다.
LOOP를 RDB의 한 기능으로 말해야할 지, SQL 상의 한 구문이라 말해야할 지는 참 모호하다. 어쨌든 중요한 건 어떻게 이걸 사용하냐는 것이겠다.
PROCEDURE
Procedure는 프로그래밍 상에서 여러 명령어들의 묶음을 가리키는 용어이다. RDB에서도 다르지 않다. 여러 SQL 구문을 묶어서 처리할 수 있게하는 것이 곧 PROCEDURE 이다. RDB에서 PROCEDURE는 SQL의 묶음 그 자체를 표현하며 동시에 그러한 SQL 묶음을 한 큐에 처리하는 RDB의 기능을 말하기도 한다. RDB 상에서 테이블을 만들어 이를 요리조리 이용하듯 이 PROCEDURE라는 것도 만들고 RDB 시스템에 저장한 후 필요할 때마다 꺼내 사용할 수 있다. 이런 점에서 FUNCTION과 상당히 유사 개념으로 보이는데, 실제 작동상의 표현 방식도 다르고 FUNCTION은 INPUT에 따라 OUTPUT을 내는 알고리즘인데 반해 PROCEDURE는 input, output 매커니즘에 구속되지 않는다는 점에서 확실히 구별된다.
PROCEDURE에 대한 자세한 연구는 다음에 하기로 하고 여기선 PROCEDURE와 CURSOR, LOOP의 관계에 대해서만 짚어보자. 앞서 설명했던 CURSOR와 LOOP는 일반적인 DML 구문처럼 사용될 수 없고 반드시 PROCEDURE를 통해서 정의되고 사용되어야 한다. 꼭 PROCEDURE가 아니라 FUNCTION이나 TRIGGER를 통해서도 사용되지만 가장 자연스러운 사용방식이 PROCEDURE를 통한 사용이다. 왤까? 이 점에 대해서는 정확히 파악하지 못했다. 추측컨데 RDB가 가지고 있는 '쿼리를 통해 실행계획을 세우는 메커니즘'은 CURSOR나 LOOP 구문을 해독하지 못하고 따라서 대안으로 CURSOR 및 LOOP를 PROCEDURE로 묶고 PROCEDURE를 여러 SQL 구문으로 풀어헤치는 과정을 거친 후 이를 '쿼리를 통해 실행계획을 세우는 메커니즘'에 올려태우는게 아닌가 싶다. 뇌절일 수도 있지만 이와 다른 이유는 딱히 생각나지 않는다. 이에 대해선 앞으로 연구를 해보기로 한다.
관건은 CURSOR와 LOOP의 개념을 이해하고 이것들이 PROCEDURE를 통해 정의되고 사용된다는 점이다. 이제 이에 대한 간단한 사용예시를 알아보자.
제시된 표 변환을 수행한다고 해보자. Record 단위로 알파벳이 나열된 Alphabet 테이블을 Record 별 3개의 묶음이 존재하는 Collection 테이블로 변환하는 예시다. Pandas가 떠오르게하는 이런 작업을 SQL을 통해 처리할 수 있게 해주는 것이 CURSOR, LOOP, PROCEDURE이다!
우선 Alphabet 테이블을 만들어보자.
DROP TABLE IF EXISTS alphabet;
CREATE TABLE alphabet (
id INT NOT NULL ,
word VARCHAR(30)
);
INSERT INTO alphabet VALUES( 1, 'A');
INSERT INTO alphabet VALUES( 2, 'B');
INSERT INTO alphabet VALUES( 3, 'C');
INSERT INTO alphabet VALUES( 5, 'E');
INSERT INTO alphabet VALUES( 6, 'F');
INSERT INTO alphabet VALUES( 8, 'H');
SELECT * FROM alphabet;
그런 다음 Collection 테이블을 만들어보자. 조회만을 위해 이용할 테이블이기에 Temporary로 만들자. Temporary 테이블은 데이터베이스와 Client가 맺은 Session 동안에만 유효하게 존재하는 테이블을 말한다.
DROP TEMPORARY TABLE IF EXISTS collection;
CREATE TEMPORARY TABLE collection (
first varchar(5),
second varchar(5),
third varchar(5)
);
다음으로 데이터 변환 처리를 위한 PROCEDURE 를 구성해준다.
DROP TEMPORARY TABLE IF EXISTS collection;
CREATE TEMPORARY TABLE collection (
first varchar(5),
second varchar(5),
third varchar(5)
);
DROP PROCEDURE IF EXISTS ts_prc;
CREATE PROCEDURE ts_prc()
BEGIN
DECLARE FIRST_VAL VARCHAR(5) DEFAULT NULL;
DECLARE SECOND_VAL VARCHAR(5) DEFAULT NULL;
DECLARE THIRD_VAL VARCHAR(5) DEFAULT NULL;
DECLARE _endOfRow BOOLEAN DEFAULT FALSE;
DECLARE _id INT;
DECLARE _cursor CURSOR FOR SELECT id FROM alphabet;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET _endOfRow = TRUE;
OPEN _cursor;
_loop : LOOP
FETCH _cursor INTO _id;
IF _endOfRow THEN
INSERT INTO collection VALUES (FIRST_VAL, SECOND_VAL, THIRD_VAL);
LEAVE _loop;
END IF;
IF _id % 3 = 1 THEN
SET FIRST_VAL = (SELECT word from alphabet WHERE id = _id);
ELSEIF _id % 3 = 2 THEN
SET SECOND_VAL = (SELECT word from alphabet WHERE id = _id);
ELSE
SET THIRD_VAL = (SELECT word from alphabet WHERE id = _id);
INSERT INTO collection VALUES (FIRST_VAL, SECOND_VAL, THIRD_VAL);
SET FIRST_VAL = (SELECT null);
SET SECOND_VAL = (SELECT null);
SET THIRD_VAL = (SELECT null);
END IF;
END LOOP _loop;
CLOSE _cursor;
END;
그런 다음 프로시져를 호출하고 collection 에서 데이터를 조회하면 변환된 테이블을 얻을 수 있다. collection 테이블을 Temporary로 만들었기 때문에 프로시저 구성, 호출, SELCT은 모두 하나의 Query Session 에서 수행되어야 한다. 말하자면 하나의 SQL 파일로 만들어 DB에 던지라는 말!
아무쪼록 성공적으로 테이블 변환에 성공했다.
CALL ts_prc();
SELECT * FROM collection;
#########################
# first, second, third #
# A, B, C #
# null, E, F #
# null, H, null #
#########################
참조