什么是存储过程？
- 存储过程（procedure）类似于C语言中的函数
- 用来执行管理任务或应用复杂的业务规则
- 存储过程可以带参数，也可以返回结果
-
存储过程可以包含数据操纵语句、变量、逻辑 控制语句等
存储过程（
Stored Procedure
）是一组为了完成特定功能的
SQL
语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给定参数（如果该存储过程带有参数）来调用执行它。
外部程序可以直接调用数据库里面定义好的存储过程，另外数据库内部的触发器(trigger)、或者其他存储过程也可以调用它。
存储过程的优点
(1)执行速度快：存储过程创建是就已经通过语法检查和性能优化，在执行时无需每次编译。存储在数据库服务器，性能高。
(2)允许模块化设计：只需创建存储过程一次并将其存储在数据库中，以后即可在程序中调用该过程任意次。存储过程可由在数据库编程方面有专长的人员创建，并可独立于程序源代码而单独修改 。
(3)提高系统安全性：可将存储过程作为用户存取数据的管道。可以限制用户对数据表的存取权限，建立特定的存储过程供用户使用，完成对数据的访问。
存储过程的定义文本可以被加密，使用户不能查看其内容。
(4)减少网络流量：一个需要数百行Transact-SQL代码的操作由一条执行过程代码的单独语句就可实现，而不需要在网络中发送数百行代码。
存储过程的缺点
数据库
占用的系统资源加大(cpu、memory)，数据库毕竟主要用来做数据存取的，并不进行复杂的业务逻辑操作。
存储过程的分类
- 系统存储过程
- 由系统定义，存放在master数据库中
- 类似C语言中的系统函数
- 系统存储过程的名称都以“sp_”开头或”xp_”开头
- 用户自定义存储过程
- 由用户在自己的数据库中创建的存储过程
- 类似C语言中的用户自定义函数
关于
MySQL
的存储过程
存储过程是数据库存储的一个重要的功能，但是
MySQL
在
5.0
以前并不支持存储过程，这使得
MySQL
在应用上大打折扣。好在
MySQL 5.0
终于开始已经支持存储过程，这样即可以大大提高数据库的处理速度，同时也可以提高数据库编程的灵活性。
一、MySQL
存储过程的创建
通过MySQL提供的Query browser来创建
数据库
中创建一个简单的表名叫”products“的，里面包括下面这两个字段：
通过命令行创建
(1).
格式
MySQL
存储过程创建的格式：
CREATE PROCEDURE
过程名
([
过程参数
[,…]])
[
特性
…]
过程体
这里先举个例子：
mysql> DELIMITER // mysql> CREATE PROCEDURE proc1(OUT s int) -> BEGIN -> SELECT COUNT(*) INTO s FROM user; -> END -> // mysql> DELIMITER ;
注：（
1
）这里需要注意的是
DELIMITER //
和
DELIMITER ;
两句，
DELIMITER
是分割符的意思，因为
MySQL
默认以
“;”
为分隔符，如果我们没有声明分割符，那么编译器会把存储过程当成
SQL
语句进行处理，则存储过程的编译过程会报错，所以要事先用
DELIMITER
关键字申明当前段分隔符，这样
MySQL
才会将
“;”
当做存储过程中的代码，不会执行这些代码，用完了之后要把分隔符还原。
（
2
）存储过程根据需要可能会有输入、输出、输入输出参数，这里有一个输出参数
s
，类型是
int
型，如果有多个参数用
“,”
分割开。
（
3
）过程体的开始与结束使用
BEGIN
与
END
进行标识。
(2).
声明分割符
其实，关于声明分割符，上面的注解已经写得很清楚，不需要多说，只是稍微要注意一点的是：如果是用
MySQL
的
Administrator
管理工具时，可以直接创建，不再需要声明。
(3).
参数
MySQL
存储过程的参数用在存储过程的定义，共有三种参数类型
,IN,OUT,INOUT,
形式如：
CREATE PROCEDURE([[IN |OUT |INOUT ]
参数名
数据类形
…])
-
IN
输入参数
:
表示该参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值
-
OUT
输出参数
:
该值可在存储过程内部被改变，并可返回
-
INOUT
输入输出参数
:
调用时指定，并且可被改变和返回
Ⅰ
. IN
参数例子
创建
:
mysql > DELIMITER // mysql > CREATE PROCEDURE demo_in_parameter(IN p_in int) -> BEGIN -> SELECT p_in; -> SET p_in=2; -> SELECT p_in; -> END; -> // mysql > DELIMITER ;
执行结果
:
mysql > SET @p_in=1; mysql > CALL demo_in_parameter(@p_in); +------+ | p_in | +------+ | 1 | +------+ +------+ | p_in | +------+ | 2 | +------+ mysql> SELECT @p_in; +-------+ | @p_in | +-------+ | 1 | +-------+
以上可以看出，
p_in
虽然在存储过程中被修改，但并不影响
@p_id
的值
Ⅱ
.OUT
参数例子
创建
:
mysql > DELIMITER // mysql > CREATE PROCEDURE demo_out_parameter(OUT p_out int) -> BEGIN -> SELECT p_out; -> SET p_out=2; -> SELECT p_out; -> END; -> // mysql > DELIMITER ;
执行结果
:
mysql > SET @p_out=1; mysql > CALL sp_demo_out_parameter(@p_out); +-------+ | p_out | +-------+ | NULL | +-------+ +-------+ | p_out | +-------+ | 2 | +-------+ mysql> SELECT @p_out; +-------+ | p_out | +-------+ | 2 | +-------+
Ⅲ
. INOUT
参数例子
创建
:
mysql > DELIMITER // mysql > CREATE PROCEDURE demo_inout_parameter(INOUT p_inout int) -> BEGIN -> SELECT p_inout; -> SET p_inout=2; -> SELECT p_inout; -> END; -> // mysql > DELIMITER ;
执行结果
:
mysql > SET @p_inout=1; mysql > CALL demo_inout_parameter(@p_inout) ; +---------+ | p_inout | +---------+ | 1 | +---------+ +---------+ | p_inout | +---------+ | 2 | +---------+ mysql > SELECT @p_inout; +----------+ | @p_inout | +----------+ | 2 | +----------+
(4).
变量
Ⅰ
.
变量定义
DECLARE variable_name [,variable_name…] datatype [DEFAULT value];
其中，
datatype
为
MySQL
的数据类型，如
:int, float, date, varchar(length)
例如
:
DECLARE l_int int unsigned default 4000000; DECLARE l_numeric number(8,2) DEFAULT 9.95; DECLARE l_date date DEFAULT '1999-12-31'; DECLARE l_datetime datetime DEFAULT '1999-12-31 23:59:59'; DECLARE l_varchar varchar(255) DEFAULT 'This will not be padded'
Ⅱ
.
变量赋值
SET
变量名
=
表达式值
[,variable_name = expression …]
Ⅲ
.
用户变量
ⅰ
.
在
MySQL
客户端使用用户变量
mysql > SELECT 'Hello World' into @x; mysql > SELECT @x; +-------------+ | @x | +-------------+ | Hello World | +-------------+ mysql > SET @y='Goodbye Cruel World'; mysql > SELECT @y; +---------------------+ | @y | +---------------------+ | Goodbye Cruel World | +---------------------+ mysql > SET @z=1+2+3; mysql > SELECT @z; +------+ | @z | +------+ | 6 | +------+
ⅱ.
在存储过程中使用用户变量
mysql > CREATE PROCEDURE GreetWorld( ) SELECT CONCAT(@greeting,' World'); mysql > SET @greeting='Hello'; mysql > CALL GreetWorld( ); +----------------------------+ | CONCAT(@greeting,' World') | +----------------------------+ | Hello World | +----------------------------+
ⅲ
.
在存储过程间传递全局范围的用户变量
mysql> CREATE PROCEDURE p1() SET @last_procedure='p1'; mysql> CREATE PROCEDURE p2() SELECT CONCAT('Last procedure was ',@last_proc); mysql> CALL p1( ); mysql> CALL p2( ); +-----------------------------------------------+ | CONCAT('Last procedure was ',@last_proc | +-----------------------------------------------+ | Last procedure was p1 | +-----------------------------------------------+
注意
:
①
用户变量名一般以
@
开头
②
滥用用户变量会导致程序难以理解及管理
(5).
注释
MySQL
存储过程可使用两种风格的注释
双模杠：
—
该风格一般用于单行注释
c
风格：
一般用于多行注释
例如：
mysql > DELIMITER // mysql > CREATE PROCEDURE proc1 --name存储过程名 -> (IN parameter1 INTEGER) -> BEGIN -> DECLARE variable1 CHAR(10); -> IF parameter1 = 17 THEN -> SET variable1 = 'birds'; -> ELSE -> SET variable1 = 'beasts'; -> END IF; -> INSERT INTO table1 VALUES (variable1); -> END -> // mysql > DELIMITER ;
三.
MySQL
存储过程的调用
用
call
和你过程名以及一个括号，括号里面根据需要，加入参数，参数包括输入参数、输出参数、输入输出参数。具体的调用方法可以参看上面的例子。
四.
MySQL
存储过程的查询
我们像知道一个数据库下面有那些表，我们一般采用
show tables;
进行查看。那么我们要查看某个数据库下面的存储过程，是否也可以采用呢？答案是，我们可以查看某个数据库下面的存储过程，但是是令一钟方式。
我们可以用
select name from mysql.proc where db=’
数据库名
’
或者
select routine_name from information_schema.routines where routine_schema=’
数据库名
‘;
或者
show procedure status where db=’
数据库名
‘;
进行查询。
如果我们想知道，某个存储过程的详细，那我们又该怎么做呢？是不是也可以像操作表一样用
describe
表名进行查看呢？
答案是：我们可以查看存储过程的详细，但是需要用另一种方法：
SHOW CREATE PROCEDURE
数据库
.
存储过程名,
就可以查看当前存储过程的详细。
五.
MySQL
存储过程的修改
ALTER PROCEDURE
更改用
CREATE PROCEDURE
建立的预先指定的存储过程，其不会影响相关存储过程或存储功能。
六.
MySQL
存储过程的删除
删除一个存储过程比较简单，和删除表一样：
DROP PROCEDURE
从
MySQL
的表格中删除一个或多个存储过程。
七
MySQL
存储过程的控制语句
(1).
变量作用域
内部的变量在其作用域范围内享有更高的优先权，当执行到
end
。变量时，内部变量消失，此时已经在其作用域外，变量不再可见了，应为在存储
过程外再也不能找到这个申明的变量，但是你可以通过
out
参数或者将其值指派
给会话变量来保存其值。
mysql > DELIMITER // mysql > CREATE PROCEDURE proc3() -> begin -> declare x1 varchar(5) default 'outer'; -> begin -> declare x1 varchar(5) default 'inner'; -> select x1; -> end; -> select x1; -> end; -> // mysql > DELIMITER ;
(2).
条件语句
Ⅰ
. if-then -else
语句
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
|
Ⅱ
. case
语句：
mysql > DELIMITER // mysql > CREATE PROCEDURE proc3 (in parameter int) -> begin -> declare var int; -> set var=parameter+1; -> case var -> when 0 then -> insert into t values(17); -> when 1 then -> insert into t values(18); -> else -> insert into t values(19); -> end case; -> end; -> // mysql > DELIMITER ;
(3).
循环语句
Ⅰ
. while ···· end while
：
mysql > DELIMITER // mysql > CREATE PROCEDURE proc4() -> begin -> declare var int; -> set var=0; -> while var<6 do -> insert into t values(var); -> set var=var+1; -> end while; -> end; -> // mysql > DELIMITER ;
Ⅱ
. repeat···· end repeat
：
它在执行操作后检查结果，而
while
则是执行前进行检查。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
|
Ⅲ
. loop ·····end loop:
loop
循环不需要初始条件，这点和
while
循环相似，同时和
repeat
循环一样不需要结束条件
, leave
语句的意义是离开循环。
mysql > DELIMITER // mysql > CREATE PROCEDURE proc6 () -> begin -> declare v int; -> set v=0; -> LOOP_LABLE:loop -> insert into t values(v); -> set v=v+1; -> if v >=5 then -> leave LOOP_LABLE; -> end if; -> end loop; -> end; -> // mysql > DELIMITER ;
Ⅳ
. LABLES
标号：
标号可以用在
begin repeat while
或者
loop
语句前，语句标号只能在合法的语句前面使用。可以跳出循环，使运行指令达到复合语句的最后一步。
(4). ITERATE
迭代
Ⅰ
. ITERATE:
通过引用复合语句的标号
,
来从新开始复合语句
mysql > DELIMITER // mysql > CREATE PROCEDURE proc10 () -> begin -> declare v int; -> set v=0; -> LOOP_LABLE:loop -> if v=3 then -> set v=v+1; -> ITERATE LOOP_LABLE; -> end if; -> insert into t values(v); -> set v=v+1; -> if v>=5 then -> leave LOOP_LABLE; -> end if; -> end loop; -> end; -> // mysql > DELIMITER ;
八.
MySQL
存储过程的基本函数
(1).
字符串类
CHARSET(str) //
返回字串字符集
CONCAT (string2 [,… ]) //
连接字串
INSTR (string ,substring ) //
返回
substring
首次在
string
中出现的位置
,
不存在返回
0
LCASE (string2 ) //
转换成小写
LEFT (string2 ,length ) //
从
string2
中的左边起取
length
个字符
LENGTH (string ) //string
长度
LOAD_FILE (file_name ) //
从文件读取内容
LOCATE (substring , string [,start_position ] )
同
INSTR,
但可指定开始位置
LPAD (string2 ,length ,pad ) //
重复用
pad
加在
string
开头
,
直到字串长度为
length
LTRIM (string2 ) //
去除前端空格
REPEAT (string2 ,count ) //
重复
count
次
REPLACE (str ,search_str ,replace_str ) //
在
str
中用
replace_str
替换
search_str
RPAD (string2 ,length ,pad) //
在
str
后用
pad
补充
,
直到长度为
length
RTRIM (string2 ) //
去除后端空格
STRCMP (string1 ,string2 ) //
逐字符比较两字串大小
,
SUBSTRING (str , position [,length ]) //
从
str
的
position
开始
,
取
length
个字符
,
注：
mysql
中处理字符串时，默认第一个字符下标为
1
，即参数
position
必须大于等于
1
mysql> select substring('abcd',0,2); +-----------------------+ | substring('abcd',0,2) | +-----------------------+ | | +-----------------------+ 1 row in set (0.00 sec) mysql> select substring('abcd',1,2); +-----------------------+ | substring('abcd',1,2) | +-----------------------+ | ab | +-----------------------+ 1 row in set (0.02 sec)
TRIM([[BOTH|LEADING|TRAILING] [padding] FROM]string2) //
去除指定位置的指定字符
UCASE (string2 ) //
转换成大写
RIGHT(string2,length) //
取
string2
最后
length
个字符
SPACE(count) //
生成
count
个空格
(2).
数学类
ABS (number2 ) //
绝对值
BIN (decimal_number ) //
十进制转二进制
CEILING (number2 ) //
向上取整
CONV(number2,from_base,to_base) //
进制转换
FLOOR (number2 ) //
向下取整
FORMAT (number,decimal_places ) //
保留小数位数
HEX (DecimalNumber ) //
转十六进制
注：
HEX()
中可传入字符串，则返回其
ASC
-11
码
，如
HEX(‘DEF’)
返回
4142143
也可以传入十进制整数，返回其十六进制编码，如
HEX(25)
返回
19
LEAST (number , number2 [,..]) //
求最小值
MOD (numerator ,denominator ) //
求余
POWER (number ,power ) //
求指数
RAND([seed]) //
随机数
ROUND (number [,decimals ]) //
四舍五入
,decimals
为小数位数
]
注：返回类型并非均为整数，如：
(1)
默认变为整形值
mysql> select round(1.23); +-------------+ | round(1.23) | +-------------+ | 1 | +-------------+ 1 row in set (0.00 sec) mysql> select round(1.56); +-------------+ | round(1.56) | +-------------+ | 2 | +-------------+ 1 row in set (0.00 sec)
(2)
可以设定小数位数，返回浮点型数据
mysql> select round(1.567,2); +----------------+ | round(1.567,2) | +----------------+ | 1.57 | +----------------+ 1 row in set (0.00 sec)
SIGN (number2 ) //
(3).
日期时间类
ADDTIME (date2 ,time_interval ) //
将
time_interval
加到
date2
CONVERT_TZ (datetime2 ,fromTZ ,toTZ ) //
转换时区
CURRENT_DATE ( ) //
当前日期
CURRENT_TIME ( ) //
当前时间
CURRENT_TIMESTAMP ( ) //
当前时间戳
DATE (datetime ) //
返回
datetime
的日期部分
DATE_ADD (date2 , INTERVAL d_value d_type ) //
在
date2
中加上日期或时间
DATE_FORMAT (datetime ,FormatCodes ) //
使用
formatcodes
格式显示
datetime
DATE_SUB (date2 , INTERVAL d_value d_type ) //
在
date2
上减去一个时间
DATEDIFF (date1 ,date2 ) //
两个日期差
DAY (date ) //
返回日期的天
DAYNAME (date ) //
英文星期
DAYOFWEEK (date ) //
星期
(1-7) ,1
为星期天
DAYOFYEAR (date ) //
一年中的第几天
EXTRACT (interval_name FROM date ) //
从
date
中提取日期的指定部分
MAKEDATE (year ,day ) //
给出年及年中的第几天
,
生成日期串
MAKETIME (hour ,minute ,second ) //
生成时间串
MONTHNAME (date ) //
英文月份名
NOW ( ) //
当前时间
SEC_TO_TIME (seconds ) //
秒数转成时间
STR_TO_DATE (string ,format ) //
字串转成时间
,
以
format
格式显示
TIMEDIFF (datetime1 ,datetime2 ) //
两个时间差
TIME_TO_SEC (time ) //
时间转秒数
]
WEEK (date_time [,start_of_week ]) //
第几周
YEAR (datetime ) //
年份
DAYOFMONTH(datetime) //
月的第几天
HOUR(datetime) //
小时
LAST_DAY(date) //date
的月的最后日期
MICROSECOND(datetime) //
微秒
MONTH(datetime) //
月
MINUTE(datetime) //
分
返回符号
,
正负或
0
SQRT(number2) //
开平方
-
参考：http://blog.sina.com.cn/s/blog_52d20fbf0100ofd5.html
-
http://www.2cto.com/database/201208/148791.html
-
资料：http://tech.ccidnet.com/zt/guocheng/
-
http://tech.ccidnet.com/zt/guocheng/
-
http://www.cnblogs.com/hoojo/archive/2011/07/19/2110862.html