浅谈GBase 8s数据库游标

转载

南大通用GBase 2024-07-08 11:26:33

文章标签 数据类型 SQL 结果集 8s 文章分类 Oracle 数据库

在数据库编程中，处理复杂的数据集是一项常见任务。游标（CURSOR）作为数据库操作中的一项重要工具，允许我们逐行处理查询结果，极大地提升了数据处理的灵活性和效率。本文将深入探讨游标的概念、类型、属性以及在实际编程中的应用。

一、游标的概念

游标(CURSOR):游标是把从数据表中提取出来的数据，以临时表的形式存放在内存中，在游标中有一个数据指针，在初始状态下指向的是首记录，利用fetch语句可以移动该指针，从而对游标中的数据进行各种操作。

游标是用在plsql块中的！！！！（declare..begin..end; procedure）

通俗解释：数据库中，没有数组或集合的概念，所以如果储存多个结果集呢？使用游标！

select * from user; 如使用游标储存该结果集

游标基本原理：逐行处理查询结果，以编程的方式访问数据

浅谈GBase 8s数据库游标_SQL

二、游标的作用

游标是用来处理使用SELECT语句从数据库中检索到的多行记录的工具。借助于游标的功能，数据库应用程序可以对一组记录逐条进行处理，每次处理一行。

三、游标的类型

显式游标(Explicit Cursor)：显式游标需要定义声明，在使用前要打开和获取，使用完毕后要关闭。多用于返回多行的SELECT语句
隐式游标(Implicit Cursor)：在执行一个SQL语句时，服务器将自动创建一个隐式游标，该游标是内存中的工作区，存储了执行SQL语句的结果，可通过游标的属性获得SQL的执行结果及状态信息。多用于只返回一行的SQL语句。

四、游标的主要属性

%FOUND 布尔型属性，当SQL语句至少影响一行时为TRUE，否则为FALSE
%NOTFOUND 布尔型属性，当SQL语句没有影响的行时为TRUE，否则为FALSE
%ISOPEN 布尔型属性，当游标已打开时返回TRUE，否则为FALSE(对用户而言，隐式游标永远是false)
%ROWCOUNT 数字型属性，返回受到SQL影响的行数

(ORACLE在创建隐式游标时，默认的游标名为SQL)

注意：

属性名与游标名之间没有空格。

游标的属性只能在PL/SQL块中使用,而不能在SQL语句中使用

例：将PRODUCTS表中类型为1的所有产品的单价打9折,并显示该更新所影响的行数.

BEGIN
   UPDATE products
   SET  unitprice=unitprice*0.9
   WHERE categoryid=1;
   IF SQL%FOUND THEN
      dbms_output.put_line('更新了'||SQL%ROWCOUNT||'条记录');
  ELSE
      dbms_output.put_line('没有更新记录');
  END IF;
END;

上面是隐式游标

1、显示游标

1、定义游标

在使用显示游标之前，必须先在声明部分定义游标，

其定义语法如下：

CURSOR cursor_name[(parameter,…)] IS select_statement;

说明：参数parameter形式如下：para_name [IN] data_type [:=|DEFAULT value]

2、打开游标

当打开游标时，ORACLE会执行游标所对应的SELECT语句，并将结果存放到结果集，

其定义语法如下：

OPEN cursor_name[(parameter,…)]；

3、提取数据

FETCH  cursor_name INTO variable[,…];

说明：

对游标第一次执行FETCH语句时，它将工作区中的第一条记录赋给变量，并使工作区内的指针指向下一条记录。
工作区中的游标指针只能向下移动，不能回退。
在使用FETCH语句之前，必须先打开游标，才能保证工作区内有数据。
INTO子句中的变量，顺序、类型必须与工作区中每行记录的字段数、顺序以及数据类型一一对应。

4、关闭游标

关闭游标可释放其结果集，

语法如下：

CLOSE  cursor_name；

说明：关闭游标，就是使游标所对应的内存工作区变为无效，并释放与游标相关的系统资源。

显示游标——无参游标

例：用显式游标显示输出products表中供应商编号为6 的产品的信息。

