27.3.12 JavaScript Stored Program Examples

27.3.12 JavaScript Stored Program Examples

이 섹션에는 다양한 상황에서 JavaScript 프로그램을 사용하는 여러 측면을 보여 주는 예제가 포함되어 있습니다.

다음 예제는 테이블 컬럼 값과 함께 JavaScript 저장 함수(stored function)를 사용하는 방법을 보여 줍니다. 먼저 두 정수의 최대공약수를 찾는 저장 함수 gcd()를 다음과 같이 정의합니다:

1mysql> CREATE FUNCTION gcd(a INT, b INT)
2    -> RETURNS INT NO SQL LANGUAGE JAVASCRIPT AS
3    -> $mle$
4    $>   let x = Math.abs(a)
5    $>   let y = Math.abs(b)
6    $>   while(y) {
7    $>     var t = y
8    $>     y = x % y
9    $>     x = t
10    $>   }
11    $>   return x
12    $> $mle$
13    -> ;
14Query OK, 0 rows affected (0.01 sec)

이 저장 함수는 다음과 같이 테스트할 수 있습니다:

1mysql> SELECT gcd(75, 220), gcd(75, 225);
2+--------------+--------------+
3| gcd(75, 220) | gcd(75, 225) |
4+--------------+--------------+
5|            5 |           75 |
6+--------------+--------------+
71 row in set (0.00 sec)

다음으로 두 개의 정수 컬럼을 가진 테이블 t1을 생성하고, 다음과 같이 몇 개의 로우로 채웁니다:

1mysql> CREATE TABLE t1 (c1 INT, c2 INT);
2Query OK, 0 rows affected (0.02 sec)
3
4mysql> INSERT INTO t1 VALUES ROW(12,70), ROW(17,3), ROW(81,9);
5Query OK, 3 rows affected (0.01 sec)
6Records: 3  Duplicates: 0  Warnings: 0
7
8mysql> TABLE t1;
9+------+------+
10| c1   | c2   |
11+------+------+
12|   12 |   70 |
13|   17 |    3 |
14|   81 |    9 |
15+------+------+
163 rows in set (0.00 sec)

이제 t1에서 셀렉트를 수행하면서, 컬럼 값을 함수 호출의 인자 값으로 사용하여 gcd() 함수를 다음과 같이 사용할 수 있습니다:

1mysql> SELECT c1, c2, gcd(c1, c2) AS G
2    -> FROM t1
3    -> WHERE gcd(c1, c2) > 1
4    -> ORDER BY gcd(c1, c2);
5+----+----+---+
6| c1 | c2 | G |
7+----+----+---+
8| 12 | 70 | 2 |
9| 81 |  9 | 9 |
10+----+----+---+
118 rows in set (0.01 sec)

지정된 타입이 아닌 인자 값은 가능한 경우 올바른 타입으로 강제 변환(coerce)됩니다. 예시는 다음과 같습니다:

1mysql> SELECT gcd(500.3, 600), gcd(500.5, 600);
2+-----------------+-----------------+
3| gcd(500.3, 600) | gcd(500.5, 600) |
4+-----------------+-----------------+
5|             100 |               3 |
6+-----------------+-----------------+
71 row in set (0.01 sec)

부동 소수점(floating point) 값을 정수로 반올림하는 것은 Math.round()를 사용하여 수행됩니다. 이 경우 500.3은 500으로 내림(round down)되고, 500.5는 501로 올림(round up)됩니다.

다음으로, CREATE PROCEDURE 구문을 사용하여 간단한 JavaScript 저장 프로시저를 생성합니다. 이 프로시저에는 현재 날짜와 시간을 사람이 읽을 수 있는 형식으로 사용자 변수에 전달하기 위한 OUT 파라미터가 포함됩니다. 이 표현이 얼마나 길지 확실하지 않으므로, 파라미터의 타입으로 VARCHAR(25)를 사용합니다:

