您的位置:新葡亰496net > 网络数据库 > 新葡亰496netMysql学习笔记,Mysql的游标的概念使用

新葡亰496netMysql学习笔记,Mysql的游标的概念使用

发布时间:2019-10-20 23:45编辑:网络数据库浏览(136)

    Procedure & Function

    Procedure 语法:

    CREATE
        [DEFINER = { user | CURRENT_USER }]
        PROCEDURE sp_name ([proc_parameter[,...]])
        [characteristic ...] routine_body
    
    
    proc_parameter:
        [ IN | OUT | INOUT ] param_name type
    
    type:
        Any valid MySQL data type
    
    characteristic:
        COMMENT 'string'
      | LANGUAGE SQL
      | [NOT] DETERMINISTIC
      | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
      | SQL SECURITY { DEFINER | INVOKER }
    
    begin
        Valid SQL routine statement
    end;
    

     

    Function 语法:

    CREATE
        [DEFINER = { user | CURRENT_USER }]
        FUNCTION sp_name ([func_parameter[,...]])
        RETURNS type
        [characteristic ...] routine_body
    
    func_parameter:
        param_name type
    
    type:
        Any valid MySQL data type
    
    characteristic:
        COMMENT 'string'
      | LANGUAGE SQL
      | [NOT] DETERMINISTIC
      | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
      | SQL SECURITY { DEFINER | INVOKER }
    
    begin
        Valid SQL routine statement
    end;
    

    官网关于 procedure, function相关文档:

      FAQ:

      语法说明:

     

    Mysql 学习笔记(六)

    Mysql从5.0开始支持存储过程和trigger,给我们喜欢用mysql的朋友们更喜欢mysql的理由了,语法上和PL/SQL有差别,不过搞过编程的人都知道,语法不是问题,关键是思想,大致了解语法后,就从变量定义,循环,判断,游标,异常处理这个几个方面详细学习了。关于游标的用法Mysql现在提供的还很特别,虽然使用起来没有PL/SQL那么顺手,不过使用上大致上还是一样,

    Cursor

    Cursor官方文档:

    在遍历时,mysql中的3种循环方式(loop, while, repeat)都可以使用。官方文档中给了 loop 方式的deamo。

    在使用cursor时要注意:

    1)declare cursor之前不能有任何的除了declare以外的操作,也就是之前只能有变量声明。

    2)declar cursor 之后不能有任何变量的声明,可以声明异常处理 handler。

    3)cursor 只能在procedure, function中。

    4)fetch into var1, var2。这里的var名不能与 declare cursor时select 中的列名一样。如果一样会fetch 到NULL。例如下面deamon中的 metric ==> m 。

    新葡亰496net 1

    其它的deamon:

     

    游标(Cursor)

    是系统为用户开设的一个数据缓冲区,存放 SQL 语句的执行结果。每个游标区都有 一个名字。用户可以用 SQL 语句逐一从游标中获取记录,并赋给主变量,交由主语言进一 步处理。

    游标的特性:

    ➢ READ ONLY 只读,只能取值而不能赋值;

    ➢ NOT SCROOLABLE 不可回滚,只能顺序读取;

    ➢ ASENSITIVE 敏感,不能在已经打开游标的表上执行 update 事务;

    游标操作:

    ➢ 声明游标: DECLARE cursor_name CURSOR FOR select_statement 这个语句声明一个光标。也可以在子程序中定义多个光标,但是一个 块中的每一个光标必须有唯一的名字。

    ➢ 打开游标: OPEN cursor_name 游标 FETCH:FETCH cursor_name INTO var_name [, var_name] ... 这个语句用指定的打开光标读取下一行(如果有下一行的话),并且前进光标指针。

    ➢ 关闭游标 CLOSE: CLOSE cursor_name 这个语句关闭先前打开的光标。如果未被明确地关闭,光标在它被声明的复合语句 的末尾被关闭。

    定义游标
    declare fetchSeqCursor cursor for select seqname, value from sys_sequence;

    Handler

    在什么样的条件下,做什么样的处理。例如当发生异常时,该怎么做。

    相关文档:

    在下面的deamon中就有declare continue handler NOT FOUND 、declare continue handler SQLSTATE 等。

     

    (1)游标的使用方法

    1)声明一个游标

    declare 游标名 cursor for select_statement;

    2)打开一个游标

    open 游标名;

    3)取值

    fetch 游标名 into var1,var2[,...];

    4)关闭

    close 游标名;

    使用游标
    open fetchSeqCursor;
    fetch数据
    fetch cursor into _seqname, _value;

    Demo

    新葡亰496net 2

    新葡亰496net 3

     

     

     

    (2)实例

    delimiter //

    create procedure p3()

    begin

    declare row_sno varchar(10);

    declare row_sname varchar(20);

    declare row_sage int;

    declare row_ssex varchar(5);

    declare ergodic int default 0;

    declare getstudent cursor for select sno,sname,sage,ssex from student;

    declare 新葡亰496netMysql学习笔记,Mysql的游标的概念使用及停业深切剖判。continue handler for not found set ergodic=1;

    open getstudent;

    repeat

    fetch getstudent into row_sno,row_sname,row_sage,row_ssex;

    select row_sno,row_sname,row_sage,row_ssex;

    until ergodic=1 end repeat;

    close getstudent;

    end //

    call p3() // 

    新葡亰496net 4

    未使用游标--1

    新葡亰496net 5

    未使用游标--2

    新葡亰496net 6

    游标--1

    新葡亰496net 7

    游标--2

    新葡亰496net 8

    游标--3

    新葡亰496net 9

    游标--4

    从上面的图中可以看出最后一条数据出现了两次

    exit与continue的区别是:exit触发后,后面的语句不再执行,而continue还需要继续执行。

    下面使用exit

    create procedure p4()

    begin

    declare row_sno varchar(10);

    declare row_sname varchar(20);

    declare row_sage int;

    declare row_新葡亰496netMysql学习笔记,Mysql的游标的概念使用及停业深切剖判。ssex varchar(5);

    declare ergodic int default 0;

    declare getstudent cursor for select sno,sname,sage,ssex from student;

    declare exit handler for not found set ergodic=1;

    open getstudent;

    repeat

    fetch getstudent into row_sno,row_sname,row_sage,row_ssex;

    select row_sno,row_sname,row_sage,row_ssex;

    until ergodic=1 end repeat;

    close getstudent;

     end  //

    新葡亰496net 10

    游标--5

    新葡亰496net 11

    游标--6

    新葡亰496net 12

    游标--7

    新葡亰496net 13

    游标--8

    由上图可以看出,最后一条数据没有出现两次。

    注:本文参考http://blog.csdn.net/xushouwei/article/details/52201360

    关闭游标
    close fetchSeqCursor;
    不过这都是针对cursor的操作而已,和PL/SQL没有什么区别吧,不过光是了解到这个是根本不足以写出Mysql的fetch过程的,还要了解其他的更深入的知识,我们才能真正的写出好的游标使用的procedure
    首先fetch离不开循环语句,那么先了解一下循环吧。
    我一般使用Loop和while觉得比较清楚,而且代码简单。

    Debugger Tool

    新葡亰496net 14

     

     

     

    上面 的两个procedure,在使用debugger调试时,只需要在main中写直接调用 就可以了。

    新葡亰496net 15

     

    这里使用Loop为例

    复制代码 代码如下:

    fetchSeqLoop:Loop
    fetch cursor into _seqname, _value;
    end Loop;

    现在是死循环,还没有退出的条件,那么在这里和oracle有区别,Oracle的PL/SQL的指针有个隐性变量%notfound,Mysql是通过一个Error handler的声明来进行判断的,
    declare continue handler for Not found (do some action);
    在Mysql里当游标遍历溢出时,会出现一个预定义的NOT FOUND的Error,我们处理这个Error并定义一个continue的handler就可以叻,关于Mysql Error handler可以查询Mysql手册定义一个flag,在NOT FOUND,标示Flag,在Loop里以这个flag为结束循环的判断就可以叻。

    复制代码 代码如下:

    declare fetchSeqOk boolean; ## define the flag for loop judgement
    declare _seqname varchar(50); ## define the varient for store the data
    declare _value bigint(20);
    declare fetchSeqCursor cursor for select seqname, value from sys_sequence;## define the cursor
    declare continue handler for NOT FOUND set fetchSeqOk = true; ## define the continue handler for not
    found flag
    set fetchSeqOk = false;
    open fetchSeqCursor;
    fetchSeqLoop:Loop
    if fetchSeqOk then
    leave fetchSeqLoop;
    else
    fetch cursor into _seqname, _value;
    select _seqname, _value;
    end if;
    end Loop;
    close fetchSeqCursor;

    这就是一个完整的过程叻,那么会思考的人一般在这里都会思考,如果是这样的话,怎样做嵌套的游标循环叻,这里可以根据statement block的scope实现叻,Mysql里通过begin end来划分一个statement block,在block里定义的变量范围也在这个block里,所以关于嵌套的游标循环我们可以多加一个begin end来区分他们所对应的error handler(注意在Mysql里同一个error的handler只能定义一次,多定义的话,在compile的过程中会提示里duplicate handler defination,所以NOT FOUND的handler就只能定义一次),在一个begin end里定义这个里面游标的NOT FOUND handler,

    复制代码 代码如下:

    declare fetchSeqOk boolean; ## define the flag for loop judgement
    declare _seqname varchar(50); ## define the varient for store the data
    declare _value bigint(20);
    declare fetchSeqCursor cursor for select seqname, value from sys_sequence;## define the cursor
    declare continue handler for NOT FOUND set fetchSeqOk = true; ## define the continue handler for not
    found flag
    set fetchSeqOk = false;
    open fetchSeqCursor;
    fetchSeqLoop:Loop
    if fetchSeqOk then
    leave fetchSeqLoop;
    else
    fetch cursor into _seqname, _value;
    begin
    declare fetchSeqOk boolean default 'inner';
    declare cursor2 cursor for select .... from ...;## define the cursor
    declare continue handler for NOT FOUND set fetchSeqOk = true; ## define the continue handler for n
    ot
    set fetchSeqOk = false;
    新葡亰496net,open cursor2;
    fetchloop2 loop
    if fetchSeqOk then
    else
    end if;
    end loop;
    close cursor2;
    end;
    end if;
    end Loop;
    close fetchSeqCursor;

    这样就可以轻松实现更多层次的循环了,不过相对oracle的PL/SQL来说,Mysql现在还不支持动态游标的定义,所以很强大的动态拼出SQL的在游标里还不能做到,不过这完全不影响我对Mysql的喜爱程度,她就想那羞涩的荷花一样,虽然没有灿烂的色彩,但那简约的色调,清新而不染一丝铅尘的高雅,一样吸引着无数的mysql迷么,正如接天莲叶无穷碧,映日荷花别样红。

    :Mysql也有类似Oracle里的execute immediate的动态SQL的功能,通过这个功能可有多少弥补一些动态游标的缺憾叻
    set @sqlStr='select * from table where condition1 = ?';
    prepare s1 for @sqlStr;
    execute s1 using @condition1; 如果有多个参数用逗号分隔
    deallocate prepare s1; 手工释放,或者是connection关闭时,server自动回收。

    ...

    本文由新葡亰496net发布于网络数据库,转载请注明出处:新葡亰496netMysql学习笔记,Mysql的游标的概念使用

    关键词: