mysql存储过程执行ddl mysql存储过程执行完不停止

转载

mob6454cc61981e 2023-10-07 22:49:17

文章标签 mysql存储过程执行ddl mysql dba 存储过程全局变量 文章分类 MySQL 数据库

mysql存储过程

1 、什么是存储过程

说白了就是mysql服务的脚本，登录服务器后要重复执行的命令写成存储过程

存储过程就是mysql服务的脚本。

2、创建存储过程的命令格式（需牢记）

演示delimiter 命令的作用

命令行的结束符号默认是；

mysql> delimiter //                  把命令行的结束符号 改为//
mysql> desc tarena.user //    执行命令是得使用//结束命令

mysql> delimiter ;    再改回默认的 ；
mysql> desc tarena.user ;

存储过程的使用

创建存储过程 pria()

mysql> use  tarena;
mysql> delimiter //
mysql> create procedure pria()
    -> begin
    -> select  count(*) from   tarena.salary  ;
    -> select  count(*) from   tarena.employees  ;
    -> end
    -> //
mysql> delimiter  ;

执行存储过程 pria()

mysql> call pria();  或  call  tarena.pria();    
+----------+
| count(*) |
+----------+
|     8055 |
+----------+
1 row in set (0.00 sec)

+----------+
| count(*) |
+----------+
|      135 |
+----------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> 

mysql> 
mysql> call pria;   存储创建时括号（） 里没有参数 ，执行可以省略()

查当前所在库已有的存储过程

mysql> use tarena; 
mysql> show procedure status \G
                      Db: tarena
                Name: pria
                  Type: PROCEDURE

或

mysql库里proc表存放所有的存储过程

列出服务器上所有的存储过程

mysql> select db, name ,  type  from mysql.proc where   type="PROCEDURE";  

mysql> select  db, name ,  type  from mysql.proc 
where   type="PROCEDURE"  and   name="pria";   查看是否有名字叫pria的存储过程
+--------+------+-----------+
| db     | name | type      |
+--------+------+-----------+
| tarena | pria | PROCEDURE |
+--------+------+-----------+
1 row in set (0.00 sec)

查看存储过程的功能代码

mysql> select  name , body  from mysql.proc
where  name="pria" and type="PROCEDURE";

+------+------------------------------------------------------------------------------------------------+
| name | body                                                                                           |
+------+------------------------------------------------------------------------------------------------+
| pria | begin
select  count(*) from   tarena.salary  ;
select  count(*) from   tarena.employees  ;
end |
+------+------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

删除存储过程

mysql> drop procedure 库.存储过程名 ;

mysql> drop  procedure   tarena.pria;
Query OK, 0 rows affected (0.00 sec)

mysql> call tarena.pria;
ERROR 1305 (42000): PROCEDURE tarena.pria does not exist
mysql> select name  from mysql.proc where name="pria";
Empty set (0.00 sec)

存储过程进阶

1 变量的使用

1.1 变量的分类：

系统变量： mysql服务定义包括：

全局变量（任意用户连接服务查看到值都一样的）

会话变量：连接服务器的用户登录期间使用的变量

自定义变量：连接数据库服务的用户定义包括：

用户变量：用户登录数据库服务器，自己定义的变量

局部变量：在begin 和 end 定义的变量

仅存储过程执行中有效

全局变量使用演示

[root@host50 ~]# mysql -uroot -p123456
mysql> show global variables \G      查看所有的全局变量
Variable_name: version_compile_os
         Value: Linux

mysql> show global variables like "version_compile_os" ;
仅查看一个全局变量

修改全局变量的值

set   global   变量名="值"； 
mysql> set global  wait_timeout = 20000;
mysql> show global variables like "wait_timeout";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 20000 |
+---------------+-------+
1 row in set (0.00 sec)
mysql> select  @@version_compile_os;    输出某个全局变量的值 
+----------------------+
| @@version_compile_os |
+----------------------+
| Linux                |
+----------------------+
1 row in set (0.00 sec)

mysql>

会话变量管理：当前连接用户使用的变量，会话变量只在连接过程中有效

mysql> show session variables \G    查看连接的所有变量
mysql> show session variables  like   "%关键字%"  \G
    
