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 
      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 
     
        {BEFORE | AFTER}  {
    INSERT | UPDATE | DELETE} ON 
      
        (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 测试触发器.
      
      
        FOR EACH ROW