【MySQL 视频笔记9】存储过程

在一个语言中,如pascal,有一个概念叫 “过程” procedure"函数" function
在php中,没有过程,只有函数

过程:封装了若干条语句,调用时执行这些封装体。过程类似函数,但是没有返回值

存储过程:把过程存储在数据库中

  • 创建存储过程语法:
create procedure procedureName()
begin
    -- sql语句
end$
mysql> delimiter $
mysql> create procedure p1()
    -> begin
    -> select 1+2;
    -> end$
Query OK, 0 rows affected (0.03 sec)
  • 查看存储过程语法:show procedure status
mysql> show procedure status \G
*************************** 1. row ***************************
                  Db: test2
                Name: p1
                Type: PROCEDURE
             Definer: root@localhost
            Modified: 2018-05-24 15:46:12
             Created: 2018-05-24 15:46:12
       Security_type: DEFINER
             Comment:
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.01 sec)
  • 调用存储过程语法:call procedureName
mysql> call p1()$
+-----+
| 1+2 |
+-----+
|   3 |
+-----+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.03 sec)
  • 在存储过程中,用 declare 声明变量,语法类似声明列:declare 变量名 变量类型 [default 默认值]
mysql> create procedure p2()
    -> begin
    -> declare age int default 18;
    -> declare height int default 180;
    ->
    -> select concat('年龄是',age,'身高是',height);
    -> end$
Query OK, 0 rows affected, 2 warnings (0.05 sec)

mysql> call p2()$
+--------------------------------------+
| concat('年龄是',age,'身高是',height)             |
+--------------------------------------+
| 年龄是18身高是180                                |
+--------------------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected, 2 warnings (0.03 sec)
  • 在存储过程中,变量可以在sql中合法运算,如+-*/,并且运算的结果可以赋值给变量,语法为:set 变量名 := expression
mysql> create procedure p3()
    -> begin
    -> declare age int default 18;
    -> set age := age + 20;
    -> select concat('20年后的年龄是',age);
    -> end$
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> call p3()$
+------------------------------+
| concat('20年后的年龄是',age)             |
+------------------------------+
| 20年后的年龄是38                         |
+------------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.01 sec)
  • 存储过程可以用控制结构

    if / else 控制结构

mysql> CREATE PROCEDURE p4 ()
    -> BEGIN
    ->  DECLARE age INT DEFAULT 18;
    ->  IF age >= 18 THEN
    ->          SELECT'已成年';
    ->  ELSE
    ->          SELECT '未成年';
    ->  END IF;
    -> end$
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> call p4()$
+--------+
| 已成年      |
+--------+
| 已成年      |
+--------+
1 row in set (0.00 sec)

Query OK, 0 rows affected, 2 warnings (0.02 sec)

while 控制结构

//求1~100的和

mysql> CREATE PROCEDURE p6 ()
    -> BEGIN
    ->  DECLARE total INT DEFAULT 0;
    ->  DECLARE num INT DEFAULT 0;
    ->
    ->  WHILE num < 100 DO
    ->          SET num := num + 1;
    ->          SET total := total + num;
    ->  END WHILE;
    ->  select total;
    -> end$
Query OK, 0 rows affected (0.00 sec)

mysql> call p6$
+-------+
| total |
+-------+
|  5050 |
+-------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.02 sec)

case 控制结构


create procedure p9()
begin
    declare pos int default 0;
    set  pos := floor(1+rand()*4);
    case pos 
    when 1 then select '数字1';
    when 2 then select '数字2';
    when 3 then select '数字3';
    else select '其他数字';
    end case;
end$

mysql> call p9()$
+-------+
| 数字1     |
+-------+
| 数字1     |
+-------+
1 row in set (0.00 sec)

mysql> call p9()$
+-------+
| 数字3     |
+-------+
| 数字3     |
+-------+
1 row in set (0.00 sec)

repeat 循环控制结构

mysql> create procedure p10()
    -> begin
    ->  declare sum int default 0;
    ->  declare n int default 0;
    ->  repeat
    ->          set n := n + 1;
    ->          set sum := sum + n;
    ->  until n >= 100 end repeat;
    ->  select sum;
    -> end$
Query OK, 0 rows affected (0.00 sec)

mysql> call p10()$
+------+
| sum  |
+------+
| 5050 |
+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.02 sec)
  • 存储过程的括号里可以声明参数,语法为: [ int / out / inout] 参数名 参数类型,其中
    in ,往过程里输入值
    out ,往外输出值
    inout,即可往里输入值,也可往外输出值
