PL/SQL游标

最新推荐文章于 2023-09-25 11:52:24 发布

一头拱白菜的猪ll

最新推荐文章于 2023-09-25 11:52:24 发布

阅读量507

点赞数

文章标签： sql 数据库

本文链接：https://blog.csdn.net/weixin_45772393/article/details/126309898

版权

4.2 PL/SQL游标

SQL是面向集合的

4.2.1 基本原理

在PL/SQL块中执行SELECT、INSERT、UPDATE和DELETE语句时，Oracle会在内存中为其分配上下文区，即一个缓冲区。游标是指向该区的一个指针，或是命名一个工作区，或是一种结构化数据类型。它为应用程序提供了一种具有多行数据查询结果集中的每一行数据分别进行单独处理的方法，是设计嵌入式SQL语句的应用程序的常用编程方式。

4.2.2 显示游标

显示游标的处理包括

声明游标
打开游标
提取游标
关闭游标

声明游标
```
cursor <游标名> is select <语句>;
```
游标名是一个PL/SQL标识符，所以它必须在被引用以前声明。

游标声明可以在where子句中共引用PL/SQL变量，这些变量被认为是联编变量bindVARIABLE，即已经被分配空间并映射到绝对地址的变量。由于可以使用通常的作用域法则，因此这些变量必须在声明游标的位置是可见的。

声明游标举例
```
declare
   teacher_id number(5);      --定义4个变量来存放TEACHERS表中的内容
   teacher_name varchar2(50);
   teacher_title varchar2(50);
   teacher_sex  char(1);
cursor teacher_cur is        --定义游标teacher_cur      
   select TID, TNAME, TITLE, SEX
   from teachers
   where TID < 117 ;         --选出号码小于117的老师
```
在游标定义的SELECT<语句>不包含INTO子句。INTO子句是FETCH(提取游标)的一部分

打开游标

open <游标名>

打开游标就是执行定义的select语句，执行完毕，查询结果装入内存，游标停在查询结果的首部，注意并不是第一行。当打开一个游标时，会完成以下几件事情：

检查联编变量的取值
根据联编变量的取值，确定活动集
活动集的指针指向第一行。

打开游标举例

declare
   teacher_id number(5);      --定义4个变量来存放TEACHERS表中的内容
   teacher_name varchar2(50);
   teacher_title varchar2(50);
   teacher_sex  char(1);
cursor teacher_cur is        --定义游标teacher_cur      
   select TID, TNAME, TITLE, SEX
   from teachers
   where TID < 117 ;         --选出号码小于117的老师
begin
   open teacher_cur          --打开游标

一次也可以同时打开多个游标

提取游标

fetch <游标名> into <变量列表>；
或
fetch <游标名> into PL/SQL记录;

其中：

<游标名>标识了已经被声明的并且别被打开的游标

<变量列表>是已经声明的PL/SQL变量的列表（变量之间用逗号隔开）

PL/SQL记录是已经声明的PL/SQL记录。

在这两种情况下，into子句中的变量的类型都必须与查询的选择列表的类型相兼容，否则将拒绝执行。

declare
  teacher_id number(5);      --定义4个变量来存放TEACHERS表中的内容
  teacher_name varchar2(50);
  teacher_title varchar2(50);
  teacher_sex  char(1);
cursor teacher_cur is        --定义游标teacher_cur      
  select TID, TNAME, TITLE, SEX
  from teachers
  where TID < 117 ;         --选出号码小于117的老师
begin
  open teacher_cur          --打开游标
  fetch teacher_cur into teacher_id, teacher_name, teacher_title, teacher_sex;
                            --将第一行数据放入变量中，游标后移。

fetch语句每执行一次，游标向后移动一行，直到结束

关闭游标

当所有的活动集都被检索以后，游标就应该被关闭。

declare
   teacher_id number(5);      --定义4个变量来存放TEACHERS表中的内容
   teacher_name varchar2(50);
   teacher_title varchar2(5);
   teacher_sex  char(1);
cursor teacher_cur is         --定义游标teacher_cur      
   select TID, TNAME, TITLE, SEX
   from teachers
   where TID < 117 ;         --选出号码小于117的老师
begin
open teacher_cur             --打开游标
    fetch teacher_cur into teacher_id, teacher_name, teacher_title, teacher_sex;
                             --将第一行数据放入变量中，游标后移。
    loop
        exit when not teacher_cur%FOUND;  --如果游标到位则结束
        if teacher_sex = 'M' then 
                             --将性别为男的行放入男老师表MALE_TEACHERS中
           insert into MALL_TEACHERS(TID, TNAME, TITLE)
                 values(teeacher_id, teacher_name, teacher_title);
        else 
             insert into FEMALE_TEACHERS(TID, TNAME, TITLE)
                  values(teachers_id, teacher_name, teacher_title);
        end if;
        fetch teacher_cur into teacher_id, teacher_name, teacher_title, teacher_sex;
    end loop;
close teacher_cur;          --关闭游标
end;

4.2.3 隐式游标

在PL/SQL程序中用select语句进行操作，则隐式地使用了游标

