Mysql的存储过程

一. 存储过程的定义：

　　存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。

二. 存储过程的优点：

• 简化应用开发人员的工作。当用不同语言编写多客户应用程序，或多客户应用程序在不同平台上运行且需要执行相同的数据库操作之时。

• 增强安全性。比如，银行对所有普通操作使用存储程序。这提供一个坚固而安全的环境，程序可以确保每一个操作都被妥善记入日志。在这样一个设置中，应用程序和用户不可能直接访问数据库表，但是仅可以执行指定的存储程序。

• 减少数据在数据库和应用服务器之间的传输。

三.存储过程的权限

• 使用权限：

    ALTER ROUTINE 编辑或删除存储过程
 　 CREATE ROUTINE 建立存储过程
    EXECUTE 运行存储过程

    CREATE PROCEDURE p()SQL SECURITY INVOKER创建存储过程中有一个特征子句可以让存储过程使用运行者的权限

    CREATE PROCEDURE p() SQL SECURITY DEFINER 默认值

四. 存储过程的创建

 

　　CREATE PROCEDURE sp_name ([proc_parameter[,...]])   

        [characteristic ...] routine_body　　

　　默认地，子程序与当前数据库关联。要明确地把子程序与一个给定数据库关联起来，可以在创建子程序的时候指定其名字为db_name.sp_name。

　　 sp_name 存储过程的名字proc_parameter指定参数为IN， OUT，或INOUTcharacteristic 特征routine_body 包含合法的SQL过程语句。

　　in 把数据从外部传递给存储过程

　　out 从存储过程内部返回值给外部使用者

　　inout  把数据传递给存储过程和将存储过程的返回值传递给外部使用者

　　示例：

 

　　CREATE PROCEDURE sp_test      /* 存储过程名 */(IN inparms INT,OUT outparams varchar(32))

五. 存储过程的删除

　　DROP PROCEDURE  [IF EXISTS] sp_name

    这个语句被用来移除一个存储程序或函数。即，从服务器移除一个制定的子程序。在MySQL 5.1中，你必须有ALTER ROUTINE权限才可用此子程序。这个权限被自动授予子程序的创建者。IF EXISTS 子句是一个MySQL的扩展。如果程序或函数不存在，它防止发生错误。示例：DROP PROCEDURE IF EXISTS `Proc_Notify_UserfavDiscount_Mail2`$$

六. 存储过程的状态

　　SHOW  PROCEDURE  STATUS ;显示数据库中所有存储的存储过程基本信息

 

　　show create procedure procedure_name;显示某一个存储过程的创建语句

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

delimiter //             /*改变输入结束符*/ DROP PROCEDURE IF EXISTS sp_test // CREATE PROCEDURE sp_test                                         /* 存储过程名 */ (IN inparms INT,OUT outparams varchar(32))                         /* 输入参数 */ BEGIN                                                           /* 语句块头 */      DECLARE var CHAR(10);                                      /* 变量声明 */      DECLARE num int;      IF inparms = 1 THEN                                         /* IF条件开始*/          SET var = 'hello';                                        /* 赋值 */      ELSE         SET var = 'world';      END IF;                                                     /* IF结束 */      INSERT INTO t1 VALUES (var);                                /* SQL语句 */      select count(*) from t1 into num;      SELECT name FROM t1 LIMIT num, 1 INTO outparams; END // delimiter ;  call sp_test(1, @out); Select @out; 事先创建表 create table t1(id int not null auto_increment,name varchar(45),primary key pk_id (id));

七. 存储过程的变量

　　声明变量：

　　DECLARE var_name[,...] type [DEFAULT value]     → 这个语句被用来声明局部变量。如果要给变量提供一个默认值，需要  包含一个DEFAULT子句。值可以被指定为一个表达式，不需要为一个常  数。如果没有DEFAULT子句，初始值为NULL。    → 局部变量的作用范围在它被声明的BEGIN ... END块内。示例：DECLARE fromDate DATETIMEDECLARE nHour 　　　INTDECLARE v_exit INT DEFAULT 0

八. 变量赋值

　　变量赋值，可以直接赋值，或者通过查询赋值

　　语句：

　　SET var_name = expr [, var_name = expr] ...select col_name into var_name[,...]  table_expr 

　　示例：SET nowDate=NOW();SET a.fanli_discount = b.max_fanli;select count(*) into num  from table1 where price=100;

九. BEGIN...END复合语句

　　[begin_label:] BEGIN      [statement_list] END [end_label] 

　　存储子程序可以使用BEGIN ... END复合语句来包含多个语句。statement_list 代表一个或多个语句的列表。statement_list之内每个语句都必须用分号（；）来结尾。   复合语句可以被标记。除非begin_label存在，否则end_label不能被给出，并且如果二者都存在，他们必须是同样的。   使用多重语句需要客户端能发送包含语句定界符;的查询字符串。这个符号在命令行客户端被用delimiter命令来处理。改变查询结尾定界符;（比如改变为//）使得; 可被用在子程序体中。举例：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

DELIMITER // CREATE PROCEDURE p1(IN parameter1 INT)       BEGIN       DECLARE variable1 INT;       SET variable1 = parameter1 + 1;       IF variable1 = 0 THEN           INSERT INTO t VALUES (17);       END IF;       IF parameter1 = 0 THEN           UPDATE t SET s1 = s1 + 1;       ELSE           UPDATE t SET s1 = s1 + 2;       END IF; END // DELIMITER ;

