[SQL] 04-3 SQL 프로그래밍

2024-05-29 7 분 소요

SQL 고급 문법

SQL 프로그래밍

스토어드 프로시저는 MySQL에서 프로그래밍 기능이 필요한 때 사용하는 데이터베이스 개체이다.
SQL 프로그래밍은 기본적으로 스토어드 프로시저 안에 만들어야 한다.

스토어드 프로시저는 다음과 같은 구조를 갖는다.

DELIMITER $$
CREATE PROCEDURE 스토어드_프로시저_이름()
BEGIN
    이 부분에 SQL 프로그래밍 코딩
END $$
DELIMITER ;
CALL 스토어드_프로시저_이름()

IF 문

IF 문은 조건문으로 가장 많이 사용되는 프로그래밍 문법 중 하나이다.
IF 문을 활용하면 다양한 조건을 처리할 수 있다.

IF 문의 기본 형식

IF 문은 조건식이 참이라면 ‘SQL문장들’을 실행하고, 그렇지 않으면 그냥 넘어간다.

기본 IF 문의 형식은 다음과 같다.

IF <조건식> THEN
        SQL 문장들
END IF;

‘SQL문장들’이 한 문장이라면 그 문장만 써도 되지만, 두 문장 이상이 처리되어야 할 때는 BEGIN ~ END로 묶어줘야 한다.
간단한 예를 살펴보겠다.

DROP PROCEDURE IF EXISTS ifproc1;
DELIMITER $$
CREATE PROCEDURE ifproc1()
BEGIN
    IF 100 = 100 THEN
        SELECT '100은 100과 같습니다.';
    END IF;
END $$
DELIMITER ;
CALL ifproc1();

만약 기존에 ifproc1()을 만든 적이 있다면 삭제한다.
세미콜론(;)으로는 SQL의 끝인지, 스토어드 프로시저의 끝인지 구별할 수 없어서 $$를 사용한다.
스토어드 프로시저의 이름을 ifproc1()로 지정한다.
조건식으로 100과 100이 같은지 비교한다.
당연히 참(True)일 테니 다음 행이 실행될 것이다.
CALL로 호출하면 ifproc1()이 실행된다.

[Output] 100은 100과 같습니다.

IF ~ ELSE 문

IF ~ ELSE 문은 조건에 따라 다른 부분을 수행한다.
조건식이 참이라면 ‘SQL문장들1’을 실행하고, 그렇지 않으면 ‘SQL문장들2 ‘를 실행한다.

예를 살펴보겠다.

DROP PROCEDURE IF EXISTS ifproc2;
DELIMITER $$
CREATE PROCEDURE ifproc2()
BEGIN
    DECLARE myNum INT;
    SET myNum = 200;
    IF myNum = 100 THEN
        SELECT '100입니다.';
    ELSE
        SELECT '100이 아닙니다.';
    END IF;
END $$
DELIMITER ;
CALL ifproc2();

DECLARE 예약어를 사용해서 myNum 변수를 선언한다.
제일 뒤에는 변수의 데이터 형식을 INT로 지정한다.
SET 예약어로 myNum 변수에 200을 대입한다.
myNum이 100인지 아닌지를 구분한다.

[Output] 100이 아닙니다.

IF 문의 활용

아이디가 APN인 회원의 데뷔 일자가 5년이 넘었는지 확인해보고 5년이 넘었으면 축하 메시지를 출력해보겠다.

DROP PROCEDURE IF EXISTS ifproc3;
DELIMITER $$
CREATE PROCEDURE ifproc3()
BEGIN
    DECLARE debutDate DATE;
    DECLARE curDate DATE;
    DECLARE days INT;
    SELECT dubut_date INTO debutDate
        FROM market_db.member
        WHERE mem_id = 'APN';

    SET curDate = CURRENT_DATE();
    SET days = DATEDIFF(curDate, dubutDate);

    IF (days/365) >= 5 THEN
        SELECT CONCAT('데뷔한 지 ', days, '일이나 지났습니다. 축하합니다!');
    ELSE
        SELECT '데뷔한 지 ' + days + '일밖에 안되었네요. 파이팅~';
    END IF;
