Mysql：存储过程

变量

系统变量

变量由系统提供，不是用户定义，属于服务器层面

全局变量

查看所有的全局系统变量：

SHOW GLOBAL VARIABLES
Variable_name                            Value                                                           
​
---------------------------------------  ----------------------------------------------------------------
​
auto_increment_increment                 1                                                               
auto_increment_offset                    1                                                               
automatic_sp_privileges                  ON                                                              
back_log                                 50                                                              
basedir                                  D:Program Files (x86)mysql                                   
binlog_cache_size                        32768                                                           
bulk_insert_buffer_size                  8388608                                                         
character_set_client                     utf8                                                            
character_set_connection                 utf8                                                            
character_set_database                   utf8                                                            
character_set_filesystem                 binary                                                          
character_set_results                    utf8     

查看指定的全局变量

例如查看隔离级别：

SELECT @@tx_isolation
@@tx_isolation   
-----------------
REPEATABLE-READ  

全局变量修改后，其他的连接的变量的值也会改变

作用域：服务器每次启动将为所有的全局变量赋初始值，针对于所有的会话（连接）有效，但不能跨重启（配置文件内的除外）

会话变量

作用域:仅仅针对于当前会话（连接）有效

查看会话变量

SHOW SESSION VARIABLES
Variable_name                            Value                                                           
​
---------------------------------------  ----------------------------------------------------------------
​
auto_increment_increment                 1                                                               
auto_increment_offset                    1                                                               
automatic_sp_privileges                  ON                                                              
back_log                                 50                                                              
basedir                                  D:Program Files (x86)mysql                                   
binlog_cache_size                        32768                                                           
bulk_insert_buffer_size                  8388608                                                         
character_set_client                     utf8                                                            
character_set_connection                 utf8                                                            
character_set_database                   utf8                                                            
character_set_filesystem                 binary    

自定义变量

用户变量

作用域

针对于当前会话有效，与会话变量的作用域有效

声明

声明时必须初始化，格式是 ：

set @变量名=值;或
set @变量名：=值;或
select @变量名:=值

赋值

方式一：

set @变量名=值;或
set @变量名：=值;或
select @变量名:=值

方式二：

select 字段 into 变量名
from 表名

SELECT COUNT(1) INTO @studentNumber
FROM student

使用（查看用户变量的值）

SELECT @studentNumber

局部变量

作用域

仅仅在定义它的begin end中有效，且只能放在begin end中的第一句话

声明

declare 变量名 类型；
declare 变量名 类型 default 值(声明变量的同时进行赋值)

赋值

set 变量名=值;或
set 变量名：=值;或
select @变量名:=值
select 字段 into 变量名
from 表名

使用

select 局部变量名

案例

用户变量

声明两个变量并赋初始值，求和

SET @a=1;
SET @b=2;
SET @sum= @a+@b;
SELECT @sum;

结果

@sum    
--------
3     

存储过程和函数

含义

一组预编译好的SQL语句的集合

好处

提高代码的可重用性

简化操作

减少了编译次数和数据库服务器的连接次数，提高了效率

创建

创建语法

create procedure 存储过程名（参数列表）
begin
    存储过程体
end

参数列表

参数模式 参数名 参数类型

参数模式

IN：该参数作为输入，需要调用方传入值1

OUT：该参数作为输出，可以作为返回值

INOUT：该参数既可以作为输入也可以作为输出，既可以输入值也可以返回值

调用语法

CALL 存储过程名（实参列表）

注意事项

如果存储过程体只有一句话，begin end可省略

存储过程体中的每条SQL语句的结尾必须加分号

存储过程的结尾可以使用 DELIMITER重新设置

案例

无参

进入docker容器mysql窗口并登录，将书写的存储过程语句粘贴进去运行,运行成功后可以在可视化工具中看到存储过程

delimiter $
create procedure myInsert()
begin 
    insert into student (birthday,classno,phone,sex,sname,score) values
    ('1998-11-11','202001','18739396145','nv','tom',501.0),
    ('1998-11-11','202001','18739490123','nan','jack',503.0);
end $

调用存储过程

 call myInsert()$

执行查询语句

| 2020043105 | 1998-11-11 | 202001  | 18739396145 | nv   | tom       |   501 |
| 2020043106 | 1998-11-11 | 202001  | 18739490123 | na   | jack      |   503 |
+------------+------------+---------+-------------+------+-----------+-------+

有输入参数

案例一

delimiter $
create procedure snameIn(IN name varchar(8))
begin
    select sname,score.*
    from student  
    left join score on  student.studentno =score.studentno
where student.sname=name;
end $

调用存储过程

call snameIn('王石')$

案例二

DELIMITER $
CREATE PROCEDURE login(IN username VARCHAR(12),IN passwd VARCHAR(6))
BEGIN
DECLARE result INT DEFAULT 0;
SELECT COUNT(*) INTO result
FROM login
WHERE login.account=username
AND login.password=passwd;
​
SELECT IF(result>0,'成功','失败');
END $

有输出参数

返回一个字段的值

DELIMITER $
CREATE PROCEDURE shuchu(IN NAME VARCHAR(8),OUT finall DOUBLE)
BEGIN
    SELECT score.finall INTO finall
    FROM student  
    LEFT JOIN score ON student.studentno=score.studentno
        WHERE student.sname=NAME;
END $
CALL shuchu('zhai',@fin)$
SELECT @fin$

返回多个字段的值

需要注意一个问题：下面的书写select并分别给两个输出参数赋值的写法是不对的

DELIMITER $
CREATE PROCEDURE outputTwo(IN NAME VARCHAR(8),OUT finall DOUBLE,OUT usually DOUBLE)
BEGIN
    SELECT score.finall INTO finall,score.usually INTO usually
    FROM student  
    LEFT JOIN score ON student.studentno=score.studentno
        WHERE student.sname=NAME;
END $

需要将两个赋值语句合并为一次赋值

DELIMITER $
CREATE PROCEDURE outputTwo(IN NAME VARCHAR(8),OUT finall DOUBLE,OUT usually DOUBLE)
BEGIN
    SELECT score.finall,score.usually INTO finall,usually
    FROM student  
    LEFT JOIN score ON student.studentno=score.studentno
        WHERE student.sname=NAME;
END $

先调用：

查看结果：

INOUT模式

CREATE PROCEDURE inouttest(INOUT a INT,INOUT b INT)
BEGIN
    SET a=a*2;
    SET b=b*2;
END $

删除

存储过程的删除

drop procedure 存储过程名$

查看

查看存储过程信息

SHOW CREATE PROCEDURE 存储过程名称

 

 

总结：

DELIMITER：相当于定义一个结束符，在默认情况下遇到；后就会默认结束