Mysql之存储过程与存储函数_mysql中存储函数-优快云博客

一、概述

存储过程和存储函数都是Mysql5.0版本后开始的过程式数据库对象，作为数据库存储的重要功能，可以有效提高数据库的处理速度、数据库编程的灵活性。

二、存储过程

存储过程是一组为了完成某特定功能的sql语句集，这组语句集经编译后存储在数据库中，用户只需通过指定存储过程的名字并给定参数（如果该存储过程带有参数），即可随时调用并执行它，而不必通过重新编译。当希望在不同的应用程序或平台上执行相同的函数，或者封装特定功能时，存储过程是非常有用的。
示例一：
创建一个存储过程，用于实现给定表customers中一个cust_id即可修改表customers中该客户的性别为一个指定性别：

delimiter $$
create procedure sp_update_sex(in cid int ,in csex char(1))//传入参数需要用in来说明，传出参数用out来说明
begin
	update customers set cust_sex=csex where cust_id=cid;
end $$

在mysql命令行客户端输入如下sql语句对存储过程sp_update_sex进行调用：

call sp_update_sex(1,'M');//通过call关键字进行调用，并传入对应的参数

示例二：
在存储过程体中（begin…end）可以使用各种sql语句与过程式语句的组合，来封装数据库应用中复杂的业务逻辑和处理规则，以实现数据库应用的灵活编程。
首先创建一张测试表：

create table customers
(
cust_id int not null auto_increment,
cust_name char(50) not null,
cust_sex char(1) not null default 'M',
cust_address char(50) null,
cust_contact char(50) null,
primary key(cust_id)
)engine = innodb;

插入一些测试数据：

insert customers values(1,’张三’,’F’,’北京市’,’朝阳区’);
insert customers values(2,’李小明渣渣’,’M’,’广东省’,’广州市’);

创建存储过程sp_sumofrow，用于计算表customers中数据行的行数：

delimiter $$--修改结束符
create procedure sp_sumofrow(out rows int)
begin

declare cid int;--声明局部变量
declare found boolean default true;

declare cur_cid cursor for--声明游标，局部变量要在游标之前定义，句柄要在游标之后定义，否则系统会出现错误信息。即局部变量-游标-句柄
select cust_id from customers;

declare continue handler for not found--声明句柄，条件处理
set found=false;

set rows=0;
open cur_cid;--打开游标
fetch cur_cid into cid ;--读取数据

while found do--循环语句
set rows=rows+1;
fetch cur_cid into cid;
end while;

close cur_cid;--关闭游标
end $$

在mysql命令行客户端输入如下sql语句对存储过程sp_sumofrow进行调用：

call sp_sumofrow(@rows);

查看调用存储过程sp_sumofrow后的结果：

select @rows;--注意@与rows之间不能有空格

删除存储过程：

drop procedure sp_update_sex;

三、存储函数

存储函数是与存储过程十分相似的过程式数据库对象，与存储过程的区别主要如下：
1、不需要使用call语句，即可直接对存储函数进行调用
2、存储函数必须包含一条return语句，有且只有一个返回值，而存储过程不能有返回值。
3、函数只能有输入参数（不能有输出参数），而且输入参数不能带in, 而存储过程可以有多个in,out,inout参数。
4、存储过程可以调用存储函数。但函数不能调用存储过程。
5、存储过程一般是作为一个独立的部分来执行(call调用)。而函数可以作为查询语句的一个部分来调用。

创建存储函数：

delimiter $$
create function fn_search(cid int)
    returns char(2)
    deterministic    //[dɪˌtɜ:mɪ'nɪstɪk]确定性的

begin
    declare sex char(2);
    select cust_sex into sex from customers
        where cust_id=cid;
    if sex is null then
        return(select'没有该客户');
    else if sex='F' then
        return(select '女');
        else return(select '男');
        end if;
    end if;
end $$

调用存储函数：

select fn_search(2);

如果想查看数据库中存在哪些存储函数，可以使用：

show function status;

如果想要查看数据库中某个具体的存储函数的信息，可以使用：

show create function fn_search;--fn_search是我们刚刚创建的存储函数的名称

删除存储函数：

drop function if exists fn_search;