END $$
DELIMITER ;
CALL ifproc3();

변수를 3개 준비한다.
데뷔 일자는 debutDate에, 오늘 날짜는 curDate에, 데뷔 일자부터 오늘까지 며칠이 지났는지는 days에 저장할 예정이다.
APN의 데뷔 일자(debut_date)를 추출하는 SELECT 문이다.
그런데 그냥 SELECT와 달리 INTO 변수가 붙었다.
이럴 경우 결과를 변수에 저장한다.
결국 APN의 데뷔 일자가 debutDate에 저장된다.
CURRENT_DATE() 함수로 현재 날짜를 curDate에 저장한다.
DATEDIFF() 함수로 데뷔 일자부터 현재 날짜까지 일수를 days에 저장한다.
일자가 저장된 days를 365로 나눠서 연으로 변환한 후 5년이 넘는 것과 그렇지 않은 경우에 메시지를 다르게 출력한다.

[Output] 데뷔한 지 4857일이나 지났습니다. 축하합니다!

날짜 관련 함수

CURRENT_DATE(): 오늘 날짜를 알려준다.
CURRENT_TIMESTAMP(): 오늘 날짜 및 시간을 함께 알려준다.
DATEDIFF(날짜1, 날짜2): 날짜2부터 날짜1까지 일수로 며칠인지 알려준다.

SELECT CURRENT_DATE(), DATEDIFF('2021-12-31', '2000-1-1');

[Output] 2024-05-29, 8035

CASE 문

여러 가지 조건 중에서 선택해야 하는 경우 CASE 문을 사용해서 조건을 설정할 수 있다.

CASE 문의 기본 형식

IF 문은 참 아니면 거짓 두 가지만 있기 때문에 2중 분기라는 용어를 사용한다.
CASE 문은 2가지 이상의 여러 가지 경우일 때 처리가 가능하므로 ‘다중 분기’라고 부른다.

CASE 문의 형식은 다음과 같다.

CASE
  WHEN 조건1 THEN
    SQL 문장들1
  WHEN 조건2 THEN
    SQL 문장들2
  WHEN 조건3 THEN
    SQL 문장들3
  ELSE
    SQL 문장들4
END CASE;

CASE와 END CASE 사이에는 여러 조건을 넣을 수 있다.
WHEN 다음에 조건이 나오는데, 조건이 여러 개라면 WHEN을 여러 번 반복한다.
그리고 모든 조건에 해당하지 않으면 마지막 ELSE 부분을 수행한다.

예로 시험 점수와 학점을 생각해보겠다.
90점 이상은 A, 80점 이상은 B, 70점 이상은 C, 60점 이상은 D, 60점 미만은 F로 나눌 수 있다.
이때 5가지의 경우에 따라 달라지므로 CASE를 사용한다.

DROP PROCEDURE IF EXISTS caseProc;
DELIMITER $$
CREATE PROCEDURE caseProc()
BEGIN
    DECLARE point INT;
    DECLARE credit CHAR(1);
    SET point = 88;

    CASE
      WHEN point >= 90 THEN
        SET credit = 'A';
      WHEN point >= 80 THEN
        SET credit = 'B';
      WHEN point >= 70 THEN
        SET credit = 'C';
      WHEN point >= 60 THEN
        SET credit = 'D';
      ELSE
        SET credit = 'F';
    END CASE;
    SELECT CONCAT('취득점수==>', point), CONCAT('학점==>', credit);
END $$
DELIMITER ;
CALL caseProc();

시험 점수 point 변수에 88을 입력하고, 학점을 저장한 credit 변수를 준비한다.
point에 따라서 credit을 A부터 F까지 설정한다.
앞의 모든 조건에 해당하지 않으면 F학점으로 처리한다.
결과를 출력한다.

[Output] 취득점수==>88, 학점==>B

CASE 문의 활용

인터넷 마켓 데이터베이스(market_db)의 회원들의 총 구매액을 계산해서 회원의 등급을 다음과 같이 4단계로 나누려 한다.

