图片 4

MySQL游标的使用笔记大全

以下的文章主要介绍的是MySQL游标的使用笔记,其可以用在存储过程的SQL语句,其主要类型主要有以下几种,以下就是对其详细介绍,相信如果你掌握了这项技术,会在以后的学习或是工作中带来很大的帮助。

摘要:本文主要讲解了存储过程的创建、调用、以及游标的使用
,相信掌握了游标      会对你有不错的帮助,有不足之处还请指教
 
导航 : 一、存储过程的创建及调用
            二 、游标的使用
            三、  示例
            四、补充
 
说明:
        1、用到的两个数据表:
 
       from_data  

此书及其相关资源下载方法:关注微信公众号,点击功能介绍-书籍链接下载,即可获取

1、 无返回结果语句,如:INSERT,UPDATE,DROP, DELETE等

图片 1

说明

ORACLE
PL/SQL从入门到精通的事务处理和锁定,异常处理机制将会在<ORACLE官方文档>的笔记中具体详细介绍,至此ORACLE
PL/SQL的入门和基础已经进入完结
本文主要介绍如下:

  • 12.1游标基本结构
    12.1.1 游标简介
    12.1.2 游标分类
    12.1.3 定义游标类型
    12.1.4 打开游标
    12.1.5 使用游标属性
    12.1.6 提取游标数据
    12.1.7 批量提取游标数据
    12.1.8 关闭游标
  • 12.2操纵游标数据
    12.2.1 LOOP循环
    12.2.2 WHILE循环
    12.2.3游标FOR循环
    12.2.4 修改游标数据
  • 12.3游标变量
    12.3.1 游标变量简介
    12.3.2 声明游标变量类型
    12.3.3 定义游标变量
    12.3.4 打开游标变量
    12.3.5 控制游标变量
    12.3.6 处理游标变量异常
    12.3.7 在包中使用游标变量
    12.3.8 游标变量的限制

2、 select语句返回单行变量并可传给本地变量(select ..into)

  
to_data

———————————–12.1 游标基本结构———————————–

3、 返回多行结果集的select语句,并可使用MySQL游标循环处理

图片 2

12.1.1 游标简介

注意,存储过程返回的多行结果集,可以被客户端程序如php)所接收,但要在一个存储过程中接收另一个存储过程的结果集是不可能的,一般解决办法是存入临时表供其它过程共用

2、示例需求
: 将表from_data 的select结果集循环插入到表 to_data;
 
                              伪代码:   while 循环 select id ,name
from_data
 
                                          insert into to_data(id,name)
value(from_data.id,from_data.name)
 
                                end
 
        3、环境: mysql
 
 
 
 一、存储过程的创建及调用
 
           我们创建一个名叫 add_test的存储过程
 
       1 、检查是否有 add_test
Sql代码 
drop procedure if exists add_test;   
       2、创建
 
 
Sql代码 
create procedure add_test()    
   (    
   #[in|out|inout] 参数 datatype  
    
  
     a int; 
     b int; 
   )    
   begin    
   #SQL 语句; 
 
   
     
   end;   
 
   3、调用
 
Sql代码 
call add_test(1,2 
 
); 
  
 
   以上就是基本的创建方法,注意已下几点:
        1 、在建立和调用时,add_test后面的“()”是必须的
        2、MySQL
存储过程参数如果不显式指定“in”、“out”、“inout”,则默认为“in”,并且参数不能指定默认值

        3、包含多条 SQL 语句时,需要 begin end 关键字,在begin
end里面的每条语句的末尾,都要加上分号 “;”
        4、在begin end里面声明变量,使用关键字 DECLARE ,如:
 
Sql代码 
begin  
 
  #声明一个name变量,类型是varchar(记得分号) 
  name varchar(32);    
end; 
 
 
二 、游标的使用
 
         1、定义游标
Sql代码 
/* 
    定义游标的关键字:CURSOR。 
    定义游标cursor_name, 
    游标cursor_name当前指针的记录 
    是一个表from_data的多行结果集 
 */ 
  DECLARE cursor_name CURSOR FOR select id,name 
 
from from_data; 
 
 
 
       2、打开游标
 
Sql代码 
#关键字:OPEN 
OPEN cursor_name;  
 
   3、 获取游标
 
Sql代码 
#声明两个变量 
  DECLARE a int 
 

  DECLARE b varchar(32) 
 

 