1mysql> CREATE PROCEDURE d1 (OUT res VARCHAR(25))
2    -> LANGUAGE JAVASCRIPT
3    -> AS
4    -> $$
5    $>   let d = new Date().toString()
6    $>   res = d
7    $> $$
8    -> ;
9Query OK, 0 rows affected (0.01 sec)

이제 저장 프로시저를 테스트할 수 있습니다. 먼저 사용자 변수 @today가 아직 어떤 값으로도 설정되지 않았음을 다음과 같이 확인합니다:

1mysql> SELECT @today;
2+----------------------+
3| @today               |
4+----------------------+
5| NULL                 |
6+----------------------+
71 row in set (0.01 sec)
8
9mysql> CALL d1(@today);
10ERROR 1406 (22001): Data too long for column 'res' at row 1

프로시저는 문법적으로는 유효하지만, INOUT 파라미터(res)의 데이터 타입이 충분한 수의 문자를 허용하지 않습니다. 값이 잘리는 대신 저장 프로그램은 이를 거부합니다. 프로시저 코드를 제자리에서 변경(alter)하는 것은 불가능하므로, 프로시저를 드롭한 후 다시 생성해야 합니다. 이번에는 INOUT 파라미터에 대해 지정된 길이를 두 배로 늘려 시도합니다:

1mysql> DROP PROCEDURE d1;
2Query OK, 0 rows affected (0.02 sec)
3
4mysql> CREATE PROCEDURE d1 (OUT res VARCHAR(50))
5    -> LANGUAGE JAVASCRIPT
6    -> AS
7    -> $$
8    $>   let d = new Date().toString()
9    $>   res = d
10    $> $$
11    -> ;
12Query OK, 0 rows affected (0.01 sec)

이제 다음과 같이 테스트를 반복할 수 있습니다:

1mysql> SELECT @today;
2+----------------------+
3| @today               |
4+----------------------+
5| NULL                 |
6+----------------------+
71 row in set (0.01 sec)

업데이트된 프로시저를 CALL로 호출하기 전까지 @today의 값은 설정되지 않은 상태로 남아 있습니다. d1()의 원래 버전은 성공적으로 실행되지 않았기 때문입니다. 업데이트된 버전은 성공적으로 실행되며, 이번에는 사용자 변수의 값이 예상대로 설정되었음을 이후에 확인할 수 있습니다:

1mysql> CALL d1(@today);
2Query OK, 0 rows affected (0.00 sec)
3
4mysql> SELECT @today;
5+-----------------------------------------+
6| @today                                  |
7+-----------------------------------------+
8| Mon Oct 30 2023 20:47:29 GMT+0000 (GMT) |
9+-----------------------------------------+
101 row in set (0.00 sec)

참고

이 예제를 실행하여 얻는 값은 여기 표시된 것과 어느 정도 다를 수 있습니다. 날짜의 정확한 표현은 시스템 로케일 및 기타 설정에 따라 달라지기 때문입니다. 자세한 내용은 JavaScript Date 객체에 대한 문서를 참조하십시오.

다음 예제는 트리거에서 JavaScript 저장 함수를 사용하는 방법을 보여 줍니다.

먼저 다음과 같이 세 개의 정수 컬럼을 포함하는 테이블 t2를 생성합니다:

1mysql> CREATE TABLE t2 (c1 INT, c2 INT, c3 INT);
2Query OK, 0 rows affected (0.04 sec)

이제 이 테이블에 대해 트리거를 생성할 수 있습니다. 이것은 SQL을 사용하여 일반적인 방식으로 작성된 CREATE TRIGGER 구문으로 수행해야 합니다( Section 27.4, “Using Triggers” 참조). 그러나 이 트리거는 이 섹션의 앞부분에 나와 있는 js_pow() 함수와 같이 JavaScript로 작성된 저장 루틴을 사용할 수 있습니다:

