小胖之家

http://blog.sina.com.cn/jixp1984 [订阅][手机订阅]

首页 博文目录 图片关于我

个人资料

小胖之家

博客等级：
博客积分：852

博客访问：43,240
关注人气：18

MySql存储例程、存储过程进阶学习

(2009-07-27 18:05:36)

转载▼

什么是存储例程?
存储例程是存储在数据库服务器中的一组sql语句，通过在查询中调用一个指定的名称来执行这些sql语句命令。
为什么要使用存储过程？
我们都知道应用程序分为两种，一种是基于web，一种是基于桌面，他们都和数据库进行交互来完成数据的存取工作。假设现在有一种应用程序包含了这两种，现在要修改其中的一个查询sql语句，那么我们可能要同时修改他们中对应的查询sql语句，当我们的应用程序很庞大很复杂的时候问题就出现这，不易维护！另外把sql查询语句放在我们的web程序或桌面中很容易遭到sql注入的破坏。而存储例程正好可以帮我们解决这些问题。
存储过程(stored procedure)、存储例程(store routine)、存储函数区别
Mysql存储例程实际包含了存储过程和存储函数，它们被统称为存储例程。
其中存储过程主要完成在获取记录或插入记录或更新记录或删除记录，即完成select insert delete update等的工作。而存储函数只完成查询的工作，可接受输入参数并返回一个结果。

创建存储过程、存储函数

create procedure 存储过程名(参数)
存储过程体

create function 存储函数名(参数)
存储函数体
假设现在有一个数据库omcmc中的表db_info 表结构如下：
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for db_news
-- ----------------------------
DROP TABLE IF EXISTS `db_news`;
CREATE TABLE `db_news` (
`id` int(10) NOT NULL auto_increment,
`title` varchar(200) NOT NULL,
`editor` varchar(20) default NULL,
`origin` varchar(20) default NULL,
`tags` varchar(200) default NULL,
`content` text NOT NULL,
`hits` int(10) default '0',
`ip` varchar(15) NOT NULL,
`time` int(10) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records
-- ----------------------------
INSERT INTO `db_news` VALUES ('1', First App', 'xqbar.com', 'xqbar.com', 'omcmc', 'detail。。', '100', '127.0.0.1', '1215051225');

使用上面的表我们创建一个简单的存储过程
create procedure select_news()
select title,hits from db_news;

在终端运行并调用

从上面的截图我们看到如何调用我们的存储过程

调用存储过程
call 存储过程名();

以上我们创建了一个简单的存储过程，当然我们的应用程序不可能使用这么简单的存储过程，我们需要的是能给存储过程传递参数，以返回给我们所需要的结果数据。下面就了解下存储过程的参数。
存储过程的参数
通常存储过程接受用户的参数，返回结果给调用用户。
mysql规定对于存储过程的参数要求其每个参数都必须声明其参数名，数据类型以及该参数是输入参数还是用于返回信息还是两者兼有，对于存储函数php只支持输入参数。
声明参数时规定要使用关键字IN,OUT,INOUT。
其中：
IN：用于输入参数
OUT：用于返回参数
INOUT：用于向存储过程传递参数值，如果该值改变则返回
另外规定对于声名为OUT，INOUT的参数当我们调用存储过程时需要在参数名前加@,以确保参数在过程外调用，下面我们修改上面的存储过程以传递信息编号给存储过程select_news,返回对应的信息标题给我们查看。

drop procedure if exists select_news;
create procedure select_news(IN id int,OUT title varchar(200))
select db_news.title from db_news where db_news.id=id;

在终端运行并调用

注意调用格式
call select_news(1,@title);
其中1是我们要传入的信息编号，title是要返回的对应信息编号标题，由于是OUT所以再调用时要在其前加@在过程外调用。（见下例）
drop procedure if exists getNum;
create procedure getNum(OUT num int)
select 100 into num;

call getNum(@num);
select @num;
截图：

php存储例程、存储过程进阶学习之二
接着上篇文章，再调用有返回值时上个例子我们可以使用以下的方法调用存储过程：
select @返回参数;
我们现在使用这个方法来调用上面我们创建的select_news,来看看

有人会疑问为什么返回的title是null,而不是像call select_news(1,@title)一样返回的是我们数据库编号对应的数据First App,这是由于我们的存储过程体并没有给title返回参数返回值。

下面我们来看看存储函数
仍然以db_news表为例说明，不过我们在这个存储函数中我们加入其他一些关于存储例程的知识来引入我们这次要谈及的学习对象。

drop function if exists count_news;
delimiter //
create function count_news(hits int) returns int
comment '根据传入的点击次数统计超过此点击数的信息数目'
begin
   declare total_news int;
   declare hits_num int default 0;
   if hits>=0 then
      set hits_num=hits;
      select count(id) into total_news from db_news where db_news.hits>hits_num;
else
     set total_news=0;
   end if;
   return total_news;
end;
//
delimiter ;

在上面的存储函数中我们使用到了存储例程变量的声明、设置其值、判断语句、存储函数特有的返回值类型及存储例程如何描述(comment).

与存储过程返回参数不同的是存储函数在定义时没用直接声明哪个变量是返回参数，而只是使用了returns声明了返回参数所属的数据类型，返回参数是在函数体中使用return返回要返回的数据变量的形式来表示的。这就需要注意的是：
存储函数只支持输入参数，并且输入参数前没有IN或INOUT.

返回上面的存储函数，我们来解释下代码含义：
drop function if exists count_news;
如果存在存储函数count_news则删除该存储函数，与存储过程一样我们可以使用下面的语句删除存储过程或函数
drop procedure|function [if exists] 存储过程名|存储函数名;
delimiter //
使用delimiter更改mysql默认使用分号(;)使用新的结束符号来结束当前语句，使用delimiter后原先默认的分号(;)结束语句符号不再起作用，直到重新恢复声明结束符后。
create function count_news(hits int) returns int
注意存储函数的参数只有输入参数并且前不再声明IN或INOUT，返回只需声明要返回的数据类型
comment '根据传入的点击次数统计超过此点击数的信息数目'
使用comment 来描述该存储过程或存储函数的功能信息。使用格式为 comment '描述字符串 '
begin
   使用begin限定一个处理模块
declare total_news int;
声明变量格式 declare 变量名数据类型 [default 默认值]
   declare hits_num int default 0;
声明变量格式 declare 变量名数据类型 [default 默认值]
   if hits>=0 then
      set hits_num=hits;
      使用set 给变量赋值
select count(id) into total_news from db_news where db_news.hits>hits_num;
sql语句可以使用into给变量赋值
   else
     set total_news=0;
   end if;
   上面是一个判断语句，注意格式为if-else-end if 或者 if -else if-else-end if
注意分号(;)
   return total_news;
   使用return 返回存储函数要返回的值，注意该值只能是一个值。
end;
//
delimiter ;
综合一下我们上面的概念知识：
1.    存储函数的参数与存储过程有何不同。
2.    在存储例程中如何更改mysql的默认结束符号。
3.    在存储例程中如何描述功能信息。
4.    声明、设置变量。
5.    begin-end语句块。
6.    条件判断if-else if-else-end if 或if-else-end if 或者if-end if