[Codesquad] 수업 정리 - 스토어드 프로시저 stored procedure

수업 정리 - 스토어드 프로시저 stored procedure

모르면 이상하지만 굳이 공부할 필요는 없는..?.. 하지만 중요한..!!..

sql 명령은 기본적으로 선언적(declarative) (what) 명령이다.

DB에서 '절차적 명령(imperative) (how)'을 수행하기 위해서는 스토어드 프로그램을 사용해야 한다.

스토어드 프로그램

• DB에 저장된 프로그램
• 한 마디로 DB에 프로그램을 저장해 사용하는 것

스토어드 프로시저는 DB에 저장되기 때문에 MySQL 종료 후 다시 실행해도 남아 있다.

스토어드 프로그램의 종류

• 스토어드 함수
• 스토어드 프로시져
• 트리거
• 이벤트 핸들러

스토어드 프로그램의 장점

• 응용프로그램의 성능 향상
• 네트워크 트래픽 감소
  • 여러 번 쿼리 날려야 하는 걸 한 번으로 줄일 수 있음
  • DB에서 최종 결과물까지 만들어 그것만 보내주기 때문
• 보안성 향상
• 개발 업무의 구분 - 스토어드 프로그램은 DBA가 짠다

스토어드 프로그램의 단점

• 유지보수가 매우 어렵다
  • 코드가 DB에 들어있기 때문
  • 문제가 생겼을 때 rollback을 하기 어렵다
• git에서 관리가 쉽지않다
  • DBA가 깃을 사용하지 않기 떄문
• 명령 자체의 성능 감소 (자바보다 느림)

스토어드 프로그램 사용하는 곳

• 게임 분야에서 많이 사용.
• 웹분야는 케바케지만 사용율 감소 중

stored function vs stored procedure

함수는 쿼리 내에 사용할 수 있음. 대신 제약사항이 많음.

프로시져는 쿼리 내에서 사용할 수 없음

우리는 프로시저만 다룬다.

Hello World 찍기

DROP PROCEDURE IF EXISTS SP_HELLO;
DELIMITER $$
CREATE PROCEDURE SP_HELLO()
    BEGIN
        DECLARE STR CHAR(20) DEFAULT 'POPI';
        SET STR = 'HELLO, WORLD';
        SELECT STR;
        END $$
DELIMITER ;

CALL SP_HELLO();

• SP_... : 관행. 스토어드 프로시저 이름 앞에 붙임

• DELIMITER $$ : 구분자. 구문 끝내는 문장을 사용자가 지정할 수 있음. (;대신)

  • END $$까지 $$를 델리미터로 사용함

• DECLARE STR : STR이라는 이름의 변수를 선언함.

  • DB에서는 문자열을 쌍따옴표가 아닌 홑따옴표'로 감싸야 한다!

기본 변수 사용하기

기본 변수 선언 : DECLARE 변수명 타입 [DEFAULT 기본값]

([]내의 값은 옵션이다)

• 기본 변수는 프로시져 내에서만 유효함!

세션 변수 선언 및 사용

SET @변수명 = 값;

• 세션 변수는 말 그대로 세션 내에서 계속 유효함.

• SET @VAR_TEST = 10;

• SELECT @VAR_TEST;

기본 변수에 값 넣기

SET 사용

SET A = 10;

DROP PROCEDURE IF EXISTS SP_TEST1;
DELIMITER $$
CREATE PROCEDURE SP_TEST1()
    BEGIN
        DECLARE A INT;
        SET A = 10;
        SET A = A * 2;
        SELECT A;
    END $$
DELIMITER ;

CALL SP_TEST1();

SELECT INTO 사용

쿼리의 결과를 변수에 넣을 수 있음.

단, 쿼리의 결과값이 스칼라 값이 경우만 가능

DB에서 스칼라 = 단일값을 뜻함. (테이블 열 하나가 아닌, '값' 하나)

DROP PROCEDURE IF EXISTS SP_TEST2;
DELIMITER $$
CREATE PROCEDURE SP_TEST2()
    BEGIN
        DECLARE A ING;
        SELECT COUNT(UID) INTO A FROM USER_INFO;
        SELECT A;
    END $$
DELIMITER ;

CALL SP_TEST2();

결과값을 테이블에 넣기

CREATE TABLE TEST3(NUM INT);
INSERT INTO TEST3 VALUES (1);
DROP PROCEDURE IF EXISTS SP_TEST3;
DELIMITER $$
CREATE PROCEDURE SP_TEST3()
    BEGIN
        DECLARE A INT DEFAULT 1;
        SELECT MAX(NUM) INTO A FROM TEST3;
        SET A = A + 1;
        INSERT INTO TEST3 VALUES(A);
        SELECT * FROM TEST3;
    END $$
