mysql------存储过程和函数

1、创建存储过程和函数

（1） 创建存储过程

创建存储过程，需要使用 CREATE    PROCEDURE 语句 。

语法： CREATE   PROCEDURE

            sp_name   ( [proc_parameter] )   [characteristics ...]

            routine_body

sp_name ：存储过程的名字；
proc_parameter：为指定存储过程的参数列表，列表形式如下：[ IN | OUT | INOUT ] param_name   type 。IN表示输入参数，OUT表示输出参数，INOUT表示可以输入也可以输出，param_name表示参数名称，  type表示参数类型；
characteristics：指定存储过程的特性；
routine_body：SQL代码的内容，可以用begin...end来表示开始和结束

例1：创建查看fruits表的存储过程， 每次调用这个存储过程的时候都会执行SELECT语句查看表的内容，代码的执行过程如下

DELIMITER //
 CREATE PROCEDURE Proc()
     BEGIN
     SELECT * FROM fruits;
     END //
Query OK, 0 rows affected (0.00 sec)

 DELIMITER ;

delimiter //是将mysql的结束符设置成// ,完毕后delimiter ;恢复默认结束符，当使用DELIMITER命令时，应该避免使用反斜杠（’\’）字符，因为反斜线是MySQL的转义字符。

例2：创建一个获取fruits表记录条数的存储过程，名称是CountProc，COUNT(*) 计算后把结果放入参数param1中

DELIMITER // 
 CREATE PROCEDURE CountProc(OUT param1 INT)
  BEGIN
  SELECT COUNT(*) INTO param1 FROM fruits;
  END //
Query OK, 0 rows affected (0.00 sec)
 DELIMITER ;

（2） 创建存储函数

创建存储过程，需要使用 CREATE   FUNCTION 语句 。

语法：CREATE   FUNCTION      func_name( [func_parameter] )

                 RETURNS    type

                 [characteristic ...]         routine_body

 func_name：存储函数的名字；

func_parameter：为指定存储过程的参数列表，列表形式如下：[ IN | OUT | INOUT ] param_name   type 。IN表示输入参 数，OUT表示输出参数，INOUT表示可以输入也可以输出，param_name表示参数名称，  type表示参数类型；
RETURNS    type ：表示函数返回数据的类型；
characteristics：指定存储函数的特性。

例1：创建存储函数，名称为NameByZip，参数为空，该函数返回SELECT语句的查询结果，返回的数值类型为字符串型

CREATE FUNCTION NameByZip ()
 RETURNS CHAR(50)
 RETURN  (SELECT s_name FROM suppliers WHERE s_call= '48075');

（3） 变量的使用
A、定义变量

语法：DECLARE    var_name[,varname]…     date_type    [DEFAULT value]；

如果没有default  子句，初始值为null
例1：定义名称为myparam的变量，类型为INT类型，默认值为100

DECLARE  myparam  INT  DEFAULT 100;

B、为变量赋值

语法：SET    var_name = expr [, var_name = expr] ...;

例1：声明3个变量，分别为var1、var2和var3，数据类型为INT，使用SET为变量赋值

DECLARE var1, var2, var3 INT;
SET var1 = 10, var2 = 20;
SET var3 = var1 + var2;

MySQL中还可以通过SELECT ... INTO为一个或多个变量赋值
语法：SELECT   col_name[,...]    INTO    var_name[,...] table_expr;
这个SELECT语法把选定的列直接存储到对应位置的变量。
col_name表示字段名称；var_name表示定义的变量名称；table_expr表示查询条件表达式，包括表名称和WHERE子句。

例2：声明变量fruitname和fruitprice，通过SELECT ... INTO语句查询指定记录并为变量赋值

DECLARE fruitname CHAR(50);
DECLARE fruitprice DECIMAL(8,2);

SELECT f_name,f_price INTO fruitname, fruitprice
FROM fruits WHERE f_id ='a1';

（4）   定义条件和处理程序

A、定义条件

B、定义处理程序

（5） 光标的使用

Mysql的光标只能在存储过程和函数中使用

A、声明光标

创建光标语法：Declare 光标名 custor for  select语句内容

如：daclare cursor_fruit cursor for selectf_name,f_price from fruits;

B、 打开光标
语法：open 光标名;

如：open cusor_fruit;

C、 使用光标

语法：fetch 光标名  into 参数1，参数2……

