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;

->     /*执行iterate语句将使嵌入该语句的LOOP,REPEAT,或WHILE循环返回到顶部，并再次执行.此处将去除plant=’SJ 01’ 的数据.*/

->   UPDATE inv SET inv_oh_qty=inv_oh_qty * 10

->    WHERE inv_plant=plant AND inv_loc=loc AND inv_part=part;

    ->     /*此处对数据集进行处理*/

    ->   END LOOP calcloop;  /*loop循环结束*/

    ->   CLOSE oh_cur;     /*关闭游标*/

    ->   END//           /*存储过程结束*/

Query OK, 0 rows affected (0.00 sec)

 

mysql> DELIMITER ; 

mysql> CALL oh_qty();   /*执行存储过程*/

Query OK, 1 row affected (0.02 sec)

（2）Procedure:

   mysql>DELIMITER //

   mysql>CREATE PROCEDURE get_inventory(OUT inv INT)

->SELECT 45 INTO inv;

->//

mysql>CALL get_inventory(@inv);

mysql>SELECT @inv;

/*如果希望在存储过程外使用变量，需要将其作为OUT变量传递*/

 

返回结果：45

 

（3）IF语句

   IF years_employed<5 THEN

      SET bonus=total*.05;

   ELSEIF years_employed>=5 and years_employed<10 THEN

      SET bonus=total*.06;

   ELSEIF years_employed>=10 THEN

      SET bonus=total*.07;

   END IF

 

 （4）CASE语句

   CASE

       WHEN state=”AL” THEN

           SET tax_rate=.04;

       WHEN state=”AK” THEN

           SET tax_rate=.00;

   END CASE;

 

   或：

    CASE state

       WHEN ”AL” THEN

           SET tax_rate=.04;

       WHEN ”AK” THEN

           SET tax_rate=.00;

   END CASE;

 

  （5）REPEAT语句

CREATE PROCEDURE test_date(rows INT)

BEGIN

   DECLARE val1 FLOAT;

   DECLARE val2 FLOAT;

 

   REPEAT

      SELECT RAND() INTO val1;

      SELECT RAND() INTO val2;

      INSERT INTO analysis VALUES(NULL,val1,val2);

      SET rows=rows-1;

UNTIL rows=0

END REPEAT;

END//

      /*此存储过程将随机产生五条记录插入到analysis表中*/

 

    （6）WHILE语句

 CREATE PROCEDURE test_date(rows INT)

BEGIN

   DECLARE val1 FLOAT;

   DECLARE val2 FLOAT;

 

   WHILE rows> 0 DO

      SELECT RAND() INTO val1;

      SELECT RAND() INTO val2;

      INSERT INTO analysis VALUES(NULL,val1,val2);

      SET rows=rows-1;

END WHILE;

END//

      /*执行结果同上*/

 

   （7）查看例程状态

   mysql>SHOW PROCEDURE STATUS /G

   mysql>SHOW PROCEDURE STATUS LIKE ‘get_products’ /G

   mysql>SHOW FUNCTION STATUS /G

   mysql>SHOW FUNCTION STATUS LIKE ‘get_%’ /G

   

    （8）删除存储过程

   mysql>DROP PROCEDURE calculate_bonus;

   mysql>DROP FUNCTION calculate_bonus;

 

   （9）查看例程的创建语法

   mysql>SHOW CREATE PROCEDURE get_products;

   mysql>SHOW CREATE FUNCTION calculate_bonus;

 

    （10）php调用procedure

   <?php

     $result = mysql_query(“CALL get_employees()”);

   ?>

 

 

 

2.function

/*根据员工的销售收入计算奖金*/

DELIMITER //

CREATE FUNTION calculate_bonus(employ_id CHAR(8))

RETURNS DECIMAL(10,2)

COMMENT ‘Calculate employee bonus’

BEGIN

   DECLARE total DECIMAL(10,2);

   DECLARE bonus DECIMAL(10,2);

   SELECT SUM(price) INTO total

FROM sales WHERE employee_id=employee_id;

   SET bonus= total * 0.05;

   RETURN bonus;

END;

//

 

DELIMITER ;

 

/*调用calculate_bonus函数*/

SELECT calculate_bonus(“35558zhu”);

 

3.trigger

 语法：

 CREATE TRIGGER <trigger name>

 {BEFORE | AFTER}

 {INSERT | UPDATE | DELETE}

ON <table name>

FOR EACH ROW

<triggered SQL statement>

(1) 查看现有触发器

  语法：SHOW TRIGGERS [FROM db_name] [LIKE expr]

实例：mysql>SHOW TRIGGERS/G

还有一种方法是：对INFORMATION_SCHEMA数据库中的TRIGGERS表查询

mysql>SELECT * FROM triggers T WHERE trigger_name=”mytrigger” /G

(2)修改触发器

   暂时还没有修改现有触发器的命令，看来只能先删除后重建啦^_^

(3)删除触发器

  DROP TRIGGER table_name.trigger_name;

为了成功执行，需要SUPER权限

（4）列名前可以加上NEW或OLD来使用列的最新值或原始值。

 例子：

  1.创建mytrigger.sql文件：

   DELIMITER //

   CREATE TRIGGER mytrigger

   AFTER UPDATE ON inv FOR EACH ROW

   BEGIN

       IF NEW.oh_qty<0 THEN

          SET NEW.oh_qty=0;

       END IF;

   END//

   DELIMITER ;

2.    导入mytrigger.sql文件到mysql数据库中：

mysql>/. C:/mytrigger.sql;

3. update inv 测试触发器.