Query를 JSON형태로 출력하는 GET_JSON_RESULT 프로시저

외부에서 Query를 호출하여 배열형태로 받아서 처리하는데,

보고서 라이브러리에서 JSON 형태 출력을 그대로 데이터 소스로 사용하는 게 있어서 

사용에 필요하지 않을까해 ChatGPT 도움으로 작성한 프로시저입니다.

QueryToJSON 처럼 이름을 짓는게 나을지 모르겠네요.

 

필드명 분석의 한계 때문에  select * form table_name 형태의 Query문은 사용할 수 없습니다.

GET_JSON_FIELDS함수를 좀 더 확장하면 가능할 듯 하지만 제가 사용하는 데는 문제가 없으므로 그냥 갑니다.

 

JSON출력은 "header"와 "body"로 나뉘어 header에 결과와 출력행수, body에 query결과를 담도록 했습니다.

DELIMITER $$

DROP PROCEDURE IF EXISTS GET_JSON_RESULT;

CREATE PROCEDURE GET_JSON_RESULT (
    IN in_query TEXT
)
BEGIN

    -- 2025-01-17, EGTOOLS
    -- 이 프로시저는 in_query로 실행할 Query문을 받아서 실행후 결과를 JSON으로 출력함
    -- Query문의 Select이 "Seclect * FROM table_name" 처럼 *를 사용시 사용할 수 없음

    -- 필요한 변수 초기화
    DECLARE status VARCHAR(10) DEFAULT 'success';
    DECLARE exit_handler_called INT DEFAULT 0;
    DECLARE field_names TEXT;
    DECLARE json_query TEXT;
    DECLARE json_header TEXT;

    -- 오류 핸들러 설정
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    BEGIN
        SET status = 'error';
        SET exit_handler_called = 1;
    END;

    -- 필드명 동적으로 추출
    SET field_names = GET_JSON_FIELDS(in_query);
    
    -- 동적Query는 프로시저에서 사용할 수 있으며, Local @변수에 대입 가능
    SET json_query = CONCAT(
        'SELECT JSON_ARRAYAGG(JSON_OBJECT(', field_names, ')) INTO @json_body ',
        'FROM (', in_query, ') AS temp_table'
    );

    -- JSON 변환 쿼리 실행
    PREPARE stmt_json FROM json_query;
    EXECUTE stmt_json;
    DEALLOCATE PREPARE stmt_json;

    -- 오류에 따라 status에 표시하고, 전체 행수와 목록을 body에 나열
    IF exit_handler_called = 0 THEN
        -- @json_body 전체 행수 가져오기
        SELECT JSON_LENGTH(@json_body) INTO @total_lines;
    
        -- JSON Header 작성
        SET json_header = JSON_OBJECT('status', status,'total_lines', @total_lines);
        -- header와 body를 가진 JSON 출력;
        SELECT CONCAT('{{\"header\": ', json_header, '}, {\"body\":', @json_body, '}}');
    ELSE
        -- 오류 발생 시 에러 메시지만 반환
        SET json_header = JSON_OBJECT('status', status,'total_lines', 0);
        SELECT CONCAT('{{\"header\": ', json_header, '}, {\"body\":[]}}');
    END IF;    
    
END$$

DELIMITER ;