南大通用GBase 8s的plsql游标应用——嵌套游标

原文链接：https://www.gbase.cn/community/post/4339
更多精彩内容尽在南大通用GBase技术社区，南大通用致力于成为用户最信赖的数据库产品供应商。

在数据库编程中，游标是实现对结果集逐行处理的重要工具。特别是在复杂的数据处理场景中，嵌套游标的使用可以极大地提高开发效率和程序的可读性。本文将详细介绍在GBase 8s  PL/SQL中的嵌套游标应用，包括其定义、使用方法以及实际案例，帮助您更好地掌握这一高级数据库编程技术。

游标表达式用游标操作符表示，返回的是一个嵌套在查询语句中的游标。使用游标表达式的时候需要显示关闭，否则会出现锁表。

1. 游标表达式把子查询作为外层查询的一列；
2. 游标表达式把查询转换成一个结果集，该结果集可以作为参数传递给流或者转换函数。

使用嵌套cursor表达式提取数据

drop database testdb with log;
create database testdb with log;

set environment sqlmode 'oracle';
set serveroutput on;
drop table t3;
drop table t1;
create table t1(id int primary key,name varchar2(20)); --一级单位
create table t3(id int primary key,name varchar2(20),id2 int ); --三级单位
insert into t1 values(1,'北京'),(2,'天津');
insert into t3 values(1,'海淀',1),(2,'昌平',1),(3,'西青',2),(4,'南开',2);

create or replace procedure pro1(v_id int)
is
type refcursor is ref cursor;
c2 refcursor;
cursor c1 is
select a.name,cursor(select name from t3 b where b.id2=a.id ) as name3
from t1 a where a.id=v_id;
v_name1 varchar2(10);
v_name2 varchar2(10);
begin
 open c1;
   loop
fetch c1 into v_name1,c2;
exit when c1%notfound;
DBMS_OUTPUT.PUT_LINE('t1.name ' || v_name1);
      loop
FETCH c2 INTO v_name2; 
EXIT WHEN c2%NOTFOUND; 
DBMS_OUTPUT.PUT_LINE('t3.name ' || v_name2);
end loop;
END LOOP;
close c1;
end;
/
call pro1(1);
--执行结果：
Routine executed.

t1.name 北京
t3.name 海淀
t3.name 昌平

游标表达式作为函数参数或存储过程参数传入

drop database testdb with log;
create database testdb with log;

set environment sqlmode 'oracle';
set serveroutput on;
create table t_1(c1 int);
insert into t_1 values(1),(2),(3);

create or replace function f1(c1 sys_refcursor) --游标变量
return int
is
type tt is table of t_1%rowtype; --嵌套表
tt1 tt;
begin
fetch c1 bulk collect into tt1; --批量插入
close c1;
return tt1.count; --游标属性：总记录数
end;
/

select f1(cursor(select * from t_1)) from dual;
--结果为
(EXPRESSION)

           3

1 row(s) retrieved.
create table t_2(c1 int, c2 char(10));
insert into t_2 values(1,'a'),(2,'b'),(3,'c');

create or replace function f2(c1 sys_refcursor) --游标变量
return int
is
--type rt is record(sno varchar2(10),sname varchar2(20)); --记录类型
type tt is table of t_2%rowtype; --嵌套表
tt1 tt;
begin
fetch c1 bulk collect into tt1; --批量插入
close c1;
return tt1.count; --游标属性：总记录数
end;
/

select f2(cursor(select * from t_2)) from dual;
--(EXPRESSION)

--          3

--1 row(s) retrieved.

create or replace procedure p_1(c1 sys_refcursor) --游标变量
is
--type rt is record(sno varchar2(10),sname varchar2(20)); --记录类型
type tt_c1 is table of t_2.c1%type; --嵌套表
type tt_c2 is table of t_2.c2%type; --嵌套表
tt1 tt_c1;
tt2 tt_c2;
begin
fetch c1 bulk collect into tt1,tt2; --批量插入
close c1;
for i in tt1.first..tt1.last loop
 DBMS_OUTPUT.PUT_LINE('c1 ' || tt1(i));
 DBMS_OUTPUT.PUT_LINE('c2 ' || tt2(i));
end loop;
end;
/
call p_1(cursor(select c1,c2 from t_2));
--Routine executed.

--c1 1
--c2 a
--c1 2
--c2 b
--c1 3
--c2 c

以上，嵌套游标是PL/SQL提供的一种数据库编程工具，它使得对复杂数据结构的操作变得更加简洁和高效。通过本文的示例和介绍，希望能够帮助您理解嵌套游标的使用方法和应用场景。感谢您的阅读。

原文链接：https://www.gbase.cn/community/post/4339
更多精彩内容尽在南大通用GBase技术社区，南大通用致力于成为用户最信赖的数据库产品供应商