Movatterモバイル変換

Two functions, listed here, provide JavaScriptFunction objects reflecting MySQL stored routines:

Use theclose() method to close the resource associated with the stored routine. An error is thrown if the routine, after it is closed, is called again, or if itsclose() method is called again.

The following example creates two stored functionsgetArea() andgetDiag(), then creates and runs a JavaScript stored procedure procRect which uses these functions by instantiating them and executing them by means ofFunction objects.

mysql> CREATE FUNCTION getArea(w INT, h INT)    -> RETURNS INT DETERMINISTIC    -> RETURN w * h;Query OK, 0 rows affected (0.01 sec)mysql> CREATE FUNCTION getDiag(w INT, h INT)     ->   RETURNS FLOAT DETERMINISTIC    ->   RETURN Math.sqrt(Math.pow(w, 2) + Math.pow(h, 2));Query OK, 0 rows affected (0.01 sec)mysql> CREATE PROCEDURE procRect(IN x INT, IN y INT) LANGUAGE JAVASCRIPT    -> AS $$    $>   console.clear()    $>     $>   let s = session.getDefaultSchema()    $>   let f = s.getFunction("getArea")    $>   let g = s.getFunction("getDiag")    $>     $>   let a = x    $>   let b = y    $>     $>   console.log (    $>                 "Width: " + a + ", Height: " + b + "; Area: " +     $>                 f(a,b) + "; Diagonal: " + g(a,b)    $>               )    $>     $>   f.close()    $>   g.close()    $> $$;Query OK, 0 rows affected (0.01 sec)mysql> CALL procRect(5, 10);Query OK, 0 rows affected (0.03 sec)mysql> SELECT mle_session_state("stdout")\G*************************** 1. row ***************************mle_session_state("stdout"): Width: 5, Height: 10; Area: 50; Diagonal: 11.1803398132324221 row in set (0.00 sec)mysql> CALL procRect(2, 25);Query OK, 0 rows affected (0.02 sec)mysql> SELECT mle_session_state("stdout")\G*************************** 1. row ***************************mle_session_state("stdout"): Width: 2, Height: 25; Area: 50; Diagonal: 25.0798721313476561 row in set (0.00 sec)

For stored functions, arguments are simply passed by value, as shown in the examples just shown withgetDiag() andgetArea(). For stored procedures, argument handling is as follows:

mysql.arg().  This function is always called as a method of the globalmysql object. It creates anArgument object, which can be assigned a value on creation, or by a procedure call. Afterwards, the value can be retrieved asargument.val. This is shown in the following example, where argument instancesa andb are created inuse_my_proc() to act as placeholders fory andz inmy_proc():

mysql> CREATE PROCEDURE my_proc(    ->   IN x INT,    ->   OUT y VARCHAR(20),    ->   INOUT z TEXT    -> )    -> LANGUAGE JAVASCRIPT    -> AS $$    $>     y = "Hello world " + x    $>     z += "Hello again JS"    $> $$;Query OK, 0 rows affected (0.04 sec)mysql> CREATE PROCEDURE use_my_proc() LANGUAGE JAVASCRIPT    -> AS $$    $>     console.clear()    $>    $>     let s = session.getDefaultSchema()    $>     let p = s.getProcedure("my_proc")    $>    $>     let a = mysql.arg()    $>     let b = mysql.arg("World ")    $>    $>     p(42, a, b)    $>    $>     console.log(a.val)    $>     console.log(b.val)    $>    $>     p.close()    $> $$;Query OK, 0 rows affected (0.01 sec)mysql> CALL use_my_proc();Query OK, 0 rows affected (0.00 sec)mysql> SELECT mle_session_state("stdout")\G*************************** 1. row ***************************mle_session_state("stdout"): Hello world 42World Hello again JS1 row in set (0.00 sec)

Equivalents between the MySQL types ofOUT orINOUT parameters and JavaScript types are shown in the following table: