Re: recursive search

From: Thomas 'PointedEars' Lahn <PointedEars_at_web.de>
Date: Sun, 11 Sep 2016 04:38:49 +0200
Message-ID: <8989816.nUPlyArG6x_at_PointedEars.de>

mireero wrote:
^^^^^^^

[Quoted] Your real name belongs there.

> Thanks for the answers.
>
> What I get is:
> - No recursive support in mysql […]

Incorrect. There are no recursive stored *FUNCTIONs* in MySQL (yet).

But recursive stored *PROCEDUREs* are supported, and with “call by reference” they can work like FUNCTIONs. For example, consider the textbook recursive function, the factorial function, written as a procedure:

$ echo '
/* Show original GRANTS */
SHOW GRANTS; DROP PROCEDURE IF EXISTS fac;

/* For mysql(1) only: Pass “;” in procedure code through to server */ DELIMITER // CREATE PROCEDURE fac (IN i TINYINT, OUT result BIGINT) DETERMINISTIC BEGIN
  DECLARE fac_result BIGINT;

  IF i < 0 THEN
    SET result = NULL;
  ELSEIF i < 2 THEN
    SET result = 1;
  ELSE
    CALL fac(i - 1, fac_result);
    SET result = fac_result * i;
  END IF;
END// /* For mysql(1) only: Restore statement delimiter */ DELIMITER ; /* Show that GRANTS have changed to include the new stored procedure */ SHOW GRANTS; /* Required for recursion, the default is 0 */ SET _at__at_max_sp_recursion_depth = 255;

CALL fac(15, _at_result);
SELECT _at_result AS `15!`\G
' | mysql -u guest tmp
Grants for guest_at_localhost
GRANT USAGE ON *.* TO 'guest'_at_'localhost' GRANT SELECT, CREATE ROUTINE, ALTER ROUTINE ON `tmp`.* TO 'guest'_at_'localhost'
Grants for guest_at_localhost
GRANT USAGE ON *.* TO 'guest'_at_'localhost' GRANT SELECT, CREATE ROUTINE, ALTER ROUTINE ON `tmp`.* TO 'guest'_at_'localhost'
GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE `tmp`.`fac` TO 'guest'_at_'localhost'

*************************** 1. row ***************************

15!: 1307674368000

$ mysql --version
mysql Ver 14.14 Distrib 5.6.25, for debian-linux-gnu (x86_64) using EditLine wrapper

[Quoted] [15! appears to be the maximum factorial that can be computed with this procedure in the default MySQLd setup.]

-- 
PointedEars

Twitter: _at_PointedEars2
Please do not cc me. / Bitte keine Kopien per E-Mail.

Received on Sun Sep 11 2016 - 04:38:49 CEST