mysql 프로시저 활용하기

데이터를 (),(),(); 형태로 만들었고, insert하기 위한 프로시저를 실행시켰다

 

프로시저를 활용하는 이유 : insert / update 는 실행 중 일때 데이터의 안정성을 위해 해당 작업이 끝날 때 까지 다른 작업을 시작하지 않는다. 그렇기 때문에 실제로 운영하는 서버에서는 insert,update를 수행하는 시간을 매우짧게 가져가야한다.

여기서는 배열 형태의 데이터를 임시테이블에 저장해뒀다가 insert 시키기 위해 프로시저를 활용했다.

CREATE DEFINER=`host` PROCEDURE `DataInsert`(
	IN `input_string` TEXT,
	IN `standardDate` INT
)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
    -- Create the temporary table
    CREATE TEMPORARY TABLE IF NOT EXISTS TEMPTABLE (
        ID INT,
        DATE INT,
        VALUE DECIMAL(12, 4),
        STANDARD_DATE INT
    );
    
    -- Load data from input_string into TEMPTABLE using INSERT INTO ... VALUES
    SET @insert_query = CONCAT(
        "INSERT INTO TEMPTABLE (ID, DATE, VALUE) VALUES ",
        input_string
    );
    PREPARE insert_stmt FROM @insert_query;
    EXECUTE insert_stmt;
    DEALLOCATE PREPARE insert_stmt;
    
    UPDATE TEMPTABLE SET STANDARD = standardDate;
    
    -- Process the data and insert into PRICE table
    INSERT INTO PRICE (ID, DATE, VALUE, STANDARD)
    SELECT t.ID, t.DATE, t.VALUE, t.STANDARD
    FROM TEMPTABLE t
    LEFT JOIN (
        SELECT p.ID, MAX(p.DATE) AS DATE
        FROM PRICE p
        WHERE p.DBSTATUS = 'A'
        GROUP BY p.ID
    ) p ON t.ID = p.ID AND t.DATE <= p.DATE
    WHERE p.ID IS NULL;
    
    UPDATE stock_error_log sel
    JOIN TEMPTABLE t ON sel.ID = t.ID
    SET sel.STATUS = 'D';
    
    -- Clean up: Drop the temporary table
    DROP TEMPORARY TABLE IF EXISTS TEMPTABLE;
    
END

프로시저를 생성하면, BEGIN과 END 사이에 수행하고자 하는 쿼리문을 작성하면 된다

CREATE DEFINER=`host` PROCEDURE `DataInsert`(
	IN `input_string` TEXT,
	IN `standardDate` INT
)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN

쿼리문

END

 

이 프로시저를 시작하기 전에, 쿼리문에 사용할 데이터를 string 형태로 만들어 뒀기 때문에

프로시저 내부에서 반복문을 사용하지 않고 JSON형태의 데이터로 임시 테이블을 작성할 수 있다.

 

프로시저 내부에서는 단순한 CURD 명령어만 수행하고 반복, 조건문 등의 명령을 자제하도록 하자.

 

CREATE TEMPORARY TABLE IF NOT EXISTS TEMPTABLE (
        ID INT,
        DATE INT,
        VALUE DECIMAL(12, 4),
        STANDARD_DATE INT
    );
    
-- Load data from input_string into TEMPTABLE using INSERT INTO ... VALUES
SET @insert_query = CONCAT(
    "INSERT INTO TEMPTABLE (ID, DATE, VALUE) VALUES ",
    input_string
);
PREPARE insert_stmt FROM @insert_query;
EXECUTE insert_stmt;
DEALLOCATE PREPARE insert_stmt;

임시 테이블을 CREATE 한 뒤,

SET 을 사용해서 사용자 정의 변수 내부에 실행할 쿼리문( @insert_query )을 저장한다.

PREPARE 로 @insert_query에 저장된 쿼리문을 실행할 수 있도록 컴파일하고 저장한다.

EXECUTE 를 통해 컴파일된 쿼리문을 실행한다.

DEALLOCATE PREPARE 을 이용해서 저장되어있는 컴파일 실행문을  삭제한다.

 

INSERT INTO PRICE (ID, DATE, VALUE, STANDARD)
    SELECT t.ID, t.DATE, t.VALUE, t.STANDARD
    FROM TEMPTABLE t
    LEFT JOIN (
        SELECT p.ID, MAX(p.DATE) AS DATE
        FROM PRICE p
        WHERE p.DBSTATUS = 'A'
        GROUP BY p.ID
    ) p ON t.ID = p.ID AND t.DATE <= p.DATE
    WHERE p.ID IS NULL;

LEFT JOIN 과 WHERE p.ID IS NULL 을 활용해서 기존의 테이블과 임시테이블을 비교해서 기존 테이블에 이미 들어가있는 데이터는 제외하고 새로운 데이터만 저장되도록 한다.

 

DROP TEMPORARY TABLE IF EXISTS TEMPTABLE;

마지막으로 메모리에 올라가있는 임시 테이블을 삭제한다. IF문을 사용하지 않아도 무방하다.