mysql存储过程和函数示例

v

2013-09-23 23:44:58|  分类： MySQL基础篇 |  标签：mysql存储过程和函数   |举报 |字号 订阅

本来想说些关于这个前奏，快晚上12点了。直接上正题吧！

字体说明： 红色字体为需要强调的。 蓝色字体为MySQL关键字。 绿色字体为解释性说明文字。

另，图片里面的标注和红色字体解释也很重要。

1.  创建存储过程

     语法：

CREATE PROCEDURE    sp_name (  [  proc_parameter ] )

[   characteristics ...  ]   routune_body

      说明：  proc_parameter  的形式有：    [   IN   |   OUT    |   INOUT  ]  paramP_name    type .

routune_body 为SQL语句组成。

     例： 创建一个名称为AvgPrice的存储过程，返回所有产品的平均价格。如下：

/*   此存储过程虽然没有参数。但是后面的 () 还是得要加上  */

CREATE  PROCEDURE  AvgPrice   ()

BEGIN

  SELECT  AVG(price) AS  avgPrice  

  FROM  product;

END;

注意： DELIMITER 的用法。

      如图：

 

     例： 创建一个名为 CountProc的存储过程，带有输出参数。

CREATE  PROCEDURE CountProc( OUT param1  INT )

BEGIN

  SELECT  COUNT(*) INTO  param1  FROM  product;

END;

     如图：

 

注意： 使用DELIMITER命令时候，应该避免使用反斜杠（‘  \  ’）字符，因为反斜杠是转义字符。

2.  创建函数

     语法：

CREATE  FUNCTION func_name (  [  func_parameter  ]  )

           RETURNS   type

[   characteristic ...  ]  routine_body 

      说明：  func_name   为函数名称； func_parameter   的形式有：    

[   IN   |   OUT    |   INOUT  ]  paramP_name    type . 

RETURNS   type 语句表示函数的返回数据的类型。

     例：创建存储函数，名称为TestFunc1. 返回id为 1 的产品名称。

CREATE  FUNCTION TestFunc1()

RETURNS VARCHAR(30)

RETURN ( SELECT  prodname FROM product WHERE id='1' );

     如图：

 

3.  变量的使用

3.1  定义变量

       语法：

DECLARE  var_name  [  ,varname  ] ... data_type  [   DEFAULT  value ] ;

       例： 定义名称为p1的变量，数据类型为INT，默认值为 100。如下：

DECLARE  p1   INT  DEFAULT  100 ;

3.2  为变量赋值

       语法：

SET   var_name  = expr [ ,  var_name = expr  ] ... ;

        例： 声明3个变量var1、 var2、var3，数据类型为INT，使用SET赋值。如下：

DECLARE  var1 , var2 , var3  INT ;

SET  var1 = 10,  var2 = 20;

SET  var3 = var1 + var2 ;

        例：使用 SELECT ...  INTO 赋值。如下：

DECLARE name VARCHAR(20) ;

DECLARE  prodprice  DECIMAL( 8 ,2 );

SELECT  prodname , price   INTO    name  ,  prodprice    FROM  product  WHERE  id = '1' ;

4.  定义条件和处理程序

4.1  定义条件

       语法：

DELCARE   condition_name   CONDITION   FOR  [  condition_type ]

其中 [  condition_type ] ：

SQLSTATE  [ VALUE ]  sqlstate_vlaue  |  mysql_error_code

     例： 定义 “ERRROR 1148(42000)”错误。名称为command_not_allowed。用两种方式定义：

//方法一： 使用sqlstate_value

DECLARE   command_not_allowed  CONDITION  FROM SQLSTATE  '42000' ;

//方法二：使用mysql_error_code

DECLARE   command_not_allowed  CONDITION  FOR  1148;

4.2  定义处理程序

       语法：

DECLARE  handler_type   HANDLER   FOR  condition_value  [ , ...  ] sp_statement

其中 handler_type    形式如下：

                                         CONTINUE  |   EXIT    |   UNDO

         