DECLARE
   CURSOR  prod_cursor  IS
     select * from products where
     supplierid=6;
  prod_record products%rowtype; 
BEGIN
  OPEN prod_cursor;
  LOOP
   FETCH prod_cursor INTO prod_record;
EXIT WHEN
    prod_cursor%NOTFOUND;
dbms_output.put_line('产品编号'||prod_record.productid);
dbms_output.put_line('产品名称'||prod_record.productname);
dbms_output.put_line('供应商编号'||prod_record.supplierid);
END LOOP;
CLOSE prod_cursor;
END;

说明：%rowtype属性声明的变量对应于数据库表或视图中列的集合。在%ROWTYPE之前加上数据库表名。字段表示表中的列。

这种数据类型可以一个变量把整个表的东西读出来，用".元素名"使用（如上面的prod_record.productid）。（关于%rowtype与%type见附录1）

显示游标——参数游标

例：用显式参数游标显示输出products表中供应商编号为XX 的产品的信息。

DECLARE
   CURSOR  prod_cursor (suppID in number DEFAULT 1)  IS
     select * from products where
     supplierid=suppId;
  prod_record products%rowtype;
BEGIN
  OPEN prod_cursor(2);
  LOOP
   FETCH prod_cursor INTO prod_record;
EXIT WHEN
    prod_cursor%notfound;
dbms_output.put_line('产品编号'||prod_record.productid);
dbms_output.put_line('产品名称'||prod_record.productname);
dbms_output.put_line('供应商编号'||prod_record.supplierid);
END LOOP;
CLOSE prod_cursor;
END;

注意：在为参数游标定义参数的数据类型时，不能使用长度约束

显示游标——游标FOR循环

语法格式：

FOR 循环变量 IN 游标类型名 LOOP 循环语句 END LOOP；

注意：

循环变量不需要定义，系统隐含地定义其数据类型为%ROWTYPE的变量
使用游标FOR循环时，不能使用OPEN、FETCH、CLOSE语句。

例：用显式参数游标显示输出products表中供应商编号为XX 的产品的信息。

DECLARE
   CURSOR  prod_cursor (suppID in number DEFAULT 1)  IS
     select * from products where supplierid=suppId;
BEGIN
FOR v_pr IN prod_cursor(3) LOOP
dbms_output.put_line('产品编号'||v_pr.productid);
dbms_output.put_line('产品名称'||v_pr.productname);
dbms_output.put_line('供应商编号'||v_pr.supplierid);
END LOOP;
END;

2、显示游标与隐式游标比较

隐式游标	显示游标
当查询时，有PL/SQL内部管理，自动打开和关闭	游标有一个名字，可在程序中显示地定义、打开关闭
游标属性的前缀是SQL	游标属性的前缀是游标名
游标属性%ISOPEN总是FALSE，因为当查询执行完毕后立即关闭隐式游标	依赖游标的状态，属性%ISOPEN有个有效值
SELECT.....INTO只能处理一行	可以处理任何行，在程序中设置循环过程，每行都应该显示地取(除非在一个游标的FOR循环中)

3、游标变量（动态游标）

游标包括显示游标和隐式游标，在定义时与特定的查询绑定，即在声明中定义查询，其结构是不变的，因此又称静态变量。

游标变量是一个指向多行查询结果集的指针，不与特定的查询绑定，可以在打开游标变量时定义查询，可以返回不同结构的结果集。

在PL/SQL 中，使用游标变量包括定义游标引用类型(REF CURSOR)，声明游标变量，打开游标变量、检索游标变量、关闭游标变量等几个基本步骤。

游标变量基本步骤

1、定义游标CURSOR类型的指针

TYPE type_name IS REF CURSOR [RETURN return_type];

type_name 是新引用类型的名字

return_type表示该游标变量返回的记录类型。

2、声明REF CURSOR类型的变量

游标变量 type_name;

3、打开游标变量的语法格式如下所示：

OPEN 游标变量名 FOR SELECT 语句;

4、检索游标变量

检索游标变量的方法与检索静态游标相似，使用 FETCH…INTO 语句循环检索游标变量的

结果集中的记录。语法为：