/* 
   FETCH 获取游标当前指针的记录,并传给指定变量 a 、b 
*/ 
FETCH cursor_name INTO a,b; 
 
   注意:(1、此处很重要,我们在后面的循环例子中会详细讲解如何用,
          
(2、注意变量数必须与MySQL游标返回的字段数以及类型一致,请看2,3步的标红处,
           a的类型对应 id,b类型对应name
 
  4、关闭游标
 
Sql代码 
CLOSE cursor_name ; 
 
 
   
以上就是游标的常见使用方法,关键的部分我已在每一步中说明,就不在多说了,现在我们看下例子:
 
三、示例
 
Sql代码 
drop procedure if exists add_test; 
# 创建存储过程 add_test 
 
 
CREATE PROCEDURE add_test() 
 
    BEGIN 
           #定义 变量 
 
 
           DECLARE a int; 
           DECLARE b VARCHAR(30); 
            
          
#此变可有可无,为了给个该存储函数执行成功后给个提示,运行下便知道 
 
 
           DECLARE str VARCHAR(300); 
           DECLARE x int; 
            
           #这个用于处理游标到达最后一行的情况 
 
    
           DECLARE

定义

游标实际上指向的是一块内存区域,这块内存区域位于进程全局区内部,称为上下文
区域 (ContextAIea) ,在上下文区域中保存了如下3类信息。

  • 查询返回的数据行。
  • 查询所处理的数据的行号。
  • 指向共享池中的己分析的 SQL语句。

4、 prepare语句

,相信掌握了游标 会对你有不错的帮助,有不足之处还请指教 导航 :
一…

代码部分

DECLARE
   emprow   emp%ROWTYPE;     --定义保存游标检索结果行的记录变量
   CURSOR emp_cur            --定义游标
   IS
      SELECT *
        FROM emp
       WHERE deptno IS NOT NULL;
BEGIN
   OPEN emp_cur;             --打开游标
   LOOP                      --循环检索游标
      FETCH emp_cur          --提取游标内容
       INTO emprow;
      --输出检索到的游标行的信息
      DBMS_OUTPUT.put_line (   '员工编号:'
                            || emprow.empno
                            || ' '
                            || '员工名称:'
                            || emprow.ename
                           );
      EXIT WHEN emp_cur%NOTFOUND;  --当游标数据检索完成退出循环
   END LOOP;   
   CLOSE emp_cur;           --关闭游标
END;

以下主要讲述游标及prepare部分

执行过程如图所示

图片 3

游标

执行过程文字说明

  • 上述语句的执行过程如下所示:
    (l) 在语句的声明块中,
    声明了一个用来保存游标检索结果的记录类型,指向 emp表
    的行记录.
    (2) 在声明块中, 使用 CURSOR 关键字定义了一个游标, 在IS
    子句后面指定了查询
    语句查询 emp表中的数据.
    (3) 在执行块中, 首先使用 OPEN 语旬打开emp_cur游标,
    此时查询数据将保存到内
    存中’ 游标就指向该内存区域.
    (4)通过 FETCH语句提取游标中的数据到游标行中.
    (5) 输出游标数据到屏幕上.
    (6) 使用游标变量%NOTFOUND判断记录是否己检索完.
    (7) 循环结束, 必须关闭游标.
  • 通过上面的示例y 可以发现一个显式的游标由如下的几个过程实现:
    (1) 声明游标并为游标关联SELECT语句.
    (2)执行 SELECT语句打开游标。
    (3)将游标的结果放到PL/SQL变量中.
    (4) 关闭游标。

定义

12.1.2 游标分类

  • 显式游标:使用 CURSOR语句显式定义的游标, 游标被定义之后,
    需要打开并提
    取游标。
  • 隐式游标: 由0racle 为每一个不属于显式游标的 SQL DML
    语句都创建一个隐式
    的游标, 由于隐式游标没有名称, 困此也可以叫做SQL游标。
DECLARE cursor_name CURSOR FOR SELECT_statement; 

12.1.3 定义游标类型

游标操作

指定的SELECT语句。 显式游标的定义语法如下所示。

CURSOR cursor_name [parameter_list]
[RETURN return_type]
IS query
[FOR UPDATE [OF (column_11st)] [NOWAIT] ]:

OPEN 打开游标

语法说明

声明语句中关键部分的含义如下。

  • cursor_naIne:用于指定一个有效的游标名称, 这个名称遵循 PL/SQL
    的标识符命
    名规范。
  • parameter_list: 用于指定一个或多个可选的游标参数,
    这些参数将用于查询执行。
  • RETURN return_type:可选的 RETURN 子句指定游标将要返回的由
    return_type 指
    定的数据类型, return_type必须是记录或数据表的行类型。
  • query: 可以是任何 SELECT语句。
  • FOR UPDATE: 指定该子句将在游标打开期间锁定游标记录,
    这些记录对其他用
    户来说为只读模式。
OPEN cursor_name; 

12.1.4 打开游标

FETCH
获取游标当前指针的记录,并传给指定变量列表,注意变量数必须与MySQL游标返回的字段数一致,要获得多行数据,使用循环语句去执行FETCH

打开游标的语法如下所示。

OPEN cursor_name [ (parameter_values
FETCH cursor_name INTO variable list; 

语法说明

curs0r_name 用于指定游标名称,parameter_values
用于指定游标的参数列表。 在游标
被打开时, 将会发生如下所示的几个动作。

  • 检验绑定变量的值。
  • 基于查询的语句确定游标的活动集
  • 游标指针指向游标活动集的第l行。

CLOSE关闭游标

打开游标执行流程

图片 4

CLOSE cursor_name ; 

12.1.5 使用游标属性

不论显示还是隐式游标都含有四个属性:%ISOPEN,%FOUND,%NOTFOUND和%ROWCOUNT

注意:MySQL的游标是向前只读的,也就是说,你只能顺序地从开始往后读取结果集,不能从后往前,也不能直接跳到中间的记录.

%ISOPEN属性

判断对应的游标是否被打开,如果打开:TRUE,否则FALSE

一个完整的例子:

%FOUND属性

在调用FETCH语句获取数据之前,为NULL值,而此后会每取得一行数据返回为TRUE,直到最后一个值被取完后找不到数据就会为FALSE

定义本地变量

%NOTFOUND属性

和%FOUND数据完全相反,在调用FETCH语句获取数据之前,为NULL值,而此后会每取得一行数据返回为FALSE,直到最后一个值被取完后找不到数据就会为TRUE

DECLARE o varchar(128); 
ROWCOUNT属性

游标被打开时,值为0,每获取一条数据,则加1
注意:一定要在被使用的情况下,才会加1,而不是计算查询结果总数据量

定义游标

12.1.6 提取游标数据

在使用游标时′,只有将游标数据提取出来, 才 能进行进一步的使用
提取游标数据使用 FETCH 语句,该语句可以一次一行地提取游标数据,
也可以使用 BULK
COLLECT子句一次性接收所有的游标数据到一个数组或一个PL/SQL表中.