mysql函数、存储过程、触发器、游标

1.   procedure

(1) 存储过程&游标

mysql> DELIMITER //  

mysql> DROP PROCEDURE IF EXISTS ‘oh_qty’ //

/*如果存在同名存储过程则删除*/

mysql> CREATE PROCEDURE oh_qty()     /*创建存储过程*/

    -> BEGIN

    ->   DECLARE plant CHAR(4);

    ->   DECLARE loc   CHAR (4);

    ->   DECLARE part  CHAR (15);

    ->   DECLARE onhand DECIMAL(15,4);

    ->   DECLARE finished INTEGER DEFAULT 0;

    ->   DECLARE oh_cur CURSOR FOR

->       SELECT inv_plant,inv_loc,inv_part,inv_oh_qty FROM inv ;

->    /*声明游标*/

->   DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished=1;

->    /*如果没有找到数据则finished=1*/

    ->   OPEN oh_cur;

    ->    /*打开游标*/

->   calcloop:

->       LOOP

->       FETCH oh_cur INTO plant,loc,part,onhand;

->    /*fetch命令获取游标结果集并保存到变量中*/

    ->       IF finished=1 THEN

    ->            LEAVE calcloop;

    ->        END IF;

->    /*执行leave命令立即退出循环或BEGIN/END块.*/

->        IF plant=’SJ 01’ THEN

->             ITERATE calcloop;

->        END IF;

->     /*执行