LOOP
FETCH cursor _ variable INTO variablel,variable2........;
EXIT WHEN Cursor_ variables%NOTFOUND;
........
END LOOP;

检索游标变量时只能使用简单循环或 WHILE 循环，不能采用 FOR 循环。

5、关闭游标变量

检索并处理完游标变量所对应的结果集后，可以关闭游标变量，释放存储空间。

语法为;

close 游标变量名；

游标变量的不同定义方式举例

DECLARE

1、-- 用 %ROWTYPE类型定义游标变量的返回值.

TYPE t_productsRef IS REF CURSOR
       RETURN products%ROWTYPE;

2、 -- 定义一个新的记录类型

TYPE t_prodRecord IS RECORD (
        prodid  products.productid%TYPE,
        prodname  products.productname%TYPE);
       --定义游标变量的返回值为记录类型变量
      TYPE t_prodRef IS REF CURSOR
        RETURN  t_prodRecord;

例：使用游标变量查询出产品表中类别为1的产品的信息，并输出产品名称、类别编号、单价。

DECLARE
      TYPE prod_record IS RECORD
      ( pname products.productname%type,
        cid products.categoryid%type,
        uprice products.unitprice%type);
      TYPE prod_cursor IS REF CURSOR RETURN prod_reord;
      v_prodcur prod_cursor ;
      v_prodrec  prod_record;
BEGIN
   OPEN v_prodcur FOR select productname,categoryid,unitprice from products where categoryid=1;
  LOOP
FETCH v_prodcur INTO v_prodrec ;
 EXIT WHEN v_prodcur%notfound;
 dbms_output.put_line ('产名'||v_prodrec.pname);
dbms_output.put_line('产品类别'||v_prodrec.cid);
dbms_output.put_line ('单价'||v_prodrec.uprice);
  End  LOOP;
Close v_prodcur;
End;

使用同一游标变量打开多个查询

例：使用游标变量查询出产品表中类别为1的产品的信息，然后查询输出产品单价小于20的产品信息。

DECLARE
TYPE prod_cursor IS REF CURSOR;
      v_cur prod_cursor;
      v_rec products%rowtype;
BEGIN
   OPEN v_cur FOR select *  from products where categoryid=1;
  LOOP
     FETCH v_cur INTO v_rec;
     EXIT WHEN v_cur%notfound;
     dbms_output.put_line('产品名' || v_rec.productname);
     dbms_output.put_line('产品类别' || v_rec.categoryid);
     dbms_output.put_line('单价' || v_rec.unitprice);
  End  LOOP;
OPEN v_cur FOR select *  from products where unitprice<20;
  LOOP
     FETCH v_cur INTO v_rec;
 EXIT WHEN v_cur%notfound;
    dbms_output.put_line('产品名' || v_rec.productname);
    dbms_output.put_line('产品类别' || v_rec.categoryid);
     dbms_output.put_line('单价' || v_rec.unitprice);
  End  LOOP;
Close v_cur;
End;

4、动态游标应用在存储过程

简单示例：

create or replace procedure sigleRowCursor(id_p in number) --接收参数是number类型
is
  cursor student_c is select * from t_student where id = id_p; --静态游标，静态游标值为select执行的结果
  
  rs t_student%rowType; --定义rs变量，为t_student表行
  
  type ref_cursor_type is ref cursor;--自定义了一个游标类型，并声明它是一个动态游标	
  
  teacher_c ref_cursor_type; --定义teacher_c变量，为游标类型
  
  rs_teacher t_teacher%rowType; --定义rs_teacher变量，为t_teacher表行
  
  v_sql varchar2(100); --定义v_sql变量，为varchar2类型
  
begin
  open student_c;
  fetch student_c into rs;
  dbms_output.put_line(rs.id || ' ' || rs.name);
  v_sql := 'select * from t_teacher where tea_id = ' || rs.tea_id; --给varchar2类型v_sql赋值
  close student_c;
  
  open teacher_c for v_sql;--打开动态游标并为动态游标赋值，值为v_sql执行的结果
  fetch teacher_c into rs_teacher;
  dbms_output.put_line(rs_teacher.tea_id || ' ' || rs_teacher.tea_name);
  close teacher_c;
  
