存储过程

MYSQL语句组成的脚本。
是数据库中保存的一系列SQL命令的集合。可以在存储过程中使用变量、条件判断、流程控制等。
不是解释执行(解释器把命令转化成电脑能听懂的二进制0和1)，而是编译执行。

优点：提高性能、减轻网络负担、防止对表的直接访问、避免重复编写SQL操作。

存储过程要在库里进行。
我们定义的存储过程，全都存放在mysql.proc这个表里。其中的body这个字段里保存的就是，我们定义的存储过程p1里的命令。
################################################################################
创建存储过程

格式：
mysql> delimiter //
mysql> create procedure 名称()
-> begin
-> ... ...功能代码
-> end
-> // 打//代表结束这个存储过程。
mysql> delimiter ; 指定；作为命令的结束符号

备注：delimiter这个词用来指定存储过程的分隔符(默认是；)。
它后面跟的是什么符号，那个符号就作为结束命令的符号。
如delimiter // 代表以 // 作为命令的结束符号
delimiter ; 代表以 ; 作为命令的结束符号
如果没有指定分隔符，默认分隔符就是；系统会把存储过程中的命令当成SQL语句来处理，从而执行出错。
#################################################################################
查看存储过程

方法1
mysql> show procedure status\G; 查看全部的存储过程

方法2
格式：mysql> select db,name,type from mysql.proc where name="存储过程名";

mysql> select db,name,type from mysql.proc where name="p1"; 看p1这个存储过程在哪个库里，它的名称，类型
+-----+------+-----------+
| db | name | type |
+-----+------+-----------+
| db9 | p1 | PROCEDURE | p1这个存储过程在db9库里，它的名称p1，类型是存储过程PROCEDURE
+-----+------+-----------+
################################################################################
调用存储过程

call 存储过程名()
mysql> call p1(); 执行p()这个存储过程

删除存储过程
drop procedure 存储过程名;
mysql> drop procedure p1; 删除p1这个存储过程

删除的时候，只要名p1，不要括号（）
################################################################################
mysql> use db9; 先进入到库里面

mysql> delimiter // 指定以//作为命令的结束符号
mysql> create procedure p1() 创建一个存储过程，名叫p1()
-> begin 开始
-> select * from db9.user limit 3; 查询db9.user这个表的前3行
-> create table if not exists db9.a(id int); 如果这个表不存在的话，就创建db9.a这个表
-> end 结束
-> // 打//代表结束这个存储过程。

mysql> delimiter ; 指定；作为命令的结束符号

mysql> call p1(); 执行p()这个存储过程
+----+--------+----------+---------+------+---------+---------+---------------+
| id | name | password | uid | gid | comment | homedir | shell | 这里显示p()这个存储过程里定义的查询命令
+----+--------+----------+---------+------+---------+---------+---------------+
| 1 | admin | x | 0 | 0 | root | /root | /bin/bash |
| 2 | bin | x | 5000000 | 1 | bin | /bin | /sbin/nologin |
| 3 | daemon | x | 2 | 2 | daemon | /sbin | /sbin/nologin |
+----+--------+----------+---------+------+---------+---------+---------------+
3 rows in set (0.01 sec)

Query OK, 0 rows affected (0.29 sec) 这里显示p()这个存储过程里定义的创建db9.a这个表，成功创建之后的输出信息。

mysql> show tables; 我们可以查询得到db9.a这个表已经创建成功了
+---------------+
| Tables_in_db9 |
+---------------+
| a |
... ...

mysql> drop procedure p1; 删除p1这个存储过程
###############################################################################################
案例：创建存储过程名为p1，功能是显示db9.user这个表里shell是/bin/bash的用户个数，然后调用存储过程p1

mysql> delimiter //
mysql> create procedure p1()
-> begin
-> select count(name) from db9.user where shell="/bin/bash"; 计算db9.user表里shell是/bin/bash的用户个数
-> end
-> //

