mysql自定义函数收集

代码：

查找字符串 in_string 中，存在多少个字符串 in_find_str

delimiter $$
DROP FUNCTION IF EXISTS `fn_findCharCount` $$
CREATE FUNCTION fn_findCharCount
(
in_string VARCHAR(500),
in_find_str VARCHAR(50)
)
RETURNS INT
BEGIN

    DECLARE tcount INT DEFAULT 0;
    DECLARE new_str VARCHAR(500);
    DECLARE scount INT;

    SET new_str = in_string;

    SELECT INSTR(new_str,in_find_str) INTO scount;

WHILE scount>0 DO
SET tcount = tcount+1;
SELECT SUBSTRING(new_str FROM (scount+1) FOR CHAR_LENGTH(new_str)) INTO new_str;
SELECT INSTR(new_str,in_find_str) INTO scount;
END WHILE;
    
    RETURN(tcount);

END$$
delimiter ;

 产生范围在 start_num 和 end_num 之间的随机数：

delimiter $$
DROP FUNCTION IF EXISTS `fn_rand_num` $$
CREATE FUNCTION fn_rand_num (
    start_num INTEGER,
    end_num INTEGER
) RETURNS INTEGER
BEGIN
    RETURN FLOOR(start_num + RAND() * (end_num - start_num + 1));
END$$;
delimiter ;

 删除函数：DROP FUNCTION IF EXISTS `fn_findCharCount`;执行函数：select fn_findCharCount('abcd','b');

查询该项目以及子项目的库存数据

DELIMITER $$

USE `glw-pro-0218`$$

DROP FUNCTION IF EXISTS `checkProject`$$

CREATE DEFINER=`root`@`%` FUNCTION `checkProject`(projectId1 VARCHAR(50),projectId2 VARCHAR (50)) RETURNS VARCHAR(2555) CHARSET utf8mb4
BEGIN
  DECLARE str VARCHAR (2555) DEFAULT '';
 
  RETURN (SELECT EXISTS (
SELECT 1 FROM 
(
SELECT all_project.id FROM tbl_glw_project all_project WHERE all_project.id = projectId2 OR all_project.parent_id = projectId2
) temp1
WHERE id = projectId1
));
END$$

DELIMITER ;

SELECT t1.current_number FROM tbl_glw_item_location AS t1 WHERE checkProject(t1.project_id,'706f2cd2e7b911ec80b80242ac150006');