//普通传参(默认in)

mysql> CREATE PROCEDURE p5 (width int,height int)
    -> begin
    ->          select concat('你的面积是',width*height) as area;
    ->
    ->          if width>height then
    ->                  select '胖';
    ->          elseif width<height then
    ->                  select '瘦';
    ->          else
    ->                  select '方';
    ->          end if;
    ->
    -> end$
Query OK, 0 rows affected, 4 warnings (0.00 sec)


mysql> call p5(3,4)$
+--------------+
| area         |
+--------------+
| 你的面积是12           |
+--------------+
1 row in set (0.00 sec)

+----+
| 瘦   |
+----+
| 瘦   |
+----+

Query OK, 0 rows affected, 4 warnings (0.04 sec)
//in和out

mysql> CREATE PROCEDURE p7 (in n int,out m int)
    -> BEGIN
    ->  declare num int DEFAULT 0;
    ->  set m = 0;
    ->  while num < 10 do
    ->                  set num := n + 1;
    ->                  set m := m + num;
    ->  end while;
    -> end$
Query OK, 0 rows affected (0.00 sec)

mysql> call p7(100,@m)$
Query OK, 0 rows affected (0.00 sec)

mysql> select @m$
+------+
| @m   |
+------+
|  101 |
+------+
1 row in set (0.00 sec)
//inout 

mysql> CREATE PROCEDURE p8 (inout age int)
    -> BEGIN
    ->  set age := age + 10;
    -> end$
Query OK, 0 rows affected (0.00 sec)

//注意:直接传参会报错
mysql> call p8(24)$
ERROR 1414 (42000): OUT or INOUT argument 1 for routine test2.p8 is not a variale or NEW pseudo-variable in BEFORE trigger

//应该先在过程外设置变量并赋值,然后把变量传进去
mysql> set @curage = 11$
Query OK, 0 rows affected (0.00 sec)

mysql> call p9(@curage)$
Query OK, 0 rows affected (0.00 sec)

mysql> select @curage$
+---------+
| @curage |
+---------+
|      21 |
+---------+
1 row in set (0.00 sec)
  • cursor 游标
    游标是,1条sql对应N条结果集的资源,取出资源的接口 / 句柄
    沿着游标可以一次取出一行 (类似指针,取出一行后指向下一行)

    声明: declare 游标名 cursor for select_statement;
    打开:open 游标名
    取值:fetch 游标名 into var1,var2[,...]
    关闭:close 游标名

    注意:游标的意义在于,可以对每取出一行的数据做复杂的判断处理,不只是简单的取出数据而已

//当游标指向的行没有数据时会报错

mysql> select * from g$
+------+------+------+
| gid  | name | num  |
+------+------+------+
|    1 | cat  |   34 |
|    2 | dog  |  255 |
|    3 | pig  |  255 |
+------+------+------+
3 rows in set (0.10 sec)

mysql> create procedure p12()
    -> begin
    ->
    ->  declare row_gid int;
    ->  declare row_name varchar(20);
    ->  declare row_num int;
    ->
    ->  declare getg cursor for select gid,`name`,num from g;
    ->
    ->  open getg;
    ->
    ->  fetch getg into row_gid,row_name,row_num;
    ->  select row_gid,row_name,row_num;
    ->
    ->  fetch getg into row_gid,row_name,row_num;
    ->  select row_gid,row_name,row_num;
    ->
    ->  fetch getg into row_gid,row_name,row_num;
    ->  select row_gid,row_name,row_num;
    ->
    ->  fetch getg into row_gid,row_name,row_num;
    ->  select row_gid,row_name,row_num;
    ->
    ->  close getg;
    -> end$
Query OK, 0 rows affected (0.00 sec)

mysql> call p12()$
+---------+----------+---------+
| row_gid | row_name | row_num |
+---------+----------+---------+
|       1 | cat      |      34 |
+---------+----------+---------+
1 row in set (0.01 sec)

+---------+----------+---------+
| row_gid | row_name | row_num |
+---------+----------+---------+
|       2 | dog      |     255 |
+---------+----------+---------+
1 row in set (0.03 sec)

+---------+----------+---------+
| row_gid | row_name | row_num |
+---------+----------+---------+
|       3 | pig      |     255 |
+---------+----------+---------+
1 row in set (0.05 sec)

ERROR 1329 (02000): No data - zero rows fetched, selected, or processed
//取出表中每一行数据

