一: 前言
CREATE DATABASE IF NOT EXISTS `db_book2` DEFAULT CHARACTER SET GBK;
USE `db_book2`;
DROP TABLE IF EXISTS `t_book`;
CREATE TABLE `t_book` (
`id` INT(11) PRIMARY KEY AUTO_INCREMENT,
`bookName` VARCHAR(20) DEFAULT NULL,
`price` DECIMAL(6,2) DEFAULT NULL,
`author` VARCHAR(20) DEFAULT NULL,
`bookTypeId` INT(11) DEFAULT NULL
) ENGINE=INNODB DEFAULT CHARSET=GBK;
INSERT INTO `t_book` VALUES (NULL,'Java编程思想','100.00','埃史尔',1),(NULL,'Struts2权威指南','80.00','李刚',1),(NULL,'三剑客','70.00','大仲马',2),(NULL,'生理学(第二版)','24.00','刘先国',3);
DROP TABLE IF EXISTS `t_booktype`;
CREATE TABLE `t_booktype` (
`id` INT(11) PRIMARY KEY AUTO_INCREMENT,
`bookTypeName` VARCHAR(20) DEFAULT NULL
) ENGINE=INNODB DEFAULT CHARSET=GBK;
INSERT INTO `t_booktype`(`id`,`bookTypeName`) VALUES (1,'计算机类'),(2,'文学类'),(3,'教育类');
这是为大家准备的练习数据,大家复制粘贴到mysql里就行了,如果遇到乱码问题,请参考我的另一篇文章http://blog.csdn.net/a672489861/article/details/16330821
二：存储过程和函数的引入
存储过程和函数是在数据库中定义一些SQL 语句的集合，然后直接调用这些存储过程和函数来执行已经定义好的SQL 语句。存储过程和函数可以避免开发人员重复的编写相同的SQL 语句。而且，存储过程和函数是在MySQL服务器中存储和执行的，可以减少客户端和服务器端的数据传输。
三：创建存储过程和函数
3.1 创建存储过程
CREATE PROCEDURE sp_name([proc_parameter[,...]])
[characteristic...] routine_body
sp_name 参数是存储过程的名称；
proc_parameter 表示存储过程的参数列表；
characteristic 参数指定存储过程的特性；
routine_body 参数是SQL 代码的内容，可以用BEGIN...END 来标志SQL 代码的开始和结束。
proc_parameter 中的每个参数由3 部分组成。这3 部分分别是输入输出类型、参数名称和参数类型。
[ IN | OUT | INOUT ] param_name type
其中，IN 表示输入参数；OUT 表示输出参数；INOUT 表示既可以是输入，也可以是输出；
param_name 参数是存储过程的参数名称；
type 参数指定存储过程的参数类型，该类型可以是MySQL 数据库的任意数据类型；
Characteristic 参数有多个取值。其取值说明如下：
LANGUAGE SQL：说明routine_body 部分是由SQL 语言的语句组成，这也是数据库系统默认的语言。
[ NOT ] DETERMINISTIC ：指明存储过程的执行结果是否是确定的。
DETERMINISTIC 表示结果是确定的。每次执行存储过程时，相同的输入会得到相同的输出。
NOT DETERMINISTIC 表示结果是非确定的，相同的输入
可能得到不同的输出。默认情况下，结果是非确定的。
{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } ：指明子程序使用SQL 语句的限制；
CONTAINS SQL 表示子程序包含SQL 语句，但不包含读或写数据的语句；
NO SQL 表示子程序中不包含SQL语句；
READS SQL DATA 表示子程序中包含读数据的语句；
MODIFIES SQL DATA 表示子程序中包含写数据的语句。默认情况下，系统会指定为CONTAINS SQL；
SQL SECURITY { DEFINER | INVOKER }；指明谁有权限来执行。
DEFINER 表示只有定义者自己才能够执行；
INVOKER 表示调用者可以执行。默认情况下，系统指定的权限是DEFINER。
COMMENT ‘string’ ：注释信息；
例:mysql>DELIMITER $$
CREATE PROCEDURE pro_book(IN b INT)
READS SQL DATA
BEGIN
SELECT COUNT(*) FROM t_book WHERE booktypeid=b;
END;
$$
DELIMITER ;
Mysql>CALL pro_book(1);
大家可以把存储过程想像为封装好的类,然后调用的时候传入参数即可。
3.2 创建存储函数
CREATE FUNCTION sp_name ( [func_parameter[,...]] )
RETURNS type
[ characteristic... ] routine_body
sp_name 参数是存储函数的名称
func_parameter 表示存储函数的参数列表
RETURNS type 指定返回值的类型
characteristic 参数指定存储过程的特性，该参数的取值与存储过程中的取值是一样的；routine_body 参数是SQL 代码的内容，可以用BEGIN...END 来标志SQL 代码的开始和结束；
func_parameter 可以由多个参数组成，其中每个参数由参数名称和参数类型组成，其形式如下：
param_name type
其中，param_name 参数是存储函数的参数名称
type 参数指定存储函数的参数类型，该类型可以是MySQL 数据库的任意数据类型。
和存储过程参数不同的是,存储过程是:输入输出类型、参数名称和参数类型。
而存储函数参数是:参数名称和参数类型
例: mysql>DELIMITER $$
CREATE FUNCTION fun_book(bookId INT)
RETURNS VARCHAR(20)
BEGIN
RETURN(SELECT bookName FROM t_book WHERE id=bookId);
END;
$$
DELIMITER ;
Mysql>SELECT fun_book(1);
因为存储函数有返回值,所以用SELECT,不能用CALL.
四: 调用存储过程和函数
4.1 调用存储过程
CALL sp_name( [parameter[,...]] )
Mysql>CALL pro_book(1);
4.2 调用存储函数
fun_name( [parameter[,...]] )
Mysql>SELECT fun_book(1);
五: 查看存储过程和函数
5.1 SHOW STATUS 语句查看存储过程和函数的状态
SHOW { PROCEDURE | FUNCTION } STATUS [ LIKE ‘pattern’ ] ;
Mysql>
SHOW PROCEDURE STATUS LIKE 'pro_book';
5.2 SHOW CREATE 语句查看存储过程的函数的定义
SHOW CREATE { PROCEDURE | FUNCTION } sp_name ;
Mysql>SHOW CREATE FUNCTION fun_book;
5.3 从information_schema.Routines 表中查看存储过程和函数的信息
Mysql>USE information_scherma;
Mysql>SELECT * from Routines;
六: 修改存储过程和函数
ALTER { PROCEDURE | FUNCTION } sp_name [ characteristic ... ]
characteristic :
{ CONTAINS SQL } NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT ‘string’
其中，sp_name 参数表示存储过程或函数的名称,characteristic 参数指定函数的特性
CONTAINS SQL 表示子程序包含SQL 语句，但不包含读或写数据的语句
NO SQL 表示子程序中不包含SQL 语句
READS SQL DATA表示子程序中包含数据的语句
MODIFIES SQL DATA 表示子程序中包含写数据的语句
SQL SECURITY{ DEFINER | INVODER } 指明谁有权限来执行。
DEFINER 表示只有定义者自己才能够执行；
INVODER 表示调用者可以执行。
COMMENT ‘string’ 是注释信息。
例: mysql>ALTER PROCEDURE `pro_book` COMMENT '测试';
修改完后我们来查看一下是否修改成功
Mysql>SHOW PROCEDURE STATUS LIKE `pro_book`;
其实不怎么推荐用Alter来修改因为Alter只能修改其特性，不能修改代码。直接删除然后新建存储过程或者函数
七: 删除存储过程和函数
DROP {PROCEDURE | FUNCTION } sp_name ;
例: mysql>DROP PROCEDURE `pro_book`;
Mysql>DROP FUNCTION `fun_book`;
八: 存储过程和存储函数的详细区别
存储过程是用户定义的一系列sql语句的集合，涉及特定表或其它对象的任务，用户可以调用存储过程，而函数通常是数据库已定义的方法，它接收参数并返回某种类型的值并且不涉及特定用户表。
存储过程和函数存在以下几个区别：
1）一般来说，存储过程实现的功能要复杂一点，而函数的实现的功能针对性比较强。存储过程，功能强大，可以执行包括修改表等一系列数据库操作；用户定义函数不能用于执行一组修改全局数据库状态的操作。
2）对于存储过程来说可以返回参数，如记录集，而函数只能返回值或者表对象。函数只能返回一个变量；而存储过程可以返回多个。存储过程的参数可以有IN,OUT,INOUT三种类型，而函数只能有IN类~~存储过程声明时不需要返回类型，而函数声明时需要描述返回类型，且函数体中必须包含一个有效的RETURN语句。
3）存储过程，可以使用非确定函数，不允许在用户定义函数主体中内置非确定函数。
4）存储过程一般是作为一个独立的部分来执行（ CALL 语句执行），而函数可以作为查询语句的一个部分来调用（SELECT调用）。 SQL语句中不可用存储过程，而可以使用函数
九: 总结
今天为大家带来了有关存储过程的知识,大家自己消化练习下,下节为大家带来游标,变量,流程控制的相关知识,谢谢大家!