存储过程，触发器，游标

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;

->    

    ->   OPEN oh_cur;

    ->    

->   calcloop:

->       LOOP

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

->    

    ->       IF finished=1 THEN

    ->            LEAVE calcloop;

    ->        END IF;

->    

->        IF plant=’SJ01’ THEN

->             ITERATE calcloop;

->        END IF;

->     

->   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;  

    ->   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;

 

 

返回结果：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//

（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 ;

 

 

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 测试触发器.