Mysql数据库 存储过程和存储函数

存储过程：

存储过程是一组为了完成特定的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给定参数来调用执行它。

创建存储过程的关键字： PROCEDURE

格式：CREATE PROCEDURE  sp_name （proc_parameter[,...]）

                         [characteristic] routin_body

其中，sp_name参数是存储过程的名字；proc_parameter表示存储过程的参数列表；characteristic参数指定存储过程的特性；routine_body参数是SQL代码的内容，可以用begin....end来标识SQL代码的开始和结束。

proc_parameter中的每个参数由3部分组成。分别是：输入，输出类型、参数名称、和参数类型。

输入参数用IN表示，输出参数用OUT表示，INOUT表示即可输入也可输出。

characteristic参数的说明：

language SQL: 说明routine_body部分是由SQL语言的语句组成，这也是数据库系统默认的语言。

[not] deteministic :指明存储过程中的执行结果是否确定。deteministic表示结果是确定的，每次执行存储时，相同的输入会得到相同的输出。not deterministic 表示结果是非确定的，相同的输入可能得到不同的输出。在默认情况下，结果都是非确定的。

{contains SQL | no SQL |  reads SQL data | modifies SQL data} ：指明程序使用SQL语句的限制。cintains SQL 表示子程序包含SQL语句，但不包含读或者写的语句.。 no SQL 表示子程序中不包含SQL语句；reads SQL data 表示子程序中包含读数据的语句； modifies SQL data表示子程序中包含写数据的语句。在默认情况下，系统会指定为cintains SQL。

SQL security{definer|invoker}:指明谁有权来执行。definer 表示只有定义者自己才能够执行；Invoker 表示调用者才可以执行。在默认情况下，系统指定的权限是definer。

调用存储过程的语法格式如下：

CALL sp_name ([parameter[,...]])

存储函数的创建：

语法格式：

CREATE FUNCTION sp_name ([func_parameter[,....]])

RETURNS type

[characteristic...] routine_body

其中，sp_name参数是存储函数的名称；func_parameter表示存储函数的参数列表；RETURNS type指定返回值的类型；characteristic参数指定存储函数的特性，该参数的取值与存储过程中的取值是一样的；routine_body参数是SQL代码的内容，可以用BEGIN...END标志SQL代码的开始和结束。

调用存储函数语法格式如下：

SELECT sp_name ([func_parameter[,....]])

DELIMITER命令

在MySQL命令行的客户端中，服务器处理语句默认是以分号（；）作为结束标志的，如果有一行命令以分号结束，那么按【enter】键后，MySQL将会执行该命令。但在存储过程中，可能输入较多的语句，且语句中含分号。如果还以分号作为结束标志，那么，我们可以用MySQL DELIMITER来改变默认的结束标志。

DELIMITER格式语法为：

                DELIMITER  $ $

其中，$$是用户定义的结束符，通常使用一些特殊字符发的符号。当使用DELIMITER命令时，应该避免使用反斜杠\字符，因为那是MySQL的转义字符。

变量：

（1）DECLARE语句申明局部变量。

注意：DECLARE 语句申明局部的变量只适用于BEGIN....ENG之间。

语法格式：

DECLARE var_name[,var_name2]...type [default vaule]

type参数用来指明变量的类型，default vaule 字句将变量默认值设置为vaule，没有使用default字句，默认是null

（2）用SET语句来给变量赋值。

格式： 

SET     var_name= exper[,var_name= exper]

DECLARE 定义的变量作用范围是BEGIN....ENDZ块内，只能在块中使用。SET定义的变量为用户变量。在定义变量时，变量名称前使用@修饰。如SET @ var =12;

(3)使用SELECT  语句给变量赋值。

SELECT  clo_name[,...] INTO var_name [,...] table_expr

其中col_name是列名，var_name是要赋值的变量名称，table_expr是select语句中的from字句后面的部分。

下面是实例代码:

-- 存储过程	
DELIMITER &&
		CREATE PROCEDURE teacherinfo1(IN teacherid FLOAT, IN type FLOAT,OUT info VARCHAR(20)) 
		READS SQL DATA
		BEGIN
	  CASE  type
	     WHEN 1 THEN
	         SELECT namea INTO info FROM teacherinfo WHERE  num = teacherid;
			 WHEN 2 THEN
					 SELECT YEAR(NOW())-YEAR(Birthday) INTO info FROM teacherinfo WHERE num = teacherid;
			 ELSE
					SELECT 'Error' into info ;
		END CASE;
	END &&
	DELIMITER 

-- (存储函数)

	DELIMITER &&
	CREATE FUNCTION Pfood_price_count1(price_info1 FLOAT, price_info2 FLOAT)
	  RETURNS INT READS SQL DATA 
		   BEGIN 
			   RETURN (SELECT COUNT(*) FROM food WHERE price>price_info1 AND price <price_info2);
	END &&
DELIMITER ;