How to make TWO loops in a UDF at mysql

Question

similar to StackOverflowPosting I would like to calculate the Levenshtein Distance for a m x n matrix consisting of TITLE1 and TITLE2.

My Levenshtein Functions works fine and is from here: LD But my Question is how can I loop through the m x n in a UDF?

The Result should be a table with m x n rows with LD, TITLE1 and TITLE2.

I have done this - BUT I ALWAYS GET AN ERROR

     1338 Cursor Declaration after Handler Declaration

My UDF looks like this:

  BEGIN  
       DECLARE bDone INT;
       DECLARE bDone1 INT;

       DECLARE var2 varCHAR(255);    -- or approriate type
       DECLARE Var1 INT;
       DECLARE c1Var1 VARCHAR(250);

       DECLARE curs CURSOR FOR  SELECT recid as BIOTIrecid, replace(replace(BIOGRAPHYTITLE," [in SCOPUS]",""),"[SIMILAR]","") as bioti FROM BIO ;
       DECLARE CONTINUE HANDLER FOR NOT FOUND SET bDone = 1;
       DECLARE curs1 CURSOR FOR  SELECT trim(concat(scopus.Titel," ",scopus.Untertitel)) as scopusti FROM scopus ;
       DECLARE CONTINUE HANDLER FOR NOT FOUND SET bDone = 1;

       DROP  TABLE IF EXISTS LDResults;
        CREATE TABLE `LDResults` (
           `BIOGRAPHYTITLE` varchar(255) DEFAULT NULL,
           `recid` int(11) NOT NULL AUTO_INCREMENT,
           `BIOTIrecid` int(11) default NULL,
           `LD` varchar(255) DEFAULT NULL,
           `ScopusTI` varchar(255) DEFAULT NULL,
           PRIMARY KEY (`recid`)
        ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

        OPEN curs1;

        SET bDone1 = 0;
        #---------------- run all rows for scopusti
        REPEAT
            FETCH curs1 into c1var1;

        #-----------------------------------------
            OPEN curs;

            SET bDone = 0;
            #----- run all COLUMNs for biographytitle
            REPEAT
                FETCH curs INTO var1, var2;
                INSERT INTO LDResults (`BIOGRAPHYTITLE`, `BIOTIrecid`, `LD`, `ScopusTI`) VALUES (var2, var1, LEVENSHTEIN(var2,c1var1), c1var1);
            UNTIL bDone END REPEAT;
        #--------------------------------------------
        CLOSE curs;

        UNTIL bDone1 END REPEAT;


       CLOSE curs1;
       SELECT * FROM LDResults;
     END

Is my way to solve this problem sophisticated or could this be done on a more faster and better solution ?

Thanks for all advices.

EDIT: I could make it with a counter here: Any comments?

     BEGIN  
        -- DECLARE bDone INT;
        -- DECLARE bDone1 INT;

        DECLARE i INT;

       DECLARE var2 varCHAR(255);    -- or approriate type
       DECLARE Var1 INT;
       DECLARE cVar1 VARCHAR(250);

       DECLARE curs1 CURSOR FOR  SELECT trim(concat(t.Titel," ",t.Untertitel)) as scopusti FROM tscopus t ;
       DECLARE curs CURSOR FOR  SELECT recid as BIOTIrecid, replace(replace(BIOGRAPHYTITLE," [in SCOPUS]",""),"[SIMILAR]","") as bioti FROM tBIO ;
       DECLARE CONTINUE HANDLER FOR NOT FOUND SET bDone = 1;
       #DECLARE curs1 CURSOR FOR  SELECT trim(concat(t.Titel," ",t.Untertitel)) as scopusti FROM tscopus t ;
       #DECLARE CONTINUE HANDLER FOR NOT FOUND SET bDone = 1;

       DROP  TABLE IF EXISTS LDResults;
        CREATE TABLE `LDResults` (
       `BIOGRAPHYTITLE` varchar(255) DEFAULT NULL,
       `recid` int(11) NOT NULL AUTO_INCREMENT,
       `BIOTIrecid` int(11) default NULL,
       `LD` varchar(255) DEFAULT NULL,
       `ScopusTI` varchar(255) DEFAULT NULL,
       PRIMARY KEY (`recid`)
        ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;


        OPEN curs1;
        SET i = 0;

        SET bDone1 = 0;
        -- ---------------- run all rows for scopusti
        REPEAT
            FETCH curs1 into cvar1;

         set i=(i+1);
        -- -----------------------------------------
            OPEN curs;

            SET bDone = 0;
            -- ----- run all COLUMNs for biographytitle
            REPEAT
                FETCH curs INTO var1, var2;
                INSERT INTO LDResults (`BIOGRAPHYTITLE`, `BIOTIrecid`, `LD`, `ScopusTI`) VALUES (var2, var1, LEVENSHTEIN(var2,cvar1), cvar1);
            UNTIL bDone END REPEAT;
        -- --------------------------------------------
        CLOSE curs;

        UNTIL (i >= 2) END REPEAT;


       CLOSE curs1;
       SELECT * FROM LDResults;
     END

Slava Rozhnev · Accepted Answer · 2020-04-18T10:01:35.583

I mean you can do it by next way useng CROSS JOIN without loops in your code. CROSS JOIN by definition return product of two tables rows result. So you can use this result and after some data manipulation insert the result into new table like:

DROP  TABLE IF EXISTS LDResults;
CREATE TABLE `LDResults` (
   `BIOGRAPHYTITLE` varchar(255) DEFAULT NULL,
   `recid` int(11) NOT NULL AUTO_INCREMENT,
   `BIOTIrecid` int(11) default NULL,
   `LD` varchar(255) DEFAULT NULL,
   `ScopusTI` varchar(255) DEFAULT NULL,
   PRIMARY KEY (`recid`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

INSERT INTO LDResults (`BIOGRAPHYTITLE`, `BIOTIrecid`, `LD`, `ScopusTI`)
SELECT bioti, BIOTIrecid, LEVENSHTEIN(bioti,scopusti), scopusti
FROM (
    SELECT 
        replace(replace(BIO.BIOGRAPHYTITLE," [in SCOPUS]",""),"[SIMILAR]","") as bioti,
        BIO.recid as BIOTIrecid,
        trim(concat(scopus.Titel," ",scopus.Untertitel)) as scopusti 
    FROM scopus
    CROSS JOIN BIO
) tbl;

How to make TWO loops in a UDF at mysql

1 Answers1