详解MySQL中的存储过程和函数

吾爱主题阅读：121 2024-04-01 23:21:05 评论：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 注释

下面是创建一个储存过程的定义，在对应的工具中找到创建储存过程的地方。

1 2 3 4 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] 默认值

1	`declare` `totalprice` `decimal` `(10,2)` `default` `0.00;`

赋值

set 赋值

set totalprice = 399.99

查询赋值

select sum(t_price) into totalprice from t_goods

变量例子

创建一个储存过程使用变量的例子

1 2 3 4 5 6 7 8 9 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 ’

1 2 3 4 5 6 7 8 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，计算结果。

1 2 3 4 5 6 7 8 9 10 11 12 13 14 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