mysql 存储过程
一,什么是存储过程:
简单的说,就是一组SQL语句集,功能强大,可以实现一些比较复杂的逻辑功能,类似于JAVA语言中的方法;
存储过程跟触发器有点类似,都是一组SQL集,但是存储过程是主动调用的,且功能比触发器更加强大,触发器是某件事触发后自动调用;
二,有哪些特性:
有输入输出参数,可以声明变量,有if/else, case,while等控制语句,通过编写存储过程,可以实现复杂的逻辑功能;
函数的普遍特性:模块化,封装,代码复用;
速度快,只有首次执行需经过编译和优化步骤,后续被调用可以直接执行,省去以上步骤;
三,创建存储过程:mysql>delimiter // /初始化结束符为‘//’
mysql>create procedure 方法名() /定义方法名
->begin /开始关键字
->执行语句; /执行语句
->end // /结束关键字 结束符号为‘//’
mysql>delimiter ; /重新初始化结束符为‘;’
mysql>call 方法名(); /调用方法
mysql>delimiter // /修改sql语句结束符为双斜杠‘//’ mysql>create procedure proc() /创建proc()程序 ->begin /创建开始关键字 ->select * from student; /执行语句 ->end /创建结束关键字 -> // /双斜杠结束符 Query OK,0 rows affected (0.00 sec) /创建成功提示 mysql>call proc(); /调用上面创建的proc()程序 -> // /双斜杠结束符 +----+------+------+--------+ /运行结果 | id | name | age | gender | +----+------+------+--------+ | 1 | 小强 | 12 | 男 | | 2 | 小丽 | 13 | 女 | | 3 | 小芳 | 18 | 女 | | 4 | 小王 | 13 | 男 | | 5 | 小苟 | 19 | 男 | +----+------+------+--------+ 5 rows in set (0.00 sec)
1, 在存储过程中定义并使用变量:declare 变量[default 100] ; /定义变量[初始化变量值]
set 变量=值; /给变量赋值
mysql>drop procedure proc; /删除上一个proc()方法,*注意proc后面没有括号*
mysql>delimiter // /修改sql语句结束符为双斜杠‘//’ mysql>create procedure proc() /创建proc()程序 ->begin /创建开始关键字
->declare var int default 100; /定义int变量并赋初始值为100 ->select var; /输出var
->set var=var+10; /设置var值自增10
->select var; /再次输出var
->end /创建结束关键字 -> // /双斜杠结束符 Query OK,0 rows affected (0.00 sec) /创建成功提示
mysql>delimiter ; /将结束符修改为分号‘;’ mysql>call proc(); /调用上面创建的proc()程序
+------+ /输出结果
| var |
+------+
| 100 |
+------+
1 row in set (0.00 sec)
+------+
| var |
+------+
| 110 |
+------+
1 row in set (0.03 sec)
使用变量: declare 变量; /声明变量
执行语句1的值 into 变量; /将执行语句1的值 传给 变量
mysql> delimiter // mysql> create procedure proc1() -> begin -> declare var1 char(20); -> declare var2 int; -> select name,age into var1,var2 from student where id=1;/分别用变量var1和var2接收name和age的值 -> select var1 as '姓名',var2 as '年龄';/输出var1,var2的值 -> end // Query OK, 0 rows affected (0.00 sec) mysql> delimiter ; mysql> call proc1(); +------+------+ | 姓名 | 年龄 | +------+------+ | 小强 | 12 | +------+------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.03 sec)
2,游标的使用:declare 游标名 cursor for 执行语句1; /声明一个游标,并将执行语句1的值传给游标(值可能不只一个)
open 游标名; /开启游标(关键字open)
fetch 游标名 into 变量1,···; /使用游标(关键字fetch):将游标中的值传给其他变量(值应与变量一一对应)
close 游标名; /关闭游标(关键字close)
mysql> delimiter // mysql> create procedure proc2() -> begin -> declare var1 char(20);/变量的声明 -> declare var2 int; /声明游标,并将查询的结果保存到游标中(游标必须声明在变量声明的后面) -> declare getData_Stu cursor for select name,age from student where id=2; -> open getData_Stu;/打开游标 -> fetch getData_Stu into var1,var2;/将游标中的值传给变量(使用游标) -> close getData_Stu;/关闭游标 -> select var1 as '姓名',var2 as '年龄';/输出变量中的值 -> end // Query OK, 0 rows affected (0.00 sec) mysql> delimiter ; mysql> call proc2(); +------+------+ | 姓名 | 年龄 | +------+------+ | 小丽 | 13 | +------+------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.02 sec)
3,存储过程中的流程控制语句:
a,if 语句:if 判断语句 then 执行语句1; /当判断语句为真,则运行执行语句1
else 执行语句2; /当判断语句为假,则运行执行语句2
end if; /关闭if语句
mysql> delimiter // mysql> create procedure proc3() -> begin -> declare var int default 10;/定义一个var变量并赋给初始值10 -> if var is null then select 'var is null';/判断var是否为null,如果是,则输出‘var is null’ -> else select 'var is not null';/如果不是,则输出‘var is not null’ -> end if;/关闭if判断语句 -> end // Query OK, 0 rows affected (0.00 sec) mysql> delimiter ; mysql> call proc3(); +-----------------+ /结果 | var is not null | +-----------------+ | var is not null | +-----------------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.02 sec)
if else嵌套使用:需要注意的是 elseif 是连着写的
mysql> delimiter //
mysql> create procedure print(a int)
-> begin
-> if a<0 then select 'a<0 is error!';
-> elseif a>4 then select 'a>4 is error!';
-> else select * from student where id=a;
-> end if;
-> end //
Query OK, 0 rows affected (0.00 sec)
b,case 语句:case 变量 /读取变量的值
when 值1 then 语句1; /当变量的值为值1时,执行语句1
when 值2 then 语句2; /当变量的值为值2时,执行语句2
···;
else 语句; /变量的值不包含在以上情况时,执行else的语句
end case; /关闭case语句
mysql> create procedure proc4(in a int) -> begin -> case a /a为传入的变量 -> when 1 then select * from student where id=1; /当变量a的值为1时,执行1后面的语句,下同 -> when 2 then select * from student where id=2; -> when 3 then select * from student where id=3; -> else select * from student; /当变量a的值不包含在上面的情况中时,执行else后面的语句 -> end case; /关闭case语句 -> end // Query OK, 0 rows affected (0.00 sec) mysql> delimiter ; mysql> call proc4(1); +----+------+------+--------+ | id | name | age | gender | +----+------+------+--------+ | 1 | 小强 | 12 | 男 | +----+------+------+--------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.02 sec) mysql> call proc4(2); +----+------+------+--------+ | id | name | age | gender | +----+------+------+--------+ | 2 | 小丽 | 13 | 女 | +----+------+------+--------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.04 sec) mysql> call proc4(3); +----+------+------+--------+ | id | name | age | gender | +----+------+------+--------+ | 3 | 小芳 | 18 | 女 | +----+------+------+--------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.02 sec) mysql> call proc4(5); +----+------+------+--------+ | id | name | age | gender | +----+------+------+--------+ | 1 | 小强 | 12 | 男 | | 2 | 小丽 | 13 | 女 | | 3 | 小芳 | 18 | 女 | | 4 | 小王 | 13 | 男 | | 5 | 小苟 | 19 | 男 | +----+------+------+--------+ 5 rows in set (0.00 sec) Query OK, 0 rows affected (0.03 sec)
c,loop循环:(先执行语句然后判断条件再进行循环变量自加1)
declare 循环变量 int default 0; /定义并初始化循环变量
循环体名称:loop 执行语句; /声明循环体并执行语句
if 判断条件 then leave 循环体名称; /判断循环条件,成立则退出循环
end if; /结束if语句
set 循环变量=循环变量+1; /此处不支持:a++语法
end loop; /结束loop语句
mysql> delimiter // mysql> create procedure proc() -> begin -> declare a int default 1; /定义一个循环变量,并赋初始值为1 -> myloop:loop insert student values(null,'小乐',20,'男');/声明一个叫myloop的loop循环体 -> if a>3 then leave myloop; /循环条件的判断,成立则退出循环 -> end if; /结束if语句 -> set a=a+1; /循环变量自加1后进入下一次循环 -> end loop; /结束loop语句 -> end // Query OK, 0 rows affected (0.00 sec) mysql> delimiter ; mysql> call proc(); Query OK, 1 row affected (0.00 sec) mysql> select * from student; +----+------+------+--------+ | id | name | age | gender | +----+------+------+--------+ | 1 | 小强 | 12 | 男 | | 2 | 小丽 | 13 | 女 | | 3 | 小芳 | 18 | 女 | | 4 | 小王 | 13 | 男 | | 5 | 小苟 | 19 | 男 | | 6 | 小乐 | 20 | 男 | | 7 | 小乐 | 20 | 男 | | 8 | 小乐 | 20 | 男 | | 9 | 小乐 | 20 | 男 | +----+------+------+--------+ 9 rows in set (0.00 sec)
d,repeat 循环语句:(相当于do_while语句)(先执行语句然后循环变量自加1再判断条件)
declare 循环变量 int default 0;
repaet 执行语句;
set 循环变量=循环变量+1;
until 判断条件 end repeat;
ysql> delimiter // mysql> create procedure proc() -> begin -> declare a int default 0; /定义并初始化循环变量 -> repeat update student set name=null,age=null,gender=null where id=(a+6); /声明repeat循环和执行语句 -> set a=a+1; /循环变量自加1 -> until a>=9 end repeat; /判断条件 -> end // Query OK, 0 rows affected (0.00 sec) mysql> delimiter ; mysql> call proc(); Query OK, 0 rows affected (0.00 sec) mysql> select * from student; +----+------+------+--------+ | id | name | age | gender | +----+------+------+--------+ | 1 | 小强 | 12 | 男 | | 2 | 小丽 | 13 | 女 | | 3 | 小芳 | 18 | 女 | | 4 | 小王 | 13 | 男 | | 5 | 小苟 | 19 | 男 | | 6 | NULL | NULL | NULL | | 7 | NULL | NULL | NULL | | 8 | NULL | NULL | NULL | | 9 | NULL | NULL | NULL | +----+------+------+--------+ 9 rows in set (0.00 sec)
e,while do循环:
declare 循环变量 int default 0;
while 判断条件 do 执行语句;
set 变量=变量+1;
end while;
mysql> delimiter // mysql> create procedure proc() -> begin -> declare a int default 6; /定义并初始化循环变量 -> while a<10 do update student set name='小哥',age=22,gender='女' where id=a;/声明while do语句 -> set a=a+1; /循环变量自加1 -> end while; /结束while语句 -> end // Query OK, 0 rows affected (0.00 sec) mysql> delimiter ; mysql> call proc(); Query OK, 1 row affected (0.01 sec) mysql> select * from student; +----+------+------+--------+ | id | name | age | gender | +----+------+------+--------+ | 1 | 小强 | 12 | 男 | | 2 | 小丽 | 13 | 女 | | 3 | 小芳 | 18 | 女 | | 4 | 小王 | 13 | 男 | | 5 | 小苟 | 19 | 男 | | 6 | 小哥 | 22 | 女 | | 7 | 小哥 | 22 | 女 | | 8 | 小哥 | 22 | 女 | | 9 | 小哥 | 22 | 女 | +----+------+------+--------+ 9 rows in set (0.00 sec)
四,查看存储过程:
show create procedure proc;/注意proc后面没有括号