对每个隐式游标来说，必须要有一个into子句，因此使用隐式游标的select语句必须只选中一行数据或只产生一行数据。

4.2.4 游标属性

四种：

%ISOPEN
%FOUND
%NOTFOUND
%ROWCOUNT

它们描述与游标操作相关的DML语句的执行情况。游标属性只能在PL/SQL的流程控制语句，而不能用在SQL语句内。

是否找到游标(%FOUND)

该属性表示当前游标是否指向有效一行，是—TURE，否则为FALSE。

检查此属性可以判断是否结束游标使用。

%FOUND示例

open teacher_cur;    --打开游标
fetch teacher_cur into teacher_id, teacher_name, teacher_title, teacher Sex;
                     --将第一行数据放入变量中，游标后移
loop
   exit when not teacher_cur%FOUND; --使用了%FOUND属性
end loop;

SQL%FOUND示例

delete from TEACHERS
   where TID=teacher_id;     --teacher_id为一个有值变量
if SQL%FOUND then            --如果删除成功则写入SUCCESS表中该行号码
   insert into SUCCESS values(TID);
else                         --不成功则写入FALL次奥中共该行号码。
   insert into FALL values(TID);
end if;

是否没找到游标(%NOTFOUND)

该属性与%FOUND属性相类似，但其值正好相反

游标行数

该属性记录了游标抽取过得记录行数

loop
   fetch teacher_cur into teacher_id, teacher_name, teacher_title, teacher_sex;
   exit when teacher_cur%ROWCOUNT = 10; --只抽取10条记录
   ...
end loop;

还可以用for语句控制游标的循环，系统隐含地定义了一个数据类型为%ROWCOUNT的记录，作为循环计数器，并将隐式地打开和关闭游标。

FOR语句中%ROWCOUNT示例。

for teacher_record in teacher_cur loop
                      --teacher_record为记录名，它隐含地打开游标teacher_cur
       insert into temp TEACHERS(TID, TNAME, TITLE, SEX)
       values(teacher_record.TID, teacher_record.TNAME, teacher_record.TITLE, teacher_record.SEX);
end loop;

游标是否打开(%ISOPEN)

先检查，再操作

if teacher_cur%ISOPEN then 
    fetch teacher_cur into teacher_id, teacher_name, teacher_title, teacher_sex;
else 
    open teacher_cur;
end if;

参数化游标

动态使用

ACCEPT my_tid prompt 'Please input the tid:'
declare
--定义游标时带上参数cursor_id
   cursor teacher_cur(cursor_id number) is
      select TNAME, TITLE, SEX
      from TEACHERS
      where TID = cursor_id;   --使用参数
begin
   open teacher_cur(my_tid);   --带上实参量
   loop
      fetch teacher_cur into teacher_name, teacher_title, teacher_sex;
      exit when teacher_cur%NOTFOUND;
      ...
    end loop;
    close teacher_cur;
end;

4.2.5 游标变量

游标变量被用于处理多行的查询结果集

声明游标变量

PL/SQL中的引用类型通过下述的语法进行声明

REF type;

type是已经被定义的类型。REF关键字指明新的类型必须是一个指向经过定义的类型的指针。因此，游标可以使用的类型就是REF CURSOR。

定义一个游标变量类型的完整语法如下：

type <类型名> is REF cursor
return <返回类型>;

<类型名>是新的引用类型的名字，

<返回类型>是一个记录类型，它指明了最终由游标变量返回的选择列表的类型。

游标变量的返回类型必须是一个记录类型

declare
   type t_StudentRef is ref cursor    --定义使用%ROWTYPE
   return students%ROWTYPE;
   type t_AbstractstudentRecord IS record( --定义新的记录类型
              sname STUDENTS.sname%TYPE,
              sex STUDENTS.sex%TYPE);
   v_AbstractStudentsRecord t_AbstractStudentsRecord;
   type t_AbstractStudentsRef is REF cursor --使用记录类型的游标变量
   return t_AbstractStudentsRecord;
   type t_NamesRef2 is REF cursor      --另一类型定义
   return v_AbstractStudentsRecord%TYPE;
   v_StudentCV t_StudentsRef;       --声明上述类型的游标变量
   v_AbstractStudentCV t_ABstractStudnetsRef;

游标变量受限，它的返回类型只能是特定类型。而PL/SQL语言中还有一种非受限游标变量，它在声明的时候没有return子句。一个非受限游标变量可以为任何查询打开

定义游标变量

declare 
          --定义非受限游标变量
    type t_FlexibleRef IS REF CURSOR;
          --游标变量
    V_CURSORVar t_FlexibleRef;

打开游标变量

如果要将一个游标变量与一个特定的selec语句相关联，需要使用open for语句

open <游标变量> for <select语句>；

如果游标变量是受限的，则select语句的返回类型必须与游标所限的记录类型匹配。

游标变量的打开示例

declare
   type t_StudentsRef IS ref cursor  --定义使用%ROWTYPE
   return STUDNETS%ROWTYPE;
   V_StudentSCV t_StudentRef;
begin
   open v_studentSCV for 
        select * from STUDENTS；
end;