存储过程和存储函数

存储过程

一:介绍

1.存储过程是事先经过编译并1存储在数据库中的一段SQL语句的集合，调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对提高数据处理的效率是有好处的。

存储过程思想上很简单，就是数据库SQL语言层面的代码封装与重用。

2.特点

①封装，重用

②可以接受参数，也可以返回数据

③减少网络交互，效率提升

二:基本语法

创建

CREATE PROCEDURE 存储过程名称([参数列表])

BEGIN

--SQL语句

END;

调用

CALL 名称([参数]);

查看

SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA ='xxx'; --查询指定数据库的存储过程及状态信息

SHOW CREATE PROCEDURE 存储过程名称; --查询某个存储过程的定义

删除

DROP PROCEDURE [IF EXISTS] 存储过程名称;

注意：在命令行中，执行创建存储过程的SQL时，需要通过关键字delimiter指定SQL语句的结束符。

-- 创建存储过程p1
create procedure p1()
begin

    select * from tb_user;

end;
-- 调用存储过程p1
call p1();
-- 查看itcast数据库中的存储过程及状态信息
select * from information_schema.ROUTINES where ROUTINE_SCHEMA = 'itcast';
-- 查询p1存储过程的定义
show create  procedure p1;
-- 删除p1存储过程
drop procedure if exists p1;

三:系统变量

系统变量是MySQL服务器提供的，不是用户定义的，属于服务器层面。分为全局变量（GLOBAL）、会话变量（SESSION）。

查看系统变量

SHOW [SESSION | GLOBAL] VARIABLES; --查看所有系统变量

SHOW [SESSION | GLOBAL] VARIBLES LIKE '……'; -- 可以通过LIKE模糊匹配方式查找变量

SELECT @@[SESSION | GLOBAL] 系统变量名; -- 查看指定变量的值

设置系统变量

SET [SESSION | GLOBAL] 系统变量名 = 值;

SET @@[SESSION | GLOBAL] 系统变量名 = 值;

-- 查看所有系统变量
show variables;
-- 查看当前会话端的系统变量
show session variables;
-- 查看当前整个客户端的系统变量
show global variables;
-- 通过LIKE模糊匹配的方式来查找变量
show variables like 'auto%';
-- 查看指定的客户端内的变量
select @@session.autocommit;
select @@global.autocommit;
-- 设置指定客户端的系统变量
-- 方式1
set session autocommit = 1;
set global autocommit = 1;
-- 方式2
set @@session.autocommit = 1;
set @@global.autocommit = 1;

四:用户自定义变量

用户自定义变量是用户根据需要自己定义的变量，用户变量不能提前声明，在用的时候直接用“@变量名”使用就可以。其作用域为当前连接。

赋值

SET @变量名 = expr [,@变量名 = expr]...;

SET @变量名 := expr [,@变量名 := expr]...;

SELECT @变量名 := expr [,@变量名 := expr]...;

SELECT 字段名 INTO @变量名 FROM 表名;

使用

SELECT @变量名;

注意：用户定义的变量无需对其进行声明或初始化，只不过获取到的值为NULL。

-- 对变量进行赋值
set @age = 18;
set @height := 176 ,@hobby = '小说';
select @name := '王凯';
-- 将从tb_user表中统计出的用户数量的值赋给自己定义的变量
select count(*) into @myCount from tb_user;
-- 使用定义的变量
select @name,@age,@height,@hobby;
select @myCount;

五:局部变量

局部变量是根据需要定义的在局部生效的变量，访问之前，需要DECLARE声明。可用作存储过程内的局部变量和输入参数，局部变量的范围是在其声明的BEGIN...END块中。

声明

DECLARE 变量名 变量类型 [DEFAULT ...];

变量类型就是数据库字段类型：INT、BIGINT、VARCHAR、DATE、TIME等

赋值

SET 变量名 = 值;

SET 变量名 := 值;

SELECT 字段名 INTO 变量名 FROM 表名...;

create procedure p1()
begin
    -- 声明局部变量
    declare name varchar(10);
    -- 对局部变量进行赋值
    set name = '蔡建宇';
    set name := '蔡建宇';
    select name into name from tb_user where id = 14;

end;

六:if判断

语法：

IF THEN

......

ELSEIF THEN --可选

......

ELSE --可选

......

END IF;

-- 根据定义的分数score变量，判定当前分数对应的分数等级。
-- score >= 85分，等级为优秀。
-- score >= 60分 且 score < 85分，等级为及格。
-- score < 60分，等级为不及格。

create procedure p1()
begin
    declare score int default 50;
    declare result varchar(3);

    if score >= 85
        then set result = '优秀';
    elseif score >= 60 and score < 85
        then set result = '及格';
    elseif score >= 0 and score < 60
        then set result = '不及格';
    else set result = '输入异常';
    end if;

    select result;

end;

call p1();

七:参数

用法：

CREATE PROCEDURE 存储过程名称([IN/OUT/INOUT 参数名 参数类型])

BEGIN

--SQL语句

END;

-- 根据传入(in)参数score，判定当前分数对应的分数等级，并返回(out)。
-- score >= 85分，等级为优秀。
-- score >= 60分 且 score < 85分，等级为及格。
-- score < 60分，等级为不及格。

create procedure p2(in score int)
begin
    declare result varchar(3);

    if score >= 85
    then set result = '优秀';
    elseif score >= 60 and score < 85
    then set result = '及格';
    elseif score >= 0 and score < 60
    then set result = '不及格';
    else set result = '输入异常';
    end if;

    select result;

end;

call p2(95);