총 구매액	회원 등급
1500 이상	최우수고객
1000 ~ 1499	우수고객
1 ~ 999	일반고객
0 이하 (구매한 적 없음)	유령고객

먼저 구매 테이블(buy)에서 회원별로 총 구매액을 구해보겠다.

SELECT mem_id, SUM(price*amount) "총구매액"
    FROM buy
    GROUP BY mem_id;

추가로 ORDER BY를 사용해서 총 구매액이 많은 순서로 정렬하겠다.

SELECT mem_id, SUM(price*amount) "총구매액"
    FROM buy
    GROUP BY mem_id
    ORDER BY SUM(price*amount) DESC;

이번에는 회원의 이름도 출력해보겠다.
그런데 회원의 이름은 회원 테이블(member)에 있으므로 구매 테이블(buy)과 조인시켜야 한다.

SELECT B.mem_id, M.mem_name, SUM(price*amount) "총구매액"
    FROM buy B
        INNER JOIN member M
        ON B.mem_id = M.mem_id
    GROUP BY B.mem_id
    ORDER BY SUM(price*amount) DESC;

이번에는 구매하지 않은 회원의 아이디와 이름도 출력해보겠다.
내부 조인 대신에 외부 조인을 시키면 된다.
그리고 구매 테이블에는 구매한 적이 없어도 회원 테이블에 있는 회원은 모두 출력해야 하므로 INNER JOIN을 RIGHT OUTER JOIN으로 변경한다.

주의할 점은 구매 테이블에서는 4명만 구매했으므로, 나머지 6명에 대한 아이디 등의 정보가 없다.
그래서 SELECT에서 회원 테이블의 아이디인 M.mem_id를 조회하고 GROUP BY도 M.mem_id로 변경한다.

SELECT M.mem_id, M.mem_name, SUM(price*amount) "총구매액"
    FROM buy B
        RIGHT OUTER JOIN member M
        ON B.mem_id = M.mem_id
    GROUP BY M.mem_id
    ORDER BY SUM(price*amount) DESC;

이제 총 구매액에 따라 회원 등급을 구분해보겠다.

SELECT M.mem_id, M.mem_name, SUM(price*amount) "총구매액",
      CASE
          WHEN (SUM(price*amount) >= 1500) THEN '최우수고객'
          WHEN (SUM(price*amount) >= 1000) THEN '우수고객'
          WHEN (SUM(price*amount) >= 1) THEN '일반고객'
          ELSE '유령고객'
      END "회원등급"
    FROM buy B
        RIGHT OUTER JOIN member M
        ON B.mem_id = M.mem_id
    GROUP BY M.mem_id
    ORDER BY SUM(price*amount) DESC;

WHILE 문

WHILE 문은 필요한 만큼 계속 같은 내용을 반복할 수 있다.

WHILE 문의 기본 형식

WHILE 문은 조건식이 참인 동안에 ‘SQL문장들’을 계속 반복한다.

WHILE 문의 형식은 다음과 같다.

WHILE <조건식> DO
    SQL 문장들
END WHILE;

1에서 100까지의 값을 모두 더하는 간단한 기능을 WHILE 문으로 구현해보겠다.

DROP PROCEDURE IF EXISTS whildProc;
DELIMITER $$
CREATE PROCEDURE whileProc()
BEGIN
    DECLARE i INT;
    DECLARE hap INT;
    SET i = 1;
    SET hap = 0;

    WHILE (i <= 100) DO
        SET hap = hap + i;
        SET i = i + 1;
    END WHILE;

    SELECT '1부터 100까지의 합 ==>', hap;
END $$
DELIMITER ;
CALL whileProc();

1, 2, 3, …으로 증가할 변수 i와 합계를 누적할 변수 hap을 준비한다.
i가 100 이하인 동안에 계속 반복한다.
i를 계속 hap에 누적시키고, i는 1씩 증가시킨다.

[Output] 1부터 100까지의 합 ==> 5050

WHILE 문의 응용

ITERATE[레이블]: 지정한 레이블로 가서 계속 진행한다.
LEAVE[레이블]: 지정한 레이블을 빠져나간다. (즉 WHILE 문이 종료된다.)