mysql> delimiter ;
mysql> call p1();
+-------------+
| count(name) |
+-------------+
| 4 | 得出db9.user这个表里shell是/bin/bash的用户共有4个
+-------------+
#########################################################################################
mysql> select name,body from mysql.proc where name="p1"; 我们定义的存储过程，全都存放在mysql.proc这个表里
+------+---------------------------------------------------------------------+
| name | body |
+------+---------------------------------------------------------------------+
| p1 | begin
select count(name) from db9.user where shell="/bin/bash"; body这字段里存储的是我们定义的存储过程p1里的命令
end |
+------+---------------------------------------------------------------------+

我们定义的存储过程，全都存放在mysql.proc这个表里。
mysql> select * from mysql.proc where name="p1"\G; 查看p1这个存储过程的所有详细信息
*************************** 1. row ***************************
db: db9 p1这个存储过程是在哪个库里面定义的
name: p1
type: PROCEDURE 类型是存储过程
specific_name: p1
language: SQL
sql_data_access: CONTAINS_SQL
is_deterministic: NO
security_type: DEFINER
param_list:
returns:
body: begin body这个字段里存储的就是我们定义的存储过程p1里的命令
select count(name) from db9.user where shell="/bin/bash";
end
definer: root@localhost
created: 2018-11-27 16:02:01 p1这个存储过程是什么时候创建的
modified: 2018-11-27 16:02:01
sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
comment:
character_set_client: utf8
collation_connection: utf8_general_ci
db_collation: latin1_swedish_ci
body_utf8: begin
select count(name) from db9.user where shell="/bin/bash";
end
###########################################################################################
变量类型：

系统变量(包括: 会话和全局变量)，使用set命令定义。

会话变量的修改,只会影响到"当前的会话"。
全局变量的修改,会影响到"整个服务器"。

mysql> select @@hostname;
+------------+
| @@hostname |
+------------+
| mysql55 |
+------------+

用户变量：在客户端连接到数据库服务的整个过程中都是有效的。在当前连接断开后，所有用户变量失效。
定义: set @变量名=值；
输出: select @变量名；

mysql> set @a=500;
mysql> select @a;
+------+
| @a |
+------+
| 500 |
+------+

局部变量: 只能用在存储过程中的begin/end中间。执行存储变量完成之后，变量就失效了。
declare专门用来定义局部变量

注意！！！调用局部和参数变量时，变量名前不要加@
############################################################################################
mysql> show global variables\G; 查看全部变量
mysql> show session variables\G; 查看会话变量

mysql> set session sort_buffer_size = 40000; 设置会话变量
mysql> show session variables like "sort_buffer_size";
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| sort_buffer_size | 40000 |
+------------------+-------+

mysql> show global variables like "%关键字%"; 查看包含password这个单词的所有全局变量
mysql> show global variables like "%password%";
+---------------------------------------+-------+
| Variable_name | Value |
+---------------------------------------+-------+
| default_password_lifetime | 0 |
| disconnect_on_expired_password | ON |
| log_builtin_as_identified_by_password | OFF |
| mysql_native_password_proxy_users | OFF |
| old_passwords | 0 |
| report_password | |
| sha256_password_proxy_users | OFF |
| validate_password_check_user_name | OFF |
| validate_password_dictionary_file | |
| validate_password_length | 6 |
| validate_password_mixed_case_count | 1 |
| validate_password_number_count | 1 |
| validate_password_policy | LOW |
| validate_password_special_char_count | 1 |
+---------------------------------------+-------+

mysql> set @w=3; 自定义变量w=3
mysql> select @w; 可以看到变量w=3
+------+
| @w |
+------+
| 3 |
+------+

mysql> select max(uid) from user; 查看到user中最大的uid值是5000000
+----------+
| max(uid) |
+----------+
| 5000000 |
+----------+

mysql> select max(uid) into @w from user; 使用sql命令把查询结果赋值给w。把user中最大的uid值赋值给w这个变量
mysql> select @w; 可以看到变量已经变成了最新的，w=5000000
+---------+
| @w |
+---------+
| 5000000 |
+---------+
#############################################################################################
只影响当前的连接时，本次会话生效。
session会话
procedure过程
declare 宣布;声明
全局变量，前面都用@@，一共2个@来表示。如@@hostname