-- 将传入的 200分制的分数,进行换算,换算成百分制 , 然后返回分数 ---> inout

call p2(95);


create procedure p3(inout score int)
begin

    set score := score/2;

end;

set @score = 186;
call p3(@score);
select @score;

八:case

语法一

CASE case_value

WHEN when_value1 THEN statement_list1

[WHEN when_value2 THEN statement_list2]...

[ELSE statement_list]

END CASE;

语法二

CASE

WHEN search_condition1 THEN statement_list1

[WHEN search_condition2 THEN statement_list2]...

[ELSE statement_list]

END CASE;

-- 根据传入的月份，判定月份所属的季节（要求采用case结构）。
-- 1-3月份，为第一季度
-- 4-6月份，为第二季度
-- 7-9月份，为第三季度
-- 10-12月份，为第四季度

create procedure p4(in month int)
begin
    declare result varchar(4);

    case
        when month >= 1 and month <= 3 then
            set result := '第一季度';
        when month >= 4 and month <= 6 then
            set result := '第二季度';
        when month >= 7 and month <= 9 then
            set result := '第三季度';
        when month >= 10 and month <= 12 then
            set result := '第四季度';
        else set result = '非法参数';
    end case;
    select concat('你输入的月份为：',month,'所属的季度为：',result);

end;

九:循环

1.while

while循环是有条件的循环控制语句。满足条件后再执行循环体中的SQL语句。具体语法：

#先判定条件，如果条件为true，则执行逻辑，否则，不执行逻辑

WHILE 条件 DO

SQL逻辑...

END WHIEL;

-- while   计算从1累加到n的值，n为传入的参数值。

create procedure p5(in n int)
begin
    declare total int default 0;

    while n>0 do
        set total = total + n;
        set n := n-1;
    end while;
    select total;

end;

call p5(10);

2.repeat

repeat是有条件的循环控制语句，当满足条件的时候退出循环。具体语法为：

#先执行一次逻辑，然后判定逻辑是否满足，如果满足，则退出。如果不满足，则继续下一次循环

REPEAT

SQL逻辑...

UNTIL 条件

END REPEAT;

-- repeat   计算从1累加到n的值，n为传入的参数值。

create procedure p6(in n int)
begin
    declare total int default 0;

    repeat
        set total := total + n;
        set n = n - 1;
    until
        n = 0
    end repeat;
    select total;

end;

call p5(10);

3.loop

loop实现简单的循环，如果不在SQL逻辑中增加退出循环的条件，可以用其来实现简单的死循环。loop可以配合一下两个语句使用：

①LEAVE：配合循环使用，退出循环

②ITERATE：必须用在循环中，作用是跳过当前循环剩下的语句，直接进入下一次循环

[begin_label:] LOOP

SQL逻辑...

END LOOP [end_label];

LEAVE label; --退出指定标记的循环体

ITERATE label; --直接进入下一次循环

-- loop  计算从1累加到n的值，n为传入的参数值。

create procedure p7(in n int)
begin
    declare total int default 0;
    sum:loop
        if n <= 0
            then
            leave sum;
        end if;
        set total := total + n;
        set n := n - 1;
    end loop;
    select total;

end;

call p7(10);

十:游标

游标(CURSOR)是用来存储查询结果集的数据类型，在存储过程和函数中可以使用游标对结果集进行循环的处理。游标的使用包括游标的声明、OPEN、FETCH和CLOSE，其语法分别如下。

声明游标

DECLARE 游标名称 CURSOR FOR 查询语句;

打开游标

OPEN 游标名称;

获取游标记录

FETCH 游标名称 INTO 变量[,变量...];

关闭游标

CLOSE 游标名称;

十一:条件处理程序

条件处理程序（Handler）可以用来定义在流程控制结构执行过程中遇到问题时相应的处理步骤。具体语法为：

DECLARE handler_action HANDLER FOR condition_value [, condition_value]... statement;

handler_action

CONTINUE:继续执行当前程序

EXIT:终止执行当前程序

condition_value

SQLSTATE sqlstate_value:状态码，如02000

SQLWARNING:所有以01开头的SQLSTATE代码的简写

NOT FOUND:所有以02开头的SQLSTATE代码的简写

SQLEXCEPTION:所有没有被SQLWARNING 或 NOT FOUNT 捕获的SQLSTATE代码的简写

-- 游标
-- 根据传入的参数uage，来查询用户表 tb_user中，所有的用户年龄小于等于uage的用户姓名（name）和专业（profession），
-- 并将用户的姓名和专业插入到所创建的一张新表(id,name,profession)中。
create procedure p8(in uage int)
begin
    declare uname varchar(10);
    declare upro varchar(20);
    declare cur_user cursor for select name,profession from tb_user where age < uage;
    declare exit handler for sqlstate '02000' close cur_user;

    create table user(
      id int primary key auto_increment,
      name varchar(100),
      profession varchar(100)
    );

    open cur_user;
    while true do
        fetch cur_user into uname,upro;
        insert into user values(null,uname,upro);
    end while;
    close cur_user;

end;

call p8(30);

---------------------------------------------------------------------------------------------------------------------------------

存储函数

存储函数是有返回值的存储过程，存储函数的参数只能是IN类型的。具体语法如下：

CREATE FUNCTION 存储函数名称([参数列表])

RETURNS tyep [charceteristic ...]

BEGIN

-- SQL语句

RETURN ...;

END;

characteristic说明

①DETERMINISTIC:相同的输入参数总是产生相同的结果

②NO SQL:不包含SQL语句

③READS SQL DATA:包含读取数据的语句，但不包含写入数据的语句