mysql> show session variables  like  "%cache%" ;   仅查看与内存相关的变量
mysql> set session sort_buffer_size=50000;  修改 

mysql>  show session variables  like "sort_buffer_size";
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| sort_buffer_size | 50000 |
+------------------+-------+
1 row in set (0.00 sec)

部变量的使用

declare 命令用来的定义局部变量

例子：

mysql> use tarena;
mysql> delimiter  //
mysql> create  procedure  say48() 
begin declare  x  int default 9; 
declare  y  char(10); 
set y = "plj"; 
select x , y ; 
end 
//
mysql> delimiter  ;

调用存储过程

mysql> call say48;             
+------+------+
| x    | y    |
+------+------+
|    9 | plj  |
+------+------+
1 row in set (0.00 sec)

使用任意一种方式调用局部变量都会报错因为局部变量只在存储过程执行中有效

mysql> select x , y ;
ERROR 1054 (42S22): Unknown column 'x' in 'field list'
mysql> 
mysql> select @x , @y ;
+------+------+
| @x   | @y   |
+------+------+
| NULL | NULL |
+------+------+
1 row in set (0.00 sec)

mysql> call say48;
+------+------+
| x    | y    |
+------+------+
|    9 | plj  |
+------+------+
1 row in set (0.00 sec)

用户变量的使用

#定义并赋值

mysql> set @age=19 , @name="pangljing" ;
Query OK, 0 rows affected (0.00 sec)

mysql> select @age , @name;
+------+-----------+
| @age | @name     |
+------+-----------+
|   19 | pangljing |
+------+-----------+
1 row in set (0.00 sec)

使用查询结果赋值

mysql> select count(name) into @numbers from tarena.user where shell = "/bin/bash";
mysql> select count(*) into @lines from tarena.user
mysql> select @lines as  总行数  ,   @numbers  as 登录系统用户数;
+-----------+-----------------------+
| 总行数    | 登录系统用户数        |
+-----------+-----------------------+
|         26 |                                1 |
+-----------+-----------------------+
1 row in set (0.00 sec)

mysql> select max(uid) , min(uid) into  @b,@s  from tarena.user;
 Query OK, 1 row affected (0.00 sec)

mysql> select @b,@s;
+-------+------+
| @b    | @s   |
+-------+------+
| 65535 |    1 |
+-------+------+
1 row in set (0.00 sec)

mysql>

2 参数的使用

create procedure 名(参数,参数,......)
参数定义的语法格式参数类型变量名数据类型

参数类型分3种： in out inout

in in类型的参数负责把数据传给存储过程

例如    create     proucedure  p2(  in   x   int )
        begin
......
       end

例子

mysql> delimiter //
mysql> create procedure tarena.p3(in  dept_no int)
    -> begin
    -> select dept_id , count(*) as 总人数  from 
	-> tarena.employees where dept_id=dept_no group by  dept_id;
    -> end
    -> //
mysql> delimiter ;	
mysql> call  p3() ;    不给参数会报错
mysql> call  p3(1) ;  查看部门编号1  的员工人数
mysql> call  p3(3) ;  查看部门编号3  的员工人数

out类型的参数负责接收存储过程的处理结果。

存储过程执行结束后可以调用 out类型的参数获取存储过程的处理结果

例如

create     proucedure  tarena.p31(  out   x   int )
begin
 ......
end
	
			
call   tarena.p31(@名)；
select  @名；

编写存储过程tarena.p4 功能获取员工表里指定用户的邮箱

mysql> delimiter //
mysql> create procedure  tarena.p4( in emp_name varchar(10) , OUT mail varchar(25))
begin
       select email into mail  from employees  where name=emp_name;
end  //
mysql> delimiter ;	
insert into employees(name,email)   插入做测试的员工
values("john","john@163.com"),("jerry","jerry@tedu.cn");
mysql> call tarena.p4("jerry",@m);  执行存储过程
Query OK, 1 row affected (0.00 sec)

mysql> select @m;  查看变量 看 员工的邮箱 
+---------------+
| @m            |
+---------------+
| jerry@tedu.cn |
+---------------+
1 row in set (0.00 sec)

存储过程归属的库使用的不是中文字符集时 ,创建的存储过程无法识别中文