十. 流程控制

　　IF语句

      IF search_condition THEN statement_list         [ELSEIF search_condition THEN statement_list] ...        [ELSE statement_list]      END IF 

　　 IF实现了一个基本的条件构造。如search_condition求值为真，相应的SQL语句列表被执行。如果没有search_condition匹配，在ELSE子句里的语句列表被执行。

　　statement_list可以包括一个或多个语句。

十一. CASE语句

  　CASE case_value       WHEN when_value THEN statement_list         [WHEN when_value THEN statement_list] ...         [ELSE statement_list]  END CASEOr:  　　　　  CASE       WHEN search_condition THEN statement_list         [WHEN search_condition THEN statement_list] ...         [ELSE statement_list]  END CASE

　　存储程序的CASE语句实现一个复杂的条件构造。如果search_condition 求值为真，相应的SQL被执行。如果没有搜索条件匹配，在ELSE子句里的语句被执行。

   示例：

delimiter //CREATE PROCEDURE p2 (IN parameter1 INT)BEGIN     DECLARE variable1 INT;     SET variable1 = parameter1 + 1;     CASE variable1          WHEN 0 THEN INSERT INTO t  VALUES (17);          WHEN 1 THEN INSERT INTO t   VALUES (18);          ELSE INSERT INTO t VALUES (19);     END CASE;END// 

十二. 循环语句

WHILE … END WHILE示例：delimiter //CREATE PROCEDURE p4 ()BEGIN       DECLARE v INT;       SET v = 0;       WHILE v < 5 DO           INSERT INTO t VALUES (v);           SET v = v + 1;      END WHILE;END //

 

LOOP … END LOOP示例：CREATE PROCEDURE p5 ()BEGIN     DECLARE v INT;     SET v = 0;     loop_label: LOOP         INSERT INTO t VALUES (v);         SET v = v + 1;         IF v >= 5 THEN             LEAVE loop_label;         END IF;    END LOOP;END //[begin_label:] LOOP         statement_listEND LOOP [end_label]LOOP允许某特定语句或语句群的重复执行，实现一个简单的循环构造。在循环内的语句一直重复直到循环被退出，退出通常伴随着一个LEAVE 语句。

十三. 补充：迭代（ITERATE）语句

CREATE PROCEDURE p7 ()BEGIN     DECLARE v INT;     SET v = 0;     loop_label: LOOP         IF v = 3 THEN             SET v = v + 1;             ITERATE loop_label;         END IF;         INSERT INTO t VALUES (v);         SET v = v + 1;         IF v >= 5 THEN             LEAVE loop_label;         END IF;     END LOOP;END //

十四. 注释语法：

mysql存储过程可使用两种风格的注释双模杠：--，该风格一般用于单行注释c风格：/* 注释内容 */， 一般用于多行注释

 

 

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36

DELIMITER $$   USE `51fanli_cang`$$   DROP PROCEDURE IF EXISTS `Proc_Notify_UserfavDiscount_Mail2`$$   CREATE DEFINER=`root`@`%` PROCEDURE `Proc_Notify_UserfavDiscount_Mail2`(IN latestPushTime DATETIME) BEGIN DROP TEMPORARY TABLE IF EXISTS tmp_Userfav_Discount_Cnt; CREATE TEMPORARY TABLE tmp_Userfav_Discount_Cnt     AS     SELECT         a.user_id,         COUNT(a.id) COUNT,         ROUND(SUM(a.price) - SUM(b.pricenow), 2) diffprice     FROM fav_userfav a         INNER JOIN fav_product b             ON a.product_id = b.id     WHERE a.is_del = 0         AND a.price_flg = 2         AND a.in_buy = 0         AND b.pricedown_time >= latestPushTime     GROUP BY a.user_id;    SELECT user_id,user_name,user_email,topic_title,link_pic,price,link_value,source_title,pricenow,shop_id,source_pic,COUNT,diffprice,rank FROM (     SELECT user_id,user_name,user_email,topic_title,link_pic,price,link_value,source_title,pricenow,shop_id,source_pic,COUNT,diffprice,@rownum:=@rownum+1,     IF(@pdept=heyf_tmp.user_id,@rank:=@rank+1,@rank:=1) AS rank,      @pdept:=heyf_tmp.user_id FROM (     SELECT a.user_id ,a.topic_title,a.link_pic,a.price,b.link_value,b.source_title,b.pricenow,b.shop_id,b.source_pic,c.count,c.diffprice,d.user_name,d.user_email FROM fav_userfav a INNER JOIN fav_product b ON  a.product_id=b.id INNER JOIN     tmp_Userfav_Discount_Cnt c ON a.user_id=c.user_id INNER JOIN fav_user d ON d.user_id = c.user_id     WHERE  a.is_del = 0 AND a.price_flg = 2 AND a.in_buy = 0 AND d.user_email IS NOT NULL     ORDER BY a.user_id,b.pricedown_time,a.price - b.pricenow DESC     ) heyf_tmp ,(SELECT @rownum :=0 , @pdept := NULL ,@rank:=0) a ) result      WHERE rank<=5; END$$   DELIMITER ;