mysql> select @x,@y; 先查看变量x和y是否有值
+------+------+
| @x | @y |
+------+------+
| NULL | NULL | 可以看到目前变量x和y的值是空的
+------+------+

mysql> delimiter //
mysql> create procedure db9.p2()
-> begin
-> declare x int default 9; 定义局部变量x
-> declare y char(10); 定义局部变量y
-> set y="jim"; 把y的值设置位jim
-> select x; select y; 输出变量x和y的值
-> end
-> //

mysql> delimiter ;
mysql> call db9.p2();
+------+
| x |
+------+
| 9 | 此时变量x的值是9
+------+

+------+
| y |
+------+
| jim | 此时变量y的值是jim
+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec
#####################################################################################################
把查询结果赋值给局部变量
当变量名同名时，后赋值的生效
如果要定义多个变量，一定要一次性都定义完了之后，才能够输出值，而且变量名前不能加@。比如不能写select @y; 只能写select y;

mysql> delimiter //
mysql> create procedure db9.p3() 创建存储过程p3()的时候，最好指定库名！db9.p3()
-> begin
-> declare y int default 1; 先赋值给y=1
-> select y; 输出y的值，也就是1
-> select count(id) into y from db9.user; 然后把从db9.user这个表里面的所有行的总数，赋值给y
-> select y; 输出y的值，也就是查询的结果。这次定义的y是最后一次定义的，所以以这次的y的值为准。
-> end
-> //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> call db9.p3();
+------+
| y |
+------+
| 1 | 第一次定义的y的值是1
+------+
1 row in set (0.00 sec)

+------+
| y |
+------+
| 43 | db9.user这个表里面的所有行的总数是43，也就是这次的y的值
+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)
####################################################################################################
参数类型

调用参数时，名称前不要加@
create procedure 名称(
类型参数名数据类型，
类型参数名数据类型
)

关键字
in 指输入参数作用是给存储过程传值，必须在调用存储过程时赋值，在存储过程中此参数的值不允许修改；默认类型是in
out 指输出参数这个值可以在存储过程内部被改变，并可返回。
inout 指输入/输出参数调用时指定，并且可以被改变和返回
####################################################################################################
局部变量和参数变量不要加@
只有用户自定义变量才加@
in类型，一定要有值输入！

案例：存储过程参数的使用
创建名为p6的存储过程
可以接受用户输入的shell的名字
统计user表中用户输入shell名字的个数

mysql> delimiter //
mysql> create procedure db9.p6(in shellname char(30)) 定义in类型的参数变量shellname
-> begin
-> declare x int default 9; 如果这样不写x的默认值是9也可以，也能得到一样的结果。如declare x int;
-> select count(name) into x from db9.user where shell=shellname;
-> select x;
-> end
-> //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> call db9.p6(); in类型一定要有值输入！否则会报错！
ERROR 1318 (42000): Incorrect number of arguments for PROCEDURE db9.p6; expected 1, got 0

mysql> call db9.p6("/bin/bash"); 直接赋值/bin/bash给shellname
+------+
| x |
+------+
| 4 |
+------+

mysql> call db9.p6("/sbin/nologin"); 直接赋值/sbin/nologin给shellname
+------+
| x |
+------+
| 35 |
+------+

mysql> set @x="/bin/bash"; 自定义变量x="/bin/bash"
mysql> select @x; 查看到变量x的值是/bin/bash
+-----------+
| @x |
+-----------+
| /bin/bash |
+-----------+

mysql> call db9.p6(@x); 调用自定义的变量x
+------+
| x |
+------+
| 4 |
+------+
##############################################################################################################
out类型要用变量来占位，不管变量有值没值。因为out只用来输出
inout类型也要用变量来占位

mysql> delimiter //
mysql> create procedure db9.p7(in shellname char(30),out number int)
-> begin
-> select count(name) into number from db9.user where shell=shellname;
-> select number;
-> end
-> //

mysql> delimiter ;
mysql> set @z=7;
mysql> select @z;
+------+
| @z |
+------+
| 7 |
+------+

