울면서개발하기
DB / MYSQL

MySQL 에서 Oracle 의 테이블 함수 흉내 내기 — JSON_TABLE 과 대안들

· 2026년 10월 25일 다시 씀

Oracle 에서는 컬렉션을 돌려주는 함수(파이프라인 함수 포함)를 만들어 SELECT * FROM TABLE(FN_xxx(...)) 처럼 테이블로 쓸 수 있다. Oracle 에서 MySQL 로 옮기다 보면 이런 함수를 어떻게 바꿀지가 문제가 된다.

MySQL(8.4 LTS 기준)에는 테이블을 돌려주는 사용자 함수가 없다. 저장 함수는 스칼라 값 하나만 RETURN 할 수 있고, 함수 안에서 결과 집합을 내보내는 SELECT 도 쓸 수 없다. 저장 프로시저는 결과 집합을 돌려줄 수 있지만 FROM 절에 넣어 조인할 수는 없다.

그래서 쓴 방법이 함수는 JSON 배열을 돌려주고, 쓰는 쪽에서 JSON_TABLE 로 행으로 펼치는 것이다. 다만 뒤에서 설명하듯, 이 방법이 꼭 필요한 경우는 생각보다 적다.

1. JSON 을 돌려주는 함수

사용자별 권한 목록을 돌려주는 함수다.

DELIMITER //

CREATE FUNCTION FN_USER_ROLES (P_USER_ID VARCHAR(50))
RETURNS JSON
NOT DETERMINISTIC
READS SQL DATA
BEGIN
    DECLARE V_RESULT JSON;

    SELECT COALESCE(
               JSON_ARRAYAGG(JSON_OBJECT('ROLE_CD', A.ROLE_CD, 'ADMIN_YN', A.ADMIN_YN)),
               JSON_ARRAY()
           )
      INTO V_RESULT
      FROM USER_ROLE A
     WHERE A.USER_ID = P_USER_ID;

    RETURN V_RESULT;
END //

DELIMITER ;
SELECT FN_USER_ROLES('user01');
-- [{"ROLE_CD": "000006", "ADMIN_YN": "0"}]
  • JSON_ARRAYAGG 는 대상 행이 없으면 NULL 을 돌려준다. COALESCE(..., JSON_ARRAY()) 로 빈 배열 [] 을 돌려주게 하면 쓰는 쪽에서 NULL 을 따로 신경 쓰지 않아도 된다
  • 반환형은 JSON 으로 했다. 문자열(TEXT, LONGTEXT)로 돌려줘도 JSON_TABLE 은 읽어 주지만, TEXT 는 최대 64KB 라 결과가 길면 잘리거나 오류가 난다
  • DELIMITER 는 mysql 명령줄 클라이언트용이다. GUI 도구는 도구마다 함수 본문을 실행하는 방법이 다르다

DETERMINISTIC 을 붙이면 안 된다

원래 글에서는 함수에 DETERMINISTIC 을 붙였다. 아마 함수를 만들 때 이 오류를 만났기 때문일 것이다.

ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA
in its declaration and binary logging is enabled ...

MySQL 8.x 는 바이너리 로그가 기본으로 켜져 있어서, 함수에 DETERMINISTIC, NO SQL, READS SQL DATA 중 하나를 밝혀야 만들 수 있다. 하지만 이 함수는 테이블을 읽으므로 데이터가 바뀌면 같은 입력에도 결과가 달라진다. 즉 결정적이지 않다. MySQL 은 이 선언을 검사하지 않고 만든 사람의 말을 그대로 믿는데, 비결정적인 함수를 DETERMINISTIC 으로 선언하면 옵티마이저가 잘못된 실행 계획을 고를 수 있다고 공식 문서에 나와 있다. 테이블을 읽기만 하는 함수는 READS SQL DATA 가 맞는 선언이고, 이것만으로 1418 오류도 피할 수 있다.

(만드는 계정에 따라 SUPER 또는 SET_ANY_DEFINER 권한이 없다는 1419 오류가 날 수도 있는데, 이것은 DBA 가 권한이나 log_bin_trust_function_creators 설정으로 풀 문제다.)

