SQL Macro - Scalar Expression

Oracle 21c에 SQL Macro(SQM) 기능이 추가되었습니다. SQM을 사용하면 SQL 표현식(스칼라 표현식)과 SQL 문(테이블 표현식)을 재사용 가능한 매개변수화된 구조로 변환할 수 있습니다. 이번 글에서 스칼라 표현식을 사용하는 스칼라 SQM에 대해 간단히 살펴보겠습니다.

You can create SQL macros (SQM) to factor out common SQL expressions and statements into reusable, parameterized constructs that can be used in other SQL statements.
SQL macros can either be scalar expressions, typically used in SELECT lists, WHERE, GROUP BY, and HAVING clauses, to encapsulate calculations and business logic, or can be table expressions, typically used in a FROM clause, to act as a sort of polymorphic (parameterized) views.
SQL macros increase developer productivity, simplify collaborative development, and improve code quality.

 

아래는 SQL_MACRO 절의 구문입니다. 괄호 안에 사용할 표현식을 지정할 수 있으며, 기본값은 TABLE입니다.

SQL_MACRO {({TYPE =>} ( SCALAR | TABLE ))}

 

테스트 버전은 아래와 같습니다.

-- 1
SELECT version_full FROM product_component_version;

VERSION_FULL
------------
21.3.0.0.0

1 row selected.

 

테스트를 위해 아래와 같이 테이블을 생성하겠습니다.

-- 1
DROP TABLE t1 PURGE;
DROP TABLE t2 PURGE;

CREATE TABLE t1 (c1, c2) AS SELECT 1, 1 FROM DUAL;
CREATE TABLE t2 (c1, c2) AS SELECT 1, 2 FROM DUAL;

 

아래와 같이 함수의 RETURN 절 끝에 SQL_MACRO (SCALAR)를 기술하면 스칼라 SQM을 생성할 수 있습니다. 쿼리에 f1 함수를 사용하면 스칼라 표현식으로 계산된 값이 반환됩니다.

-- 2-1
CREATE OR REPLACE FUNCTION f1 (
    i_c1 IN NUMBER
)
    RETURN VARCHAR2 SQL_MACRO (SCALAR)
IS
BEGIN
    RETURN 'i_c1 + 1';
END;
/

-- 2-2
SELECT f1 (c1) AS c1 FROM t1;

C1
--
 2

1 row selected.

 

10053 트레이스를 살펴보면 변환된 쿼리를 확인할 수 있습니다.

-- 3-1
SELECT sql_id, sql_text FROM v$sql WHERE sql_text = 'SELECT f1 (c1) AS c1 FROM t1';

SQL_ID        SQL_TEXT
------------- ----------------------------
9jtfr03h4vpf5 SELECT f1 (c1) AS c1 FROM t1

1 row selected.

-- 3-2
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "T1"."C1"+1 "C1" FROM "TUNA"."T1" "T1"

 

아래 예제는 i_op 값이 +면 i_c1 + 1, -면 i_c1 - 1을 반환할 것처럼 보이지만 i_op가 널로 평가되어 에러가 발생합니다. SQM 내에서 반환할 문자열에 사용하지 않은 입력 매개변수는 null로 평가되는 것으로 보입니다. 이것은 파싱을 위한 동작으로 예상되지만 입력 값이 리터럴인 경우에는 값이 정상적으로 평가되도록 기능이 개선되어야 할 것 같습니다.

-- 4-1
CREATE OR REPLACE FUNCTION f1 (
    i_c1 IN NUMBER
  , i_op IN VARCHAR2
)
    RETURN VARCHAR2 SQL_MACRO (SCALAR)
IS
BEGIN
    DBMS_OUTPUT.PUT_LINE ('i_op:' || i_op);
    RETURN CASE i_op WHEN '+' THEN 'i_c1 + 1' WHEN '-' THEN 'i_c1 - 1' END;
END;
/

-- 4-2
SET SERVEROUT ON

SELECT f1 (c1, '-') AS c1 FROM t1;

ORA-62565: The SQL Macro method failed with error(s).
ORA-01405: The fetched column returned a NULL value.

i_op:

 

아래와 같이 i_op 매개변수을 반환할 문자열에 포함시키면 에러가 발생하지 않습니다.

-- 5-1
CREATE OR REPLACE FUNCTION f1 (
    i_c1 IN NUMBER
  , i_op IN VARCHAR2
)
    RETURN VARCHAR2 SQL_MACRO (SCALAR)