其中 condition_value      形式如下：

                                         SQLSTATE[ VALUE  ]  sqlstate_value

                                         |  condition_name

                                         |  SQLWARNING

                                         |  NOT  FOUND

                                         |  SQLEXCEPTION

                                         |  mysql_error_code

      说明：

handler_type 为处理方式，参数取值有3个值：

      CONTINUE  （表示遇到错误不处理，继续执行）；

        EXIT（表示 遇到错马上就退出）；

        UNDO（表示遇到错误就撤回之前的操作，MySQL5.5还不 支持）。

condition_value   表示错误类型，可以取值有：

       SQLSTATE[ VALUE  ]  sqlstate_value 包含5个字符的字符串错误值；

       condition_name表示 DECLARE  CONDITION 定义的错误条件名称；

       SQLWARNING 匹配所有以 01 开头的SQLSTATE错误代码；

       NOT FOUND 匹配所有以 02 开头的SQLSTATE错误代码；

       SQLEXCEPTION 匹配所有没有被SQLWARNING 和或者NOT  FOUND 捕获的

SQLSTATE错误代码；

     例：定义处理程序。如下：

// 方法一：捕获sqlstate_value 

DECLARE  CONTINUE HANDLER  FOR SQLSTATE  '42S02'  SET @info = 'NO_SUCH_TABLE';

//方法二： 捕获mysql_error_code

DECLARE  CONTINUE  HANDLER  FOR 1146  SET @info = 'NO_SUCH_TABLE';

//方法三：先定义条件，然后调用

DECLARE no_such_table    CONDITION  FOR 1146;

DECLARE CONTINUE   HANDLER  FOR  NO_SUCH_TABLE  SET  @info = ' NO_SUCH_TABLE ';

//方法四：使用 SQLWARNING

DECLARE EXIT HANDLER FOR  SQLWARNING  SET @info = 'ERROR';

//方法五：使用 NOT  FOUND 

DECLARE  EXIT HANDLER FOR NOT FOUND SET @info = 'NO_SUCH_TABLE';

//方法六：使用 SQLEXCEPTION

DECLARE EXIT HANDLER  FOR SQLEXCEPTION   SET  @info = 'ERROR';

     例：定义条件和处理程序，具体执行如下：

CREATE  TABLE t7( s1 int ,primary key(s1)  );

CREATE PROCEDURE testhandler()

BEGIN

 DECLARE  CONTINUE  HANDLER  FOR SQLSTATE '23000' SET  @x2 = 1 ;

 SET @X = 1;

 INSERT INTO t7 VALUES(1);

 SET  @x = 2;

 INSERT  INTO  t7 VALUES(1);

 SET  @x = 3;

END ;

CALL  testhandler();

SELECT  @x;

     如图：

 

在本例中，将  DECLARE  CONTINUE  HANDLER  FOR SQLSTATE '23000' SET  @x2 = 1 ;去掉

查看返回情况：

 

5.  游标的使用

游标只能在存储过程和函数中使用。

5.1  声明游标

      语法：

DECLARE  cursor_name   CURSOR   FOR  select_statement 

5.2  打开游标

       语法：

OPEN  cursor_name {游标名称}

5.3  使用游标

        语法：

FETCH   cursor_name   INTO  var_name [  , var_name  ]...{参数名称}

5.4  关闭游标

       语法：

CLOSE   cursor_name {游标名称};

6.  流程控制语句

6.1  IF 语句

       语法：

IF   expr_condtion   THEN   statement_list

      [   ELSEIF  expr_condition   THEN   statement_list  ] ...

      [   ELSE   statement_list  ]

END   IF 

     

     例：IF语句例子。如下：

IF  val  IS  NULL 

     THEN  SELECT  'val  is  NULL'

      ELSEIF  SELECT  'val is not  NULL'

END  IF ;

6.2  CASE语句

       语法：

CASE   case_expr   

        WHEN   when_value   THEN   statement_list

        [   WHEN  when_value   THEN  statement  ] ....

        [   WHEN  statement_list  ]

END  CASE

       或者

