MySQL存储过程

Top

NSD DBA2 DAY05

1. 案例1：创建存储过程
2. 案例2：存储过程参数的使用
3. 案例3：使用循环结构

1 案例1：创建存储过程

1.1 问题

• 存储过程名称为p1
• 功能显示user表中 shell是/bin/bash的用户个数
• 调用存储过程p1

1.2 步骤

实现此案例需要按照如下步骤进行。

步骤一：存储过程基本使用

1）创建存储过程

1. mysql> delimiter //         //定义定界符
2. mysql> create procedure say() //say()随便写括号一定要有
3. -> begin
4. -> select * from user where id<=10;
5. -> end
6. -> //
7. Query OK, 0 rows affected (0.01 sec)
8. mysql> delimiter ;        //把命令的定界符改回来，分号前有空格
9. mysql> call say();    //调用存储过程名,在括号里面不写参数时，可以不加括号

2）查看存储过程

方法一：

1. mysql> show procedure status\G;

方法二：

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

3）删除存储过程

1. mysql> drop procedure say;
2. Query OK, 0 rows affected (0.00 sec)

4）创建存储过程名称为p1

 功能显示user表中 shell是/bin/bash的用户

 调用存储过程p1

1. mysql> delimiter //
2. mysql> create procedure p1()
3. -> begin
4. -> select count(username) from user where shell="/bin/bash";
5. -> end
6. -> //
7. mysql> delimiter ;
8. mysql> call p1();
9. +-----------+
10. | shell |
11. +-----------+
12. | /bin/bash |
13. | /bin/bash |
14. +-----------+
15. 2 rows in set (0.01 sec)
17. Query OK, 0 rows affected (0.01 sec)

2 案例2：存储过程参数的使用

2.1 问题

• 创建名为p2的存储过程
• 可以接收用户输入shell的名字
• 统计user表中用户输入shell名字的个数

2.2 步骤

实现此案例需要按照如下步骤进行。

步骤一：存储过程参数的使用

1）参数类型

MySQL存储过程，共有三种参数类型IN,OUT,INOUT

