mysql存储过程入门

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();