1mysql> delimiter //
2mysql> CREATE TRIGGER jst BEFORE INSERT ON t2
3    -> FOR EACH ROW
4    -> BEGIN
5    ->   SET NEW.c2 = js_pow(NEW.c1, 2);
6    ->   SET NEW.c3 = js_pow(NEW.c1, 3);
7    -> END;
8    -> //
9Query OK, 0 rows affected (0.02 sec)
10
11mysql> delimiter ;
12mysql>

이 트리거는 t2에 로우가 인서트될 때 작동합니다. 첫 번째 컬럼에 인서트된 값을 가져와, 이 값의 제곱을 두 번째 컬럼에 인서트하고, 세제곱을 세 번째 컬럼에 인서트합니다. 테이블에 몇 개의 로우를 인서트하여 트리거를 테스트합니다. 컬럼 c1에 대해 제공하는 값만 버려지지 않으므로, 나머지 두 컬럼에 대해서는 다음과 같이 간단히 NULL을 사용할 수 있습니다:

1mysql> INSERT INTO t2
2    -> VALUES
3    ->   ROW(1, NULL, NULL),
4    ->   ROW(2.49, NULL, NULL),
5    ->   ROW(-3, NULL, NULL),
6    ->   ROW(4.725, NULL, NULL);
7Query OK, 4 rows affected (0.01 sec)
8Records: 4  Duplicates: 0  Warnings: 0

트리거에 의해 호출되는 함수는 JavaScript로 작성되었으므로, JavaScript 반올림 규칙이 적용됩니다. 따라서 2.49는 2로 내림되고, 4.75는 5로 올림됩니다. TABLE 구문을 사용해 결과를 확인하면 이것이 사실임을 알 수 있습니다:

1mysql> TABLE t2;
2+------+------+------+
3| c1   | c2   | c3   |
4+------+------+------+
5|    1 |    1 |    1 |
6|    2 |    4 |    8 |
7|   -3 |    9 |  -27 |
8|    5 |   25 |  125 |
9+------+------+------+
104 rows in set (0.00 sec)

다음 예제들은 MySQL JavaScript 저장 프로그램에서 VECTOR 값을 다루는 기본적인 방법 몇 가지를 보여 줍니다. 먼저 VECTOR 컬럼 c1을 포함하는 테이블 v1을 다음과 같이 생성합니다:

1mysql> CREATE TABLE v1 (
2    ->   c1 VECTOR(3)
3    -> );
4Query OK, 0 rows affected (0.02 sec)

이 테이블에 값을 인서트하기 위해 인자로 벡터의 문자열 표현을 받아 이를 VECTOR 값으로 변환한 뒤 인서트하는 JavaScript 저장 프로시저 vxin을 생성합니다. 그런 다음 다음과 같이 이 프로시저를 여러 번 호출합니다:

1mysql> CREATE PROCEDURE vxin (IN val VARCHAR(100))
2    -> LANGUAGE JAVASCRIPT
3    ->   AS $$
4    $>     let q = "INSERT INTO v1 VALUES("
5    $>     q += "STRING_TO_VECTOR(\"" + val + "\")"
6    $>     q += ")"
7    $>
8    $>     let s = session.sql(q)
9    $>
10    $>     s.execute()
11    $>   $$;
12Query OK, 0 rows affected (0.00 sec)
13
14mysql> CALL vxin("[50, 100, 50]");
15Query OK, 1 row affected (0.01 sec)
16
17mysql> CALL vxin("[100, 0, -50]");
18Query OK, 1 row affected (0.00 sec)
19
20mysql> CALL vxin("[250, 350, 450]");
21Query OK, 1 row affected (0.01 sec)

v1이 채워진 후, TABLE v1의 출력은 다음과 같습니다:

