金仓数据库KingbaseES PLSQL中的结果集处理-存储过程和函数

关键字：KingbaseES、结果集处理、RETURN NEXT、RETURN QUERY、人大金仓

结果集处理

在PLSQL中，可以通过执行一系列SQL查询并返回一个查询结果集。用户可以使用基本的循环或单独的语句来运行查询、检索结果并完成结果处理以实现精确的控制过程。在本文中我们主要介绍在KES中，使用PLSQL的存储过程和函数来处理不同的查询结果集。

创建存储过程和函数

在PL/SQL中，可以使用SQL的CREATE语句创建一个新的存储过程或函数，例如要创建一个存储过程，我们可以使用CREATE PROCEDURE语句：

CREATE PROCEDURE pro_name(para INT)

AS

–定义局部变量

BEGIN

–执行语句

END;

其中，存储过程的执行语句可以是PLSQL语句和SQL语句，当SQL语句是查询语句时，存储过程可以直接将结果集返回给用户。

函数的创建与存储过程类似，使用CREATE FUNCTION语句，不同于存储过程，函数必须声明返回值的类型，主要注意的是如果想要利用函数处理结果集，函数的返回类型必须为SETOF或者TABLE：

CREATE OR REPLACE FUNCTION func_name(para INT) RETURN SETOF

AS

–定义局部变量

BEGIN

–执行语句

Return NEXT/QUERY;

END;

通过RETURN NEXT或者RETURN QUERY来返回结果集。

存储过程或函数的查询结果集处理

本节将介绍存储过程和函数的查询结果集处理方法。首先我们创建一个student表，用于后续的结果集处理：

DROP TABLE IF EXISTS student;

CREATE TABLE student(id INT, name VARCHAR2(20));

INSERT INTO student VALUES(1, ‘Li Ming’);

INSERT INTO student VALUES(2, ‘Zhang San’);

INSERT INTO student VALUES(3, ‘Wang Ping’);

INSERT INTO student VALUES(4, ‘Liu Xin’);

INSERT INTO student VALUES(5, ‘Li Si’);

INSERT INTO student VALUES(6, ‘Tan Wu’);

  1. 存储过程的查询结果集处理：

在KES中可以直接在存储过程中执行简单或复杂SELECT语句，并在调用存储过程的地方返回结果集。

\set SQLTERM /

CREATE OR REPLACE PROCEDURE pro_select

AS

BEGIN

SELECT * FROM student WHERE id = 3;

END;

/

BEGIN

pro_select;

END;

/

–输出

id | name

—-+———–

3 | Wang Ping

(1 row)

ANONYMOUS BLOCK

  1. 函数的查询结果集处理：

函数可以使用RETURN NEXT和RETURN QUERY返回处理后的结果集。使用RETURN NEXT和RETURN QUERY时需要在函数定义时，将返回类型设置为SETOF sometype，这里的sometype可以是标量也可以是一个复合数据类型（即表），对于复合类型，将返回一个表结构。

RETURN NEXT和RETURN QUERY 实际上不会从函数中返回，而是向函数的结果集中追加零或多行。然后会继续执行函数中的下一条语句。随着后继的RETURN NEXT和RETURN QUERY命令的执行，函数就建立起了整个结果集。最后一个不含表达式的RETURN语句会控制退出该函数。

  1. RETURN NEXT语句

1）当将函数的返回类型设置为复合类型时：

\set SQLTERM /

CREATE OR REPLACE FUNCTION func_return_next(n INT)

RETURNS SETOF student

AS

DECLARE

rec student%ROWTYPE;

BEGIN

FOR rec IN SELECT * FROM student WHERE id >= 3

LOOP

rec.id := rec.id + n; –对查询的一行结果进行处理

RETURN NEXT rec; –向函数的结果集追加1行

END LOOP;

RETURN; –表示函数执行结束

END;

/

–查询函数的结果集

SELECT * FROM func_return_next(5);

–输出

id | name

—-+———–

