mysql系列：存储过程与自定义函数

1、存储过程

定义一个PROCEDURE：

这里演示的是：SELECT ... INTO语句

delimiter //
CREATE PROCEDURE simpleproc (OUT param1 INT)
  BEGIN
    # doSomething
    SELECT COUNT(*) INTO param1 FROM person;
  END
  //
delimiter ;

在程序被定义的时候，用mysql客户端delimiter命令来把语句定界符从 ;变为//。这就允许用在程序体中的 ;定界符被传递到服务器而不是被mysql自己来解释。

执行这个PROCEDURE

CALL simpleproc(@t_out);
SELECT @t_out AS count;

原数据库：

执行结果：

再来一个例子：

这里演示的是：变量SET语句

delimiter //
CREATE PROCEDURE test_in_out(IN param1 INT,OUT param2 INT)
   BEGIN
     SET param2 = param1+1;
   END
  //
delimiter ;


SET @t_in = 1;
CALL test_in_out(@t_in,@t_out);
SELECT @t_out AS result;

结果如下：

存储过程还可以使用：DECLARE局部变量，如下：

delimiter //
CREATE PROCEDURE sp1 (x VARCHAR(5))
  BEGIN
    DECLARE xname VARCHAR(5) DEFAULT 'bob';
    
    SELECT xname,x;
  END;

  //
delimiter ;

CALL sp1(3);

结果如下所示：

注：指定参数为IN, OUT, 或INOUT 只对PROCEDURE是合法的。（FUNCTION参数总是被认为是IN参数）

2、自定义函数

delimiter //
 
CREATE FUNCTION hello (s CHAR(20)) RETURNS CHAR(50)
   RETURN CONCAT('Hello, ',s,'!');
   //
delimiter ;
 
SELECT hello('world') AS greet;

结果如下：

注：如果在存储函数中的RETURN语句返回一个类型不同于在函数的RETURNS子句中指定类型的值，返回值被强制为恰当的类型。

补充：修改使用ALTER，删除使用DROP

一个官方案例，仅供参考：

delimiter //
 
CREATE PROCEDURE curdemo()
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE a CHAR(16);
  DECLARE b,c INT;
  DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1; # 声明游标
  DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;
	# 定义条件，当游标状态为'02000'时，设置 done值为 1
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
 
  OPEN cur1;	#打开游标
  OPEN cur2;
 
  REPEAT	# 循环
    FETCH cur1 INTO a, b;	# 如果有下一行，则读取下一行
    FETCH cur2 INTO c;
    IF NOT done THEN
       IF b < c THEN
          INSERT INTO test.t3 VALUES (a,b);
       ELSE
          INSERT INTO test.t3 VALUES (a,c);
       END IF;
    END IF;
  UNTIL done END REPEAT;	# 直到满足done这个条件后终止
 
  CLOSE cur1;  # 关闭游标
  CLOSE cur2;
END
   //
delimiter ;
 
SELECT curdemo();

注：游标状态'02000'

流程控制构造：

IF, CASE, LOOP, WHILE, ITERATE, 及 LEAVE,目前还不支持FOR循环,此处非本文重点不作过深入讲解