mysql> create procedure p14()
    -> begin
    ->
    ->  declare row_gid int;
    ->  declare row_name varchar(20);
    ->  declare row_num int;
    ->
    ->  declare cnt int default 0;
    ->  declare i int default 0;
    ->
    ->  declare getg cursor for select gid,`name`,num from g;
    ->
    ->  select  count(*) into cnt from g;
    ->
    ->  open getg;
    ->
    ->  repeat
    ->          set i := i+1;
    ->          fetch getg into row_gid,row_name,row_num;
    ->          select row_gid,row_name,row_num;
    ->  until i >= cnt end repeat;
    ->
    ->  close getg;
    ->
    -> end$
Query OK, 0 rows affected (0.00 sec)

mysql> call p14()$
+---------+----------+---------+
| row_gid | row_name | row_num |
+---------+----------+---------+
|       1 | cat      |      34 |
+---------+----------+---------+
1 row in set (0.01 sec)

+---------+----------+---------+
| row_gid | row_name | row_num |
+---------+----------+---------+
|       2 | dog      |     255 |
+---------+----------+---------+
1 row in set (0.03 sec)

+---------+----------+---------+
| row_gid | row_name | row_num |
+---------+----------+---------+
|       3 | pig      |     255 |
+---------+----------+---------+
1 row in set (0.05 sec)

Query OK, 0 rows affected (0.07 sec)
  • handler 越界标识

    游标取值越界时可以用标识来结束

    语法为:declare continue/exit/undo handler for NOT FOUND statement;
    continue 触发后,后面的语句继续执行
    exit 触发后,后面的语句不再执行
    undo触发后,前面的语句撤销(mysql不支持)

//用 declare continue handler 来操作一个越界标识
//这里是个逻辑有问题的查询语句

mysql> create procedure p15()
    -> begin
    ->
    ->  declare row_gid int;
    ->  declare row_name varchar(20);
    ->  declare row_num int;
    ->
    ->  declare mark int default 1; -- 定义标识为1
    ->  declare getg cursor for select gid,`name`,num from g;
    ->  declare continue handler for NOT FOUND set mark := 0; -- 当repeat查
到数据时,mark将赋值为0
    ->
    ->  open getg;
    ->
    ->  repeat
    ->          fetch getg into row_gid,row_name,row_num;
    ->          select row_gid,row_name,row_num;
    ->  until mark=0 end repeat; -- 当标识为0结束循环
    ->
    ->  close getg;
    ->
    -> end$
Query OK, 0 rows affected (0.00 sec)

mysql> call p15()$
+---------+----------+---------+
| row_gid | row_name | row_num |
+---------+----------+---------+
|       1 | cat      |      34 |
+---------+----------+---------+
1 row in set (0.02 sec)

+---------+----------+---------+
| row_gid | row_name | row_num |
+---------+----------+---------+
|       2 | dog      |     255 |
+---------+----------+---------+
1 row in set (0.04 sec)

+---------+----------+---------+
| row_gid | row_name | row_num |
+---------+----------+---------+
|       3 | pig      |     255 |
+---------+----------+---------+
1 row in set (0.06 sec)

//最后一行重复取出
+---------+----------+---------+
| row_gid | row_name | row_num |
+---------+----------+---------+
|       3 | pig      |     255 |
+---------+----------+---------+
1 row in set (0.08 sec)

Query OK, 0 rows affected, 1 warning (0.09 sec)

//报错变为警告
mysql> show warnings$
+-------+------+-----------------------------------------------------+
| Level | Code | Message                                             |
+-------+------+-----------------------------------------------------+
| Error | 1329 | No data - zero rows fetched, selected, or processed |
+-------+------+-----------------------------------------------------+
1 row in set (0.00 sec)

上例问题出现在continue
当游标从第三行指向第四行时,发现没有数据,触发 handler 的 NOT FOUND
于是 mark 赋值为0,但是 handler 为 continue,语句还要往这句 handler 之后执行,于是还要查询一次最后一行
(游标已经在第四行,在NOT FOUND时,时光倒流,游标倒退一行?)

把continue改为exit即可,即不再执行后面的语句

//用 declare exit handler 来操作一个越界标识

mysql> create procedure p16()
    -> begin
    ->
    ->  declare row_gid int;
    ->  declare row_name varchar(20);
    ->  declare row_num int;
    ->
    ->  declare mark int default 1; -- 定义标识为1
    ->  declare getg cursor for select gid,`name`,num from g;
    ->  declare exit handler for NOT FOUND set mark := 0; -- 当repeat查询不到数据时,mark将赋值为0
    ->
    ->  open getg;
    ->
    ->  repeat
    ->          fetch getg into row_gid,row_name,row_num;
    ->          select row_gid,row_name,row_num;
    ->  until mark=0 end repeat; -- 当标识为0结束循环
    ->
    ->  close getg;
    ->
    -> end$