DROP PROCEDURE IF EXISTS whildProc2;
DELIMITER $$
CREATE PROCEDURE whileProc2()
BEGIN
    DECLARE i INT;
    DECLARE hap INT;
    SET i = 1;
    SET hap = 0;

    myWhile:
    WHILE (i <= 100) DO
        IF (i%4 = 0) THEN
            SET i = i + 1;
            ITERATE myWhile;
        END IF;
        SET hap = hap + i;
        IF (hap > 1000) THEN
            LEAVE myWhile;
        END IF;
        SET i = i + 1;
    END WHILE;

    SELECT '1부터 100까지의 합(4의 배수 제외), 1000 넘으면 종료 ==>', hap;
END $$
DELIMITER ;
CALL whileProc2();

WHILE 문을 myWhile이라는 레이블로 지정한다.
i가 4의 배수라면 i를 증가시키고 ITERATE를 만나서 첫 단계로 올라간다. (즉, WHILE 문을 계속 진행한다.)
i가 4의 배수가 아니면 hap에 누적시킨다.
hap이 1,000을 초과하면 LEAVE를 만나서 myWhile 레이블을 빠져 나간다.

[Output] 1부터 100까지의 합(4의 배수 제외), 1000 넘으면 종료 ==> 1014

동적 SQL

SQL 문은 내용이 고정되어 있는 경우가 대부분이다.
하지만 상황에 따라 내용 변경이 필요할 때 동적 SQL을 사용하면 변경되는 내용을 실시간으로 적용시켜 사용할 수 있다.

PREPARE와 EXECUTE

PREPARE는 SQL 문을 실행하지는 않고 미리 준비만 해놓고, EXECUTE는 준비한 SQL 문을 실행한다.
그리고 실행 후에는 DEALLOCATE PREPARE로 문장을 해제해주는 것이 바람직하다.
간단한 예제를 살펴보겠다.

USE market_db;
PREPARE myQuery FROM 'SELECT * FROM member WHERE mem_id = "BLK"';
EXECUTE myQuery;
DEALLOCATE PREPARE myQuery;

PREPARE 문에서는 SELECT * FROM member WHERE mem_id = “BLK”를 바로 실행하지 않고 myQuery에 입력만 시켜놓는다.
실행이 필요한 시점에서 EXECUTE myQuery 문으로 실행한다.

이렇게 미리 SQL을 준비한 후에 나중에 실행하는 것을 동적 SQL이라고 부른다.

동적 SQL의 활용

PREPARE 문에서는 ?로 향후에 입력될 값을 비워 놓고, EXECUTE에서 USING으로 ?에 값을 전달할 수 있다.
그러면 실시간으로 필요한 값들을 전달해서 동적으로 SQL이 실행된다.
예제를 살펴보겠다.

보안이 중요한 출입문에서는 출입한 내역을 테이블로 기록해 놓는다.
이때 출입증을 태그하는 순간의 날짜와 시간이 INSERT 문으로 만들어져서 입력되도록 해야 한다.

DROP TABLE IF EXISTS gate_table;
CREATE TABLE gate_table (id INT AUTO_INCREMENT PRIMARY KEY, entry_time DATETIME);

SET @curDate = CURRENT_TIMESTAMP();

PREPARE myQuery FROM 'INSERT INTO gate_table VALUES(NULL, ?)';
EXECUTE myQuery USING @curDate;
DEALLOCATE PREPARE myQuery;

SELECT * FROM gate_table;

출입용 테이블을 간단히 만든다.
아이디는 자동으로 증가되도록 하고, 출입하는 시간은 DATETIME형으로 준비한다.
현재 날짜와 시간을 @curDate 변수에 넣는다.
?를 사용해서 entry_time에 입력할 값을 비워 놓는다.
USING 문으로 앞에서 준비한 @curDate 변수를 넣은 후에 실행된다.
결국 이 SQL을 시행한 시점의 날짜와 시간이 입력된다.

Twitter Facebook LinkedIn

Sieun Kim