1. 存储过程和函数的定义
方便远程用户调用。sql语句集合,每个sql语句用“;”终止。
1.1 使用场景
1.2声明语句
CREATE
[DEFINER = user]
PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body
CREATE
[DEFINER = user]
FUNCTION sp_name ([func_parameter[,...]])
RETURNS type
[characteristic ...] routine_body
proc_parameter:
[ IN | OUT | INOUT ] param_name type
func_parameter:
param_name type
type:
Any valid MySQL data type
characteristic: {
COMMENT 'string'
| LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
}
routine_body:
Valid SQL routine statement
#举例
mysql> delimiter //
mysql> CREATE PROCEDURE citycount (IN country CHAR(3), OUT cities INT)
BEGIN
SELECT COUNT(*) INTO cities FROM world.city
WHERE CountryCode = country;
END//
调用
CALL citycount('JPN', @cities);
删除过程或者函数
DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name
2. 分割符重定义
delimiter //
作用:在CMD 上写mysql过程或者函数时,会用到‘;’符号,所以
3.变量
3.1 局部变量
3.1.1 声明方式
声明在begin ····· end 体中的变量,作用范围仅限过程和函数中。
The scope of a local variable is the BEGIN ... END
block within which it is declared.
DECLARE var_name [, var_name] ... type [DEFAULT value]
3.1.2 变量操作
-
赋值
set VarName
SELECT name INTO VarName FROM table1 WHERE xname = xname;
-
获取变量值
select VarName;
-
变量使用
SET sum = sum + i
直接运算即可,无需加@符号
3.1.3 example
mysql> CREATE PROCEDURE sp1 ()
-> BEGIN
-> DECLARE xname VARCHAR(5) DEFAULT 'bob';
-> set xname='zhang';
-> select xname;
-> END;
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> call sp1()//
+-------+
| xname |
+-------+
| zhang |
+-------+
1 row in set (0.00 sec)
3.2 用户变量
mysql中用户变量不用提前声明,在用的时候直接用“@变量名”使用就可以了,其作用域为当前链接
3.2.1 语法
SET @var_name = expr [, @var_name = expr] ...
delimiter //
mysql> set @age=12//
Query OK, 0 rows affected (0.00 sec)
mysql> select @age//
+------+
| @age |
+------+
| 12 |
+------+
1 row in set (0.02 sec)
3.2.2 赋值&操作
赋值
- set @age=12
- set @age:=12
操作
- select @age:=age from student where name=‘zhangsan’;
mysql> select @num:=m_Id from member where m_Name='123';
+------------+
| @num:=m_Id |
+------------+
| 49 |
+------------+
1 row in set (0.10 sec)
3.3 全局(系统)变量
mysql全局变量,全局变量影响服务器整体操作,当服务启动时,它将所有全局变量初始化为默认值,要想变更全局变量,必须具有super权限,其作用域为server的整个生命周期。
-
显示所有的会话变量
show global variables
-
设置会话变量的值的两种方式
set global auto_increment_increment=1
set @@global.auto_increment_increment=1
-
查询会话变量的两种方式
select @@global.auto_increment_increment;
show global variables like ‘%auto_increment_increment%’ ----这里面的字母都是关键字,关键字session也可以用local关键字代替
3.4 会话变量
服务器为每个连接的客户端维护一系列会话变量,其作用域仅限于当前链接,即每个连接中的会话变量是独立的。
-
显示所有的会话变量
show session variables
-
设置会话变量的值的三种方式
set session auto_increment_increment=1
set @@auto_increment_increment=1
set auto_increment_increment=1
-
查询会话变量的三种方式
select @@auto_increment_increment;
select @@session.auto_increment_increment;
show session variables like ‘%auto_increment_increment%’ ----这里面的字母都是关键字,关键字session也可以用local关键字代替
变量部分引用:UPJu加油坊的Mysql数据库存储过程详解【2023版最新教程】视频
4. 使用案例
4.1插入数据
表结构
desc stu//
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| id | int(10) | NO | PRI | NULL | |
| name | varchar(20) | NO | | NULL | |
| date | date | YES | | NULL | |
| time | time | YES | | NULL | |
| classroom | varchar(20) | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
创建存储过程
CREATE PROCEDURE insert_stu (in id int(10),in name varchar(10),in date_now date,in time_now time ,in classroom varchar(10))
BEGIN
insert into school.stu
(
id ,
name ,
`date`,
`time`,
classroom
)
values
(
id ,
name ,
date_now ,
time_now ,
classroom
)
;
END
调用
call insert_stu(10,'zhangsan','2024-07-23','08:00:00','sasd');
4.2 批量向表中插入数据
表结构同4.1
CREATE PROCEDURE insert_stu_02()
BEGIN
declare MaxVar int;
select max(id) into MaxVar from school.stu;
while MaxVar <200 DO
set MaxVar=MaxVar+1;
insert into school.stu(id,name,`date`,`time`,classroom) values(MaxVar,'zhangsan','2024-07-23','08:00:00','sasd' );
end while;
END
call insert_stu_02();