人大金仓数据库KingbaseES --PLSQL 处理查询结果集

关键字：

Select into、人大金仓、KingbaseES、

处理查询结果集

在 PL/SQL 中，和传统数据库编程一样，可以通过隐式游标或显式游标来处理查询结果集。

隐式游标的编程更加简洁，显示游标编程更灵活。例如，显式游标可以接受参数。

以下 PL/SQL 语句使用 PL/SQL 为您定义和管理的隐式游标：

• SELECT INTO

• 隐式游标 FOR LOOP

以下 PL/SQL 语句使用显式游标：

• 显式游标 FOR LOOP

定义显式游标，但 PL/SQL 在语句运行时对其进行管理。

• OPEN, FETCH, 和 CLOSE

定义和管理显式游标。

使用 SELECT INTO 语句处理查询结果集

使用隐式游标，SELECT INTO 语句从一个或多个数据库表中检索值（就像 SQL SELECT 语句所做的那样）并将它们存储在变量中（SQL SELECT 语句不这样做）。

1. 处理单行结果集

使用select into语句处理单行结果查询结果集，可以将查询结果直接存储到变量中。在以下实例中，表t1是一个存在的表，id和name是该表中的列，v1和v2是用来存储响应列的变量。在执行select into语句时，如果查询结果为空，则会触发no_data_found异常。需要注意的是，该例子只能返回单行数据，若返回结果集存在多行数据，将会触发too_many_rows异常。因此可在select子句中使用where添加条件或limit 1的限制来保证查询结果集的只存在单行数据。

\set SQLTERM /

declare

v1 t1.id%type;

v2 t1.name%type;

begin

select id,name into v1,v2 from t1 where id =1;

raise notice 'id=%,name=%',v1,v1;

exception

when NO_DATA_FOUND then

raise notice 'no data found';

end;

/

（2）处理大型多行结果集

如果必须将大量表数据分配给变量，KingbaseES 建议使用带有 BULK COLLECT 子句的 SELECT INTO 语句。此语句将整个结果集检索到一个或多个集合变量中。

\set SQLTERM ;

CREATE TYPE my_nest_table_type1 IS TABLE OF varchar(50);

\set SQLTERM /

CREATE OR REPLACE PROCEDURE pr1() AS

DECLARE

var1 my_nest_table_type1 :=my_nest_table_type1();

i int:=0;

BEGIN

SELECT name BULK COLLECT INTO var1 FROM t1;

FOR i in 1..var1.count LOOP

RAISE NOTICE '%',var1(i);

END LOOP;

END;

/

使用 游标FOR LOOP 语句处理查询结果集

游标 FOR LOOP 语句允许您运行 SELECT 语句，然后立即循环遍历结果集的行。此语句可以使用隐式或显式游标（但不能使用游标变量）。

如果仅在游标 FOR LOOP 语句中使用 SELECT 语句，则在游标 FOR LOOP 语句中指定 SELECT 语句。这种形式的游标 FOR LOOP 语句使用隐式游标，称为隐式游标 FOR LOOP 语句。因为隐式游标在语句内部，所以不能使用名称 SQL 来引用它。

如果在同一个 PL/SQL 单元中多次使用 SELECT 语句，则为它定义一个显式游标并在游标 FOR LOOP 语句中指定该游标。这种形式的游标 FOR LOOP 语句称为显式游标 FOR LOOP 语句。您可以在同一 PL/SQL 单元的其他地方使用相同的显式游标。

游标 FOR LOOP 语句将其循环索引隐式声明为其游标返回的类型的%ROWTYPE 记录变量。此 record 是循环本地的，仅在循环执行期间存在。循环内的语句可以引用 record 及其字段。他们只能通过别名引用虚拟列。

声明循环索引记录变量后，FOR LOOP 语句打开指定的游标。对于循环的每次迭代，FOR LOOP 语句从结果集中取出一行并将其存储在记录中。当没有更多行要获取时，游标 FOR LOOP 语句将关闭游标。如果循环内的语句将控制转移到循环外或 PL/SQL 引发异常，则游标也会关闭。注意: 当游标 FOR LOOP 语句中引发异常时，游标会在异常处理程序运行之前关闭。因此，显式游标属性的值在异常处理程序中不可用。（1）隐式游标 FOR LOOP 语句

\set SQLTERM /

BEGIN

FOR item IN (

SELECT id, name

FROM t1

)

LOOP

RAISE NOTICE 'id: %, name: %',item.id, item.name;

END LOOP;

END;

/

1. 显式游标 FOR LOOP 语句

\set SQLTERM /

DECLARE

CURSOR c1 IS

SELECT id,name FROM t1;

BEGIN

FOR item IN c1

LOOP

RAISE NOTICE 'id: %, name: %',item.id, item.name;

END LOOP;

END;

/

1. 游标 FOR 循环引用虚拟列

\set SQLTERM /

BEGIN

FOR item IN (

SELECT id || '.' || name AS id_name,

score * 10 AS dream_score

FROM stu_temp

WHERE ROWNUM <= 5

) LOOP

RAISE NOTICE 'id_name: %, dream_score: %', item.id_name, item.dream_score;

END LOOP;

END;

/

1. 将参数传递给显式游标 FOR LOOP 语句

\set SQLTERM /

DECLARE

CURSOR c1 (id int) IS

SELECT * FROM t1;

BEGIN

FOR item IN c1(1)

LOOP

RAISE NOTICE 'id: %, name: %',item.id, item.name;

END LOOP;

END;

/

使用显式游标、OPEN、FETCH 和 CLOSE 语句处理查询结果集

要完全控制查询结果集处理，请声明显式游标并使用语句 OPEN、FETCH 和 CLOSE 管理它们。

这种结果集处理技术比其他技术更复杂，但也更灵活。例如，您可以：

• 使用多个游标并行处理多个结果集。

• 在单个循环迭代中处理多行、跳过行或将处理拆分为多个循环。

• 在一个 PL/SQL 单元中指定查询，但在另一个 PL/SQL 单元中检索行。

处理带有子查询的查询结果集

如果您通过循环处理查询结果集并为每一行运行另一个查询，那么您可以通过从循环内部删除第二个查询并使其

成为第一个查询的子查询来提高性能。

每个表都执行了一个普通的子查询，表中每一行也执行了一个相关的子查询。

1. 父查询的 FROM 子句中的子查询

\set SQLTERM /

DECLARE

CURSOR c1 IS

SELECT t1.id, t1.name, t1.score

FROM student t1,

( SELECT id, COUNT(*) AS count

FROM stu_temp

GROUP BY id

) t2

WHERE (t1.id = t2.id) AND count >= 1

ORDER BY count;

BEGIN

FOR item IN c1

LOOP

RAISE NOTICE 'id: %, name: %, score: %', item.id, item.name, item.score;

END LOOP;

END;

/

1. 相关子查询

\set SQLTERM /

DECLARE

CURSOR c1 IS

SELECT id, name, score

FROM stu_temp t

WHERE score > ( SELECT AVG(score)

FROM stu_temp

)

ORDER BY id;

BEGIN

FOR person IN c1

LOOP

RAISE NOTICE 'Making above-average score = %', person.name;

END LOOP;

END;

/

参考资料