Query OK, 0 rows affected (0.00 sec)

mysql> call p16()$
+---------+----------+---------+
| row_gid | row_name | row_num |
+---------+----------+---------+
|       1 | cat      |      34 |
+---------+----------+---------+
1 row in set (0.00 sec)

+---------+----------+---------+
| row_gid | row_name | row_num |
+---------+----------+---------+
|       2 | dog      |     255 |
+---------+----------+---------+
1 row in set (0.01 sec)

+---------+----------+---------+
| row_gid | row_name | row_num |
+---------+----------+---------+
|       3 | pig      |     255 |
+---------+----------+---------+
1 row in set (0.02 sec)

Query OK, 0 rows affected, 1 warning (0.02 sec)

//警告仍然存在(可忽略?)
mysql> show warnings$
+-------+------+-----------------------------------------------------+
| Level | Code | Message                                             |
+-------+------+-----------------------------------------------------+
| Error | 1329 | No data - zero rows fetched, selected, or processed |
+-------+------+-----------------------------------------------------+
1 row in set (0.00 sec)

在循环外fetch一次也可避免最后一行查询两次的问题


mysql> create procedure p17()
    -> begin
    ->
    ->  declare row_gid int;
    ->  declare row_name varchar(20);
    ->  declare row_num int;
    ->
    ->  declare mark int default 1; 
    ->  declare getg cursor for select gid,`name`,num from g;
    ->  declare continue handler for NOT FOUND set mark := 0; 
    ->
    ->  open getg;
    ->  fetch getg into row_gid,row_name,row_num; //先在repeat外fetch一次,判断是否为NULL(如果为null则跑到not found去)
    ->
    ->  repeat
    ->          select row_gid,row_name,row_num;  //先输出列表再查询
    ->          fetch getg into row_gid,row_name,row_num;
    ->  until mark=0 end repeat; 
    ->
    ->  close getg;
    ->
    -> end$
Query OK, 0 rows affected (0.00 sec)

mysql> call p17()$
+---------+----------+---------+
| row_gid | row_name | row_num |
+---------+----------+---------+
|       1 | cat      |      34 |
+---------+----------+---------+
1 row in set (0.00 sec)

+---------+----------+---------+
| row_gid | row_name | row_num |
+---------+----------+---------+
|       2 | dog      |     255 |
+---------+----------+---------+
1 row in set (0.02 sec)

+---------+----------+---------+
| row_gid | row_name | row_num |
+---------+----------+---------+
|       3 | pig      |     255 |
+---------+----------+---------+
1 row in set (0.04 sec)

Query OK, 0 rows affected, 1 warning (0.05 sec)

mysql>

用 while 也行

mysql> create procedure p18()
    -> begin
    ->
    ->  declare row_gid int;
    ->  declare row_name varchar(20);
    ->  declare row_num int;
    ->
    ->  declare mark int default 1; 
    ->  declare getg cursor for select gid,`name`,num from g;
    ->  declare continue handler for NOT FOUND set mark := 0; 
    ->
    ->  open getg;
    ->  fetch getg into row_gid,row_name,row_num;
    ->
    ->  while mark = 1 do   //repeat改为while
    ->          select row_gid,row_name,row_num;
    ->          fetch getg into row_gid,row_name,row_num;
    ->  end while; 
    ->
    ->  close getg;
    ->
    -> end$
Query OK, 0 rows affected (0.00 sec)

mysql> call p18()$
+---------+----------+---------+
| row_gid | row_name | row_num |
+---------+----------+---------+
|       1 | cat      |      34 |
+---------+----------+---------+
1 row in set (0.00 sec)

+---------+----------+---------+
| row_gid | row_name | row_num |
+---------+----------+---------+
|       2 | dog      |     255 |
+---------+----------+---------+
1 row in set (0.02 sec)

+---------+----------+---------+
| row_gid | row_name | row_num |
+---------+----------+---------+
|       3 | pig      |     255 |
+---------+----------+---------+
1 row in set (0.03 sec)

Query OK, 0 rows affected, 1 warning (0.04 sec)
发布了204 篇原创文章 · 获赞 21 · 访问量 14万+
展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 编程工作室 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览