8 | Wang Ping

9 | Liu Xin

10 | Li Si

11 | Tan Wu

(4 rows)

2）当将函数的返回类型设置为标量类型时：

\set SQLTERM /

CREATE OR REPLACE FUNCTION func_return_next(n INT)

RETURNS SETOF VARCHAR2

AS

DECLARE

var VARCHAR2(20);

BEGIN

FOR var IN SELECT name FROM student WHERE id >= n

LOOP

RETURN NEXT var; –向函数的结果集追加1行

END LOOP;

RETURN; –表示函数执行结束

END;

/

–查询函数的结果集

SELECT * FROM func_return_next(1);

–输出

func_return_next

——————

Li Ming

Zhang San

Wang Ping

Liu Xin

Li Si

Tan Wu

(6 rows)

  1. RETURN QUREY：RETURN QUERY 可以与一个SELECT语句关联，并返回查询结果。

1）返回表中的一列的查询结果集

\set SQLTERM /

CREATE OR REPLACE FUNCTION func_return_query(i INT, n INT)

RETURNS SETOF VARCHAR2

AS

BEGIN

RETURN QUERY

SELECT name FROM student WHERE id >= i AND id < n;

RETURN;

END;

/SELECT * FROM func_return_query(1, 3);

–输出

func_return_query

——————-

Li Ming

Zhang San

(2 rows)

  1. 返回整个表的查询结果集：当函数返回类型为表时，可以使用RETURNS SETOF+表名，来指定返回类型。也可以使用RETURN table(para para_type,….)来指定返回的table类型，且指定的table中每列的参数类型需要与实际查询的表每列的参数类型相同。否则会发生错误。

\set SQLTERM /

CREATE OR REPLACE FUNCTION func_return_query(i INT, n INT)

RETURN table (id INT, name VARCHAR2(20)) –与student表类型相同

AS

BEGIN

RETURN QUERY

SELECT * FROM student WHERE id >= i AND id < n;

RETURN;

END;

/SELECT * FROM func_return_query(1, 3);

–输出

id | name

—-+———–

1 | Li Ming

2 | Zhang San

(2 rows)

  1. RETURN NEXT 与RETURN QUERY混合使用：当返回的SETOF类型相同时，RETURN NEXT 与RETURN QUERY可以混合使用。按照执行顺序向函数结果集追加查询结果。

\set SQLTERM /

CREATE OR REPLACE FUNCTION func_returns(n INT) RETURNS SETOF student

AS

DECLARE

rec student%ROWTYPE;

BEGIN

FOR rec IN SELECT * FROM student WHERE id >= n

LOOP

RETURN NEXT rec; –向函数结果集追加1行

RETURN QUERY –向函数结果集追加1或n行

SELECT * FROM student WHERE id = rec.id;

END LOOP;

RETURN;

END;

/

SELECT * FROM func_returns(4);

–输出

id | name

—-+———

4 | Liu Xin

4 | Liu Xin

5 | Li Si

5 | Li Si

6 | Tan Wu

6 | Tan Wu

(6 rows)

总结

本文主要介绍了在KES中的如何使用存储过程和函数进行查询结果集的处理，对于存储过程，可以直接在过程内执行SELECT查询语句，并在存储过程的调用处返回查询的结果集。对于函数，可以通过设置返回的SETOF类型来指定函数的返回结果集类型，并使用 RETURN NEXT语句向函数返回结果集追加单行查询结果，或使用RETURN QUERY语句向函数返回结果集追加N行查询结果，最后使用无表达式的RETURN语句结束函数的执行。对于函数的结果集，使用SELECT语句来查询并返回函数的结果集。

相比于使用游标和DBMS_SQL包来处理查询结果集，函数可以使用RETRN NEXT和RETURN QUERY语句更灵活的将查询的结果进行处理并生成一个复合需求的返回结果集，并在任意需要的地方进行返回。

参考文档：

《KingbaseES PL/SQL 过程语言参考手册》