1 存储过程简介
存储过程是一段代码,由存储在一个数据库的目录中、声明式的和过程式的sql语句组成,可以从一个程序、触发器或者另一个存储过程调用它从而激活它。存储过程(Stored Procedure)是一组为了完成特定功能的SQL语句集,经编译后存储在数据库中,用户通过指定存储过程的名字并给定参数(如果该存储过程带有参数)来调用执行它。
一个存储过程是一个可编程的函数,它在数据库中创建并保存。它可以有SQL语句和一些特殊的控制结构组成。当希望在不同的应用程序或平台上执行相同的函数,或者封装特定功能时,存储过程是非常有用的。数据库中的存储过程可以看做是对编程中面向对象方法的模拟。它允许控制数据的访问方式。
2 存储过程优缺点
2.1 优点
2.1.1 执行速度更快。有两个原因:首先,在存储过程创建的时候,数据库已经对其进行了一次解析和优化,它只在创造时进行编译,以后每次执行存储过程都不需再重新编译,而一般 SQL 语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。其次,存储过程一旦执行,在内存中就会保留一份这个存储过程,这样下次再执行同样的存储过程时,可以从内存中直接调用。
2.1.2 减少网络通信量。调用一个SQL语句不多的存储过程与直接调用SQL语句的网络通信量可能不会有很大的差别,但如果存储过程包含较多SQL语句,那么其性能绝对比一条一条的调用SQL语句要高得多。
2.1.3 安全性高。可设定只有某此用户才具有对指定存储过程的使用权。
2.1.4 可维护性高。更新存储过程通常比更改、测试以及重新部署程序集需要较少的时间和精力。
2.1.5 不依赖某种宿主语言。如果用多种语言开发,某些通用代码不用重复。
2.1.6 布式工作。应用程序和数据库的编码工作可以分别独立进行,而不会相互压制。
2.1.7 存储过程可以重复使用,可减少数据库开发人员的工作量。
2.2 缺点
2.2.1 SQL本身是一种结构化查询语言,加上了一些控制(赋值、循环和异常处理等),本质上还是过程化的,面对复杂的业务逻辑,过程化的处理会很吃力。这一点算致命伤。
2.2.2 不便于调试。基本上没有较好的调试器,很多时候是用print来调试,但用这种方法调试长达数百行的存储过程简直是噩梦。
2.2.3 无法适应数据库的切割(水平或垂直切割)。数据库切割之后,存储过程并不清楚数据存储在哪个数据库中。
2.2.4 精通SQL的新手越来越少。
3 创建存储过程
3.1 创建语法如下
CREATE PROCEDURE存储过程名 (参数列表) BEGIN SQL语句代码块 END3.2 例如:计算两个数之和
delimiter // create procedure procedureAdd (a int, IN b int) begin declare c int; if a is null then set a = 0; end if; if b is null then set b = 0; end if; set c = a + b; select c as sum; end; // delimiter ;3.3 注意:
由括号包围的参数列必须总是存在。如果没有参数,也该使用一个空参数列()。每个参数默认都是一个IN参数,要指定为其它参数,可在参数名之前使用关键词 OUT或INOUT
在mysql客户端定义存储过程的时候使用delimiter命令来把语句定界符从;变为//,创建完之后在将分隔符更改为;号。
4 查看存储过程
查看数据库中所有存储的存储过程基本信息,包括所属数据库,存储过程名称,创建时间等。
show procedure status \G
或者
select name from mysql.proc where db = 'your_db_name' and 'type' = 'PROCEDURE';
查看某个已存在的存储过程的详细信息(其中procedureAdd为以创建的存储过程名)
show create procedure procedureAdd \G
5 调用存储过程
5.1 基本语法
call 存储过程名(参数列表)
5.2 例如call procedureAdd(10, 30);
或者
set @a = 10; set @b = 30; call procedureAdd(@a, @b);注意:在使用SET定义变量时应遵循SET的语法规则 SET @变量名=初始值;
6 修改存储过程
6.1 基本语法
ALTER PROCEDURE 存储过程名 [characteristic ...]注意:characteristic是存储过程创建时的特征,在CREATE PROCEDURE语句中已经介绍过。只要设定了其中的值,存储过程的特征就随之变化。
如果要修改存储过程的内容,可以使用先删除再重新定义存储过程的方法。 存储过程某些的特征如下
characteristic:
COMMENT 'string'
| LANGUAGE SQL
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
6.2 例如:修改特征
查看默认特征值
mysql> SELECT SPECIFIC_NAME,SQL_DATA_ACCESS, ROUTINE_COMMENT FROM information_schema.Routines WHERE ROUTINE_NAME='procedureAdd'; +---------------+-----------------+-----------------+ | SPECIFIC_NAME | SQL_DATA_ACCESS | ROUTINE_COMMENT | +---------------+-----------------+-----------------+ | procedureAdd | CONTAINS SQL | | +---------------+-----------------+-----------------+将读写权限改为READS SQL DATA,并加上注释信息'This is a test!',代码执行如下:
mysql> ALTER PROCEDURE procedureAdd READS SQL DATA COMMENT 'This is a test!';查看修改后的特征值
mysql> SELECT SPECIFIC_NAME,SQL_DATA_ACCESS, ROUTINE_COMMENT FROM information_schema.Routines WHERE ROUTINE_NAME='procedureAdd'; +---------------+-----------------+-----------------+ | SPECIFIC_NAME | SQL_DATA_ACCESS | ROUTINE_COMMENT | +---------------+-----------------+-----------------+ | procedureAdd | READS SQL DATA | This is a test! | +---------------+-----------------+-----------------+说明:从查询的结果可以看出,访问数据的权限(SQL_DATA_ACCESS)已经变成READS SQL DATA,函数注释(ROUTINE_COMMENT)已经变成了"This is a test!"。
7 删除存储过程
7.1 基本语法
DROP PROCEDURE IF EXISTS 存储过程名7.2 例如
DROP PROCEDURE IF EXISTS procedureAdd;
注意:不能在一个存储过程中删除另一个存储过程,只能调用另一个存储过程。
*
8 存储过程局部变量
8.1 介绍
在一个存储过程内部,可以声明局部变量。他们可以用来存储中间临时结果。如果我们在一个存储过程中需要一个局部变量,必须使用DECLARE VARIABLE语句引入它。通过声明,就确定了变量的数据类型,并且也可以指定初始值。(如果使用了DECLARE VARIABLE语句,他们必须作为BEGIN-END语句块的第一条语句包含其中)
8.2 例如:
delimiter // create procedure test(out num1 integer) begin declare num2 integer default 100; set num1 = num2; end // delimiter ;调用存储过程
call test(@num); select @num;9 存储过程和用户变量
9.1 介绍
用户变量总有一个全局特性,即便它在一个存储过程内部创建,在存储过程结束后他们依然保留。在存储过程之外创建的用户变量,仍然可以在存储过程中保留他们自己的值。
9.2 例如
delimiter // create procedure user_variable() begin set @varTest = 1; end // delimiter ;调用存储过程后查看varTest值为1
call user_variable(); select @varTest;说明:set语句是sql本身的一部分,它可以讲一个值赋给用户变量和局部变量,也可使用任何随机表达式。
10 存储过程与游标
10.1 介绍
常规的select语句可能返回多行,使用游标(cursor)可以处理这一点,把数据一行一行的取入到存储过程中。使用游标需要用到四个特殊语句:declare sursor,open sursor,fetch cursor,和close cursor。
如果使用declare cursor语句声明一个游标,我们就把它连接到了一个表表达式。接下来就可以使用fetch cursor语句来把产生的结果一行一行的获取到存储过程中。在某个时刻,结果中只有一行可见,也就是当前行。它就好像是指向结果中一行的一个箭头,这也是游标这个名字的来历。使用fetch cursor这条语句,我们可以把游标移动到下一行,当处理完所有的行,可以使用close cursor语句来删除结果。
10.2 游标作用及属性
作用:
就是用于对查询数据库所返回的记录进行遍历,以便进行相应的操作;
属性:
游标是只读的,也就是不能更新它;
游标是不能滚动的,也就是只能在一个方向上进行遍历,不能在记录之间随意进退,不能跳过某些记录;
避免在已经打开游标的表上更新数据。
10.3 如何使用游标
声明游标
DECLARE cursor_name CURSOR FOR SELECT语句;
打开游标
OPEN cursor_name;
移动游标
FETCH cursor_name INTO variable list;
关闭游标
CLOSE cursor_name;
10.4 游标实例
创建测试表及数据
CREATE TABLE test.users ( ID bigint(20) unsigned NOT NULL AUTO_INCREMENT, user_name varchar(60) NOT NULL DEFAULT '', user_pass varchar(64) NOT NULL DEFAULT '', PRIMARY KEY (ID) )ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
insert into test.users values(1,'name1', 'pass1'); insert into test.users values(2,'name2', 'pass2'); insert into test.users values(3,'name3', 'pass3'); insert into test.users values(4,'name4', 'pass4'); insert into test.users values(5,'name5', 'pass5');
创建游标存储过程
delimiter | create procedure test_cursor (in param int(10),out result varchar(90)) begin declare name varchar(20); declare pass varchar(20); declare done int; declare cur_test CURSOR for select user_name,user_pass from test.users; declare continue handler FOR SQLSTATE '02000' SET done = 1; if param then select concat_ws(',',user_name,user_pass) into result from test.users where id=param; else open cur_test; repeat fetch cur_test into name, pass; select concat_ws(',',result,name,pass) into result; until done end repeat; close cur_test; end if; end;| delimiter ;
各行命令详解
1行,告诉MySQL解释器,输入结束命令改为|,默认为;(命令本身与存储过程无关) 2行,创建一个存储过程,注意:如果我把out result varchar(90)改成out result varchar,返回的结果中只有一个字符。 3行,开始 4行,定义一个变量name 5行,定义变量pass 6行,定义一个结束标识 7行,定义一个光标,指向select user_name,user_pass from test.users;语句 8行,如果sqlstate等于02000时,把done设置成1,也就是找不到数据时 9,11,18行,if判断 10行,根据参数,把数据取出来,放到result中,concat_ws函数表示concat with separator,即有分隔符的字符串连接,如连接后以逗号分隔 12行,打开光标 13,16行,repeat循环,根php的do while原理一样 14行,从光标中取出数据。 15行,将数据合并起来 17行,关闭光标 18,19行,标签闭合。 20行,
结果反馈
mysql> call test_cursor(3,@test); Query OK, 1 row affected (0.00 sec) mysql> select @test; +-------------+ | @test | +-------------+ | name3,pass3 | +-------------+ 1 row in set (0.00 sec) mysql> call test_cursor('',@test); Query OK, 1 row affected, 2 warnings (0.00 sec) mysql> select @test; +-------------------------------------------------------------------------+ | @test | +-------------------------------------------------------------------------+ | name1,pass1,name2,pass2,name3,pass3,name4,pass4,name5,pass5,name5,pass5 | +-------------------------------------------------------------------------+ 1 row in set (0.00 sec)
转载请注明出处:http://blog.csdn.net/jesseyoung/article/details/34420839