mysql> call tarena.p4("王小红",@m);
ERROR 1366 (HY000): Incorrect string value: '\xE7\x8E\x8B\xE5\xB0\x8F...' for column 'emp_name' at row 1
mysql>

修改库使用的字符集

alter database tarena default CHARACTER SET utf8;  修改库使用的字符集
   drop  procedure  tarena.p4;  删除已经的存储过程重新创建 ， 因为字符集 对已经存储的存储过程无效

mysql> delimiter //
mysql> create procedure  tarena.p4( in emp_name varchar(10) , OUT mail varchar(25)) 
begin select email into mail  from employees  where name=emp_name;
end
//
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> call tarena.p4("王小红",@m);
Query OK, 1 row affected (0.00 sec)

mysql> select  @m;
+----------------------+
| @m                   |
+----------------------+
| wangxiaohong@tedu.cn |
+----------------------+
1 row in set (0.00 sec)

使用INOUT参数(既有in参数的功能又有out参数的功能)

mysql>  delimiter //
mysql>  create procedure tarena.myadd(INOUT i int)
    -> begin
    -> set i=i+100;
    -> end //
mysql> delimiter ;

mysql> set  @x = 8 , @y = 9 ;
Query OK, 0 rows affected (0.00 sec)

mysql> call tarena.myadd(@x);
Query OK, 0 rows affected (0.00 sec)

mysql> call tarena.myadd(@y);
Query OK, 0 rows affected (0.00 sec)

mysql> select  @x , @y;
+------+------+
| @x   | @y   |
+------+------+
|  108 |  109 |
+------+------+
1 row in set (0.00 sec)

mysql>

存储过程里的判断语句 if语句

格式1 一个判断条件

IF 条件 THEN
语句;
END IF;

格式2 条件不成立的时候执行什么操作

IF 条件 THEN
语句1;
ELSE
语句2;
END IF;

格式3 有多个判断条件

IF 条件1 THEN
  语句1;
ELSEIF 条件2 THEN
  语句2;
ELSE
  语句3;
END IF;

例子

mysql> delimiter //
mysql> create procedure tarena.deptype_pro(IN no int, OUT dept_type varchar(5))
    -> begin
    -> declare type varchar(5);
    -> select dept_name into type from departments where dept_id=no;
    ->      if type='运维部' then
    ->          set dept_type='技术部';
    ->        elseif type='开发部' then
    ->          set dept_type='技术部';
    ->        elseif type='测试部' then
    ->          set dept_type='技术部';
    ->        else
    ->          set dept_type='非技术部';
    ->        end if;
    ->      end //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> call deptype_pro(2,@t);
Query OK, 1 row affected (0.00 sec)

mysql> select  @t;
+--------------+
| @t           |
+--------------+
| 非技术部     |
+--------------+
1 row in set (0.00 sec)

mysql> call deptype_pro(4,@t);
Query OK, 1 row affected (0.00 sec)

mysql> select  @t;
+-----------+
| @t        |
+-----------+
| 技术部    |
+-----------+
1 row in set (0.00 sec)

mysql>

case语句

语法格式

CASE 变量|表达式|字段
WHEN 判断的值1 THEN 返回值1;
WHEN 判断的值2 THEN 返回值2;
... ...
ELSE 返回值n;
END CASE;

例子

delimiter  //
create procedure tarena.deptype_pro2(IN no int, OUT dept_type varchar(5))
begin
declare type varchar(5);
select dept_name into type from departments  where dept_id=no;
case type
when '运维部' then set dept_type='技术部';
when '开发部' then set dept_type='技术部';
when '测试部' then set dept_type='技术部';
else set dept_type='非技术部';
end case; 
end //      
delimiter ;

调用存储过程tarena.deptype_pro2

call  tarena.deptype_pro2(1,@t);  @使用自定义变量接收out参数的值
select  @t;  查看查看自定义变量@t的值 

mysql> call deptype_pro2(4,@t);
Query OK, 1 row affected (0.00 sec)

mysql> select  @t;
+-----------+
| @t        |
+-----------+
| 技术部    |
+-----------+
1 row in set (0.00 sec)

mysql>

