MySQL存储过程 函数
我的天才女友 人气:0储存过程和函数就是数据器将一些处理封装起来,调用
区别
调用储存过程只需要使用CALL,然后指定储存名称和参数,参数可以是IN、OUT和INOUT
调用储存函数只需要使用SELECT,指定名称和参数,储存函数必须有返回值,参数只能是IN
优点
- 良好的封装性
- 应用程序和SQL逻辑分离
- 让SQL也具有处理能力
- 减少网络交互
- 能够提高系统性能
- 降低数据出错的概率,保证数据的一致性和完整性
- 保证数据的安全性
创建储存函数和过程
储存过程
create procedure sp_name ([proc_parameter[,…]]) [characteristic …] routine_body
create procedure 创建储存过程关键字
sp_name 储存过程名称
proc_parameter 参数
characteristic 约束
routine_body 执行体,使用BEGIN— END包括
proc_parameter
IN | OUT | INOUT param_name type
characteristic
language SQL 执行体
[not] deterministic 得出的结果是否确定,不带NOT 出入相同,默认带NOT
constains SQL 不包含读写SQL , no sql 不包含sql语句, reads sql data 读数据的sql, modifies sql data 包含读sql语句, 默认contains sql
sql security definer 只有创建者菜能执行 invoker 表示有权限访问的就可执行
comment 注释
下面是创建一个储存过程的定义,在对应的工具中找到创建储存过程的地方。
create PROCEDURE SelectAllData() begin select * from t_goods; end
创建好的储存过程
储存函数
create function func_name (func_parameter[,…]) returns type [characteristic …] routine_body
create function 创建储存函数关键字
func_name 储存函数名字
func_parameter 参数,储存函数只能是IN
returns type 返回数据类型
characteristic 函数约束
routine_body SQL执行体
查看储存过程
show create procedure sp_name
show procedure status like ‘’
从数据库中information_schema中查询
操作
call 调用
drop 删除
alter 修改
变量
declare var_name[,…] type [default value]
declare 定义变量关键字
var_name 变量名称
type 类型
[default value] 默认值
declare totalprice decimal(10,2) default 0.00;
赋值
set 赋值
set totalprice = 399.99
查询赋值
select sum(t_price) into totalprice from t_goods
变量例子
创建一个储存过程使用变量的例子
CREATE DEFINER=`root`@`localhost` PROCEDURE `SelectCountAndPrice`() begin declare totalcount int default 0; declare totalprice, avgprice decimal(10, 2) default 0.00; select count(*) into totalcount from t_goods; select sum(t_price) totalprice from t_goods; set avgprice = totalprice / totalcount; select totalprice,totalcount,avgprice; end
定义条件和处理过程
条件
declare condition_name condition for condition_value
condition_name 条件名称
condition_value 条件类型
SQLSTATE [value] sqlstate_value | mysql_error_code
sqlstate_value 长度为5的错误信息
mysql_error_code 数值类型的错误代码
declare exec_refused condition for sqlstate ‘48000’;
处理程序
declare handler_type handler for condition_value[,…] sq_statement
handler_type 定义错误的处理方式
condition_value 错误类型
sq_statement 遇到错误,需要执行的储存过程或函数
handler_type
continue 继续处理
exit 退出
undo 撤回,目前不支持
condition_value
sqlstate [value] sqlstate_value
condition_name
sqlwarning
not found
sqlexception
mysql_error_code
- sqlstate [value] sqlstate_value 长度为5的字符串的错误信息
- condition_name 条件名称
- sqlwarning 所有以01开头的sqlstate错误代码
- not found 所有以02开头的sqlstate错误代码
- sqlexception 没有被sqlwarning和not found 捕捉的错误代码
- mysql_error_code 数值类型错误
declare continue handler for sqlstate ‘29011’ set @log=’ database not found ’
CREATE DEFINER=`root`@`localhost` PROCEDURE `InsertDataNoCondition`() BEGIN set @x = 1; insert into t_goodss (id,t_cate,t_remake,ages) values (3,'22','3333',10); set @x = 2; insert into t_goodss (id,t_cate,t_remake,ages) values (4,'22','3333',10); set @x = 3; END
游标
储存过程查询数据打,使用游标对结果集循环处理。
声明游标
declare cursor_name cursor for select_statement;
cursor_name 游标名称
select_statement 查询语句
打开游标
open cursor_name
使用游标
fetch cursor_name into var_name[,…]
关闭游标
close cursor_name
例子
定义一个StatisticsPrice的储存过程,参数是totalprice,定义cursor_price 游标,将查询的结果使用repeat 语句赋值于cursor_price,计算结果。
CREATE PROCEDURE StatisticsPrice(OUT totalprice DECIMAL(10, 2)) BEGIN #Routine body goes here... declare price decimal(10,2) DEFAULT 0.00; declare cursor_price cursor for select t_price from t_goods; declare exit HANDLER FOr not found close cursor_price; set totalprice = 0.00; open cursor_price; repeat FETCH cursor_price into price; set totalprice= totalprice + price; until 0 END repeat; close cursor_price; END
流程控制语句
if
loop (leave 退出当前流程,iterate 跳出本次循环)
while
case
加载全部内容