1mysql> TABLE v1;
2+----------------------------+
3| c1                         |
4+----------------------------+
5| 0x000048420000C84200004842 |
6| 0x0000C84200000000000048C2 |
7| 0x00007A430000AF430000E143 |
8+----------------------------+
93 rows in set (0.00 sec)

다음으로, JavaScript 저장 프로시저 **vxout1**을 생성합니다. 이 프로시저는 **v1**의 모든 로우를 셀렉트하여 컬럼 값을 가져와 stdout에 기록합니다. 이 프로시저를 생성하기 위해 사용되는 CREATE PROCEDURE 구문은 다음과 같습니다:

1mysql> CREATE PROCEDURE vxout1 ()
2    -> LANGUAGE JAVASCRIPT
3    ->   AS $$
4    $>     let s = session.sql("TABLE v1");
5    $>
6    $>     let res = s.execute()
7    $>
8    $>     console.log(res.getColumnNames())
9    $>
10    $>     let row = res.fetchOne()
11    $>
12    $>     while(row) {
13    $>       console.log(row.toArray())
14    $>       row = res.fetchOne()
15    $>     }
16    $>   $$;
17Query OK, 0 rows affected (0.00 sec)

이 프로시저는 다음과 같이 테스트할 수 있습니다:

1mysql> SELECT mle_session_reset();
2+------------------------------------------+
3| mle_session_reset()                      |
4+------------------------------------------+
5| The session state is successfully reset. |
6+------------------------------------------+
71 row in set (0.00 sec)
8
9mysql> CALL vxout1();
10Query OK, 0 rows affected (0.01 sec)
11
12mysql> SELECT mle_session_state("stdout")\G
13*************************** 1. row ***************************
14mle_session_state("stdout"): c1
1550,100,50
16100,0,-50
17250,350,450
18
191 row in set (0.00 sec)

TABLE 구문 출력에서 보았던 것과 동일한 바이너리 표현을 기대할 수도 있습니다. 그러나 JavaScript는 VECTOR 값을 배열(Float32Array 인스턴스)로 처리하므로, 배열 형식을 사용해 표시합니다. 원한다면 SQL HEX() 함수를 사용하여 이러한 값을 바이너리 표기를 사용해 표시하도록 강제할 수 있습니다. 예시는 다음과 같습니다:

1mysql> CREATE PROCEDURE vxout2 ()
2    -> LANGUAGE JAVASCRIPT
3    ->   AS $$
4    $>     let s = session.sql("SELECT HEX(c1) FROM v1");
5    $>
6    $>     let res = s.execute()
7    $>
8    $>     console.log(res.getColumnNames())
9    $>
10    $>     let row = res.fetchOne()
11    $>
12    $>     while(row) {
13    $>       console.log(row.toArray())
14    $>       row = res.fetchOne()
15    $>     }
16    $>   $$;
17Query OK, 0 rows affected (0.01 sec)
18
19mysql> SELECT mle_session_reset();
20+------------------------------------------+
21| mle_session_reset()                      |
22+------------------------------------------+
23| The session state is successfully reset. |
24+------------------------------------------+
251 row in set (0.00 sec)
26
27mysql> CALL vxout2();
28Query OK, 0 rows affected (0.00 sec)
29
30mysql> SELECT mle_session_state("stdout")\G
31*************************** 1. row ***************************
32mle_session_state("stdout"): HEX(c1)
33000048420000C84200004842
340000C84200000000000048C2
3500007A430000AF430000E143
36
371 row in set (0.00 sec)

표시 형식과는 관계없이 VECTOR 값은 문자열이나 배열이 아닌 벡터로 처리됩니다. 이는 (MySQL HeatWave 전용) DISTANCE() 함수를 사용하는 다음 예제에서 확인할 수 있습니다. 먼저 다음에 보이는 SQL 구문을 사용해 두 개의 VECTOR 컬럼을 포함하는 테이블 v2를 생성하고 데이터를 채웁니다:

1CREATE TABLE v2 (
2  c1 VECTOR(3),
3  c2 VECTOR(3)
4);
5
6INSERT INTO v2 VALUES
7  ROW(STRING_TO_VECTOR("[50, 100, 50]"), STRING_TO_VECTOR("[0, 200, 0]")),
8  ROW(STRING_TO_VECTOR("[100, 0, -50]"), STRING_TO_VECTOR("[5, 10, 5]")),
9  ROW(STRING_TO_VECTOR("[250, 350, 450]"), STRING_TO_VECTOR("[-150, 1000, 50]"))
10;

다음 쿼리는 각 로우에 있는 두 벡터의 내적(dot product)을 보여 줍니다:

1mysql> SELECT VECTOR_DISTANCE(c1, c2, "DOT") AS d FROM v2;
2+--------+
3| d      |
4+--------+
5|  20000 |
6|    250 |
7| 335000 |
8+--------+
93 rows in set (0.00 sec)

참고

두 벡터의 내적은 성분들의 곱을 순서대로 합한 값으로 정의됩니다. 예를 들어, 테이블 v5의 두 번째 로우에 있는 벡터([100, 0, -50] 및 [5, 10,\ 5])의 경우 내적은 (100)(5) + (0)(10) + (-50)(5) = 500 + 0 - 250 = 250입니다.

두 벡터의 내적을 구하기 위한 JavaScript 함수를 다음과 유사하게 직접 작성할 수 있습니다:

1mysql> CREATE FUNCTION dot_product (v1 VECTOR, v2 VECTOR)
2    -> RETURNS FLOAT DETERMINISTIC
3    -> LANGUAGE JAVASCRIPT
4    ->   AS $$
5    $>     if(v1.length !== v2.length)
6    $>       throw new Error('Vectors must be of the same length')
7    $>
8    $>     let dot = 0, i=0
9    $>
10    $>     for(i=0; i<v1.length; i++)
11    $>       dot += v1[i]*v2[i]
12    $>
13    $>     return dot
14    $>   $$;
15
16mysql> SELECT dot_product(c1, c2) AS dot FROM v5\G
17*************************** 1. row ***************************
18dot: 20000
19*************************** 2. row ***************************
20dot: 250
21*************************** 3. row ***************************
22dot: 335000

다음으로, 동일한 쿼리를 실행하는 JavaScript 저장 프로시저 vxout5를 다음과 같이 생성합니다:

1mysql> CREATE PROCEDURE vxout5 ()
2    -> LANGUAGE JAVASCRIPT
3    ->   AS $$
4    $>     let s = session.sql("SELECT VECTOR_DISTANCE(c1, c2, \"DOT\") AS d FROM v5");
5    $>
6    $>     let res = s.execute()
7    $>
8    $>     console.log(res.getColumnNames())
9    $>
10    $>     let row = res.fetchOne()
11    $>
12    $>     while(row) {
13    $>       console.log(row.toArray())
14    $>       row = res.fetchOne()
15    $>     }
16    $>   $$;
17Query OK, 0 rows affected (0.01 sec)

vxout5를 실행하면(이전에 했던 것처럼 먼저 mle_session_reset()을 사용해 세션 상태를 초기화하고), v5의 컬럼 값이 벡터로 처리되며, mysql 클라이언트에서 쿼리를 직접 실행했을 때와 동일한 결과를 얻는다는 것을 확인할 수 있습니다:

1mysql> SELECT mle_session_reset();
2+------------------------------------------+
3| mle_session_reset()                      |
4+------------------------------------------+
5| The session state is successfully reset. |
6+------------------------------------------+
71 row in set (0.00 sec)
8
9mysql> CALL vxout5();
10Query OK, 0 rows affected (0.00 sec)
11
12mysql> SELECT mle_session_state("stdout")\G
13*************************** 1. row ***************************
14mle_session_state("stdout"): d
1520000
16250
17335000
18
191 row in set (0.00 sec)