MySQL存储过程的循环结构的例子：

while 循环结构条件判断成立时重复执行同一段代码
如果第1次判断条件就不成立会直接结束循环

语法格式

while 判断条件 do
代码
end while;

while 循环结构的例子：

条件判断成立就执行do下边的命令反之执行end while 结束循环

delimiter //
create procedure tarena.while_pro(IN i int)
begin
declare j int default 1;
while j<i do      
    insert into tarena.departments(dept_name) values('hr');  
	set j=j + 1; 
end while;
end //
delimiter ;
mysql> select * from  departments;
mysql> call  tarena.while_pro(3);
mysql> select * from  departments;

当首次判断条件就没成立，while是不会执行的。

mysql> call  tarena.while_pro(0);
mysql> call  tarena.while_pro(1);
mysql> select * from  departments;

loop 循环结构

没有判断条件重复执行同一段代码只要不人为结束就一直执行所以被称为死循环

语法格式

loop
	代码
end loop；

loop循环结构例子

delimiter  //
create procedure tarena.loop2()
begin
declare i int default 1;
loop
	select  sleep(1) , i;
end loop；
end  //
delimiter ;

call  tarena.loop2();

终止循环开一个新的终端终端

在mysql登录状态下 查看正在执行的命令
mysql>  show  processlist;

在mysql登录状态下终止命令的执行
mysql>  kill   id号;

repeat循环的例子:

至少循环一次
因为先执行循环体，再判断条件（当判断条件成立时继续执行循环体（判断条件不成立为为真），反之结束循环）

语法格式

repeat
循环体
until 判断条件
end repeat;

例子

delimiter //
create procedure tarena.repeat_pro(IN i int)
begin
declare j int default 1;
repeat
   set j=j+1;  
   insert into tarena.departments(dept_name) values('sales');
   until j>i  #判断条件不成立执行循环体，反之循环结束
end repeat;
end //
delimiter ;

mysql> call  tarena.repeat_pro(4);
mysql> select  * from tarena.departments;

验证repeat 是先执行循环体再判断条件的

mysql> call  tarena.repeat_pro(0);  判断条件成立了 也执行了添加部门的insert into  命令
mysql> select  * from tarena.departments;

循环控制语句

leave (结束循环)

iterate 终止当前循环并开始下次循环

leave例子

delimiter //
create procedure tarena.p0()
begin
	loop
	     select sleep(1);
	     select "one";
	end loop;
end
//
delimiter ;

mysql> call tarena.p0 ; 一直在输出

drop procedure tarena.p0 ;

delimiter //
create procedure tarena.p0()
begin
	p:loop
             leave p;
	     select sleep(1);
	     select "one";
	end loop p;
end
//
delimiter ;

mysql> call tarena.p0 ;  没有输出

iterate例子

delimiter //
create procedure tarena.while_pro3(IN i int)
begin
	declare j int default 0;
	a:while j<i do
		set j=j+1;
		if mod(j,2)=0 then iterate a;  #变量j 存储的是偶数时，开始下一次循环
		end if;
		insert into tarena.departments(dept_name) values(concat('hr', j));
    end while a;
end //
delimiter ;  
 
mysql> call  tarena.while_pro3(10);
mysql> select  * from  tarena.departments;

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。

上一篇：javascript 正则表达式替换 js 正则替换字符串某一段

下一篇：java snapshot快照 jvm快照分析

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯

mysql存储过程执行ddl mysql存储过程执行完不停止

mysql存储过程执行ddl mysql存储过程执行完不停止

mysql存储过程

1 、 什么是存储过程

2、创建存储过程的命令格式（需牢记）

存储过程的使用

执行 存储过程 pria()

查当前所在库 已有的存储过程

查看存储过程的功能代码

删除存储过程

存储过程进阶

1 变量的使用

部变量的使用

调用存储过程

用户变量的使用

2 参数的使用

out类型的参数 负责接收存储过程的处理结果。

存储过程里的判断语句 if语句

case语句

MySQL存储过程的循环结构的例子：

loop 循环结构

循环控制语句

51CTO博客

1 、什么是存储过程

执行存储过程 pria()

查当前所在库已有的存储过程

out类型的参数负责接收存储过程的处理结果。