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, fruitpriceFROM 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 DATASQL 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/