美文网首页
11、MySQL存储过程条件语句

11、MySQL存储过程条件语句

作者: go以恒 | 来源:发表于2018-09-26 11:37 被阅读0次

    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、循环语句

    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 ;
    

    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 ;
    

    loop

    BEGIN
        
        declare i int default 0;
        loop_label: loop
            
            set i=i+1;
            if i<8 then
                iterate loop_label;
            end if;
            if i>=10 then
                leave loop_label;
            end if;
            select i;
        end loop loop_label;
    
    END
    

    3、动态执行SQL语句

    动态执行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 ;
    

    相关文章

      网友评论

          本文标题:11、MySQL存储过程条件语句

          本文链接:https://www.haomeiwen.com/subject/opedoftx.html