Create procedure 名称(

类型 参数名 数据类型,

类型 参数名 数据类型

）

in 输入参数 传递值给存储过程，必须在调用存储过程时指定，在存储过程中修改该参数的值不能；默认类型是in

out 输出参数 该值可在存储过程内部被改变，并可返回

inout 输入/输出参数 调用时指定，并且可被改变和返回

1. mysql> delimiter //
2. mysql> create procedure say2(in username char(10))
3. -> begin
4. -> select username;
5. -> select * from user where username=username;
6. -> end
7. -> //
8. Query OK, 0 rows affected (0.00 sec)
10. mysql> delimiter ;
11. mysql> call say2("tom");

2）创建名为p2的存储过程，可以接收用户输入shell的名字，统计user表中用户输入shell名字的个数

1. mysql> delimiter //
2. mysql> create procedure p2(out number int)
3. -> begin
4. -> select count(username) into @number from user where shell!="/bin/bash";
5. -> select @number;
6. -> end
7. -> //
8. Query OK, 0 rows affected (0.01 sec)
9. mysql> delimiter ;
10. mysql> call p2(@number);
11. +---------+
12. | @number |
13. +---------+
14. | 38 |
15. +---------+
16. 1 row in set (0.00 sec)
18. Query OK, 0 rows affected (0.00 sec)

3 案例3：使用循环结构

3.1 问题

• 定义名称为p3的存储过程
• 用户可以自定义显示user表记录的行数
• 若调用时用户没有输入行数，默认显示第1条记录

3.2 步骤

实现此案例需要按照如下步骤进行。

步骤一：算数运算

1）算数运算符号，如图-1所示：

图-1

1. mysql> set @z=1+2;select @z;
2. Query OK, 0 rows affected (0.00 sec)
4. +------+
5. | @z |
6. +------+
7. | 3 |
8. +------+
9. 1 row in set (0.00 sec)
10. mysql> set @x=1; set @y=2;set @z=@x*@y; select @z;
11. Query OK, 0 rows affected (0.00 sec)
13. Query OK, 0 rows affected (0.00 sec)
15. Query OK, 0 rows affected (0.00 sec)
17. +------+
18. | @z |
19. +------+
20. | 2 |
21. +------+
22. 1 row in set (0.00 sec)
24. mysql> set @x=1; set @y=2;set @z=@x-@y; select @z;
25. Query OK, 0 rows affected (0.00 sec)
27. Query OK, 0 rows affected (0.00 sec)
29. Query OK, 0 rows affected (0.00 sec)
31. +------+
32. | @z |
33. +------+
34. | -1 |
35. +------+
36. 1 row in set (0.00 sec)
37. mysql> set @x=1; set @y=2;set @z=@x/@y; select @z;
38. Query OK, 0 rows affected (0.00 sec)
40. Query OK, 0 rows affected (0.00 sec)
42. Query OK, 0 rows affected (0.00 sec)
44. +-------------+
45. | @z |
46. +-------------+
47. | 0.500000000 |
48. +-------------+
49. 1 row in set (0.00 sec)

declare调用变量不需要@其他都需要

调用变量时，有@符号的变量 如@x：调用的是用户自定义变量

没有@符号的变量 如x：调用的是存储过程的参数变量

1. mysql> delimiter //
2. mysql> create procedure say5(in bash char(20), in nologin char(25), out x int , out y int)
3. -> begin
4. -> declare z int ;
5. -> set z=0;
6. -> select count(username) into @x from user where shell=bash;
7. -> select count(username) into @y from user where shell=nologin;
8. -> set z=@x+@y;
9. -> select z;
10. -> end
11. -> //
12. Query OK, 0 rows affected (0.00 sec)
14. mysql> delimiter ;
15. mysql> call say5("/bin/bash","/sbin/nologin",@x,@y);
16. +------+
17. | z |
18. +------+
19. | 36 |
20. +------+
21. 1 row in set (0.00 sec)
23. Query OK, 0 rows affected (0.00 sec)

2）条件判断，数值的比较如图-2所示：

图-2

逻辑比较、范围、空、非空、模糊、正则，如图-3所示：

图-3

顺序结构（if判断）当“条件成立”时执行命令序列,否则，不执行任何操作

1. mysql> delimiter //
2. mysql> create procedure say6(in x int(1) )
3. -> begin
4. -> if x <= 10 then
5. -> select * from user where id <=x;
6. -> end if;
7. -> end
8. -> //
9. Query OK, 0 rows affected (0.01 sec)
11. mysql> delimiter ;
12. mysql> call say6(1);        //条件判断成立，等于1是否成立
13. +----+----------+----------+------+------+---------+---------+-----------+
14. | id | username | password | uid | gid | comment | homedir | shell |
15. +----+----------+----------+------+------+---------+---------+-----------+
16. | 1 | root | x | 0 | 0 | root | /root | /bin/bash |
17. +----+----------+----------+------+------+---------+---------+-----------+
18. 1 row in set (0.00 sec)
20. Query OK, 0 rows affected (0.00 sec)
22. mysql> call say6(2);
23. +----+----------+----------+------+------+---------+---------+---------------+
24. | id | username | password | uid | gid | comment | homedir | shell |
25. +----+----------+----------+------+------+---------+---------+---------------+
26. | 1 | root | x | 0 | 0 | root | /root | /bin/bash |
27. | 2 | bin | x | 1 | 1 | bin | /bin | /sbin/nologin |
28. +----+----------+----------+------+------+---------+---------+---------------+
29. 2 rows in set (0.00 sec)
31. Query OK, 0 rows affected (0.00 sec)

3）定义名称为p3的存储过程，用户可以自定义显示user表记录的行数，若调用时用户没有输入行数，默认显示第1条记录

1. mysql> delimiter //
2. mysql> create procedure p3(in linenum char(10) )
3. -> begin
4. -> if linenum is null then
5. -> set @linenum=1;
6. -> select * from user where id=@linenum;
7. -> else
8. -> select linenum;
9. -> select * from user where id=linenum;
10. -> end if;
11. -> end
12. -> //
13. Query OK, 0 rows affected (0.00 sec)
15. mysql> delimiter ;
16. mysql> call p3(null);        //不输入查看的行数
17. +----+----------+----------+------+------+---------+---------+-----------+
18. | id | username | password | uid | gid | comment | homedir | shell |
19. +----+----------+----------+------+------+---------+---------+-----------+
20. | 1 | root | x | 0 | 0 | root | /root | /bin/bash |
21. +----+----------+----------+------+------+---------+---------+-----------+
22. 1 row in set (0.00 sec)
24. Query OK, 0 rows affected (0.00 sec)
26. mysql> call p3(3);        //输入查看的行数
27. +---------+
28. | linenum |
29. +---------+
30. | 3 |
31. +---------+
32. 1 row in set (0.00 sec)
34. +----+----------+----------+------+------+---------+---------+---------------+
35. | id | username | password | uid | gid | comment | homedir | shell |
36. +----+----------+----------+------+------+---------+---------+---------------+
37. | 3 | daemon | x | 2 | 2 | daemon | /sbin | /sbin/nologin |
38. +----+----------+----------+------+------+---------+---------+---------------+
39. 1 row in set (0.00 sec)
41. Query OK, 0 rows affected (0.00 sec)

转载于:https://www.cnblogs.com/tiki/p/10785271.html