2. JSON_TABLE 로 행으로 펼치기

JSON_TABLE(MySQL 8.0.4 부터)은 JSON 을 행과 열로 바꿔 주는 테이블 함수다. FROM 절에 일반 테이블처럼 쓴다.

SELECT R.ROLE_CD
     , R.ADMIN_YN
  FROM JSON_TABLE(
         FN_USER_ROLES('user01'),
         '$[*]' COLUMNS (
             ROLE_CD  VARCHAR(20) PATH '$.ROLE_CD',
             ADMIN_YN CHAR(1)     PATH '$.ADMIN_YN'
         )
       ) AS R;
ROLE_CD ADMIN_YN
000006 0

일반 테이블과 조인할 수도 있다.

SELECT R.ROLE_CD
     , M.ROLE_NM
  FROM JSON_TABLE(
         FN_USER_ROLES('user01'),
         '$[*]' COLUMNS (ROLE_CD VARCHAR(20) PATH '$.ROLE_CD')
       ) AS R
  JOIN ROLE_MASTER M
    ON M.ROLE_CD = R.ROLE_CD;

JSON_TABLE 은 앞에 나온 테이블의 열을 인자로 받을 수 있다(암묵적인 LATERAL). 그래서 사용자마다 함수를 부르는 것도 된다.

SELECT U.USER_ID
     , R.ROLE_CD
  FROM USERS U
 CROSS JOIN JSON_TABLE(
         FN_USER_ROLES(U.USER_ID),
         '$[*]' COLUMNS (ROLE_CD VARCHAR(20) PATH '$.ROLE_CD')
       ) AS R;

알아 둘 점:

  • 위 쿼리는 사용자 수만큼 함수가 실행되고, 함수 안의 쿼리도 그만큼 돈다. 함수 결과는 인덱스를 탈 수 없으니 행이 많으면 느리다
  • JSON_ARRAYAGG 는 MySQL 8.4 에서 ORDER BY 를 받지 않아 배열 안의 순서가 보장되지 않는다. 순서가 필요하면 펼친 뒤 바깥 쿼리에서 ORDER BY 한다
  • 정렬된 순번이 필요하면 COLUMNS 에 SEQ FOR ORDINALITY 를 넣으면 배열 순서대로 1, 2, 3 … 이 붙는다

3. 사실은 함수가 필요 없는 경우가 많다

Oracle 에서 테이블 함수를 쓰는 이유는 대개 다음 중 하나다. MySQL 8 에는 각각 더 단순한 방법이 있다.

매개변수 있는 뷰가 필요했다 → 파생 테이블·CTE·뷰

위 예제처럼 “조건을 받아 몇 행을 돌려주는” 함수라면, 그냥 그 SELECT 를 서브쿼리나 CTE 로 쓰면 된다. 여러 곳에서 쓰면 조건 없이 뷰를 만들고 바깥에서 WHERE 를 건다. MySQL 은 단순한 뷰를 바깥 쿼리에 합쳐서(merge) 실행하므로 인덱스도 그대로 탄다.

CREATE VIEW V_USER_ROLE AS
SELECT A.USER_ID, A.ROLE_CD, A.ADMIN_YN
  FROM USER_ROLE A;

SELECT ROLE_CD, ADMIN_YN
  FROM V_USER_ROLE
 WHERE USER_ID = 'user01';

행마다 결과 몇 건을 붙이고 싶었다 → LATERAL 파생 테이블

MySQL 8.0.14 부터 LATERAL 파생 테이블로 바깥 행의 값을 받는 서브쿼리를 FROM 에 쓸 수 있다. 위의 “사용자마다 함수 호출” 은 이렇게 바꾸는 편이 낫다.

SELECT U.USER_ID
     , R.ROLE_CD
  FROM USERS U
  JOIN LATERAL (
        SELECT A.ROLE_CD
          FROM USER_ROLE A
         WHERE A.USER_ID = U.USER_ID
         ORDER BY A.ROLE_CD
         LIMIT 3
       ) AS R ON TRUE;