参数必须在声明光标之前就定义好，把select查询出来的信息存入该参数中。

如：fetch cursor_fruit into fruit_name,fruit_price;

表示将cursor_fruit中select语句查询出来的信息存入fruit_name和fruit_price中。

D、 关闭光标

语法：close 光标名

如：close cursor_fruit;

（6） 流程控制的使用

A、IF 语句

B、 CASE 语句

C、 LOOP 语句

D、 LEAVE 语句

E、 ITERATE 语句

F、 REPEAT 语句

G、 WHILE 语句

2、调用存储过程和函数

（1） 调用存储过程

存储过程的调用是通过 CALL 语句进行调用的 。

语法：CALL    sp_name ([parameter[,...]])

例1：定义存储过程

 DELIMITER //
 CREATE PROCEDURE CountProc1 (IN sid INT, OUT num INT)
     BEGIN
       SELECT COUNT(*) INTO num FROM fruits WHERE s_id = sid;
     END //
Query OK, 0 rows affected (0.00 sec)

  DELIMITER ;

调用存储过程

 CALL CountProc1 (101, @num);
Query OK, 1 row affected (0.00 sec)

查看返回结果

select @num;
+------+
| @num |
+------+
|    3 |
+------+
1 row in set (0.02 sec)

该存储过程返回了指定s_id=101的水果商提供的水果种类，返回值存储在num变量中，使用SELECT查看，返回结果为3。

当存储过程中传入中文参数时，要加上character set gbk

如：create procedure useinfo(in u_name varchar(50) characterset gbk,out u_age int)

（2）   调用存储函数

存储函数的使用方法与MySQL内部函数的使用方法是一样的

例1：定义存储函数CountProc2，然后调用这个函数

 DELIMITER //
 CREATE FUNCTION  CountProc2 (sid INT)
     RETURNS INT
     BEGIN
     RETURN (SELECT COUNT(*) FROM fruits WHERE s_id = sid);
     END //
Query OK, 0 rows affected (0.00 sec)
  DELIMITER ;

调用存储函数

SELECT CountProc2(101);
+--------------------+
| Countproc(101) |
+--------------------+
|             3 |
+-------------------+

3、 查看存储过程和函数

(1)SHOW   STATUS 语句查看存储过程和函数的状态

语法：SHOW {PROCEDURE | FUNCTION}     STATUS     [LIKE 'pattern']

 如：SHOW    PROCEDURE   STATUS    LIKE     'C%'\G

(2)SHOW    CREATE语句查看存储过程和函数的定义

语法：SHOW  CREATE   {PROCEDURE | FUNCTION}    sp_name

如：SHOW    CREATE    FUNCTION      test.CountProc    \G

(3)从 information_schema.Routines 表中查看存储过程和函数的信息

MySQL 中存储过程和函数的信息存储在 information_schema 数据库下的 Routines 表中 。 可以通过查询该表的记录来查询存储过程和函数的信息。

语法：SELECT   *    FROM       information_schema.Routines     WHERE     ROUTINE_NAME=' sp_name ' ;

如：SELECT   *   FROM    information_schema.Routines   WHERE        ROUTINE_NAME='CountProc'     AND  ROUTINE_TYPE = 'FUNCTION'     \G

4、修改存储过程和函数

使用 ALTER 语句可以修改存储过程或函数的特性。

语法：ALTER {PROCEDURE | FUNCTION}      sp_name [characteristic ...]

例1：修改存储过程CountProc的定义。将读写权限改为MODIFIES   SQL   DATA，并指明调用者可以执行

ALTER  PROCEDURE  CountProc  
MODIFIES SQL DATA
SQL SECURITY INVOKER ; 

查询修改后的CountProc表信息 

SELECT SPECIFIC_NAME,SQL_DATA_ACCESS,SECURITY_TYPE
      FROM information_schema.Routines
     WHERE ROUTINE_NAME='CountProc' AND ROUTINE_TYPE='PROCEDURE';

5、删除存储过程和函数

删除存储过程和函数，可以使用 DROP 语句 。

语法：DROP {PROCEDURE | FUNCTION}     [IF    EXISTS]       sp_name

例1：删除存储过程和存储函数

DROP PROCEDURE CountProc;
DROP FUNCTION CountProc;

转载请注明出处：http://blog.csdn.net/linshuxin111/