1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35
|
DELIMITER |
DROP FUNCTION IF EXISTS generateName|
CREATE FUNCTION generateName (val VARCHAR(100), ext VARCHAR(20)) RETURNS VARCHAR(100)
BEGIN
DECLARE i INT;
DECLARE loopval VARCHAR(100);
DECLARE newvalcount VARCHAR(100);
DECLARE val2 VARCHAR(100);
SET val = TRIM(val);
SET loopval = '';
SET i = 0;
SET loopval = val;
SET val = SUBSTRING(val,1,INSTR(val, SUBSTRING_INDEX(val, '.', -1)) - 2);
SET val2 = SUBSTRING(val,1,INSTR(val, SUBSTRING_INDEX(val, '(', -1)) - 2);
IF val2 != '' THEN
SET val = val2;
END IF;
txtexist: LOOP
SET newvalcount = 0;
SET newvalcount = (SELECT COUNT(pk_ged_docs) FROM ged_docs WHERE TRIM(doc_titre) LIKE TRIM(loopval));
IF newvalcount = 0
THEN LEAVE txtexist;
END IF;
SET i = i + 1;
SET loopval = CONCAT_WS('', val, '(' , i,')');
SET loopval = CONCAT(loopval, ext);
END LOOP;
RETURN loopval;
END;
|
DELIMITER ; |
Partager