데이터베이스 프로그래밍

응용 프로그램에서 데이터베이스에 접근해 CRUD를 수행하도록 구현하려면 SQL을 소스코드에 삽입해 구현해야 하지만, 이 과정에서 저장 프로그램ㅇ을 이용해 절차적 언어 스타일로 원하는 데이터베이스 작업을 미리 만들어 두면 생산성 및 유지보수가 더욱 증가한다.

I. 저장 프로그램

저장 프로그램은 함수와 비슷한 개념으로, 일련의 작업 절차를 정의해 객체 형태로 사용하는 수행 단위이다. 저장 프로그램에는 프로시저, 트리거, 사용자 정의 함수가 있다.

II. 프로시저

프로시저 정의 방법은 다음과 같다.

선언부(CREATE PROCEDURE ...), 실행부(BEGIN ... END)로 구성
매개변수는 저장 프로시저 호출 시 전달되는 값
변수는 프로시저 내에서 사용되는 값

다음은 삽입 작업을 하는 프로시저의 예시이다.

-- 구분자 임시 지정, 프로시저 정의를 끝낼 때 사용
-- 프로시저 내의 ;와 구분
DELIMITER // 
-- 선언부
CREATE PROCEDURE insertbook (
    IN mybookid INTEGER,
    IN mybookname VARCHAR(40),
    IN mypublisher VARCHAR(40),
    IN myprice INTEGER -- 외부에서 전달받을 값들
)
-- 실행부
BEGIN
    INSERT INTO book(bookid, bookname, publisher, price)
    VALUES(mybookid, mybookname, mypublisher, myprice);
END
//
DELIMITER;

-- 프로시저 호출
CALL insertbook(13, "new_book", "new_publisher", 2000);

제어문 활용

프로시저에서는 다음과 같은 다양한 제어문이 사용될 수 있어 절자적 프로그래밍 언어의 형질을 띈다.

구문	의미
DELIMITER	구문 종료 기호 설정
BEGIN-END	실행부의 SQL문을 감싸는 블록
IF-ELSE	조건문
LOOP	LEAVE를 만나기 전까지 반복
WHILE	조건이 참이면 블록 반복. BREAK, CONTINUE 사용 가능
REPEAT	조건이 참이면 블록 반복. WHILE과 비슷
RETURN	프로시저 종료, 상태값 반환
DECLARE	변수 선언

제어문을 사용해 다음과 같이 조건에 따라 삽입 또는 업데이트하는 프로시저를 구현할 수 있다.

BEGIN
    DECLARE mycount INTEGER;

    SELECT COUNT(*) INTO mycount 
    FROM book
    WHERE bookname LIKE mybookname;

    IF mycount != 0 THEN
        UPDATE book SET price=myprice WHERE bookname LIKE mybookname;
    ELSE
        INSERT INTO book(bookid, bookname, publisher, price)
        VALUES(mybookid, mybookname, mypublisher, myprice);
    END IF;
END

결과 반환

프로시저에서 결과를 반환하게 하려면 선언부에서 반환 변수를 정의한다.

CREATE PROCEDURE AvgPrice(
  OUT avg INTEGER
)

BEGIN
  SELECT AVG(price) INTO avg FROM book;
END

커서(CURSOR)를 활용한 세밀한 로직 구성

SELECT의 결과가 다중행일 경우 일반적인 SQL 문은 데이터를 집합 단위(Set-based)로 한꺼번에 처리하지만, 커서를 사용하면 프로그래밍 언어의 for나 while 반복문처럼 행 단위로 세밀한 로직을 수행할 수 있다.

반복문 내에서 테이블의 튜플을 하나씩 가져와 데이터를 처리하는 로직 구현이 커서의 대표적인 사용 예제이다.

CREATE PROCEDURE CalcTotalSalary()
BEGIN
    -- 1. 변수 선언
    DECLARE endOfRow BOOLEAN DEFAULT FALSE; -- 반복문 종료 여부를 체크할 변수
    DECLARE v_salary INT;           -- 커서에서 가져온 급여를 담을 변수
    DECLARE total_salary INT DEFAULT 0; -- 총합을 저장할 변수

    -- 2. 커서 선언 (DECLARE CURSOR)
    -- employees 테이블의 salary 컬럼을 조회하는 커서 정의
    DECLARE my_cursor CURSOR FOR SELECT salary FROM employees;

    -- 3. 핸들러 선언 (종료 조건)
    -- 더 이상 읽을 행이 없으면(NOT FOUND), done 변수를 TRUE로 설정
    DECLARE CONTINUE handler FOR NOT FOUND SET endOfRow = TRUE;

    -- 4. 커서 열기 (OPEN)
    OPEN my_cursor;

    -- 5. 반복문 시작 (LOOP)
    read_loop: LOOP
        -- 행 데이터 가져오기 (FETCH)
        FETCH my_cursor INTO v_salary;

        -- 종료 조건: 반복문 탈출
        IF endOfRow THEN LEAVE read_loop;
        END IF;

        -- 로직 수행 (급여 누적)
        SET total_salary = total_salary + v_salary;
    END LOOP;

    -- 6. 커서 닫기 (CLOSE)
    CLOSE my_cursor;

    -- 결과 출력
    SELECT CONCAT('전체 누적 급여 = ', total_salary);
