Mysql自定义函数

1.mysql自定义函数

delimiter $$
create function f1(
    i1 int,
    i2 int)
returns int
BEGIN
    declare num int;
    set num = i1 + i2;
    return(num);
END $$
delimiter ;

2.删除函数

drop function func_name;

3、执行函数

# 获取返回值
declare @i VARCHAR(32);
select UPPER('alex') into @i;
SELECT @i;


# 在查询中使用
select f1(11,nid) ,name from tb2;

4.mysql 条件语句

1.if 条件语句

delimiter $$
CREATE PROCEDURE proc_if ()
BEGIN
    
    declare i int default 0;
    if i = 1 THEN
        SELECT 1;
    ELSEIF i = 2 THEN
        SELECT 2;
    ELSE
        SELECT 7;
    END IF;

END$$
delimiter ;

2.循环语句

　　1）。while循环

delimiter \\
CREATE PROCEDURE proc_while ()
BEGIN

    DECLARE num INT ;
    SET num = 0 ;
    WHILE num < 10 DO
        SELECT
            num ;
        SET num = num + 1 ;
    END WHILE ;

END\\
delimiter ;

　　2）。repeat循环

delimiter \\
CREATE PROCEDURE proc_repeat ()
BEGIN

    DECLARE i INT ;
    SET i = 0 ;
    repeat
        select i;
        set i = i + 1;
        until i >= 5
    end repeat;

END\\
delimiter ;

　　3）。loop

delimiter \\
CREATE PROCEDURE proc_loop ()
BEGIN
    
    declare i int default 0;
    loop_label: loop
        select i;
        set i=i+1;
        if i>=5 then
            leave loop_label;
            end if;
    end loop;

END\\
delimiter ;

3、动态执行SQL语句

delimiter \\
DROP PROCEDURE IF EXISTS proc_sql \\
CREATE PROCEDURE proc_sql ()
BEGIN
    declare p1 int;
    set p1 = 11;
    set @p1 = p1;

    PREPARE prod FROM 'select * from tb2 where nid > ?';
    EXECUTE prod USING @p1;
    DEALLOCATE prepare prod; 

END\\
delimiter ;

 

 最新版mysql8.0自定义函数示例

CREATE DEFINER=`root`@`%` FUNCTION `CountLayer`(`nodeId` INT) RETURNS int(11)
    READS SQL DATA
BEGIN
  DECLARE resultCount INT(11);
  DECLARE Lft INT;
  DECLARE Rgt INT;
  SET @resultCount = 3;
  IF EXISTS(SELECT Id FROM setting_tree WHERE Id=@nodeId) THEN 
        SELECT Lft,Rgt INTO @Lft,@Rgt FROM setting_tree WHERE Id=@nodeId;
    SELECT IFNULL(COUNT(*),0) INTO @resultCount FROM setting_tree WHERE Lft<=@Lft AND Rgt>=@Rgt;
  END IF;
    RETURN @resultCount;
END