DELIMITER ;

CALL SP_TEST3();
CALL SP_TEST3();
CALL SP_TEST3();
SELECT * FROM TEST3;

TEST3 테이블에는 1,2,3,4,5가 들어가게 된다.

매개변수 사용하기 (IN, OUT)

• IN 매개변수명 .

  • 입력에 사용

  • DROP TABLE IF EXISTS TEST4;
    CREATE TABLE TEST4 (
        ID INT PRIMARY KEY AUTO_INCREMENT,
        NAME VARCHAR(20)
    );
    INSERT INTO TEST4 VALUES (NULL, 'AA');
    INSERT INTO TEST4 VALUES (NULL, 'BB');
    INSERT INTO TEST4 VALUES (NULL, 'CC');
    INSERT INTO TEST4 VALUES (NULL, 'DD');
    
    DROP PROCEDURE IF EXISTS SP_TEST4;
    DELIMITER $$
    CREATE PROCEDURE SP_TEST4(IN pname varchar(10) )
        BEGIN
            DECLARE A INT DEFAULT 1;
            SELECT * FROM TEST4 WHERE NAME = pname;
        END $$
    DELIMITER ;
    
    CALL SP_TEST4('DD');

  • 결과로 ID가 4, NAME이 DD인 데이터가 조회된다.

• OUT 매개변수명

  • 출력에 사용

  • (자바의 return과 비슷한 용도)

  • DROP PROCEDURE IF EXISTS SP_TEST5;
    DELIMITER $$
    CREATE PROCEDURE SP_TEST5(IN pname varchar(10),
        OUT pid int)
        BEGIN
            DECLARE A INT DEFAULT 1;
            SELECT id into pid
                FROM TEST4
                WHERE NAME = pname;
        END $$
    DELIMITER ;
    
    CALL SP_TEST5('DD', @var_ret);
    SELECT @VAR_RET;

• INOUT 매개변수명

  • 입력, 출력에 동시에 사용할 변수

IF 사용하기

END IF;로 끝난다는 점을 주의해야.

IF if_expression THEN commands
    [ELSEIF elseif_expression THEN commands]
    [ELSE commands]
    END IF;

DROP TABLE IF EXISTS TEST6;
CREATE TABLE TEST6(NUM INT);

DROP PROCEDURE IF EXISTS SP_TEST6;
DELIMITER $$
CREATE PROCEDURE SP_TEST6(OUT RET INT)
    BEGIN
        DECLARE A INT DEFAULT 1;
        SELECT MAX(NUM) INTO A FROM TEST6;
        IF A IS NULL THEN
            SET A = 1;
        ELSE
            SET A = A + 1;
        END IF;
        INSERT INTO TEST6 VALUES (A);
        SET RET = A;
    END $$
DELIMITER ;

CALL SP_TEST6(@i);
SELECT @i;

CALL SP_TEST6(@i);
SELECT @i;

CALL SP_TEST6(@i);
SELECT @i;

@i의 최종 결과값 : 3

DB에서의 NULL은 '값을 잘 모르겠다'는 의미에 가깝다.

NULL + 1 = NULL

WHILE 사용하기

역시 END WHILE;로 끝난다는 것을 조심해야.

WHILE expression DO
    Statements
END WHILE

기타

이 외에 CASE (switch case와 같은 용도), REPEAT_UNTIL(do while과 같은 용도)도 있다.

실습1 - DB로 별 찍기

DROP TABLE IF EXISTS STAR;
CREATE TABLE STAR (LNO INT, LINE VARCHAR(64));

DROP PROCEDURE IF EXISTS SP_STAR;
DELIMITER $$
CREATE PROCEDURE SP_STAR(IN LNO INT)
    BEGIN
        DECLARE S VARCHAR(64) DEFAULT '*';
        DECLARE I INT DEFAULT 1;

        DELETE FROM STAR WHERE LNO >= 1;

        WHILE I <= LNO DO // WHILE문
            INSERT INTO STAR VALUES(I, S);
            SET S = CONCAT(S, '*');
            SET I = I + 1;
        END WHILE;
        SELECT * FROM STAR;
    END $$
DELIMITER ;

• CONCAT(a, b); : 내장함수. 두 문자열을 합치는 함수

• ❗DECLARE가 있다면 반드시 그 구문의 맨 위에 있어야 한다.