문자열을 쪼개 행으로 만들고 싶었다 → JSON_TABLE

'A01,B02,C03' 같은 쉼표 목록을 행으로 바꾸는 함수도 Oracle 에서 흔히 만든다. MySQL 에서는 JSON 배열 문자열로 바꿔 JSON_TABLE 에 넘기면 된다.

SELECT T.SEQ, T.CODE
  FROM JSON_TABLE(
         CONCAT('["', REPLACE('A01,B02,C03', ',', '","'), '"]'),
         '$[*]' COLUMNS (
             SEQ  FOR ORDINALITY,
             CODE VARCHAR(20) PATH '$'
         )
       ) AS T;

값에 큰따옴표(")나 역슬래시(\)가 들어 있으면 JSON 이 깨지니 그런 값이 없을 때만 쓴다. 애플리케이션에서 넘어오는 목록이라면 MyBatis <foreach> 로 IN (...) 을 만드는 쪽이 낫다.

번호·날짜 목록을 만들고 싶었다 → 재귀 CTE

Oracle 의 CONNECT BY LEVEL 이나 행 생성 함수 대신 재귀 CTE(MySQL 8.0.1 부터)를 쓴다.

WITH RECURSIVE D (DT) AS (
    SELECT DATE '2026-10-01'
    UNION ALL
    SELECT DT + INTERVAL 1 DAY
      FROM D
     WHERE DT < DATE '2026-10-31'
)
SELECT DT FROM D;

재귀 횟수는 cte_max_recursion_depth(기본 1000)를 넘을 수 없다. 더 길게 만들려면 세션에서 값을 늘린다.

절차적인 로직(반복·분기)이 필요했다 → 프로시저 + 임시 테이블

반복문으로 결과를 쌓아야 하는 경우라면 프로시저가 TEMPORARY TABLE 에 결과를 채우고, 호출한 쪽이 그 임시 테이블을 조인한다. 임시 테이블은 세션마다 따로라서 동시에 실행해도 섞이지 않는다.

CALL SP_BUILD_USER_ROLES('user01');   -- 안에서 TMP_USER_ROLES 를 만들고 채운다

SELECT R.ROLE_CD, M.ROLE_NM
  FROM TMP_USER_ROLES R
  JOIN ROLE_MASTER M ON M.ROLE_CD = R.ROLE_CD;

정리

하고 싶은 것 MySQL 8.4
조건 받아 행 돌려주기 서브쿼리·CTE·뷰 + WHERE
행마다 연관 결과 붙이기 JOIN LATERAL (...) ON TRUE
함수 안 로직을 꼭 재사용 JSON 을 돌려주는 함수 + JSON_TABLE
문자열 목록을 행으로 JSON_TABLE
번호·날짜 목록 WITH RECURSIVE
반복·분기가 필요한 로직 프로시저 + 임시 테이블

JSON 을 돌려주는 함수 방식은 Oracle 코드를 구조 그대로 옮길 때 편하다. 다만 결과가 인덱스를 못 타고 행마다 함수가 실행되므로, 데이터가 많은 화면에서는 위의 다른 방법으로 바꾸는 것을 먼저 검토한다.

다시 쓰며 바로잡은 점

  • 테이블을 읽는 함수에 붙였던 DETERMINISTIC 을 NOT DETERMINISTIC + READS SQL DATA 로 고쳤다. 바이너리 로그 오류(1418)를 피하는 올바른 선언이 READS SQL DATA 다
  • 반환형을 TEXT 에서 JSON 으로 바꾸고, 결과가 없을 때 NULL 대신 빈 배열을 돌려주게 했다
  • JSON 함수 방식만 있던 원래 글에 뷰·LATERAL·재귀 CTE·임시 테이블 같은 대안과 각각 언제 쓰는지를 덧붙였다
  • 업무 테이블·사용자 ID 는 일반적인 예시로 바꿨다