Mysql自訂函數

標籤：

1.mysql自訂函數

delimiter $$create function f1(    i1 int,    i2 int)returns intBEGIN    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 ;

 

 

 

Mysql自訂函數