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 = "吉吉23";
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="jjjj" ;
Query OK, 0 rows affected (0.00 sec)
mysql> select @age , @name;
+------+-----------+
| @age | @name |
+------+-----------+
| 19 | jjjj |
+------+-----------+
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.loop1();
终止循环
在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;
扫一扫
06-23
228
228
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