CASE     

        WHEN   expr_condition   THEN   statement_list

        [  WHEN   expr_condition  THEN   statement_list ] ....

        [   WHEN  statement_list  ]

END  CASE

     例：CASE 语句示例。如下：

CASE val

       WHEN  1   THEN  SELECT  'val  is 1';

       WHEN  2   THEN  SELECT  'val  is 2';

        ELSE  SELECT  'val  is not 1  or 2 ';

END  CASE;

      例：CASE语句的另一种形式。如下：

CASE

       WHEN  val  IS NULL THEN  SELECT  'val  is NULL';

       WHEN  val < 0  THEN  SELECT  'val is less than 0;

       WHEN  val > 0  THEN  SELECT  'val  is greater than 0';

END  CASE;

       

6.3  LOOP 语句

       语法：

[  loop_lable : ]   LOOP

    statement_list

END  LOOP [loop_lable]

        例：使用LOOP进行循环操作，id值小于10等于之前，将重复执行循环过程。如下：

DECLARE  id INT  DEFAULT  0;

add_loop: LOOP

     SET  id = id + 1;

     IF id >= 10 THEN  LEAVE  add_loop;

     END IF;

END  LOOP add_loop;

6.4  LEAVE 语句

LEAVE 语句用来退出任何被标注的流程控制构造。

       语法：

LEAVE   lable

       例： 使用LEAVE 语句退出循环。如下：

add_num : LOOP

     SET  @count = @count + 1;

     IF @count = 50  THEN  LEAVE  add_num;

     END  IF;

END  LOOP  add_num;

6.5  ITERATE  语句

ITERATE 语句将执行顺序转到语句段开头出，相当于java中的continue。

       语法：

ITERATE    lable ;

      例：如果p1的值小于10时，重复执行p1加1操作；当p1大于等于10且小于20 的时候打印

消息“p1  is  between  10  an  20”；当p1大于20时，退出循环。如下：

CREATE PROCEDURE doiterate()

BEGIN

DECLARE  p1  INT  DEFAULT  0;

my_loop : LOOP

   SET p1 = p1 +1;

   IF  p1 < 10 THEN  ITERATE  my_loop;

   ELSEIF p1 > 20 THEN LEAVE my_loop;

    END  IF;

   SELECT  ' p1  is  between  10  an  20 '  AS msg;

END  LOOP my_loop;

END;

      如图：

 

6.6  REPEAT 语句

REPEAT 语句创建一个带有条件判断的循环过程，每次语句执行完毕后，会对条件表达式进行判断，如果

表达式为真，则循环结束；否则重复执行循环体中的语句。

      语法：

[repeat_lable : ] REPEAT 

    statement_list

UNTIL  expr_condition

END REPEAT  [ repeat_lable ]

     例：id值小于等于10之前，将重复执行循环过程。如下：

DECLARE  id  INT  DEFAULT  0;

REPEAT 

   SET id = id +1;

   UNTIL  id > 10 ;

END  REPEAT ;

7.  调用存储过程和函数

     调用存储过程用关键字CALL。 调用函数时，直接在语句中使用。 由于这个叫简单。略。

8.  查看存储过程和函数

8.1   SHOW  STATUS 语句查看存储过程和函数的状态。

        语法：

SHOW {   PROCEDURE |   FUNCTION   }   STATUS   [  LIKE 'pattern'  ];  其中 LIKE 语句表示匹配的

存储过程或者函数的名称。

       

        例：获取以 ‘do’ 开头的存储过程的状态。如下：

 

8.2  SHOW  CREATE  语句查看存储过程和函数的定义。

       语法：

SHOW  CREATE  {   PROCEDURE   |   FUNCTION   }  sp_name

     例： 查看存储过程 doiterate的定义。如下：

 

9.  删除存储过程和函数

     语法：

DROP   {   PROCEDURE  |   FUNCTION  }  [   IF  EXISTS  ]  sp_name;

======================END=======================

由于前几晚上都快凌晨一点才睡，吃不消啊。今晚的11点准时睡觉啦。 加油！