IS
BEGIN
    RETURN q'`CASE i_op WHEN '+' THEN i_c1 + 1 WHEN '-' THEN i_c1 - 1 END`';
END;
/

-- 5-2
SET SERVEROUT ON

SELECT f1 (c1, '-') AS c1 FROM t1;

C1
--
 0

1 row selected.

 

SQM은 표현식을 치환하므로 반환할 문자열에 스칼라 서브쿼리를 사용할 수 있습니다.

-- 6-1
CREATE OR REPLACE FUNCTION f1 (
    i_c1 IN NUMBER
)
    RETURN VARCHAR2 SQL_MACRO (SCALAR)
IS
BEGIN
    RETURN 'SELECT c2 FROM t2 WHERE c1 = i_c1';
END;
/

-- 6-2
SELECT f1 (c1) AS c2 FROM t1;

C2
--
 2

1 row selected.

 

아쉽게도 스칼라 SQM은 DBMS_TF 패키지의 DBMS_TF.TABLE_T 타입을 지원하지 않습니다.

-- 7-1
CREATE OR REPLACE FUNCTION f1 (
    i_tab IN DBMS_TF.TABLE_T
  , i_c1  IN NUMBER
)
    RETURN VARCHAR2 SQL_MACRO (SCALAR)
IS
BEGIN
    RETURN 'SELECT c2 FROM i_tab WHERE c1 = i_c1';
END;
/

Warning: Function created with compilation errors.

-- 7-2
SHOW ERR

PLS-00777: scalar SQL macro cannot have argument of type DBMS_TF.TABLE_T

 

아래는 스칼라 SQM에 DBMS_TF.COLUMNS_T 타입을 사용한 예제입니다. DBMS_TF.COLUMNS_T 타입으로 입력된 값은 반환할 문자열에 사용하지 않아도 값이 정상적으로 평가되는 것으로 보입니다.

-- 8-1
CREATE OR REPLACE FUNCTION f1 (
    i_col IN DBMS_TF.COLUMNS_T
)
    RETURN VARCHAR2 SQL_MACRO (SCALAR)
IS
BEGIN
    DBMS_OUTPUT.PUT_LINE ('i_col(1) :' || i_col(1));
    RETURN i_col(1) || '-' || i_col(2);
END;
/

-- 8-2
SELECT f1 (COLUMNS (c1, c2)) AS c2 FROM t2;

C2
--
-1

1 row selected.

i_col(1) :"C1"

 

앞선 예제를 응용하여 아래와 같은 트릭을 사용하면 4번 예제를 에러 없이 수행할 수 있지만 원래의 목적에 부합하지 않으므로 권장하지는 않습니다.

-- 9-1
CREATE OR REPLACE FUNCTION f1 (
    i_c1 IN NUMBER
  , i_op IN DBMS_TF.COLUMNS_T
)
    RETURN VARCHAR2 SQL_MACRO (SCALAR)
IS
BEGIN
    RETURN CASE i_op(1) WHEN '"PLUS"' THEN 'i_c1 + 1' WHEN '"MINUS"' THEN 'i_c1 - 1' END;
END;
/

-- 9-2
SELECT f1 (c1, COLUMNS (plus)) AS c1 FROM t1;

C1
--
 2

1 row selected.

 

스칼라 SQM을 사용하면 SQL과 PL/SQL 간의 Context Switching로 인한 성능 저하를 개선할 수 있습니다. 일반 함수를 사용한 아래 예제의 쿼리는 16.75초가 소요었습니다.

-- 10-1
CREATE OR REPLACE FUNCTION f1 (
    i_c1 IN NUMBER
)
    RETURN NUMBER
IS
BEGIN
    RETURN i_c1 + 1;
END;
/

-- 10-2
SELECT SUM (f1 (ROWNUM)) AS c1 FROM XMLTABLE ('1 to 10000000');

Elapsed: 00:00:16.75

 

스칼라 SQM을 사용한 아래 예제의 쿼리는 4.21초가 소요됩니다. 참고로 Oracle 23ai에 단순 표현식을 반환하는 PL/SQL 함수를 SQL 표현식으로 변환하는 Automatic SQL Transpiler 기능이 추가되었습니다.

-- 11-1
CREATE OR REPLACE FUNCTION f1 (
    i_c1 IN NUMBER
)
    RETURN VARCHAR2 SQL_MACRO (SCALAR)
IS
BEGIN
    RETURN 'i_c1 + 1';
END;
/

-- 11-2
SELECT SUM (f1 (ROWNUM)) AS c1 FROM XMLTABLE ('1 to 10000000');

Elapsed: 00:00:04.21