end;

复杂示例：存储过程返回游标，该例中的for不是游标for循环，而是普通循环，注意与上面“显示游标——游标FOR循环”区别！

--创建返回游标的存储过程
create or replace procedure backParamter2 (res out sys_refcursor) is --存储过程输出返回res ，res类型为sys_refcursor系统动态游标类型
begin
  open res for select * from t_student; --打开动态游标res并使用select执行结果为游标赋值
end;


--执行上面存储过程
declare
--声明一个系统游标类型的变量
student_cursor sys_refcursor;
--声明一个自定义类型（这个类型是学生表t_student里的行对应的表） 注意与type student_type t_student%rowType区别
type student_table_type is table of t_student%rowType;
--声明一个变量为自定义类型
student_table student_table_type;
begin
  --执行存储过程，此过程返回系统游标
  backParamter2(student_cursor);
  --抓取系统游标（集合）里的数据到学生表类型变量里
  fetch student_cursor bulk collect into student_table; --bulk collect表示将系统游标大集合全部抓取到 student_table
  --迭代学生表
  for i in 1..student_table.count
  loop
    --取学生表里的行对应的列的值
	dbms_output.put_line(student_table(i).id || ',' || student_table(i).name);
  end loop;
  --关闭游标
  close student_cursor;--在存储过程中打开的
end;

附录1—%type和%rowtype的使用

1、%type

解释：%TYPE是定义一个变量，其数据类型与已经定义的某个数据变量的类型相同。type的使用时为了使一个新定义的变量与另一个已经定义了的变量(通常是表的某一列)的数据类型保持一致, 当被参照的那个变量的数据类型发生改变时,那么这个新定义的变量的数据类型也会随之发生改变,这种定义方式类似与其他编程语言中的"var",其类型不由自己决定，而是由与之关联的对象决定。

优点：当参数类型发生变化的时候不用修改Sql语句了,当不能确切的知道那个变量的类型是,就采用这种方法来定义变量的数据类型。

eg:

declare

use_id users.userid%type;

user_name users.username%type;

2、%rowtype

%ROWTYPE返回的是一个记录类型，其数据类型和数据库表的数据结构一致。

声明的变量对应于数据库表或视图中列的集合。

在%ROWTYPE之前加上数据库表名。

记录内字段名和数据类型与参照表或视图中的列相同。

为了使一个变量的数据类型与一个表中记录的各个列的数据类型相对应、一致，用%rowtype的定义方式。当表的某些列的数据类型改变了之后，这个新定义的变量的数据类型会自动跟随其改变，容易保持一致，也不用每次修改PL/SQL语言了。当不能确切地知道被参照的那个表的结构及其数据类型时，就只能采用这种方法定义变量的数据类型。

一行记录可以保存从一个表或游标中查询到的整个数据行的各列数据。一行记录的各个列与表中一行的各个列有相同的名称和数据类型。

例：

DECLARE   
  V_SF_ORG_REC SF_ORG%ROWTYPE; --与SF_ORG表中的各个列相同  
BEGIN  
  SELECT * INTO V_SF_ORG_REC  
  FROM SF_ORG SO  
  WHERE SO.ORG_ID=&ORG_ID;  
  DBMS_OUTPUT.PUT_LINE('部门ID：' || TO_CHAR(V_SF_ORG_REC.ORG_ID));  
  DBMS_OUTPUT.PUT_LINE('部门名称：' || V_SF_ORG_REC.ORG_NAME);  
  DBMS_OUTPUT.PUT_LINE('上级部门编码：' || TO_CHAR(V_SF_ORG_REC.PARENT_ID));  
END;

游标是数据库编程中一项强大的功能，它使得开发者能够以编程方式逐行处理复杂的数据集。通过本文的介绍，我们希望读者无论是在简单的查询还是在复杂的数据处理任务中都能够更好地理解和应用游标。

上一篇：GBase 8a MPP Cluster 多实例最佳实践（中）

下一篇：GBase 8a MPP Cluster 多实例最佳实践（下）

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