END $$

III. 트리거

트리거는 프로시저의 한 종류로, 어떤 데이터의 변경이 일어났을 때 자동으로 같이 실행된다. 트리거 실행 시점은 다음 세 가지가 있다.

실행 전: BEFORE
대체: INSTEAD OF
실행 후: AFTER

먼저 다음 문장을 root계정에서 실행해 트리거 작동 여부를 활성화한다.

SET GLOBAL log_bin_trust_function_creators = ON;

다음은 삽입 발생 후 시점에서 실행되는 트리거의 예제이다.

CREATE TRIGGER afterinsertbook
    -- 실행 시점: AFTER INSERT -> 삽입 후
    -- FOR EACH ROW: 영향을 받는 모든 행에 수행하라는 의미
    AFTER INSERT ON book FOR EACH ROW
BEGIN
    DECLARE average INTEGER;
    INSERT INTO book_log
    -- 삽입이 이루어진 튜플이 자동으로  NEW 변수에 할당된다.
    VALUES(NEW.bookid, NEW.bookname, NEW.publisher, NEW.price);
END;

IV. 사용자 정의 함수

프로시저는 CALL 명령에 의해 수행되는 독립된 프로그램이였다면, 함수는 SQL문이나 프로시저 안에서 호출되어 값을 제공하는 프로그램 조각이다. MySQL에선 단일 값을 반환하는 스칼라 함수가 일반적이다.

다음은 책 가격에 따라 이익을 계산해 반환하는 간단한 사용자 정의 함수이다.

CREATE FUNCTION get_benefit(price INT) RETURNS INT
BEGIN
    DECLARE benefit INT;
    IF price >= 30000 THEN SET benefit = price * 0.1;
    ELSE SET benefit = price * 0.05;
    END IF;
    RETURN benefit;
END

이러한 함수는 다음과 같이 SELECT문 안에서 호출할 수 있다.

SELECT orderid, saleprice, get_benefit(saleprice) AS benefit
FROM Orders;

V. 저장 프로그램 요약

프로시저, 트리거, 사용자 정의 함수의 공통점과 차이점

구분	프로시저	트리거	사용자 정의 함수
공통점	저장 프로그램	저장 프로그램	저장 프로그램
정의 방법	CREATE PROCEDURE 문	CREATE TRIGGER 문	CREATE FUNCTION 문
호출 방법	CALL 문으로 직접 호출	INSERT, DELETE, UPDATE 문이 실행될 때 자동으로 실행	SELECT 문에 포함
기능 차이	SQL 문으로 할 수 없는 복잡한 로직을 수행함	기본값 제공, 데이터 제약 준수, SQL 뷰의 수정 및 무결성 유지 작업 수행	속성값 가공 반환, SQL문에서 직접 사용

저장 프로그램의 기본 문법

구분	명령어
데이터 정의어	CREATE TABLE
	CREATE PROCEDURE
	CREATE FUNCTION
	CREATE TRIGGER
	DROP
데이터 조작어	SELECT
	INSERT
	DELETE
	UPDATE
데이터 타입	INTEGER, VARCHAR(n), DATE
변수	DECLARE 문으로 선언
	치환(SET, = 사용)
연산자	산술 연산자
	비교 연산자
	문자열 연산자
	논리 연산자(NOT, AND, OR)
주석	--, /* */
내장 함수	숫자 함수(ABS, CEIL, FLOOR, POWER 등)
	집계 함수(AVG, COUNT, MAX, MIN, SUM)
	날짜 함수(SYSDATE, DATE, DATNAME 등)
	문자 함수(CHAR, LEFT, LOWER, SUBSTR 등)
제어문	BEGIN-END
	IF-THEN-ELSE
	WHILE, LOOP
데이터 제어어	GRANT
	REVOKE