首页 > 代码库 > MYSQL PL/SQL

MYSQL PL/SQL

一组SQL语句集,速度快,只有首次执行需经过编译和优化步骤,后续被调用可以直接执行

Sample:

DROP PROCEDURE IF EXISTS `proc_adder`;

DELIMITER &&

CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_adder`(IN a int, IN b int, OUT sum int)

BEGIN

DECLARE c int;

/*DECLARE c int DEFAULT 10*/

    if a is null then set a = 0;

    end if;

  

    if b is null then set b = 0;

    end if;
set sum  = a + b;

END

&&

DELIMITER ;


说明:

DEFINER: 创建者

DECLARE: 定义变量

SET:为变量赋

SELECTINTO语句为变量赋值如下:

SELECT  col_name[,]  INTO  var_name[,]  

FROM  table_name  WEHRE  condition

DELIMITER

MySQL中默认的语句结束符为分号(;)。存储过程中的SQL语句需要分号(;)来结束。为了避免冲突,首先用"DELIMITER &&"MySQL的结束符设置为&&。最后再用"DELIMITER ;"来将结束符恢复成分号。这与创建触发器时是一样的。


常用语法:

IF:

IF type = 0 THEN

        set c = ‘param is 0‘;

    ELSEIF type = 1 THEN

        set c = ‘param is 1‘;

    ELSE

        set c = ‘param is others, not 0 or 1‘;

END IF;

 

CASE:

CASE type

    WHEN 0 THEN

        set c = ‘param is 0‘;

    WHEN 1 THEN

        set c = ‘param is 1‘;

    ELSE

        set c = ‘param is others, not 0 or 1‘;

END CASE;

 

WHILE:

DECLARE i int;

DECLARE s int;

SET i = 0;

SET s = 0;

WHILE i <= n DO

    set s = s + i;

    set i = i + 1;

END WHILE;

本文出自 “小小码农” 博客,请务必保留此出处http://daoqingyu.blog.51cto.com/5185063/1950443

MYSQL PL/SQL