mysql> call db9.p7("/bin/bash",@z); 一定要用变量来占位
+--------+
| number |
+--------+
| 4 |
+--------+
###################################################################################################
mysql> delimiter //
mysql> create procedure db9.p8(inout x char(30))
-> begin
-> select x;
-> select name into x from db9.user where id=1;
-> select x;
-> end
-> //

mysql> set @y="abc";
mysql> call db9.p8(@y);
+------+
| x |
+------+
| abc |
+------+

mysql> call db9.p8(@y);
+------+
| x |
+------+
| abc |
+------+
1 row in set (0.00 sec)

+-------+
| x |
+-------+
| admin |
+-------+
########################################################
mysql> set @z=1+2;
Query OK, 0 rows affected (0.00 sec)

mysql> select @z;
+------+
| @z |
+------+
| 3 |
+------+
1 row in set (0.00 sec)

mysql> set @a=1;set @b=2;set @c=@a+@b;select @c;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

+------+
| @c |
+------+
| 3 |
+------+
1 row in set (0.00 sec)

mysql> set @u=10/3;select @u;
Query OK, 0 rows affected (0.00 sec)

+-------------+
| @u |
+-------------+
| 3.333333333 |
+-------------+
1 row in set (0.00 sec)

mysql> set @u=10 DIV 3;select @u;
Query OK, 0 rows affected (0.00 sec)

+------+
| @u |
+------+
| 3 |
+------+
1 row in set (0.00 sec)

mysql> set @u=10%3;select @u;
Query OK, 0 rows affected (0.00 sec)

+------+
| @u |
+------+
| 1 |
+------+
1 row in set (0.00 sec)

mysql> set @t=3*5;select @t;
Query OK, 0 rows affected (0.00 sec)

+------+
| @t |
+------+
| 15 |
+------+
1 row in set (0.00 sec)

mysql> set @w=2+2;select @w;
Query OK, 0 rows affected (0.00 sec)

+------+
| @w |
+------+
| 4 |
+------+
1 row in set (0.00 sec)

mysql> set @w=2-2;select @w;
Query OK, 0 rows affected (0.00 sec)

+------+
| @w |
+------+
| 0 |
+------+
1 row in set (0.00 sec)

mysql> set @p=2-2;select @p;
Query OK, 0 rows affected (0.00 sec)

+------+
| @p |
+------+
| 0 |
+------+
1 row in set (0.00 sec)

mysql>
########################################################
mysql> delimiter //
mysql> create procedure db9.p10(in bash char(30),in nologin char(30))
-> begin
-> declare x int;
-> declare y int;
-> declare z int;
-> set x = 0; 这行可以不写，不给x值也可以，因为后面会把查询到的shell=变量bash的用户个数的结果赋值给x
-> set y = 0; 这行可以不写，不给x值也可以，因为后面会把查询到的shell=变量nologin的用户个数的结果赋值给y
-> select count(name) into x from db9.user where shell=bash;
-> select count(name) into y from db9.user where shell=nologin;
-> set z = x + y;
-> select z;
-> end
-> //

mysql> delimiter ;

mysql> call db9.p10("/bin/bash","/sbin/nologin");
+------+
| z |
+------+
| 39 |
+------+
############################################################
这部就是省略了定义set x = 0;和set y = 0; 的存储过程，也能成功执行

mysql> delimiter //
mysql> create procedure db9.p80(in bash char(30),in nologin char(30))
-> begin
->
-> declare x int;
-> declare y int;
-> declare z int;
-> select count(name) into x from db9.user where shell=bash;
-> select count(name) into y from db9.user where shell=nologin;
-> set z = x + y;
-> select z;
-> end
-> //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call db9.p80("/bin/bash","/sbin/nologin");
+------+
| z |
+------+
| 39 |
+------+

#############################################################
mysql> delimiter //
mysql> create procedure db9.p100()
-> begin
-> declare z int;
-> select count(name) into z from db9.user where shell="/bin/bash" or shell="/sbin/nologin";
-> select z;
-> end
-> //

mysql> delimiter //
mysql> create procedure db9.p101()
-> begin
-> declare x int;
-> declare y int;
-> declare z int;
-> select count(name) into x from db9.user where shell="/bin/bash";
-> select count(name) into y from db9.user where shell="/sbin/nologin";
-> set z = x + y;
-> select z;
-> end
-> //

