MySQL学习笔记Day6

一、存储过程

存储过程是事先经过编译并存储在数据库中的一段 SQL语句的集合，调用存储过程可以简化应用开发人员的很多重复的工作，提高数据处理的效率。

1、特点

(1)封装、复用

(2)可接收参数

(3)减少网络交互，提高效率

2、语法结构

delimiter $$  -- 设置sql语句以$$结束

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

BEGIN

        -- SQL语句

END;

delimiter ;

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

3、存储过程的查看删除

• 查看

SHOW CREATE PROCEDURE 存储过程名称；

SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA=’库名’；

• 删除

DROP PROCEDURE IF EXISTS 存储过程名称;

4、变量

• 系统变量

系统变量是MYSQL服务器提供，不是用户定义的，属于服务器层面。在数据库的配置文件中（.ini文件）

查看系统变量：

• SHOW [SESSION|GLOBAL] VARIABLES; -- 查看所有系统变量
• SHOW [SESSION|GLOBAL] VARIABLES LIKE ‘……’; -- 可以通过LIKE模糊匹配方式查找变量
• SELECT @@[SESSIOON|GLOBAL] 系统变量名; -- 查看指定变量的值

设置系统变量：

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

• 用户定义变量

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

语法结构：

• SET @var_name=expr[,@var_name=expr]...;
• SET @var_name:=exp [,@var_name :=expr]...;
• SELECT @var_name:=expr [,@var_name :=expr]...;
• SELECT 字段名 INTO @var_name FROM 表名;

使用：

• SELECT @var_name;

• 局部变量

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

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

语法结构

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

赋值

• SET 变量名=值;
• SET 变量名:=值;
• SELECT 字段名 INTO 变量名 FROM 表名...;

5、if判断

语法结构

IF 条件1 THEN
......
ELSEIF 条件2 THEN
......
ELSE
......
END IF;

6、参数列表

参数类型：

• IN(默认)：该类参数作为输入，调用时需要传入值
• OUT：该类参数作为输出，参数可以作为返回值
• INOUT：既可以作为输入参数，也可以作为输出参数

语法结构：

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

BEGIN

        -- SQL语句

END;

例子：
delimiter $$
create PROCEDURE p(inout score double)
begin
    set score = score/2;
end$$
delimiter ;
set @score = 165
call p(@score);
select @score

7、case多条件判断

创造过程时进行分支选择判断

例子：判断月份季度

delimiter $$
create PROCEDURE p6(in month int)
begin
    DECLARE result varchar(10);
    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$$
delimiter ;

call p6(15)

8、while循环

while 循环是有条件的循环控制语句。满足条件后，再执行循环体中的SQL语句。先判定条件，如果条件为true，则执行逻辑，否则，不执行逻辑。

例子：累加

delimiter $$
create PROCEDURE p7(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$$
delimiter ;

call p7(150)

9、repeat循环

repeat是有条件的循环控制语句,当满足条件的时候退出循环。先执行一次逻辑，然后判定逻辑是否满足，如果满足，则退出。如果不满足，则继续下一次循环。

例子：累加

delimiter $$
create PROCEDURE p8(in n int)
begin
    DECLARE total int DEFAULT 0;
    REPEAT
        set total=total+n;
        set n=n-1;
    UNTIL n<=0 -- 返回结果为true跳出
    END REPEAT;
    SELECT total;
end$$
delimiter ;

call p8(1000)

10、loop循环

LOOP 实现简单的循环，如果不在SQL逻辑中增加退出循环的条件，可以用其来实现简单的死循环。

LOOP可以配合一下两个语句使用:

• LEAVE:配合循环使用，退出循环。
• ITERATE:必须用在循环中，作用是跳过当前循环剩下的语句，直接进入下一次循环。

例子：累加，求偶数的和

delimiter $$
create PROCEDURE p9(in n int)
begin
    DECLARE total int DEFAULT 0;
    sum:loop
        if n<=0 then
            leave sum;
        end if;
        if n%2=1 then
            set n=n-1;
            iterate sum;
        end if;
        set total = total+n;
        set n=n-1;
    end loop sum;
    SELECT total;
end$$
delimiter ;

call p9(100)

11、游标

在存储过程中使用变量存在的问题。查询结果为多列，无法赋值给一个变量。

游标(CURSOR)是用来存储查询结果集的数据类型,在存储过程和函数中可以使用游标对结果集进行循环的处理。

游标的使用包括游标的声明、OPEN、FETCH和 CLOSE，其语法分别如下。

语法结构：

• 声明游标 ——DECLARE 游标名称 CURSOR FOR 查询语句;
• 打开游标 ——OPEN 游标名称;
• 获取游标记录 ——FETCH 游标名称 INTO 变量[,变量];
• 关闭游标 ——CLOSE 游标名称;

例子：根据传入的薪资参数，来查询员工表t_emp中，所有底薪 大于传入薪资的员工姓名(ename)、岗位(job)、底薪数 (sal)。将查询到的结果插入到一张新表中。

delimiter $$
create PROCEDURE p11(in esal decimal)
begin
    -- 声明变量
    declare e_ename varchar(100);
    declare e_job varchar(100);
    declare e_sal varchar(100);
    -- 声明游标
    DECLARE e_cursor cursor for 
    SELECT ename,job,sal from t_emp where sal>esal;
    -- 声明处理程序，满足条件时退出游标
    declare exit handler for SQLSTATE '02000' close e_cursor;
    -- 删除并创建存放数据的表
    drop table if exists t_emp_pro;
    create table if not exists t_emp_pro(
        empno int PRIMARY key auto_increment,
        ename varchar(100),
        job varchar(100),
        sal decimal
    );
    -- 打开游标
    open e_cursor;
    -- 游标赋值给变量
    while true do 
        fetch e_cursor into e_ename,e_job,e_sal;
        -- 向表中添加数据
        insert into t_emp_pro values(null,e_ename,e_job,e_sal);
    end while ;
    -- 关闭游标
    close e_cursor;
end$$
delimiter ;

call p11(2000)