mysql存储过程、函数及游标

存储过程是一个程序化函数，由sql语句及相应的控制结构组成。

DELIMITER $$

CREATE PROCEDURE Total_Orders(OUT Total FLOAT)
BEGIN
    SELECT SUM(Amount) INTO Total FROM Orders;
END
$$

DELIMITER ;

DELIMITER #将语句结束符；改为$，将存储过程看成一个整体，执行完存储过程后需要将其改回结束符 ;

使用存储过程

CALL Total_Orders(@t);         #将存储过程的结果放在一个用户变量@t里面

SELECT @t;

函数

DELIMITER $$

CREATE FUNCTION Add_Tax(Price FLOAT) 
RETURNS FLOAT 
DETERMINISTIC
BEGIN
    DECLARE Tax FLOAT DEFAUT 0.10;
    RETURN Price*(1+Tax);
END
$$

DELIMITER ;

存储函数参数中没有IN或OUT ，参数都是输入值

returns float 表示函数返回一个float类型的数值

DETERMINISTIC表示在给定相同参数情况下，一个确定性函数可以返回相同的值，该位置可以有别的关键字，例如NO SQL 表示程序体没有sql语句

使用return返回一个值

##查看定义

SHOW CREATE PROCEDURE Total_Orders;
SHOW CREATE FUNCTION Add_Tax;

##删除存储过程或者函数

DROP PROCEDURE Total_Orders;
DROP FUNCTION Add_Tax;

##调用函数

SELECT Add_Tax(@a);

 ##游标的使用

#Procedure to find the orderid with the largest amount
#could be done with max ,but just to illustrate stored procedure
#principles

DELIMITER $$

CREATE PROCEDURE Largest_Order(OUT Largest_ID INT)
BEGIN
    DECLARE This_ID INT;
    DECLARE This_Amout FLOAT;
    DECLARE L_Amout FLOAT DEFAULT 0.0;
    DECLARE L_ID INT;

    DECLARE Done INT DEFAULT 0;
    DECLARE C1 CURSOR FOR SELECT OrderID,Amount FROM Orders;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET Done=1;
    
    OPEN C1;
    REPEAT
        FETCH C1 INTO This_ID,This_Amout;
        IF NOT DONE THEN
            IF This_Amount > L_Amount THEN
                SET L_Amount=This_Amout;
                SET L_ID = This_ID;
            END IF;
        END IF;
    UNTIL Done END REPEAT;
    CLOSE C1;

SET Largest_ID = L_ID;

END
$$

DELIMITER ;

使用过程

①在存储过程中声明游标 DECLARE C1 CURSOR FOR SELECT OrderID,Amount FROM Orders;

②声明处理程序 DECLARE CONTINUE HANDLER FOR NOT FOUND SET Done = 1;   

③打开游标OPEN C1；

④获取每行数据，

FETCH C1 INTO This_ID,This_Amout;

使用repeat。。。until done end repeat；来遍历。或者使用while  condition DO   .....end while;

LOOP ....END LOOP;使用LEAVE语句退出

⑤关闭游标CLOSE C1;

⑥返回输出值。

CALL Largest_Order(@a);

SELECT @a;