mysql> delimiter ;
mysql> call db9.p101;
+------+
| z |
+------+
| 39 |
+------+

########################################################
mysql> delimiter //
mysql> create procedure db9.p13(in linenum int)
-> begin
-> if 1=linenum then
-> select * from db9.user where id=1;
-> else
-> select * from db9.user where id=2;
-> end if;
-> end
-> //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call db9.p13(1);
+----+-------+----------+------+------+---------+---------+-----------+
| id | name | password | uid | gid | comment | homedir | shell |
+----+-------+----------+------+------+---------+---------+-----------+
| 1 | admin | x | 0 | 0 | root | /root | /bin/bash |
+----+-------+----------+------+------+---------+---------+-----------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> call db9.p13(7);
+----+------+----------+---------+------+---------+---------+---------------+
| id | name | password | uid | gid | comment | homedir | shell |
+----+------+----------+---------+------+---------+---------+---------------+
| 2 | bin | x | 5000000 | 1 | bin | /bin | /sbin/nologin |
+----+------+----------+---------+------+---------+---------+---------------+
########################################################
mysql> create procedure db9.p11()
-> begin
-> if 1=1 then
-> select * from db9.user limit 3;
-> end if;
-> end
-> //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call db9.p11;
+----+--------+----------+---------+------+---------+---------+---------------+
| id | name | password | uid | gid | comment | homedir | shell |
+----+--------+----------+---------+------+---------+---------+---------------+
| 1 | admin | x | 0 | 0 | root | /root | /bin/bash |
| 2 | bin | x | 5000000 | 1 | bin | /bin | /sbin/nologin |
| 3 | daemon | x | 2 | 2 | daemon | /sbin | /sbin/nologin |
+----+--------+----------+---------+------+---------+---------+---------------+
########################################################
mysql> create procedure db9.p15()
-> begin
-> declare x int default 1;
-> while x <= 5 do
-> select * from db9.user where id=1;
-> set x = x + 1;
-> end while;
-> end
-> //
Query OK, 0 rows affected (0.00 sec)

mysql> call db9.p15;
-> ^C
mysql> delimiter ;
mysql> call db9.p15;
+----+-------+----------+------+------+---------+---------+-----------+
| id | name | password | uid | gid | comment | homedir | shell |
+----+-------+----------+------+------+---------+---------+-----------+
| 1 | admin | x | 0 | 0 | root | /root | /bin/bash |
+----+-------+----------+------+------+---------+---------+-----------+
1 row in set (0.00 sec)

mysql> delimiter ;
mysql> call db9.p16;

无限死循环
必须ctrl+c结束。

declare 必须写在begin和end之间，而且不能写在循环里面,不管是哪个循环if，while，loop，repeat
########################################################
mysql> delimiter //
mysql> create procedure db9.p17()
-> begin
-> declare x int default 1;
-> repeat
-> select * from db9.user where id=1;
-> set x = x + 1;
-> until x >= 3 until后面不能有分号；
-> end repeat;
-> end
-> //

mysql> delimiter ;
mysql> call db9.p17;
+----+-------+----------+------+------+---------+---------+-----------+
| id | name | password | uid | gid | comment | homedir | shell |
+----+-------+----------+------+------+---------+---------+-----------+
| 1 | admin | x | 0 | 0 | root | /root | /bin/bash |
+----+-------+----------+------+------+---------+---------+-----------+
1 row in set (0.00 sec)

+----+-------+----------+------+------+---------+---------+-----------+
| id | name | password | uid | gid | comment | homedir | shell |
+----+-------+----------+------+------+---------+---------+-----------+
| 1 | admin | x | 0 | 0 | root | /root | /bin/bash |
+----+-------+----------+------+------+---------+---------+-----------+
########################################################
mysql> delimiter //
mysql> create procedure db9.p100()
-> begin
-> declare x int default 1;
-> repeat
-> select * from db9.user where id=1;
-> until x >= 3
-> set x = x + 1;
-> end repeat;
-> end
-> //
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'set x = x + 1;
end repeat;
end' at line 7
mysql> create procedure db9.p100() begin declare x int default 1; repeat select * from db9.user where id=1; until x >= 3 set x = x + 1 end repeat; end//
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'set x = x + 1 end repeat; end' at line 1
########################################################
mysql> delimiter //
mysql> create procedure db9.p19()
-> begin
-> declare x int default 1;
-> while x <=10 do
-> select x;
-> set x = x + 1;
-> end while;
-> end
-> //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call db9.p19;
+------+
| x |
+------+
| 1 |
+------+
1 row in set (0.00 sec)

+------+
| x |
+------+
| 2 |
+------+
1 row in set (0.00 sec)

+------+
| x |
+------+
| 3 |
+------+
1 row in set (0.00 sec)

+------+
| x |
+------+
| 4 |
+------+
1 row in set (0.00 sec)

+------+
| x |
+------+
| 5 |
+------+
1 row in set (0.00 sec)

+------+
| x |
+------+
| 6 |
+------+
1 row in set (0.01 sec)

+------+
| x |
+------+
| 7 |
+------+
1 row in set (0.01 sec)

+------+
| x |
+------+
| 8 |
+------+
1 row in set (0.01 sec)

+------+
| x |
+------+
| 9 |
+------+
1 row in set (0.01 sec)

+------+
| x |
+------+
| 10 |
+------+
##################################################################
mysql> delimiter //
mysql> create procedure db9.p20()
-> begin
-> declare x int default 1;
-> loab1:while x <=10 do
-> iterate loab1;
-> select x;
-> set x = x + 1;
-> end while;
-> end
-> //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call db9.p20;

它就一直卡在这里，因为一直跳过
它是跳过本次循环的意思，开始下一次循环。
#################################################################
mysql> delimiter //
mysql> create procedure db9.p21()
-> begin
-> declare x int default 1;
-> loab1:while x <= 10 do
-> if x = 3 then
-> leave loab1;
-> end if;
-> select x;
-> set x = x + 1;
-> end while;
-> end
-> //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call db9.p21;
+------+
| x |
+------+
| 1 |
+------+
1 row in set (0.00 sec)

+------+
| x |
+------+
| 2 |
+------+

它是符合条件就直接结束整个循环
#####################################################################
mysql> delimiter //
mysql> create procedure db9.p22()
-> begin
-> declare x int default 1;
-> loab1:while x <= 10 do 只要小于等于10
-> if x = 3 then 从1到10，跳过了3，其他的数都会输出。即只输出1，2，4，5，6，7，8，9，10
-> set x = x + 1;
-> iterate loab1;
-> end if;
-> select x;
-> set x = x + 1;
-> end while;
-> end
-> //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call db9.p22;
+------+
| x |
+------+
| 1 |
+------+
1 row in set (0.00 sec)

+------+
| x |
+------+
| 2 |
+------+
1 row in set (0.00 sec)

+------+
| x |
+------+
| 4 |
+------+
1 row in set (0.00 sec)

+------+
| x |
+------+
| 5 |
+------+
1 row in set (0.00 sec)

+------+
| x |
+------+
| 6 |
+------+
1 row in set (0.00 sec)

+------+
| x |
+------+
| 7 |
+------+
1 row in set (0.00 sec)

+------+
| x |
+------+
| 8 |
+------+
1 row in set (0.00 sec)

+------+
| x |
+------+
| 9 |
+------+
1 row in set (0.00 sec)

+------+
| x |
+------+
| 10 |
+------+
#########################################################################################
统计用户的uid是偶数的个数。(一定要确保id是连续的，不能断)

mysql> select id from db9.user;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
| 11 |
| 12 |
| 13 |
| 14 |
| 15 |
| 16 |
| 17 |
| 18 |
| 19 |
| 20 |
| 21 |
| 22 |
| 23 |
| 24 |
| 25 |
| 26 |
| 27 |
| 28 |
| 29 |
| 30 |
| 31 |
| 32 |
| 33 |
| 34 |
| 35 |
| 36 |
| 37 |
| 38 |
| 39 |
| 40 |
| 41 |
| 42 |
| 43 |
| 44 |
| 45 |
+----+

mysql> delimiter //
mysql> create procedure db9.sum2user()
-> begin
-> declare sumline int;
-> declare x int;
-> declare i int;
-> declare j int;
-> set x = 1;
-> set i = 0;
-> set j = 0;
-> select count(uid) into sumline from db9.user;
-> while x <= sumline do
-> select uid into j from db9.user where id=x;
-> if j % 2 = 0 then
-> set i = i + 1;
-> end if;
-> set x = x + 1;
-> end while;
-> select i;
-> end
-> //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call db9.sum2user;
+------+
| i |
+------+
| 26 |
+------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

mysql> insert into db9.user(name,uid) values("haha",1002);
Query OK, 1 row affected (0.05 sec)

mysql> call db9.sum2user;
+------+
| i |
+------+
| 27 |
+------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

mysql> insert into db9.user(name,uid) values("haha1",1001);
Query OK, 1 row affected (0.05 sec)

mysql> call db9.sum2user;
+------+
| i |
+------+
| 27 |
+------+
###################################################################
写一个存储过程，通过调用变量来删除一个表

mysql> delimiter //
mysql> create procedure droptab1(in tabname char(10)) 定义一个存储过程droptab1，用局部变量tabname来存储表名，调用时我们给一个表名作为参数
-> begin
-> declare tab_name char(30); 定义一个局部变量 tab_name，是字符类型的
-> declare d_name char(30); 定义一个局部变量 d_name，是字符类型的
-> set tab_name=tabname; 局部变量 tab_name 用来存储局部变量 tabname ，也就是用户输入的那个表名
-> set d_name=concat("drop table db5.",tab_name); 局部变量 d_name 用来存储不同的字符拼接组成的字符串，也就是drop table db5.(自定义变量tab_name)
-> set @x=d_name; 定义一个自定义变量 @x ，用来存储局部变量 d_name
-> select @x; 输出自定义变量 @x的值。这行可以省略，写这句只是让我们更清楚地看到，我们将要执行的命令是什么。
-> prepare abc from @x; 创建一个mysql命令 abc，这个命令执行时，就是执行自定义变量 @x的值。固定格式prepare ... from ...;
-> execute abc; 执行我们刚定义的mysql命令 abc。固定格式execute ...;
-> deallocate prepare abc; 取消我们刚定义的mysql命令 abc。固定格式deallocate prepare ...;
-> end
-> //

mysql> delimiter ;

mysql> use db5; 进入到db5库
mysql> show tables; 我们可以看到，db5库里一共有3个表，分别是a，b，c
+---------------+
| Tables_in_db5 |
+---------------+
| a |
| b |
| c |
+---------------+

mysql> call droptab1("c"); 我们在括号里输入了c，那么我们将要执行的命令是：删除db5.c这个表。drop table db5.c
+------------------+
| @x |
+------------------+
| drop table db5.c |
+------------------+

mysql> show tables; 我们可以看到，db5库里一共有2个表，分别是a，b。说明成功执行了刚才存储的命令：删除表c。
+---------------+
| Tables_in_db5 |
+---------------+
| a |
| b |
+---------------+

mysql> call droptab10("b"); 我们在括号里输入了b，那么我们将要执行的命令是：删除db5.b这个表。drop table db5.b
+------------------+
| @x |
+------------------+
| drop table db5.b |
+------------------+

mysql> show tables; 我们可以看到，db5库里一共有3个表，分别是a。说明成功执行了刚才存储的命令：删除表b。
+---------------+
| Tables_in_db5 |
+---------------+
| a |
+---------------+

mysql> call droptab10("a"); 我们在括号里输入了a，那么我们将要执行的命令是：删除db5.a这个表。drop table db5.a
+------------------+
| @x |
+------------------+
| drop table db5.a |
+------------------+

mysql> show tables; 我们可以看到，db5库已经空了，没有表格了。说明成功执行了刚才存储的命令：删除表c。都已经删除完了。
Empty set (0.00 sec)

posted @ 2019-04-30 22:31 安于夏阅读(614) 评论(0) 编辑收藏举报

刷新页面返回顶部

存储过程

公告